Pavel, Thank you so much!! You are right, this change solves all of my problems. Looking at your suggestion, the syntax makes a lot more sense this way. I can't tell you how helpful this is.
very best, Peter Pavel Ivanov wrote: > 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 -- 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
