github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/optbuilder/testdata/orderby (about) 1 # tests adapted from logictest -- order_by 2 3 exec-ddl 4 CREATE TABLE t ( 5 a INT PRIMARY KEY, 6 b INT, 7 c BOOLEAN 8 ) 9 ---- 10 11 build 12 SELECT c FROM t ORDER BY c 13 ---- 14 sort 15 ├── columns: c:3 16 ├── ordering: +3 17 └── project 18 ├── columns: c:3 19 └── scan t 20 └── columns: a:1!null b:2 c:3 21 22 build 23 SELECT c FROM t ORDER BY c DESC 24 ---- 25 sort 26 ├── columns: c:3 27 ├── ordering: -3 28 └── project 29 ├── columns: c:3 30 └── scan t 31 └── columns: a:1!null b:2 c:3 32 33 build 34 SELECT a, b FROM t ORDER BY b 35 ---- 36 sort 37 ├── columns: a:1!null b:2 38 ├── ordering: +2 39 └── project 40 ├── columns: a:1!null b:2 41 └── scan t 42 └── columns: a:1!null b:2 c:3 43 44 build 45 SELECT a, b FROM t ORDER BY b DESC 46 ---- 47 sort 48 ├── columns: a:1!null b:2 49 ├── ordering: -2 50 └── project 51 ├── columns: a:1!null b:2 52 └── scan t 53 └── columns: a:1!null b:2 c:3 54 55 build 56 SELECT a, b FROM t ORDER BY b DESC LIMIT 2 57 ---- 58 limit 59 ├── columns: a:1!null b:2 60 ├── internal-ordering: -2 61 ├── ordering: -2 62 ├── sort 63 │ ├── columns: a:1!null b:2 64 │ ├── ordering: -2 65 │ ├── limit hint: 2.00 66 │ └── project 67 │ ├── columns: a:1!null b:2 68 │ └── scan t 69 │ └── columns: a:1!null b:2 c:3 70 └── 2 71 72 build 73 SELECT a FROM t ORDER BY 1 DESC 74 ---- 75 project 76 ├── columns: a:1!null 77 ├── ordering: -1 78 └── scan t,rev 79 ├── columns: a:1!null b:2 c:3 80 └── ordering: -1 81 82 # This query causes an error in Postgres, and the optimizer has followed 83 # that lead. However, it is supported by the heuristic planner in CockroachDB 84 # with the semantics: 85 # SELECT c FROM t GROUP BY c ORDER BY max(b) DESC; 86 build 87 SELECT DISTINCT c FROM t ORDER BY b DESC 88 ---- 89 error (42P10): for SELECT DISTINCT, ORDER BY expressions must appear in select list 90 91 build 92 SELECT a AS foo, b FROM t ORDER BY foo DESC 93 ---- 94 project 95 ├── columns: foo:1!null b:2 96 ├── ordering: -1 97 └── scan t,rev 98 ├── columns: a:1!null b:2 c:3 99 └── ordering: -1 100 101 # Check that ambiguous references to renders are properly reported. 102 build 103 SELECT a AS foo, b AS foo FROM t ORDER BY foo 104 ---- 105 error (42P09): ORDER BY "foo" is ambiguous 106 107 # Check that no ambiguity is reported if the ORDER BY name refers 108 # to two or more equivalent renders (special case in SQL92). 109 build 110 SELECT a AS foo, (a) AS foo FROM t ORDER BY foo LIMIT 1 111 ---- 112 limit 113 ├── columns: foo:1!null foo:1!null 114 ├── internal-ordering: +1 115 ├── ordering: +1 116 ├── project 117 │ ├── columns: a:1!null 118 │ ├── ordering: +1 119 │ ├── limit hint: 1.00 120 │ └── scan t 121 │ ├── columns: a:1!null b:2 c:3 122 │ ├── ordering: +1 123 │ └── limit hint: 1.00 124 └── 1 125 126 # Check that this orders by the aliased column b (i.e., column a), not the 127 # original column b. 128 build 129 SELECT a AS b, b AS c FROM t ORDER BY b 130 ---- 131 project 132 ├── columns: b:1!null c:2 133 ├── ordering: +1 134 └── scan t 135 ├── columns: a:1!null b:2 c:3 136 └── ordering: +1 137 138 build 139 SELECT a AS "foo.bar", b FROM t ORDER BY "foo.bar" DESC 140 ---- 141 project 142 ├── columns: foo.bar:1!null b:2 143 ├── ordering: -1 144 └── scan t,rev 145 ├── columns: a:1!null b:2 c:3 146 └── ordering: -1 147 148 build 149 SELECT a AS foo, b FROM t ORDER BY a DESC 150 ---- 151 project 152 ├── columns: foo:1!null b:2 153 ├── ordering: -1 154 └── scan t,rev 155 ├── columns: a:1!null b:2 c:3 156 └── ordering: -1 157 158 build 159 SELECT b FROM t ORDER BY a DESC 160 ---- 161 project 162 ├── columns: b:2 [hidden: a:1!null] 163 ├── ordering: -1 164 └── scan t,rev 165 ├── columns: a:1!null b:2 c:3 166 └── ordering: -1 167 168 build 169 SELECT b FROM t ORDER BY a LIMIT 1 170 ---- 171 limit 172 ├── columns: b:2 [hidden: a:1!null] 173 ├── internal-ordering: +1 174 ├── ordering: +1 175 ├── project 176 │ ├── columns: a:1!null b:2 177 │ ├── ordering: +1 178 │ ├── limit hint: 1.00 179 │ └── scan t 180 │ ├── columns: a:1!null b:2 c:3 181 │ ├── ordering: +1 182 │ └── limit hint: 1.00 183 └── 1 184 185 build 186 SELECT b FROM t ORDER BY a DESC, b ASC 187 ---- 188 project 189 ├── columns: b:2 [hidden: a:1!null] 190 ├── ordering: -1,+2 191 └── scan t,rev 192 ├── columns: a:1!null b:2 c:3 193 └── ordering: -1 194 195 build 196 SELECT b FROM t ORDER BY a DESC, b DESC 197 ---- 198 project 199 ├── columns: b:2 [hidden: a:1!null] 200 ├── ordering: -1,-2 201 └── scan t,rev 202 ├── columns: a:1!null b:2 c:3 203 └── ordering: -1 204 205 # both presentation and ordering 206 build 207 SELECT a, b, b FROM t ORDER BY c 208 ---- 209 sort 210 ├── columns: a:1!null b:2 b:2 [hidden: c:3] 211 ├── ordering: +3 212 └── scan t 213 └── columns: a:1!null b:2 c:3 214 215 build 216 SELECT * FROM t ORDER BY (b, t.*) 217 ---- 218 sort 219 ├── columns: a:1!null b:2 c:3 220 ├── ordering: +2,+1,+2,+3 221 └── scan t 222 └── columns: a:1!null b:2 c:3 223 224 build 225 SELECT * FROM t ORDER BY (b, a), c 226 ---- 227 sort 228 ├── columns: a:1!null b:2 c:3 229 ├── ordering: +2,+1,+3 230 └── scan t 231 └── columns: a:1!null b:2 c:3 232 233 build 234 SELECT * FROM t ORDER BY b, (a, c) 235 ---- 236 sort 237 ├── columns: a:1!null b:2 c:3 238 ├── ordering: +2,+1,+3 239 └── scan t 240 └── columns: a:1!null b:2 c:3 241 242 build 243 SELECT * FROM t ORDER BY (b, (a, c)) 244 ---- 245 sort 246 ├── columns: a:1!null b:2 c:3 247 ├── ordering: +2,+1,+3 248 └── scan t 249 └── columns: a:1!null b:2 c:3 250 251 build 252 SELECT a, b FROM t WHERE b = 7 ORDER BY b, a 253 ---- 254 project 255 ├── columns: a:1!null b:2!null 256 ├── ordering: +2,+1 257 └── select 258 ├── columns: a:1!null b:2!null c:3 259 ├── ordering: +1 opt(2) 260 ├── scan t 261 │ ├── columns: a:1!null b:2 c:3 262 │ └── ordering: +1 opt(2) 263 └── filters 264 └── b:2 = 7 265 266 build 267 SELECT a, b FROM t ORDER BY b, a DESC 268 ---- 269 sort 270 ├── columns: a:1!null b:2 271 ├── ordering: +2,-1 272 └── project 273 ├── columns: a:1!null b:2 274 └── scan t 275 └── columns: a:1!null b:2 c:3 276 277 build 278 SELECT a, b, a+b AS ab FROM t WHERE b = 7 ORDER BY ab DESC, a 279 ---- 280 sort 281 ├── columns: a:1!null b:2!null ab:4!null 282 ├── ordering: -4,+1 283 └── project 284 ├── columns: ab:4!null a:1!null b:2!null 285 ├── select 286 │ ├── columns: a:1!null b:2!null c:3 287 │ ├── scan t 288 │ │ └── columns: a:1!null b:2 c:3 289 │ └── filters 290 │ └── b:2 = 7 291 └── projections 292 └── a:1 + b:2 [as=ab:4] 293 294 build 295 SELECT a FROM t ORDER BY a+b DESC, a 296 ---- 297 sort 298 ├── columns: a:1!null [hidden: column4:4] 299 ├── ordering: -4,+1 300 └── project 301 ├── columns: column4:4 a:1!null 302 ├── scan t 303 │ └── columns: a:1!null b:2 c:3 304 └── projections 305 └── a:1 + b:2 [as=column4:4] 306 307 build 308 SELECT a FROM t ORDER BY (((a))) 309 ---- 310 project 311 ├── columns: a:1!null 312 ├── ordering: +1 313 └── scan t 314 ├── columns: a:1!null b:2 c:3 315 └── ordering: +1 316 317 build 318 (((SELECT a FROM t))) ORDER BY a DESC LIMIT 4 319 ---- 320 limit 321 ├── columns: a:1!null 322 ├── internal-ordering: -1 323 ├── ordering: -1 324 ├── project 325 │ ├── columns: a:1!null 326 │ ├── ordering: -1 327 │ ├── limit hint: 4.00 328 │ └── scan t,rev 329 │ ├── columns: a:1!null b:2 c:3 330 │ ├── ordering: -1 331 │ └── limit hint: 4.00 332 └── 4 333 334 build 335 (((SELECT a FROM t ORDER BY a DESC LIMIT 4))) 336 ---- 337 limit 338 ├── columns: a:1!null 339 ├── internal-ordering: -1 340 ├── ordering: -1 341 ├── project 342 │ ├── columns: a:1!null 343 │ ├── ordering: -1 344 │ ├── limit hint: 4.00 345 │ └── scan t,rev 346 │ ├── columns: a:1!null b:2 c:3 347 │ ├── ordering: -1 348 │ └── limit hint: 4.00 349 └── 4 350 351 build 352 ((SELECT a FROM t ORDER BY a)) ORDER BY a 353 ---- 354 error (42601): multiple ORDER BY clauses not allowed 355 356 build 357 SELECT CASE a WHEN 1 THEN b ELSE c END as val FROM t ORDER BY val 358 ---- 359 error (22023): incompatible value type: expected c to be of type int, found type bool 360 361 build 362 SELECT * FROM t ORDER BY 0 363 ---- 364 error (42P10): ORDER BY position 0 is not in select list 365 366 build 367 SELECT * FROM t ORDER BY true 368 ---- 369 error (42601): non-integer constant in ORDER BY: true 370 371 build 372 SELECT * FROM t ORDER BY 'a' 373 ---- 374 error (42601): non-integer constant in ORDER BY: 'a' 375 376 build 377 SELECT * FROM t ORDER BY 2.5 378 ---- 379 error (42601): non-integer constant in ORDER BY: 2.5 380 381 build 382 SELECT * FROM t ORDER BY foo 383 ---- 384 error (42703): column "foo" does not exist 385 386 build 387 SELECT a FROM t ORDER BY a.b 388 ---- 389 error (42P01): no data source matches prefix: a 390 391 build 392 SELECT generate_series FROM generate_series(1, 100) ORDER BY ARRAY[generate_series] 393 ---- 394 sort 395 ├── columns: generate_series:1 [hidden: column2:2] 396 ├── ordering: +2 397 └── project 398 ├── columns: column2:2 generate_series:1 399 ├── project-set 400 │ ├── columns: generate_series:1 401 │ ├── values 402 │ │ └── () 403 │ └── zip 404 │ └── generate_series(1, 100) 405 └── projections 406 └── ARRAY[generate_series:1] [as=column2:2] 407 408 build 409 SELECT ARRAY[generate_series] FROM generate_series(1, 100) ORDER BY ARRAY[generate_series] 410 ---- 411 sort 412 ├── columns: array:2 413 ├── ordering: +2 414 └── project 415 ├── columns: array:2 416 ├── project-set 417 │ ├── columns: generate_series:1 418 │ ├── values 419 │ │ └── () 420 │ └── zip 421 │ └── generate_series(1, 100) 422 └── projections 423 └── ARRAY[generate_series:1] [as=array:2] 424 425 build 426 SELECT ARRAY[generate_series] FROM generate_series(1, 100) ORDER BY 1 427 ---- 428 sort 429 ├── columns: array:2 430 ├── ordering: +2 431 └── project 432 ├── columns: array:2 433 ├── project-set 434 │ ├── columns: generate_series:1 435 │ ├── values 436 │ │ └── () 437 │ └── zip 438 │ └── generate_series(1, 100) 439 └── projections 440 └── ARRAY[generate_series:1] [as=array:2] 441 442 build 443 SELECT ARRAY[generate_series] AS a FROM generate_series(1, 100) ORDER BY a 444 ---- 445 sort 446 ├── columns: a:2 447 ├── ordering: +2 448 └── project 449 ├── columns: a:2 450 ├── project-set 451 │ ├── columns: generate_series:1 452 │ ├── values 453 │ │ └── () 454 │ └── zip 455 │ └── generate_series(1, 100) 456 └── projections 457 └── ARRAY[generate_series:1] [as=a:2] 458 459 build 460 SELECT generate_series, ARRAY[generate_series] FROM generate_series(1, 1) ORDER BY 1 461 ---- 462 sort 463 ├── columns: generate_series:1 array:2 464 ├── ordering: +1 465 └── project 466 ├── columns: array:2 generate_series:1 467 ├── project-set 468 │ ├── columns: generate_series:1 469 │ ├── values 470 │ │ └── () 471 │ └── zip 472 │ └── generate_series(1, 1) 473 └── projections 474 └── ARRAY[generate_series:1] [as=array:2] 475 476 build 477 SELECT generate_series, ARRAY[generate_series] FROM generate_series(1, 1) ORDER BY generate_series 478 ---- 479 sort 480 ├── columns: generate_series:1 array:2 481 ├── ordering: +1 482 └── project 483 ├── columns: array:2 generate_series:1 484 ├── project-set 485 │ ├── columns: generate_series:1 486 │ ├── values 487 │ │ └── () 488 │ └── zip 489 │ └── generate_series(1, 1) 490 └── projections 491 └── ARRAY[generate_series:1] [as=array:2] 492 493 build 494 SELECT generate_series, ARRAY[generate_series] FROM generate_series(1, 1) ORDER BY -generate_series 495 ---- 496 sort 497 ├── columns: generate_series:1 array:2 [hidden: column3:3] 498 ├── ordering: +3 499 └── project 500 ├── columns: array:2 column3:3 generate_series:1 501 ├── project-set 502 │ ├── columns: generate_series:1 503 │ ├── values 504 │ │ └── () 505 │ └── zip 506 │ └── generate_series(1, 1) 507 └── projections 508 ├── ARRAY[generate_series:1] [as=array:2] 509 └── -generate_series:1 [as=column3:3] 510 511 512 # Sort should be skipped if the ORDER BY clause is constant. 513 build 514 SELECT * FROM t ORDER BY 1+2 515 ---- 516 project 517 ├── columns: a:1!null b:2 c:3 [hidden: column4:4!null] 518 ├── ordering: +4 519 ├── scan t 520 │ └── columns: a:1!null b:2 c:3 521 └── projections 522 └── 3 [as=column4:4] 523 524 build 525 SELECT 1 AS r, * FROM t ORDER BY 1 526 ---- 527 project 528 ├── columns: r:4!null a:1!null b:2 c:3 529 ├── ordering: +4 530 ├── scan t 531 │ └── columns: a:1!null b:2 c:3 532 └── projections 533 └── 1 [as=r:4] 534 535 build 536 SELECT * FROM t ORDER BY length('abc') 537 ---- 538 project 539 ├── columns: a:1!null b:2 c:3 [hidden: column4:4] 540 ├── ordering: +4 541 ├── scan t 542 │ └── columns: a:1!null b:2 c:3 543 └── projections 544 └── length('abc') [as=column4:4] 545 546 build 547 SELECT b+2 AS r FROM t ORDER BY b+2 548 ---- 549 sort 550 ├── columns: r:4 551 ├── ordering: +4 552 └── project 553 ├── columns: r:4 554 ├── scan t 555 │ └── columns: a:1!null b:2 c:3 556 └── projections 557 └── b:2 + 2 [as=r:4] 558 559 # Check that the sort picks up a renamed render properly. 560 build 561 SELECT b+2 AS y FROM t ORDER BY y 562 ---- 563 sort 564 ├── columns: y:4 565 ├── ordering: +4 566 └── project 567 ├── columns: y:4 568 ├── scan t 569 │ └── columns: a:1!null b:2 c:3 570 └── projections 571 └── b:2 + 2 [as=y:4] 572 573 build 574 SELECT b+2 AS y FROM t ORDER BY b+2 575 ---- 576 sort 577 ├── columns: y:4 578 ├── ordering: +4 579 └── project 580 ├── columns: y:4 581 ├── scan t 582 │ └── columns: a:1!null b:2 c:3 583 └── projections 584 └── b:2 + 2 [as=y:4] 585 586 build 587 SELECT b, c FROM t ORDER BY @2 588 ---- 589 sort 590 ├── columns: b:2 c:3 [hidden: column4:4] 591 ├── ordering: +4 592 └── project 593 ├── columns: column4:4 b:2 c:3 594 ├── scan t 595 │ └── columns: a:1!null b:2 c:3 596 └── projections 597 └── b:2 [as=column4:4] 598 599 build 600 SELECT b, c FROM t ORDER BY @4 601 ---- 602 error (42703): invalid column ordinal: @4 603 604 exec-ddl 605 CREATE TABLE abc ( 606 a INT, 607 b INT, 608 c INT, 609 d CHAR, 610 PRIMARY KEY (a, b, c), 611 UNIQUE INDEX bc (b, c), 612 INDEX ba (b, a), 613 FAMILY (a, b, c), 614 FAMILY (d) 615 ) 616 ---- 617 618 exec-ddl 619 CREATE VIEW abcview AS SELECT * FROM abc 620 ---- 621 622 build 623 SELECT d FROM abc ORDER BY lower(d) 624 ---- 625 sort 626 ├── columns: d:4 [hidden: column5:5] 627 ├── ordering: +5 628 └── project 629 ├── columns: column5:5 d:4 630 ├── scan abc 631 │ └── columns: a:1!null b:2!null c:3!null d:4 632 └── projections 633 └── lower(d:4) [as=column5:5] 634 635 build 636 SELECT * FROM abc ORDER BY a 637 ---- 638 scan abc 639 ├── columns: a:1!null b:2!null c:3!null d:4 640 └── ordering: +1 641 642 build 643 SELECT a, b FROM abc ORDER BY b, a 644 ---- 645 sort 646 ├── columns: a:1!null b:2!null 647 ├── ordering: +2,+1 648 └── project 649 ├── columns: a:1!null b:2!null 650 └── scan abc 651 └── columns: a:1!null b:2!null c:3!null d:4 652 653 build 654 SELECT a, b FROM abc ORDER BY b, c 655 ---- 656 sort 657 ├── columns: a:1!null b:2!null [hidden: c:3!null] 658 ├── ordering: +2,+3 659 └── project 660 ├── columns: a:1!null b:2!null c:3!null 661 └── scan abc 662 └── columns: a:1!null b:2!null c:3!null d:4 663 664 build 665 SELECT a, b FROM abc ORDER BY b, c, a DESC 666 ---- 667 project 668 ├── columns: a:1!null b:2!null [hidden: c:3!null] 669 ├── ordering: +2,+3,-1 670 └── sort 671 ├── columns: a:1!null b:2!null c:3!null d:4 672 ├── ordering: +2,+3 673 └── scan abc 674 └── columns: a:1!null b:2!null c:3!null d:4 675 676 build 677 SELECT a FROM abc ORDER BY a DESC 678 ---- 679 project 680 ├── columns: a:1!null 681 ├── ordering: -1 682 └── scan abc,rev 683 ├── columns: a:1!null b:2!null c:3!null d:4 684 └── ordering: -1 685 686 build 687 SELECT a FROM abc ORDER BY a DESC LIMIT 1 688 ---- 689 limit 690 ├── columns: a:1!null 691 ├── internal-ordering: -1 692 ├── ordering: -1 693 ├── project 694 │ ├── columns: a:1!null 695 │ ├── ordering: -1 696 │ ├── limit hint: 1.00 697 │ └── scan abc,rev 698 │ ├── columns: a:1!null b:2!null c:3!null d:4 699 │ ├── ordering: -1 700 │ └── limit hint: 1.00 701 └── 1 702 703 build 704 SELECT a FROM abc ORDER BY a DESC OFFSET 1 705 ---- 706 offset 707 ├── columns: a:1!null 708 ├── internal-ordering: -1 709 ├── ordering: -1 710 ├── project 711 │ ├── columns: a:1!null 712 │ ├── ordering: -1 713 │ └── scan abc,rev 714 │ ├── columns: a:1!null b:2!null c:3!null d:4 715 │ └── ordering: -1 716 └── 1 717 718 build 719 SELECT c FROM abc WHERE b = 2 ORDER BY c 720 ---- 721 sort 722 ├── columns: c:3!null 723 ├── ordering: +3 724 └── project 725 ├── columns: c:3!null 726 └── select 727 ├── columns: a:1!null b:2!null c:3!null d:4 728 ├── scan abc 729 │ └── columns: a:1!null b:2!null c:3!null d:4 730 └── filters 731 └── b:2 = 2 732 733 build 734 SELECT c FROM abc WHERE b = 2 ORDER BY c DESC 735 ---- 736 sort 737 ├── columns: c:3!null 738 ├── ordering: -3 739 └── project 740 ├── columns: c:3!null 741 └── select 742 ├── columns: a:1!null b:2!null c:3!null d:4 743 ├── scan abc 744 │ └── columns: a:1!null b:2!null c:3!null d:4 745 └── filters 746 └── b:2 = 2 747 748 build 749 SELECT * FROM (SELECT b, c FROM abc WHERE a=1 ORDER BY a,b) ORDER BY b,c 750 ---- 751 project 752 ├── columns: b:2!null c:3!null 753 ├── ordering: +2,+3 754 └── project 755 ├── columns: a:1!null b:2!null c:3!null 756 ├── ordering: +2,+3 opt(1) 757 └── select 758 ├── columns: a:1!null b:2!null c:3!null d:4 759 ├── ordering: +2,+3 opt(1) 760 ├── scan abc 761 │ ├── columns: a:1!null b:2!null c:3!null d:4 762 │ └── ordering: +2,+3 opt(1) 763 └── filters 764 └── a:1 = 1 765 766 build 767 SELECT a FROM abc ORDER BY INDEX abc@bc 768 ---- 769 sort 770 ├── columns: a:1!null [hidden: b:2!null c:3!null] 771 ├── ordering: +2,+3 772 └── project 773 ├── columns: a:1!null b:2!null c:3!null 774 └── scan abc 775 └── columns: a:1!null b:2!null c:3!null d:4 776 777 build 778 SELECT a FROM abc ORDER BY PRIMARY KEY a 779 ---- 780 error (42P01): no data source matches prefix: "a" 781 782 build 783 SELECT a FROM abcview ORDER BY INDEX abcview@bc 784 ---- 785 error (42809): "abcview" is not a table 786 787 exec-ddl 788 CREATE TABLE bar (id INT PRIMARY KEY, baz STRING, UNIQUE INDEX i_bar (baz)) 789 ---- 790 791 build 792 SELECT * FROM bar ORDER BY baz, id 793 ---- 794 sort 795 ├── columns: id:1!null baz:2 796 ├── ordering: +2,+1 797 └── scan bar 798 └── columns: id:1!null baz:2 799 800 exec-ddl 801 CREATE TABLE abcd ( 802 a INT PRIMARY KEY, 803 b INT, 804 c INT, 805 d INT, 806 INDEX abc (a, b, c), 807 INDEX bcd (b, c DESC, d) 808 ) 809 ---- 810 811 # Verify that projections after ORDER BY perform correctly (i.e., the outer 812 # expression does not guarantee it will apply the ORDER BY). 813 814 build 815 SELECT a+b AS r FROM (SELECT * FROM abcd ORDER BY d) 816 ---- 817 project 818 ├── columns: r:5 819 ├── scan abcd 820 │ └── columns: a:1!null b:2 c:3 d:4 821 └── projections 822 └── a:1 + b:2 [as=r:5] 823 824 build 825 SELECT b+d AS r FROM (SELECT * FROM abcd ORDER BY a,d) 826 ---- 827 project 828 ├── columns: r:5 829 ├── scan abcd 830 │ └── columns: a:1!null b:2 c:3 d:4 831 └── projections 832 └── b:2 + d:4 [as=r:5] 833 834 build 835 SELECT * FROM (VALUES ('a'), ('b'), ('c')) AS c(x) ORDER BY x 836 ---- 837 sort 838 ├── columns: x:1!null 839 ├── ordering: +1 840 └── values 841 ├── columns: column1:1!null 842 ├── ('a',) 843 ├── ('b',) 844 └── ('c',) 845 846 build 847 SELECT * FROM (SELECT * FROM (VALUES ('a'), ('b'), ('c')) AS c(x) ORDER BY x) 848 ---- 849 values 850 ├── columns: x:1!null 851 ├── ('a',) 852 ├── ('b',) 853 └── ('c',) 854 855 exec-ddl 856 CREATE TABLE blocks ( 857 block_id INT, 858 writer_id STRING, 859 block_num INT, 860 raw_bytes BYTES, 861 PRIMARY KEY (block_id, writer_id, block_num) 862 ) 863 ---- 864 865 # Regression test for #13696. 866 build 867 SELECT block_id,writer_id,block_num,block_id FROM blocks ORDER BY block_id, writer_id, block_num LIMIT 1 868 ---- 869 limit 870 ├── columns: block_id:1!null writer_id:2!null block_num:3!null block_id:1!null 871 ├── internal-ordering: +1,+2,+3 872 ├── ordering: +1,+2,+3 873 ├── project 874 │ ├── columns: block_id:1!null writer_id:2!null block_num:3!null 875 │ ├── ordering: +1,+2,+3 876 │ ├── limit hint: 1.00 877 │ └── scan blocks 878 │ ├── columns: block_id:1!null writer_id:2!null block_num:3!null raw_bytes:4 879 │ ├── ordering: +1,+2,+3 880 │ └── limit hint: 1.00 881 └── 1 882 883 build 884 SELECT a FROM abcd ORDER BY PRIMARY KEY abcd 885 ---- 886 project 887 ├── columns: a:1!null 888 ├── ordering: +1 889 └── scan abcd 890 ├── columns: a:1!null b:2 c:3 d:4 891 └── ordering: +1 892 893 build 894 SELECT a FROM abcd ORDER BY b, PRIMARY KEY abcd 895 ---- 896 sort 897 ├── columns: a:1!null [hidden: b:2] 898 ├── ordering: +2,+1 899 └── project 900 ├── columns: a:1!null b:2 901 └── scan abcd 902 └── columns: a:1!null b:2 c:3 d:4 903 904 build 905 SELECT a FROM abcd ORDER BY INDEX abcd@abc 906 ---- 907 project 908 ├── columns: a:1!null [hidden: b:2 c:3] 909 ├── ordering: +1,+2,+3 910 └── scan abcd 911 ├── columns: a:1!null b:2 c:3 d:4 912 └── ordering: +1 913 914 build 915 SELECT a FROM abcd ORDER BY INDEX abcd@abc DESC 916 ---- 917 project 918 ├── columns: a:1!null [hidden: b:2 c:3] 919 ├── ordering: -1,-2,-3 920 └── scan abcd,rev 921 ├── columns: a:1!null b:2 c:3 d:4 922 └── ordering: -1 923 924 build 925 SELECT a FROM abcd AS foo ORDER BY INDEX abcd@abc DESC 926 ---- 927 error (42P01): no data source matches prefix: t.public.abcd 928 929 build 930 SELECT a FROM abcd AS foo ORDER BY INDEX foo@abc DESC 931 ---- 932 error (42P01): no data source matches prefix: "foo" 933 934 build 935 SELECT a FROM abcd ORDER BY INDEX abcd@bcd 936 ---- 937 sort 938 ├── columns: a:1!null [hidden: b:2 c:3 d:4] 939 ├── ordering: +2,-3,+4,+1 940 └── scan abcd 941 └── columns: a:1!null b:2 c:3 d:4 942 943 build 944 SELECT a FROM abcd ORDER BY INDEX abcd@bcd DESC 945 ---- 946 sort 947 ├── columns: a:1!null [hidden: b:2 c:3 d:4] 948 ├── ordering: -2,+3,-4,-1 949 └── scan abcd 950 └── columns: a:1!null b:2 c:3 d:4 951 952 953 build 954 SELECT a FROM abcd ORDER BY INDEX abcd@nonexistent 955 ---- 956 error (42704): index "nonexistent" not found 957 958 build 959 SELECT a FROM t.public.abcd ORDER BY INDEX t.public.abcd@bcd 960 ---- 961 sort 962 ├── columns: a:1!null [hidden: b:2 c:3 d:4] 963 ├── ordering: +2,-3,+4,+1 964 └── scan t.public.abcd 965 └── columns: a:1!null b:2 c:3 d:4 966 967 build 968 SELECT a FROM t.abcd ORDER BY INDEX t.abcd@bcd 969 ---- 970 sort 971 ├── columns: a:1!null [hidden: b:2 c:3 d:4] 972 ├── ordering: +2,-3,+4,+1 973 └── scan t.public.abcd 974 └── columns: a:1!null b:2 c:3 d:4 975 976 build 977 SELECT a FROM public.abcd ORDER BY INDEX public.abcd@bcd 978 ---- 979 sort 980 ├── columns: a:1!null [hidden: b:2 c:3 d:4] 981 ├── ordering: +2,-3,+4,+1 982 └── scan public.abcd 983 └── columns: a:1!null b:2 c:3 d:4 984 985 build 986 SELECT a FROM (SELECT a FROM abcd) ORDER BY INDEX abcd@bcd 987 ---- 988 error (42P01): no data source matches prefix: t.public.abcd 989 990 # Drop previous table with same name, but different schema. 991 exec-ddl 992 DROP TABLE abcd 993 ---- 994 995 exec-ddl 996 CREATE TABLE abcd ( 997 a INT PRIMARY KEY, 998 b INT, 999 c INT, 1000 d INT 1001 ) 1002 ---- 1003 1004 build 1005 SELECT a, b FROM abcd ORDER BY b, c 1006 ---- 1007 sort 1008 ├── columns: a:1!null b:2 [hidden: c:3] 1009 ├── ordering: +2,+3 1010 └── project 1011 ├── columns: a:1!null b:2 c:3 1012 └── scan abcd 1013 └── columns: a:1!null b:2 c:3 d:4 1014 1015 build 1016 SELECT a FROM abcd ORDER BY b, c 1017 ---- 1018 sort 1019 ├── columns: a:1!null [hidden: b:2 c:3] 1020 ├── ordering: +2,+3 1021 └── project 1022 ├── columns: a:1!null b:2 c:3 1023 └── scan abcd 1024 └── columns: a:1!null b:2 c:3 d:4 1025 1026 build 1027 SELECT a FROM abcd ORDER BY a, b, c 1028 ---- 1029 project 1030 ├── columns: a:1!null [hidden: b:2 c:3] 1031 ├── ordering: +1,+2,+3 1032 └── scan abcd 1033 ├── columns: a:1!null b:2 c:3 d:4 1034 └── ordering: +1 1035 1036 build 1037 SELECT ARRAY[a] FROM abcd ORDER BY 1 1038 ---- 1039 sort 1040 ├── columns: array:5!null 1041 ├── ordering: +5 1042 └── project 1043 ├── columns: array:5!null 1044 ├── scan abcd 1045 │ └── columns: a:1!null b:2 c:3 d:4 1046 └── projections 1047 └── ARRAY[a:1] [as=array:5]