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 ... > >

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

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

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 > does.

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

2016-10-11 Thread Keith Medcalf
EVERSE_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 UNICODE_STRI

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

2016-10-11 Thread Keith Medcalf
sday, 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 to > build the sq

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

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

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: sqlite-users [mailto:sqlite-users-boun...@mailin

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

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

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

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

2016-10-11 Thread Keith Medcalf
ROM 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

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

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

[sqlite] LIMIT doesn't return expected rows

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