SQLite

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.

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