gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/with3.test (about) 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 |--MATERIALIZE 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 |--MATERIALIZE cnt 107 | |--SETUP 108 | | `--SCAN CONSTANT ROW 109 | `--RECURSIVE STEP 110 | `--SCAN cnt 111 |--SCAN y1 112 `--SEARCH cnt USING AUTOMATIC COVERING INDEX (i=?) 113 }] 114 } 115 116 do_execsql_test 3.2.1 { 117 CREATE TABLE w1(pk INTEGER PRIMARY KEY, x INTEGER); 118 CREATE TABLE w2(pk INTEGER PRIMARY KEY); 119 } 120 121 do_eqp_test 3.2.2 { 122 WITH RECURSIVE c(w,id) AS (SELECT 0, (SELECT pk FROM w2 LIMIT 1) 123 UNION ALL SELECT c.w + 1, x FROM w1, c LIMIT 1) 124 SELECT * FROM c, w2, w1 125 WHERE c.id=w2.pk AND c.id=w1.pk; 126 } { 127 QUERY PLAN 128 |--MATERIALIZE c 129 | |--SETUP 130 | | |--SCAN CONSTANT ROW 131 | | `--SCALAR SUBQUERY xxxxxx 132 | | `--SCAN w2 133 | `--RECURSIVE STEP 134 | |--SCAN w1 135 | `--SCAN c 136 |--SCAN c 137 |--SEARCH w2 USING INTEGER PRIMARY KEY (rowid=?) 138 `--SEARCH w1 USING INTEGER PRIMARY KEY (rowid=?) 139 } 140 141 do_execsql_test 4.0 { 142 WITH t5(t5col1) AS ( 143 SELECT ( 144 WITH t3(t3col1) AS ( 145 WITH t2 AS ( 146 WITH t1 AS (SELECT 1 AS c1 GROUP BY 1) 147 SELECT a.c1 FROM t1 AS a, t1 AS b 148 WHERE anoncol1 = 1 149 ) 150 SELECT (SELECT 1 FROM t2) FROM t2 151 ) 152 SELECT t3col1 FROM t3 WHERE t3col1 153 ) FROM (SELECT 1 AS anoncol1) 154 ) 155 SELECT t5col1, t5col1 FROM t5 156 } {1 1} 157 do_execsql_test 4.1 { 158 SELECT EXISTS ( 159 WITH RECURSIVE Table0 AS ( 160 WITH RECURSIVE Table0(Col0) AS (SELECT ALL 1 ) 161 SELECT ALL ( 162 WITH RECURSIVE Table0 AS ( 163 WITH RECURSIVE Table0 AS ( 164 WITH RECURSIVE Table0 AS (SELECT DISTINCT 1 GROUP BY 1 ) 165 SELECT DISTINCT * FROM Table0 NATURAL INNER JOIN Table0 166 WHERE Col0 = 1 167 ) 168 SELECT ALL (SELECT DISTINCT * FROM Table0) FROM Table0 WHERE Col0 = 1 169 ) 170 SELECT ALL * FROM Table0 NATURAL INNER JOIN Table0 171 ) FROM Table0 ) 172 SELECT DISTINCT * FROM Table0 NATURAL INNER JOIN Table0 173 ); 174 } {1} 175 176 # 2020-01-18 chrome ticket 1043236 177 # Correct handling of the sequence: 178 # OP_OpenEphem 179 # OP_OpenDup 180 # Op_OpenEphem 181 # OP_OpenDup 182 # 183 do_execsql_test 4.2 { 184 SELECT ( 185 WITH t1(a) AS (VALUES(1)) 186 SELECT ( 187 WITH t2(b) AS ( 188 WITH t3(c) AS ( 189 WITH t4(d) AS (VALUES('elvis')) 190 SELECT t4a.d FROM t4 AS t4a JOIN t4 AS t4b LEFT JOIN t4 AS t4c 191 ) 192 SELECT c FROM t3 WHERE a = 1 193 ) 194 SELECT t2a.b FROM t2 AS t2a JOIN t2 AS t2x 195 ) 196 FROM t1 GROUP BY 1 197 ) 198 GROUP BY 1; 199 } {elvis} 200 201 # 2021-02-13 202 # Avoid manifesting the same CTE multiple times. 203 # 204 do_eqp_test 5.1 { 205 WITH RECURSIVE c(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM c WHERE x<1) 206 SELECT x1.x||x2.x||x3.x||x4.x FROM c AS x1, c AS x2, c AS x3, c AS x4 207 ORDER BY 1; 208 } { 209 QUERY PLAN 210 |--MATERIALIZE c 211 | |--SETUP 212 | | `--SCAN CONSTANT ROW 213 | `--RECURSIVE STEP 214 | `--SCAN c 215 |--SCAN x1 216 |--SCAN x2 217 |--SCAN x3 218 |--SCAN x4 219 `--USE TEMP B-TREE FOR ORDER BY 220 } 221 do_execsql_test 5.2 { 222 WITH RECURSIVE c(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM c WHERE x<1) 223 SELECT x1.x||x2.x||x3.x||x4.x FROM c AS x1, c AS x2, c AS x3, c AS x4 224 ORDER BY 1; 225 } {0000 0001 0010 0011 0100 0101 0110 0111 1000 1001 1010 1011 1100 1101 1110 1111} 226 227 #------------------------------------------------------------------------- 228 # At one point this would incorrectly report "circular reference: cte1" 229 # 230 do_catchsql_test 6.0 { 231 with 232 cte1(x, y) AS ( select 1, 2, 3 ), 233 cte2(z) as ( select 1 from cte1 ) 234 select * from cte2, cte1; 235 } {1 {table cte1 has 3 values for 2 columns}} 236 237 do_catchsql_test 6.1 { 238 with 239 cte1(x, y) AS ( select 1, 2, 3 ), 240 cte2(z) as ( select 1 from cte1 UNION ALL SELECT z+1 FROM cte2 WHERE z<5) 241 select * from cte2, cte1; 242 } {1 {table cte1 has 3 values for 2 columns}} 243 244 245 246 247 finish_test