github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/exec/execbuilder/testdata/select (about) 1 # LogicTest: local 2 3 # Prepare a trace to be inspected below. 4 5 statement ok 6 SET tracing = on; BEGIN; SELECT 1; COMMIT; SELECT 2; SET tracing = off 7 8 # Inspect the trace: we exclude messages containing newlines as these 9 # may contain non-deterministic txn object descriptions. 10 # This also checks that the span column properly reports separate 11 # SQL transactions. 12 # We replace the command position because the values depend on exactly 13 # how many commands we ran in the session. 14 query ITT 15 SELECT 16 span, regexp_replace(message, 'pos:[0-9]*', 'pos:?'), operation 17 FROM [SHOW TRACE FOR SESSION] 18 WHERE message LIKE '%SPAN START%' OR message LIKE '%pos%executing%'; 19 ---- 20 0 === SPAN START: session recording === session recording 21 1 === SPAN START: exec stmt === exec stmt 22 1 [NoTxn pos:?] executing ExecStmt: BEGIN TRANSACTION exec stmt 23 2 === SPAN START: sql txn === sql txn 24 3 === SPAN START: exec stmt === exec stmt 25 3 [Open pos:?] executing ExecStmt: SELECT 1 exec stmt 26 4 === SPAN START: consuming rows === consuming rows 27 5 === SPAN START: flow === flow 28 6 === SPAN START: exec stmt === exec stmt 29 6 [Open pos:?] executing ExecStmt: COMMIT TRANSACTION exec stmt 30 7 === SPAN START: exec stmt === exec stmt 31 7 [NoTxn pos:?] executing ExecStmt: SELECT 2 exec stmt 32 8 === SPAN START: sql txn === sql txn 33 9 === SPAN START: exec stmt === exec stmt 34 9 [Open pos:?] executing ExecStmt: SELECT 2 exec stmt 35 10 === SPAN START: consuming rows === consuming rows 36 11 === SPAN START: flow === flow 37 12 === SPAN START: exec stmt === exec stmt 38 12 [NoTxn pos:?] executing ExecStmt: SET TRACING = off exec stmt 39 40 # ------------------------------------------------------------------------------ 41 # Numeric References Tests. 42 # These are put at the beginning of the file to ensure the numeric table 43 # reference is 53 (the numeric reference of the first table). 44 # If the numbering scheme in cockroach changes, this test will break. 45 # These tests replicate the tests at sql/table_ref_test.go. The reason 46 # for duplication is to include tests within the opt testing framework 47 # TODO(madhavsuresh): get the numeric reference ID in a less brittle fashion 48 # ------------------------------------------------------------------------------ 49 statement ok 50 CREATE TABLE num_ref (a INT PRIMARY KEY, xx INT, b INT, c INT, INDEX bc (b,c)) 51 52 statement ok 53 CREATE TABLE num_ref_hidden (a INT, b INT) 54 55 statement ok 56 ALTER TABLE num_ref RENAME COLUMN b TO d 57 58 statement ok 59 ALTER TABLE num_ref RENAME COLUMN a TO p 60 61 statement ok 62 ALTER TABLE num_ref DROP COLUMN xx 63 64 query TTTTT 65 EXPLAIN (VERBOSE) SELECT * FROM [53 AS num_ref_alias] 66 ---- 67 · distributed false · · 68 · vectorized true · · 69 scan · · (p, d, c) · 70 · table num_ref@primary · · 71 · spans FULL SCAN · · 72 73 query TTTTT 74 EXPLAIN (VERBOSE) SELECT * FROM [53(4) AS num_ref_alias] 75 ---- 76 · distributed false · · 77 · vectorized true · · 78 scan · · (c) · 79 · table num_ref@primary · · 80 · spans FULL SCAN · · 81 82 query TTTTT 83 EXPLAIN (VERBOSE) SELECT * FROM [53(1,4) AS num_ref_alias] 84 ---- 85 · distributed false · · 86 · vectorized true · · 87 scan · · (p, c) · 88 · table num_ref@primary · · 89 · spans FULL SCAN · · 90 91 query TTTTT 92 EXPLAIN (VERBOSE) SELECT * FROM [53(1,3,4) AS num_ref_alias] 93 ---- 94 · distributed false · · 95 · vectorized true · · 96 scan · · (p, d, c) · 97 · table num_ref@primary · · 98 · spans FULL SCAN · · 99 100 query TTTTT 101 EXPLAIN (VERBOSE) SELECT * FROM [53(4,3,1) AS num_ref_alias] 102 ---- 103 · distributed false · · 104 · vectorized true · · 105 render · · (c, d, p) · 106 │ render 0 c · · 107 │ render 1 d · · 108 │ render 2 p · · 109 └── scan · · (p, d, c) · 110 · table num_ref@primary · · 111 · spans FULL SCAN · · 112 113 query TTTTT 114 EXPLAIN (VERBOSE) SELECT * FROM [53(4,3,1) AS num_ref_alias(col1,col2,col3)] 115 ---- 116 · distributed false · · 117 · vectorized true · · 118 render · · (col1, col2, col3) · 119 │ render 0 c · · 120 │ render 1 d · · 121 │ render 2 p · · 122 └── scan · · (p, d, c) · 123 · table num_ref@primary · · 124 · spans FULL SCAN · · 125 126 query TTTTT 127 EXPLAIN (VERBOSE) SELECT * FROM [53(4,3,1) AS num_ref_alias]@bc 128 ---- 129 · distributed false · · 130 · vectorized true · · 131 render · · (c, d, p) · 132 │ render 0 c · · 133 │ render 1 d · · 134 │ render 2 p · · 135 └── scan · · (p, d, c) · 136 · table num_ref@bc · · 137 · spans FULL SCAN · · 138 139 query TTTTT 140 EXPLAIN (VERBOSE) SELECT * FROM [53(4) AS num_ref_alias]@bc 141 ---- 142 · distributed false · · 143 · vectorized true · · 144 scan · · (c) · 145 · table num_ref@bc · · 146 · spans FULL SCAN · · 147 148 query TTTTT 149 EXPLAIN (VERBOSE) SELECT * FROM [53(3) AS num_ref_alias]@bc 150 ---- 151 · distributed false · · 152 · vectorized true · · 153 scan · · (d) · 154 · table num_ref@bc · · 155 · spans FULL SCAN · · 156 157 query TTTTT 158 EXPLAIN (VERBOSE) SELECT * FROM [53(1) AS num_ref_alias]@bc 159 ---- 160 · distributed false · · 161 · vectorized true · · 162 scan · · (p) · 163 · table num_ref@bc · · 164 · spans FULL SCAN · · 165 166 query TTTTT 167 EXPLAIN (VERBOSE) SELECT * FROM [53(1) AS num_ref_alias]@[1] 168 ---- 169 · distributed false · · 170 · vectorized true · · 171 scan · · (p) · 172 · table num_ref@primary · · 173 · spans FULL SCAN · · 174 175 query TTTTT 176 EXPLAIN (VERBOSE) SELECT * FROM [53(1) AS num_ref_alias]@[2] 177 ---- 178 · distributed false · · 179 · vectorized true · · 180 scan · · (p) · 181 · table num_ref@bc · · 182 · spans FULL SCAN · · 183 184 query TTTTT 185 EXPLAIN (VERBOSE) SELECT * FROM [53(3) AS num_ref_alias]@[1] 186 ---- 187 · distributed false · · 188 · vectorized true · · 189 scan · · (d) · 190 · table num_ref@primary · · 191 · spans FULL SCAN · · 192 193 query TTTTT 194 EXPLAIN (VERBOSE) SELECT * FROM [53(3) AS num_ref_alias]@[2] 195 ---- 196 · distributed false · · 197 · vectorized true · · 198 scan · · (d) · 199 · table num_ref@bc · · 200 · spans FULL SCAN · · 201 202 query TTTTT 203 EXPLAIN (VERBOSE) SELECT * FROM [53(4) AS num_ref_alias]@[1] 204 ---- 205 · distributed false · · 206 · vectorized true · · 207 scan · · (c) · 208 · table num_ref@primary · · 209 · spans FULL SCAN · · 210 211 query TTTTT 212 EXPLAIN (VERBOSE) SELECT * FROM [53(4) AS num_ref_alias]@[2] 213 ---- 214 · distributed false · · 215 · vectorized true · · 216 scan · · (c) · 217 · table num_ref@bc · · 218 · spans FULL SCAN · · 219 220 query TTTTT 221 EXPLAIN (VERBOSE) SELECT * FROM [54(1,3) AS num_ref_alias] 222 ---- 223 · distributed false · · 224 · vectorized true · · 225 scan · · (a) · 226 · table num_ref_hidden@primary · · 227 · spans FULL SCAN · · 228 229 query TTTTT 230 EXPLAIN (VERBOSE) SELECT * FROM [54(3) AS num_ref_alias] 231 ---- 232 · distributed false · · 233 · vectorized true · · 234 scan · · () · 235 · table num_ref_hidden@primary · · 236 · spans FULL SCAN · · 237 238 query TTTTT 239 EXPLAIN (VERBOSE) SELECT rowid FROM [54(3) AS num_ref_alias] 240 ---- 241 · distributed false · · 242 · vectorized true · · 243 scan · · (rowid[hidden]) · 244 · table num_ref_hidden@primary · · 245 · spans FULL SCAN · · 246 247 query error pq: \[666\(1\) AS num_ref_alias\]: relation \"\[666\]\" does not exist 248 EXPLAIN (VERBOSE) SELECT * FROM [666(1) AS num_ref_alias] 249 250 query error pq: column \[666\] does not exist 251 EXPLAIN (VERBOSE) SELECT * FROM [53(666) AS num_ref_alias] 252 253 query error pq: column \[2\] does not exist 254 EXPLAIN (VERBOSE) SELECT * FROM [53(2) AS num_ref_alias] 255 256 query error pq: an explicit list of column IDs must include at least one column 257 EXPLAIN (VERBOSE) SELECT * FROM [53() AS num_ref_alias] 258 259 query error pq: an explicit list of column IDs must include at least one column 260 EXPLAIN (VERBOSE) SELECT 1 FROM [53() as num_ref_alias] 261 262 statement ok 263 DROP TABLE num_ref 264 265 query error pq: \[53\(1\) AS num_ref_alias\]: table is being dropped 266 EXPLAIN (VERBOSE) SELECT * FROM [53(1) AS num_ref_alias] 267 268 # ------------------------------------------------------------------------------ 269 # Basic filter combinations. 270 # ------------------------------------------------------------------------------ 271 statement ok 272 CREATE TABLE a (x INT PRIMARY KEY, y INT, FAMILY (x, y)); 273 274 query TTTTT 275 EXPLAIN (VERBOSE) SELECT * FROM a WHERE x > 1 276 ---- 277 · distributed false · · 278 · vectorized true · · 279 scan · · (x, y) · 280 · table a@primary · · 281 · spans /2- · · 282 283 query TTTTT 284 EXPLAIN (VERBOSE) SELECT * FROM a WHERE y > 10 285 ---- 286 · distributed false · · 287 · vectorized true · · 288 scan · · (x, y) · 289 · table a@primary · · 290 · spans FULL SCAN · · 291 · filter y > 10 · · 292 293 query TTTTT 294 EXPLAIN (VERBOSE) SELECT * FROM a WHERE x > 1 AND x < 3 295 ---- 296 · distributed false · · 297 · vectorized true · · 298 scan · · (x, y) · 299 · table a@primary · · 300 · spans /2-/2/# · · 301 302 query TTTTT 303 EXPLAIN (VERBOSE) SELECT * FROM a WHERE x > 1 AND y < 30 304 ---- 305 · distributed false · · 306 · vectorized true · · 307 scan · · (x, y) · 308 · table a@primary · · 309 · spans /2- · · 310 · filter y < 30 · · 311 312 query TTTTT 313 EXPLAIN (VERBOSE) SELECT x + 1 AS r FROM a 314 ---- 315 · distributed false · · 316 · vectorized true · · 317 render · · (r) · 318 │ render 0 x + 1 · · 319 └── scan · · (x) · 320 · table a@primary · · 321 · spans FULL SCAN · · 322 323 query TTTTT 324 EXPLAIN (VERBOSE) SELECT x AS a, x + 1 AS b, y, y + 1 AS c, x + y AS d FROM a 325 ---- 326 · distributed false · · 327 · vectorized true · · 328 render · · (a, b, y, c, d) · 329 │ render 0 x · · 330 │ render 1 x + 1 · · 331 │ render 2 y · · 332 │ render 3 y + 1 · · 333 │ render 4 x + y · · 334 └── scan · · (x, y) · 335 · table a@primary · · 336 · spans FULL SCAN · · 337 338 query TTTTT 339 EXPLAIN (VERBOSE) SELECT u * v + v AS r FROM (SELECT x + 3, y + 10 FROM a) AS foo(u, v) 340 ---- 341 · distributed false · · 342 · vectorized true · · 343 render · · (r) · 344 │ render 0 "?column?" + ("?column?" * "?column?") · · 345 └── render · · ("?column?", "?column?") · 346 │ render 0 x + 3 · · 347 │ render 1 y + 10 · · 348 └── scan · · (x, y) · 349 · table a@primary · · 350 · spans FULL SCAN · · 351 352 query TTTTT 353 EXPLAIN (VERBOSE) SELECT x, x, y, x FROM a 354 ---- 355 · distributed false · · 356 · vectorized true · · 357 render · · (x, x, y, x) · 358 │ render 0 x · · 359 │ render 1 x · · 360 │ render 2 y · · 361 │ render 3 x · · 362 └── scan · · (x, y) · 363 · table a@primary · · 364 · spans FULL SCAN · · 365 366 query TTTTT 367 EXPLAIN (VERBOSE) SELECT x + 1 AS a, x + y AS b FROM a WHERE x + y > 20 368 ---- 369 · distributed false · · 370 · vectorized true · · 371 render · · (a, b) · 372 │ render 0 x + 1 · · 373 │ render 1 x + y · · 374 └── scan · · (x, y) · 375 · table a@primary · · 376 · spans FULL SCAN · · 377 · filter (x + y) > 20 · · 378 379 statement ok 380 DROP TABLE a 381 382 # ------------------------------------------------------------------------------ 383 # Test with a hidden column. 384 # ------------------------------------------------------------------------------ 385 statement ok 386 CREATE TABLE b (x INT, y INT); 387 388 query TTTTT 389 EXPLAIN (VERBOSE) SELECT * FROM b 390 ---- 391 · distributed false · · 392 · vectorized true · · 393 scan · · (x, y) · 394 · table b@primary · · 395 · spans FULL SCAN · · 396 397 query TTTTT 398 EXPLAIN (VERBOSE) SELECT x, y, rowid FROM b WHERE rowid > 0 399 ---- 400 · distributed false · · 401 · vectorized true · · 402 scan · · (x, y, rowid[hidden]) · 403 · table b@primary · · 404 · spans /1- · · 405 406 statement ok 407 DROP TABLE b 408 409 # ------------------------------------------------------------------------------ 410 # Test with storing columns. 411 # ------------------------------------------------------------------------------ 412 statement ok 413 CREATE TABLE t ( 414 a INT PRIMARY KEY, 415 b INT, 416 c INT, 417 d INT, 418 INDEX b_idx (b) STORING (c, d), 419 UNIQUE INDEX c_idx (c) STORING (b, d), 420 FAMILY (a, b, c, d) 421 ) 422 423 query TTBITTBB colnames 424 SHOW INDEXES FROM t 425 ---- 426 table_name index_name non_unique seq_in_index column_name direction storing implicit 427 t primary false 1 a ASC false false 428 t b_idx true 1 b ASC false false 429 t b_idx true 2 c N/A true false 430 t b_idx true 3 d N/A true false 431 t b_idx true 4 a ASC false true 432 t c_idx false 1 c ASC false false 433 t c_idx false 2 b N/A true false 434 t c_idx false 3 d N/A true false 435 t c_idx false 4 a ASC false true 436 437 statement ok 438 INSERT INTO t VALUES (1, 2, 3, 4) 439 440 statement ok 441 SET tracing = on,kv,results; SELECT * FROM t@b_idx; SET tracing = off 442 443 query T 444 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 445 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 446 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 447 ---- 448 fetched: /t/b_idx/2/1/c/d -> /3/4 449 output row: [1 2 3 4] 450 451 statement ok 452 SET tracing = on,kv,results; SELECT * FROM t@c_idx; SET tracing = off 453 454 query T 455 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 456 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 457 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 458 ---- 459 fetched: /t/c_idx/3/b/d -> /2/4 460 output row: [1 2 3 4] 461 462 # Test index backfill for UNIQUE and non-UNIQUE indexes with STORING columns. 463 464 statement ok 465 CREATE INDEX d_idx ON t (d) STORING (b) 466 467 statement ok 468 SET tracing = on,kv,results; SELECT a, b, d FROM t@d_idx; SET tracing = off 469 470 query T 471 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 472 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 473 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 474 ---- 475 fetched: /t/d_idx/4/1/b -> /2 476 output row: [1 2 4] 477 478 statement ok 479 CREATE UNIQUE INDEX a_idx ON t (a) STORING (b) 480 481 statement ok 482 SET tracing = on,kv,results; SELECT a, b FROM t@a_idx; SET tracing = off 483 484 query T 485 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 486 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 487 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 488 ---- 489 fetched: /t/a_idx/1/b -> /2 490 output row: [1 2] 491 492 # Test that unspecified storing values are treated like NULL values. 493 statement ok 494 INSERT INTO t (a) VALUES (2) 495 496 statement ok 497 INSERT INTO t VALUES (3) 498 499 statement ok 500 SET tracing = on,kv,results; SELECT * FROM t@b_idx; SET tracing = off 501 502 query T 503 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 504 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 505 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 506 ---- 507 fetched: /t/b_idx/NULL/2 -> NULL 508 fetched: /t/b_idx/NULL/3 -> NULL 509 fetched: /t/b_idx/2/1/c/d -> /3/4 510 output row: [2 NULL NULL NULL] 511 output row: [3 NULL NULL NULL] 512 output row: [1 2 3 4] 513 514 # Regression test for #14601. 515 516 statement ok 517 CREATE TABLE t14601 (a STRING, b BOOL) 518 519 statement ok 520 CREATE INDEX i14601 ON t14601 (a) STORING (b) 521 522 query TTT 523 EXPLAIN SELECT a FROM t14601 ORDER BY a 524 ---- 525 · distributed false 526 · vectorized true 527 scan · · 528 · table t14601@i14601 529 · spans FULL SCAN 530 531 # Updates were broken too. 532 533 statement ok 534 CREATE TABLE t14601a ( 535 a STRING, 536 b BOOL, 537 c INT, 538 FAMILY f1 (a), 539 FAMILY f2 (b), 540 FAMILY f3 (c) 541 ) 542 543 statement ok 544 CREATE INDEX i14601a ON t14601a (a) STORING (b, c) 545 546 query TTT 547 EXPLAIN SELECT a, b FROM t14601a ORDER BY a 548 ---- 549 · distributed false 550 · vectorized true 551 scan · · 552 · table t14601a@i14601a 553 · spans FULL SCAN 554 555 statement ok 556 DROP index i14601a 557 558 statement ok 559 CREATE UNIQUE INDEX i14601a ON t14601a (a) STORING (b) 560 561 query TTT 562 EXPLAIN SELECT a, b FROM t14601a ORDER BY a 563 ---- 564 · distributed false 565 · vectorized true 566 scan · · 567 · table t14601a@i14601a 568 · spans FULL SCAN 569 570 statement ok 571 DROP TABLE t; DROP TABLE t14601; DROP TABLE t14601a 572 573 # ------------------------------------------------------------------------------ 574 # String inequality filter. 575 # ------------------------------------------------------------------------------ 576 statement ok 577 CREATE TABLE c (n INT PRIMARY KEY, str STRING, INDEX str(str DESC)); 578 579 query TTTTT 580 EXPLAIN (VERBOSE) SELECT * FROM c WHERE str >= 'moo' 581 ---- 582 · distributed false · · 583 · vectorized true · · 584 scan · · (n, str) · 585 · table c@str · · 586 · spans -/"moo"/PrefixEnd · · 587 588 statement ok 589 DROP TABLE c 590 591 # ------------------------------------------------------------------------------ 592 # "*" must expand to zero columns if there are zero columns to select. 593 # ------------------------------------------------------------------------------ 594 statement ok 595 CREATE TABLE nocols(x INT); ALTER TABLE nocols DROP COLUMN x 596 597 query TTTTT 598 EXPLAIN (VERBOSE) SELECT 1 AS a, * FROM nocols 599 ---- 600 · distributed false · · 601 · vectorized true · · 602 render · · (a) · 603 │ render 0 1 · · 604 └── scan · · () · 605 · table nocols@primary · · 606 · spans FULL SCAN · · 607 608 statement ok 609 DROP TABLE nocols 610 611 # ------------------------------------------------------------------------------ 612 # Ensure that index is used when indexed column has collation. 613 # ------------------------------------------------------------------------------ 614 statement ok 615 CREATE TABLE coll ( 616 a STRING COLLATE da, 617 b INT, 618 c BOOL, 619 PRIMARY KEY (a, b), 620 INDEX (b, a) STORING (c) 621 ) 622 623 query TTTTT 624 EXPLAIN (TYPES) SELECT a, b FROM coll ORDER BY a, b 625 ---- 626 · distributed false · · 627 · vectorized true · · 628 scan · · (a collatedstring{da}, b int) +a,+b 629 · table coll@primary · · 630 · spans FULL SCAN · · 631 632 query TTTTT 633 EXPLAIN (TYPES) SELECT b, a FROM coll ORDER BY b, a 634 ---- 635 · distributed false · · 636 · vectorized true · · 637 render · · (b int, a collatedstring{da}) · 638 │ render 0 (b)[int] · · 639 │ render 1 (a)[collatedstring{da}] · · 640 └── scan · · (a collatedstring{da}, b int) +b,+a 641 · table coll@coll_b_a_idx · · 642 · spans FULL SCAN · · 643 644 statement ok 645 DROP TABLE coll 646 647 # ------------------------------------------------------------------------------ 648 # Ensure correct index is used when indexed column is computed. 649 # ------------------------------------------------------------------------------ 650 statement ok 651 CREATE TABLE computed ( 652 k INT PRIMARY KEY, 653 a JSON, 654 b TEXT AS (a->>'q') STORED, 655 INDEX (b) 656 ) 657 658 query TTTTT 659 EXPLAIN (TYPES) SELECT b FROM computed ORDER BY b 660 ---- 661 · distributed false · · 662 · vectorized true · · 663 scan · · (b string) +b 664 · table computed@computed_b_idx · · 665 · spans FULL SCAN · · 666 667 statement ok 668 DROP TABLE computed 669 670 # ------------------------------------------------------------------------------ 671 # Ensure that Select filter probes expected date/time key/values that are in 672 # different column families. 673 # ------------------------------------------------------------------------------ 674 statement ok 675 CREATE TABLE dt ( 676 a TIMESTAMP PRIMARY KEY, 677 b DATE, 678 c INTERVAL, 679 UNIQUE (b), 680 UNIQUE (c), 681 FAMILY (a), 682 FAMILY (b), 683 FAMILY (c) 684 ) 685 686 statement ok 687 INSERT INTO dt VALUES 688 ('2015-08-30 03:34:45.34567', '2015-08-30', '34h2s'), 689 ('2015-08-25 04:45:45.53453', '2015-08-25', '2h45m2s234ms'), 690 ('2015-08-29 23:10:09.98763', '2015-08-29', '234h45m2s234ms') 691 692 statement ok 693 SET tracing = on,kv,results; SELECT * FROM dt WHERE a = '2015-08-25 04:45:45.53453+02:00'::timestamp; SET tracing = off 694 695 query T 696 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 697 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 698 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 699 ---- 700 fetched: /dt/primary/'2015-08-25 04:45:45.53453+00:00' -> NULL 701 fetched: /dt/primary/'2015-08-25 04:45:45.53453+00:00'/b -> '2015-08-25' 702 fetched: /dt/primary/'2015-08-25 04:45:45.53453+00:00'/c -> '02:45:02.234' 703 output row: ['2015-08-25 04:45:45.53453+00:00' '2015-08-25' '02:45:02.234'] 704 705 statement ok 706 SET tracing = on,kv,results; SELECT b FROM dt WHERE b < '2015-08-29'::date; SET tracing = off 707 708 query T 709 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 710 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 711 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 712 ---- 713 fetched: /dt/dt_b_key/'2015-08-25' -> /'2015-08-25 04:45:45.53453+00:00' 714 output row: ['2015-08-25'] 715 716 statement ok 717 SET tracing = on,kv,results; SELECT c FROM dt WHERE c < '234h45m2s234ms'::interval; SET tracing = off 718 719 query T 720 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 721 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 722 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 723 ---- 724 fetched: /dt/dt_c_key/'02:45:02.234' -> /'2015-08-25 04:45:45.53453+00:00' 725 fetched: /dt/dt_c_key/'34:00:02' -> /'2015-08-30 03:34:45.34567+00:00' 726 output row: ['02:45:02.234'] 727 output row: ['34:00:02'] 728 729 statement ok 730 DROP TABLE dt 731 732 # ------------------------------------------------------------------------------ 733 # Ensure that decimal values result in correct scan spans. 734 # ------------------------------------------------------------------------------ 735 statement ok 736 CREATE TABLE dec (d decimal, v decimal(3, 1), primary key (d, v)) 737 738 query TTTTT 739 EXPLAIN (TYPES) SELECT * FROM dec WHERE d IS NaN and v IS NaN 740 ---- 741 · distributed false · · 742 · vectorized true · · 743 scan · · (d decimal, v decimal) · 744 · table dec@primary · · 745 · spans /NaN/NaN-/NaN/NaN/# · · 746 747 # The NaN suffix is decimalNaNDesc, not decimalNaN(Asc). 748 query TTTTT 749 EXPLAIN (TYPES) SELECT * FROM dec WHERE d = 'Infinity' and v = 'Infinity' 750 ---- 751 · distributed false · · 752 · vectorized true · · 753 scan · · (d decimal, v decimal) · 754 · table dec@primary · · 755 · spans /Infinity/Infinity-/Infinity/Infinity/# · · 756 757 query TTTTT 758 EXPLAIN (TYPES) SELECT * FROM dec WHERE d = '-Infinity' and v = '-Infinity' 759 ---- 760 · distributed false · · 761 · vectorized true · · 762 scan · · (d decimal, v decimal) · 763 · table dec@primary · · 764 · spans /-Infinity/-Infinity-/-Infinity/-Infinity/# · · 765 766 statement ok 767 DROP TABLE dec 768 769 # Test composite encoding of DECIMAL type in indexes. 770 statement ok 771 CREATE TABLE c ( 772 a INT PRIMARY KEY, 773 b DECIMAL(2,2), 774 INDEX b_idx (b) 775 ) 776 777 statement ok 778 INSERT INTO c VALUES(1, 0.4) 779 780 # Test that unspecifying b is like specifying NULL. 781 statement ok 782 INSERT INTO c (a) VALUES(2) 783 784 statement ok 785 INSERT INTO c VALUES(3) 786 787 statement ok 788 SET tracing = on,kv,results; SELECT * FROM c@b_idx; SET tracing = off 789 790 query T 791 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 792 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 793 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 794 ---- 795 fetched: /c/b_idx/NULL/2 -> NULL 796 fetched: /c/b_idx/NULL/3 -> NULL 797 fetched: /c/b_idx/0.4/1/b -> /0.40 798 output row: [2 NULL] 799 output row: [3 NULL] 800 output row: [1 0.40] 801 802 # ------------------------------------------------------------------------------ 803 # Verify that lookups for Decimal NaN use indices when possible: 804 # - `WHERE d IS NaN` should perform a point lookup. 805 # - `WHERE d = 'NaN'` should also perform a point lookup. 806 # - `WHERE isnan(d)` is a function so it can't perform a point lookup. 807 # ------------------------------------------------------------------------------ 808 statement ok 809 CREATE TABLE dec2 (d decimal null, index (d)) 810 811 query TTTTT 812 EXPLAIN (TYPES) SELECT * FROM dec2 WHERE d IS NaN 813 ---- 814 · distributed false · · 815 · vectorized true · · 816 scan · · (d decimal) · 817 · table dec2@dec2_d_idx · · 818 · spans /NaN-/-Infinity · · 819 820 query TTTTT 821 EXPLAIN (TYPES) SELECT * FROM dec2 WHERE d = 'NaN' 822 ---- 823 · distributed false · · 824 · vectorized true · · 825 scan · · (d decimal) · 826 · table dec2@dec2_d_idx · · 827 · spans /NaN-/-Infinity · · 828 829 query TTTTT 830 EXPLAIN (TYPES) SELECT * FROM dec2 WHERE isnan(d) 831 ---- 832 · distributed false · · 833 · vectorized true · · 834 scan · · (d decimal) · 835 · table dec2@primary · · 836 · spans FULL SCAN · · 837 · filter (isnan((d)[decimal]))[bool] · · 838 839 statement ok 840 DROP TABLE dec2 841 842 # ------------------------------------------------------------------------------ 843 # Verify that lookups for Float NaN use indices when possible: 844 # - `WHERE f IS NaN` should perform a point lookup. 845 # - `WHERE f = 'NaN'` should also perform a point lookup. 846 # - `WHERE isnan(f)` is a function so it can't perform a point lookup. 847 # ------------------------------------------------------------------------------ 848 statement ok 849 CREATE TABLE flt (f float null, unique index (f)) 850 851 query TTTTT 852 EXPLAIN (TYPES) SELECT * FROM flt WHERE f IS NaN 853 ---- 854 · distributed false · · 855 · vectorized true · · 856 scan · · (f float) · 857 · table flt@flt_f_key · · 858 · spans /NaN-/NaN/PrefixEnd · · 859 860 query TTTTT 861 EXPLAIN (TYPES) SELECT * FROM flt WHERE f = 'NaN' 862 ---- 863 · distributed false · · 864 · vectorized true · · 865 scan · · (f float) · 866 · table flt@flt_f_key · · 867 · spans /NaN-/NaN/PrefixEnd · · 868 869 query TTTTT 870 EXPLAIN (TYPES) SELECT * FROM flt WHERE isnan(f) 871 ---- 872 · distributed false · · 873 · vectorized true · · 874 scan · · (f float) · 875 · table flt@primary · · 876 · spans FULL SCAN · · 877 · filter (isnan((f)[float]))[bool] · · 878 879 statement ok 880 DROP TABLE flt 881 882 # ------------------------------------------------------------------------------ 883 # Verify we create the correct spans for negative numbers with extra 884 # operations. 885 # ------------------------------------------------------------------------------ 886 887 statement ok 888 CREATE TABLE num ( 889 i int null, 890 unique index (i), 891 f float null, 892 unique index (f), 893 d decimal null, 894 unique index (d), 895 n interval null, 896 unique index (n) 897 ) 898 899 query TTTTT 900 EXPLAIN (TYPES) SELECT i FROM num WHERE i = -1:::INT 901 ---- 902 · distributed false · · 903 · vectorized true · · 904 scan · · (i int) · 905 · table num@num_i_key · · 906 · spans /-1-/0 · · 907 908 query TTTTT 909 EXPLAIN (TYPES) SELECT f FROM num WHERE f = -1:::FLOAT 910 ---- 911 · distributed false · · 912 · vectorized true · · 913 scan · · (f float) · 914 · table num@num_f_key · · 915 · spans /-1-/-1/PrefixEnd · · 916 917 query TTTTT 918 EXPLAIN (TYPES) SELECT d FROM num WHERE d = -1:::DECIMAL 919 ---- 920 · distributed false · · 921 · vectorized true · · 922 scan · · (d decimal) · 923 · table num@num_d_key · · 924 · spans /-1-/-1/PrefixEnd · · 925 926 query TTTTT 927 EXPLAIN (TYPES) SELECT n FROM num WHERE n = -'1h':::INTERVAL 928 ---- 929 · distributed false · · 930 · vectorized true · · 931 scan · · (n interval) · 932 · table num@num_n_key · · 933 · spans /-01:00:00-/1 day -25:00:00 · · 934 935 statement ok 936 DROP TABLE num 937 938 # ------------------------------------------------------------------------------ 939 # ANY, ALL tests. 940 # ------------------------------------------------------------------------------ 941 statement ok 942 CREATE TABLE abc (a INT, b INT, c INT) 943 944 statement ok 945 INSERT INTO abc VALUES (1, 10, 100), (2, 20, 200), (3, 30, 300) 946 947 query III 948 SELECT * FROM abc WHERE a = ANY(SELECT a FROM abc WHERE b = 10) 949 ---- 950 1 10 100 951 952 query III 953 SELECT * FROM abc WHERE a < ANY(SELECT a FROM abc WHERE b = 30) ORDER BY a 954 ---- 955 1 10 100 956 2 20 200 957 958 query III 959 SELECT * FROM abc WHERE a > ANY(SELECT a FROM abc WHERE b = 30) 960 ---- 961 962 query III 963 SELECT * FROM abc WHERE a < ALL(SELECT b FROM abc) ORDER BY a 964 ---- 965 1 10 100 966 2 20 200 967 3 30 300 968 969 query III 970 SELECT * FROM abc WHERE a < ALL(SELECT a FROM abc WHERE a >= 2) 971 ---- 972 1 10 100 973 974 query III 975 SELECT * FROM abc WHERE a < ALL(SELECT a FROM abc) 976 ---- 977 978 statement ok 979 DROP TABLE abc 980 981 # ------------------------------------------------------------------------------ 982 # IN tests. 983 # ------------------------------------------------------------------------------ 984 # Regression tests for #22670. 985 query B 986 SELECT 1 IN (1, 2) 987 ---- 988 true 989 990 query B 991 SELECT NULL IN (1, 2) 992 ---- 993 NULL 994 995 query B 996 SELECT 1 IN (1, NULL) 997 ---- 998 true 999 1000 query B 1001 SELECT 1 IN (NULL, 2) 1002 ---- 1003 NULL 1004 1005 query B 1006 SELECT (1, NULL) IN ((1, 1)) 1007 ---- 1008 NULL 1009 1010 query B 1011 SELECT (2, NULL) IN ((1, 1)) 1012 ---- 1013 false 1014 1015 query B 1016 SELECT (1, 1) IN ((1, NULL)) 1017 ---- 1018 NULL 1019 1020 query B 1021 SELECT (1, 1) IN ((2, NULL)) 1022 ---- 1023 false 1024 1025 # Tests with a tuple coming from a subquery. 1026 query B 1027 SELECT NULL::int IN (SELECT * FROM (VALUES (1)) AS t(a)) 1028 ---- 1029 NULL 1030 1031 query B 1032 SELECT (1, NULL::int) IN (SELECT * FROM (VALUES (1, 1)) AS t(a, b)) 1033 ---- 1034 NULL 1035 1036 query B 1037 SELECT (2, NULL::int) IN (SELECT * FROM (VALUES (1, 1)) AS t(a, b)) 1038 ---- 1039 false 1040 1041 query B 1042 SELECT (NULL::int, 1) IN (SELECT * FROM (VALUES (1, 1)) AS t(a, b)) 1043 ---- 1044 NULL 1045 1046 query B 1047 SELECT (NULL::int, 2) IN (SELECT * FROM (VALUES (1, 1)) AS t(a, b)) 1048 ---- 1049 false 1050 1051 query B 1052 SELECT (NULL::int, NULL::int) IN (SELECT * FROM (VALUES (1, 1)) AS t(a, b)) 1053 ---- 1054 NULL 1055 1056 query B 1057 SELECT NULL::int NOT IN (SELECT * FROM (VALUES (1)) AS t(a)) 1058 ---- 1059 NULL 1060 1061 query B 1062 SELECT (1, NULL::int) NOT IN (SELECT * FROM (VALUES (1, 1)) AS t(a, b)) 1063 ---- 1064 NULL 1065 1066 query B 1067 SELECT (2, NULL::int) NOT IN (SELECT * FROM (VALUES (1, 1)) AS t(a, b)) 1068 ---- 1069 true 1070 1071 query B 1072 SELECT (NULL::int, 1) NOT IN (SELECT * FROM (VALUES (1, 1)) AS t(a, b)) 1073 ---- 1074 NULL 1075 1076 query B 1077 SELECT (NULL::int, 2) NOT IN (SELECT * FROM (VALUES (1, 1)) AS t(a, b)) 1078 ---- 1079 true 1080 1081 query B 1082 SELECT (NULL::int, NULL::int) NOT IN (SELECT * FROM (VALUES (1, 1)) AS t(a, b)) 1083 ---- 1084 NULL 1085 1086 # Tests with an empty IN tuple. 1087 query B 1088 SELECT NULL::int IN (SELECT * FROM (VALUES (1)) AS t(a) WHERE a > 1) 1089 ---- 1090 false 1091 1092 query B 1093 SELECT (1, NULL::int) IN (SELECT * FROM (VALUES (1, 1)) AS t(a, b) WHERE a > 1) 1094 ---- 1095 false 1096 1097 query B 1098 SELECT (NULL::int, 1) IN (SELECT * FROM (VALUES (1, 1)) AS t(a, b) WHERE a > 1) 1099 ---- 1100 false 1101 1102 query B 1103 SELECT (NULL::int, NULL::int) IN (SELECT * FROM (VALUES (1, 1)) AS t(a, b) WHERE a > 1) 1104 ---- 1105 false 1106 1107 query B 1108 SELECT NULL::int NOT IN (SELECT * FROM (VALUES (1)) AS t(a) WHERE a > 1) 1109 ---- 1110 true 1111 1112 query B 1113 SELECT (1, NULL::int) NOT IN (SELECT * FROM (VALUES (1, 1)) AS t(a, b) WHERE a > 1) 1114 ---- 1115 true 1116 1117 query B 1118 SELECT (NULL::int, 1) NOT IN (SELECT * FROM (VALUES (1, 1)) AS t(a, b) WHERE a > 1) 1119 ---- 1120 true 1121 1122 query B 1123 SELECT (NULL::int, NULL::int) NOT IN (SELECT * FROM (VALUES (1, 1)) AS t(a, b) WHERE a > 1) 1124 ---- 1125 true 1126 1127 statement ok 1128 CREATE TABLE abcd (a INT, b INT, c INT, d INT, PRIMARY KEY (a, b)) 1129 1130 # Ensure that (non-top-level) render nodes get populated with the correct ordering. 1131 query TTTTT 1132 EXPLAIN (VERBOSE) SELECT a + x FROM (SELECT a, b + c AS x FROM abcd) ORDER BY a 1133 ---- 1134 · distributed false · · 1135 · vectorized true · · 1136 render · · ("?column?") · 1137 │ render 0 a + (b + c) · · 1138 └── scan · · (a, b, c) +a 1139 · table abcd@primary · · 1140 · spans FULL SCAN · · 1141 1142 query TTTTT 1143 EXPLAIN (VERBOSE) SELECT a + x FROM (SELECT a, b, a + b + c AS x FROM abcd) ORDER BY b 1144 ---- 1145 · distributed false · · 1146 · vectorized true · · 1147 render · · ("?column?") · 1148 │ render 0 "?column?" · · 1149 └── sort · · ("?column?", b) +b 1150 │ order +b · · 1151 └── render · · ("?column?", b) · 1152 │ render 0 a + (c + (a + b)) · · 1153 │ render 1 b · · 1154 └── scan · · (a, b, c) · 1155 · table abcd@primary · · 1156 · spans FULL SCAN · · 1157 1158 1159 query TTTTT 1160 EXPLAIN (VERBOSE) SELECT a + x FROM (SELECT a, b, a + b + c AS x FROM abcd) ORDER BY a DESC, b DESC 1161 ---- 1162 · distributed false · · 1163 · vectorized true · · 1164 render · · ("?column?") · 1165 │ render 0 a + (c + (a + b)) · · 1166 └── revscan · · (a, b, c) -a,-b 1167 · table abcd@primary · · 1168 · spans FULL SCAN · · 1169 1170 # Ensure that filter nodes (and filtered scan nodes) get populated with the correct ordering. 1171 query TTTTT 1172 EXPLAIN (VERBOSE) SELECT * FROM abcd WHERE a > b ORDER BY a 1173 ---- 1174 · distributed false · · 1175 · vectorized true · · 1176 scan · · (a, b, c, d) +a 1177 · table abcd@primary · · 1178 · spans FULL SCAN · · 1179 · filter a > b · · 1180 1181 query TTTTT 1182 EXPLAIN (VERBOSE) SELECT * FROM abcd WHERE a > b ORDER BY a DESC, b DESC 1183 ---- 1184 · distributed false · · 1185 · vectorized true · · 1186 sort · · (a, b, c, d) -a,-b 1187 │ order -a,-b · · 1188 └── scan · · (a, b, c, d) · 1189 · table abcd@primary · · 1190 · spans FULL SCAN · · 1191 · filter a > b · · 1192 1193 query TTTTT 1194 EXPLAIN (VERBOSE) SELECT * FROM (SELECT a, b FROM abcd LIMIT 10) WHERE a > b ORDER BY a 1195 ---- 1196 · distributed false · · 1197 · vectorized true · · 1198 filter · · (a, b) +a 1199 │ filter a > b · · 1200 └── scan · · (a, b) +a 1201 · table abcd@primary · · 1202 · spans LIMITED SCAN · · 1203 · limit 10 · · 1204 1205 query TTTTT 1206 EXPLAIN (VERBOSE) SELECT * FROM (SELECT a, a+b+c AS x FROM (SELECT * FROM abcd LIMIT 10)) WHERE x > 100 ORDER BY a 1207 ---- 1208 · distributed false · · 1209 · vectorized true · · 1210 render · · (a, x) · 1211 │ render 0 a · · 1212 │ render 1 c + (a + b) · · 1213 └── filter · · (a, b, c) +a 1214 │ filter (c + (a + b)) > 100 · · 1215 └── scan · · (a, b, c) +a 1216 · table abcd@primary · · 1217 · spans LIMITED SCAN · · 1218 · limit 10 · · 1219 1220 statement ok 1221 CREATE TABLE xyz (x INT, y INT, z INT, INDEX(x,y,z)) 1222 1223 # Verify the scan is configured with the correct ordering +x,+y,+z (#31882). 1224 query TTTTT 1225 EXPLAIN (VERBOSE) SELECT * FROM (SELECT * FROM xyz LIMIT 10) WHERE y = 1 ORDER BY x, y, z 1226 ---- 1227 · distributed false · · 1228 · vectorized true · · 1229 filter · · (x, y, z) +x,+z 1230 │ filter y = 1 · · 1231 └── scan · · (x, y, z) +x,+y,+z 1232 · table xyz@xyz_x_y_z_idx · · 1233 · spans LIMITED SCAN · · 1234 · limit 10 · · 1235 1236 # ------------------------------------------------------ 1237 # Verify that multi-span point lookups are parallelized. 1238 # ------------------------------------------------------ 1239 statement ok 1240 CREATE TABLE a (a INT PRIMARY KEY, item STRING, price FLOAT, FAMILY (a, item, price), UNIQUE INDEX item (item), UNIQUE INDEX p (price)) 1241 1242 statement ok 1243 CREATE TABLE b (a INT, b INT, c INT NULL, d INT NULL, PRIMARY KEY (a, b), FAMILY (a, b, c, d)) 1244 1245 # No parallel line printed out for single-span selects. 1246 query TTT 1247 EXPLAIN SELECT * FROM a WHERE a = 10 1248 ---- 1249 · distributed false 1250 · vectorized true 1251 scan · · 1252 · table a@primary 1253 · spans /10-/10/# 1254 1255 query TTT 1256 EXPLAIN SELECT * FROM a WHERE a = 10 OR a = 20 1257 ---- 1258 · distributed false 1259 · vectorized true 1260 scan · · 1261 · table a@primary 1262 · spans /10-/10/# /20-/20/# 1263 · parallel · 1264 1265 query TTT 1266 EXPLAIN SELECT * FROM a WHERE a IN (10, 20) 1267 ---- 1268 · distributed false 1269 · vectorized true 1270 scan · · 1271 · table a@primary 1272 · spans /10-/10/# /20-/20/# 1273 · parallel · 1274 1275 # Verify that consolidated point spans are still parallelized. 1276 query TTT 1277 EXPLAIN SELECT * FROM a WHERE a in (10, 11) 1278 ---- 1279 · distributed false 1280 · vectorized true 1281 scan · · 1282 · table a@primary 1283 · spans /10-/11/# 1284 · parallel · 1285 1286 query TTT 1287 EXPLAIN SELECT * FROM a WHERE a > 10 AND a < 20 1288 ---- 1289 · distributed false 1290 · vectorized true 1291 scan · · 1292 · table a@primary 1293 · spans /11-/19/# 1294 · parallel · 1295 1296 # This ticks all the boxes for parallelization apart from the fact that there 1297 # is no end key in the span. 1298 query TTT 1299 EXPLAIN SELECT * FROM a WHERE a > 10 1300 ---- 1301 · distributed false 1302 · vectorized true 1303 scan · · 1304 · table a@primary 1305 · spans /11- 1306 1307 # Test non-int types. 1308 1309 # Point queries on non-int types are parallel. 1310 query TTT 1311 EXPLAIN SELECT price FROM a WHERE item IN ('sock', 'ball') 1312 ---- 1313 · distributed false 1314 · vectorized true 1315 render · · 1316 └── index-join · · 1317 │ table a@primary 1318 │ key columns a 1319 └── scan · · 1320 · table a@item 1321 · spans /"ball"-/"ball"/PrefixEnd /"sock"-/"sock"/PrefixEnd 1322 · parallel · 1323 1324 # Range queries on non-int types are not parallel due to unbounded number of 1325 # results. 1326 query TTT 1327 EXPLAIN SELECT item FROM a WHERE price > 5 AND price < 10 OR price > 20 AND price < 40 1328 ---- 1329 · distributed false 1330 · vectorized true 1331 render · · 1332 └── index-join · · 1333 │ table a@primary 1334 │ key columns a 1335 └── scan · · 1336 · table a@p 1337 · spans /5.000000000000001-/9.999999999999998/PrefixEnd /20.000000000000004-/39.99999999999999/PrefixEnd 1338 1339 statement ok 1340 SET CLUSTER SETTING sql.parallel_scans.enabled = false 1341 1342 query TTT 1343 EXPLAIN SELECT * FROM a WHERE a IN (10, 20) 1344 ---- 1345 · distributed false 1346 · vectorized true 1347 scan · · 1348 · table a@primary 1349 · spans /10-/10/# /20-/20/# 1350 1351 statement ok 1352 SET CLUSTER SETTING sql.parallel_scans.enabled = true 1353 1354 query TTT 1355 EXPLAIN SELECT * FROM b WHERE (a = 10 AND b = 10) OR (a = 20 AND b = 20) 1356 ---- 1357 · distributed false 1358 · vectorized true 1359 scan · · 1360 · table b@primary 1361 · spans /10/10-/10/10/# /20/20-/20/20/# 1362 · parallel · 1363 1364 # This one isn't parallelizable because it's not a point lookup - only part of 1365 # the primary key is specified. 1366 query TTT 1367 EXPLAIN SELECT * FROM b WHERE a = 10 OR a = 20 1368 ---- 1369 · distributed false 1370 · vectorized true 1371 scan · · 1372 · table b@primary 1373 · spans /10-/11 /20-/21 1374 1375 # This one isn't parallelizable because it has a LIMIT clause. 1376 query TTT 1377 EXPLAIN SELECT * FROM a WHERE a = 10 OR a = 20 LIMIT 1 1378 ---- 1379 · distributed false 1380 · vectorized true 1381 scan · · 1382 · table a@primary 1383 · spans /10-/10/# /20-/20/# 1384 · limit 1 1385 1386 statement ok 1387 CREATE INDEX on b(b) STORING (c) 1388 1389 # This one isn't parallelizable because its index isn't unique. 1390 query TTT 1391 EXPLAIN SELECT b FROM b WHERE b = 10 OR b = 20 1392 ---- 1393 · distributed false 1394 · vectorized true 1395 scan · · 1396 · table b@b_b_idx 1397 · spans /10-/11 /20-/21 1398 1399 statement ok 1400 CREATE UNIQUE INDEX on b(c) 1401 1402 # If the index has nullable values, parallelize only when the spans do not 1403 # specify any nulls. 1404 query TTT 1405 EXPLAIN SELECT c FROM b WHERE c = 10 OR c = 20 1406 ---- 1407 · distributed false 1408 · vectorized true 1409 scan · · 1410 · table b@b_c_key 1411 · spans /10-/11 /20-/21 1412 · parallel · 1413 1414 query TTT 1415 EXPLAIN SELECT c FROM b WHERE c = 10 OR c < 2 1416 ---- 1417 · distributed false 1418 · vectorized true 1419 scan · · 1420 · table b@b_c_key 1421 · spans /!NULL-/2 /10-/11 1422 1423 statement ok 1424 CREATE UNIQUE INDEX on b(d DESC) 1425 1426 # This scan is not parallelizable because the second span has a null in its end 1427 # key. 1428 query TTT 1429 EXPLAIN SELECT d FROM b WHERE d = 10 OR d < 2 1430 ---- 1431 · distributed false 1432 · vectorized true 1433 scan · · 1434 · table b@b_d_key 1435 · spans /10-/9 /1-/NULL 1436 1437 statement ok 1438 CREATE UNIQUE INDEX ON b(c, d) 1439 1440 # This scan is not parallelizable because although the second column is 1441 # constrained, the first column is null. 1442 query TTT 1443 EXPLAIN SELECT d FROM b WHERE c = 10 AND d = 10 OR c IS NULL AND d > 0 AND d < 2 1444 ---- 1445 · distributed false 1446 · vectorized true 1447 render · · 1448 └── scan · · 1449 · table b@b_c_d_key 1450 · spans /NULL/1-/NULL/2 /10/10-/10/11 1451 1452 statement ok 1453 DROP INDEX b_b_idx 1454 1455 statement ok 1456 CREATE UNIQUE INDEX on b(b) STORING (c) 1457 1458 # This one is parallelizable because its index is unique and non-null. 1459 query TTT 1460 EXPLAIN SELECT b FROM b WHERE b = 10 OR b = 20 1461 ---- 1462 · distributed false 1463 · vectorized true 1464 scan · · 1465 · table b@b_b_key 1466 · spans /10-/11 /20-/21 1467 · parallel · 1468 1469 statement ok 1470 ALTER TABLE a SPLIT AT VALUES(5) 1471 1472 # Run a select to prime the range cache to simplify the trace below. 1473 statement ok 1474 SELECT * FROM a 1475 1476 # Make sure that the scan actually gets parallelized. 1477 statement ok 1478 SET tracing = on; SELECT * FROM a WHERE a = 0 OR a = 10; SET tracing = off 1479 1480 # The span "sending partial batch" means that the scan was parallelized. 1481 # If this test is failing and doesn't have that span, it means that the scanNode 1482 # was improperly configured to add a limit to the ScanRequest batch. 1483 # See #30943 for more details. 1484 query T 1485 SELECT message FROM [SHOW TRACE FOR SESSION] 1486 WHERE message IN 1487 ('querying next range at /Table/73/1/0', 1488 'querying next range at /Table/73/1/10', 1489 '=== SPAN START: kv.DistSender: sending partial batch ===' 1490 ) 1491 ---- 1492 querying next range at /Table/73/1/0 1493 === SPAN START: kv.DistSender: sending partial batch === 1494 querying next range at /Table/73/1/10 1495 1496 # Test for 42202 -- ensure filters can get pushed down through project-set. 1497 statement ok 1498 CREATE TABLE e (x INT PRIMARY KEY, y INT, z STRING); 1499 CREATE TABLE s (x INT PRIMARY KEY, y INT, z INT) 1500 1501 query TTT 1502 EXPLAIN SELECT e.z, s.z, n FROM e, s, generate_series(0, s.z, 1000) as n WHERE e.y = s.y ORDER BY s.z LIMIT 10 1503 ---- 1504 · distributed false 1505 · vectorized true 1506 render · · 1507 └── limit · · 1508 │ count 10 1509 └── sort · · 1510 │ order +z 1511 └── project set · · 1512 └── hash-join · · 1513 │ type inner 1514 │ equality (y) = (y) 1515 ├── scan · · 1516 │ table e@primary 1517 │ spans FULL SCAN 1518 └── scan · · 1519 · table s@primary 1520 · spans FULL SCAN