Looking at the query I guess that 3.3.6 processed it incorrectly and
3.6.23.1 gives the correct result for it.
But I also think that it's written incorrectly and not as was
intended. The correct query will be like this:
select
count (*) from (select * from reporting) as foo
left outer join (select id from type) as A0
on A0.id = foo.id
left outer join (select id from level) as A1
on A1.id = foo.id
left outer join (select id from message_id) as A2
on A2.id = foo.id
left outer join (select id from message) as A3
on A3.id = foo.id
group by foo.id
And I bet this query will give the same results both on 3.3.6 and on 3.6.23.
Pavel
On Tue, Apr 27, 2010 at 2:49 PM, Peter Pawlowski <[email protected]> wrote:
> I have an unusual query that takes about 15 seconds to process on a tiny
> database using version 3.6.23.1. In older versions of sqlite3 this query
> executes in about 20ms and produces different output (I tested 3.3.6).
>
> I tried to simplify the query and data as much as possible. The query is
> weird, I'm not even sure I know what I would expect the output to be.
> But the fact that the behavior has changed is certain. See my test case
> below.
>
> My question is, is this a bug or is this unsupported or unspecified
> functionality?
>
> thanks!
> Peter Pawlowski
>
>
> ==== SQL statement ====
>
> select
> count (*) from (select * from reporting) as foo
> left outer join (select id from type) as A0
> left outer join (select id from level) as A1
> left outer join (select id from message_id) as A2
> left outer join (select id from message) as A3
> on ( A0.id = foo.id and A1.id = foo.id and A2.id = foo.id and A3.id
> = foo.id )
> group by foo.id
> ;
>
>
> ==== Test run with sqlite-3.3.6 ====
>
> $ sqlite3 -version
> 3.3.6
>
> $ time sqlite3 db < sql
> 1
> 1
> 1
> 1
> 1
> 1
> 1
> 1
> 1
> 1
> 1
> 1
> 1
> 1
> 1
> 1
> 1
> 1
> 1
> 1
> 1
> 1
> 1
> 1
> 1
> 1
> 1
> 1
> 1
> 1
>
> real 0m0.017s
> user 0m0.001s
> sys 0m0.009s
>
>
> ==== Test run with sqlite 3.6.23.1 ====
>
> $ ./sqlite3 -version
> 3.6.23.1
>
> $ time ./sqlite3 db < sql
> 27000
> 27000
> 27000
> 27000
> 27000
> 27000
> 27000
> 27000
> 27000
> 27000
> 27000
> 27000
> 27000
> 27000
> 27000
> 27000
> 27000
> 27000
> 27000
> 27000
> 27000
> 27000
> 27000
> 27000
> 27000
> 27000
> 27000
> 27000
> 27000
> 27000
>
> real 0m15.296s
> user 0m15.144s
> sys 0m0.030s
>
>
> ==== Database dump ====
>
> BEGIN TRANSACTION;
> CREATE TABLE reporting ( ID VARCHAR PRIMARY KEY, DATE INTEGER);
> INSERT INTO "reporting" VALUES('S9XnCAo2JBvX', 1272309512);
> INSERT INTO "reporting" VALUES('S9XnCAo+bxvX', 1272309512);
> INSERT INTO "reporting" VALUES('S9XnCQG92xvX', 1272309513);
> INSERT INTO "reporting" VALUES('S9XnCQH5shvX', 1272309513);
> INSERT INTO "reporting" VALUES('S9XnCQIBzRvX', 1272309513);
> INSERT INTO "reporting" VALUES('S9XnCQp6EBvX', 1272309513);
> INSERT INTO "reporting" VALUES('S9XnCQqmiBvX', 1272309513);
> INSERT INTO "reporting" VALUES('S9XnCQqvCxvX', 1272309513);
> INSERT INTO "reporting" VALUES('S9XnCgaTChvX', 1272309514);
> INSERT INTO "reporting" VALUES('S9XnCga9EhvX', 1272309514);
> INSERT INTO "reporting" VALUES('S9XnCgbF-hvX', 1272309514);
> INSERT INTO "reporting" VALUES('S9XnCg1sVRvX', 1272309514);
> INSERT INTO "reporting" VALUES('S9XnCg2VwxvX', 1272309514);
> INSERT INTO "reporting" VALUES('S9XnCg2dnxvX', 1272309514);
> INSERT INTO "reporting" VALUES('S9XnCwgyBxvX', 1272309515);
> INSERT INTO "reporting" VALUES('S9XnCwhcEhvX', 1272309515);
> INSERT INTO "reporting" VALUES('S9XnDAhuBBvX', 1272309516);
> INSERT INTO "reporting" VALUES('S9XnDAh2OBvX', 1272309516);
> INSERT INTO "reporting" VALUES('S9XnDQQBVRvX', 1272309517);
> INSERT INTO "reporting" VALUES('S9XnDQQu-xvX', 1272309517);
> INSERT INTO "reporting" VALUES('S9XnDQQ3BBvX', 1272309517);
> INSERT INTO "reporting" VALUES('S9XnDQrNLhvX', 1272309517);
> INSERT INTO "reporting" VALUES('S9XnDQr2axvX', 1272309517);
> INSERT INTO "reporting" VALUES('S9XnDgFfli74', 1272309518);
> INSERT INTO "reporting" VALUES('S9XnDgsHSBvX', 1272309518);
> INSERT INTO "reporting" VALUES('S9XnkgP0yBvX', 1272309650);
> INSERT INTO "reporting" VALUES('S9Xnkg1QBBvX', 1272309650);
> INSERT INTO "reporting" VALUES('S9Xnkg17UxvX', 1272309650);
> INSERT INTO "reporting" VALUES('S9Xnkg2W1BvX', 1272309650);
> INSERT INTO "reporting" VALUES('S9XnlAKYNFwW', 1272309652);
> CREATE TABLE type (id varchar);
> INSERT INTO "type" VALUES('S9XnCAo2JBvX');
> INSERT INTO "type" VALUES('S9XnCAo+bxvX');
> INSERT INTO "type" VALUES('S9XnCQG92xvX');
> INSERT INTO "type" VALUES('S9XnCQH5shvX');
> INSERT INTO "type" VALUES('S9XnCQIBzRvX');
> INSERT INTO "type" VALUES('S9XnCQp6EBvX');
> INSERT INTO "type" VALUES('S9XnCQqmiBvX');
> INSERT INTO "type" VALUES('S9XnCQqvCxvX');
> INSERT INTO "type" VALUES('S9XnCgaTChvX');
> INSERT INTO "type" VALUES('S9XnCga9EhvX');
> INSERT INTO "type" VALUES('S9XnCgbF-hvX');
> INSERT INTO "type" VALUES('S9XnCg1sVRvX');
> INSERT INTO "type" VALUES('S9XnCg2VwxvX');
> INSERT INTO "type" VALUES('S9XnCg2dnxvX');
> INSERT INTO "type" VALUES('S9XnCwgyBxvX');
> INSERT INTO "type" VALUES('S9XnCwhcEhvX');
> INSERT INTO "type" VALUES('S9XnDAhuBBvX');
> INSERT INTO "type" VALUES('S9XnDAh2OBvX');
> INSERT INTO "type" VALUES('S9XnDQQBVRvX');
> INSERT INTO "type" VALUES('S9XnDQQu-xvX');
> INSERT INTO "type" VALUES('S9XnDQQ3BBvX');
> INSERT INTO "type" VALUES('S9XnDQrNLhvX');
> INSERT INTO "type" VALUES('S9XnDQr2axvX');
> INSERT INTO "type" VALUES('S9XnDgFfli74');
> INSERT INTO "type" VALUES('S9XnDgsHSBvX');
> INSERT INTO "type" VALUES('S9XnkgP0yBvX');
> INSERT INTO "type" VALUES('S9Xnkg1QBBvX');
> INSERT INTO "type" VALUES('S9Xnkg17UxvX');
> INSERT INTO "type" VALUES('S9Xnkg2W1BvX');
> INSERT INTO "type" VALUES('S9XnlAKYNFwW');
> CREATE TABLE message_id (id varchar);
> INSERT INTO "message_id" VALUES('S9XnCAo2JBvX');
> INSERT INTO "message_id" VALUES('S9XnCAo+bxvX');
> INSERT INTO "message_id" VALUES('S9XnCQG92xvX');
> INSERT INTO "message_id" VALUES('S9XnCQH5shvX');
> INSERT INTO "message_id" VALUES('S9XnCQIBzRvX');
> INSERT INTO "message_id" VALUES('S9XnCQp6EBvX');
> INSERT INTO "message_id" VALUES('S9XnCQqmiBvX');
> INSERT INTO "message_id" VALUES('S9XnCQqvCxvX');
> INSERT INTO "message_id" VALUES('S9XnCgaTChvX');
> INSERT INTO "message_id" VALUES('S9XnCga9EhvX');
> INSERT INTO "message_id" VALUES('S9XnCgbF-hvX');
> INSERT INTO "message_id" VALUES('S9XnCg1sVRvX');
> INSERT INTO "message_id" VALUES('S9XnCg2VwxvX');
> INSERT INTO "message_id" VALUES('S9XnCg2dnxvX');
> INSERT INTO "message_id" VALUES('S9XnCwgyBxvX');
> INSERT INTO "message_id" VALUES('S9XnCwhcEhvX');
> INSERT INTO "message_id" VALUES('S9XnDAhuBBvX');
> INSERT INTO "message_id" VALUES('S9XnDAh2OBvX');
> INSERT INTO "message_id" VALUES('S9XnDQQBVRvX');
> INSERT INTO "message_id" VALUES('S9XnDQQu-xvX');
> INSERT INTO "message_id" VALUES('S9XnDQQ3BBvX');
> INSERT INTO "message_id" VALUES('S9XnDQrNLhvX');
> INSERT INTO "message_id" VALUES('S9XnDQr2axvX');
> INSERT INTO "message_id" VALUES('S9XnDgFfli74');
> INSERT INTO "message_id" VALUES('S9XnDgsHSBvX');
> INSERT INTO "message_id" VALUES('S9XnkgP0yBvX');
> INSERT INTO "message_id" VALUES('S9Xnkg1QBBvX');
> INSERT INTO "message_id" VALUES('S9Xnkg17UxvX');
> INSERT INTO "message_id" VALUES('S9Xnkg2W1BvX');
> INSERT INTO "message_id" VALUES('S9XnlAKYNFwW');
> CREATE TABLE message (id varchar);
> INSERT INTO "message" VALUES('S9XnCAo2JBvX');
> INSERT INTO "message" VALUES('S9XnCAo+bxvX');
> INSERT INTO "message" VALUES('S9XnCQG92xvX');
> INSERT INTO "message" VALUES('S9XnCQH5shvX');
> INSERT INTO "message" VALUES('S9XnCQIBzRvX');
> INSERT INTO "message" VALUES('S9XnCQp6EBvX');
> INSERT INTO "message" VALUES('S9XnCQqmiBvX');
> INSERT INTO "message" VALUES('S9XnCQqvCxvX');
> INSERT INTO "message" VALUES('S9XnCgaTChvX');
> INSERT INTO "message" VALUES('S9XnCga9EhvX');
> INSERT INTO "message" VALUES('S9XnCgbF-hvX');
> INSERT INTO "message" VALUES('S9XnCg1sVRvX');
> INSERT INTO "message" VALUES('S9XnCg2VwxvX');
> INSERT INTO "message" VALUES('S9XnCg2dnxvX');
> INSERT INTO "message" VALUES('S9XnCwgyBxvX');
> INSERT INTO "message" VALUES('S9XnCwhcEhvX');
> INSERT INTO "message" VALUES('S9XnDAhuBBvX');
> INSERT INTO "message" VALUES('S9XnDAh2OBvX');
> INSERT INTO "message" VALUES('S9XnDQQBVRvX');
> INSERT INTO "message" VALUES('S9XnDQQu-xvX');
> INSERT INTO "message" VALUES('S9XnDQQ3BBvX');
> INSERT INTO "message" VALUES('S9XnDQrNLhvX');
> INSERT INTO "message" VALUES('S9XnDQr2axvX');
> INSERT INTO "message" VALUES('S9XnDgFfli74');
> INSERT INTO "message" VALUES('S9XnDgsHSBvX');
> INSERT INTO "message" VALUES('S9XnkgP0yBvX');
> INSERT INTO "message" VALUES('S9Xnkg1QBBvX');
> INSERT INTO "message" VALUES('S9Xnkg17UxvX');
> INSERT INTO "message" VALUES('S9Xnkg2W1BvX');
> INSERT INTO "message" VALUES('S9XnlAKYNFwW');
> CREATE TABLE level (id varchar);
> INSERT INTO "level" VALUES('S9XnCAo2JBvX');
> INSERT INTO "level" VALUES('S9XnCAo+bxvX');
> INSERT INTO "level" VALUES('S9XnCQG92xvX');
> INSERT INTO "level" VALUES('S9XnCQH5shvX');
> INSERT INTO "level" VALUES('S9XnCQIBzRvX');
> INSERT INTO "level" VALUES('S9XnCQp6EBvX');
> INSERT INTO "level" VALUES('S9XnCQqmiBvX');
> INSERT INTO "level" VALUES('S9XnCQqvCxvX');
> INSERT INTO "level" VALUES('S9XnCgaTChvX');
> INSERT INTO "level" VALUES('S9XnCga9EhvX');
> INSERT INTO "level" VALUES('S9XnCgbF-hvX');
> INSERT INTO "level" VALUES('S9XnCg1sVRvX');
> INSERT INTO "level" VALUES('S9XnCg2VwxvX');
> INSERT INTO "level" VALUES('S9XnCg2dnxvX');
> INSERT INTO "level" VALUES('S9XnCwgyBxvX');
> INSERT INTO "level" VALUES('S9XnCwhcEhvX');
> INSERT INTO "level" VALUES('S9XnDAhuBBvX');
> INSERT INTO "level" VALUES('S9XnDAh2OBvX');
> INSERT INTO "level" VALUES('S9XnDQQBVRvX');
> INSERT INTO "level" VALUES('S9XnDQQu-xvX');
> INSERT INTO "level" VALUES('S9XnDQQ3BBvX');
> INSERT INTO "level" VALUES('S9XnDQrNLhvX');
> INSERT INTO "level" VALUES('S9XnDQr2axvX');
> INSERT INTO "level" VALUES('S9XnDgFfli74');
> INSERT INTO "level" VALUES('S9XnDgsHSBvX');
> INSERT INTO "level" VALUES('S9XnkgP0yBvX');
> INSERT INTO "level" VALUES('S9Xnkg1QBBvX');
> INSERT INTO "level" VALUES('S9Xnkg17UxvX');
> INSERT INTO "level" VALUES('S9Xnkg2W1BvX');
> INSERT INTO "level" VALUES('S9XnlAKYNFwW');
> CREATE INDEX reporting_date ON reporting ( DATE );
> COMMIT;
>
>
>
>
>
>
> --
> Peter Pawlowski | Senior Software Engineer
> Office: +1.412.422.2499 x116
> [email protected] | Connect: www.vivisimo.com
> Vivisimo - Information Optimized
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
