On Fri, 28 Mar 2003, Jean-Christian Imbeault wrote:
> Stephan Szabo wrote:
> >
> > I think this is because by the time you get to the order by, products and
> > rel_cast_crew_movies are treated as being out of scope. The column
> > produced by the union is just named "id" I think.
>
> You're right. changing the ORDER by products.id to simply ORDER by id
> solved the problem ...
>
> I don't know much about SQL scoping but I would have hoped that a UNION
> could have kept the fully-qualified column names (i.e. products.id
> instead of simply ID).
I believe the appropriate portion of the rules is (7.10 SR12b)
1) Let C be the <column name> of the i-th column of T1. If
the <column name> of the i-th column of T2 is C, then
the <column name> of the i-th column of TR is C.
2) Otherwise, the <column name> of the i-th column of TR is
implementation-dependent and different from the <column
name> of any column, other than itself, of any table
referenced by any <table reference> contained in the
SQL-statement.
Note that AFAIK the column name reference above is just the column name
not a qualified name.
