sqlite> select 1 union select 1 from (select 1 as x) group by x order by 1 collate binary; Error: no such column: x
sqlite> select 1 from (select 1 as x) group by x; 1 sqlite> select 1 from (select 1 as x) group by x order by 1; 1 sqlite> select 1 from (select 1 as x) group by x order by 1 collate binary; 1 sqlite> select 1 order by 1; 1 sqlite> select 1 order by 1 collate binary; 1 sqlite> select 1 union select 1 order by 1 collate binary; 1 sqlite> select 1 union select 1 from (select 1 as x) group by x; 1 sqlite> select 1 union select 1 from (select 1 as x) group by x order by 1; 1 sqlite> select 1 union select 1 from (select 1 as x) group by x order by 1 collate binary; Error: no such column: x Interesting indeed -- seems we get lost applying the explicit collation over the union even though both halves of the union work properly by themselves. --- Theory is when you know everything but nothing works. Practice is when everything works but no one knows why. Sometimes theory and practice are combined: nothing works and no one knows why. >-----Original Message----- >From: [email protected] [mailto:sqlite-users- >[email protected]] On Behalf Of RSmith >Sent: Saturday, 8 November, 2014 06:01 >To: General Discussion of SQLite Database >Subject: Re: [sqlite] bug: "no such column" with UNION + subquery + GROUP >BY + ORDER BY + COLLATE > > >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 >[email protected] >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
