| 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 |