Following up: same for the Mac distribution of 3.14.2 command line tool. Using the pre-release snapshot of 3.15.0 from the main download page to build the sqlite3 3.15.0 on the Mac, I get the same answer: 1,2,3.
SQLite version 3.15.0 2016-10-10 14:34:00 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-10-10 14:34:00 aebe429e52ffef026cb0803fb164339d61bd2e88 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 1:23 PM, David Empson <demp...@emptech.co.nz> wrote: > > 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 -- David Empson demp...@emptech.co.nz Snail mail: P.O. Box 27-103, Wellington 6141, New Zealand _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
