[EMAIL PROTECTED] wrote:
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).
I believe it does. It calls such columns extended sort keys. The users
result table is extended by adding these columns to produce an
intermediate result table which is sorted as usual, and then it removes
those columns from the result table that is returned.
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.
Or failures to produce the required diagnostics. :-)
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.
I would say it is bug fix. The previous versions executed the queries
incorrectly.
* We have taken no steps toward fixing GROUP BY.
If I got ORDER BY wrong, I'm guessing GROUP BY
is wrong too.
And you should probably have another bug fix version release when these
bugs are fixed as well.
That's my two cents.
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
