RE: [sqlite] Union queries with sub-select tables with limits returns no results
Thanks for the help, Dennis. I created a bug with ticket number 1035 for anyone interested in following up on this. Thanks! Bob -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Thursday, December 16, 2004 2:01 PM To: [EMAIL PROTECTED] Subject: Re: [sqlite] Union queries with sub-select tables with limits returns no results 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, As a work around you can create temp tables from the two sub-selects and use a union select to combine them, or create a temp table with the output of the first sub-select and then insert the result of the second sub-select into the temp table and then dump that table. create temp table t1 as select * from a limit 3; create temp table t2 as select * from b limit 3; select * from t1 union select * from t2; or create temp table u as select * from a limit 3; insert into u select * from b limit 3; select * from u; I hope this helps.
Re: [sqlite] Union queries with sub-select tables with limits returns no results
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, As a work around you can create temp tables from the two sub-selects and use a union select to combine them, or create a temp table with the output of the first sub-select and then insert the result of the second sub-select into the temp table and then dump that table. create temp table t1 as select * from a limit 3; create temp table t2 as select * from b limit 3; select * from t1 union select * from t2; or create temp table u as select * from a limit 3; insert into u select * from b limit 3; select * from u; I hope this helps.
Re: [sqlite] Union queries with sub-select tables with limits returns no results
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 opcodep1 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 Rewind2 17
10MemIncr 0 17
11Column2 0
12Column2 1
13MakeRecord2 0
14String8 0 0
15PutStrKey 0 0
16Next 2 10
17Close 2 0
18Integer -3 0
19MemStore 1 1
20Integer 0 0
21OpenRead 4 3
22SetNumColumn 4 2
23Rewind4 31
24MemIncr 1 31
25Column4 0
26Column4 1
27MakeRecord2 0
28String8 0 0
29PutStrKey 0 0
30Next 4 24
31Close 4 0
32Rewind0 38
33MemIncr 1 38
34Column0 0
35Column0 1
36Callback 2 0
37Next 0 33
38Close 0 0
39Halt 0 0
40Transaction 0 0
41VerifyCookie 0 2
42Goto 0 6
43Noop 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.
[sqlite] Union queries with sub-select tables with limits returns no results
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 -Original Message- From: George Ionescu [mailto:[EMAIL PROTECTED] Sent: Thursday, December 16, 2004 1:00 AM To: SQLite Forum Subject: Re: [sqlite] db admin tool Hello sten, you could try SQLiteDb Query Analyzer from http://www.terrainformatica.com/sqlitedb (it's included in the SQLiteDb install package). It's not much right now but it's going to be improved alot in the next weeks. Best regards, George Ionescu
