github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/exec/execbuilder/testdata/orderby (about) 1 # LogicTest: local 2 3 statement ok 4 CREATE TABLE t ( 5 a INT PRIMARY KEY, 6 b INT, 7 c BOOLEAN, 8 FAMILY "primary" (a, b, c) 9 ) 10 11 query TTT 12 EXPLAIN SELECT a, b FROM t ORDER BY b 13 ---- 14 · distributed false 15 · vectorized true 16 sort · · 17 │ order +b 18 └── scan · · 19 · table t@primary 20 · spans FULL SCAN 21 22 query TTT 23 EXPLAIN SELECT a, b FROM t ORDER BY b DESC 24 ---- 25 · distributed false 26 · vectorized true 27 sort · · 28 │ order -b 29 └── scan · · 30 · table t@primary 31 · spans FULL SCAN 32 33 # TODO(radu): Should set "strategy top 2" on sort node 34 query TTT 35 EXPLAIN SELECT a, b FROM t ORDER BY b LIMIT 2 36 ---- 37 · distributed false 38 · vectorized true 39 limit · · 40 │ count 2 41 └── sort · · 42 │ order +b 43 └── scan · · 44 · table t@primary 45 · spans FULL SCAN 46 47 query TTTTT 48 EXPLAIN (VERBOSE) SELECT DISTINCT c, b FROM t ORDER BY b LIMIT 2 49 ---- 50 · distributed false · · 51 · vectorized true · · 52 render · · (c, b) · 53 │ render 0 c · · 54 │ render 1 b · · 55 └── limit · · (b, c) +b 56 │ count 2 · · 57 └── sort · · (b, c) +b 58 │ order +b · · 59 └── distinct · · (b, c) · 60 │ distinct on b, c · · 61 └── scan · · (b, c) · 62 · table t@primary · · 63 · spans FULL SCAN · · 64 65 query TTT 66 EXPLAIN SELECT b FROM t ORDER BY a DESC 67 ---- 68 · distributed false 69 · vectorized true 70 render · · 71 └── revscan · · 72 · table t@primary 73 · spans FULL SCAN 74 75 # Check that LIMIT propagates past nosort nodes. 76 query TTT 77 EXPLAIN SELECT b FROM t ORDER BY a LIMIT 1 78 ---- 79 · distributed false 80 · vectorized true 81 render · · 82 └── scan · · 83 · table t@primary 84 · spans LIMITED SCAN 85 · limit 1 86 87 query TTT 88 EXPLAIN SELECT b FROM t ORDER BY a DESC, b ASC 89 ---- 90 · distributed false 91 · vectorized true 92 render · · 93 └── revscan · · 94 · table t@primary 95 · spans FULL SCAN 96 97 query TTT 98 EXPLAIN SELECT b FROM t ORDER BY a DESC, b DESC 99 ---- 100 · distributed false 101 · vectorized true 102 render · · 103 └── revscan · · 104 · table t@primary 105 · spans FULL SCAN 106 107 query TTTTT 108 EXPLAIN (VERBOSE) SELECT * FROM t ORDER BY (b, t.*) 109 ---- 110 · distributed false · · 111 · vectorized true · · 112 sort · · (a, b, c) +b,+a 113 │ order +b,+a · · 114 └── scan · · (a, b, c) · 115 · table t@primary · · 116 · spans FULL SCAN · · 117 118 query TTTTT 119 EXPLAIN (VERBOSE) SELECT * FROM t ORDER BY (b, a), c 120 ---- 121 · distributed false · · 122 · vectorized true · · 123 sort · · (a, b, c) +b,+a 124 │ order +b,+a · · 125 └── scan · · (a, b, c) · 126 · table t@primary · · 127 · spans FULL SCAN · · 128 129 query TTTTT 130 EXPLAIN (VERBOSE) SELECT * FROM t ORDER BY b, (a, c) 131 ---- 132 · distributed false · · 133 · vectorized true · · 134 sort · · (a, b, c) +b,+a 135 │ order +b,+a · · 136 └── scan · · (a, b, c) · 137 · table t@primary · · 138 · spans FULL SCAN · · 139 140 query TTTTT 141 EXPLAIN (VERBOSE) SELECT * FROM t ORDER BY (b, (a, c)) 142 ---- 143 · distributed false · · 144 · vectorized true · · 145 sort · · (a, b, c) +b,+a 146 │ order +b,+a · · 147 └── scan · · (a, b, c) · 148 · table t@primary · · 149 · spans FULL SCAN · · 150 151 # Check that sort is skipped if the ORDER BY clause is constant. 152 query TTT 153 EXPLAIN SELECT * FROM t ORDER BY 1+2 154 ---- 155 · distributed false 156 · vectorized true 157 scan · · 158 · table t@primary 159 · spans FULL SCAN 160 161 query TTT 162 EXPLAIN SELECT 1, * FROM t ORDER BY 1 163 ---- 164 · distributed false 165 · vectorized true 166 render · · 167 └── scan · · 168 · table t@primary 169 · spans FULL SCAN 170 171 query TTT 172 EXPLAIN SELECT * FROM t ORDER BY length('abc') 173 ---- 174 · distributed false 175 · vectorized true 176 scan · · 177 · table t@primary 178 · spans FULL SCAN 179 180 # Check that the sort key reuses the existing render. 181 query TTTTT 182 EXPLAIN (VERBOSE) SELECT b+2 r FROM t ORDER BY b+2 183 ---- 184 · distributed false · · 185 · vectorized true · · 186 sort · · (r) +r 187 │ order +r · · 188 └── render · · (r) · 189 │ render 0 b + 2 · · 190 └── scan · · (b) · 191 · table t@primary · · 192 · spans FULL SCAN · · 193 194 # Check that the sort picks up a renamed render properly. 195 query TTTTT 196 EXPLAIN (VERBOSE) SELECT b+2 AS y FROM t ORDER BY y 197 ---- 198 · distributed false · · 199 · vectorized true · · 200 sort · · (y) +y 201 │ order +y · · 202 └── render · · (y) · 203 │ render 0 b + 2 · · 204 └── scan · · (b) · 205 · table t@primary · · 206 · spans FULL SCAN · · 207 208 statement ok 209 CREATE TABLE abc ( 210 a INT, 211 b INT, 212 c INT, 213 d VARCHAR, 214 PRIMARY KEY (a, b, c), 215 UNIQUE INDEX bc (b, c), 216 INDEX ba (b, a), 217 FAMILY (a, b, c), 218 FAMILY (d) 219 ) 220 221 statement ok 222 INSERT INTO abc VALUES (1, 2, 3, 'one'), (4, 5, 6, 'Two') 223 224 statement ok 225 SET tracing = on,kv,results; SELECT * FROM abc ORDER BY a; SET tracing = off 226 227 query T 228 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 229 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 230 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 231 ---- 232 fetched: /abc/primary/1/2/3 -> NULL 233 fetched: /abc/primary/1/2/3/d -> 'one' 234 fetched: /abc/primary/4/5/6 -> NULL 235 fetched: /abc/primary/4/5/6/d -> 'Two' 236 output row: [1 2 3 'one'] 237 output row: [4 5 6 'Two'] 238 239 statement ok 240 SET tracing = on,kv,results; SELECT a, b FROM abc ORDER BY b, a; SET tracing = off 241 242 query T 243 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 244 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 245 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 246 ---- 247 fetched: /abc/ba/2/1/3 -> NULL 248 fetched: /abc/ba/5/4/6 -> NULL 249 output row: [1 2] 250 output row: [4 5] 251 252 # The non-unique index ba includes column c (required to make the keys unique) 253 # so the results will already be sorted. 254 query TTT 255 EXPLAIN SELECT a, b, c FROM abc ORDER BY b, a, c 256 ---- 257 · distributed false 258 · vectorized true 259 scan · · 260 · table abc@ba 261 · spans FULL SCAN 262 263 # We use the WHERE condition to force the use of the index. 264 query TTT 265 EXPLAIN SELECT a, b, c FROM abc WHERE b > 10 AND b < 30 ORDER BY b, a, d 266 ---- 267 · distributed false 268 · vectorized true 269 render · · 270 └── sort · · 271 │ order +b,+a,+d 272 │ already ordered +b,+a 273 └── index-join · · 274 │ table abc@primary 275 │ key columns a, b, c 276 └── scan · · 277 · table abc@ba 278 · spans /11-/30 279 280 # An inequality should not be enough to force the use of the index. 281 query TTT 282 EXPLAIN SELECT a, b, c FROM abc WHERE b > 10 ORDER BY b, a, d 283 ---- 284 · distributed false 285 · vectorized true 286 render · · 287 └── sort · · 288 │ order +b,+a,+d 289 └── scan · · 290 · table abc@primary 291 · spans FULL SCAN 292 · filter b > 10 293 294 query III 295 SELECT a, b, c FROM abc WHERE b > 4 ORDER BY b, a, d 296 ---- 297 4 5 6 298 299 query III 300 SELECT a, b, c FROM abc WHERE b > 4 ORDER BY b, a, d 301 ---- 302 4 5 6 303 304 # We cannot have rows with identical values for a,b,c so we don't need to 305 # sort for d. 306 query TTTTT 307 EXPLAIN (VERBOSE) SELECT a, b, c, d FROM abc WHERE b > 10 ORDER BY b, a, c, d 308 ---- 309 · distributed false · · 310 · vectorized true · · 311 sort · · (a, b, c, d) +b,+a,+c 312 │ order +b,+a,+c · · 313 └── scan · · (a, b, c, d) · 314 · table abc@primary · · 315 · spans FULL SCAN · · 316 · filter b > 10 · · 317 318 query TTT 319 EXPLAIN SELECT a, b FROM abc ORDER BY b, c 320 ---- 321 · distributed false 322 · vectorized true 323 render · · 324 └── scan · · 325 · table abc@bc 326 · spans FULL SCAN 327 328 query TTTTT 329 EXPLAIN (VERBOSE) SELECT a, b FROM abc ORDER BY b, c 330 ---- 331 · distributed false · · 332 · vectorized true · · 333 render · · (a, b) · 334 │ render 0 a · · 335 │ render 1 b · · 336 └── scan · · (a, b, c) +b,+c 337 · table abc@bc · · 338 · spans FULL SCAN · · 339 340 query TTT 341 EXPLAIN SELECT a, b FROM abc ORDER BY b, c, a 342 ---- 343 · distributed false 344 · vectorized true 345 render · · 346 └── scan · · 347 · table abc@bc 348 · spans FULL SCAN 349 350 query TTT 351 EXPLAIN SELECT a, b FROM abc ORDER BY b, c, a DESC 352 ---- 353 · distributed false 354 · vectorized true 355 render · · 356 └── scan · · 357 · table abc@bc 358 · spans FULL SCAN 359 360 statement ok 361 SET tracing = on,kv,results; SELECT b, c FROM abc ORDER BY b, c; SET tracing = off 362 363 query T 364 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 365 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 366 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 367 ---- 368 fetched: /abc/bc/2/3 -> /1 369 fetched: /abc/bc/5/6 -> /4 370 output row: [2 3] 371 output row: [5 6] 372 373 statement ok 374 SET tracing = on,kv,results; SELECT a, b, c FROM abc ORDER BY b; SET tracing = off 375 376 query T 377 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 378 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 379 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 380 ---- 381 fetched: /abc/bc/2/3 -> /1 382 fetched: /abc/bc/5/6 -> /4 383 output row: [1 2 3] 384 output row: [4 5 6] 385 386 statement ok 387 SET tracing = on,kv,results; SELECT a FROM abc ORDER BY a DESC; SET tracing = off 388 389 query T 390 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 391 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 392 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 393 ---- 394 fetched: /abc/primary/4/5/6/d -> 'Two' 395 fetched: /abc/primary/4/5/6 -> NULL 396 fetched: /abc/primary/1/2/3/d -> 'one' 397 fetched: /abc/primary/1/2/3 -> NULL 398 output row: [4] 399 output row: [1] 400 401 query TTT 402 EXPLAIN SELECT a FROM abc ORDER BY a DESC 403 ---- 404 · distributed false 405 · vectorized true 406 revscan · · 407 · table abc@primary 408 · spans FULL SCAN 409 410 query TTT 411 EXPLAIN SELECT c FROM abc WHERE b = 2 ORDER BY c 412 ---- 413 · distributed false 414 · vectorized true 415 render · · 416 └── scan · · 417 · table abc@bc 418 · spans /2-/3 419 420 query TTT 421 EXPLAIN SELECT c FROM abc WHERE b = 2 ORDER BY c DESC 422 ---- 423 · distributed false 424 · vectorized true 425 render · · 426 └── revscan · · 427 · table abc@bc 428 · spans /2-/3 429 430 # Verify that the ordering of the primary index is still used for the outer sort. 431 query TTTTT 432 EXPLAIN (VERBOSE) SELECT * FROM (SELECT b, c FROM abc WHERE a=1 ORDER BY a,b) ORDER BY b,c 433 ---- 434 · distributed false · · 435 · vectorized true · · 436 render · · (b, c) +b,+c 437 │ render 0 b · · 438 │ render 1 c · · 439 └── scan · · (a, b, c) +b,+c 440 · table abc@primary · · 441 · spans /1-/2 · · 442 443 statement ok 444 CREATE TABLE bar (id INT PRIMARY KEY, baz STRING, UNIQUE INDEX i_bar (baz)) 445 446 query TTTTT 447 EXPLAIN (VERBOSE) SELECT * FROM bar ORDER BY baz, id 448 ---- 449 · distributed false · · 450 · vectorized true · · 451 scan · · (id, baz) +baz,+id 452 · table bar@i_bar · · 453 · spans FULL SCAN · · 454 455 statement ok 456 CREATE TABLE abcd ( 457 a INT PRIMARY KEY, 458 b INT, 459 c INT, 460 d INT, 461 INDEX abc (a, b, c) 462 ) 463 464 # Verify that render expressions after sorts perform correctly. We need the 465 # rowsort as we're attempting to force a RENDER expression after the first 466 # ORDER BY, to ensure it renders correctly, but the outer query doesn't 467 # guarantee that it will preserve the order. 468 469 # The following tests verify we recognize that sorting is not necessary 470 query TTT 471 EXPLAIN SELECT a, b, c FROM abcd@abc WHERE (a, b) = (1, 4) ORDER BY c 472 ---- 473 · distributed false 474 · vectorized true 475 scan · · 476 · table abcd@abc 477 · spans /1/4-/1/5 478 479 query TTT 480 EXPLAIN SELECT a, b, c FROM abcd@abc WHERE (a, b) = (1, 4) ORDER BY c, b, a 481 ---- 482 · distributed false 483 · vectorized true 484 scan · · 485 · table abcd@abc 486 · spans /1/4-/1/5 487 488 query TTT 489 EXPLAIN SELECT a, b, c FROM abcd@abc WHERE (a, b) = (1, 4) ORDER BY b, a, c 490 ---- 491 · distributed false 492 · vectorized true 493 scan · · 494 · table abcd@abc 495 · spans /1/4-/1/5 496 497 query TTT 498 EXPLAIN SELECT a, b, c FROM abcd@abc WHERE (a, b) = (1, 4) ORDER BY b, c, a 499 ---- 500 · distributed false 501 · vectorized true 502 scan · · 503 · table abcd@abc 504 · spans /1/4-/1/5 505 506 statement ok 507 CREATE TABLE nan (id INT PRIMARY KEY, x REAL) 508 509 query TTTTT 510 EXPLAIN (VERBOSE) SELECT * FROM (SELECT * FROM (VALUES ('a'), ('b'), ('c')) AS c(x) ORDER BY x) 511 ---- 512 · distributed false · · 513 · vectorized false · · 514 values · · (x) · 515 · size 1 column, 3 rows · · 516 · row 0, expr 0 'a' · · 517 · row 1, expr 0 'b' · · 518 · row 2, expr 0 'c' · · 519 520 query TTT 521 EXPLAIN SELECT * FROM (VALUES ('a'), ('b'), ('c')) WITH ORDINALITY ORDER BY ordinality ASC 522 ---- 523 · distributed false 524 · vectorized false 525 ordinality · · 526 └── values · · 527 · size 1 column, 3 rows 528 529 query TTT 530 EXPLAIN SELECT * FROM (VALUES ('a'), ('b'), ('c')) WITH ORDINALITY ORDER BY ordinality DESC 531 ---- 532 · distributed false 533 · vectorized false 534 sort · · 535 │ order -"ordinality" 536 └── ordinality · · 537 └── values · · 538 · size 1 column, 3 rows 539 540 query TTTTT 541 EXPLAIN (VERBOSE) SELECT * FROM (SELECT * FROM (VALUES ('a'), ('b'), ('c')) AS c(x)) WITH ORDINALITY 542 ---- 543 · distributed false · · 544 · vectorized false · · 545 ordinality · · (x, "ordinality") · 546 └── values · · (column1) · 547 · size 1 column, 3 rows · · 548 · row 0, expr 0 'a' · · 549 · row 1, expr 0 'b' · · 550 · row 2, expr 0 'c' · · 551 552 query TTTTT 553 EXPLAIN (VERBOSE) SELECT * FROM (SELECT * FROM (VALUES ('a'), ('b'), ('c')) AS c(x) ORDER BY x) WITH ORDINALITY 554 ---- 555 · distributed false · · 556 · vectorized false · · 557 ordinality · · (x, "ordinality") · 558 └── sort · · (column1) +column1 559 │ order +column1 · · 560 └── values · · (column1) · 561 · size 1 column, 3 rows · · 562 · row 0, expr 0 'a' · · 563 · row 1, expr 0 'b' · · 564 · row 2, expr 0 'c' · · 565 566 # Check that the ordering of the source does not propagate blindly to RETURNING. 567 query TTTTT 568 EXPLAIN (VERBOSE) INSERT INTO t(a, b) SELECT * FROM (SELECT 1 AS x, 2 AS y) ORDER BY x RETURNING b 569 ---- 570 · distributed false · · 571 · vectorized false · · 572 render · · (b) · 573 │ render 0 b · · 574 └── run · · (a, b) · 575 └── insert-fast-path · · (a, b) · 576 · into t(a, b, c) · · 577 · strategy inserter · · 578 · auto commit · · · 579 · size 3 columns, 1 row · · 580 · row 0, expr 0 1 · · 581 · row 0, expr 1 2 · · 582 · row 0, expr 2 CAST(NULL AS BOOL) · · 583 584 query TTTTT 585 EXPLAIN (VERBOSE) DELETE FROM t WHERE a = 3 RETURNING b 586 ---- 587 · distributed false · · 588 · vectorized false · · 589 render · · (b) · 590 │ render 0 b · · 591 └── run · · (a, b) · 592 └── delete · · (a, b) · 593 │ from t · · 594 │ strategy deleter · · 595 │ auto commit · · · 596 └── scan · · (a, b) · 597 · table t@primary · · 598 · spans /3-/3/# · · 599 600 query TTTTT 601 EXPLAIN (VERBOSE) UPDATE t SET c = TRUE RETURNING b 602 ---- 603 · distributed false · · 604 · vectorized false · · 605 render · · (b) · 606 │ render 0 b · · 607 └── run · · (a, b) · 608 └── update · · (a, b) · 609 │ table t · · 610 │ set c · · 611 │ strategy updater · · 612 │ auto commit · · · 613 └── render · · (a, b, c, c_new) · 614 │ render 0 a · · 615 │ render 1 b · · 616 │ render 2 c · · 617 │ render 3 true · · 618 └── scan · · (a, b, c) · 619 · table t@primary · · 620 · spans FULL SCAN · · 621 · locking strength for update · · 622 623 statement ok 624 CREATE TABLE uvwxyz ( 625 u INT, 626 v INT, 627 w INT, 628 x INT, 629 y INT, 630 z INT, 631 INDEX ywxz (y, w, x, z, u, v), 632 INDEX ywz (y, w, z, x) 633 ) 634 635 # Verify that the outer ordering is propagated to index selection and we choose 636 # the index that avoids any sorting. 637 query TTTTT 638 EXPLAIN (VERBOSE) SELECT * FROM (SELECT y, w, x FROM uvwxyz WHERE y = 1 ORDER BY w) ORDER BY w, x 639 ---- 640 · distributed false · · 641 · vectorized true · · 642 render · · (y, w, x) · 643 │ render 0 y · · 644 │ render 1 w · · 645 │ render 2 x · · 646 └── scan · · (w, x, y) +w,+x 647 · table uvwxyz@ywxz · · 648 · spans /1-/2 · · 649 650 651 statement ok 652 CREATE TABLE blocks ( 653 block_id INT, 654 writer_id STRING, 655 block_num INT, 656 raw_bytes BYTES, 657 PRIMARY KEY (block_id, writer_id, block_num) 658 ) 659 660 # Test that ordering goes "through" a renderNode that has a duplicate render of 661 # an order-by column (#13696). 662 # Note that if we have a hard limit of 1, the scanNode won't necessarily have an 663 # ordering; if we ever plan multiple tablereaders in this case, we must make 664 # sure to set the merge ordering below to the natural order of the index we are 665 # scanning. 666 query TTTTT 667 EXPLAIN (VERBOSE) SELECT block_id,writer_id,block_num,block_id FROM blocks ORDER BY block_id, writer_id, block_num LIMIT 1 668 ---- 669 · distributed false · · 670 · vectorized true · · 671 render · · (block_id, writer_id, block_num, block_id) · 672 │ render 0 block_id · · 673 │ render 1 writer_id · · 674 │ render 2 block_num · · 675 │ render 3 block_id · · 676 └── scan · · (block_id, writer_id, block_num) · 677 · table blocks@primary · · 678 · spans LIMITED SCAN · · 679 · limit 1 · · 680 681 statement ok 682 CREATE TABLE foo(a INT, b CHAR) 683 684 # Check that sort by ordinal picks up the existing render. 685 query TTTTT 686 EXPLAIN (VERBOSE) SELECT b, a FROM foo ORDER BY @1 687 ---- 688 · distributed false · · 689 · vectorized true · · 690 render · · (b, a) · 691 │ render 0 b · · 692 │ render 1 a · · 693 └── sort · · (column4, a, b) +a 694 │ order +a · · 695 └── render · · (column4, a, b) · 696 │ render 0 a · · 697 │ render 1 a · · 698 │ render 2 b · · 699 └── scan · · (a, b) · 700 · table foo@primary · · 701 · spans FULL SCAN · · 702 703 query TTTTT 704 EXPLAIN (VERBOSE) SELECT b, a FROM foo ORDER BY @2 705 ---- 706 · distributed false · · 707 · vectorized true · · 708 render · · (b, a) · 709 │ render 0 b · · 710 │ render 1 a · · 711 └── sort · · (column4, a, b) +b 712 │ order +b · · 713 └── render · · (column4, a, b) · 714 │ render 0 b · · 715 │ render 1 a · · 716 │ render 2 b · · 717 └── scan · · (a, b) · 718 · table foo@primary · · 719 · spans FULL SCAN · · 720 721 # ------------------------------------------------------------------------------ 722 # Check star expansion in ORDER BY. 723 # ------------------------------------------------------------------------------ 724 statement ok 725 CREATE TABLE a(x, y) AS VALUES (1, 1), (2, 2) 726 727 query TTT 728 SELECT tree, field, description FROM [ 729 EXPLAIN (VERBOSE) SELECT * FROM a ORDER BY a.* 730 ] 731 ---- 732 · distributed false 733 · vectorized true 734 sort · · 735 │ order +x,+y 736 └── scan · · 737 · table a@primary 738 · spans FULL SCAN 739 740 query TTT 741 SELECT tree, field, description FROM [ 742 EXPLAIN (VERBOSE) SELECT * FROM a ORDER BY (a.*) 743 ] 744 ---- 745 · distributed false 746 · vectorized true 747 sort · · 748 │ order +x,+y 749 └── scan · · 750 · table a@primary 751 · spans FULL SCAN 752 753 # ------------------------------------------------------------------------------ 754 # ORDER BY INDEX test cases. 755 # ------------------------------------------------------------------------------ 756 # subtest order_by_index 757 758 statement ok 759 CREATE TABLE kv(k INT PRIMARY KEY, v INT); CREATE INDEX foo ON kv(v DESC) 760 761 query TTTTT 762 EXPLAIN (VERBOSE) SELECT v FROM kv ORDER BY PRIMARY KEY kv 763 ---- 764 · distributed false · · 765 · vectorized true · · 766 render · · (v) · 767 │ render 0 v · · 768 └── scan · · (k, v) +k 769 · table kv@primary · · 770 · spans FULL SCAN · · 771 772 query TTTTT 773 EXPLAIN (VERBOSE) SELECT v FROM kv ORDER BY PRIMARY KEY kv ASC 774 ---- 775 · distributed false · · 776 · vectorized true · · 777 render · · (v) · 778 │ render 0 v · · 779 └── scan · · (k, v) +k 780 · table kv@primary · · 781 · spans FULL SCAN · · 782 783 query TTTTT 784 EXPLAIN (VERBOSE) SELECT v FROM kv ORDER BY PRIMARY KEY kv DESC 785 ---- 786 · distributed false · · 787 · vectorized true · · 788 render · · (v) · 789 │ render 0 v · · 790 └── revscan · · (k, v) -k 791 · table kv@primary · · 792 · spans FULL SCAN · · 793 794 query TTTTT 795 EXPLAIN (VERBOSE) SELECT k FROM kv ORDER BY v, PRIMARY KEY kv, v-2 796 ---- 797 · distributed false · · 798 · vectorized true · · 799 render · · (k) · 800 │ render 0 k · · 801 └── sort · · (k, v) +v,+k 802 │ order +v,+k · · 803 └── scan · · (k, v) · 804 · table kv@primary · · 805 · spans FULL SCAN · · 806 807 query TTTTT 808 EXPLAIN (VERBOSE) SELECT k FROM kv ORDER BY INDEX kv@foo 809 ---- 810 · distributed false · · 811 · vectorized true · · 812 render · · (k) · 813 │ render 0 k · · 814 └── scan · · (k, v) -v,+k 815 · table kv@foo · · 816 · spans FULL SCAN · · 817 818 query TTTTT 819 EXPLAIN (VERBOSE) SELECT k FROM kv ORDER BY INDEX kv@foo ASC 820 ---- 821 · distributed false · · 822 · vectorized true · · 823 render · · (k) · 824 │ render 0 k · · 825 └── scan · · (k, v) -v,+k 826 · table kv@foo · · 827 · spans FULL SCAN · · 828 829 query TTTTT 830 EXPLAIN (VERBOSE) SELECT k FROM kv ORDER BY INDEX kv@foo DESC 831 ---- 832 · distributed false · · 833 · vectorized true · · 834 render · · (k) · 835 │ render 0 k · · 836 └── revscan · · (k, v) +v,-k 837 · table kv@foo · · 838 · spans FULL SCAN · · 839 840 query TTTTT 841 EXPLAIN (VERBOSE) SELECT k FROM kv ORDER BY INDEX kv@foo, k 842 ---- 843 · distributed false · · 844 · vectorized true · · 845 render · · (k) · 846 │ render 0 k · · 847 └── scan · · (k, v) -v,+k 848 · table kv@foo · · 849 · spans FULL SCAN · · 850 851 # Check the syntax can be used with joins. 852 # 853 # Note: an ORDER BY INDEX clause on the result of the join 854 # does not imply use of that index by the underlying scan. 855 # 856 857 query TTTTT 858 EXPLAIN (VERBOSE) 859 SELECT k FROM kv JOIN (VALUES (1,2), (3,4)) AS z(a,b) ON kv.k = z.a ORDER BY INDEX kv@foo 860 ---- 861 · distributed false · · 862 · vectorized false · · 863 render · · (k) · 864 │ render 0 k · · 865 └── sort · · (k, v) -v,+k 866 │ order -v,+k · · 867 └── render · · (k, v) · 868 │ render 0 k · · 869 │ render 1 v · · 870 └── lookup-join · · (column1, k, v) · 871 │ table kv@primary · · 872 │ type inner · · 873 │ equality (column1) = (k) · · 874 │ equality cols are key · · · 875 │ parallel · · · 876 └── values · · (column1) · 877 · size 1 column, 2 rows · · 878 · row 0, expr 0 1 · · 879 · row 1, expr 0 3 · · 880 881 query TTTTT 882 EXPLAIN (VERBOSE) SELECT k FROM kv a NATURAL JOIN kv ORDER BY INDEX kv@foo 883 ---- 884 · distributed false · · 885 · vectorized true · · 886 render · · (k) · 887 │ render 0 k · · 888 └── merge-join · · (k, v, k, v) -v,+k 889 │ type inner · · 890 │ equality (v, k) = (v, k) · · 891 │ left cols are key · · · 892 │ right cols are key · · · 893 │ mergeJoinOrder -"(v=v)",+"(k=k)" · · 894 ├── scan · · (k, v) -v,+k 895 │ table kv@foo · · 896 │ spans FULL SCAN · · 897 └── scan · · (k, v) -v,+k 898 · table kv@foo · · 899 · spans FULL SCAN · · 900 901 statement ok 902 CREATE TABLE xyz (x INT, y INT, z INT, INDEX(z,y)) 903 904 # Verify that we set up the ordering of the inner scan correctly (see #27347). 905 query TTTTT 906 EXPLAIN (VERBOSE) SELECT * FROM xyz WHERE z=1 AND x=y ORDER BY x; 907 ---- 908 · distributed false · · 909 · vectorized true · · 910 sort · · (x, y, z) +x 911 │ order +x · · 912 └── filter · · (x, y, z) · 913 │ filter x = y · · 914 └── index-join · · (x, y, z) · 915 │ table xyz@primary · · 916 │ key columns rowid · · 917 └── scan · · (y, z, rowid[hidden]) · 918 · table xyz@xyz_z_y_idx · · 919 · spans /1/!NULL-/2 · ·