/ Ticket Change Details
Login
Overview

Artifact ID: d28d654485803572fd084ed1b7ae92bf843af5e343e7af81db7681c4aa72100d
Ticket: 892fc34f173e99d8aa03d52fbf42e0f9f2dfb210
Incorrect query result when a LEFT JOIN subquery is flattened
User & Date: drh 2017-06-20 16:15:03
Changes

  1. Change foundin to "3.19.3"
  2. Change icomment to:

    The following SQL returns a different result in SQLite version 3.19.3 compared to SQLite version 3.18.0 and PostgreSQL. The result is also different if automatic indexes are disabled using "PRAGMA automatic_index=OFF".

    CREATE TABLE t1(id INTEGER PRIMARY KEY);
    CREATE TABLE t2(id INTEGER PRIMARY KEY, c2 INTEGER);
    CREATE TABLE t3(id INTEGER PRIMARY KEY, c3 INTEGER);
    INSERT INTO t1(id) VALUES(456);
    INSERT INTO t3(id) VALUES(1),(2);
    
    SELECT t1.id, x2.id, x3.id
    FROM t1
    LEFT JOIN (SELECT * FROM t2) AS x2 ON t1.id=x2.c2
    LEFT JOIN t3 AS x3 ON x2.id=x3.c3;
    

    This problem was introduced by the LEFT JOIN subquery flattener optimization of check-in [41c27bc0ff] (which is also the cause of other bugs such as [cad1ab4cb7b0] and [7fde638e94287d]). The problem first appeared in the 3.19.0 release (2017-05-22).

    This problem was reported on the SQLite users mailing list by Eric Sink.

  3. Change login to "drh"
  4. Change mimetype to "text/x-fossil-wiki"
  5. Change severity to "Severe"
  6. Change status to "Open"
  7. Change title to:

    Incorrect query result when a LEFT JOIN subquery is flattened

  8. Change type to "Code_Defect"