Re: [sqlite] Union all writting on /var/tmp

Also, as far as I'm concerned, if the choice is "more optimizations in SQLite3" or "more work on SQLite4", then put me down for the latter. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Union all writting on /var/tmp

On Mon, Oct 29, 2012 at 8:08 PM, Richard Hipp wrote: > The reason for using a temp table for UNION ALL in a subquery is because > that is the path of least resistance. [...] For what it's worth, UNION ALL is generally understood to be a major optimization over UNION because no

Re: [sqlite] Union all writting on /var/tmp

On Tue, Oct 30, 2012 at 7:39 AM, Eleytherios Stamatogiannakis < est...@gmail.com> wrote: > > Mr. Hipp please excuse my attitude on my first email > All is forgotten. Thank you for bringing this optimization opportunity to our attention! -- D. Richard Hipp d...@sqlite.org

Re: [sqlite] Union all writting on /var/tmp

Hello, Mr. Hipp please excuse my attitude on my first email (the one your replied to). It came after 3 days of intense pressure to find out what the problem of machines coming to a crawl whenever a particular query with sufficiently large union-ed all tables was run. Due to the quality of

Re: [sqlite] Union all writting on /var/tmp

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/

Re: [sqlite] Union all writting on /var/tmp documentation warning

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

Re: [sqlite] Union all writting on /var/tmp documentation warning

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

Re: [sqlite] Union all writting on /var/tmp documentation warning

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

Re: [sqlite] Union all writting on /var/tmp documentation warning

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

Re: [sqlite] Union all writting on /var/tmp documentation warning

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

Re: [sqlite] Union all writting on /var/tmp documentation warning

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

[sqlite] Union all writting on /var/tmp documentation warning

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.

Re: [sqlite] Union all writting on /var/tmp

Thank you Simon. Your solution would work for the example i gave. Nevertheless my problem is still more complex because i also use SQLite as a generic streaming engine (yes i know, SQLite wasn't designed for doing things like that). Appart from input VTs (FILE) we also have output VTs like so

Re: [sqlite] Union all writting on /var/tmp

On 26 Oct 2012, at 10:28pm, Elefterios Stamatogiannakis wrote: > create table t as > select upper(c1), c2, lower(c3) from > ( > select * from file('http://www.foo.com/list1.tsv.gz') > union all > select * from file('http://www.foo.com/list2.tsv.gz') > ) > where c2!=c4; Does

Re: [sqlite] Union all writting on /var/tmp

On 10/26/2012 5:28 PM, Elefterios Stamatogiannakis wrote: The real query in madIS looks like the following: create table t as select upper(c1), c2, lower(c3) from ( select * from file('http://www.foo.com/list1.tsv.gz') union all select * from file('http://www.foo.com/list2.tsv.gz') ) where

Re: [sqlite] Union all writting on /var/tmp

Thank you for answering Mr. Hipp. The general problem doesn't have to do with counting the rows (it was there as a placeholder). I just want to merge 2 (and more) table/streams. The real query in madIS looks like the following: create table t as select upper(c1), c2, lower(c3) from ( select *

Re: [sqlite] Union all writting on /var/tmp

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/

[sqlite] Union all writting on /var/tmp

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