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

[Dan Kennedy]

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)

for example:
*select a, rank() over(order by b) from t *
*union all *
*select a, rank() over(order by b desc) from t;*
WILL CRASH

but single statement is fine

*select a, rank() over(order by b desc) from t;  *

and also window function just before union is fine
*select a, rank() over(order by b) from t *
*union all *
*select a, b from t;*

when I used e_sqlite3.dll (https://github.com/ericsink/SQLitePCL.raw) from
my c# app, I got error below
Exception thrown at 0x7FFF563BF797 (e_sqlite3.dll) in WebLES.exe:
0xC005: Access violation reading location 0x0008.

I believe it's a bug in core sqlite, which should be fixed



It is indeed, thanks for reporting it. Should now be fixed here:

  https://www.sqlite.org/src/info/059ff53a46c7f1e4

Dan.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Keith Medcalf]

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" around your text, how about you do it before the 
beginning and after the end so that the script commands remain a script 
command, as in:

*
create table t(a int, b int);
insert into t values(1,11);
insert into t values(2,12);
*

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[David Yip]

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 like you need to have a window function and a column from t in
the second SELECT to trigger the error; e.g. having 1, RANK()... there
won't do it.

- David

On Monday, October 22, 2018 3:13:12 PM CDT 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)
> 
> for example:
> *select a, rank() over(order by b) from t *
> *union all *
> *select a, rank() over(order by b desc) from t;*
> WILL CRASH
> 
> but single statement is fine
> 
> *select a, rank() over(order by b desc) from t;  *
> 
> and also window function just before union is fine
> *select a, rank() over(order by b) from t *
> *union all *
> *select a, b from t;*
> 
> 
> when I used e_sqlite3.dll (https://github.com/ericsink/SQLitePCL.raw) from
> my c# app, I got error below
> Exception thrown at 0x7FFF563BF797 (e_sqlite3.dll) in WebLES.exe:
> 0xC005: Access violation reading location 0x0008.
> 
> I believe it's a bug in core sqlite, which should be fixed
> 
> Please let me know how it goes
> 
> Thanks in advacne,
> Peter Ďurica
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UNION

[jose isaias cabrera]

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 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.  There are others like us in
this group that love to learn and your posts always are well received.
Thanks.  In Spanish we say, "muchas gracias."

josé

On 2017-03-01 09:51, R Smith wrote:


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 noted exception of the one
column which is common (board). I've no idea what to do now.

Why is this an error?
I think that perhaps you are new to SQL and other replies assumed you 
simply wanted what you wrote. I could be wrong, but just in case, here 
are my suggestions:


Perhaps what you would rather like to do is JOIN these tables and not 
UNION them?


Do you wish to match /every/ processor with /every/ board?
In this case, the statement should read: (Warning: this could produced 
excessively long listings)

SELECT * FROM processors, storage;

Do you wish to match only processors and storage that fit on the same 
boards?

In this case the statement might read something like:
SELECT *
FROM processors
JOIN storage USING board
;

Do you wish to list /all/ processors and add the storage options for the 
same board /if/ there are any?

In this case the statement might read something like:
SELECT *
FROM processors
LEFT JOIN storage ON storage.board = processors.board
;

As you can see, lots of different things can be achieved. A quick course 
in SQL via perhaps W3Schools will teach all these in a few hours.


Cheers,
Ryan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users 


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UNION

[don v nielsen]

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.  There are others like us in
this group that love to learn and your posts always are well received.
Thanks.  In Spanish we say, "muchas gracias."

josé

On 2017-03-01 09:51, R Smith wrote:


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 noted exception of the one
column which is common (board). I've no idea what to do now.

Why is this an error?

I think that perhaps you are new to SQL and other replies assumed you simply 
wanted what you wrote. I could be wrong, but just in case, here are my 
suggestions:

Perhaps what you would rather like to do is JOIN these tables and not UNION 
them?

Do you wish to match /every/ processor with /every/ board?
In this case, the statement should read: (Warning: this could produced 
excessively long listings)
SELECT * FROM processors, storage;

Do you wish to match only processors and storage that fit on the same boards?
In this case the statement might read something like:
SELECT *
FROM processors
JOIN storage USING board
;

Do you wish to list /all/ processors and add the storage options for the same 
board /if/ there are any?
In this case the statement might read something like:
SELECT *
FROM processors
LEFT JOIN storage ON storage.board = processors.board
;

As you can see, lots of different things can be achieved. A quick course in SQL 
via perhaps W3Schools will teach all these in a few hours.

Cheers,
Ryan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UNION

[R Smith]

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 noted exception of the one
column which is common (board). I've no idea what to do now.

Why is this an error?


I think that perhaps you are new to SQL and other replies assumed you 
simply wanted what you wrote. I could be wrong, but just in case, here 
are my suggestions:


Perhaps what you would rather like to do is JOIN these tables and not 
UNION them?


Do you wish to match /every/ processor with /every/ board?
In this case, the statement should read: (Warning: this could produced 
excessively long listings)

SELECT * FROM processors, storage;

Do you wish to match only processors and storage that fit on the same 
boards?

In this case the statement might read something like:
SELECT *
FROM processors
  JOIN storage USING board
;

Do you wish to list /all/ processors and add the storage options for the 
same board /if/ there are any?

In this case the statement might read something like:
SELECT *
  FROM processors
  LEFT JOIN storage ON storage.board = processors.board
;

As you can see, lots of different things can be achieved. A quick course 
in SQL via perhaps W3Schools will teach all these in a few hours.


Cheers,
Ryan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UNION

[Hick Gunter]

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 [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von do...@mail.com
Gesendet: Mittwoch, 01. März 2017 02:40
An: sqlite-users@mailinglists.sqlite.org
Betreff: [sqlite] UNION

# 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 noted exception of the one column which is 
common (board). I've no idea what to do now.

Why is this an error?

Thank you,
David
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UNION

[J Decker]

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 amounts of columns with the noted exception of the one
> column which is common (board). I've no idea what to do now.
>
> Why is this an error?
>

1   6770
2  3770

1  samsung 960   250G
2  seagate5T
3

what should it use for fields, if the query first encounters 2 columns, and
starts allocating rows for it, then runs into data with 3, then it shouln't
just drop some arbitrary data, so it throws an error.
If you want them really to merge... on the first select *,"nothing" from X
untion select * from Y
(I think sqlite allows * as first or last argument)
or sepcifically name some number from X and the same number from Y and
it'll put them together
Whether that actually has any meaning is up to the application





>
> Thank you,
> David
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UNION

[Stephen Chrzanowski]

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,  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 noted exception of the one
> column which is common (board). I've no idea what to do now.
>
> Why is this an error?
>
> Thank you,
> David
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Nico Williams]

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

[Nico Williams]

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 temp table should be required to
implement UNION ALL;.  Also, IIUC, INTERSECT and EXCEPT could also be
run without temp tables by converting them to slightly different
queries: SELECT ... FROM source1 ... WHERE EXISTS (SELECT ... FROM
source2 ...).  And UNION ALL queries could be re-written into a UNION
where the second query is an EXCEPT query (all the rows in the second
that are not in the first).  INTERSECT and EXCEPT are not used very
frequently, but UNION ALL is used quite frequently, and probably much
more so than UNION: UNION ALL is the logical operator for building
VIEWs to implement a SQL equivalent of virtual classes, with each
virtual class implemented as the UNION ALL of several queries.  It
would be quite worthwhile to have UNION ALL not use a temp table.

Incidentally, there are a lot of possible optimizations where a query
could be re-written differently where the optimizer could be external
to SQLite, using a parser that objectifies SQL, an analyzer, and a
re-writer that outputs SQL to be parsed by SQLite.  Optimizations like
GCSE, OR -> UNION, INTERSET/EXECPT -> SELECT ... WHERE EXISTS / NOT
EXISTS, and others.  But UNION ALL cannot be so re-written, and that,
I think, is a good argument for having this optimization in SQLite
while not necessarily having the others.  I've been tempted before to
write at least the objectifying parser for this.

> 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:

We all appreciate this very much.  It is a lot of work, I know.

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Union all patch, preliminary tests

[Richard Hipp]

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 functionality (without virtual table
> or additional functions) on a 1.5GB DB. Some remarks:
>
> - It is lightning fast. For queries that fit to the free space (some of
> them could not finish before due to space constraints) and previously took
> minutes to finish, with the patch they are instantaneous and they do not
> touch the drive. The results are the same.
>
> - I was expecting that only simple scans over unioned queries would be
> affected by the patch. When i tried to do a filter on a unioned table
> composition, and the query was again instantaneous, i was flabbergasted.
> The patch really pushes index accesses downwards into the unioned tables. I
> didn't expect the patch to go that far. I'm amazed,
>
> - Group by on unioned all tables was also instantaneous without filling
> the hard disk.
>
> - Oddly, order by works the same both with the patch and without the patch
> (SQLite 3.7.14.1). I assume that order by had a similar optimization in
> previous versions of SQLite too?
>
> - On all of the above tests, the free space on the hard disk wasn't
> affected by the running query.
>
> More tests will be done in the context of madIS, but they will take more
> time. I want to test plain SQLite first, because this is what most people
> will use in practise.
>
> Best regards,
>
> lefteris.
>
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> 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

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

[Richard Hipp]

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
___
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

[Eleytherios Stamatogiannakis]

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 SQLite it literally was the last thing that i 
looked at. I even had prepared emails to other people asking them to 
look at their software, and to not to buffer on the hard disk so much.


Concerning your effort to fix it. I also suspected about the "path of 
least resistance", considering "union all". This is why in my next 
emails i mainly asked for a switch to change the "/var/tmp" path. The 
other thing that i've asked for, was for the documentation to have a 
warning about union all's behaviour. I believed that warning could save 
a lot of time and effort for other people that tripped on the same thing 
as me.


Thank you very very much for your fix. I'm glad that you put the 
considerable effort to it. My Phd was in databases, so i can understand 
how much effort this fix required. I have already downloaded the patch 
and i'll test it asap.


Due to me also working/having worked on Open Source software (madIS, 
Rigs of Rods), i realize your second point in my skin. Nevertheless 
sometimes, i also slide to this kind of behaviour. I'll try to be on 
guard against it in the future.


Best regards,

lefteris.

On 30/10/12 03:08, Richard Hipp wrote:



On Fri, Oct 26, 2012 at 2:16 PM, Eleytherios Stamatogiannakis
> 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





--
D. Richard Hipp
d...@sqlite.org 


___
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

[Richard Hipp]

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
>



-- 
D. Richard Hipp
d...@sqlite.org
___
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 documentation warning

[Elefterios Stamatogiannakis]

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 
need it at all.


l.

On 29/10/2012 10:37 μμ, Clemens Ladisch wrote:

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 t(x);
   open("/tmp/test.db-journal", O_RDWR|O_CREAT|O_CLOEXEC, 0644) = 4
   open("/dev/urandom", O_RDONLY|O_CLOEXEC) = 5
   open("/tmp", O_RDONLY|O_CLOEXEC)= 5
   sqlite> begin;
   sqlite> insert into t values('long string');
   open("/tmp/test.db-journal", O_RDWR|O_CREAT|O_CLOEXEC, 0644) = 4
   sqlite> insert into t select * from t;
   sqlite> insert into t select * from t;
   sqlite> insert into t select * from t;

... that becomes so big that the subquery overflows the cache:

   [...]
   sqlite> insert into t select * from t;
   open("/var/tmp/etilqs_Oekg82a6826YGdz", 
O_RDWR|O_CREAT|O_EXCL|O_NOFOLLOW|O_CLOEXEC, 0600) = 5
   sqlite> commit;

Only the UNION query claims to use temporary storage for the subquery:
(These two queries are constructed so that they have no result records.)

   sqlite> explain query plan select * from (select rowid, x from t union all 
select rowid, x from t) where x = '';
   1|0|0|SCAN TABLE t (~10 rows)
   2|0|0|SCAN TABLE t (~10 rows)
   0|0|0|COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)
   sqlite> explain query plan select * from (select rowid, x from t union 
select rowid, x from t) where x = '';
   2|0|0|SCAN TABLE t (~100 rows)
   3|0|0|SCAN TABLE t (~100 rows)
   1|0|0|COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)
   0|0|0|SCAN SUBQUERY 1 (~20 rows)

