/ View Ticket
Login
Ticket UUID: c41afac34f15781fe09cd1475601db0186b0995b
Title: LIMIT applied globally rather than locally
Status: Fixed Type: Code_Defect
Severity: Severe Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2019-05-29 17:22:56
Version Found In: 3.28.0
User Comments:
drh added on 2019-05-29 16:04:56:

In the SQL below, the "LIMIT 1" clause ends up being applied to the entire query, rather than to just the one subquery to which it is attached.

CREATE TABLE t1 AS VALUES(1),(2);
CREATE TABLE t2 AS VALUES(3);
SELECT * FROM (
  SELECT * FROM (SELECT * FROM t1 LIMIT 1)
  UNION ALL
  SELECT * from t2);

Bisect shows the error was introduced by check-in [67bfd59d9087a987f] (about 5.3 years ago) and first appeared in SQLite version 3.8.4 (2014-03-10).

This problem was reported on the mailing list by Marco Foit.