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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
