github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/distsql_interleaved_join (about) 1 # LogicTest: 5node-default-configs 2 3 # The following tables form the interleaved hierarchy: 4 # name: primary key: # rows: 'a' = id mod X : 5 # parent1 (pid1) 40 8 6 # child1 (pid1, cid1) 150 66 7 # grandchild1 (pid1, cid1, gcid1) 410 201 8 # child2 (pid1, cid2, cid3) 15 7 9 # grandchild2 (pid1, cid2, cid3, gcid2) 51 13 10 # parent2 (pid2) 5 2 11 # Additional rows in child1, child2, and grandchild1 with no corresponding 12 # parent row are also inserted. 13 # 14 # All IDs belonging to a table (pid1 --> parent1, cid1 --> child1, cid2,cid3 15 # --> child2, etc.) start from 1 up to (# rows). 16 # Foreign keys are modded by their ancestor's (# rows). For example, for child1 17 # row with cid1=500, we take ((cid1-1) % 200 + 1) = 100 as pid1. 18 # One exception is cid3, which is taken as cid2 % 15. 19 # There's a column 'a' that's modded by a factor. 20 # 21 # This allows us to test the following edge cases (in order of tests): 22 # - one-to-many (parent1 - child1) 23 # - one-to-one and one-to-none (parent1 - child2) 24 # - parent-grandchild (parent1 - grandchild1) 25 # - multiple interleaved columns (child2 - grandchild2) 26 # - additional ancestor above (child2 - grandchild2) 27 # - no interleaved relationship (parent1 - parent2, parent2 - child1) 28 # - TODO(richardwu): sibling-sibling (child1 - child2) 29 30 statement ok 31 SET CLUSTER SETTING sql.stats.automatic_collection.enabled = false 32 33 ################# 34 # Create tables # 35 ################# 36 37 statement ok 38 CREATE TABLE parent1 (pid1 INT PRIMARY KEY, pa1 INT) 39 40 statement ok 41 CREATE TABLE parent2 (pid2 INT PRIMARY KEY, pa2 INT) 42 43 statement ok 44 CREATE TABLE child1 ( 45 pid1 INT, 46 cid1 INT, 47 ca1 INT, 48 PRIMARY KEY(pid1, cid1) 49 ) 50 INTERLEAVE IN PARENT parent1 (pid1) 51 52 statement ok 53 CREATE TABLE child2 ( 54 pid1 INT, 55 cid2 INT, 56 cid3 INT, 57 ca2 INT, 58 PRIMARY KEY(pid1, cid2, cid3) 59 ) 60 INTERLEAVE IN PARENT parent1 (pid1) 61 62 statement ok 63 CREATE TABLE grandchild1 ( 64 pid1 INT, 65 cid1 INT, 66 gcid1 INT, 67 gca1 INT, 68 PRIMARY KEY(pid1, cid1, gcid1) 69 ) 70 INTERLEAVE IN PARENT child1 (pid1, cid1) 71 72 # No foreign key since we are permitting the rows to overflow out of child2 73 # for pid1 > 15. 74 statement ok 75 CREATE TABLE grandchild2 ( 76 pid1 INT, 77 cid2 INT, 78 cid3 INT, 79 gcid2 INT, 80 gca2 INT, 81 PRIMARY KEY(pid1, cid2, cid3, gcid2) 82 ) 83 INTERLEAVE IN PARENT child2 (pid1, cid2, cid3) 84 85 #################### 86 # Insert some rows # 87 #################### 88 89 statement ok 90 INSERT INTO parent1 SELECT 91 pid, 92 mod(pid, 8) 93 FROM 94 generate_series(1, 40) AS ID(pid) 95 96 statement ok 97 INSERT INTO parent2 SELECT 98 pid, 99 mod(pid, 2) 100 FROM 101 generate_series(1, 5) AS ID(pid) 102 103 # child1 has more rows than parent1. 104 statement ok 105 INSERT INTO child1 SELECT 106 mod(cid-1, 40) + 1, 107 cid, 108 mod(cid, 66) 109 FROM 110 generate_series(1, 150) AS ID(cid) 111 112 # Insert additional rows with no correspond parent rows to check for correctness. 113 statement ok 114 INSERT INTO child1 VALUES 115 (-1, -1, -1), 116 (0, 0, 0), 117 (41, 41, 41), 118 (151, 151, 19), 119 (160, 160, 28) 120 121 # child2 has fewer rows than parent1. 122 statement ok 123 INSERT INTO child2 SELECT 124 mod(cid-1, 40) + 1, 125 cid, 126 mod(cid, 14), 127 mod(cid, 7) 128 FROM 129 generate_series(1, 15) AS ID(cid) 130 131 statement ok 132 INSERT INTO child2 VALUES 133 (-1, -1, -1, -1), 134 (0, 0, 0, 0), 135 (16, 16, 2, 2), 136 (20, 20, 6, 6) 137 138 statement ok 139 INSERT INTO grandchild1 SELECT 140 mod(mod(gcid-1, 150), 40) + 1, 141 mod(gcid-1, 150) + 1, 142 gcid, 143 mod(gcid, 201) 144 FROM 145 generate_series(1, 410) AS ID(gcid) 146 147 statement ok 148 INSERT INTO grandchild1 VALUES 149 (-1, -1, -1, -1), 150 (0, 0, 0, 0), 151 (200, 200, 200, 200), 152 (411, 411, 411, 9) 153 154 155 # We let grandchild2.pid1 exceed child2.pid1 (one of the interleaved keys). 156 # So instead of 157 # (gcid1 - 1) % 15 % 40 + 1 158 # we choose to only mod by 40 (nrows of parent1) instead of first modding 159 # by 15 (nrows of child2). 160 statement ok 161 INSERT INTO grandchild2 SELECT 162 mod(gcid-1, 40) + 1, 163 mod(gcid-1, 15) + 1, 164 mod(mod(gcid-1, 15) + 1, 14), 165 gcid, 166 mod(gcid, 13) 167 FROM 168 generate_series(1, 51) AS ID(gcid) 169 170 #################### 171 # Split our ranges # 172 #################### 173 174 # Split at parent1 key into five parts. 175 statement ok 176 ALTER TABLE parent1 SPLIT AT SELECT i FROM generate_series(8, 32, 8) AS g(i) 177 178 # Split at child1 keys in between parent1 parts (total 10 parts). 179 statement ok 180 ALTER TABLE child1 SPLIT AT SELECT pid1, pid1 + 40 FROM 181 generate_series(4, 36, 8) AS g(pid1) 182 183 # Split at grandchild2 keys in between the 10 parts (total 20 parts). 184 statement ok 185 ALTER TABLE grandchild2 SPLIT AT SELECT pid1, pid1 + 40, pid1, pid1 FROM 186 generate_series(2, 38, 4) AS g(pid1) 187 188 # Relocate the twenty parts to the five nodes. 189 statement ok 190 ALTER TABLE grandchild2 EXPERIMENTAL_RELOCATE 191 SELECT ARRAY[((i-1)/2)::INT%5+1], i, i+20, i, i FROM generate_series(1, 39, 2) AS g(i) 192 193 # Verify data placement. 194 query TTTI colnames,rowsort 195 SELECT start_key, end_key, replicas, lease_holder from [SHOW RANGES FROM TABLE parent1] 196 ---- 197 start_key end_key replicas lease_holder 198 NULL /2/#/56/1/42/2/#/58/1/2 {1} 1 199 /2/#/56/1/42/2/#/58/1/2 /4/#/55/1/44 {2} 2 200 /4/#/55/1/44 /6/#/56/1/46/6/#/58/1/6 {3} 3 201 /6/#/56/1/46/6/#/58/1/6 /8 {4} 4 202 /8 /10/#/56/1/50/10/#/58/1/10 {5} 5 203 /10/#/56/1/50/10/#/58/1/10 /12/#/55/1/52 {1} 1 204 /12/#/55/1/52 /14/#/56/1/54/14/#/58/1/14 {2} 2 205 /14/#/56/1/54/14/#/58/1/14 /16 {3} 3 206 /16 /18/#/56/1/58/18/#/58/1/18 {4} 4 207 /18/#/56/1/58/18/#/58/1/18 /20/#/55/1/60 {5} 5 208 /20/#/55/1/60 /22/#/56/1/62/22/#/58/1/22 {1} 1 209 /22/#/56/1/62/22/#/58/1/22 /24 {2} 2 210 /24 /26/#/56/1/66/26/#/58/1/26 {3} 3 211 /26/#/56/1/66/26/#/58/1/26 /28/#/55/1/68 {4} 4 212 /28/#/55/1/68 /30/#/56/1/70/30/#/58/1/30 {5} 5 213 /30/#/56/1/70/30/#/58/1/30 /32 {1} 1 214 /32 /34/#/56/1/74/34/#/58/1/34 {2} 2 215 /34/#/56/1/74/34/#/58/1/34 /36/#/55/1/76 {3} 3 216 /36/#/55/1/76 /38/#/56/1/78/38/#/58/1/38 {4} 4 217 /38/#/56/1/78/38/#/58/1/38 NULL {5} 5 218 219 query TTTI colnames,rowsort 220 SELECT start_key, end_key, replicas, lease_holder from [SHOW RANGES FROM TABLE child1] 221 ---- 222 start_key end_key replicas lease_holder 223 NULL /2/#/56/1/42/2/#/58/1/2 {1} 1 224 /2/#/56/1/42/2/#/58/1/2 /4/#/55/1/44 {2} 2 225 /4/#/55/1/44 /6/#/56/1/46/6/#/58/1/6 {3} 3 226 /6/#/56/1/46/6/#/58/1/6 /8 {4} 4 227 /8 /10/#/56/1/50/10/#/58/1/10 {5} 5 228 /10/#/56/1/50/10/#/58/1/10 /12/#/55/1/52 {1} 1 229 /12/#/55/1/52 /14/#/56/1/54/14/#/58/1/14 {2} 2 230 /14/#/56/1/54/14/#/58/1/14 /16 {3} 3 231 /16 /18/#/56/1/58/18/#/58/1/18 {4} 4 232 /18/#/56/1/58/18/#/58/1/18 /20/#/55/1/60 {5} 5 233 /20/#/55/1/60 /22/#/56/1/62/22/#/58/1/22 {1} 1 234 /22/#/56/1/62/22/#/58/1/22 /24 {2} 2 235 /24 /26/#/56/1/66/26/#/58/1/26 {3} 3 236 /26/#/56/1/66/26/#/58/1/26 /28/#/55/1/68 {4} 4 237 /28/#/55/1/68 /30/#/56/1/70/30/#/58/1/30 {5} 5 238 /30/#/56/1/70/30/#/58/1/30 /32 {1} 1 239 /32 /34/#/56/1/74/34/#/58/1/34 {2} 2 240 /34/#/56/1/74/34/#/58/1/34 /36/#/55/1/76 {3} 3 241 /36/#/55/1/76 /38/#/56/1/78/38/#/58/1/38 {4} 4 242 /38/#/56/1/78/38/#/58/1/38 NULL {5} 5 243 244 query TTTI colnames,rowsort 245 SELECT start_key, end_key, replicas, lease_holder from [SHOW RANGES FROM TABLE grandchild1] 246 ---- 247 start_key end_key replicas lease_holder 248 NULL /2/#/56/1/42/2/#/58/1/2 {1} 1 249 /2/#/56/1/42/2/#/58/1/2 /4/#/55/1/44 {2} 2 250 /4/#/55/1/44 /6/#/56/1/46/6/#/58/1/6 {3} 3 251 /6/#/56/1/46/6/#/58/1/6 /8 {4} 4 252 /8 /10/#/56/1/50/10/#/58/1/10 {5} 5 253 /10/#/56/1/50/10/#/58/1/10 /12/#/55/1/52 {1} 1 254 /12/#/55/1/52 /14/#/56/1/54/14/#/58/1/14 {2} 2 255 /14/#/56/1/54/14/#/58/1/14 /16 {3} 3 256 /16 /18/#/56/1/58/18/#/58/1/18 {4} 4 257 /18/#/56/1/58/18/#/58/1/18 /20/#/55/1/60 {5} 5 258 /20/#/55/1/60 /22/#/56/1/62/22/#/58/1/22 {1} 1 259 /22/#/56/1/62/22/#/58/1/22 /24 {2} 2 260 /24 /26/#/56/1/66/26/#/58/1/26 {3} 3 261 /26/#/56/1/66/26/#/58/1/26 /28/#/55/1/68 {4} 4 262 /28/#/55/1/68 /30/#/56/1/70/30/#/58/1/30 {5} 5 263 /30/#/56/1/70/30/#/58/1/30 /32 {1} 1 264 /32 /34/#/56/1/74/34/#/58/1/34 {2} 2 265 /34/#/56/1/74/34/#/58/1/34 /36/#/55/1/76 {3} 3 266 /36/#/55/1/76 /38/#/56/1/78/38/#/58/1/38 {4} 4 267 /38/#/56/1/78/38/#/58/1/38 NULL {5} 5 268 269 statement ok 270 SET CLUSTER SETTING sql.distsql.interleaved_joins.enabled = true; 271 272 ##################### 273 # Interleaved joins # 274 ##################### 275 276 # Select over two ranges for parent/child with split at children key. 277 # Returns: 278 # pid1 pa1 cid1 ca1 279 # (pid1 % 8) (cid1 % 66) 280 query IIII rowsort,colnames 281 SELECT * FROM parent1 JOIN child1 USING(pid1) WHERE pid1 >= 3 AND pid1 <= 5 282 ---- 283 pid1 pa1 cid1 ca1 284 3 3 3 3 285 3 3 43 43 286 3 3 83 17 287 3 3 123 57 288 4 4 4 4 289 4 4 44 44 290 4 4 84 18 291 4 4 124 58 292 5 5 5 5 293 5 5 45 45 294 5 5 85 19 295 5 5 125 59 296 297 # Swap parent1 and child1 tables. 298 299 query IIII rowsort,colnames 300 SELECT * FROM child1 JOIN parent1 USING(pid1) WHERE pid1 >= 3 AND pid1 <= 5 301 ---- 302 pid1 cid1 ca1 pa1 303 3 3 3 3 304 3 43 43 3 305 3 83 17 3 306 3 123 57 3 307 4 4 4 4 308 4 44 44 4 309 4 84 18 4 310 4 124 58 4 311 5 5 5 5 312 5 45 45 5 313 5 85 19 5 314 5 125 59 5 315 316 # Select over two ranges for parent/child with split at grandchild key. 317 # Also, rows with pid1 <= 30 should have 4 rows whereas pid1 > 30 should 318 # have 3 rows. 319 # Returns: 320 # parent1.pid1 pa1 child1.pid1 cid1 ca1 321 # (pid1 % 8) (cid1 % 66) 322 query IIIII colnames 323 SELECT * FROM parent1 JOIN child1 ON parent1.pid1 = child1.pid1 WHERE parent1.pid1 >= 29 AND parent1.pid1 <= 31 ORDER BY parent1.pid1 324 ---- 325 pid1 pa1 pid1 cid1 ca1 326 29 5 29 29 29 327 29 5 29 69 3 328 29 5 29 109 43 329 29 5 29 149 17 330 30 6 30 30 30 331 30 6 30 70 4 332 30 6 30 110 44 333 30 6 30 150 18 334 31 7 31 31 31 335 31 7 31 71 5 336 31 7 31 111 45 337 338 # parent-child where pid1 <= 15 have one joined row and pid1 > 15 have no 339 # joined rows (since child2 only has 15 rows up to pid1 = 15). 340 # Returns: 341 # pid1 pa1 cid2 cid3 ca2 342 # (pid1 % 8) (cid2 % 14) (cid2 % 7) 343 query IIIII rowsort,colnames 344 SELECT * FROM parent1 JOIN child2 USING(pid1) WHERE pid1 >= 12 345 ---- 346 pid1 pa1 cid2 cid3 ca2 347 12 4 12 12 5 348 13 5 13 13 6 349 14 6 14 0 0 350 15 7 15 1 1 351 16 0 16 2 2 352 20 4 20 6 6 353 354 # Single gateway node query (node 1). 355 # Returns: 356 # pid1 pa1 cid2 cid3 ca2 357 # (pid1 % 8) (cid2 % 14) (cid2 % 7) 358 # Note pid=21, 31 has no joined rows since child2 only has pid1 <= 15. 359 query IIIII rowsort,colnames 360 SELECT * FROM parent1 JOIN child2 USING(pid1) WHERE pid1 IN (1, 11, 21, 31) 361 ---- 362 pid1 pa1 cid2 cid3 ca2 363 1 1 1 1 1 364 11 3 11 11 4 365 366 # Parent-grandchild. 367 # Returns: 368 # pid1 pa2 cid2 cid3 gcid2 gca2 369 # (pid1 % 8) (cid2 % 14) (gcid2 % 13) 370 # Where clause is on ranges that overlap children, grandchildren, and parent 371 # key splits, respectively. 372 # Rows with pid >= 11 have only one joined row since there are only 51 373 # granchild2 rows. 374 query IIIIII rowsort,colnames 375 SELECT * FROM parent1 JOIN grandchild2 USING(pid1) WHERE 376 pid1 >= 11 AND pid1 <= 13 377 OR pid1 >= 19 AND pid1 <= 21 378 OR pid1 >= 31 AND pid1 <= 33 379 ---- 380 pid1 pa1 cid2 cid3 gcid2 gca2 381 11 3 6 6 51 12 382 11 3 11 11 11 11 383 12 4 12 12 12 12 384 13 5 13 13 13 0 385 19 3 4 4 19 6 386 20 4 5 5 20 7 387 21 5 6 6 21 8 388 31 7 1 1 31 5 389 32 0 2 2 32 6 390 33 1 3 3 33 7 391 392 # Swap parent1 and grandchild2 positions. 393 query IIIIII rowsort,colnames 394 SELECT * FROM grandchild2 JOIN parent1 USING(pid1) WHERE 395 pid1 >= 11 AND pid1 <= 13 396 OR pid1 >= 19 AND pid1 <= 21 397 OR pid1 >= 31 AND pid1 <= 33 398 ---- 399 pid1 cid2 cid3 gcid2 gca2 pa1 400 11 6 6 51 12 3 401 11 11 11 11 11 3 402 12 12 12 12 12 4 403 13 13 13 13 0 5 404 19 4 4 19 6 3 405 20 5 5 20 7 4 406 21 6 6 21 8 5 407 31 1 1 31 5 7 408 32 2 2 32 6 0 409 33 3 3 33 7 1 410 411 # Join on multiple interleaved columns with an overarching ancestor (parent1). 412 # Returns: 413 # child2.pid1 gc2.pid1 child2.cid2 gc2.cid2 child2.cid3 gc2.cid3 child2.ca2 gcid2 gca2 414 # (gcid2 % 40) (cid2 % 14) (gcid2 % 13) 415 query IIIIIIIII colnames,rowsort 416 SELECT * FROM child2 JOIN grandchild2 ON 417 child2.pid1=grandchild2.pid1 418 AND child2.cid2=grandchild2.cid2 419 AND child2.cid3=grandchild2.cid3 420 WHERE 421 child2.pid1 >= 5 AND child2.pid1 <= 7 422 OR child2.cid2 >= 12 AND child2.cid2 <= 14 423 OR gcid2 >= 49 AND gcid2 <= 51 424 ---- 425 pid1 cid2 cid3 ca2 pid1 cid2 cid3 gcid2 gca2 426 5 5 5 5 5 5 5 5 5 427 6 6 6 6 6 6 6 6 6 428 7 7 7 0 7 7 7 7 7 429 12 12 12 5 12 12 12 12 12 430 13 13 13 6 13 13 13 13 0 431 14 14 0 0 14 14 0 14 1 432 433 # Aggregation over parent and child keys. 434 # There are 4 rows for each 10 <= pid1 <= 30 and 3 rows for each 30 < pid1 <= 435 # 39. 436 # We thus have 3 arithmetic series of 10 + ... + 39 and 1 arithmetic series 437 # of 10 + ... + 30 or 438 # sum(pid1) = 3 * (39 - 10 + 1) * (10 + 39)/2 + (30 - 10 + 1) * (10 + 30)/2 = 2625 439 # For sum(cid1), we notice that pid1 = cid1 % 40, thus for every additional 440 # round of rows under a pid1, cid1 is increased by 40. 441 # For each additional round up to the 3rd (2 rounds after the first where 50 <= cid1 <= 79, 442 # 90 <= cid1 <= 119) , we have an additional 443 # 40 * (1 + 2) * (39 - 10 + 1) = 3600 444 # For the 4th round, we have 150 - 130 + 1 = 21 rows (130 <= cid1 <= 150) each 445 # additional row adds 120, thus 446 # sum(cid1) = sum(pid1) + 3600 + 21 * 120 = 8745 447 # For each 448 query RR 449 SELECT sum(parent1.pid1), sum(child1.cid1) FROM parent1 JOIN child1 USING(pid1) WHERE 450 pid1 >= 10 AND pid1 <= 39 451 ---- 452 2625 8745 453 454 ############### 455 # Outer joins # 456 ############### 457 458 # The schema/values for each table are as follows: 459 # Table: pkey: pkey values (same): values: 460 # outer_p1 (pid1) {1, 2, 3, ... 20} 100 + pkey 461 # outer_c1 (pid1, cid1, cid2) {2, 4, 6, ... 28} 200 + pkey 462 # outer_gc1 (pid1, cid1, cid2, gcid1) {4, 8, 12, ... 36} 300 + pkey 463 464 # Split between 4 nodes based on pkey value (p): 465 # node 1: p - 1 mod 20 ∈ [1...5) 466 # node 2: p - 1 mod 20 ∈ [5...10) 467 # node 3: p - 1 mod 20 ∈ [10...15) 468 # node 4: p - 1 mod 20 ∈ [15...20) 469 470 statement ok 471 CREATE TABLE outer_p1 ( 472 pid1 INT PRIMARY KEY, 473 pa1 INT 474 ) 475 476 statement ok 477 CREATE TABLE outer_c1 ( 478 pid1 INT, 479 cid1 INT, 480 cid2 INT, 481 ca1 INT, 482 PRIMARY KEY (pid1, cid1, cid2) 483 ) INTERLEAVE IN PARENT outer_p1 (pid1) 484 485 statement ok 486 CREATE TABLE outer_gc1 ( 487 pid1 INT, 488 cid1 INT, 489 cid2 INT, 490 gcid1 INT, 491 gca1 INT, 492 PRIMARY KEY (pid1, cid1, cid2, gcid1) 493 ) INTERLEAVE IN PARENT outer_c1 (pid1, cid1, cid2) 494 495 statement ok 496 INSERT INTO outer_p1 497 SELECT i, i+100 FROM generate_series(1, 20) AS g(i) 498 499 statement ok 500 INSERT INTO outer_c1 501 SELECT i, i, i, i+200 FROM generate_series(-2, 28, 2) AS g(i) 502 503 statement ok 504 INSERT INTO outer_gc1 505 SELECT i, i, i, i, i+300 FROM generate_series(-4, 36, 4) AS g(i) 506 507 statement ok 508 ALTER TABLE outer_p1 SPLIT AT 509 SELECT i FROM generate_series(0, 40, 5) AS g(i) 510 511 statement ok 512 ALTER TABLE outer_p1 EXPERIMENTAL_RELOCATE 513 SELECT ARRAY[(((i-3)/5)%4)::INT + 1], i FROM generate_series(3, 40, 5) AS g(i) 514 515 query TTTI colnames,rowsort 516 SELECT start_key, end_key, replicas, lease_holder from [SHOW RANGES FROM TABLE outer_p1] 517 ---- 518 start_key end_key replicas lease_holder 519 NULL /0 {5} 5 520 /0 /5 {1} 1 521 /5 /10 {2} 2 522 /10 /15 {3} 3 523 /15 /20 {4} 4 524 /20 /25 {1} 1 525 /25 /30 {2} 2 526 /30 /35 {3} 3 527 /35 /40 {4} 4 528 /40 NULL {5} 5 529 530 ### Begin OUTER queries 531 532 query IIIII rowsort,colnames 533 SELECT * FROM outer_p1 FULL OUTER JOIN outer_c1 USING (pid1) 534 ---- 535 pid1 pa1 cid1 cid2 ca1 536 -2 NULL -2 -2 198 537 0 NULL 0 0 200 538 1 101 NULL NULL NULL 539 2 102 2 2 202 540 3 103 NULL NULL NULL 541 4 104 4 4 204 542 5 105 NULL NULL NULL 543 6 106 6 6 206 544 7 107 NULL NULL NULL 545 8 108 8 8 208 546 9 109 NULL NULL NULL 547 10 110 10 10 210 548 11 111 NULL NULL NULL 549 12 112 12 12 212 550 13 113 NULL NULL NULL 551 14 114 14 14 214 552 15 115 NULL NULL NULL 553 16 116 16 16 216 554 17 117 NULL NULL NULL 555 18 118 18 18 218 556 19 119 NULL NULL NULL 557 20 120 20 20 220 558 22 NULL 22 22 222 559 24 NULL 24 24 224 560 26 NULL 26 26 226 561 28 NULL 28 28 228 562 563 query IIIIII rowsort,colnames 564 SELECT * FROM outer_gc1 FULL OUTER JOIN outer_c1 USING (pid1, cid1, cid2) 565 ---- 566 pid1 cid1 cid2 gcid1 gca1 ca1 567 -4 -4 -4 -4 296 NULL 568 -2 -2 -2 NULL NULL 198 569 0 0 0 0 300 200 570 2 2 2 NULL NULL 202 571 4 4 4 4 304 204 572 6 6 6 NULL NULL 206 573 8 8 8 8 308 208 574 10 10 10 NULL NULL 210 575 12 12 12 12 312 212 576 14 14 14 NULL NULL 214 577 16 16 16 16 316 216 578 18 18 18 NULL NULL 218 579 20 20 20 20 320 220 580 22 22 22 NULL NULL 222 581 24 24 24 24 324 224 582 26 26 26 NULL NULL 226 583 28 28 28 28 328 228 584 32 32 32 32 332 NULL 585 36 36 36 36 336 NULL 586 587 query IIIII rowsort,colnames 588 SELECT * FROM outer_c1 LEFT OUTER JOIN outer_p1 USING (pid1) WHERE pid1 >= 0 AND pid1 < 40 589 ---- 590 pid1 cid1 cid2 ca1 pa1 591 0 0 0 200 NULL 592 2 2 2 202 102 593 4 4 4 204 104 594 6 6 6 206 106 595 8 8 8 208 108 596 10 10 10 210 110 597 12 12 12 212 112 598 14 14 14 214 114 599 16 16 16 216 116 600 18 18 18 218 118 601 20 20 20 220 120 602 22 22 22 222 NULL 603 24 24 24 224 NULL 604 26 26 26 226 NULL 605 28 28 28 228 NULL 606 607 query IIIIII rowsort,colnames 608 SELECT * FROM outer_p1 RIGHT OUTER JOIN outer_gc1 USING (pid1) WHERE pid1 >= 1 AND pid1 <= 20 609 ---- 610 pid1 pa1 cid1 cid2 gcid1 gca1 611 4 104 4 4 4 304 612 8 108 8 8 8 308 613 12 112 12 12 12 312 614 16 116 16 16 16 316 615 20 120 20 20 20 320 616 617 # Regression test for #22655. 618 619 statement ok 620 CREATE TABLE a (a STRING, b STRING, PRIMARY KEY (a, b)) 621 622 statement ok 623 CREATE TABLE b (a STRING, b STRING, PRIMARY KEY (a, b)) INTERLEAVE IN PARENT a (a, b) 624 625 statement ok 626 SELECT * FROM a JOIN b ON a.a=b.a AND a.b=b.b WHERE a.a='foo' 627 628 subtest ParentChildDifferentSize 629 # Regression test for #22647. Test when child is a few columns larger than parent. 630 statement ok 631 CREATE TABLE small_parent (a STRING PRIMARY KEY, b STRING); INSERT INTO small_parent VALUES ('first', 'second') 632 633 statement ok 634 CREATE TABLE large_child (a STRING PRIMARY KEY, c STRING, d STRING, e STRING, f STRING) INTERLEAVE IN PARENT small_parent (a) 635 636 statement ok 637 INSERT INTO large_child VALUES ('first', 'second_child', 'third_child', 'fourth_child', 'fifth_child') 638 639 query TTTTTT 640 SELECT * FROM large_child JOIN small_parent USING (a) 641 ---- 642 first second_child third_child fourth_child fifth_child second 643 644 # Test with composite keys. 645 statement ok 646 CREATE TABLE small_parent_ck (a STRING, b STRING, c STRING, PRIMARY KEY (a, b)); INSERT INTO small_parent_ck VALUES ('first', 'second', 'third') 647 648 statement ok 649 CREATE TABLE large_child_ck (a STRING, b STRING, d STRING, e STRING, f STRING, PRIMARY KEY (a, b, d)) INTERLEAVE IN PARENT small_parent_ck (a, b) 650 651 statement ok 652 INSERT INTO large_child_ck VALUES ('first', 'second', 'third_child', 'fourth_child', 'fifth_child') 653 654 query TTTTTTT 655 SELECT * FROM large_child_ck JOIN small_parent_ck USING (a) 656 ---- 657 first second third_child fourth_child fifth_child second third 658 659 660 # Test with families. 661 statement ok 662 CREATE TABLE small_parent_fam (a STRING, b STRING, c STRING, PRIMARY KEY (a, b)); INSERT INTO small_parent_fam VALUES ('first', 'second', 'third') 663 664 statement ok 665 CREATE TABLE large_child_fam ( 666 a STRING, 667 b STRING, 668 d STRING, 669 e STRING, 670 f STRING, 671 PRIMARY KEY (a, b, d), 672 FAMILY f1 (a, b, d, e), 673 FAMILY f2 (f) 674 ) INTERLEAVE IN PARENT small_parent_fam (a, b) 675 676 statement ok 677 INSERT INTO large_child_fam VALUES ('first', 'second', 'third_child', 'fourth_child', 'fifth_child') 678 679 query TTTTTTT 680 SELECT * FROM large_child_fam JOIN small_parent_fam USING (a) 681 ---- 682 first second third_child fourth_child fifth_child second third 683 684 685 # Test with parent being much larger than child. 686 statement ok 687 CREATE TABLE large_parent_fam ( 688 a STRING, 689 b STRING, 690 c STRING, 691 d STRING, 692 e STRING, 693 f STRING, 694 PRIMARY KEY (a, b), 695 FAMILY f1 (a, b, c, d), 696 FAMILY f2 (e, f) 697 ) 698 699 statement ok 700 INSERT INTO large_parent_fam VALUES ('first', 'second', 'third', 'fourth', 'fifth', 'sixth') 701 702 statement ok 703 CREATE TABLE small_child_fam ( 704 a STRING, 705 b STRING, 706 g STRING, 707 PRIMARY KEY (a, b) 708 ) INTERLEAVE IN PARENT large_parent_fam (a, b) 709 710 statement ok 711 INSERT INTO small_child_fam VALUES ('first', 'second', 'third_child') 712 713 query TTTTTTTT 714 SELECT * FROM small_child_fam JOIN large_parent_fam USING (a) 715 ---- 716 first second third_child second third fourth fifth sixth