SQLite

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

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.

See also ticket [a179fe746592d4].