github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/exec/execbuilder/testdata/interleaved (about) 1 # LogicTest: local 2 3 # ------------------------------------------------------------------------------ 4 # Test index selection for deeply interleaved tables. 5 # These tests are in their own file because table IDs appear in EXPLAIN output. 6 # ------------------------------------------------------------------------------ 7 statement ok 8 CREATE TABLE level1 ( 9 k1 INT, 10 PRIMARY KEY (k1) 11 ) 12 13 statement ok 14 CREATE TABLE level2 ( 15 k1 INT, 16 PRIMARY KEY (k1), 17 CONSTRAINT fk1 FOREIGN KEY (k1) REFERENCES level1 18 ) INTERLEAVE IN PARENT level1 (k1) 19 20 statement ok 21 CREATE TABLE level3 ( 22 k1 INT, 23 k2 INT, 24 k3 INT, 25 PRIMARY KEY (k1, k2, k3), 26 CONSTRAINT fk2 FOREIGN KEY (k1) REFERENCES level2 27 ) INTERLEAVE IN PARENT level2 (k1) 28 29 statement ok 30 CREATE TABLE level4 ( 31 k1 INT, 32 k2 INT, 33 k3 INT, 34 FAMILY (k1, k2, k3), 35 PRIMARY KEY (k1, k2, k3), 36 CONSTRAINT fk3 FOREIGN KEY (k1, k2, k3) REFERENCES level3 37 ) INTERLEAVE IN PARENT level3 (k1, k2, k3) 38 39 query TTT 40 EXPLAIN SELECT * FROM level4 41 ---- 42 · distributed false 43 · vectorized true 44 scan · · 45 · table level4@primary 46 · spans FULL SCAN 47 48 # The span below ends at the end of the first index of table 53, and is not 49 # constraining the value of k2 or k3. This is confusing on first glance because 50 # the second interleave in the hierarchy doesn't contain any new primary key 51 # columns on top of the first interleave. 52 query TTT 53 EXPLAIN SELECT * FROM level4 WHERE k1 > 1 AND k1 < 3 54 ---- 55 · distributed false 56 · vectorized true 57 scan · · 58 · table level4@primary 59 · spans /2/#/54/1/#/55/1-/2/#/54/1/#/55/2 60 61 query TTT 62 EXPLAIN SELECT * FROM level4 WHERE k1 = 2 AND k2 > 10 AND k2 < 30 63 ---- 64 · distributed false 65 · vectorized true 66 scan · · 67 · table level4@primary 68 · spans /2/#/54/1/#/55/1/11-/2/#/54/1/#/55/1/30 69 70 query TTT 71 EXPLAIN SELECT * FROM level4 WHERE k1 = 2 AND k2 = 20 AND k3 > 100 AND k3 < 300 72 ---- 73 · distributed false 74 · vectorized true 75 scan · · 76 · table level4@primary 77 · spans /2/#/54/1/#/55/1/20/101/#/56/1-/2/#/54/1/#/55/1/20/299/#/56/1/# 78 · parallel · 79 80 query TTT 81 EXPLAIN SELECT * FROM level4 WHERE k1 = 2 AND k2 = 20 AND k3 = 200 82 ---- 83 · distributed false 84 · vectorized true 85 scan · · 86 · table level4@primary 87 · spans /2/#/54/1/#/55/1/20/200/#/56/1-/2/#/54/1/#/55/1/20/200/#/56/1/# 88 89 # ------------------------------------------------------------------------------ 90 # Trace of interleaved fetches from interesting interleaved hierarchy. 91 # ------------------------------------------------------------------------------ 92 # Grandparent table 93 statement ok 94 CREATE TABLE p2 (i INT PRIMARY KEY, s STRING) 95 96 # Two tables interleaved at the same level 97 statement ok 98 CREATE TABLE p1_0 ( 99 i INT, 100 s1 STRING, 101 s2 STRING, 102 d DECIMAL, 103 PRIMARY KEY (i, s1), 104 FAMILY (i, s1, s2), 105 FAMILY (d) 106 ) INTERLEAVE IN PARENT p2 (i) 107 108 statement ok 109 CREATE TABLE p1_1 ( 110 i INT PRIMARY KEY, 111 s1 STRING, 112 s2 STRING, 113 d DECIMAL 114 ) INTERLEAVE IN PARENT p2 (i) 115 116 # Two level deep interleave 117 statement ok 118 CREATE TABLE p0 ( 119 i INT, 120 s1 STRING, 121 s2 STRING, 122 d DECIMAL, 123 PRIMARY KEY (i, s1, s2) 124 ) INTERLEAVE IN PARENT p1_0 (i, s1) 125 126 statement ok 127 INSERT INTO p2 VALUES (2, '2'), (3, '3'), (5, '5'), (7, '7') 128 129 statement ok 130 INSERT INTO p1_0 VALUES (2, '2', '2.01', 2), (3, '3', '3.01', 3), (5, '5', NULL, NULL) 131 132 statement ok 133 INSERT INTO p1_1 VALUES (2, '2', '2.11', 2), (3, '3', '3.11', 3) 134 135 statement ok 136 INSERT INTO p0 VALUES (2, '2', '2.0', 2), (3, '3', '3.0', 3), (5, '5', '5.0', 5) 137 138 statement ok 139 SET tracing = on,kv,results; SELECT * FROM p1_0; SET tracing = off 140 141 query T 142 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 143 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 144 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 145 ---- 146 fetched: /p1_0/primary/2/'2'/s2 -> /'2.01' 147 fetched: /p1_0/primary/2/'2'/d -> 2 148 fetched: /p1_0/primary/3/'3'/s2 -> /'3.01' 149 fetched: /p1_0/primary/3/'3'/d -> 3 150 fetched: /p1_0/primary/5/'5' -> NULL 151 output row: [2 '2' '2.01' 2] 152 output row: [3 '3' '3.01' 3] 153 output row: [5 '5' NULL NULL] 154 155 # ------------------------------------------------------------------------------ 156 # Test interleaved delete fast path. 157 # ------------------------------------------------------------------------------ 158 subtest InterleavedDeleteFastPath 159 160 statement ok 161 CREATE TABLE a ( 162 a_id INT PRIMARY KEY 163 ); 164 165 statement ok 166 CREATE TABLE b( 167 b_id INT, 168 a_id INT, 169 FOREIGN KEY (a_id) REFERENCES a(a_id) ON UPDATE CASCADE ON DELETE CASCADE, 170 PRIMARY KEY(a_id, b_id), 171 FAMILY "primary" (a_id, b_id) 172 ) INTERLEAVE IN PARENT a(a_id) 173 174 statement ok 175 CREATE TABLE c( 176 c_id INT, 177 a_id INT, 178 b_id INT, 179 FOREIGN KEY (a_id, b_id) REFERENCES b(a_id, b_id) ON UPDATE CASCADE ON DELETE CASCADE, 180 PRIMARY KEY(a_id, b_id, c_id), 181 FAMILY "primary" (a_id, b_id, c_id) 182 ) INTERLEAVE IN PARENT b(a_id, b_id) 183 184 statement ok 185 INSERT INTO a SELECT generate_series(1,10); 186 INSERT INTO b(a_id, b_id) SELECT generate_series(1,10), 1 187 188 statement ok 189 DELETE FROM a WHERE a_id <= 2 190 191 query I 192 SELECT count(*) from a 193 ---- 194 8 195 196 query I 197 SELECT count(*) from b 198 ---- 199 8 200 201 query I colnames 202 SELECT * from a order by a_id 203 ---- 204 a_id 205 3 206 4 207 5 208 6 209 7 210 8 211 9 212 10 213 214 query II colnames 215 SELECT * from b order by a_id, b_id 216 ---- 217 b_id a_id 218 1 3 219 1 4 220 1 5 221 1 6 222 1 7 223 1 8 224 1 9 225 1 10 226 227 statement ok 228 INSERT INTO b(a_id, b_id) select a.a_id, 2 from a; 229 INSERT INTO c(a_id, b_id, c_id) select a.a_id, b.b_id, 1 from a, b where a.a_id = b.a_id 230 231 query III colnames 232 SELECT * from c order by a_id, b_id, c_id 233 ---- 234 c_id a_id b_id 235 1 3 1 236 1 3 2 237 1 4 1 238 1 4 2 239 1 5 1 240 1 5 2 241 1 6 1 242 1 6 2 243 1 7 1 244 1 7 2 245 1 8 1 246 1 8 2 247 1 9 1 248 1 9 2 249 1 10 1 250 1 10 2 251 252 statement ok 253 SET TRACING = on,kv,results; DELETE FROM a where a_id <= 7 and a_id >= 5; SET tracing = off 254 255 # Only look at traces from SQL land. 256 query T 257 SELECT message FROM [SHOW KV TRACE FOR SESSION] 258 WHERE operation='flow' OR operation='exec stmt' 259 ---- 260 DelRange /Table/61/1/5 - /Table/61/1/7/NULL 261 fast path completed 262 rows affected: 3 263 264 query II colnames 265 select * from b order by a_id, b_id 266 ---- 267 b_id a_id 268 1 3 269 2 3 270 1 4 271 2 4 272 1 8 273 2 8 274 1 9 275 2 9 276 1 10 277 2 10 278 279 query III colnames 280 select * from c order by a_id, b_id, c_id 281 ---- 282 c_id a_id b_id 283 1 3 1 284 1 3 2 285 1 4 1 286 1 4 2 287 1 8 1 288 1 8 2 289 1 9 1 290 1 9 2 291 1 10 1 292 1 10 2 293 294 statement ok 295 SET TRACING = on,kv,results; DELETE FROM a; SET tracing = off 296 297 query T 298 SELECT message FROM [SHOW KV TRACE FOR SESSION] 299 WHERE operation='flow' OR operation='exec stmt' 300 ---- 301 DelRange /Table/61/1 - /Table/61/3 302 fast path completed 303 rows affected: 5 304 305 query II colnames 306 select * from b order by a_id, b_id 307 ---- 308 b_id a_id 309 310 query III colnames 311 select * from c order by a_id, b_id, c_id 312 ---- 313 c_id a_id b_id 314 315 statement ok 316 INSERT INTO a SELECT generate_series(1,10); 317 INSERT INTO b(a_id, b_id) SELECT generate_series(1,10), 1 318 319 statement ok 320 SET TRACING = on,kv,results; 321 322 query I 323 delete from a returning * 324 ---- 325 1 326 2 327 3 328 4 329 5 330 6 331 7 332 8 333 9 334 10 335 336 statement ok 337 SET TRACING=off; 338 339 query T 340 SELECT message FROM [SHOW TRACE FOR SESSION] 341 WHERE message LIKE '%executing cascade %' OR message LIKE 'output row%' OR 342 message LIKE 'Del %' OR message LIKE 'rows affected%' 343 ---- 344 Del /Table/61/1/1/0 345 Del /Table/61/1/2/0 346 Del /Table/61/1/3/0 347 Del /Table/61/1/4/0 348 Del /Table/61/1/5/0 349 Del /Table/61/1/6/0 350 Del /Table/61/1/7/0 351 Del /Table/61/1/8/0 352 Del /Table/61/1/9/0 353 Del /Table/61/1/10/0 354 output row: [1] 355 output row: [2] 356 output row: [3] 357 output row: [4] 358 output row: [5] 359 output row: [6] 360 output row: [7] 361 output row: [8] 362 output row: [9] 363 output row: [10] 364 executing cascade for constraint fk_a_id_ref_a 365 Del /Table/61/1/1/#/62/1/1/0 366 Del /Table/61/1/2/#/62/1/1/0 367 Del /Table/61/1/3/#/62/1/1/0 368 Del /Table/61/1/4/#/62/1/1/0 369 Del /Table/61/1/5/#/62/1/1/0 370 Del /Table/61/1/6/#/62/1/1/0 371 Del /Table/61/1/7/#/62/1/1/0 372 Del /Table/61/1/8/#/62/1/1/0 373 Del /Table/61/1/9/#/62/1/1/0 374 Del /Table/61/1/10/#/62/1/1/0 375 executing cascade for constraint fk_a_id_ref_b 376 rows affected: 10 377 378 statement ok 379 DROP TABLE c; DROP TABLE b; DROP TABLE a