Re: Table name scope (was Re: Outer joins aren't working with views)

Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)edu> writes:
>> The first and second items here seem to be perfectly clear that the
>> names t1 and t2 have scope across the whole SELECT statement and are not
>> hidden within the <joined table> formed by the OUTER JOIN clause.

> You are right. If there is a "correlation name", then those underlying
> table names become invisible, but that was not in the example here.

Right, either the table's real name or its alias ("correlation name") is
introduced into the query's scope, not both. AFAICT the scope rules
are the same for either one, though.

> Rereading my Date and Darwen clarified this for me. However, there are
> *some* columns for which this explicit table qualification is not
> allowed, including in the example of NATURAL JOIN.

I disagree on that. The table's real/alias name is certainly supposed
to be accessible, and I see nothing in the spec that says that only some
of its columns are accessible via qualification. What the spec does say
is that the *output* of the join has only one copy of the joined column.
In other words, given table A with columns ID and CA, and table B with
columns ID and CB, I believe the correct behavior is

SELECT * FROM (A NATURAL JOIN B) J produces ID, CA, CB

SELECT J.* FROM (A NATURAL JOIN B) J produces ID, CA, CB

SELECT A.* FROM (A NATURAL JOIN B) J produces ID, CA

SELECT B.* FROM (A NATURAL JOIN B) J produces ID, CB

If it's an outer join then J.ID is subtly different from A.ID and/or
B.ID --- the spec defines the output column as COALESCE(A.ID,B.ID)
(cf SQL92 7.5 <joined table>, syntax rule 6.d) to get rid of introduced
nulls. BTW, our implementation simplifies that to A.ID for an inner or
left join, or B.ID for a right join, and only uses the full COALESCE
expression for a full join.

Anyway, I believe it's true that you can't get at A.ID or B.ID in
this example except by qualifying the column name with the table name
--- but I don't see where it says that you shouldn't be able to get
at them at all. If that were true then the definition in 7.5.6.d
wouldn't be legal, because that's exactly the syntax it uses to define
the joined column.

> Date and Darwen, 4th ed, pp 142-144 discuss various aspects of join
> scope and behavior. For NATURAL JOIN, the columns with common names
> forming the join columns *lose* their underlying table name, since they
> can't be traced back to a column from a specific table (the table of
> origin is ambiguous).

My reading is that the output columns are qualified with the JOIN
clause's correlation name, if any (J in my example). If you didn't
bother to stick a correlation name on the join clause, you couldn't
refer to them with a qualified name.

In an example like

SELECT * FROM (A NATURAL LEFT JOIN (B NATURAL FULL JOIN C));

supposing that all three tables have a column ID, then the output ID
column of the B/C join has no qualified name, and it would indeed be
impossible to refer to it from the SELECT list. The only IDs accessible
from the SELECT list are the also-qualified-name-less output of the
left join and A.ID, B.ID, C.ID, none of which are quite the same as
the output of the full join. Perhaps what Date and Darwen are talking
about is cases like this?

regards, tom lane