SQLite version 3.14.2 2016-09-12 18:50:49 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> .read \\test.sql select sqlite_source_id(); 2016-09-12 18:50:49 29dbef4b8585f753861a36d6dd102ca634197bd6 create table i (id integer primary key autoincrement, flags integer); insert into i values (1,1),(2,1),(3,1),(4,1),(5,5),(6,6),(7,4); create table m (id integer); insert into m values (1),(2),(3),(4),(5),(6),(7); SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC; 6 5 7 1 2 3 4 SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC limit 3; 6 5 7
I just compiled 3.14.2 from my source repository and it works correctly. Can you check the result of "select sqlite_source_id();", which should be: 2016-09-12 18:50:49 29dbef4b8585f753861a36d6dd102ca634197bd6 https://www.sqlite.org/src/info/29dbef4b8585f753 > -----Original Message----- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Tobias Ellinghaus > Sent: Tuesday, 11 October, 2016 11:41 > To: sqlite-users@mailinglists.sqlite.org > Subject: Re: [sqlite] LIMIT doesn't return expected rows > > Am Dienstag, 11. Oktober 2016, 06:50:01 CEST schrieb Keith Medcalf: > > This was fixed September 7. The fix appears in 3.14.2 and also on the > > current 3.15.0. > > Does that mean that 3.14.2 is supposed to give the "6, 5, 7" result in the > last query? I am asking as that's the version I am using (installed from > Debian/sid) and I get "1, 2, 3" here. > > > 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 > > Tobias > > [...] _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
