Ticket Hash: | d11a6e908f8bb04ddf0de0f95b9229ddaad2fd85 | |||
Title: | Query planner fault on three-way nested join with compound inner SELECT | |||
Status: | Fixed | Type: | Code_Defect | |
Severity: | Severe | Priority: | Immediate | |
Subsystem: | Unknown | Resolution: | Fixed | |
Last Modified: | 2014-09-21 22:33:41 | |||
Version Found In: | 3.8.6 | |||
User Comments: | ||||
drh added on 2014-09-20 17:05:39:
(text/x-fossil-wiki)
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. drh added on 2014-09-20 18:42:03: (text/x-fossil-wiki) The same problem can result in queries that sort on the wrong column, thus giving an incorrect answer. For example: <blockquote><verbatim> CREATE TABLE t1(id INTEGER, data TEXT); CREATE TABLE t2(id INTEGER, data TEXT); INSERT INTO t1 VALUES(4, 'a'); INSERT INTO t2 VALUES(3, 'b'); INSERT INTO t1 VALUES(2, 'c'); INSERT INTO t2 VALUES(1, 'd'); SELECT data, id FROM ( SELECT id, data FROM ( SELECT * FROM t1 UNION ALL SELECT * FROM t2 ) ORDER BY data ); </verbatim></blockquote> |