This appears to be how MS SQL handles it... looking at the definitions below, MS SQL uses the base value in GROUP BY and the derived value in ORDER BY.
That said, 'lower(m)' referenced the base m, not the derived m in the ORDER BY. I'm afraid I don't understand enough about COLLATE to get why that changed the result. -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: Wednesday, August 14, 2013 2:27 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] name resolutionn in GROUP BY On 8/14/2013 12:59 PM, Richard Hipp wrote: > On Wed, Aug 14, 2013 at 12:22 PM, Rob Golsteijn > <rob.golste...@mapscape.eu>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? For what it's worth, my reading of SQL-92 suggests that 3.7.15 is correct. 1) If no <where clause> is specified, then let T be the result of the preceding <from clause>; otherwise, let T be the result of the preceding <where clause>. 2) Each <column reference> in the <group by clause> shall unambigu- ously reference a column of T. ORDER BY clause is different - column names there resolve in the context of the whole preceding SELECT statement, not just its FROM part: 3) Let T be the table specified by the <query expression>. 4) If ORDER BY is specified, then each <sort specification> in the <order by clause> shall identify a column of T. -- Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
