OK. The fact is still surprising considering the near column alias has precedence in every other situation [including the bug fix for CREATE TABLE ... AS SELECT]
The SQLite documentation could use a sentence about how SQLite's enhanced SQL GROUP BY name precedence works. https://www.postgresql.org/docs/9.5/static/sql-select.html "In case of ambiguity, a GROUP BY name will be interpreted as an input-column name rather than an output-column name." Peter On Mon, Jan 22, 2018 at 3:07 AM, Dan Kennedy <danielk1...@gmail.com> wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
