Re: [sqlite] LIMIT doesn't return expected rows

2016-10-12 Thread Tobias Ellinghaus
Am Mittwoch, 12. Oktober 2016, 12:11:11 CEST schrieb Richard Hipp: > On 10/11/16, Keith Medcalf wrote: > > #define SQLITE_ENABLE_EXPLAIN_COMMENTS 1 > > > > makes it work properly. neither NDEBUG nor SQLITE_DEBUG explicitly > > defined. > > > > Over to Richard ... > > Should now be fixed on tru

Re: [sqlite] LIMIT doesn't return expected rows

2016-10-12 Thread David Empson
Works for me building the sqlite3 command line tool from the prerelease snapshot, on both Mac and Windows. SQLite version 3.15.0 2016-10-12 15:15:30 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> .read t

Re: [sqlite] LIMIT doesn't return expected rows

2016-10-12 Thread Richard Hipp
On 10/11/16, Keith Medcalf wrote: > > #define SQLITE_ENABLE_EXPLAIN_COMMENTS 1 > > makes it work properly. neither NDEBUG nor SQLITE_DEBUG explicitly defined. > > Over to Richard ... Should now be fixed on trunk and in the latest Prerelease Snapshot at https://sqlite.org/download.html -- D. Ri

Re: [sqlite] LIMIT doesn't return expected rows

2016-10-11 Thread Keith Medcalf
Tuesday, 11 October, 2016 19:22 > To: SQLite mailing list > Subject: Re: [sqlite] LIMIT doesn't return expected rows > > > By default, I do not see the query being flattened. Flattening it > manually produces the same right results independant of something config.h > doe

Re: [sqlite] LIMIT doesn't return expected rows

2016-10-11 Thread Keith Medcalf
define SQLITE_REVERSE_UNORDERED_SELECTS 1 > // #define SQLITE_USE_FCNTL_TRACE 1// Enable > extra vfslog fcntrl trace > // #define SQLITE_YYTRACKMAXSTACKDEPTH 1 > > #if defined(_WIN32) && defined(__GNUC__) > #define UNICODE_STRING_MAX_BYTES ((WORD) 65534) > #define U

Re: [sqlite] LIMIT doesn't return expected rows

2016-10-11 Thread Keith Medcalf
> Sent: Tuesday, 11 October, 2016 18:41 > To: SQLite mailing list > Subject: Re: [sqlite] LIMIT doesn't return expected rows > > 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

Re: [sqlite] LIMIT doesn't return expected rows

2016-10-11 Thread David Empson
k 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: sq

Re: [sqlite] LIMIT doesn't return expected rows

2016-10-11 Thread David Empson
ite_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] >>

Re: [sqlite] LIMIT doesn't return expected rows

2016-10-11 Thread Keith Medcalf
53861a36d6dd102ca634197bd6 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.

Re: [sqlite] LIMIT doesn't return expected rows

2016-10-11 Thread Dan Kennedy
On 10/11/2016 07:57 PM, Simon Slavin wrote: On 11 Oct 2016, at 1:44pm, Eric Minbiole wrote: Your problem is that although SQL accepts the clauses written in the order you wrote them in, the LIMIT clause is processed before the ORDER BY clause. Is that really true? I had always thought that th

Re: [sqlite] LIMIT doesn't return expected rows

2016-10-11 Thread Tobias Ellinghaus
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 (insta

Re: [sqlite] LIMIT doesn't return expected rows

2016-10-11 Thread Simon Slavin
On 11 Oct 2016, at 1:44pm, Eric Minbiole wrote: >> Your problem is that although SQL accepts the clauses written in the order >> you wrote them in, the LIMIT clause is processed before the ORDER BY clause. > > Is that really true? I had always thought that the ORDER BY was processed > first, th

Re: [sqlite] LIMIT doesn't return expected rows

2016-10-11 Thread Keith Medcalf
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/0c4df

Re: [sqlite] LIMIT doesn't return expected rows

2016-10-11 Thread Eric Minbiole
> Your problem is that although SQL accepts the clauses written in the order > you wrote them in, the LIMIT clause is processed before the ORDER BY clause. > > Is that really true? I had always thought that the ORDER BY was processed first, though I admit I don't see anything authoritative either w

Re: [sqlite] LIMIT doesn't return expected rows

2016-10-11 Thread Simon Slavin
On 11 Oct 2016, at 11:52am, Tobias Ellinghaus wrote: > 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 Your problem is that although SQL accepts the clauses written in the or