Samuel R. Neff wrote:
Still, I think backwards compatibility and consistency with other databases
would be most important in this situation. I just checked MSSQL and it's
same as current sqlite which uses the first select statement's column names.
Samuel,
The following is from Oracle's documentation:
For compound queries containing set operators |UNION|, |INTERSECT|,
|MINUS|, or |UNION| |ALL|, the |ORDER| |BY| clause must specify
positions or aliases rather than explicit expressions. Also, the |ORDER|
|BY| clause can appear only in the last component query. The |ORDER|
|BY| clause orders all rows returned by the entire compound query.
So they do require positions or aliases as I suggested earlier.
Compatibility is hard to achieve when everyone does things differently.
The best we can do is work towards a common standard. Even Oracle is
changing to be more standard compliant as indicated by this note I ran
into while reviewing their docs.
Note:
To comply with emerging SQL standards, a future release of Oracle will
give the |INTERSECT| operator greater precedence than the other set
operators. Therefore, you should use parentheses to specify order of
evaluation in queries that use the |INTERSECT| operator with other set
operators.
It doesn't just affect order by too.. based on the standard SQLite should be
returning a different column name entirely in the result set. Surely it
would break a lot of code out there if SQLite suddenly started returning
seemingly random column names from union queries when the query didn't
specifically alias the columns.
I agree, this seems strange to me too. I suspect I have missed some
detail in the standard's convoluted description of parsing and
propagating column names in queries. I'm still looking for that missing
nugget.
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
