gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/closure01.test (about) 1 # 2013-04-25 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 # 12 # Test cases for transitive_closure virtual table. 13 14 set testdir [file dirname $argv0] 15 source $testdir/tester.tcl 16 set testprefix closure01 17 18 ifcapable !vtab||!cte { finish_test ; return } 19 20 load_static_extension db closure 21 22 do_execsql_test 1.0 { 23 BEGIN; 24 CREATE TABLE t1(x INTEGER PRIMARY KEY, y INTEGER); 25 WITH RECURSIVE 26 cnt(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM cnt LIMIT 131072) 27 INSERT INTO t1(x, y) SELECT i, nullif(i,1)/2 FROM cnt; 28 CREATE INDEX t1y ON t1(y); 29 COMMIT; 30 CREATE VIRTUAL TABLE cx 31 USING transitive_closure(tablename=t1, idcolumn=x, parentcolumn=y); 32 } {} 33 34 # The entire table 35 do_timed_execsql_test 1.1 { 36 SELECT count(*), depth FROM cx WHERE root=1 GROUP BY depth ORDER BY 1; 37 } {/1 0 1 17 2 1 4 2 8 3 16 4 .* 65536 16/} 38 do_timed_execsql_test 1.1-cte { 39 WITH RECURSIVE 40 below(id,depth) AS ( 41 VALUES(1,0) 42 UNION ALL 43 SELECT t1.x, below.depth+1 44 FROM t1 JOIN below on t1.y=below.id 45 ) 46 SELECT count(*), depth FROM below GROUP BY depth ORDER BY 1; 47 } {/1 0 1 17 2 1 4 2 8 3 16 4 .* 65536 16/} 48 49 # descendents of 32768 50 do_timed_execsql_test 1.2 { 51 SELECT * FROM cx WHERE root=32768 ORDER BY id; 52 } {32768 0 65536 1 65537 1 131072 2} 53 do_timed_execsql_test 1.2-cte { 54 WITH RECURSIVE 55 below(id,depth) AS ( 56 VALUES(32768,0) 57 UNION ALL 58 SELECT t1.x, below.depth+1 59 FROM t1 JOIN below on t1.y=below.id 60 WHERE below.depth<2 61 ) 62 SELECT id, depth FROM below ORDER BY id; 63 } {32768 0 65536 1 65537 1 131072 2} 64 65 # descendents of 16384 66 do_timed_execsql_test 1.3 { 67 SELECT * FROM cx WHERE root=16384 AND depth<=2 ORDER BY id; 68 } {16384 0 32768 1 32769 1 65536 2 65537 2 65538 2 65539 2} 69 do_timed_execsql_test 1.3-cte { 70 WITH RECURSIVE 71 below(id,depth) AS ( 72 VALUES(16384,0) 73 UNION ALL 74 SELECT t1.x, below.depth+1 75 FROM t1 JOIN below on t1.y=below.id 76 WHERE below.depth<2 77 ) 78 SELECT id, depth FROM below ORDER BY id; 79 } {16384 0 32768 1 32769 1 65536 2 65537 2 65538 2 65539 2} 80 81 # children of 16384 82 do_execsql_test 1.4 { 83 SELECT id, depth, root, tablename, idcolumn, parentcolumn FROM cx 84 WHERE root=16384 85 AND depth=1 86 ORDER BY id; 87 } {32768 1 {} t1 x y 32769 1 {} t1 x y} 88 89 # great-grandparent of 16384 90 do_timed_execsql_test 1.5 { 91 SELECT id, depth, root, tablename, idcolumn, parentcolumn FROM cx 92 WHERE root=16384 93 AND depth=3 94 AND idcolumn='Y' 95 AND parentcolumn='X'; 96 } {2048 3 {} t1 Y X} 97 do_timed_execsql_test 1.5-cte { 98 WITH RECURSIVE 99 above(id,depth) AS ( 100 VALUES(16384,0) 101 UNION ALL 102 SELECT t1.y, above.depth+1 103 FROM t1 JOIN above ON t1.x=above.id 104 WHERE above.depth<3 105 ) 106 SELECT id FROM above WHERE depth=3; 107 } {2048} 108 109 # depth<5 110 do_timed_execsql_test 1.6 { 111 SELECT count(*), depth FROM cx WHERE root=1 AND depth<5 112 GROUP BY depth ORDER BY 1; 113 } {1 0 2 1 4 2 8 3 16 4} 114 do_timed_execsql_test 1.6-cte { 115 WITH RECURSIVE 116 below(id,depth) AS ( 117 VALUES(1,0) 118 UNION ALL 119 SELECT t1.x, below.depth+1 120 FROM t1 JOIN below ON t1.y=below.id 121 WHERE below.depth<4 122 ) 123 SELECT count(*), depth FROM below GROUP BY depth ORDER BY 1; 124 } {1 0 2 1 4 2 8 3 16 4} 125 126 # depth<=5 127 do_execsql_test 1.7 { 128 SELECT count(*), depth FROM cx WHERE root=1 AND depth<=5 129 GROUP BY depth ORDER BY 1; 130 } {1 0 2 1 4 2 8 3 16 4 32 5} 131 132 # depth==5 133 do_execsql_test 1.8 { 134 SELECT count(*), depth FROM cx WHERE root=1 AND depth=5 135 GROUP BY depth ORDER BY 1; 136 } {32 5} 137 138 # depth BETWEEN 3 AND 5 139 do_execsql_test 1.9 { 140 SELECT count(*), depth FROM cx WHERE root=1 AND depth BETWEEN 3 AND 5 141 GROUP BY depth ORDER BY 1; 142 } {8 3 16 4 32 5} 143 144 # depth==5 with min() and max() 145 do_timed_execsql_test 1.10 { 146 SELECT count(*), min(id), max(id) FROM cx WHERE root=1 AND depth=5; 147 } {32 32 63} 148 do_timed_execsql_test 1.10-cte { 149 WITH RECURSIVE 150 below(id,depth) AS ( 151 VALUES(1,0) 152 UNION ALL 153 SELECT t1.x, below.depth+1 154 FROM t1 JOIN below ON t1.y=below.id 155 WHERE below.depth<5 156 ) 157 SELECT count(*), min(id), max(id) FROM below WHERE depth=5; 158 } {32 32 63} 159 160 # Create a much smaller table t2 with only 32 elements 161 db eval { 162 CREATE TABLE t2(x INTEGER PRIMARY KEY, y INTEGER); 163 INSERT INTO t2 SELECT x, y FROM t1 WHERE x<32; 164 CREATE INDEX t2y ON t2(y); 165 CREATE VIRTUAL TABLE c2 166 USING transitive_closure(tablename=t2, idcolumn=x, parentcolumn=y); 167 } 168 169 # t2 full-table 170 do_execsql_test 2.1 { 171 SELECT count(*), min(id), max(id) FROM c2 WHERE root=1; 172 } {31 1 31} 173 # t2 root=10 174 do_execsql_test 2.2 { 175 SELECT id FROM c2 WHERE root=10; 176 } {10 20 21} 177 # t2 root=11 178 do_execsql_test 2.3 { 179 SELECT id FROM c2 WHERE root=12; 180 } {12 24 25} 181 # t2 root IN [10,12] 182 do_execsql_test 2.4 { 183 SELECT id FROM c2 WHERE root IN (10,12) ORDER BY id; 184 } {10 12 20 21 24 25} 185 # t2 root IN [10,12] (sorted) 186 do_execsql_test 2.5 { 187 SELECT id FROM c2 WHERE root IN (10,12) ORDER BY +id; 188 } {10 12 20 21 24 25} 189 190 # t2 c2up from 20 191 do_execsql_test 3.0 { 192 CREATE VIRTUAL TABLE c2up USING transitive_closure( 193 tablename = t2, 194 idcolumn = y, 195 parentcolumn = x 196 ); 197 SELECT id FROM c2up WHERE root=20; 198 } {1 2 5 10 20} 199 200 # cx as c2up 201 do_execsql_test 3.1 { 202 SELECT id FROM cx 203 WHERE root=20 204 AND tablename='t2' 205 AND idcolumn='y' 206 AND parentcolumn='x'; 207 } {1 2 5 10 20} 208 209 # t2 first cousins of 20 210 do_execsql_test 3.2 { 211 SELECT DISTINCT id FROM c2 212 WHERE root IN (SELECT id FROM c2up 213 WHERE root=20 AND depth<=2) 214 ORDER BY id; 215 } {5 10 11 20 21 22 23} 216 217 # t2 first cousins of 20 218 do_execsql_test 3.3 { 219 SELECT id FROM c2 220 WHERE root=(SELECT id FROM c2up 221 WHERE root=20 AND depth=2) 222 AND depth=2 223 EXCEPT 224 SELECT id FROM c2 225 WHERE root=(SELECT id FROM c2up 226 WHERE root=20 AND depth=1) 227 AND depth<=1 228 ORDER BY id; 229 } {22 23} 230 231 # missing tablename. 232 do_test 4.1 { 233 catchsql { 234 SELECT id FROM cx 235 WHERE root=20 236 AND tablename='t3' 237 AND idcolumn='y' 238 AND parentcolumn='x'; 239 } 240 } {1 {no such table: t3}} 241 242 # missing idcolumn 243 do_test 4.2 { 244 catchsql { 245 SELECT id FROM cx 246 WHERE root=20 247 AND tablename='t2' 248 AND idcolumn='xyz' 249 AND parentcolumn='x'; 250 } 251 } {1 {no such column: t2.xyz}} 252 253 # missing parentcolumn 254 do_test 4.3 { 255 catchsql { 256 SELECT id FROM cx 257 WHERE root=20 258 AND tablename='t2' 259 AND idcolumn='x' 260 AND parentcolumn='pqr'; 261 } 262 } {1 {no such column: t2.pqr}} 263 264 # generic closure 265 do_execsql_test 5.1 { 266 CREATE VIRTUAL TABLE temp.closure USING transitive_closure; 267 SELECT id FROM closure 268 WHERE root=1 269 AND depth=3 270 AND tablename='t1' 271 AND idcolumn='x' 272 AND parentcolumn='y' 273 ORDER BY id; 274 } {8 9 10 11 12 13 14 15} 275 276 #------------------------------------------------------------------------- 277 # At one point the following join query was causing a malfunction in 278 # xBestIndex. 279 # 280 do_execsql_test 6.0 { 281 CREATE TABLE t4 ( 282 id INTEGER PRIMARY KEY, 283 name TEXT NOT NULL, 284 parent_id INTEGER 285 ); 286 CREATE VIRTUAL TABLE vt4 USING transitive_closure ( 287 idcolumn=id, parentcolumn=parent_id, tablename=t4 288 ); 289 } 290 291 do_execsql_test 6.1 { 292 SELECT * FROM t4, vt4 WHERE t4.id = vt4.root AND vt4.id=4 AND vt4.depth=2; 293 } 294 295 finish_test