SQLite
View Ticket
Not logged in
Ticket UUID: cb3aa0641d9a413841c004293a4fc06cdc122029
Title: Incorrect result from a subquery containing over-constrainted ORDER BY
Status: Fixed Type: Code_Defect
Severity: Critical Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2016-01-13 17:50:35
Version Found In: 3.10.0
User Comments:
drh added on 2016-01-13 15:00:25: (text/x-fossil-wiki)
The query at the end of the following SQL should return a value of "1", but it
sometimes returns "NULL" on recent versions of SQLite:

<blockquote><verbatim>
CREATE TABLE t1(x INTEGER PRIMARY KEY);
INSERT INTO t1 VALUES(1),(2);
CREATE TABLE t2(y);
INSERT INTO t2 VALUES(3),(4);
SELECT quote((SELECT x FROM t2, t1 ORDER BY x, y));
</verbatim></blockquote>

This problem appears to have been introduced on [2014-08-29 14:20:46] by
check-in [2a74129a21f], though it is unclear if that check-in actually caused
the problem or just unmasked it.
The problem appears to be cause by misbehavior in the
[https://www.sqlite.org/queryplanner.html#partialsort | partial sorting by index]
optimization.

drh added on 2016-01-13 15:21:24: (text/x-fossil-wiki)
The simple work-around to this problem is to not over-constraint ORDER BY
clauses.  Do not write:

<blockquote>
SELECT x FROM ... ORDER BY x, y, z;
</blockquote>

The extra "y" and "z" terms on the ORDER BY are pointless as the "ORDER BY x" is sufficient to completely define the output order.  So instead write:

<blockquote>
SELECT x FROM ... ORDER BY x;
</blockquote>

SQLite ought to give the correct answer in either case.  And apparently it always does in the second case.  The bug here is that sometimes the first case gives an incorrect result.