When executing them, only UNION uses temporary files:

   sqlite> select * from (select rowid, x from t union all select rowid, x from 
t) where x = '';
   sqlite> select * from (select rowid, x from t union select rowid, x from t) 
where x = '';
   open("/var/tmp/etilqs_QNvTpzSHSedfFFM", 
O_RDWR|O_CREAT|O_EXCL|O_NOFOLLOW|O_CLOEXEC, 0600) = 4
   open("/var/tmp/etilqs_RiTrAL6vrIxpnOu", 
O_RDWR|O_CREAT|O_EXCL|O_NOFOLLOW|O_CLOEXEC, 0600) = 5


Now, what UNION ALL query wants to use a temporary table?
I'd guess that SQLite needs to save the result for some other reasons.


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

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

[Clemens Ladisch]

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 t(x);
  open("/tmp/test.db-journal", O_RDWR|O_CREAT|O_CLOEXEC, 0644) = 4
  open("/dev/urandom", O_RDONLY|O_CLOEXEC) = 5
  open("/tmp", O_RDONLY|O_CLOEXEC)= 5
  sqlite> begin;
  sqlite> insert into t values('long string');
  open("/tmp/test.db-journal", O_RDWR|O_CREAT|O_CLOEXEC, 0644) = 4
  sqlite> insert into t select * from t;
  sqlite> insert into t select * from t;
  sqlite> insert into t select * from t;

... that becomes so big that the subquery overflows the cache:

  [...]
  sqlite> insert into t select * from t;
  open("/var/tmp/etilqs_Oekg82a6826YGdz", 
O_RDWR|O_CREAT|O_EXCL|O_NOFOLLOW|O_CLOEXEC, 0600) = 5
  sqlite> commit;

Only the UNION query claims to use temporary storage for the subquery:
(These two queries are constructed so that they have no result records.)

  sqlite> explain query plan select * from (select rowid, x from t union all 
select rowid, x from t) where x = '';
  1|0|0|SCAN TABLE t (~10 rows)
  2|0|0|SCAN TABLE t (~10 rows)
  0|0|0|COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)
  sqlite> explain query plan select * from (select rowid, x from t union select 
rowid, x from t) where x = '';
  2|0|0|SCAN TABLE t (~100 rows)
  3|0|0|SCAN TABLE t (~100 rows)
  1|0|0|COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)
  0|0|0|SCAN SUBQUERY 1 (~20 rows)

