[EMAIL PROTECTED] wrote:
Consider this query:
SELECT a, b FROM t1 UNION SELECT b, a FROM t1 ORDER BY a,b;
Is the query above equalent to:
(1) SELECT a, b FROM t1 UNION SELECT b, a FROM t1 ORDER BY 1,2;
Or is it the same as:
(2) SELECT a, b FROM t1 UNION SELECT b, a FROM t1 ORDER BY 2,1;
I need to know this in order to fix ticket #2296. It
might not be obvious why this is important to ticket #2296
but it is, so I would appreciate any help.
Richard,
If my reading of the SQL:1999 standard is correct (its more than a
little convoluted in this area), then from section 7.12 <query
expression> Syntax Rule 16 case b)i)1 we have:
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.
where T1 and T2 are the tables being operated on by the UNION and TR is
the result table. This case covers the normal case of matching column
names. The next case 16)b)i)2 covers the case of non matching column names:
Otherwise, the <column name> of the i-th column of TR is implementation
dependent
and not equivalent to the <column name> of any column, other than
itself, of any table referenced by any <table reference> contained in
the SQL statement.
This says that the output column names are neither a or b, but something
else. This would imply that the first query above is in fact a syntax
error since the output table does not have columns named a or b, and
therefore it can't be ordered by those columns. Either of the second two
queries should be legal, since they do not use column names for the
order by clause.
To generate a legal query the user would have to alias the column names
in one or both of the sub queries.
SELECT a, b FROM t1 UNION SELECT t1.b as a, t1.a as b from t1 ORDER by a, b
SELECT t1.a as b, t1.b as a FROM t1 UNION SELECT b, a from t1 ORDER by a, b
SELECT a as c, b as d FROM t1 UNION SELECT b as c, a as d from t1 ORDER
by c, d
HTH
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
