On 2014/11/08 14:21, Clemens Ladisch wrote:
Hi,
the following query fails to parse, although it should be valid:
sqlite> select 1 union select 1 from (select 1 as x) group by x order by 1
collate binary;
Error: no such column: x
Will call the above Version A.
I don't see how that can ever be valid - the "as x" pertains to a sub-query not referenced by that column in the higher query and
without any alias to the derived table which makes for serious lack of inferred referencing. It doesn't parse in MSSQL and doesn't
parse in MySQL either with errors that pertain to aliasing the derived table (as expected) [1]. I believe a valid way to put that
would be:
Version B: select 1 union select 1 from (select 1 as x) as t group by x order
by 1 collate binary;
since that added "as t" now qualifies the derived table removing the ambiguity
and make x referencable (if there is such a word) outside in the precise higher query
that is trying to do the grouping.
or perhaps something like:
Version C: select 1 union select 1 from (select 1 as x group by x) as t order
by 1 collate binary;
since the alias and the reference to it is again contained in a single
query-level.
Both versions B and C work in all of SQLite, MSSQL and MySQL, as it should. Of course SQL is not defined by "what other Engines do"
but I cannot seem to find a reference in the SQL-92 standard that mandates version A either (though, to be fair, I was speed-reading
to the seemingly relevant parts and might have missed it).
Not a bug I think.
PS: Did not have a PostGres DB handy to check, but I believe it will behave similarly - would love to know actually, if someone
would be so kind...
[1] Have replaced the "collate binary" with appropriate clauses for the other
engines.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