When executing them, only UNION uses temporary files:

  sqlite> select * from (select rowid, x from t union all select rowid, x from 
t) where x = '';
  sqlite> select * from (select rowid, x from t union select rowid, x from t) 
where x = '';
  open("/var/tmp/etilqs_QNvTpzSHSedfFFM", 
O_RDWR|O_CREAT|O_EXCL|O_NOFOLLOW|O_CLOEXEC, 0600) = 4
  open("/var/tmp/etilqs_RiTrAL6vrIxpnOu", 
O_RDWR|O_CREAT|O_EXCL|O_NOFOLLOW|O_CLOEXEC, 0600) = 5


Now, what UNION ALL query wants to use a temporary table?
I'd guess that SQLite needs to save the result for some other reasons.


Regards,
Clemens
___
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 documentation warning

[Eleytherios Stamatogiannakis]

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

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

[Clemens Ladisch]

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

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

[Eleytherios Stamatogiannakis]

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 composed.)"

--/SNIP--

At least to my eyes, above says what i was expecting before realizing 
what actually happens, that "union all" tries to not materialize its 
results when possible.


What the truth is, concerning materialization, is that in SQLite "union 
all" works exactly like plain "union". It always materializes its input.


lefteris.

On 29/10/12 16:37, Clemens Ladisch wrote:

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 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

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

[Clemens Ladisch]

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 list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Elefterios Stamatogiannakis]

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 [*]:

OUTPUT '10.0.0.1:8080' 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;

Internally this is implemented as a VT (OUTPUT) that takes as input a 
query and sends its results to another SQLite. The beauty of the concept 
is that a streaming query flow can go from machine to machine and still 
the transactional properties hold. If anything happens anywhere in the 
query's path, all of the machines will rollback due to the transactional 
properties of SQLite.


In addition to above we are developing a parallel processing engine 
(like Hadoop) where we use SQLite "chunks" for processing and data 
storage. So tables are split (or collected) into multiple SQLite DBs and 
transferred to multiple machines, where the queries will be executed in 
parallel [**].


For above we heavilly use UNION ALLs over the attached DBs to scan over 
the many chunks of a table.


A UNION ALL that unnecessarily buffers to the disk its inputs (for a 
single scan over them), is very nasty, performance wise, to all of the 
above machinations.


I can guess that changing UNION ALL processing for single scans, isn't 
so easy, nevertheless it would be nice if the /var/tmp buffering 
directory could be at least changed.


lefteris.

[*] In practise we pipe to named pipes that point to other machines.

[**] Other projects based on the same idea are:

http://hadoopdb.sourceforge.net/

and the company that sprang from above:

http://hadapt.com/


On 27/10/2012 3:05 πμ, Simon Slavin wrote:


Does this work instead ?

CREATE TABLE t ([[whatever columns you want]]);
INSERT INTO t SELECT upper(c1),c2,lower(c3) FROM 
file('http://www.foo.com/list1.tsv.gz') WHERE c2!=c4;
INSERT INTO t SELECT upper(c1),c2,lower(c3) FROM 
file('http://www.foo.com/list2.tsv.gz') WHERE c2!=c4;

Simon.
___
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

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

[Simon Slavin]

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 this work instead ?

CREATE TABLE t ([[whatever columns you want]]);
INSERT INTO t SELECT upper(c1),c2,lower(c3) FROM 
file('http://www.foo.com/list1.tsv.gz') WHERE c2!=c4;
INSERT INTO t SELECT upper(c1),c2,lower(c3) FROM 
file('http://www.foo.com/list2.tsv.gz') WHERE c2!=c4;

Simon.
___
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

[Igor Tandetnik]

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 c2!=c4;


Perhaps something like this instead:

create table t as
select upper(c1), c2, lower(c3)
from file('http://www.foo.com/list1.tsv.gz')
where c2!=c4;

insert into t
select upper(c1), c2, lower(c3)
from file('http://www.foo.com/list2.tsv.gz')
where c2!=c4;


Could the /var/tmp position that it writes to, be changed to another directory?


http://sqlite.org/c3ref/temp_directory.html

--
Igor Tandetnik

___
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

[Elefterios Stamatogiannakis]

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 * from file('http://www.foo.com/list1.tsv.gz')
union all
select * from file('http://www.foo.com/list2.tsv.gz')
)
where c2!=c4;

"list1.tsv.gz" and "list2.tsv.gz" are two enormous streams which i would 
like to process and put into table "t".


I have gone to great lengths to make the virtual table "file" being 
fully streamed. So both of the network files (list1, list2) arrive from 
the network packet by packet, get decompressed without touching the disk 
and then are broken into multiple columns ('tsv' is assumed to mean tab 
separated).


I admire SQLite very much for its predictability. So for the above query 
i would expect from it to scan first over the first file (list1) and 
then over the next (list2), and row by row put them in table "t".


This assumption was so strong that i've been searching all over the 
"file" VT code for a *very* long time to find out the bug in it that 
caused it to grid to a halt my system whenever i executed above query ( 
/ partition was filled ).


I have a request. If "union all" cannot be changed to not write on the 
hard disk when scanning just once over tables/streams. Could the 
/var/tmp position that it writes to, be changed to another directory? I 
prefer to keep a small root (/) partition and right now i'm unable to do 
any "union all" on anything that is bigger than the free space on it.


Thank you again,

lefteris.

On 26/10/2012 9:23 μμ, Richard Hipp wrote:



On Fri, Oct 26, 2012 at 2:16 PM, Eleytherios Stamatogiannakis
> 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





--
D. Richard Hipp
d...@sqlite.org 


___
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

[Richard Hipp]

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
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Igor Tandetnik]

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 remove the outermost SELECT *
> then I receive the error I'm expecting: SELECTs to the left and right
> of UNION ALL do not have the same number of result columns.

Looks like a bug to me, for what it's worth.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] union-having bug

[Igor Tandetnik]

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 mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] union-having bug

[Gillman, David]

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 <dgill...@akamai.com>:
> 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 
> sqlite> foo, 1 bar union select 1 ind, 1 foo, 0 bar) group by ind 
> sqlite> having bar > 0;

select ind, sum(foo) fooo, sum(bar) barr from (select 1 ind, 0 foo, 1 bar union 
select 1 ind, 1 foo, 0 bar) group by ind having barr >0;
1|1|1
--
Kit
___
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

Re: [sqlite] union-having bug

[Igor Tandetnik]

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
> 1|1|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 bar > 0; 

Your condition involves an expression that neither appears in GROUP BY clause, 
nor uses an aggregate function. The value of foo (in the first query) and bar 
(in the second) will come from some row in the group - it is undefined which 
one. Purely by accident, SQLite chose the row that satisfies the condition in 
the first case, and one that doesn't in the second.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] union-having bug

[Gillman, David]

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 select 1 ind, 1 foo, 0 bar) group by ind having foo > 0;
ind|foo|bar
1|1|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 bar > 0;

sqlite> select * from (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) where bar > 0;
ind|foo|bar
1|1|1

sqlite>

-Original Message-
From: Gillman, David [mailto:dgill...@akamai.com] 
Sent: Friday, December 02, 2011 6:37 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] union-having bug

Hi,

Is this behavior known?  The third query returns no rows even though bar = 1.

sqlite> select region, sum(edge) edge, sum(infra) infra from (select 1 region, 
0 edge, 1 infra union select 1 region, 1 edge, 0 infra) group by 
region;region|edge|infra
1|1|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;
ind|foo|bar
1|1|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
1|1|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 bar > 0;
sqlite> select * from (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) where bar > 0;
ind|foo|bar
1|1|1
sqlite>

