/ Ticket Change Details
Login
Overview

Artifact ID: 9b89fdd3fe8f68baabb8980d00c19255c305ff00f274aaccde6b67bedba530e2
Ticket: 9936b2fa443fec03ff25f9b822528c20a2200a49
Infinite loop due to the ORDER BY LIMIT optimization
User & Date: drh 2018-09-08 03:22:02
Changes

  1. foundin changed to: "3.24.0"
  2. icomment:
    A confluence of multiple defects in the code generator associated with the
    ORDER BY LIMIT optimization causes the prepared statement to enter an infinite
    loop while running the final query in the following SQL:
    
    <blockquote><verbatim>
    CREATE TABLE t1(aa VARCHAR PRIMARY KEY NOT NULL,bb,cc,x VARCHAR(400));
    INSERT INTO t1(aa,bb,cc) VALUES('maroon','meal','lecture');
    INSERT INTO t1(aa,bb,cc) VALUES('reality','meal','catsear');
    CREATE TABLE t2(aa VARCHAR PRIMARY KEY, dd INT DEFAULT 1, ee, x VARCHAR(100));
    INSERT INTO t2(aa,dd,ee) VALUES('maroon',0,'travel'),('reality',0,'hour');
    CREATE INDEX t2x1 ON t2(dd,ee);
    ANALYZE;
    DROP TABLE IF EXISTS sqlite_stat4;
    DELETE FROM sqlite_stat1;
    INSERT INTO sqlite_stat1 VALUES
      ('t2','t2x1','3 3 3'),
      ('t2','sqlite_autoindex_t2_1','3 1'),
      ('t1','sqlite_autoindex_t1_1','2 1');
    ANALYZE sqlite_master;
    PRAGMA vdbe_trace=on;
    SELECT *
      FROM t1 LEFT JOIN t2 ON t1.aa=t2.aa
     WHERE t1.bb='meal'
     ORDER BY t2.dd DESC
     LIMIT 1;
    </verbatim></blockquote>
    
    The infinite loop is only entered if the command-line shell is compiled
    without -DSQLITE_DEBUG or without -DSQLITE_ENABLE_MODULE_COMMENTS.  If both
    of those compile-time options are enabled, then another bug in the code
    generator causes the ORDER BY LIMIT optimization to be disabled, and so the
    infinite loop does not occur.
    
    This problem was originally reported on the SQLite users mailing list.
    It took considerable effort to reproduce the problem and then boil it down
    to the repro script shown above.
    
    The script above first began failing with check-in [02ba8a7ba7ba71cd7abd5d]
    which added the omit-left-join optimization.  That optimization does not
    seem to be the root cause, but merely a changed that allowed
    it to be expressed with the current script.  The ORDER BY LIMIT optimization
    that probably is the root cause was introduced in version 3.14 (2016-08-08)
    by check-in [559733b09e9630fac9d].
    
  3. login: "drh"
  4. mimetype: "text/x-fossil-wiki"
  5. severity changed to: "Critical"
  6. status changed to: "Open"
  7. title changed to:
    Infinite loop due to the ORDER BY LIMIT optimization
    
  8. type changed to: "Code_Defect"