Re: [sqlite] JOIN bug in 3.7.2, not in 3.6.22

2011-01-18 Thread Nicolas Williams
On Tue, Jan 18, 2011 at 10:13:10PM +0100, Florian Weimer wrote: > * Richard Hipp: > > > I don't think it makes sense in SQL (not just SQLite but SQL in > > general) for an aggregate query to return columns that are not in > > the GROUP BY clause. > > Isn't this just what PostgreSQL implements as

Re: [sqlite] JOIN bug in 3.7.2, not in 3.6.22

2011-01-18 Thread Florian Weimer
* Richard Hipp: > I don't think it makes sense in SQL (not just SQLite but SQL in > general) for an aggregate query to return columns that are not in > the GROUP BY clause. Isn't this just what PostgreSQL implements as DISTINCT ON? Then it *is* useful.

Re: [sqlite] JOIN bug in 3.7.2, not in 3.6.22

2011-01-17 Thread David Burström
Dan, Richard, Igor, thanks for your input, and yes, it seems as if the gamble is no longer safe. Hopefully I'm the only one that has run into this side effect ;) :-David On 01/17/2011 04:57 PM, Igor Tandetnik wrote: > David Burström wrote: >> SELECT starttime,

Re: [sqlite] JOIN bug in 3.7.2, not in 3.6.22

2011-01-17 Thread Igor Tandetnik
David Burström wrote: > SELECT starttime, endtime from entry LEFT JOIN interval ON > interval.entryid = entry.id GROUP BY entry.id HAVING starttime = > MAX(starttime); The behavior of this statement is unspecified. In standard SQL, it is syntactically invalid - in a

Re: [sqlite] JOIN bug in 3.7.2, not in 3.6.22

2011-01-17 Thread Black, Michael (IS)
X(starttime); Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org on behalf of David Burström Sent: Mon 1/17/2011 9:37 AM To: General Discussion of SQLite Database Subject:

Re: [sqlite] JOIN bug in 3.7.2, not in 3.6.22

2011-01-17 Thread Richard Hipp
On Mon, Jan 17, 2011 at 7:47 AM, David Burström wrote: > Hello all! > > I stumbled across this strange bug during Android development on 2.2.1 > late last night. Please run the following snippet in SQLite 3.7.2 and > 3.6.22 to compare the differences. The comments shows

Re: [sqlite] JOIN bug in 3.7.2, not in 3.6.22

2011-01-17 Thread Dan Kennedy
> -- if endtime is in a different position in the table, the query works > CREATE TABLE interval (endtime INTEGER, entryid INTEGER, starttime INTEGER); > CREATE TABLE entry (id INTEGER PRIMARY KEY AUTOINCREMENT); > > INSERT INTO entry (id) VALUES ( 42); > > INSERT INTO interval (endtime,

Re: [sqlite] JOIN bug in 3.7.2, not in 3.6.22

2011-01-17 Thread David Burström
> Senior Scientist > Advanced Analytics Directorate > Northrop Grumman Information Systems > > > > > From: sqlite-users-boun...@sqlite.org on behalf of David Burström > Sent: Mon 1/17/2011 6:47 AM > To: sqlite-users@sqlite.org > Subject:

Re: [sqlite] JOIN bug in 3.7.2, not in 3.6.22

2011-01-17 Thread Black, Michael (IS)
on behalf of David Burström Sent: Mon 1/17/2011 6:47 AM To: sqlite-users@sqlite.org Subject: EXTERNAL:[sqlite] JOIN bug in 3.7.2, not in 3.6.22 Hello all! I stumbled across this strange bug during Android development on 2.2.1 late last night. Please run the following snippet in SQLite 3.7.2

[sqlite] JOIN bug in 3.7.2, not in 3.6.22

2011-01-17 Thread David Burström
Hello all! I stumbled across this strange bug during Android development on 2.2.1 late last night. Please run the following snippet in SQLite 3.7.2 and 3.6.22 to compare the differences. The comments shows what alterations you can make to make the query return the expected result. :-David