Debian Codegraph @

test/with3.test

raw
1 # 2015-11-07
2 #
3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
5 #
6 # May you do good and not evil.
7 # May you find forgiveness for yourself and forgive others.
8 # May you share freely, never taking more than you give.
9 #
10 #***********************************************************************
11 # This file implements regression tests for SQLite library. The
12 # focus of this file is testing the WITH clause.
13 #
14
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
17 set ::testprefix with3
18
19 ifcapable {!cte} {
20 finish_test
21 return
22 }
23
24 # Test problems found by Kostya Serebryany using
25 # LibFuzzer. (http://llvm.org/docs/LibFuzzer.html)
26 #
27 do_catchsql_test 1.0 {
28 WITH i(x) AS (
29 WITH j AS (SELECT 10)
30 SELECT 5 FROM t0 UNION SELECT 8 FROM m
31 )
32 SELECT * FROM i;
33 } {1 {no such table: m}}
34
35 # 2019-11-09 dbfuzzcheck find
36 do_catchsql_test 1.1 {
37 CREATE VIEW v1(x,y) AS
38 WITH t1(a,b) AS (VALUES(1,2))
39 SELECT * FROM nosuchtable JOIN t1;
40 SELECT * FROM v1;
41 } {1 {no such table: main.nosuchtable}}
42
43 # Additional test cases that came out of the work to
44 # fix for Kostya's problem.
45 #
46 do_execsql_test 2.0 {
47 WITH
48 x1 AS (SELECT 10),
49 x2 AS (SELECT 11),
50 x3 AS (
51 SELECT * FROM x1 UNION ALL SELECT * FROM x2
52 ),
53 x4 AS (
54 WITH
55 x1 AS (SELECT 12),
56 x2 AS (SELECT 13)
57 SELECT * FROM x3
58 )
59 SELECT * FROM x4;
60
61 } {10 11}
62
63 do_execsql_test 2.1 {
64 CREATE TABLE t1(x);
65 WITH
66 x1(a) AS (values(100))
67 INSERT INTO t1(x)
68 SELECT * FROM (WITH x2(y) AS (SELECT * FROM x1) SELECT y+a FROM x1, x2);
69 SELECT * FROM t1;
70 } {200}
71
72 #-------------------------------------------------------------------------
73 # Test that the planner notices LIMIT clauses on recursive WITH queries.
74 #
75
76 ifcapable analyze {
77 do_execsql_test 3.1.1 {
78 CREATE TABLE y1(a, b);
79 CREATE INDEX y1a ON y1(a);
80
81 WITH cnt(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM cnt LIMIT 1000)
82 INSERT INTO y1 SELECT i%10, i FROM cnt;
83 ANALYZE;
84
85 }
86
87 do_eqp_test 3.1.2 {
88 WITH cnt(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM cnt LIMIT 1)
89 SELECT * FROM cnt, y1 WHERE i=a
90 } [string map {"\n " \n} {
91 QUERY PLAN
92 |--CO-ROUTINE cnt
93 | |--SETUP
94 | | `--SCAN CONSTANT ROW
95 | `--RECURSIVE STEP
96 | `--SCAN cnt
97 |--SCAN cnt
98 `--SEARCH y1 USING INDEX y1a (a=?)
99 }]
100
101 do_eqp_test 3.1.3 {
102 WITH cnt(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM cnt LIMIT 1000000)
103 SELECT * FROM cnt, y1 WHERE i=a
104 } [string map {"\n " \n} {
105 QUERY PLAN
106 |--CO-ROUTINE cnt
107 | |--SETUP
108 | | `--SCAN CONSTANT ROW
109 | `--RECURSIVE STEP
110 | `--SCAN cnt
111 |--SCAN y1
112 |--BLOOM FILTER ON cnt (i=?)
113 `--SEARCH cnt USING AUTOMATIC COVERING INDEX (i=?)
114 }]
115 }
116
117 do_execsql_test 3.2.1 {
118 CREATE TABLE w1(pk INTEGER PRIMARY KEY, x INTEGER);
119 CREATE TABLE w2(pk INTEGER PRIMARY KEY);
120 }
121
122 do_eqp_test 3.2.2 {
123 WITH RECURSIVE c(w,id) AS (SELECT 0, (SELECT pk FROM w2 LIMIT 1)
124 UNION ALL SELECT c.w + 1, x FROM w1, c LIMIT 1)
125 SELECT * FROM c, w2, w1
126 WHERE c.id=w2.pk AND c.id=w1.pk;
127 } {
128 QUERY PLAN
129 |--CO-ROUTINE c
130 | |--SETUP
131 | | |--SCAN CONSTANT ROW
132 | | `--SCALAR SUBQUERY xxxxxx
133 | | `--SCAN w2
134 | `--RECURSIVE STEP
135 | |--SCAN w1
136 | `--SCAN c
137 |--SCAN c
138 |--SEARCH w2 USING INTEGER PRIMARY KEY (rowid=?)
139 `--SEARCH w1 USING INTEGER PRIMARY KEY (rowid=?)
140 }
141
142 do_execsql_test 4.0 {
143 WITH t5(t5col1) AS (
144 SELECT (
145 WITH t3(t3col1) AS (
146 WITH t2 AS (
147 WITH t1 AS (SELECT 1 AS c1 GROUP BY 1)
148 SELECT a.c1 FROM t1 AS a, t1 AS b
149 WHERE anoncol1 = 1
150 )
151 SELECT (SELECT 1 FROM t2) FROM t2
152 )
153 SELECT t3col1 FROM t3 WHERE t3col1
154 ) FROM (SELECT 1 AS anoncol1)
155 )
156 SELECT t5col1, t5col1 FROM t5
157 } {1 1}
158 do_execsql_test 4.1 {
159 SELECT EXISTS (
160 WITH RECURSIVE Table0 AS (
161 WITH RECURSIVE Table0(Col0) AS (SELECT ALL 1 )
162 SELECT ALL (
163 WITH RECURSIVE Table0 AS (
164 WITH RECURSIVE Table0 AS (
165 WITH RECURSIVE Table0 AS (SELECT DISTINCT 1 GROUP BY 1 )
166 SELECT DISTINCT * FROM Table0 NATURAL INNER JOIN Table0
167 WHERE Col0 = 1
168 )
169 SELECT ALL (SELECT DISTINCT * FROM Table0) FROM Table0 WHERE Col0 = 1
170 )
171 SELECT ALL * FROM Table0 NATURAL INNER JOIN Table0
172 ) FROM Table0 )
173 SELECT DISTINCT * FROM Table0 NATURAL INNER JOIN Table0
174 );
175 } {1}
176
177 # 2020-01-18 chrome ticket 1043236
178 # Correct handling of the sequence:
179 # OP_OpenEphem
180 # OP_OpenDup
181 # Op_OpenEphem
182 # OP_OpenDup
183 #
184 do_execsql_test 4.2 {
185 SELECT (
186 WITH t1(a) AS (VALUES(1))
187 SELECT (
188 WITH t2(b) AS (
189 WITH t3(c) AS (
190 WITH t4(d) AS (VALUES('elvis'))
191 SELECT t4a.d FROM t4 AS t4a JOIN t4 AS t4b LEFT JOIN t4 AS t4c
192 )
193 SELECT c FROM t3 WHERE a = 1
194 )
195 SELECT t2a.b FROM t2 AS t2a JOIN t2 AS t2x
196 )
197 FROM t1 GROUP BY 1
198 )
199 GROUP BY 1;
200 } {elvis}
201
202 # 2021-02-13
203 # Avoid manifesting the same CTE multiple times.
204 #
205 do_eqp_test 5.1 {
206 WITH RECURSIVE c(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM c WHERE x<1)
207 SELECT x1.x||x2.x||x3.x||x4.x FROM c AS x1, c AS x2, c AS x3, c AS x4
208 ORDER BY 1;
209 } {
210 QUERY PLAN
211 |--MATERIALIZE c
212 | |--SETUP
213 | | `--SCAN CONSTANT ROW
214 | `--RECURSIVE STEP
215 | `--SCAN c
216 |--SCAN x1
217 |--SCAN x2
218 |--SCAN x3
219 |--SCAN x4
220 `--USE TEMP B-TREE FOR ORDER BY
221 }
222 do_execsql_test 5.2 {
223 WITH RECURSIVE c(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM c WHERE x<1)
224 SELECT x1.x||x2.x||x3.x||x4.x FROM c AS x1, c AS x2, c AS x3, c AS x4
225 ORDER BY 1;
226 } {0000 0001 0010 0011 0100 0101 0110 0111 1000 1001 1010 1011 1100 1101 1110 1111}
227
228 #-------------------------------------------------------------------------
229 # At one point this would incorrectly report "circular reference: cte1"
230 #
231 do_catchsql_test 6.0 {
232 with
233 cte1(x, y) AS ( select 1, 2, 3 ),
234 cte2(z) as ( select 1 from cte1 )
235 select * from cte2, cte1;
236 } {1 {table cte1 has 3 values for 2 columns}}
237
238 do_catchsql_test 6.1 {
239 with
240 cte1(x, y) AS ( select 1, 2, 3 ),
241 cte2(z) as ( select 1 from cte1 UNION ALL SELECT z+1 FROM cte2 WHERE z<5)
242 select * from cte2, cte1;
243 } {1 {table cte1 has 3 values for 2 columns}}
244
245
246
247
248 finish_test