/ Ticket Change Details
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference
Overview

Artifact ID: ce50af6bfecef0870d14bcfca8b6511fe3a1e031
Ticket: d11a6e908f8bb04ddf0de0f95b9229ddaad2fd85
Query planner fault on three-way nested join with compound inner SELECT
User & Date: drh 2014-09-20 17:05:39
Changes

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

    The SELECT statement at the end of the following SQL code causes an assertion fault if assertions are enabled, or gives the error "1st ORDER BY term out of range - should be between 1 and 1" if asserts are disabled.

    CREATE TABLE t1 (id INTEGER PRIMARY KEY, data TEXT);
    INSERT INTO t1(id,data) VALUES(9,'nine-a');
    INSERT INTO t1(id,data) VALUES(10,'ten-a');
    INSERT INTO t1(id,data) VALUES(11,'eleven-a');
    CREATE TABLE t2 (id INTEGER PRIMARY KEY, data TEXT);
    INSERT INTO t2(id,data) VALUES(9,'nine-b');
    INSERT INTO t2(id,data) VALUES(10,'ten-b');
    INSERT INTO t2(id,data) VALUES(11,'eleven-b');
    
    SELECT id FROM (
      SELECT id,data FROM (
         SELECT * FROM t1 UNION ALL SELECT * FROM t2
      )
      WHERE id=10 ORDER BY data
    );
    

    Bisecting shows that this problem was introduced by check-in [a49e909c8738317] on 2012-04-27 and first appeared in SQLite release 3.7.12. The problem was originally detected by Yuanzhong Xu and reported on the sqlite-users mailing list.

  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 "Query planner fault on three-way nested join"
  8. Change type to "Code_Defect"