Re: [sqlite] union + window functions = sqlite crash (version 3.25.2)

On 10/23/2018 03:13 AM, Peter Ďurica wrote: Table with sample data: *create table t(a int, b int);* *insert into t values(1,11);* *insert into t values(2,12);* now query using any window function (row_number, rank, ) after UNION or UNION ALL will cause sqlite.exe crash (no regular error)

Re: [sqlite] union + window functions = sqlite crash (version 3.25.2)

On Monday, 22 October, 2018 14:13, Peter Ďurica wrote: >Table with sample data: >*create table t(a int, b int);* >*insert into t values(1,11);* >*insert into t values(2,12);* ... What is up with the asterisks, they make copying VERY VERY VERY VERY difficult. If you want to put "stars"

Re: [sqlite] union + window functions = sqlite crash (version 3.25.2)

I dug a little more into this with a debug build; was able to get the same crash trace with the slightly smaller query CREATE TABLE t(a); SELECT 1, 1 UNION ALL SELECT a, RANK() OVER (ORDER BY a) FROM t; which fails the pTab!=0 assertion in sqlite3ColumnsFromExprList. It seems

Re: [sqlite] UNION

Thanks, Don. -Original Message- From: don v nielsen Sent: Friday, March 3, 2017 3:21 PM To: SQLite mailing list Subject: Re: [sqlite] UNION Might I suggest: https://www.w3schools.com/sql/ dvn On 03/01/2017 09:02 AM, jose isaias cabrera wrote: Ryan, I just want to thank you

Re: [sqlite] UNION

Might I suggest: https://www.w3schools.com/sql/ dvn On 03/01/2017 09:02 AM, jose isaias cabrera wrote: Ryan, I just want to thank you for your kindness and display of goodwill to mankind. This is a great response. I even learned something from this post. Thanks so much for your responses.

Re: [sqlite] UNION

On 2017/03/01 3:40 AM, do...@mail.com wrote: # SELECT * FROM processors UNION SELECT * FROM storage; Error: SELECTs to the left and right do not have the same number of result columns. All tables that I created in my database have differing column names, values, and amounts of columns with the

Re: [sqlite] UNION

You are literally mixing apples and oranges without creating a superclass "fruit" that contains a field to tell them apart and a set of attributes (=fields) merged from the attributes of the component tables. -Ursprüngliche Nachricht- Von: sqlite-users

Re: [sqlite] UNION

On Tue, Feb 28, 2017 at 5:40 PM, wrote: > # SELECT * FROM processors UNION SELECT * FROM storage; > Error: SELECTs to the left and right do not have the same number of > result columns. > > All tables that I created in my database have differing column names, > values, and

Re: [sqlite] UNION

The error says it. You need to have the same number of columns/fields. For example, this would throw an error if processors had 3 fields and storage had 4. I don't think the names of the fields are important, as it'll use whatever is defined in the first SELECT. On Tue, Feb 28, 2017 at 8:40 PM,

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 patch, preliminary tests

Thanks for running these tests. Clearly I should have made this change a long time ago On Tue, Oct 30, 2012 at 9:31 AM, Eleytherios Stamatogiannakis < est...@gmail.com> wrote: > In my preliminary tests, the patch has worked wonderfully. In these first > tests i have only tested plain SQLite

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

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/

Re: [sqlite] UNION ALL with queries that have a different number ofcolumns

Clay Trychta wrote: > SELECT * FROM( > SELECT * FROM( >SELECT k FROM test_table > ) > UNION ALL > SELECT * FROM( >SELECT k, v FROM test_table > ) > ) > > I would think that unioning two selects which have a different number > of columns would return an error. If I

Re: [sqlite] union-having bug

Gillman, David wrote: > Is this expected behavior? (The failure of my query to return a row.) Your query does not have a well-defined meaning, so no particular behavior is expected from it. -- Igor Tandetnik ___ sqlite-users

Re: [sqlite] union-having bug

Is this expected behavior? (The failure of my query to return a row.) David -Original Message- From: Kit [mailto:kit.sa...@gmail.com] Sent: Saturday, December 03, 2011 5:24 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] union-having bug 2011/12/3 Gillman, David

Re: [sqlite] union-having bug

Gillman, David wrote: > Is this behavior known? The third query returns no rows even though bar = 1. > > sqlite> select ind, sum(foo) foo, sum(bar) bar from (select 1 ind, 0 foo, 1 > bar union select 1 ind, 1 foo, 0 bar) group by ind > having foo > 0; > ind|foo|bar >

Re: [sqlite] union-having bug

Hmm, the mailer changed my output. Here's what I meant: sqlite> select ind, sum(foo) foo, sum(bar) bar from (select 1 ind, 0 foo, 1 bar union select 1 ind, 1 foo, 0 bar) group by ind; ind|foo|bar 1|1|1 sqlite> select ind, sum(foo) foo, sum(bar) bar from (select 1 ind, 0 foo, 1 bar union

