Bob Dankert wrote: > I am trying to union two queries with each query having a sub-select > defining the table, but I consistenly get no results if I try to limit > the sub-query. Here is my query, assuming the table a and table > contain one column filled with integers. > > Select * from (select * from a limit 3) as a; <-- this returns 3 > results > Select * from (select * from b limit 3) as b; <-- this returns 3 > results > > Select * from (select * from a limit 3) as a union select * from > (select * from b limit 3) as b; <-- this returns 0 results > > If I get rid of the limits, everything is returned from both tables as > it should be: > Select * from (select * from a) as a union select * from (select * > from b) as b; > > Unfortunately, I need to limit the results in individual queries which > are being unioned together. Since SQLite does not support limiting > individual queries in a union, I was hoping to use sub-selects for the > tables and limit these. Does anyone have any suggestions as to what I > can do for this? > > Thanks, > > Bob Dankert
Bob, It looks like you have found a bug in SQLite. :-) The explain output below shows how SQLite will execute your query. sqlite> explain select * from (select * from t limit 3) union select * from (sel ect * from tt limit 3); addr opcode p1 p2 p3 ---- ------------ ---------- ---------- -------------------------------- --- 0 OpenTemp 0 0 keyinfo(2,BINARY,BINARY) 1 KeyAsData 0 1 2 SetNumColumn 0 2 3 Integer -3 0 4 MemStore 0 1 5 Goto 0 40 6 Integer 0 0 7 OpenRead 2 2 8 SetNumColumn 2 2 9 Rewind 2 17 10 MemIncr 0 17 11 Column 2 0 12 Column 2 1 13 MakeRecord 2 0 14 String8 0 0 15 PutStrKey 0 0 16 Next 2 10 17 Close 2 0 18 Integer -3 0 19 MemStore 1 1 20 Integer 0 0 21 OpenRead 4 3 22 SetNumColumn 4 2 23 Rewind 4 31 24 MemIncr 1 31 25 Column 4 0 26 Column 4 1 27 MakeRecord 2 0 28 String8 0 0 29 PutStrKey 0 0 30 Next 4 24 31 Close 4 0 32 Rewind 0 38 33 MemIncr 1 38 34 Column 0 0 35 Column 0 1 36 Callback 2 0 37 Next 0 33 38 Close 0 0 39 Halt 0 0 40 Transaction 0 0 41 VerifyCookie 0 2 42 Goto 0 6 43 Noop 0 0 sqlite> The problem is at line 33 where it does an increment and test on memory location 1. This test shouldn't be there! It fails immediately because the limit clause on the second sub-select has counted memory location 1 up to zero already (at line 24). So the query generates the correct result set and then skips the loop with the Callback opcode, so it doesn't return any results to you. It looks like SQLite's compiler is getting confused by the limit clauses on the sub-selects and adds a limit test to the outter select when it shouldn't. You should report this bug.
