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.
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
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
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 TEMP
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
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);
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
