Ticket Hash: | 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> |