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