Query planner fault on three-way nested join with compound inner SELECT
|User & Date:||drh 2014-09-20 17:05:39|
- foundin changed to: "3.8.6"
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. <blockquote><verbatim> 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 ); </verbatim></blockquote> 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.
- login: "drh"
- mimetype: "text/x-fossil-wiki"
- severity changed to: "Severe"
- status changed to: "Open"
- title changed to: "Query planner fault on three-way nested join"
- type changed to: "Code_Defect"