This was fixed September 7. The fix appears in 3.14.2 and also on the current 3.15.0.
https://www.sqlite.org/releaselog/3_14_2.html The ORDER BY LIMIT optimization is not valid unless the inner-most IN operator loop is actually used by the query plan. Ticket https://sqlite.org/src/info/0c4df46116e90f92 SQLite version 3.15.0 2016-10-10 14:48:36 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table i (id integer primary key, flags integer); sqlite> insert into i values (1,1),(2,1),(3,1),(4,1),(5,5),(6,6),(7,4); sqlite> create table m (id integer); sqlite> insert into m values (1),(2),(3),(4),(5),(6),(7); sqlite> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC; 6 5 7 1 2 3 4 sqlite> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC limit 3; 6 5 7 > -----Original Message----- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Tobias Ellinghaus > Sent: Tuesday, 11 October, 2016 04:53 > To: sqlite-users@mailinglists.sqlite.org > Subject: [sqlite] LIMIT doesn't return expected rows > > Hello, > > first let me mention that I am new to this list so apologies if my > question > came up before. I couldn't find anything though, and in #sqlite on > Freenode I > was pointed here, so here I am. > > I am working on some code that creates a (potentially big) SQL query on > the > fly. My problem is, that I need to get a certain subset of the result the > query > gives. However, that somehow doesn't work with sqlite 3.14.2. In 3.8.5 it > still worked according to someone on IRC testing it. > > Example database: > > sqlite> .dump i > PRAGMA foreign_keys=OFF; > BEGIN TRANSACTION; > CREATE TABLE i (id integer primary key autoincrement, flags integer); > INSERT INTO "i" VALUES(1,1); > INSERT INTO "i" VALUES(2,1); > INSERT INTO "i" VALUES(3,1); > INSERT INTO "i" VALUES(4,1); > INSERT INTO "i" VALUES(5,5); > INSERT INTO "i" VALUES(6,6); > INSERT INTO "i" VALUES(7,4); > COMMIT; > sqlite> .dump m > PRAGMA foreign_keys=OFF; > BEGIN TRANSACTION; > CREATE TABLE m (id integer); > INSERT INTO "m" VALUES(1); > INSERT INTO "m" VALUES(2); > INSERT INTO "m" VALUES(3); > INSERT INTO "m" VALUES(4); > INSERT INTO "m" VALUES(5); > INSERT INTO "m" VALUES(6); > INSERT INTO "m" VALUES(7); > COMMIT; > > Simplified example query – in reality there would be a couple of those > inner > SELECT which makes it hard to use JOIN. > > sqlite> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags > DESC; > 6 > 5 > 7 > 1 > 2 > 3 > 4 > > Now I only want the first three values, 6, 5 and 7. However: > > sqlite> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags > DESC > LIMIT 0, 3; > 1 > 2 > 3 > > It almost seems to limit the inner SELECT instead of the outer one. > > Is that a bug in recent versions of sqlite? Or was that a bug before and I > am > doing something wrong? > > Thanks for any insight and maybe a hint how to get what I want > Tobias _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
