Here my streamlined situation
create table t1 (i integer, d text);
insert into t1 values (0,'aa0');
insert into t1 values (1,'aa1');
insert into t1 values (2,'aa2');
insert into t1 values (3,'aa3');
insert into t1 values (4,'aa4');
insert into t1 values (5,'aa5');
insert into t1 values (6,'aa6');
insert into t1 values (7,'aa7');
insert into t1 values (8,'aa8');
insert into t1 values (9,'aa9');
create table t2 (i integer, e text);
insert into t2 values (0,'aa0');
insert into t2 values (1,'ba1');
insert into t2 values (2,'aa2');
insert into t2 values (3,'ba3');
insert into t2 values (4,'aa4');
insert into t2 values (5,'ba5');
insert into t2 values (6,'aa6');
insert into t2 values (7,'ba7');
insert into t2 values (8,'aa8');
insert into t2 values (9,'ba9');
I then have an outer SELECT whose purpose is to print out the table t1 for a select list of ID (i)
select d from t1 where i in (3,4) limit 4;
d
----
aa3
aa4
The ID set is generated so I can end up with
select d from t1 where i in (3,4,1,6,7) limit 4;
d
----
aa1
aa3
aa4
aa6
Sometimes the ID set is the result of an inner SELECT subquery like this
select d from t1 where i in (select i from t2 where e>'b') limit 4;
d
----
aa1
aa3
aa5
aa7
In my real case t1 and t2 are big and the inner SELECT can generate a big ID list, that the outer select will nuke with its limit constraint.
My question is, does the query optimiser detect this outter limit constrainst and propagate it into the inner select?
If the answer is NO, then I should go the extra mile and my query generator, got to move the limit constraint explicitly in the inner SELECT like this
select d from t1 where i in (select i from t2 where e>'b' limit 4);
d
----
aa1
aa3
aa5
aa7
Before asking I looked EXPLAIN and EXPLAIN QUERY PLAN but this is beyond my knowledge and could not answer from there.
SQLite has no optimization that could move a LIMIT clause into a subquery, and subquery flattening does not apply to IN clauses.
This is confirmed with EXPLAIN (addr 22 is in the outer loop):
sqlite> explain select d from t1 where i in (select i from t2 where e>'b') limit 4; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 26 0 00 Start at 26 1 Integer 4 1 0 00 r[1]=4; LIMIT counter 2 OpenRead 0 2 0 2 00 root=2 iDb=0; t1 3 Rewind 0 24 0 00 4 Noop 0 0 0 00 begin IN expr 5 Once 0 16 0 00 6 OpenEphemeral 3 1 0 k(1,B) 00 nColumn=1 7 OpenRead 1 3 0 2 00 root=3 iDb=0; t2 8 Rewind 1 15 0 00 9 Column 1 1 2 00 r[2]=t2.e 10 Le 3 14 2 (BINARY) 52 if r[3]<=r[2] goto 14 11 Column 1 0 4 00 r[4]=t2.i 12 MakeRecord 4 1 5 C 00 r[5]=mkrec(r[4]) 13 IdxInsert 3 5 0 00 key=r[5] 14 Next 1 9 0 01 15 Close 1 0 0 00 16 Column 0 0 2 00 r[2]=t1.i 17 IsNull 2 23 0 00 if r[2]==NULL goto 23 18 Affinity 2 1 0 C 00 affinity(r[2]) 19 NotFound 3 23 2 1 00 key=r[2]; end IN expr 20 Column 0 1 6 00 r[6]=t1.d 21 ResultRow 6 1 0 00 output=r[6] 22 DecrJumpZero 1 24 0 00 if (--r[1])==0 goto 24 23 Next 0 4 0 01 24 Close 0 0 0 00 25 Halt 0 0 0 00 26 Transaction 0 0 2 0 01 usesStmtJournal=0 27 TableLock 0 2 0 t1 00 iDb=0 root=2 write=0 28 TableLock 0 3 0 t2 00 iDb=0 root=3 write=0 29 String8 0 3 0 b 00 r[3]='b' 30 Goto 0 1 0 00
Please note that LIMIT without an ORDER BY clause is probably not very useful, unless you actually want a random sample of rows.