On Fri, Oct 26, 2012 at 2:16 PM, Eleytherios Stamatogiannakis < est...@gmail.com> wrote:
> I have been observing the following freaky behaviour of SQLite. When i run: > > select count(*) from (select * from huge_table union all select * from > huge_table); > > Sqlite starts writting in /var/tmp/ a file like: > > /var/tmp/etilqs_**gblRd6vUPcx91Hl, the root partition of fills up and an > error is raised. > > Why does SQLite 3.7.14.1 need to write at all when doing union all? It > seems to me that there is no reason for doing so. > The only way SQLite knows to evaluate the query is to (1) compute the UNION ALL into a temporary table then (2) scan the temporary table to count the rows. /var/tmp space is used to hold the temporary table. Try instead: SELECT (select count(*) from huge_table)+(select count(*) from huge_table); > > Best regards, > > lefteris. > ______________________________**_________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users> > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users