___
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

Re: [sqlite] union all with limit

[Simon Davies]

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, when it is buried in a subquery.

It doesn't in version 3.7.0.1

sqlite> select sqlite_version();
3.7.0.1
sqlite>
sqlite> create table table1 (patientID int, age int);
sqlite> insert into table1 (patientID, age) values (1, 50);
sqlite> insert into table1 (patientID, age) values (2, 50);
sqlite> insert into table1 (patientID, age) values (3, 50);
sqlite> insert into table1 (patientID, age) values (4, 50);
sqlite> insert into table1 (patientID, age) values (5, 50);
sqlite> insert into table1 (patientID, age) values (6, 50);
sqlite> insert into table1 (patientID, age) values (7, 50);
sqlite> insert into table1 (patientID, age) values (8, 60);
sqlite> insert into table1 (patientID, age) values (9, 60);
sqlite> insert into table1 (patientID, age) values (10, 60);
sqlite> insert into table1 (patientID, age) values (11, 60);
sqlite> insert into table1 (patientID, age) values (12, 60);
sqlite>
sqlite> select patientID
   ...> from (
   ...>  select patientID
   ...>  from table1
   ...>  where age = 50
   ...>  limit 6
   ...> )
   ...> union all
   ...> select patientID
   ...> from (
   ...>  select patientID
   ...>  from table1
   ...>  where age = 60
   ...>  limit 4
   ...> );
1
2
3
4
5
6
8
9
10
11
sqlite>

>
> Swithun.

Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] union all with limit

[Bart Smissaert]

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 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 query, can a patient have more than one age? If not,
> then UNION and UNION ALL would produce the same results.
>
> sqlite> create table table1 (patientID int, age int);
> sqlite> insert into table1 (patientID, age) values (1, 50);
> sqlite> insert into table1 (patientID, age) values (2, 50);
> sqlite> insert into table1 (patientID, age) values (3, 50);
> sqlite> insert into table1 (patientID, age) values (4, 50);
> sqlite> insert into table1 (patientID, age) values (5, 50);
> sqlite> insert into table1 (patientID, age) values (6, 50);
> sqlite> insert into table1 (patientID, age) values (7, 50);
> sqlite> insert into table1 (patientID, age) values (8, 60);
> sqlite> insert into table1 (patientID, age) values (9, 60);
> sqlite> insert into table1 (patientID, age) values (10, 60);
> sqlite> insert into table1 (patientID, age) values (11, 60);
> sqlite> insert into table1 (patientID, age) values (12, 60);
>
> This seems to work:
>
> select patientID
> from table1
> where patientID in (select patientID from table1 where age=50 limit 6)
> union all
> select patientID
> from table1
> where patientID in(select patientID from table1 where age=60 limit 4);
>
> 1
> 2
> 3
> 4
> 5
> 6
> 8
> 9
> 10
> 11
>
> Swithun.
> ___
> 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

Re: [sqlite] union all with limit

[Swithun Crowe]

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 query, can a patient have more than one age? If not, 
then UNION and UNION ALL would produce the same results.

sqlite> create table table1 (patientID int, age int);
sqlite> insert into table1 (patientID, age) values (1, 50);
sqlite> insert into table1 (patientID, age) values (2, 50);
sqlite> insert into table1 (patientID, age) values (3, 50);
sqlite> insert into table1 (patientID, age) values (4, 50);
sqlite> insert into table1 (patientID, age) values (5, 50);
sqlite> insert into table1 (patientID, age) values (6, 50);
sqlite> insert into table1 (patientID, age) values (7, 50);
sqlite> insert into table1 (patientID, age) values (8, 60);
sqlite> insert into table1 (patientID, age) values (9, 60);
sqlite> insert into table1 (patientID, age) values (10, 60);
sqlite> insert into table1 (patientID, age) values (11, 60);
sqlite> insert into table1 (patientID, age) values (12, 60);

This seems to work:

select patientID 
from table1 
where patientID in (select patientID from table1 where age=50 limit 6) 
union all 
select patientID 
from table1 
where patientID in(select patientID from table1 where age=60 limit 4);

1
2
3
4
5
6
8
9
10
11

Swithun.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] union all with limit

[Bart Smissaert]

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> 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
>  from table1
>  where age = 50
>  limit 6
> )
> union all
> select patientID
> from (
>  select patientID
>  from table1
>  where age = 60
>  limit 4
> );
>
> Swithun.
> ___
> 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

Re: [sqlite] union all with limit

[Swithun Crowe]

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 
  from table1 
  where age = 50 
  limit 6
) 
union all 
select patientID 
from (
  select patientID 
  from table1 
  where age = 60 
  limit 4
);

Swithun.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[David Westbrook]

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
union
select col1 from B
union
select col1 from C
  ) as u
) as tmp
left join A using (col1)
left join B using (col1)
left join C using (col1)
order by col1
;

B) This is potentially very easy at the application level ... here's a
perl/DBI example (also untested):
my %pairs = map {
  %{ $dbh->selectall_hashref("select col1, col2 from $_", 'col1') }
} reverse qw/ A B C /;
# This next line is optional, if you want the hash values to be col2's
instead of hashrefs:
$_=$_->{col2} for values %pairs;

--david

On Tue, Mar 24, 2009 at 5:36 PM, Matthew L. Creech  wrote:
> Hi,
>
> I'm hoping someone here can help me out with a query.  I have multiple
> tables, each with the same schema.  For example:
>
> =
> Table A:
> =
> 1|"xxx"
> 2|"yyy"
> 3|"zzz"
> =
>
> =
> Table B:
> =
> 1|"xxx222"
> 3|"zzz222"
> 5|"www"
> =
>
> I'd like a SELECT statement that yields:
>
> =
> Result:
> =
> 1|"xxx"
> 2|"yyy"
> 3|"zzz"
> 5|"www"
> =
>
> In other words, I want the UNION of all the input tables, but if there
> are multiple results that have the same value in the first column, the
> first table's value should take precedence.
>
> This seems like a common scenario, so I'm probably missing something
> trivial.  :)  But so far, the only way I've figured out to do this is
> with something like:
>
> SELECT * FROM
> (SELECT 1 AS precedence, col1, col2 FROM A UNION
>  SELECT 2 AS precedence, col1, col2 FROM B
>  ORDER BY col1 ASC, precedence DESC)
> GROUP BY precedence
> ORDER BY col1 ASC;
>
> (Just an example, I've got several other columns that have to be
> sorted on, and there can be any number of tables).  This seems to do
> what I want, but it takes an order of magnitude longer than the inner
> SELECTs do on their own (i.e. without the GROUP BY which eliminates
> rows with duplicate 'col1' values).  Any ideas on how I could do this
> more efficiently?
>
> Thanks!
>
> --
> Matthew L. Creech
> ___
> 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

Re: [sqlite] UNION QUERY

[Igor Tandetnik]

"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, classnumber
> FROM music
> WHERE classnumber=8 OR classnumber=9
> AND hd="A"
> ORDER BY random() LIMIT 2;

Try

SELECT title, artist, hd, bank, bookmark, genre, class, classnumber
FROM music
WHERE
classnumber=6 OR
(classnumber=7 AND hd='B') OR
classnumber=8 OR
(classnumber=9 AND hd='A')
ORDER BY random() LIMIT 2;

