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&v1=1.335&v2=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 "<identifier>" or "<expr> as
<identifier>"
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]
-----------------------------------------------------------------------------
