Hmmm...my initial testing on 3.7.4 worked...so here's how to reproduce.
Analyze fixes it.
Why does the first entry show 1,000,000 rows???
It appears the automatic index isn't working in this case....
Before analyze
0|0|0|SCAN TABLE entry USING INTEGER PRIMARY KEY (~1000000 rows)
0|1|1|SEARCH TABLE interval USING AUTOMATIC COVERING INDEX (entryid=?) (~7 rows)
After analyze
3|1
0|0|0|SCAN TABLE entry USING INTEGER PRIMARY KEY (~1 rows)
0|1|1|SCAN TABLE interval (~2 rows)
CREATE TABLE interval (endtime INTEGER, entryid INTEGER, starttime INTEGER);
INSERT INTO "interval" VALUES(2,42,1);
INSERT INTO "interval" VALUES(1,42,3);
CREATE TABLE entry (id INTEGER PRIMARY KEY AUTOINCREMENT);
INSERT INTO "entry" VALUES(42);
DELETE FROM sqlite_sequence;
INSERT INTO "sqlite_sequence" VALUES('entry',42);
select 'Before analyze';
SELECT starttime, endtime from entry LEFT JOIN interval ON
interval.entryid = entry.id GROUP BY entry.id HAVING starttime =
MAX(starttime);
explain query plan SELECT starttime, endtime from entry LEFT JOIN interval ON
interval.entryid = entry.id GROUP BY entry.id HAVING starttime =
MAX(starttime);
select 'After analyze';
analyze;
SELECT starttime, endtime from entry LEFT JOIN interval ON
interval.entryid = entry.id GROUP BY entry.id HAVING starttime =
MAX(starttime);
explain query plan SELECT starttime, endtime from entry LEFT JOIN interval ON
interval.entryid = entry.id GROUP BY entry.id HAVING starttime =
MAX(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: EXTERNAL:Re: [sqlite] JOIN bug in 3.7.2, not in 3.6.22
I just tried the 3.7.4 binary on Linux, and the bug is still around.
:-David
On 01/17/2011 04:25 PM, Black, Michael (IS) wrote:
> This may be the patch that fixes your problem...
> http://www.sqlite.org/src/info/ece641eb89
>
> Was released in 3.7.3
>
> 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 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 and
> 3.6.22 to compare the differences. The comments shows what alterations
> you can make to make the query return the expected result.
>
>
>
>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