Re: [sqlite] union all with limit

On 24 November 2010 11:08, Swithun Crowe wrote: > Hello > > BS> Thanks, I tried that and it gives no error, but only gives the first > BS> lot, not the bit after the union all. > > Ah. I hadn't tried with data. I don't know why the LIMIT affects the UNION > ALL,

Re: [sqlite] union all with limit

Thanks, that one works indeed and will use that. RBS On Wed, Nov 24, 2010 at 11:08 AM, Swithun Crowe wrote: > Hello > > BS> Thanks, I tried that and it gives no error, but only gives the first > BS> lot, not the bit after the union all. > > Ah. I hadn't tried

Re: [sqlite] union all with limit

Hello BS> Thanks, I tried that and it gives no error, but only gives the first BS> lot, not the bit after the union all. Ah. I hadn't tried with data. I don't know why the LIMIT affects the UNION ALL, when it is buried in a subquery. It doesn't affect a UNION (no ALL). In your real data and

Re: [sqlite] union all with limit

Thanks, I tried that and it gives no error, but only gives the first lot, not the bit after the union all. RBS On Wed, Nov 24, 2010 at 10:27 AM, Swithun Crowe wrote: > Hello > > BS> select > BS> patient_id > BS> from > BS> table1 > BS> where > BS> age = 50 > BS>

Re: [sqlite] union all with limit

