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 buffering is not needed.
lefteris.
On 29/10/12 20:41, Clemens Ladisch wrote:
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 B-TREE (UNION)
sqlite> explain query plan select 1 union all select 2;
sele order from deta
---- ------------- ---- ----
0 0 0 COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)
It does not. (In the full "explain" output, "OpenEphemeral" is missing.)
Neither with real tables.
What particular query behaves unexpectedly for you?
Regards,
Clemens
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
