Re: Improving "missing FROM-clause entry" message

I wrote:
>> I'm thinking about whether we can't improve the message for "missing
>> FROM-clause entry" to somehow account for situations where the table
>> does exist in the query but it's referenced from an improper place,
>> ...

> On further investigation, this is arguably a regression in 8.1.
> Every PG release back to 7.2 has responded to this query with

> NOTICE: adding missing FROM-clause entry for table "a"
> ERROR: JOIN/ON clause refers to "a", which is not part of JOIN

> In 8.1, where add_missing_from defaults to false, you get the first
> line as an ERROR and so the much-more-useful specific message doesn't
> appear. I think we need to do something about this.

After some thought I've come up with possible ways to handle this.

Plan A: when we are about to raise an error in warnAutoRange(),
scan the rangetable to see if there are any entries anywhere that could
match the specified table name (either by alias or by real table name).
If so, don't use the "missing FROM-clause entry" wording, but instead
say something like

ERROR: invalid reference to FROM-clause entry for table "foo"
HINT: The entry cannot be referenced from this part of the query.

When the match is by real table name and there's an alias, a better HINT
might be

HINT: You probably should have used the table alias "bar".

since this would do something useful for the perennial mistake

select foo.* from foo f;

Plan B: when we are about to raise an error in warnAutoRange(), instead
just save the error info in the ParseState struct and keep going. If
we get to the end of parsing without detecting any other error, report
the missing-FROM error. This would let the specific error message
ERROR: JOIN/ON clause refers to "a", which is not part of JOIN
come out when it's applicable, but not change the behavior otherwise.

Plan C: do both. This would give us the most specific error messages
possible without major restructuring.

A reasonable objection to either Plan A or Plan C is that it will add
error strings that are not currently in the translation message files;
which wouldn't matter for a HEAD-only patch, but I'd really like to
back-patch this into 8.1. Plan B wouldn't change the set of possible
messages.

If that's not considered a show-stopper, I'd like to go with Plan C.
We've certainly got plenty of evidence that this is a confusing error
condition, and the more we can do to explain the problem in the error
message, the less time will be wasted all around.

Comments? Any thoughts about the exact wording of the proposed new
messages?

regards, tom lane