SQLite
View Ticket
Not logged in
Ticket UUID: 2326c258d02ead33d69faa63de8f4686b9b1b9d9
Title: Incorrect result when a LEFT JOIN provides the qualifying constraint for a partial index
Status: Fixed Type: Code_Defect
Severity: Severe Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2015-02-24 20:21:03
Version Found In: 3.8.8
User Comments:
drh added on 2015-02-24 15:30:52: (text/x-fossil-wiki)
The following SQL has two identical joins, one with and the other without an
ORDER BY clause.  The one without ORDER BY correctly returns two rows of 
result.  The one with ORDER BY returns no rows.

<blockquote><verbatim>
CREATE TABLE A(Name text);
CREATE TABLE Items(ItemName text , Name text);
INSERT INTO Items VALUES('Item1','Parent');
INSERT INTO Items VALUES('Item2','Parent');
CREATE TABLE B(Name text);

SELECT Items.ItemName
  FROM Items
    LEFT JOIN A ON (A.Name = Items.ItemName and Items.ItemName = 'dummy')
    LEFT JOIN B ON (B.Name = Items.ItemName)
  WHERE Items.Name = 'Parent';

SELECT Items.ItemName
  FROM Items
    LEFT JOIN A ON (A.Name = Items.ItemName and Items.ItemName = 'dummy')
    LEFT JOIN B ON (B.Name = Items.ItemName)
  WHERE Items.Name = 'Parent'
ORDER BY Items.ItemName;
</verbatim></blockquote>

This error seems to have been introduced by check-in [d95d0313c447f5]
which was first delivered in SQLite version 3.8.8.

drh added on 2015-02-24 18:13:02: (text/x-fossil-wiki)
The problem goes back much further than version 3.8.8.  There is the
potential to get an incorrect result any time the qualifying constraint
for a partial index is inside the ON clause of a LEFT JOIN.  For example,
the following SQL should return a single row of "1,  NULL" but instead
returns no rows for all prior versions of SQLite that support partial
indexes (SQLite 3.8.0 and later):

<blockquote><verbatim>
CREATE TABLE t1(a);
CREATE TABLE t2(b);
INSERT INTO t1 VALUES(1);
CREATE INDEX t1x ON t1(a) WHERE a=99;
PRAGMA automatic_index=OFF;
SELECT * FROM t1 LEFT JOIN t2 ON (a=99) ORDER BY a;
</verbatim></blockquote>