/ 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: (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>