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 reason for using a temp table for UNION ALL in a subquery is because that is the path of least resistance. The same identical code can handle UNION ALL, UNION, INTERSECT, EXCEPT and various other cases. Some queries (such as your UNION ALL) can in fact do without the temp table. But those are special cases that have to coded separately. Adding, testing, and maintaining that extra code involves a lot of work. And the extra code risks introducing bugs that might appear even for people who are not doing a UNION ALL in a subquery. And in over 12 years of use, in over a million different applications, with over 2 billion deployments, nobody has ever before requested this optimization. At http://www.sqlite.org/src/info/7af3acbbd4 there is a patch that adds the optimization to avoid using a temp table for your UNION ALL queries. This one small patch represents about 12 hours of intense work, so far. Much more work will be required to get the patch performing to our release standards. All of this effort on your behalf you are receiving for free. In return, we ask two things: (1) Please download and test the patch and report any problems, including performance problems. (2) Please learn to be less grumpy, demanding, and condescending when requesting help with software towards which you have contributed nothing. You have received this latest patch, and indeed all of SQLite, by grace. Therefore, please extend the same grace toward others. > > 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
