Incorrect result when a LEFT JOIN provides the qualifying constraint for a partial index
|User & Date:||drh 2015-02-24 15:30:52|
- Change foundin to "3.8.8"
- Change icomment to:
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.
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;
This error seems to have been introduced by check-in [d95d0313c447f5] which was first delivered in SQLite version 3.8.8.
- Change login to "drh"
- Change mimetype to "text/x-fossil-wiki"
- Change severity to "Severe"
- Change status to "Open"
- Change title to "Incorrect result on a LEFT JOIN with an ORDER BY"
- Change type to "Code_Defect"