Joe Wilson wrote:
Let's add another row to table t1...
postgres=> insert into t1 values(2, -1000, 5);
INSERT 0 1
test=> select * from t1;
a | b | c
---+-------+---
1 | 2 | 4
2 | -1000 | 5
postgres=> select a, a+b AS "c" from t1 order by c;
a | c
---+------
2 | -998
1 | 3
mysql> select * from t1;
+------+-------+------+
| a | b | c |
+------+-------+------+
| 1 | 2 | 4 |
| 2 | -1000 | 5 |
+------+-------+------+
mysql> select a, a+b AS "c" from t1 order by c;
+------+------+
| a | c |
+------+------+
| 2 | -998 |
| 1 | 3 |
+------+------+
which differs from:
SQLite version 3.5.1
sqlite> select * from t1;
a b c
---------- ---------- ----------
1 2 4
2 -1000 5
sqlite> select a, a+b AS "c" from t1 order by c;
a c
---------- ----------
1 3
2 -998
Which database is correct?
Joe and Richard,
I have been reading the SQL:1999 standard again. :-)
It looks to me like SQLite has this one wrong.
In standard SQL the order by clause can only be applied in cursors
(section 14.1), but the behavior in question applies to the cursor
specification which is a query specification with an order by clause as
all these databases use.
<cursor specification> ::= <query expression> [ <order by clause> ] [
<updatability clause> ]
Syntax rule 18 describes the order by clause, and it is very long. It
references the table T which is defined in syntax rule 3 as the result
of evaluating the query expression. These rules are copied below for
reference.
Note, by rule 18.d a literal is not allowed as a sort key, but SQLite
accepts a literal without complaint.
By rule 3, T is the result of the query expression "select a, a+b as c
from t1". This is a table with two columns, "a" and "c".
By rule 18.f.i, the sort spec list ("c") does not contain any sort key
that is not in T, so we fall down to 18.f.ii where the sort table is ST
defined as T.
This sort table is sorted as expected by General rule 2 in ascending
order by "c".
So SQLite should be sorting by the result column "c" not the column "c"
in the table T1 which it doing.
HTH
Dennis Cote
3) Let T be the result of evaluating the <query expression> QE
immediately contained in the
<cursor specification>.
18) If an <order by clause> is specified, then:
a) Let Ki be the <sort key> contained in the i-th <sort specification>.
b) Let DT be the declared type of Ki.
c) If DT is a user-defined type, then the comparison form of DT shall be
FULL.
d) Ki shall not be a <literal>.
e) If QE is a <query expression body> that is a <non-join query
expression> that is a <non-join
query term> that is a <non-join query primary> that is a <simple table>
that is a <query
specification>, then the <cursor specification> is said to be a simple
table query.
f) Case:
i) If <sort specification list> contains any <sort key> Ki that contains
a column reference
to a column that is not a column of T, then:
1) The <cursor specification> shall be a simple table query.
2) Case:
A) If Ki is not equivalent to a <value expression> immediately contained
in any
<derived column> in the <select list> SL of <query specification> QS
contained
in QE, then:
I) T shall not be a grouped table.
II) QS shall not specify the <set quantifier> DISTINCT or directly
contain one
or more <set function specification>s.
III) Let Cj be a column that is not a column of T and whose column
reference is
contained in some Ki.
IV) Let SKL be the list of <derived column>s that are <column name>s of
column references to every Cj. The columns Cj are said to be extended sort
key columns.
V) Let TE be the <table expression> immediately contained in QS.
VI) Let ST be the result of evaluating the <query specification>:
SELECT SL, SKL FROM TE
B) Otherwise:
I) Let ST be T.
II) For every <derived column> DCe of SL that is equivalent to Ki, if
DCe has a
<column name>, then let CNe be that <column name>; otherwise:
1) Let CNe be an implementation-defined <column name> that is not equal
to any <column name> of any column of ST.
2) DCe is effectively replaced by DEe AS CNe in the <select list> of ST,
where DEe is the <derived element> of DCe.
III) Ki is effectively replaced by CNe.
ii) Otherwise, let ST be T.
g) ST is said to be a sort table.
h) Ki is a <value expression>. The <value expression> shall not contain
a <subquery> or a <set
function specification>, but shall contain a <column reference>.
i) Let X be any <column reference> directly contained in Ki.
ii) If X does not contain an explicit <table or query name> or
<correlation name>, then Ki
shall be a <column name> that shall be equivalent to the name of exactly
one column of
ST.
NOTE 287 – A previous version of ISO/IEC 9075 allows <sort
specification> to be a <signed integer>
to denote a column reference of a column of T. That facility no longer
exists. See Annex E,
‘‘Incompatibilities with ISO/IEC 9075:1992 and ISO/IEC 9075-4:1996’’.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
