[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]
-----------------------------------------------------------------------------

Reply via email to