SQLite

View Ticket
Login
Plaintext
2013-04-22
22:29 • Fixed ticket [ba82a4a4]: Query optimizer removes ORDER BY when it is needed plus 3 other changes (artifact: c1b7e291 user: drh)
19:56
Do not allow a virtual table to cancel the ORDER BY clause unless all outer loops are guaranteed to return no more than one row result. Candidate fix for ticket [ba82a4a41eac1]. (check-in: 49cfa14f user: drh tags: trunk)
18:26 • Ticket [ba82a4a4] Query optimizer removes ORDER BY when it is needed status still Open with 6 other changes (artifact: 294e00a4 user: drh)
18:16 • New ticket [ba82a4a4]. (artifact: 39396502 user: drh)
Ticket Hash: 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].