/ Ticket Change Details
Login
Overview

Artifact ID: f6a379b6db04dbd185d32fc69b51b7e0ebc45a4f
Ticket: 2326c258d02ead33d69faa63de8f4686b9b1b9d9
Incorrect result when a LEFT JOIN provides the qualifying constraint for a partial index
User & Date: drh 2015-02-24 15:30:52
Changes

  1. foundin changed to: "3.8.8"
  2. icomment:
    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.
    
  3. login: "drh"
  4. mimetype: "text/x-fossil-wiki"
  5. severity changed to: "Severe"
  6. status changed to: "Open"
  7. title changed to: "Incorrect result on a LEFT JOIN with an ORDER BY"
  8. type changed to: "Code_Defect"