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
