Sorry i didn't realize before that you had:
select * from (... union all ...)
Try with a count(*) as such:
select count(*) from (... union all ...)
And you'll see that both union and "union all" will create a temp file.
Union needs the temp file to remove the duplicates. Union All doesn't
ne
Eleytherios Stamatogiannakis wrote:
> My understanding (and what my experiments have shown) is that in both
> cases "COMPOUND SUBQUERIES 1 AND 2" will write a temp file to /var/tmp.
Okay, let's create a test database ...
$ strace -e trace=open sqlite3 test.db
[...]
sqlite> create table t(x)
My understanding (and what my experiments have shown) is that in both
cases "COMPOUND SUBQUERIES 1 AND 2" will write a temp file to /var/tmp.
IMHO, the documentation should warn about this writing behaviour,
because for the second case (union all) it isn't expected/predictable
because fully bu
Eleytherios Stamatogiannakis wrote:
> "union all" works exactly like plain "union". It always materializes its
> input.
sqlite> explain query plan select 1 union select 2;
sele order from deta
-
0 0 0 COMPOUND SUBQUERIES 1 AND 2 USING
Look at what that page says about "union all" (emphasis on *all*)
--SNIP--
"Note that the UNION ALL operator for compound queries does not use
transient indices by itself (though of course the right and left
subqueries of the UNION ALL might use transient indices depending on how
they are comp
Eleytherios Stamatogiannakis wrote:
> Can a warning about "union all"'s behaviour of buffering everything in
> /var/tmp be added in SQLite's documentation?
Like this? http://www.sqlite.org/tempfiles.html
Regards,
Clemens
___
sqlite-users mailing list
Can a warning about "union all"'s behaviour of buffering everything in
/var/tmp be added in SQLite's documentation?
I think that such a warning could save a lot of time for other SQLite
users that trip over the same thing as i did.
Thank you,
lefteris.
__
7 matches
Mail list logo
