Re: [sqlite] UNION and ORDER BY errors starting from sqlite 3.4.2
> Not in principle. But I think changes that break backwards > compatibility would be more trouble than they're worth for > something like this. In the absence of clearer guidance > from sql-92, it's probably more important to be compatible > with earlier sqlite versions than with mysql and 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
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
--- 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
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
--- 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
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
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
--- 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] -
[sqlite] UNION and ORDER BY errors starting from sqlite 3.4.2
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? --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ - To unsubscribe, send email to [EMAIL PROTECTED] -
