SQLite
View Ticket
Not logged in
Ticket UUID: db4d96798da8b9707f76509e57d745d64a92e880
Title: LIMIT does not work with nested views containing UNION ALL
Status: Fixed Type: Code_Defect
Severity: Important Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2013-01-29 23:55:55
Version Found In: 3.7.15.2
User Comments:
drh added on 2013-01-28 19:34:30: (text/x-fossil-wiki)
The LIMIT on the final SELECT in the following code does not work:

<verbatim>
CREATE TABLE t1(x);
INSERT INTO t1 VALUES(5);
CREATE VIEW v1 AS SELECT x*2 FROM t1;
CREATE VIEW v2 AS SELECT * FROM v1 UNION ALL SELECT * FROM v1;
CREATE VIEW v4 AS SELECT * FROM v2 UNION ALL SELECT * FROM v2;
SELECT * FROM v4 LIMIT 3;
</verbatim>

The problem seems to have been introduced by check-ins [3ef468e7046b209]
or [a79786a961dba8] on 2008-07-01.  The problem first appeared in version
3.6.1.  The problem was detected during internal testing and has never
been seen in the wild.

drh added on 2013-01-29 23:32:51: (text/x-fossil-wiki)
A related problem:  The following SQL leaks memory - lookaside memory in
particular which will result in an assert() fault when run with SQLITE_DEBUG.

<verbatim>
CREATE TABLE t1(x);
INSERT INTO t1 VALUES(1),(2);
SELECT z FROM (
    SELECT y AS z FROM (SELECT x AS y FROM t1)
    UNION ALL
    SELECT y+10 FROM (SELECT x AS y FROM t1)
)
LIMIT 3 OFFSET 2;
</verbatim>