Note that "ORDER BY lower(m)" is not valid SQL-92. The standard only
allows sorting by columns that appear in the SELECT clause, referenced
by name or by ordinal. It doesn't allow sorting by arbitrary
expressions, nor even by columns that appear in the underlying tables
but are not selected.
Most DBMS allow sorting (and grouping) by arbitrary expressions, which
means that the standard is not directly applicable. One has to extrapolate.
Igor Tandetnik
On 8/14/2013 2:41 PM, Marc L. Allen wrote:
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
