Re: [sqlite] name resolutionn in GROUP BY

2013-08-15 Thread Richard Hipp
On Wed, Aug 14, 2013 at 12:22 PM, Rob Golsteijn wrote: > > 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

Re: [sqlite] name resolutionn in GROUP BY

2013-08-15 Thread Marc L. Allen
Good point. Ok.. I'm convinced. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of James K. Lowden Sent: Thursday, August 15, 2013 12:27 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] name resolutionn in GROUP BY On Wed

Re: [sqlite] name resolutionn in GROUP BY

2013-08-14 Thread James K. Lowden
On Wed, 14 Aug 2013 14:57:19 -0500 "Marc L. Allen" wrote: > I'd actually like a compromise. Allow GROUP BY to accept a derived > name if no base name exists. I realize that's against spec, but > there's no ambiguity (as it otherwise errors out), It would also

Re: [sqlite] name resolutionn in GROUP BY

2013-08-14 Thread Stephan Beal
On Thu, Aug 15, 2013 at 1:12 AM, Peter Aronson wrote: > While I can certainly see the value of going with what PostgreSQL and SQL > Server do on the ORDER BY issue, I have to say that I suspect that Oracle's > behavior here seem more in line Principle of Least Astonishment.

Re: [sqlite] name resolutionn in GROUP BY

2013-08-14 Thread Peter Aronson
clever thing to do in the first place, even if legal.   Peter From: Richard Hipp <d...@sqlite.org> >To: Peter Aronson <pbaron...@att.net>; General Discussion of SQLite Database ><sqlite-users@sqlite.org> >Sent: Wednesday, August 14, 2013 2:05 PM >Subject: Re: [sql

Re: [sqlite] name resolutionn in GROUP BY

2013-08-14 Thread Richard Hipp
On Wed, Aug 14, 2013 at 5:02 PM, Peter Aronson wrote: > If I understand Dominique's post, Oracle works like SQLite 3.7.15 as > well. Things only got confusing when we moved from discussing GROUP BY to > discussing ORDER BY for some reason. > There are two separate (though

Re: [sqlite] name resolutionn in GROUP BY

2013-08-14 Thread Peter Aronson
.org> >Sent: Wednesday, August 14, 2013 12:40 PM >Subject: Re: [sqlite] name resolutionn in GROUP BY > > >On Wed, Aug 14, 2013 at 3:08 PM, Igor Tandetnik <i...@tandetnik.org> wrote: > >> >> Most DBMS allow sorting (and grouping) by arbitrary expression

Re: [sqlite] name resolutionn in GROUP BY

2013-08-14 Thread Richard Hipp
On Wed, Aug 14, 2013 at 12:22 PM, Rob Golsteijn wrote: > > The behaviour of Sqlite of w.r.t. name resolving in "group by" caluses > seems to have changed in the latest version > Two new tickets have been entered: http://www.sqlite.org/src/info/1c69be2daf

Re: [sqlite] name resolutionn in GROUP BY

2013-08-14 Thread Marc L. Allen
in the GROUP BY clause. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Wednesday, August 14, 2013 3:40 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] name resolutionn in GROUP BY On Wed

Re: [sqlite] name resolutionn in GROUP BY

2013-08-14 Thread Richard Hipp
On Wed, Aug 14, 2013 at 3:08 PM, Igor Tandetnik wrote: > > Most DBMS allow sorting (and grouping) by arbitrary expressions, which > means that the standard is not directly applicable. One has to extrapolate. > PostgreSQL, MS-SQL, and SQLite 3.7.15 work one way. Oracle and

Re: [sqlite] name resolutionn in GROUP BY

2013-08-14 Thread Richard Hipp
On Wed, Aug 14, 2013 at 3:08 PM, Igor Tandetnik wrote: > 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,

Re: [sqlite] name resolutionn in GROUP BY

2013-08-14 Thread Igor Tandetnik
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 &

Re: [sqlite] name resolutionn in GROUP BY

2013-08-14 Thread Dominique Devienne
On Wed, Aug 14, 2013 at 8:42 PM, Dominique Devienne wrote: > On Wed, Aug 14, 2013 at 8:03 PM, Richard Hipp wrote: > >> On Wed, Aug 14, 2013 at 1:59 PM, Dominique Devienne > >wrote: >> > Not authoritative of course, but Oracle seems to

Re: [sqlite] name resolutionn in GROUP BY

2013-08-14 Thread Dominique Devienne
On Wed, Aug 14, 2013 at 8:03 PM, Richard Hipp wrote: > On Wed, Aug 14, 2013 at 1:59 PM, Dominique Devienne >wrote: > > Not authoritative of course, but Oracle seems to agree with the > previous behavior. --DD > > Dominique, can you please try the following

Re: [sqlite] name resolutionn in GROUP BY

2013-08-14 Thread Marc L. Allen
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

Re: [sqlite] name resolutionn in GROUP BY

2013-08-14 Thread Igor Tandetnik
On 8/14/2013 12:59 PM, Richard Hipp wrote: On Wed, Aug 14, 2013 at 12:22 PM, Rob Golsteijn 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

Re: [sqlite] name resolutionn in GROUP BY

2013-08-14 Thread Marc L. Allen
] name resolutionn in GROUP BY On Wed, Aug 14, 2013 at 6:59 PM, Richard Hipp <d...@sqlite.org> 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);

Re: [sqlite] name resolutionn in GROUP BY

2013-08-14 Thread Richard Hipp
On Wed, Aug 14, 2013 at 1:59 PM, Dominique Devienne wrote: > > Not authoritative of course, but Oracle seems to agree with the previous > behavior. --DD > Dominique, can you please try the following SQL on Oracle and let me know what you get: CREATE TABLE t1(m VARCHAR(4));

Re: [sqlite] name resolutionn in GROUP BY

2013-08-14 Thread Dominique Devienne
On Wed, Aug 14, 2013 at 6:59 PM, Richard Hipp wrote: > On Wed, Aug 14, 2013 at 12:22 PM, Rob Golsteijn > wrote: > > > create table test(name); > > insert into test values (NULL); > > insert into test values ('abc'); > > > > select count(), > >

Re: [sqlite] name resolutionn in GROUP BY

2013-08-14 Thread Richard Hipp
On Wed, Aug 14, 2013 at 12:22 PM, Rob Golsteijn 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,

[sqlite] name resolutionn in GROUP BY

2013-08-14 Thread Rob Golsteijn
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