github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/exec/execbuilder/testdata/select_index (about) 1 # LogicTest: local 2 3 statement ok 4 CREATE TABLE t ( 5 a INT, 6 b VARCHAR, 7 c INT, 8 d VARCHAR, 9 PRIMARY KEY (a, b), 10 INDEX bc (b, c), 11 INDEX dc (d, c), 12 INDEX a_desc (a DESC), 13 FAMILY (a, b), 14 FAMILY (c), 15 FAMILY (d) 16 ) 17 18 statement ok 19 INSERT INTO t VALUES 20 (1, 'one', 11, 'foo'), 21 (2, 'two', 22, 'bar'), 22 (3, 'three', 33, 'blah') 23 24 statement ok 25 SET tracing = on,kv,results; SELECT * FROM t WHERE a = 2; SET tracing = off 26 27 query T 28 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 29 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 30 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 31 ---- 32 fetched: /t/primary/2/'two' -> NULL 33 fetched: /t/primary/2/'two'/c -> 22 34 fetched: /t/primary/2/'two'/d -> 'bar' 35 output row: [2 'two' 22 'bar'] 36 37 statement ok 38 SET tracing = on,kv,results; SELECT * FROM t WHERE a IN (1, 3); SET tracing = off 39 40 query T 41 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 42 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 43 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 44 ---- 45 fetched: /t/primary/1/'one' -> NULL 46 fetched: /t/primary/1/'one'/c -> 11 47 fetched: /t/primary/1/'one'/d -> 'foo' 48 fetched: /t/primary/3/'three' -> NULL 49 fetched: /t/primary/3/'three'/c -> 33 50 fetched: /t/primary/3/'three'/d -> 'blah' 51 output row: [1 'one' 11 'foo'] 52 output row: [3 'three' 33 'blah'] 53 54 statement ok 55 SET tracing = on,kv,results; SELECT * FROM t WHERE d = 'foo' OR d = 'bar'; SET tracing = off 56 57 query T 58 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 59 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 60 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 61 ---- 62 fetched: /t/dc/'bar'/22/2/'two' -> NULL 63 fetched: /t/dc/'foo'/11/1/'one' -> NULL 64 output row: [2 'two' 22 'bar'] 65 output row: [1 'one' 11 'foo'] 66 67 statement ok 68 SET tracing = on,kv,results; SELECT * FROM t WHERE (d, c) IN (('foo', 11), ('bar', 22)); SET tracing = off 69 70 query T 71 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 72 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 73 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 74 ---- 75 fetched: /t/dc/'bar'/22/2/'two' -> NULL 76 fetched: /t/dc/'foo'/11/1/'one' -> NULL 77 output row: [2 'two' 22 'bar'] 78 output row: [1 'one' 11 'foo'] 79 80 statement ok 81 SET tracing = on,kv,results; SELECT * FROM t WHERE (d, c) = ('foo', 11); SET tracing = off 82 83 query T 84 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 85 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 86 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 87 ---- 88 fetched: /t/dc/'foo'/11/1/'one' -> NULL 89 output row: [1 'one' 11 'foo'] 90 91 statement ok 92 SET tracing = on,kv,results; SELECT * FROM t WHERE a < 2; SET tracing = off 93 94 query T 95 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 96 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 97 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 98 ---- 99 fetched: /t/primary/1/'one' -> NULL 100 fetched: /t/primary/1/'one'/c -> 11 101 fetched: /t/primary/1/'one'/d -> 'foo' 102 output row: [1 'one' 11 'foo'] 103 104 statement ok 105 SET tracing = on,kv,results; SELECT * FROM t WHERE a <= (1 + 1); SET tracing = off 106 107 query T 108 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 109 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 110 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 111 ---- 112 fetched: /t/primary/1/'one' -> NULL 113 fetched: /t/primary/1/'one'/c -> 11 114 fetched: /t/primary/1/'one'/d -> 'foo' 115 fetched: /t/primary/2/'two' -> NULL 116 fetched: /t/primary/2/'two'/c -> 22 117 fetched: /t/primary/2/'two'/d -> 'bar' 118 output row: [1 'one' 11 'foo'] 119 output row: [2 'two' 22 'bar'] 120 121 statement ok 122 SET tracing = on,kv,results; SELECT a, b FROM t WHERE b > 't'; SET tracing = off 123 124 query T 125 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 126 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 127 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 128 ---- 129 fetched: /t/bc/'three'/33/3 -> NULL 130 fetched: /t/bc/'two'/22/2 -> NULL 131 output row: [3 'three'] 132 output row: [2 'two'] 133 134 statement ok 135 SET tracing = on,kv,results; SELECT * FROM t WHERE d < ('b' || 'l'); SET tracing = off 136 137 query T 138 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 139 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 140 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 141 ---- 142 fetched: /t/dc/'bar'/22/2/'two' -> NULL 143 output row: [2 'two' 22 'bar'] 144 145 statement ok 146 SET tracing = on,kv,results; SELECT * FROM t WHERE c = 22; SET tracing = off 147 148 query T 149 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 150 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 151 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 152 ---- 153 fetched: /t/primary/1/'one' -> NULL 154 fetched: /t/primary/1/'one'/c -> 11 155 fetched: /t/primary/1/'one'/d -> 'foo' 156 fetched: /t/primary/2/'two' -> NULL 157 fetched: /t/primary/2/'two'/c -> 22 158 fetched: /t/primary/2/'two'/d -> 'bar' 159 fetched: /t/primary/3/'three' -> NULL 160 fetched: /t/primary/3/'three'/c -> 33 161 fetched: /t/primary/3/'three'/d -> 'blah' 162 output row: [2 'two' 22 'bar'] 163 164 # Use the descending index 165 statement ok 166 SET tracing = on,kv,results; SELECT a FROM t ORDER BY a DESC; SET tracing = off 167 168 query T 169 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 170 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 171 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 172 ---- 173 fetched: /t/a_desc/3/'three' -> NULL 174 fetched: /t/a_desc/2/'two' -> NULL 175 fetched: /t/a_desc/1/'one' -> NULL 176 output row: [3] 177 output row: [2] 178 output row: [1] 179 180 # Use the descending index with multiple spans. 181 statement ok 182 SET tracing = on,kv,results; SELECT a FROM t WHERE a in (2, 3) ORDER BY a DESC; SET tracing = off 183 184 query T 185 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 186 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 187 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 188 ---- 189 fetched: /t/a_desc/3/'three' -> NULL 190 fetched: /t/a_desc/2/'two' -> NULL 191 output row: [3] 192 output row: [2] 193 194 # Index selection occurs in direct join operands too. 195 query TTTTT 196 EXPLAIN (VERBOSE) SELECT * FROM t x JOIN t y USING(b) WHERE x.b = '3' 197 ---- 198 · distributed false · · 199 · vectorized true · · 200 render · · (b, a, c, d, a, c, d) · 201 │ render 0 b · · 202 │ render 1 a · · 203 │ render 2 c · · 204 │ render 3 d · · 205 │ render 4 a · · 206 │ render 5 c · · 207 │ render 6 d · · 208 └── merge-join · · (a, b, c, d, a, b, c, d) · 209 │ type inner · · 210 │ equality (b) = (b) · · 211 │ mergeJoinOrder +"(b=b)" · · 212 ├── index-join · · (a, b, c, d) · 213 │ │ table t@primary · · 214 │ │ key columns a, b · · 215 │ └── scan · · (a, b, c) · 216 │ table t@bc · · 217 │ spans /"3"-/"3"/PrefixEnd · · 218 └── index-join · · (a, b, c, d) · 219 │ table t@primary · · 220 │ key columns a, b · · 221 └── scan · · (a, b, c) · 222 · table t@bc · · 223 · spans /"3"-/"3"/PrefixEnd · · 224 225 statement ok 226 TRUNCATE TABLE t 227 228 statement ok 229 INSERT INTO t VALUES 230 (1, 'a', NULL, NULL), 231 (1, 'b', NULL, NULL), 232 (1, 'c', NULL, NULL) 233 234 statement ok 235 SET tracing = on,kv,results; SELECT * FROM t WHERE a = 1 AND b > 'b'; SET tracing = off 236 237 query T 238 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 239 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 240 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 241 ---- 242 fetched: /t/primary/1/'c' -> NULL 243 output row: [1 'c' NULL NULL] 244 245 statement ok 246 SET tracing = on,kv,results; SELECT * FROM t WHERE a > 0 AND b > 'b'; SET tracing = off 247 248 query T 249 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 250 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 251 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 252 ---- 253 fetched: /t/primary/1/'c' -> NULL 254 output row: [1 'c' NULL NULL] 255 256 statement ok 257 SET tracing = on,kv,results; SELECT * FROM t WHERE a > 1 AND b > 'b'; SET tracing = off 258 259 query T 260 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 261 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 262 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 263 ---- 264 265 query TTT 266 EXPLAIN SELECT * FROM t WHERE a > 1 AND a < 2 267 ---- 268 · distributed false 269 · vectorized true 270 norows · · 271 272 statement ok 273 SET tracing = on,kv,results; SELECT * FROM t WHERE a = 1 AND 'a' < b AND 'c' > b; SET tracing = off 274 275 query T 276 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 277 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 278 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 279 ---- 280 fetched: /t/primary/1/'b' -> NULL 281 output row: [1 'b' NULL NULL] 282 283 statement ok 284 DROP TABLE t 285 286 statement ok 287 CREATE TABLE t ( 288 a INT PRIMARY KEY, 289 b INT, 290 INDEX ab (a, b) 291 ) 292 293 statement ok 294 INSERT INTO t VALUES (1, 2), (3, 4), (5, 6) 295 296 statement ok 297 SET tracing = on,kv,results; SELECT * FROM t@ab WHERE a >= 3 AND a < 5; SET tracing = off 298 299 query T 300 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 301 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 302 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 303 ---- 304 fetched: /t/ab/3/4 -> NULL 305 output row: [3 4] 306 307 statement ok 308 SET tracing = on,kv,results; SELECT * FROM t@ab WHERE a BETWEEN 3 AND 4; SET tracing = off 309 310 query T 311 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 312 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 313 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 314 ---- 315 fetched: /t/ab/3/4 -> NULL 316 output row: [3 4] 317 318 statement ok 319 SET tracing = on,kv,results; SELECT * FROM t@ab WHERE a BETWEEN 3 AND 5; SET tracing = off 320 321 query T 322 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 323 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 324 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 325 ---- 326 fetched: /t/ab/3/4 -> NULL 327 fetched: /t/ab/5/6 -> NULL 328 output row: [3 4] 329 output row: [5 6] 330 331 statement ok 332 SET tracing = on,kv,results; SELECT * FROM t@ab WHERE a < 2 OR a < 4; SET tracing = off 333 334 query T 335 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 336 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 337 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 338 ---- 339 fetched: /t/ab/1/2 -> NULL 340 fetched: /t/ab/3/4 -> NULL 341 output row: [1 2] 342 output row: [3 4] 343 344 statement ok 345 SET tracing = on,kv,results; SELECT * FROM t@ab WHERE a < 3 OR a <= 3; SET tracing = off 346 347 query T 348 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 349 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 350 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 351 ---- 352 fetched: /t/ab/1/2 -> NULL 353 fetched: /t/ab/3/4 -> NULL 354 output row: [1 2] 355 output row: [3 4] 356 357 statement ok 358 SET tracing = on,kv,results; SELECT * FROM t@ab WHERE a <= 3 OR a < 3; SET tracing = off 359 360 query T 361 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 362 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 363 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 364 ---- 365 fetched: /t/ab/1/2 -> NULL 366 fetched: /t/ab/3/4 -> NULL 367 output row: [1 2] 368 output row: [3 4] 369 370 statement ok 371 SET tracing = on,kv,results; SELECT * FROM t@ab WHERE a > 3 OR a >= 3; SET tracing = off 372 373 query T 374 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 375 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 376 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 377 ---- 378 fetched: /t/ab/3/4 -> NULL 379 fetched: /t/ab/5/6 -> NULL 380 output row: [3 4] 381 output row: [5 6] 382 383 statement ok 384 SET tracing = on,kv,results; SELECT * FROM t@ab WHERE a >= 3 OR a > 3; SET tracing = off 385 386 query T 387 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 388 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 389 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 390 ---- 391 fetched: /t/ab/3/4 -> NULL 392 fetched: /t/ab/5/6 -> NULL 393 output row: [3 4] 394 output row: [5 6] 395 396 statement ok 397 SET tracing = on,kv,results; SELECT * FROM t@ab WHERE a = 3 OR a = 5; SET tracing = off 398 399 query T 400 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 401 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 402 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 403 ---- 404 fetched: /t/ab/3/4 -> NULL 405 fetched: /t/ab/5/6 -> NULL 406 output row: [3 4] 407 output row: [5 6] 408 409 statement ok 410 SET tracing = on,kv,results; SELECT * FROM t@ab WHERE a < 3 OR a > 3; SET tracing = off 411 412 query T 413 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 414 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 415 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 416 ---- 417 fetched: /t/ab/1/2 -> NULL 418 fetched: /t/ab/5/6 -> NULL 419 output row: [1 2] 420 output row: [5 6] 421 422 statement ok 423 SET tracing = on,kv,results; SELECT * FROM t@ab WHERE a + 1 = 4; SET tracing = off 424 425 query T 426 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 427 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 428 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 429 ---- 430 fetched: /t/ab/3/4 -> NULL 431 output row: [3 4] 432 433 query TTT 434 EXPLAIN SELECT * FROM t WHERE a = 1 AND false 435 ---- 436 · distributed false 437 · vectorized true 438 norows · · 439 440 query TTT 441 EXPLAIN SELECT * FROM t WHERE a = 1 AND NULL 442 ---- 443 · distributed false 444 · vectorized true 445 norows · · 446 447 query TTT 448 EXPLAIN SELECT * FROM t WHERE a = NULL AND a != NULL 449 ---- 450 · distributed false 451 · vectorized true 452 norows · · 453 454 # Make sure that mixed type comparison operations are not used 455 # for selecting indexes. 456 457 statement ok 458 DROP TABLE t 459 460 statement ok 461 CREATE TABLE t ( 462 a INT PRIMARY KEY, 463 b INT, 464 c INT, 465 INDEX b_desc (b DESC), 466 INDEX bc (b, c) 467 ) 468 469 query TTTTT 470 EXPLAIN (VERBOSE) SELECT a FROM t WHERE c > 1 471 ---- 472 · distributed false · · 473 · vectorized true · · 474 render · · (a) · 475 │ render 0 a · · 476 └── scan · · (a, c) · 477 · table t@primary · · 478 · spans FULL SCAN · · 479 · filter c > 1 · · 480 481 query TTTTT 482 EXPLAIN (VERBOSE) SELECT a FROM t WHERE c < 1 AND b < 5 483 ---- 484 · distributed false · · 485 · vectorized true · · 486 render · · (a) · 487 │ render 0 a · · 488 └── scan · · (a, b, c) · 489 · table t@bc · · 490 · spans /!NULL-/4/1 · · 491 · filter c < 1 · · 492 493 query TTTTT 494 EXPLAIN (VERBOSE) SELECT a FROM t WHERE c > 1.0 495 ---- 496 · distributed false · · 497 · vectorized true · · 498 render · · (a) · 499 │ render 0 a · · 500 └── scan · · (a, c) · 501 · table t@primary · · 502 · spans FULL SCAN · · 503 · filter c > 1 · · 504 505 query TTTTT 506 EXPLAIN (VERBOSE) SELECT a FROM t WHERE c < 1.0 507 ---- 508 · distributed false · · 509 · vectorized true · · 510 render · · (a) · 511 │ render 0 a · · 512 └── scan · · (a, c) · 513 · table t@primary · · 514 · spans FULL SCAN · · 515 · filter c < 1 · · 516 517 query TTTTT 518 EXPLAIN (VERBOSE) SELECT a FROM t WHERE c > 1.0 AND b < 5 519 ---- 520 · distributed false · · 521 · vectorized true · · 522 render · · (a) · 523 │ render 0 a · · 524 └── scan · · (a, b, c) · 525 · table t@bc · · 526 · spans /!NULL-/5 · · 527 · filter c > 1 · · 528 529 query TTTTT 530 EXPLAIN (VERBOSE) SELECT a FROM t WHERE b < 5.0 AND c < 1 531 ---- 532 · distributed false · · 533 · vectorized true · · 534 render · · (a) · 535 │ render 0 a · · 536 └── scan · · (a, b, c) · 537 · table t@bc · · 538 · spans /!NULL-/4/1 · · 539 · filter c < 1 · · 540 541 query TTTTT 542 EXPLAIN (VERBOSE) SELECT a FROM t WHERE (b, c) = (5, 1) 543 ---- 544 · distributed false · · 545 · vectorized true · · 546 render · · (a) · 547 │ render 0 a · · 548 └── scan · · (a, b, c) · 549 · table t@bc · · 550 · spans /5/1-/5/2 · · 551 552 query TTTTT 553 EXPLAIN (VERBOSE) SELECT a FROM t WHERE (b, c) = (5.0, 1) 554 ---- 555 · distributed false · · 556 · vectorized true · · 557 render · · (a) · 558 │ render 0 a · · 559 └── scan · · (a, b, c) · 560 · table t@bc · · 561 · spans /5/1-/5/2 · · 562 563 query TTTTT 564 EXPLAIN (VERBOSE) SELECT a FROM t WHERE (b, c) = (5.1, 1) 565 ---- 566 · distributed false · · 567 · vectorized true · · 568 render · · (a) · 569 │ render 0 a · · 570 └── scan · · (a, b, c) · 571 · table t@bc · · 572 · spans /!NULL- · · 573 · filter (b = 5.1) AND (c = 1) · · 574 575 # Note the span is reversed because of #20203. 576 query TTTTT 577 EXPLAIN (VERBOSE) SELECT a FROM t WHERE b IN (5.0, 1) 578 ---- 579 · distributed false · · 580 · vectorized true · · 581 render · · (a) · 582 │ render 0 a · · 583 └── scan · · (a, b) · 584 · table t@b_desc · · 585 · spans /5-/4 /1-/0 · · 586 587 statement ok 588 CREATE TABLE abcd ( 589 a INT, 590 b INT, 591 c INT, 592 d INT, 593 INDEX adb (a, d, b), 594 INDEX abcd (a, b, c, d) 595 ) 596 597 # Verify that we prefer the index where more columns are constrained, even if it 598 # has more keys per row. 599 query TTTTT 600 EXPLAIN (VERBOSE) SELECT b FROM abcd WHERE (a, b) = (1, 4) 601 ---- 602 · distributed false · · 603 · vectorized true · · 604 render · · (b) · 605 │ render 0 b · · 606 └── scan · · (a, b) · 607 · table abcd@abcd · · 608 · spans /1/4-/1/5 · · 609 610 query TTTTT 611 EXPLAIN (VERBOSE) SELECT b FROM abcd WHERE (a, b) IN ((1, 4), (2, 9)) 612 ---- 613 · distributed false · · 614 · vectorized true · · 615 render · · (b) · 616 │ render 0 b · · 617 └── scan · · (a, b) · 618 · table abcd@abcd · · 619 · spans /1/4-/1/5 /2/9-/2/10 · · 620 621 statement ok 622 CREATE TABLE ab ( 623 s STRING, 624 i INT 625 ); 626 627 query TTTTT 628 EXPLAIN (VERBOSE) SELECT i, s FROM ab WHERE (i, s) < (1, 'c') 629 ---- 630 · distributed false · · 631 · vectorized true · · 632 render · · (i, s) · 633 │ render 0 i · · 634 │ render 1 s · · 635 └── scan · · (s, i) · 636 · table ab@primary · · 637 · spans FULL SCAN · · 638 · filter (i, s) < (1, 'c') · · 639 640 statement ok 641 CREATE INDEX baz ON ab (i, s) 642 643 query TTTTT 644 EXPLAIN (VERBOSE) SELECT i, s FROM ab@baz WHERE (i, s) < (1, 'c') 645 ---- 646 · distributed false · · 647 · vectorized true · · 648 render · · (i, s) · 649 │ render 0 i · · 650 │ render 1 s · · 651 └── scan · · (s, i) · 652 · table ab@baz · · 653 · spans /!NULL-/1/"c" · · 654 · filter (i, s) < (1, 'c') · · 655 656 # Check that primary key definitions can indicate index ordering, 657 # and this information is subsequently used during index selection 658 # and span generation. #13882 659 query TTBITTBB 660 CREATE TABLE abz(a INT, b INT, c INT, PRIMARY KEY (a DESC, b ASC), UNIQUE(c DESC, b ASC)); SHOW INDEX FROM abz 661 ---- 662 abz primary false 1 a DESC false false 663 abz primary false 2 b ASC false false 664 abz abz_c_b_key false 1 c DESC false false 665 abz abz_c_b_key false 2 b ASC false false 666 abz abz_c_b_key false 3 a ASC false true 667 668 query TTTTT 669 EXPLAIN (VERBOSE) SELECT a FROM abz ORDER BY a DESC LIMIT 1 670 ---- 671 · distributed false · · 672 · vectorized true · · 673 scan · · (a) · 674 · table abz@primary · · 675 · spans LIMITED SCAN · · 676 · limit 1 · · 677 678 query TTTTT 679 EXPLAIN (VERBOSE) SELECT c FROM abz ORDER BY c DESC LIMIT 1 680 ---- 681 · distributed false · · 682 · vectorized true · · 683 scan · · (c) · 684 · table abz@abz_c_b_key · · 685 · spans LIMITED SCAN · · 686 · limit 1 · · 687 688 # Issue #14426: verify we don't have an internal filter that contains "a IN ()" 689 # (which causes an error in DistSQL due to expression serialization). 690 statement ok 691 CREATE TABLE tab0( 692 k INT PRIMARY KEY, 693 a INT, 694 b INT 695 ) 696 697 query TTTTT 698 EXPLAIN (VERBOSE) SELECT k FROM tab0 WHERE (a IN (6) AND a > 6) OR b >= 4 699 ---- 700 · distributed false · · 701 · vectorized true · · 702 render · · (k) · 703 │ render 0 k · · 704 └── scan · · (k, a, b) · 705 · table tab0@primary · · 706 · spans FULL SCAN · · 707 · filter ((a IN (6,)) AND (a > 6)) OR (b >= 4) · · 708 709 # Check that no extraneous rows are fetched due to excessive batching (#15910) 710 # The test is composed of three parts: populate a table, check 711 # that the problematic plan is properly derived from the test query, 712 # then test the results. 713 714 statement ok 715 CREATE TABLE test2 (id BIGSERIAL PRIMARY KEY, k TEXT UNIQUE, v INT DEFAULT 42); 716 INSERT INTO test2(k) 717 VALUES ('001'),('002'),('003'),('004'),('005'),('006'),('007'),('008'),('009'),('010'), 718 ('011'),('012'),('013'),('014'),('015'),('016'),('017'),('018'),('019'),('020'), 719 ('021'),('022'),('023'),('024'),('025'),('026'),('027'),('028'),('029'),('030') 720 721 # Plan check: 722 # The query is using an index-join and the limit is propagated to the scan. 723 724 query TTTTT 725 EXPLAIN (VERBOSE) SELECT * FROM test2 WHERE k <= '100' ORDER BY k DESC LIMIT 20 726 ---- 727 · distributed false · · 728 · vectorized true · · 729 index-join · · (id, k, v) -k 730 │ table test2@primary · · 731 │ key columns id · · 732 └── revscan · · (id, k) -k 733 · table test2@test2_k_key · · 734 · spans /!NULL-/"100"/PrefixEnd · · 735 · limit 20 · · 736 737 # The result output of this test requires that vectorized execution 738 # is not used, so it has been moved to select_index_vectorize_off. 739 740 741 # Regression test for #20035. 742 statement ok 743 CREATE TABLE favorites ( 744 id INT NOT NULL DEFAULT unique_rowid(), 745 resource_type STRING(30) NOT NULL, 746 resource_key STRING(255) NOT NULL, 747 device_group STRING(30) NOT NULL, 748 customerid INT NOT NULL, 749 jurisdiction STRING(2) NOT NULL, 750 brand STRING(255) NOT NULL, 751 created_ts TIMESTAMP NULL, 752 guid_id STRING(100) NOT NULL, 753 locale STRING(10) NOT NULL DEFAULT NULL, 754 CONSTRAINT "primary" PRIMARY KEY (id ASC), 755 UNIQUE INDEX favorites_idx (resource_type ASC, device_group ASC, resource_key ASC, customerid ASC), 756 INDEX favorites_guid_idx (guid_id ASC), 757 INDEX favorites_glob_fav_idx (resource_type ASC, device_group ASC, jurisdiction ASC, brand ASC, locale ASC, resource_key ASC), 758 FAMILY "primary" (id, resource_type, resource_key, device_group, customerid, jurisdiction, brand, created_ts, guid_id, locale) 759 ) 760 761 statement ok 762 INSERT INTO favorites (customerid, guid_id, resource_type, device_group, jurisdiction, brand, locale, resource_key) 763 VALUES (1, '1', 'GAME', 'web', 'MT', 'xxx', 'en_GB', 'tp'), 764 (2, '2', 'GAME', 'web', 'MT', 'xxx', 'en_GB', 'ts'), 765 (3, '3', 'GAME', 'web', 'MT', 'xxx', 'en_GB', 'ts1'), 766 (4, '4', 'GAME', 'web', 'MT', 'xxx', 'en_GB', 'ts2'), 767 (5, '5', 'GAME', 'web', 'MT', 'xxx', 'en_GB', 'ts3'), 768 (6, '6', 'GAME', 'web', 'MT', 'xxx', 'en_GB', 'ts4') 769 770 query TTT 771 EXPLAIN SELECT 772 resource_key, 773 count(resource_key) total 774 FROM favorites f1 775 WHERE f1.jurisdiction = 'MT' 776 AND f1.brand = 'xxx' 777 AND f1.resource_type = 'GAME' 778 AND f1.device_group = 'web' 779 AND f1.locale = 'en_GB' 780 AND f1.resource_key IN ('ts', 'ts2', 'ts3') 781 GROUP BY resource_key 782 ORDER BY total DESC 783 ---- 784 · distributed false 785 · vectorized true 786 sort · · 787 │ order -total 788 └── group · · 789 │ aggregate 0 resource_key 790 │ aggregate 1 count_rows() 791 │ group by resource_key 792 └── render · · 793 └── scan · · 794 · table favorites@favorites_glob_fav_idx 795 · spans /"GAME"/"web"/"MT"/"xxx"/"en_GB"/"ts"-/"GAME"/"web"/"MT"/"xxx"/"en_GB"/"ts"/PrefixEnd /"GAME"/"web"/"MT"/"xxx"/"en_GB"/"ts2"-/"GAME"/"web"/"MT"/"xxx"/"en_GB"/"ts2"/PrefixEnd /"GAME"/"web"/"MT"/"xxx"/"en_GB"/"ts3"-/"GAME"/"web"/"MT"/"xxx"/"en_GB"/"ts3"/PrefixEnd 796 797 query TI rowsort 798 SELECT 799 resource_key, 800 count(resource_key) total 801 FROM favorites f1 802 WHERE f1.jurisdiction = 'MT' 803 AND f1.brand = 'xxx' 804 AND f1.resource_type = 'GAME' 805 AND f1.device_group = 'web' 806 AND f1.locale = 'en_GB' 807 AND f1.resource_key IN ('ts', 'ts2', 'ts3') 808 GROUP BY resource_key 809 ORDER BY total DESC 810 ---- 811 ts 1 812 ts2 1 813 ts3 1 814 815 # Regression tests for #20362 (IS NULL handling). 816 query TTTTT 817 EXPLAIN (VERBOSE) SELECT * FROM abcd@abcd WHERE a IS NULL AND b > 5 818 ---- 819 · distributed false · · 820 · vectorized true · · 821 scan · · (a, b, c, d) · 822 · table abcd@abcd · · 823 · spans /NULL/6-/!NULL · · 824 825 query TTTTT 826 EXPLAIN (VERBOSE) SELECT * FROM abcd@abcd WHERE a IS NULL AND b < 5 827 ---- 828 · distributed false · · 829 · vectorized true · · 830 scan · · (a, b, c, d) · 831 · table abcd@abcd · · 832 · spans /NULL/!NULL-/NULL/5 · · 833 834 query TTTTT 835 EXPLAIN (VERBOSE) SELECT * FROM abcd@abcd WHERE a IS NULL ORDER BY b 836 ---- 837 · distributed false · · 838 · vectorized true · · 839 scan · · (a, b, c, d) +b 840 · table abcd@abcd · · 841 · spans /NULL-/!NULL · · 842 843 query TTTTT 844 EXPLAIN (VERBOSE) SELECT * FROM abcd@abcd WHERE a = 1 AND b IS NULL AND c > 0 AND c < 10 ORDER BY c 845 ---- 846 · distributed false · · 847 · vectorized true · · 848 scan · · (a, b, c, d) +c 849 · table abcd@abcd · · 850 · spans /1/NULL/1-/1/NULL/10 · · 851 852 # Regression test for #3548: verify we create constraints on implicit columns 853 # when they are part of the key (non-unique index). 854 statement ok 855 CREATE TABLE abc (a INT, b INT, c INT, PRIMARY KEY(a,b), INDEX(c)) 856 857 query TTTTT 858 EXPLAIN (VERBOSE) SELECT c FROM abc WHERE c = 1 and a = 3 859 ---- 860 · distributed false · · 861 · vectorized true · · 862 render · · (c) · 863 │ render 0 c · · 864 └── scan · · (a, c) · 865 · table abc@abc_c_idx · · 866 · spans /1/3-/1/4 · · 867 868 # Verify we don't create constraints on implicit columns when they may be part 869 # of the key (unique index on nullable column). 870 statement ok 871 CREATE TABLE def (d INT, e INT, f INT, PRIMARY KEY(d,e), UNIQUE INDEX(f)) 872 873 query TTTTT 874 EXPLAIN (VERBOSE) SELECT f FROM def WHERE f = 1 and d = 3 875 ---- 876 · distributed false · · 877 · vectorized true · · 878 render · · (f) · 879 │ render 0 f · · 880 └── scan · · (d, f) · 881 · table def@def_f_key · · 882 · spans /1-/2 · · 883 · filter d = 3 · · 884 885 statement ok 886 DROP TABLE def 887 888 # Verify we don't create constraints on implicit columns when they are not part 889 # of the key (unique index on not-null column). 890 statement ok 891 CREATE TABLE def (d INT, e INT, f INT NOT NULL, PRIMARY KEY(d,e), UNIQUE INDEX(f)) 892 893 query TTTTT 894 EXPLAIN (VERBOSE) SELECT f FROM def WHERE f = 1 and d = 3 895 ---- 896 · distributed false · · 897 · vectorized true · · 898 render · · (f) · 899 │ render 0 f · · 900 └── scan · · (d, f) · 901 · table def@def_f_key · · 902 · spans /1-/2 · · 903 · filter d = 3 · · 904 905 # Regression test for #20504. 906 query TTTTT 907 EXPLAIN (VERBOSE) SELECT a, b FROM abc WHERE (a, b) BETWEEN (1, 2) AND (3, 4) 908 ---- 909 · distributed false · · 910 · vectorized true · · 911 scan · · (a, b) · 912 · table abc@primary · · 913 · spans /1/2-/3/4/# · · 914 915 # Regression test for #21831. 916 statement ok 917 CREATE TABLE str (k INT PRIMARY KEY, v STRING, INDEX(v)) 918 919 query TTTTT 920 EXPLAIN (VERBOSE) SELECT k, v FROM str WHERE v LIKE 'ABC%' 921 ---- 922 · distributed false · · 923 · vectorized true · · 924 scan · · (k, v) · 925 · table str@str_v_idx · · 926 · spans /"ABC"-/"ABD" · · 927 928 query TTTTT 929 EXPLAIN (VERBOSE) SELECT k, v FROM str WHERE v LIKE 'ABC%Z' 930 ---- 931 · distributed false · · 932 · vectorized true · · 933 scan · · (k, v) · 934 · table str@str_v_idx · · 935 · spans /"ABC"-/"ABD" · · 936 · filter v LIKE 'ABC%Z' · · 937 938 query TTTTT 939 EXPLAIN (VERBOSE) SELECT k, v FROM str WHERE v SIMILAR TO 'ABC_*' 940 ---- 941 · distributed false · · 942 · vectorized true · · 943 scan · · (k, v) · 944 · table str@str_v_idx · · 945 · spans /"ABC"-/"ABD" · · 946 · filter v SIMILAR TO 'ABC_*' · · 947 948 # Test that we generate spans for IS (NOT) DISTINCT FROM. 949 statement ok 950 CREATE TABLE xy (x INT, y INT, INDEX (y)) 951 952 query TTTTT 953 EXPLAIN (VERBOSE) SELECT * FROM xy WHERE y IS NOT DISTINCT FROM NULL 954 ---- 955 · distributed false · · 956 · vectorized true · · 957 index-join · · (x, y) · 958 │ table xy@primary · · 959 │ key columns rowid · · 960 └── scan · · (y, rowid[hidden]) · 961 · table xy@xy_y_idx · · 962 · spans /NULL-/!NULL · · 963 964 query TTTTT 965 EXPLAIN (VERBOSE) SELECT * FROM xy WHERE y IS NOT DISTINCT FROM 4 966 ---- 967 · distributed false · · 968 · vectorized true · · 969 index-join · · (x, y) · 970 │ table xy@primary · · 971 │ key columns rowid · · 972 └── scan · · (y, rowid[hidden]) · 973 · table xy@xy_y_idx · · 974 · spans /4-/5 · · 975 976 query TTTTT 977 EXPLAIN (VERBOSE) SELECT x FROM xy WHERE y > 0 AND y < 2 ORDER BY y 978 ---- 979 · distributed false · · 980 · vectorized true · · 981 render · · (x) · 982 │ render 0 x · · 983 └── index-join · · (x, y) · 984 │ table xy@primary · · 985 │ key columns rowid · · 986 └── scan · · (y, rowid[hidden]) · 987 · table xy@xy_y_idx · · 988 · spans /1-/2 · · 989 990 query TTTTT 991 EXPLAIN (VERBOSE) SELECT * FROM xy WHERE y IS DISTINCT FROM NULL 992 ---- 993 · distributed false · · 994 · vectorized true · · 995 scan · · (x, y) · 996 · table xy@primary · · 997 · spans FULL SCAN · · 998 · filter y IS NOT NULL · · 999 1000 query TTTTT 1001 EXPLAIN (VERBOSE) SELECT * FROM xy WHERE y IS DISTINCT FROM 4 1002 ---- 1003 · distributed false · · 1004 · vectorized true · · 1005 scan · · (x, y) · 1006 · table xy@primary · · 1007 · spans FULL SCAN · · 1008 · filter y IS DISTINCT FROM 4 · · 1009 1010 # Regression tests for #22670. 1011 statement ok 1012 CREATE INDEX xy_idx ON xy (x, y) 1013 1014 statement ok 1015 INSERT INTO xy VALUES (NULL, NULL), (1, NULL), (NULL, 1), (1, 1) 1016 1017 query TTTTT 1018 EXPLAIN (VERBOSE) SELECT * FROM xy WHERE x IN (NULL, 1, 2) 1019 ---- 1020 · distributed false · · 1021 · vectorized true · · 1022 scan · · (x, y) · 1023 · table xy@xy_idx · · 1024 · spans /1-/3 · · 1025 1026 query TTTTT 1027 EXPLAIN (VERBOSE) SELECT * FROM xy WHERE (x, y) IN ((NULL, NULL), (1, NULL), (NULL, 1), (1, 1), (1, 2)) 1028 ---- 1029 · distributed false · · 1030 · vectorized true · · 1031 scan · · (x, y) · 1032 · table xy@xy_idx · · 1033 · spans /1/1-/1/3 · · 1034 1035 # ------------------------------------------------------------------------------ 1036 # Non-covering index 1037 # ------------------------------------------------------------------------------ 1038 statement ok 1039 CREATE TABLE noncover ( 1040 a INT PRIMARY KEY, 1041 b INT, 1042 c INT, 1043 d INT, 1044 INDEX b (b), 1045 UNIQUE INDEX c (c), 1046 FAMILY (a), 1047 FAMILY (b), 1048 FAMILY (c), 1049 FAMILY (d) 1050 ) 1051 1052 statement ok 1053 INSERT INTO noncover VALUES (1, 2, 3, 4), (5, 6, 7, 8) 1054 1055 query TTT 1056 EXPLAIN SELECT * FROM noncover WHERE b = 2 1057 ---- 1058 · distributed false 1059 · vectorized true 1060 index-join · · 1061 │ table noncover@primary 1062 │ key columns a 1063 └── scan · · 1064 · table noncover@b 1065 · spans /2-/3 1066 1067 statement ok 1068 SET tracing = on,kv,results; SELECT * FROM noncover WHERE b = 2; SET tracing = off 1069 1070 query T 1071 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 1072 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 1073 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 1074 ---- 1075 fetched: /noncover/b/2/1 -> NULL 1076 fetched: /noncover/primary/1 -> NULL 1077 fetched: /noncover/primary/1/b -> 2 1078 fetched: /noncover/primary/1/c -> 3 1079 fetched: /noncover/primary/1/d -> 4 1080 output row: [1 2 3 4] 1081 1082 query TTT 1083 EXPLAIN SELECT * FROM noncover WHERE c = 6 1084 ---- 1085 · distributed false 1086 · vectorized true 1087 index-join · · 1088 │ table noncover@primary 1089 │ key columns a 1090 └── scan · · 1091 · table noncover@c 1092 · spans /6-/7 1093 1094 statement ok 1095 SET tracing = on,kv,results; SELECT * FROM noncover WHERE c = 7; SET tracing = off 1096 1097 query T 1098 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 1099 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 1100 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 1101 ---- 1102 fetched: /noncover/c/7 -> /5 1103 fetched: /noncover/primary/5 -> NULL 1104 fetched: /noncover/primary/5/b -> 6 1105 fetched: /noncover/primary/5/c -> 7 1106 fetched: /noncover/primary/5/d -> 8 1107 output row: [5 6 7 8] 1108 1109 query TTTTT 1110 EXPLAIN (VERBOSE) SELECT * FROM noncover WHERE c > 0 ORDER BY c DESC 1111 ---- 1112 · distributed false · · 1113 · vectorized true · · 1114 sort · · (a, b, c, d) -c 1115 │ order -c · · 1116 └── scan · · (a, b, c, d) · 1117 · table noncover@primary · · 1118 · spans FULL SCAN · · 1119 · filter c > 0 · · 1120 1121 query TTTTT 1122 EXPLAIN (VERBOSE) SELECT * FROM noncover WHERE c > 0 ORDER BY c 1123 ---- 1124 · distributed false · · 1125 · vectorized true · · 1126 sort · · (a, b, c, d) +c 1127 │ order +c · · 1128 └── scan · · (a, b, c, d) · 1129 · table noncover@primary · · 1130 · spans FULL SCAN · · 1131 · filter c > 0 · · 1132 1133 query TTTTT 1134 EXPLAIN (VERBOSE) SELECT * FROM noncover WHERE c > 0 AND d = 8 1135 ---- 1136 · distributed false · · 1137 · vectorized true · · 1138 scan · · (a, b, c, d) · 1139 · table noncover@primary · · 1140 · spans FULL SCAN · · 1141 · filter (c > 0) AND (d = 8) · · 1142 1143 # The following testcases verify that when we have a small limit, we prefer an 1144 # order-matching index. 1145 1146 query TTT 1147 EXPLAIN SELECT * FROM noncover ORDER BY c 1148 ---- 1149 · distributed false 1150 · vectorized true 1151 sort · · 1152 │ order +c 1153 └── scan · · 1154 · table noncover@primary 1155 · spans FULL SCAN 1156 1157 query TTT 1158 EXPLAIN SELECT * FROM noncover ORDER BY c LIMIT 5 1159 ---- 1160 · distributed false 1161 · vectorized true 1162 index-join · · 1163 │ table noncover@primary 1164 │ key columns a 1165 └── scan · · 1166 · table noncover@c 1167 · spans LIMITED SCAN 1168 · limit 5 1169 1170 query TTT 1171 SELECT tree, field, description FROM [ 1172 EXPLAIN (VERBOSE) SELECT * FROM noncover ORDER BY c OFFSET 5 1173 ] 1174 ---- 1175 · distributed false 1176 · vectorized true 1177 limit · · 1178 │ offset 5 1179 └── sort · · 1180 │ order +c 1181 └── scan · · 1182 · table noncover@primary 1183 · spans FULL SCAN 1184 1185 # TODO(radu): need to prefer the order-matching index when OFFSET is present. 1186 query TTT 1187 SELECT tree, field, description FROM [ 1188 EXPLAIN (VERBOSE) SELECT * FROM noncover ORDER BY c LIMIT 5 OFFSET 5 1189 ] 1190 ---- 1191 · distributed false 1192 · vectorized true 1193 limit · · 1194 │ offset 5 1195 └── index-join · · 1196 │ table noncover@primary 1197 │ key columns a 1198 └── scan · · 1199 · table noncover@c 1200 · spans LIMITED SCAN 1201 · limit 10 1202 1203 query TTT 1204 SELECT tree, field, description FROM [ 1205 EXPLAIN (VERBOSE) SELECT * FROM noncover ORDER BY c LIMIT 1000000 1206 ] 1207 ---- 1208 · distributed false 1209 · vectorized true 1210 limit · · 1211 │ count 1000000 1212 └── sort · · 1213 │ order +c 1214 └── scan · · 1215 · table noncover@primary 1216 · spans FULL SCAN 1217 1218 # ------------------------------------------------------------------------------ 1219 # These tests verify that while we are joining an index with the table, we 1220 # evaluate what parts of the filter we can using the columns in the index 1221 # to avoid unnecessary lookups in the table. 1222 # ------------------------------------------------------------------------------ 1223 statement ok 1224 CREATE TABLE t2 ( 1225 a INT PRIMARY KEY, 1226 b INT, 1227 c INT, 1228 s STRING, 1229 INDEX bc (b, c), 1230 FAMILY (a), 1231 FAMILY (b), 1232 FAMILY (c), 1233 FAMILY (s) 1234 ) 1235 1236 statement ok 1237 INSERT INTO t2 VALUES 1238 (1, 1, 1, '11'), 1239 (2, 1, 2, '12'), 1240 (3, 1, 3, '13'), 1241 (4, 2, 1, '21'), 1242 (5, 2, 2, '22'), 1243 (6, 2, 3, '23'), 1244 (7, 3, 1, '31'), 1245 (8, 3, 2, '32'), 1246 (9, 3, 3, '33') 1247 1248 # Pretend we have 10x more rows in the database than we really do. 1249 statement ok 1250 ALTER TABLE t2 INJECT STATISTICS '[ 1251 { 1252 "columns": ["b"], 1253 "created_at": "2018-01-01 1:00:00.00000+00:00", 1254 "row_count": 90, 1255 "distinct_count": 30 1256 } 1257 ]' 1258 1259 query TTT 1260 SELECT tree, field, description FROM [ 1261 EXPLAIN (VERBOSE) SELECT * FROM t2 WHERE b = 2 AND c % 2 = 0 1262 ] 1263 ---- 1264 · distributed false 1265 · vectorized true 1266 index-join · · 1267 │ table t2@primary 1268 │ key columns a 1269 └── scan · · 1270 · table t2@bc 1271 · spans /2-/3 1272 · filter (c % 2) = 0 1273 1274 # We do NOT look up the table row for '21' and '23'. 1275 statement ok 1276 SET tracing = on,kv,results; SELECT * FROM t2 WHERE b = 2 AND c % 2 = 0; SET tracing = off 1277 1278 query T 1279 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 1280 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 1281 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 1282 ---- 1283 fetched: /t2/bc/2/1/4 -> NULL 1284 fetched: /t2/bc/2/2/5 -> NULL 1285 fetched: /t2/bc/2/3/6 -> NULL 1286 fetched: /t2/primary/5 -> NULL 1287 fetched: /t2/primary/5/b -> 2 1288 fetched: /t2/primary/5/c -> 2 1289 fetched: /t2/primary/5/s -> '22' 1290 output row: [5 2 2 '22'] 1291 1292 query TTT 1293 SELECT tree, field, description FROM [ 1294 EXPLAIN (VERBOSE) SELECT * FROM t2 WHERE b = 2 AND c != b 1295 ] 1296 ---- 1297 · distributed false 1298 · vectorized true 1299 index-join · · 1300 │ table t2@primary 1301 │ key columns a 1302 └── scan · · 1303 · table t2@bc 1304 · spans /2/!NULL-/2/2 /2/3-/3 1305 1306 # We do NOT look up the table row for '22'. 1307 statement ok 1308 SET tracing = on,kv,results; SELECT * FROM t2 WHERE b = 2 AND c != b; SET tracing = off 1309 1310 query T 1311 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 1312 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 1313 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 1314 ---- 1315 fetched: /t2/bc/2/1/4 -> NULL 1316 fetched: /t2/bc/2/3/6 -> NULL 1317 fetched: /t2/primary/4 -> NULL 1318 fetched: /t2/primary/4/b -> 2 1319 fetched: /t2/primary/4/c -> 1 1320 fetched: /t2/primary/4/s -> '21' 1321 fetched: /t2/primary/6 -> NULL 1322 fetched: /t2/primary/6/b -> 2 1323 fetched: /t2/primary/6/c -> 3 1324 fetched: /t2/primary/6/s -> '23' 1325 output row: [4 2 1 '21'] 1326 output row: [6 2 3 '23'] 1327 1328 # We do NOT look up the table row for '22'. 1329 statement ok 1330 SET tracing = on,kv,results; SELECT * FROM t2 WHERE b = 2 AND c != b AND s <> '21'; SET tracing = off 1331 1332 query T 1333 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 1334 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 1335 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 1336 ---- 1337 fetched: /t2/bc/2/1/4 -> NULL 1338 fetched: /t2/bc/2/3/6 -> NULL 1339 fetched: /t2/primary/4 -> NULL 1340 fetched: /t2/primary/4/b -> 2 1341 fetched: /t2/primary/4/c -> 1 1342 fetched: /t2/primary/4/s -> '21' 1343 fetched: /t2/primary/6 -> NULL 1344 fetched: /t2/primary/6/b -> 2 1345 fetched: /t2/primary/6/c -> 3 1346 fetched: /t2/primary/6/s -> '23' 1347 output row: [6 2 3 '23'] 1348 1349 # We only look up the table rows where c = b+1 or a > b+4: '23', '32', '33'. 1350 # TODO(justin): we need to push the filter into the index scan. 1351 statement ok 1352 SET tracing = on,kv,results; SELECT * FROM t2 WHERE b > 1 AND ((c = b+1 AND s != '23') OR (a > b+4 AND s != '32')); SET tracing = off 1353 1354 query T 1355 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 1356 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 1357 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 1358 ---- 1359 fetched: /t2/primary/1 -> NULL 1360 fetched: /t2/primary/1/b -> 1 1361 fetched: /t2/primary/1/c -> 1 1362 fetched: /t2/primary/1/s -> '11' 1363 fetched: /t2/primary/2 -> NULL 1364 fetched: /t2/primary/2/b -> 1 1365 fetched: /t2/primary/2/c -> 2 1366 fetched: /t2/primary/2/s -> '12' 1367 fetched: /t2/primary/3 -> NULL 1368 fetched: /t2/primary/3/b -> 1 1369 fetched: /t2/primary/3/c -> 3 1370 fetched: /t2/primary/3/s -> '13' 1371 fetched: /t2/primary/4 -> NULL 1372 fetched: /t2/primary/4/b -> 2 1373 fetched: /t2/primary/4/c -> 1 1374 fetched: /t2/primary/4/s -> '21' 1375 fetched: /t2/primary/5 -> NULL 1376 fetched: /t2/primary/5/b -> 2 1377 fetched: /t2/primary/5/c -> 2 1378 fetched: /t2/primary/5/s -> '22' 1379 fetched: /t2/primary/6 -> NULL 1380 fetched: /t2/primary/6/b -> 2 1381 fetched: /t2/primary/6/c -> 3 1382 fetched: /t2/primary/6/s -> '23' 1383 fetched: /t2/primary/7 -> NULL 1384 fetched: /t2/primary/7/b -> 3 1385 fetched: /t2/primary/7/c -> 1 1386 fetched: /t2/primary/7/s -> '31' 1387 fetched: /t2/primary/8 -> NULL 1388 fetched: /t2/primary/8/b -> 3 1389 fetched: /t2/primary/8/c -> 2 1390 fetched: /t2/primary/8/s -> '32' 1391 fetched: /t2/primary/9 -> NULL 1392 fetched: /t2/primary/9/b -> 3 1393 fetched: /t2/primary/9/c -> 3 1394 fetched: /t2/primary/9/s -> '33' 1395 output row: [9 3 3 '33'] 1396 1397 # Check that splitting of the expression filter does not mistakenly 1398 # bring non-indexed columns (s) under the index scanNode. (#12582) 1399 # To test this we need an expression containing non-indexed 1400 # columns that disappears during range simplification. 1401 query TTTTT 1402 EXPLAIN (VERBOSE) SELECT a FROM t2 WHERE b = 2 OR ((b BETWEEN 2 AND 1) AND ((s != 'a') OR (s = 'a'))) 1403 ---- 1404 · distributed false · · 1405 · vectorized true · · 1406 render · · (a) · 1407 │ render 0 a · · 1408 └── index-join · · (a, b, s) · 1409 │ table t2@primary · · 1410 │ key columns a · · 1411 └── scan · · (a, b) · 1412 · table t2@bc · · 1413 · spans /2-/3 · · 1414 1415 statement ok 1416 CREATE TABLE t3 (k INT PRIMARY KEY, v INT, w INT, INDEX v(v)) 1417 1418 query TTTTT 1419 EXPLAIN (VERBOSE) SELECT w FROM t3 WHERE v > 0 AND v < 10 ORDER BY v 1420 ---- 1421 · distributed false · · 1422 · vectorized true · · 1423 render · · (w) · 1424 │ render 0 w · · 1425 └── index-join · · (v, w) +v 1426 │ table t3@primary · · 1427 │ key columns k · · 1428 └── scan · · (k, v) +v 1429 · table t3@v · · 1430 · spans /1-/10 · · 1431 1432 # ------------------------------------------------------------------------------ 1433 # These tests are for the point lookup optimization: for single row lookups on 1434 # a table with multiple column families, we only scan the relevant column 1435 # families. Note that this applies to SELECTs and UPDATEs but not DELETEs, since 1436 # we need to ensure that we delete across all column families. 1437 # ------------------------------------------------------------------------------ 1438 statement ok 1439 CREATE TABLE t4 ( 1440 a INT, 1441 b INT, 1442 c INT, 1443 d INT, 1444 e INT, 1445 PRIMARY KEY (a, b), 1446 FAMILY (a, b), 1447 FAMILY (c), 1448 FAMILY (d), 1449 FAMILY (e) 1450 ) 1451 1452 statement ok 1453 INSERT INTO t4 VALUES (10, 20, 30, 40, 50) 1454 1455 # Point lookup on c does not touch the d or e families. 1456 query TTT 1457 EXPLAIN SELECT c FROM t4 WHERE a = 10 and b = 20 1458 ---- 1459 · distributed false 1460 · vectorized true 1461 render · · 1462 └── scan · · 1463 · table t4@primary 1464 · spans /10/20/0-/10/20/1/2 1465 1466 statement ok 1467 SET tracing = on,kv,results; SELECT c FROM t4 WHERE a = 10 and b = 20; SET tracing = off 1468 1469 query T 1470 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 1471 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 1472 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 1473 ---- 1474 fetched: /t4/primary/10/20 -> NULL 1475 fetched: /t4/primary/10/20/c -> 30 1476 output row: [30] 1477 1478 # Point lookup on d does not touch the c or e families. 1479 query TTT 1480 EXPLAIN SELECT d FROM t4 WHERE a = 10 and b = 20 1481 ---- 1482 · distributed false 1483 · vectorized true 1484 render · · 1485 └── scan · · 1486 · table t4@primary 1487 · spans /10/20/0-/10/20/1 /10/20/2/1-/10/20/2/2 1488 · parallel · 1489 1490 statement ok 1491 SET tracing = on,kv,results; SELECT d FROM t4 WHERE a = 10 and b = 20; SET tracing = off 1492 1493 query T 1494 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 1495 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 1496 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 1497 ---- 1498 fetched: /t4/primary/10/20 -> NULL 1499 fetched: /t4/primary/10/20/d -> 40 1500 output row: [40] 1501 1502 # Point lookup on both d and e uses a single span for the two adjacent column 1503 # families. 1504 query TTT 1505 EXPLAIN SELECT d, e FROM t4 WHERE a = 10 and b = 20 1506 ---- 1507 · distributed false 1508 · vectorized true 1509 render · · 1510 └── scan · · 1511 · table t4@primary 1512 · spans /10/20/0-/10/20/1 /10/20/2/1-/10/20/3/2 1513 · parallel · 1514 1515 # Optimization should also be applied for updates. 1516 query TTT 1517 EXPLAIN UPDATE t4 SET c = 30 WHERE a = 10 and b = 20 1518 ---- 1519 · distributed false 1520 · vectorized false 1521 count · · 1522 └── update · · 1523 │ table t4 1524 │ set c 1525 │ strategy updater 1526 │ auto commit · 1527 └── render · · 1528 └── scan · · 1529 · table t4@primary 1530 · spans /10/20/0-/10/20/1/2 1531 · locking strength for update 1532 1533 # Optimization should not be applied for deletes. 1534 query TTT 1535 EXPLAIN DELETE FROM t4 WHERE a = 10 and b = 20 1536 ---- 1537 · distributed false 1538 · vectorized false 1539 delete range · · 1540 · from t4 1541 · auto commit · 1542 · spans /10/20-/10/20/# 1543 1544 # Optimization should not be applied for non point lookups. 1545 query TTT 1546 EXPLAIN SELECT c FROM t4 WHERE a = 10 and b >= 20 and b < 22 1547 ---- 1548 · distributed false 1549 · vectorized true 1550 render · · 1551 └── scan · · 1552 · table t4@primary 1553 · spans /10/20-/10/21/# 1554 · parallel · 1555 1556 # Optimization should not be applied for partial primary key filter. 1557 query TTT 1558 EXPLAIN SELECT c FROM t4 WHERE a = 10 1559 ---- 1560 · distributed false 1561 · vectorized true 1562 render · · 1563 └── scan · · 1564 · table t4@primary 1565 · spans /10-/11 1566 1567 # Regression test for #40890: a point lookup on a single column family of a 1568 # table should still work properly in the face of a constraint disjunction. 1569 query TTT 1570 EXPLAIN SELECT a FROM t4 WHERE a in (1, 5) and b in (1, 5) 1571 ---- 1572 · distributed false 1573 · vectorized true 1574 render · · 1575 └── scan · · 1576 · table t4@primary 1577 · spans /1/1/0-/1/1/1 /1/5/0-/1/5/1 /5/1/0-/5/1/1 /5/5/0-/5/5/1 1578 · parallel ·