On 01/21/2018 07:21 AM, petern wrote:
SQLite 3.22.0 2018-01-12 23:38:10
dec3ea4e4e6c4b1761ddc883a29eaa50dcd663ce6199667cc0ff82f7849d4f2a
WITH t(j,k) AS (VALUES (2,4),(3,2),(3,8),(4,7)) SELECT max(j,k) AS j FROM t
GROUP BY j;
j
4
8
7
--Wrong answer.
--GROUP BY unexpectedly scopes outer source table column j rather than the
nearer local column alias j.
WITH t(j,k) AS (VALUES (2,4),(3,2),(3,8),(4,7)) SELECT max(j,k) AS x FROM t
GROUP BY x;
x
3
4
7
8
--Correct answer when the local column alias happens to be unique.
I think technically allowing an expression alias to be used in the GROUP
BY clause like that is not standard SQL. It just has to be supported for
backwards-compatibility. So SQLite tries to process the standard as
regular SQL before falling back to interpreting the identifier as an alias.
Dan.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