At the very least, use UNION ALL instead of UNION.

> I actually need to have a limit on each SELECT clause so that I only
> get one
> record from either classnumbers 6 or 7 and then one record from
> classnumbers 8 or 9, randomly chosen.

Ah. Then you need something like this:

select title, ... from
(select * FROM music
 WHERE (classnumber=6 OR (classnumber=7 AND hd='B'))
 ORDER BY random() LIMIT 1)
UNION ALL

select title, ... from
(select * FROM music
 WHERE (classnumber=8 OR (classnumber=9 AND hd='A'))
 ORDER BY random() LIMIT 1);

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Joe Wilson]

> 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 friends.
> 
> Maybe it would be better to document the current behaviour
> and move on.
> 
> Dan.

It would be tricky to document the current behavior accurately.
Compound queries with ORDER BY without aliases have never really worked 
in a uniform way in sqlite. I don't think fixing the issue will trouble 
people, as most must use column aliases and subqueries as a workaround 
for these problems anyway.

I think at the very least, the inconsistency of the column names in
the result set should be resolved:

SQLite version 3.5.3
Enter ".help" for instructions
sqlite> create table foo(a);
sqlite> insert into foo values(1);
sqlite> .header on

sqlite> select foo.a from foo;
a
1

sqlite> select foo.a from foo union all select foo.a from foo;
a
1
1

sqlite> select foo.a from foo union all select foo.a from foo order by 1;
foo.a
1
1

sqlite> select foo.a from foo union all select foo.a from foo group by 1;
a
1
1

Notice the column headings.
Why does the column name change in the result set because of the 
addition of an ORDER BY but not with a GROUP BY clause or with a 
regular non-compound query?

And should regular queries support expressions in ORDER BY, while
compound statements not?

sqlite> select a from foo order by a*a-3*a;
1

sqlite> select a from foo union all select a+5 as a from foo order by a*a-3*a;
SQL error: ORDER BY term number 1 does not match any result column

The only way to get this query to work is to use this workaround:

sqlite> select * from (select a from foo union all select a+5 from foo) order 
by a*a-3*a;
1
6

Other databases allow expressions in compound SELECT/ORDER BY without the
subquery:

mysql> select a from foo union all select a+5 as a from foo order by a*a-3*a;
+--+
| a|
+--+
|1 |
|6 |
+--+

I only have MySQL to test with here. I'm fairly certain it works on 
most other open source and popular commercial databases.



  

Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

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

[Dan]

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 leftmost SELECT,
SQLite searches the next leftmost and matches "b" to "b"
(column 2).

That's how it is at the moment, anyhow.



  http://www.mail-archive.com/sqlite-users@sqlite.org/msg23985.html



Cheers. I'm starting to realise why this little corner of sqlite
is the way it is...


I believe that there are 2 different issues with the current  
implementation:


1. The result set column names of a compound SELECT should drop all
   table qualifiers, as they've lost all meaning once in a UNION.

   i.e., instead of:

 sqlite> SELECT x1.b, a FROM x1 UNION SELECT x, y FROM g2;
 x1.b|a
 value|value

   you should see:

 b|a
 value|value

   as other databases do:

 mysql> SELECT x1.b, a FROM x1 UNION SELECT x, y FROM g2 ORDER  
BY b;

 +--+--+
 | b| a|
 +--+--+
 |2 |1 |
 |9 |0 |
 +--+--+

 mysql> SELECT x1.b, a FROM x1 UNION SELECT x, y FROM g2 ORDER  
BY x1.b;

 ERROR 1054 (42S22): Unknown column 'x1.b' in 'order clause'

2. The compound SELECT's ORDER BY statement elements should only be  
matched

   against the leftmost SELECT. If there is no match in the leftmost
   SELECT, then an error should result - even if a match could  
potentially

   be found in non-leftmost SELECTs.

Or do you disagree?


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 friends.

Maybe it would be better to document the current behaviour
and move on.

Dan.








   
__ 
__

Be a better sports nut!  Let your teams follow you
with Yahoo Mobile. Try it now.  http://mobile.yahoo.com/ 
sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ


-- 
---

To unsubscribe, send email to [EMAIL PROTECTED]
-- 
---





-
To unsubscribe, send email to [EMAIL PROTECTED]
-

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

[Joe Wilson]

--- 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 leftmost and matches "b" to "b"
> (column 2).
> 
> That's how it is at the moment, anyhow.
> 
> >
> >   http://www.mail-archive.com/sqlite-users@sqlite.org/msg23985.html
> >
> 
> Cheers. I'm starting to realise why this little corner of sqlite
> is the way it is...

I believe that there are 2 different issues with the current implementation:

1. The result set column names of a compound SELECT should drop all 
   table qualifiers, as they've lost all meaning once in a UNION.

   i.e., instead of:

 sqlite> SELECT x1.b, a FROM x1 UNION SELECT x, y FROM g2;
 x1.b|a
 value|value

   you should see:

 b|a
 value|value

   as other databases do:

 mysql> SELECT x1.b, a FROM x1 UNION SELECT x, y FROM g2 ORDER BY b;
 +--+--+
 | b| a|
 +--+--+
 |2 |1 |
 |9 |0 |
 +--+--+

 mysql> SELECT x1.b, a FROM x1 UNION SELECT x, y FROM g2 ORDER BY x1.b;
 ERROR 1054 (42S22): Unknown column 'x1.b' in 'order clause'

2. The compound SELECT's ORDER BY statement elements should only be matched
   against the leftmost SELECT. If there is no match in the leftmost
   SELECT, then an error should result - even if a match could potentially
   be found in non-leftmost SELECTs.

Or do you disagree?




  

Be a better sports nut!  Let your teams follow you 
with Yahoo Mobile. Try it now.  
http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

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

[Dan]

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;
   SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY 2;


Don't you mean ORDER BY 1?


I'm talking about sqlite cvs, as the code is implemented right
now (see matchOrderbyToColumn() in select.c). So 2 is correct,
as the test you did shows.

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 leftmost and matches "b" to "b"
(column 2).

That's how it is at the moment, anyhow.



  http://www.mail-archive.com/sqlite-users@sqlite.org/msg23985.html



Cheers. I'm starting to realise why this little corner of sqlite
is the way it is...

Dan.




   
__ 
__

Be a better friend, newshound, and
know-it-all with Yahoo! Mobile.  Try it now.  http:// 
mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ



-- 
---

To unsubscribe, send email to [EMAIL PROTECTED]
-- 
---





-
To unsubscribe, send email to [EMAIL PROTECTED]
-

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

[Joe Wilson]

--- 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 FROM x2 ORDER BY 2;

Don't you mean ORDER BY 1?

  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 FROM x2 ORDER BY 1; <--

I thought *only* the leftmost SELECT in the compound chain governs the
selection of the column names used by the ORDER BY.  The names of the 
subsequent compound SELECTs should be ignored. At least that's how it 
works on MySQL and other databases I've used:

given:

  create table x1(a INT, b INT, c INT);
  insert into x1 values(1, 2, 3);
  create table x2(a INT, b INT, c INT);
  insert into x2 values(9, 0, 4);  

mysql> SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY b;
+--+--+
| b| a|
+--+--+
|2 |1 |
|9 |0 |
+--+--+

mysql> SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY 2;
+--+--+
| b| a|
+--+--+
|9 |0 |
|2 |1 |
+--+--+

