Ticket number #2822
http://www.sqlite.org/cvstrac/tktview?tn=2822
has provoked extensive changes to the way SQLite handles
ORDER BY clauses. The current algorithm goes like this:
(1) If an ORDER BY term is a constant integer k
then sort by the k-th column of the result set.
(2) If an ORDER BY term is a simple identifer
(like "x", not "x.y" and not "x.y.z") and if
there if the k-th column uses that same identifer
as an AS alias, the sort by the k-th column.
(3) Otherwise, evaluate the expression which is
in the ORDER BY term and sort by the resulting
value.
For a compound query, the expression in step (3) must
exactly match one of the result columns. Also, the
three steps are attempted first on the left-most SELECT.
If there is a match, the process stops there. If no
match is found, the next SELECT to the right is tried.
This repeats as necessary until a match is found or
until you run out of SELECT statement in which case there
is an error.
This algorithm differs from all prior versions of SQLite
(1.0.0 through 3.5.3) by the addition of step (2).
Adding step (2) brings SQLite much closer to the SQL
standard. I believe that SQLite is now a superset of
the SQL standard. SQL has no concept of step (3). And
in a compound query, SQL only looks at the left-most
SELECT and does not fail over to SELECT statements to
the right looking for a match. But these changes can
be considered extensions.
The revised algorithm is mostly compatible with the
way SQLite has always operated before. But there
are a few obscure corner cases where there is a difference.
An example of the difference is the following:
CREATE TABLE a(x,y);
INSERT INTO a VALUES(1,8);
INSERT INTO a VALUES(9,2);
SELECT x AS y FROM a ORDER BY y;
In older versions of SQLite, the SELECT statement above
would return 9, 1 since the ORDER BY term evaluated to
the expression a.y by rule (3) In the next release,
because of the addition of rule (2) above, the result
will be 1, 9.
My question to the community is this: Are these
differences sufficient to justify going with version
3.6.0 in the next release? Or can we call the change
a "bug fix" and number the next release 3.5.4?
Other information to consider:
* We do not have a lot of time to debate the merits
of this change since we need to get out a release
to fix critical bug #2832.
* We have taken no steps toward fixing GROUP BY.
If I got ORDER BY wrong, I'm guessing GROUP BY
is wrong too.
Thanks for your input.
--
D. Richard Hipp <[EMAIL PROTECTED]>
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
