Hi List, The behaviour of Sqlite of w.r.t. name resolving in "group by" caluses seems to have changed in the latest version. This might lead to errors in previously working SQL code, or worse, undetected changes in behaviour.
Example create table test(name); select min(name) from test group by lower(name); -- OK select min(name) as name from test group by lower(name); -- Error: misuse of aggregate: min() in version 3.7.17, OK in 3.7.15 In version version 3.7.15 the last query executed without problems, in 3.7.17 it reports an error (3.7.16 not tested). In the last query Sqlite 3.7.15 used "test.name" for "name" in the group by clause, Sqlite 3.7.15 seems to refer to the result column named "name". This difference in name resolution is also illustrated by the following example: .null <null> 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); In version 5.7.15 the output is: 1|<null> 1|<null> (i.e. "group by" made 2 groups) In version 5.7.17 the output is: 2|<null> (i.e. "group by" made 1 group) I couldn't find a specifcation of which name should be used in the group by clause. I think it does not make sense to use the result column alias in the "group by" clause since it is the result of a calculation based on a grouping, but then again also used to produce the groups. Hence using the name of the result row alias looks like a circular definition. My question is whether the change is a bug or an intended change? From the release history's text it looks like it could have been introduced by the changes in Ticket 2500cdb9be05 Regards, Rob Golsteijn _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users