mysql> SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY 1;
+--+--+
| b| a|
+--+--+
|2 |1 |
|9 |0 |
+--+--+

Oracle has the same behavior as MySQL, as I recall.

sqlite 3.5 produces a different result since it appears to be
getting the column name from the rightmost compound select:

  SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY b;
  x1.b|a
  9|0
  2|1

Compare MySQL:

  create table x1(a INT, b INT, c INT);
  insert into x1 values(1, 2, 3);
  create table g2(x INT, y INT, z INT);
  insert into g2 values(9, 0, 4);

  mysql> SELECT x1.b, a FROM x1 UNION SELECT x, y FROM g2 ORDER BY y;
  ERROR 1054 (42S22): Unknown column 'y' in 'order clause'

to sqlite:

  sqlite> SELECT x1.b, a FROM x1 UNION SELECT x, y FROM g2 ORDER BY y;
  9|0
  2|1

> To my mind, the logical change to make would be to allow this:
> 
>SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY "x1.b";
>SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY [x1.b];
>SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY 1;

This query is (also) unambiguous given the logic outlined above:

  SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY b;

I thought all of this was already hashed in this thread:

  http://www.mail-archive.com/sqlite-users@sqlite.org/msg23985.html



  

Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

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

[Dan]

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 this file - http://www.sqlite.org/cvstrac/ 
filediff?f=sqlite/src/select.c=1.335=1.336


And this change results in this bug.


On 04/12/2007, at 4:59 AM, Joe Wilson wrote:


--- 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 term number 1 does not match any result column

Tables are created by:
CREATE TABLE a (field);
CREATE TABLE b (field);

Please note that the above queries worked fine with sqlite 3.2.x or
3.3.x.
Any idea?


You probably know the workarounds:

 SELECT a.field FROM a UNION ALL SELECT b.field FROM b ORDER BY 1;

or

 SELECT a.field AS x FROM a UNION ALL SELECT b.field FROM b ORDER  
BY x;


but it's odd that this one doesn't work as well:

 create table t1(a);
 create table t2(b);

 select t1.a from t1 union all select t2.b from t2 order by a;

 SQL error: ORDER BY term number 1 does not match any result column




At present, expressions in the ORDER BY clause attached to a compound
SELECT must be either:

  1) An integer between 1 and the number of columns returned by
 the SELECT statement (inclusive), or

  2) A simple identifier (no quotes). In this case SQLite tries to  
match

 the identifier to one of the returned columns of data by scanning
 the result-set of each of the individual SELECT statements,  
starting

 from the left. The identifier matches the column if the expression
 in the result set is either "" or " as  
"


This means you cannot specify an arbitrary sort key for a compound
statement, you can only nominate one of the returned columns to sort
on.

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 a;
  SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY 2;

  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 FROM x2 ORDER BY 2;

To my mind, the logical change to make would be to allow this:

  SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY "x1.b";
  SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY [x1.b];
  SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY 1;

Because it is consistent with this kind of statement:

  SELECT "x1.b" FROM (SELECT x1.b FROM x1);

Any opinions?

Dan.






Cheers.

--

Dr Gerard Hammond
Garvan Institute of Medical Research






-- 
---

To unsubscribe, send email to [EMAIL PROTECTED]
-- 
---





-
To unsubscribe, send email to [EMAIL PROTECTED]
-

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

[Dr Gerard Hammond]

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 - 
http://www.sqlite.org/cvstrac/filediff?f=sqlite/src/select.c=1.335=1.336

And this change results in this bug.


On 04/12/2007, at 4:59 AM, Joe Wilson wrote:


--- 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 term number 1 does not match any result column

Tables are created by:
CREATE TABLE a (field);
CREATE TABLE b (field);

Please note that the above queries worked fine with sqlite 3.2.x or
3.3.x.
Any idea?


You probably know the workarounds:

 SELECT a.field FROM a UNION ALL SELECT b.field FROM b ORDER BY 1;

or

 SELECT a.field AS x FROM a UNION ALL SELECT b.field FROM b ORDER BY  
x;


but it's odd that this one doesn't work as well:

 create table t1(a);
 create table t2(b);

 select t1.a from t1 union all select t2.b from t2 order by a;

 SQL error: ORDER BY term number 1 does not match any result column




Cheers.

--

Dr Gerard Hammond
Garvan Institute of Medical Research






-
To unsubscribe, send email to [EMAIL PROTECTED]
-

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

[Joe Wilson]

--- 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 term number 1 does not match any result column
> 
> Tables are created by:
> CREATE TABLE a (field);
> CREATE TABLE b (field);
> 
> Please note that the above queries worked fine with sqlite 3.2.x or  
> 3.3.x.
> Any idea?

You probably know the workarounds:

  SELECT a.field FROM a UNION ALL SELECT b.field FROM b ORDER BY 1;

or

  SELECT a.field AS x FROM a UNION ALL SELECT b.field FROM b ORDER BY x;

but it's odd that this one doesn't work as well:

  create table t1(a);
  create table t2(b);

  select t1.a from t1 union all select t2.b from t2 order by a;

  SQL error: ORDER BY term number 1 does not match any result column




  

Get easy, one-click access to your favorites. 
Make Yahoo! your homepage.
http://www.yahoo.com/r/hs 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] UNION?

[Lee Crain]

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,

> 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 WHERE related_item = 1777))
>...> ORDER BY Items.name ASC;
> SQL error: near "UNION": syntax error

Of course the problem is the brackets you have around each SELECT  
statement, which separates them from the UNION operator.

So, it works fine like this:

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 WHERE related_item = 1777
)
ORDER BY Items.name ASC;

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 wrong.

SELECT items_idx, [name], active
FROM Items
WHERE active = 'T'
 AND
 (
 items_idx IN
 ( SELECT [related_item] FROM RelatedItems WHERE item  
= 1777 )
 OR items_idx IN
 ( SELECT item FROM RelatedItems WHERE related_item =  
1777 )
 )
ORDER BY [name] ASC
;


or by using a JOIN:

SELECT items_idx, [name], active
FROM Items
JOIN RelatedItems AS RI
WHERE active = 'T' AND
 (
 RI.related_item = Items.items_idx AND RI.item = 1777
 OR
 RI.item = Items.items_idx AND RI.related_item = 1777
 )
ORDER BY Items.name ASC
;

Tom


--
---
To unsubscribe, send email to [EMAIL PROTECTED]
--
---



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] UNION?

[Lee Crain]

I agree, Jim, it "would have been nice...".

When I first posted about the problem I was having, I had gone through so
SQL many experiments trying to get the query to work that had I lost track
of which variations I had tried on which DBMS's. I inadvertently posted
one of my versions of the query as a representation of the problem that I
had tried with MS SQL Server but not yet tried with SQLite. 

Lee Crain

_