Hello BS> select BS> patient_id BS> from BS> table1 BS> where BS> age = 50 BS> limit 6 BS> union all BS> select BS> patient_id BS> from BS> table1 BS> where BS> age = 60 BS> limit 4 You might want to wrap the two selects with limits inside subqueries: select patientID from ( select patientID

Re: [sqlite] UNION with results distinct on a particular column?

Two possible solutions: A) pure sql ... (warning: untested) Start with all the possible col1 values, and then left join to the other tables, and pick the first col2 found. select col1, coalesce( A.col2, B.col2, C.col2 ) as col2 from ( select distinct col1 from ( select col1 from A

Re: [sqlite] UNION QUERY

"TW" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I have this query: > > SELECT title, artist, hd, bank, bookmark, genre, class, classnumber > FROM music > WHERE classnumber=6 OR classnumber=7 > AND hd="B" > UNION > SELECT title, artist, hd, bank, bookmark, genre, class,

Re: [sqlite] UNION and ORDER BY errors starting from sqlite 3.4.2

> Not in principle. But I think changes that break backwards > compatibility would be more trouble than they're worth for > something like this. In the absence of clearer guidance > from sql-92, it's probably more important to be compatible > with earlier sqlite versions than with mysql and

Re: [sqlite] UNION and ORDER BY errors starting from sqlite 3.4.2

On Dec 5, 2007, at 1:24 AM, Joe Wilson wrote: --- Dan <[EMAIL PROTECTED]> wrote: The "b" in the ORDER BY does not match "x1.b" because it is not a simple identifier (according to matchOrderbyToColumn()). It does not match either "" or " as ". After failing to find a match for "b" in the

Re: [sqlite] UNION and ORDER BY errors starting from sqlite 3.4.2

--- Dan <[EMAIL PROTECTED]> wrote: > The "b" in the ORDER BY does not match "x1.b" because it is > not a simple identifier (according to matchOrderbyToColumn()). > It does not match either "" or " as ". > > After failing to find a match for "b" in the leftmost SELECT, > SQLite searches the next

Re: [sqlite] UNION and ORDER BY errors starting from sqlite 3.4.2

On Dec 4, 2007, at 10:35 PM, Joe Wilson wrote: --- Dan <[EMAIL PROTECTED]> wrote: i.e., if we have: CREATE TABLE x1(a, b, c); CREATE TABLE x2(a, b, c); then the following pairs of statements are equivalent: ... SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY b;

Re: [sqlite] UNION and ORDER BY errors starting from sqlite 3.4.2

--- Dan <[EMAIL PROTECTED]> wrote: > i.e., if we have: > >CREATE TABLE x1(a, b, c); >CREATE TABLE x2(a, b, c); > > then the following pairs of statements are equivalent: ... > >SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY b; >SELECT x1.b, a FROM x1 UNION SELECT a, b

Re: [sqlite] UNION and ORDER BY errors starting from sqlite 3.4.2

On Dec 4, 2007, at 10:27 AM, Dr Gerard Hammond wrote: I have reported it as a bug - ticket is http://www.sqlite.org/ cvstrac/tktview?tn=2822 It appears as though the /src/select.c (Line1499) changed from: if( iCol<0 && mustComplete ){ to: }else if( mustComplete ){ in version 1.336 of

Re: [sqlite] UNION and ORDER BY errors starting from sqlite 3.4.2

I have reported it as a bug - ticket is http://www.sqlite.org/cvstrac/tktview?tn=2822 It appears as though the /src/select.c (Line1499) changed from: if( iCol<0 && mustComplete ){ to: }else if( mustComplete ){ in version 1.336 of this file -

Re: [sqlite] UNION and ORDER BY errors starting from sqlite 3.4.2

--- Marco Bambini <[EMAIL PROTECTED]> wrote: > Starting from version 3.4.2 I receive errors with queries like: > > SELECT a.field FROM a UNION ALL SELECT b.field FROM b ORDER BY a.field > or even > SELECT a.field FROM a UNION ALL SELECT a.field FROM a ORDER BY a.field > > error is: > ORDER BY

RE: [sqlite] UNION?

Tom, Thanks for taking the time to document for my benefit more efficient implementations. Lee Crain __ -Original Message- From: T [mailto:[EMAIL PROTECTED] Sent: Thursday, August 09, 2007 4:08 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] UNION? Hi Lee

RE: [sqlite] UNION?

sqlite.org Subject: Re: [sqlite] UNION? would have been nice to see the double parentheses in your example ... I have seen MS Access as well being overly parenthesized, what does the SQL92 standard say about that? Jim Lee Crain wrote: > Richard, > > Thanks for suggesting it but no, I do

RE: [sqlite] UNION?

> Also, you may want to consider avoiding performing an IN on a UNION. > As far as I know, SQLite doesn't optimize that, so will build the > entire union before performing the IN. If you instead do the > following, it should be a lot faster (if you have lots of data). But > I may be

Re: [sqlite] UNION?

Hi Lee, This query does not work in SQLite but works in MS SQL Server: sqlite> SELECT items_idx, [name], active FROM Items ...> WHERE active = 'T' AND Items.items_idx IN ...> ((SELECT related_item FROM RelatedItems WHERE item = 1777) ...> UNION ...> (SELECT item FROM RelatedItems

Re: [sqlite] UNION?

PROTECTED] Sent: Wednesday, August 08, 2007 3:38 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] UNION? "Lee Crain" <[EMAIL PROTECTED]> wrote: The query at the bottom of this email is failing on the word "UNION". (The query works correctly in MS SQL Serve

RE: [sqlite] UNION?

__ -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 08, 2007 3:38 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] UNION? "Lee Crain" <[EMAIL PROTECTED]> wrote: > The query at the bottom of this email is fai

RE: [sqlite] UNION?

--- Lee Crain <[EMAIL PROTECTED]> wrote: > I've queried it in both the command line interface and via an > sqlite3_exec() call in a C++ environment. You must have a typo somewhere. SQLite version 3.4.1 Enter ".help" for instructions sqlite> CREATE TABLE Items(items_idx, name, active); sqlite>

RE: [sqlite] UNION?

: Re: [sqlite] UNION? There's nothing wrong with your query. It works fine against this schema in sqlite 3.4.1: CREATE TABLE Items(items_idx, name, active); CREATE TABLE RelatedItems(item, related_item); What language/tool are you using to query sqlite? --- Lee Crain <[EMAIL PROTECTED]>

Re: [sqlite] UNION?

"Lee Crain" <[EMAIL PROTECTED]> wrote: > The query at the bottom of this email is failing on the word "UNION". (The > query works correctly in MS SQL Server.) Works when I try it Do you think you might have some invisible control characters or something in the middle of the SQL? > >

Re: [sqlite] UNION?

There's nothing wrong with your query. It works fine against this schema in sqlite 3.4.1: CREATE TABLE Items(items_idx, name, active); CREATE TABLE RelatedItems(item, related_item); What language/tool are you using to query sqlite? --- Lee Crain <[EMAIL PROTECTED]> wrote: > The query at the

Re: [sqlite] UNION?

http://www.sqlite.org/lang_select.html Lee Crain wrote: The query at the bottom of this email is failing on the word "UNION". (The query works correctly in MS SQL Server.) I believe this is, unfortunately correct, since the SQLite documentation does not mention the reserved word "UNION" in the

RE: [sqlite] Union queries with sub-select tables with limits returns no results

Thanks for the help, Dennis. I created a bug with ticket number 1035 for anyone interested in following up on this. Thanks! Bob -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Thursday, December 16, 2004 2:01 PM To: [EMAIL PROTECTED] Subject: Re: [sqlite] Union

Re: [sqlite] Union queries with sub-select tables with limits returns no results

Bob Dankert wrote: > I am trying to union two queries with each query having a sub-select > defining the table, but I consistenly get no results if I try to limit > the sub-query. Here is my query, assuming the table a and table > contain one column filled with integers. > > Select * from (select

Re: [sqlite] Union queries with sub-select tables with limits returns no results

Bob Dankert wrote: > I am trying to union two queries with each query having a sub-select > defining the table, but I consistenly get no results if I try to limit > the sub-query. Here is my query, assuming the table a and table > contain one column filled with integers. > > Select * from (select