SQLite: View Ticket

2022-08-04
17:15		Fix a problem with the query optimizer for LIMIT/OFFSET queries when underlying query is a UNION ALL and both arms of the UNION ALL are subqueries with an ORDER BY clause. This bug was reported at forum post 6b5e9188f0657616. The problem was introduced in 2015 (SQLite version 3.9.0) by check-in [4b631364354068af]. See also ticket [b65cb2c8d91f6685]. (check-in: 6c806f64bb user: drh tags: trunk)
2015-10-06
17:27		• Fixed ticket [b65cb2c8d9]: Incorrect LIMIT on a UNION ALL query plus 5 other changes (artifact: 3cd675c689 user: drh)
17:27		Fix the LIMIT and OFFSET handling for UNION ALL queries that contain a subquery with ORDER BY on the right-hand side. Fix for ticket [b65cb2c8d91f668584]. (check-in: 4b63136435 user: drh tags: trunk)
13:26		• New ticket [b65cb2c8d9] Incorrect LIMIT on a UNION ALL query. (artifact: b4cd8e8af6 user: drh)

Ticket Hash:	b65cb2c8d91f6685841d7d1e13b68c10b20da5f1
Title:	Incorrect LIMIT on a UNION ALL query
Status:	Fixed	Type:	Code_Defect
Severity:	Important	Priority:	Immediate
Subsystem:	Unknown	Resolution:	Fixed
Last Modified:	2015-10-06 17:27:29 9.60 years ago	Created:	2015-10-06 13:26:49 9.60 years ago
Version Found In:	3.8.11.1

User Comments:

drh added on 2015-10-06 13:26:49:

In a UNION ALL query with a LIMIT and OFFSET, if the OFFSET is greater than or equal to the number of rows in the first SELECT, then the LIMIT is disabled. For example, the following SQL outputs 5 rows instead of just 1:

CREATE TABLE t1(x);
INSERT INTO t1 VALUES('a');
INSERT INTO t1 VALUES('b');
INSERT INTO t1 VALUES('c');
INSERT INTO t1 VALUES('d');
INSERT INTO t1 VALUES('e');
SELECT * FROM (SELECT x, rowid FROM t1)
UNION ALL
SELECT * FROM (SELECT rowid, x FROM t1 ORDER BY x)
LIMIT 1 OFFSET 5;

But if the OFFSET is changed to 4, then the query correctly outputs just a single row.

This problem appears to have existed as far back as 3.5.1 (circa 2007).