-Original Message-
From: Jim Dodgen [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, August 08, 2007 7:59 PM
To: sqlite-users@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 don't think invisible control
> characters are the problem. I have copied and pasted the query from my
> source code into a MS SQL Server Management Studio interface and
executed
> it against a SQL Server mockup of our SQLite database. It works
perfectly.
>
> I experimented and tried some variations on the query.
>
> ___
>
> 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 WHERE related_item = 1777))
>...> ORDER BY Items.name ASC;
> SQL error: near "UNION": syntax error
> sqlite>
>
> 
>
> However, this query works in both SQLite and 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 WHERE related_item = 1777)
>...> ORDER BY Items.name ASC;
> 1706|Arizona Character|T
> 1707|Arizona Clothing and Props|T
> 1660|Arizona Hair|T
> 2325|Bonnie V3 Teen|T
> 1425|Isabella for Stephanie 3|T
> 1918|Little Darling for V3/SP|T
> 106|Rose Character|T
> 1778|Teresa Hair|T
> sqlite>
>
> 
>
> Further experimentation showed that the extra pair of parentheses in the
> first query (around each SELECT statement) caused the syntax error.
>
> Thanks for your response,
>
> Lee Crain
>
> ____________
>
>
> -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 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?
>
>   
>> __
>> SELECT Items.items_idx, Items.name, Items.active FROM Items 
>> WHERE active = 'T' AND Items.items_idx IN
>> (SELECT related_item FROM RelatedItems WHERE RelatedItems.item = 1777
>> UNION
>> SELECT item FROM RelatedItems WHERE RelatedItems.related_item = 1777)
>> ORDER BY Items.name ASC;
>>
>> 
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
>
>
>
--
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
>
--
> ---
>
>
>
>
--
---
> To unsubscribe, send email to [EMAIL PROTECTED]
>
--
---
>
>
>
>   


--
---
To unsubscribe, send email to [EMAIL PROTECTED]
--
---



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] UNION?

[Tom Briggs]

 

> 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 wrong.

   Err... I think the sub-query in an IN clause has to be executed
before the outer query can be started, so whether you do two sub-queries
and UNION them or two sub-queries and separately check the results
wouldn't seem to make much difference to me.  Either way, both have to
be executed before anything else can be done.

   -T

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] UNION?

[T]

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 WHERE related_item = 1777))
   ...> ORDER BY Items.name ASC;
SQL error: near "UNION": syntax error


Of course the problem is the brackets you have around each SELECT  
statement, which separates them from the UNION operator.


So, it works fine like this:

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 WHERE related_item = 1777
)
ORDER BY Items.name ASC;

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 wrong.


SELECT items_idx, [name], active
FROM Items
WHERE active = 'T'
AND
(
items_idx IN
( SELECT [related_item] FROM RelatedItems WHERE item  
= 1777 )

OR items_idx IN
( SELECT item FROM RelatedItems WHERE related_item =  
1777 )

)
ORDER BY [name] ASC
;


or by using a JOIN:

SELECT items_idx, [name], active
FROM Items
JOIN RelatedItems AS RI
WHERE active = 'T' AND
(
RI.related_item = Items.items_idx AND RI.item = 1777
OR
RI.item = Items.items_idx AND RI.related_item = 1777
)
ORDER BY Items.name ASC
;

Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] UNION?

[Jim Dodgen]

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 don't think invisible control
characters are the problem. I have copied and pasted the query from my
source code into a MS SQL Server Management Studio interface and executed
it against a SQL Server mockup of our SQLite database. It works perfectly.

I experimented and tried some variations on the query.

___

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 WHERE related_item = 1777))
   ...> ORDER BY Items.name ASC;
SQL error: near "UNION": syntax error
sqlite>



However, this query works in both SQLite and 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 WHERE related_item = 1777)
   ...> ORDER BY Items.name ASC;
1706|Arizona Character|T
1707|Arizona Clothing and Props|T
1660|Arizona Hair|T
2325|Bonnie V3 Teen|T
1425|Isabella for Stephanie 3|T
1918|Little Darling for V3/SP|T
106|Rose Character|T
1778|Teresa Hair|T
sqlite>



Further experimentation showed that the extra pair of parentheses in the
first query (around each SELECT statement) caused the syntax error.

Thanks for your response,

Lee Crain




-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 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?

  

__
SELECT Items.items_idx, Items.name, Items.active FROM Items 
WHERE active = 'T' AND Items.items_idx IN

(SELECT related_item FROM RelatedItems WHERE RelatedItems.item = 1777
UNION
SELECT item FROM RelatedItems WHERE RelatedItems.related_item = 1777)
ORDER BY Items.name ASC;



--
D. Richard Hipp <[EMAIL PROTECTED]>


--
---
To unsubscribe, send email to [EMAIL PROTECTED]
--
---



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



  



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] UNION?

[Lee Crain]

Richard,

Thanks for suggesting it but no, I don't think invisible control
characters are the problem. I have copied and pasted the query from my
source code into a MS SQL Server Management Studio interface and executed
it against a SQL Server mockup of our SQLite database. It works perfectly.

I experimented and tried some variations on the query.

___

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 WHERE related_item = 1777))
   ...> ORDER BY Items.name ASC;
SQL error: near "UNION": syntax error
sqlite>



However, this query works in both SQLite and 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 WHERE related_item = 1777)
   ...> ORDER BY Items.name ASC;
1706|Arizona Character|T
1707|Arizona Clothing and Props|T
1660|Arizona Hair|T
2325|Bonnie V3 Teen|T
1425|Isabella for Stephanie 3|T
1918|Little Darling for V3/SP|T
106|Rose Character|T
1778|Teresa Hair|T
sqlite>



Further experimentation showed that the extra pair of parentheses in the
first query (around each SELECT statement) caused the syntax error.

Thanks for your response,

Lee Crain




-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 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?

> 
> __
> SELECT Items.items_idx, Items.name, Items.active FROM Items 
> WHERE active = 'T' AND Items.items_idx IN
> (SELECT related_item FROM RelatedItems WHERE RelatedItems.item = 1777
> UNION
> SELECT item FROM RelatedItems WHERE RelatedItems.related_item = 1777)
> ORDER BY Items.name ASC;
> 
--
D. Richard Hipp <[EMAIL PROTECTED]>


--
---
To unsubscribe, send email to [EMAIL PROTECTED]
--
---



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] UNION?

[Joe Wilson]

--- 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> INSERT INTO "Items" VALUES(1,'dog','T');
sqlite> INSERT INTO "Items" VALUES(2,'cat','A');
sqlite> INSERT INTO "Items" VALUES(1777,'pig','G');
sqlite> CREATE TABLE RelatedItems(item, related_item);
sqlite> INSERT INTO "RelatedItems" VALUES(1,2);
sqlite> INSERT INTO "RelatedItems" VALUES(1777,1);
sqlite> .mode column
sqlite> .header on
sqlite> SELECT Items.items_idx, Items.name, Items.active FROM Items
   ...> WHERE active = 'T' AND Items.items_idx IN
   ...> (SELECT related_item FROM RelatedItems WHERE RelatedItems.item = 1777
   ...> UNION
   ...> SELECT item FROM RelatedItems WHERE RelatedItems.related_item = 1777)
   ...> ORDER BY Items.name ASC;
items_idx   nameactive
--  --  --
1   dog T



  

Fussy? Opinionated? Impossible to please? Perfect.  Join Yahoo!'s user panel 
and lay it on us. http://surveylink.yahoo.com/gmrs/yahoo_panel_invite.asp?a=7 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] UNION?

[Lee Crain]

I've queried it in both the command line interface and via an
sqlite3_exec() call in a C++ environment.

Lee Crain

___


