SQLite
View Ticket
Not logged in
Ticket UUID: ba82a4a41eac1f38eebacf7d28b6b2273a9c497e
Title: Query optimizer removes ORDER BY when it is needed
Status: Fixed Type: Code_Defect
Severity: Severe Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2013-04-22 22:29:50
Version Found In: 3.7.16.2
Description:

drh claiming to be added on 2013-04-22 22:29:50:
Regression tests added to TH3 in the where33.test module.

User Comments:
drh added on 2013-04-22 18:16:12:

In the following SQL, the query optimizer omits the ORDER BY clause under the assumption that the output will appear in the correct order naturally, but this assumption is in error. The output is not correctly ordered by without the sorting step and so an incorrect result is returned:

CREATE VIRTUAL TABLE test_fts4 USING FTS4 (word TEXT);
INSERT INTO test_fts4 VALUES ('one two');
INSERT INTO test_fts4 VALUES ('one three');
INSERT INTO test_fts4 VALUES ('one two three');
SELECT docid
  FROM test_fts4,
  (SELECT 'one two' terms UNION SELECT 'three') candidates
WHERE
  test_fts4 MATCH candidates.terms
ORDER BY docid;

This bug appears to have been introduced by the ORDER BY query optimizer enhancements of check-in 1e874629d7cf568 on 2012-09-28 and first appeared in release 3.7.15.


drh added on 2013-04-22 18:26:56:

See also ticket [a179fe746592d4].