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

[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 trunk and in the latest Prerelease Snapshot at
> https://sqlite.org/download.html

Thank you very much for the quick help. Much appreciated. :-)

Tobias


signature.asc
Description: This is a digitally signed message part.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 (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

[...]

signature.asc
Description: This is a digitally signed message part.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] LIMIT doesn't return expected rows

[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 I need to get a certain subset of the result the query 
gives. However, that somehow doesn't work with sqlite 3.14.2. In 3.8.5 it 
still worked according to someone on IRC testing it.

Example database:

sqlite> .dump i
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE i (id integer primary key autoincrement, flags integer);
INSERT INTO "i" VALUES(1,1);
INSERT INTO "i" VALUES(2,1);
INSERT INTO "i" VALUES(3,1);
INSERT INTO "i" VALUES(4,1);
INSERT INTO "i" VALUES(5,5);
INSERT INTO "i" VALUES(6,6);
INSERT INTO "i" VALUES(7,4);
COMMIT;
sqlite> .dump m
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE m (id integer);
INSERT INTO "m" VALUES(1);
INSERT INTO "m" VALUES(2);
INSERT INTO "m" VALUES(3);
INSERT INTO "m" VALUES(4);
INSERT INTO "m" VALUES(5);
INSERT INTO "m" VALUES(6);
INSERT INTO "m" VALUES(7);
COMMIT;

Simplified example query – in reality there would be a couple of those inner 
SELECT which makes it hard to use JOIN.

sqlite> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC;
6
5
7
1
2
3
4

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

It almost seems to limit the inner SELECT instead of the outer one.

Is that a bug in recent versions of sqlite? Or was that a bug before and I am 
doing something wrong?

Thanks for any insight and maybe a hint how to get what I want
Tobias

signature.asc
Description: This is a digitally signed message part.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users