Keith - using your example, I get the same result as Tobias: the second select produces 1,2,3. This is with the sqlite3.exe Windows command line tool for SQLite 3.14.2 downloaded from sqlite.org. Same sqlite_source_id() too. I’m not set up to build SQLite from source, so can’t easily test 3.15.0, but If I do the same with a copy of 3.8.11.1 I have handy I get the correct result: 6, 5, 7.
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; 1 2 3 > On 12/10/2016, at 12:59 PM, Keith Medcalf <kmedc...@dessus.com> wrote: > > 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