-Original Message-
From: Joe Wilson [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, August 08, 2007 3:30 PM
To: sqlite-users@sqlite.org
Subject: 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 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 set of supported and
> recognized SQL words (http://www.sqlite.org/lang.html).
> 
> Is this correct? If so, is there another way to accomplish this query as
a
> single query?
> 
> Thanks,
> 
> Lee Crain
> 
> __
> 
> 
> SELECT Items.items_idx, Items.name, Items.active FROM Items 
> 
> WHERE active = 'T' AND Items.items_idx IN
> 
> (SELECT related_item FROM RelatedItems WHERE RelatedItems.item = 1777
> 
> UNION
> 
> SELECT item FROM RelatedItems WHERE RelatedItems.related_item = 1777)
> 
> ORDER BY Items.name ASC;



 
__
__
Park yourself in front of a world of choices in alternative vehicles.
Visit the Yahoo! Auto Green Center.
http://autos.yahoo.com/green_center/ 

--
---
To unsubscribe, send email to [EMAIL PROTECTED]
--
---



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] UNION?

[drh]

"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?

> 
> __
> SELECT Items.items_idx, Items.name, Items.active FROM Items 
> WHERE active = 'T' AND Items.items_idx IN
> (SELECT related_item FROM RelatedItems WHERE RelatedItems.item = 1777
> UNION
> SELECT item FROM RelatedItems WHERE RelatedItems.related_item = 1777)
> ORDER BY Items.name ASC;
> 
--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] UNION?

[Joe Wilson]

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 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 set of supported and
> recognized SQL words (http://www.sqlite.org/lang.html).
> 
> Is this correct? If so, is there another way to accomplish this query as a
> single query?
> 
> Thanks,
> 
> Lee Crain
> 
> __
> 
> 
> SELECT Items.items_idx, Items.name, Items.active FROM Items 
> 
> WHERE active = 'T' AND Items.items_idx IN
> 
> (SELECT related_item FROM RelatedItems WHERE RelatedItems.item = 1777
> 
> UNION
> 
> SELECT item FROM RelatedItems WHERE RelatedItems.related_item = 1777)
> 
> ORDER BY Items.name ASC;



  

Park yourself in front of a world of choices in alternative vehicles. Visit the 
Yahoo! Auto Green Center.
http://autos.yahoo.com/green_center/ 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] UNION?

[John Stanton]

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 set of supported and
recognized SQL words (http://www.sqlite.org/lang.html).

Is this correct? If so, is there another way to accomplish this query as a
single query?

Thanks,

Lee Crain

__


SELECT Items.items_idx, Items.name, Items.active FROM Items 


WHERE active = 'T' AND Items.items_idx IN

(SELECT related_item FROM RelatedItems WHERE RelatedItems.item = 1777

UNION

SELECT item FROM RelatedItems WHERE RelatedItems.related_item = 1777)

ORDER BY Items.name ASC;


-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

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

[Bob Dankert]

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 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 * from a limit 3) as a;   <-- this returns 3
> results
> Select * from (select * from b limit 3) as b;   <-- this returns 3
> results
>
> Select * from (select * from a limit 3) as a union select * from 
> (select * from b limit 3) as b; <-- this returns 0 results
>
> If I get rid of the limits, everything is returned from both tables as

> it should be:
> Select * from (select * from a) as a union select * from (select * 
> from b) as b;
>
> Unfortunately, I need to limit the results in individual queries which

> are being unioned together.  Since SQLite does not support limiting 
> individual queries in a union, I was hoping to use sub-selects for the

> tables and limit these.  Does anyone have any suggestions as to what I

> can do for this?
>
> Thanks,
>
> Bob Dankert

Bob,

As a work around you can create temp tables from the two sub-selects and
use a union select to combine them, or create a temp table with the
output of the first sub-select and then insert the result of the second
sub-select into the temp table and then dump that table.

create temp table t1 as select * from a limit 3; create temp table t2 as
select * from b limit 3; select * from t1 union select * from t2;

or

create temp table u as select * from a limit 3; insert into u select *
from b limit 3; select * from u;

I hope this helps.

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

[Dennis Cote]

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 * from a limit 3) as a;   <-- this returns 3
> results
> Select * from (select * from b limit 3) as b;   <-- this returns 3
> results
>
> Select * from (select * from a limit 3) as a union select * from
> (select * from b limit 3) as b; <-- this returns 0 results
>
> If I get rid of the limits, everything is returned from both tables as
> it should be:
> Select * from (select * from a) as a union select * from (select *
> from b) as b;
>
> Unfortunately, I need to limit the results in individual queries which
> are being unioned together.  Since SQLite does not support limiting
> individual queries in a union, I was hoping to use sub-selects for the
> tables and limit these.  Does anyone have any suggestions as to what I
> can do for this?
>
> Thanks,
>
> Bob Dankert

Bob,

As a work around you can create temp tables from the two sub-selects and use
a union select to combine them, or create a temp table with the output of
the first sub-select and then insert the result of the second sub-select
into the temp table and then dump that table.

create temp table t1 as select * from a limit 3;
create temp table t2 as select * from b limit 3;
select * from t1 union select * from t2;

or

create temp table u as select * from a limit 3;
insert into u select * from b limit 3;
select * from u;

I hope this helps.

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

[Dennis Cote]

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 * from a limit 3) as a;   <-- this returns 3
> results
> Select * from (select * from b limit 3) as b;   <-- this returns 3
> results
>
> Select * from (select * from a limit 3) as a union select * from
> (select * from b limit 3) as b; <-- this returns 0 results
>
> If I get rid of the limits, everything is returned from both tables as
> it should be:
> Select * from (select * from a) as a union select * from (select *
> from b) as b;
>
> Unfortunately, I need to limit the results in individual queries which
> are being unioned together.  Since SQLite does not support limiting
> individual queries in a union, I was hoping to use sub-selects for the
> tables and limit these.  Does anyone have any suggestions as to what I
> can do for this?
>
> Thanks,
>
> Bob Dankert

Bob,

It looks like you have found a bug in SQLite. :-)

The explain output below shows how SQLite will execute your query.

sqlite> explain select * from (select * from t limit 3) union select * from
(sel
ect * from tt limit 3);
addr  opcodep1  p2  p3
    --  --  
---
0 OpenTemp  0   0   keyinfo(2,BINARY,BINARY)
1 KeyAsData 0   1
2 SetNumColumn  0   2
3 Integer   -3  0
4 MemStore  0   1
5 Goto  0   40
6 Integer   0   0
7 OpenRead  2   2
8 SetNumColumn  2   2
9 Rewind2   17
10MemIncr   0   17
11Column2   0
12Column2   1
13MakeRecord2   0
14String8   0   0
15PutStrKey 0   0
16Next  2   10
17Close 2   0
18Integer   -3  0
19MemStore  1   1
20Integer   0   0
21OpenRead  4   3
22SetNumColumn  4   2
23Rewind4   31
24MemIncr   1   31
25Column4   0
26Column4   1
27MakeRecord2   0
28String8   0   0
29PutStrKey 0   0
30Next  4   24
31Close 4   0
32Rewind0   38
33MemIncr   1   38
34Column0   0
35Column0   1
36Callback  2   0
37Next  0   33
38Close 0   0
39Halt  0   0
40Transaction   0   0
41VerifyCookie  0   2
42Goto  0   6
43Noop  0   0
sqlite>

The problem is at line 33 where it does an increment and test on memory
location 1. This test shouldn't be there! It fails immediately because the
limit clause on the second sub-select has counted memory location 1 up to
zero already (at line 24). So the query generates the correct result set and
then skips the loop with the Callback opcode, so it doesn't return any
results to you.

It looks like SQLite's compiler is getting confused by the limit clauses on
the sub-selects and adds a limit test to the outter select when it
shouldn't.

You should report this bug.