/ View Ticket
Login
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:

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.


drh added on 2014-09-20 18:42:03:

The same problem can result in queries that sort on the wrong column, thus giving an incorrect answer. For example:

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
);