I dug out my copy of THE GUIDE TO THE SQL STANDARD, 4th Edition, by Date and Darwen, and it states (in a footnote on page 151) that name specified for a scalar-expression in a SELECT clause can not be used in a WHERE, GROUP BY or HAVING clause as it is a column in the derived table, not the base table. Peter
From: Richard Hipp <[email protected]> >To: General Discussion of SQLite Database <[email protected]> >Sent: Wednesday, August 14, 2013 9:59 AM >Subject: Re: [sqlite] name resolutionn in GROUP BY > > >On Wed, Aug 14, 2013 at 12:22 PM, Rob Golsteijn ><[email protected]>wrote: > >> create table test(name); >> insert into test values (NULL); >> insert into test values ('abc'); >> >> select count(), >> NULLIF(name,'abc') AS name >> from test >> group by lower(name); >> > >So the question is, should the "name" symbol in the GROUP BY clause refer >to the original column name in the TEST table, or should it refer to the >result column called "name". SQLite version 3.7.15 picks the TEST table >column. Version 3.7.17 picks the result column. > >Anybody know which is correct? > >-- >D. Richard Hipp >[email protected] >_______________________________________________ >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
