github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/norm/testdata/rules/fold_constants (about) 1 exec-ddl 2 CREATE TABLE a (k INT PRIMARY KEY, i INT, f FLOAT, s STRING, j JSON, arr int[]) 3 ---- 4 5 exec-ddl 6 CREATE TABLE t ( 7 x INT PRIMARY KEY 8 ) 9 ---- 10 11 # -------------------------------------------------- 12 # FoldNullCast 13 # -------------------------------------------------- 14 norm expect=FoldNullCast 15 SELECT 16 null::int, 17 null::timestamptz, 18 null::decimal(19,2)::int::bit::char(2), 19 null::oidvector, 20 null::int2vector 21 ---- 22 values 23 ├── columns: int8:1 timestamptz:2 bpchar:3 oid:4 int2:5 24 ├── cardinality: [1 - 1] 25 ├── key: () 26 ├── fd: ()-->(1-5) 27 └── (NULL, NULL, NULL, NULL, NULL) 28 29 # -------------------------------------------------- 30 # FoldNullUnary 31 # -------------------------------------------------- 32 norm expect=FoldNullUnary 33 SELECT +null::int AS r, -null::int AS s, ~null::int AS t FROM a 34 ---- 35 project 36 ├── columns: r:7 s:8 t:9 37 ├── fd: ()-->(7-9) 38 ├── scan a 39 └── projections 40 ├── CAST(NULL AS INT8) [as=r:7] 41 ├── CAST(NULL AS INT8) [as=s:8] 42 └── CAST(NULL AS INT8) [as=t:9] 43 44 # -------------------------------------------------- 45 # FoldNullBinaryLeft, FoldNullBinaryRight 46 # -------------------------------------------------- 47 norm expect=(FoldNullBinaryLeft,FoldNullBinaryRight) 48 SELECT 49 null::int & 1 AS ra, 1 & null::int AS rb, 50 null::decimal + 1 AS sa, 1 + null::decimal AS sb, 51 null::float % 1 AS ta, 1 % null::float AS tb, 52 null::int << 4 AS ua, 4 << null::int AS ub, 53 54 -- These shouldn't be folded because AllowNullArgs is true for concat with arrays. 55 arr::decimal[] || null AS va, null || arr::string[] AS vb, 56 57 -- Scalars concatenated with nulls match array overloads, and shouldn't be folded. 58 -- In other words, the only overload for decimal concatenation is an array overload. 59 i::decimal || null AS wa, null || i::float AS wb 60 FROM a 61 ---- 62 project 63 ├── columns: ra:7 rb:8 sa:9 sb:10 ta:11 tb:12 ua:13 ub:14 va:15 vb:16 wa:17 wb:18 64 ├── fd: ()-->(7-14) 65 ├── scan a 66 │ └── columns: i:2 arr:6 67 └── projections 68 ├── CAST(NULL AS INT8) [as=ra:7] 69 ├── CAST(NULL AS INT8) [as=rb:8] 70 ├── CAST(NULL AS DECIMAL) [as=sa:9] 71 ├── CAST(NULL AS DECIMAL) [as=sb:10] 72 ├── CAST(NULL AS FLOAT8) [as=ta:11] 73 ├── CAST(NULL AS FLOAT8) [as=tb:12] 74 ├── CAST(NULL AS INT8) [as=ua:13] 75 ├── CAST(NULL AS INT8) [as=ub:14] 76 ├── arr:6::DECIMAL[] || CAST(NULL AS DECIMAL[]) [as=va:15, outer=(6)] 77 ├── CAST(NULL AS STRING[]) || arr:6::STRING[] [as=vb:16, outer=(6)] 78 ├── i:2::DECIMAL || CAST(NULL AS DECIMAL[]) [as=wa:17, outer=(2)] 79 └── CAST(NULL AS FLOAT8[]) || i:2::FLOAT8 [as=wb:18, outer=(2)] 80 81 norm 82 SELECT 83 null::json || '[1, 2]' AS ra, '[1, 2]' || null::json AS rb, 84 null::json->'foo' AS sa, '{}'::jsonb->null::string AS sb, 85 null::json->>'foo' AS ta, '{}'::jsonb->>null::string AS tb, 86 null::json->>'foo' AS ua, '{}'::jsonb->>null::string AS ub, 87 null::json#>ARRAY['foo'] AS va, '{}'::jsonb#>NULL AS vb, 88 null::json#>>ARRAY['foo'] AS wa, '{}'::jsonb#>>NULL AS wb 89 FROM a 90 ---- 91 project 92 ├── columns: ra:7 rb:8 sa:9 sb:10 ta:11 tb:12 ua:11 ub:12 va:13 vb:14 wa:15 wb:14 93 ├── fd: ()-->(7-15) 94 ├── scan a 95 └── projections 96 ├── CAST(NULL AS JSONB) [as=ra:7] 97 ├── CAST(NULL AS JSONB) [as=rb:8] 98 ├── CAST(NULL AS JSONB) [as=sa:9] 99 ├── CAST(NULL AS JSONB) [as=sb:10] 100 ├── CAST(NULL AS STRING) [as=ta:11] 101 ├── CAST(NULL AS STRING) [as=tb:12] 102 ├── CAST(NULL AS JSONB) [as=va:13] 103 ├── NULL [as=vb:14] 104 └── CAST(NULL AS STRING) [as=wa:15] 105 106 # -------------------------------------------------- 107 # FoldNullInNonEmpty 108 # -------------------------------------------------- 109 norm expect=FoldNullInNonEmpty 110 SELECT null IN (i) AS r, null NOT IN (s) AS s FROM a 111 ---- 112 project 113 ├── columns: r:7 s:8 114 ├── fd: ()-->(7,8) 115 ├── scan a 116 └── projections 117 ├── CAST(NULL AS BOOL) [as=r:7] 118 └── CAST(NULL AS BOOL) [as=s:8] 119 120 # -------------------------------------------------- 121 # FoldInNull 122 # -------------------------------------------------- 123 norm expect=FoldInNull 124 SELECT i IN (null, null) AS r, k NOT IN (1 * null, null::int, 1 < null) AS s FROM a 125 ---- 126 project 127 ├── columns: r:7 s:8 128 ├── fd: ()-->(7,8) 129 ├── scan a 130 └── projections 131 ├── CAST(NULL AS BOOL) [as=r:7] 132 └── CAST(NULL AS BOOL) [as=s:8] 133 134 # -------------------------------------------------- 135 # FoldInEmpty 136 # -------------------------------------------------- 137 norm expect=FoldInEmpty 138 SELECT 1 IN () 139 ---- 140 values 141 ├── columns: "?column?":1!null 142 ├── cardinality: [1 - 1] 143 ├── key: () 144 ├── fd: ()-->(1) 145 └── (false,) 146 147 norm expect=FoldInEmpty 148 SELECT NULL IN () 149 ---- 150 values 151 ├── columns: "?column?":1!null 152 ├── cardinality: [1 - 1] 153 ├── key: () 154 ├── fd: ()-->(1) 155 └── (false,) 156 157 norm expect=FoldInEmpty 158 SELECT i FROM a WHERE i = ANY ARRAY[] 159 ---- 160 values 161 ├── columns: i:2!null 162 ├── cardinality: [0 - 0] 163 ├── key: () 164 └── fd: ()-->(2) 165 166 # -------------------------------------------------- 167 # FoldNotInEmpty 168 # -------------------------------------------------- 169 norm expect=FoldNotInEmpty 170 SELECT 1 NOT IN () 171 ---- 172 values 173 ├── columns: "?column?":1!null 174 ├── cardinality: [1 - 1] 175 ├── key: () 176 ├── fd: ()-->(1) 177 └── (true,) 178 179 norm expect=FoldNotInEmpty 180 SELECT NULL NOT IN () 181 ---- 182 values 183 ├── columns: "?column?":1!null 184 ├── cardinality: [1 - 1] 185 ├── key: () 186 ├── fd: ()-->(1) 187 └── (true,) 188 189 # -------------------------------------------------- 190 # FoldArray 191 # -------------------------------------------------- 192 norm expect=FoldArray 193 SELECT ARRAY[1, 2, 3] FROM t 194 ---- 195 project 196 ├── columns: array:2!null 197 ├── fd: ()-->(2) 198 ├── scan t 199 └── projections 200 └── ARRAY[1,2,3] [as=array:2] 201 202 # Do not fold if there is a non-constant element. 203 norm expect-not=FoldArray 204 SELECT ARRAY[1, 2, 3, x] FROM t 205 ---- 206 project 207 ├── columns: array:2!null 208 ├── scan t 209 │ ├── columns: x:1!null 210 │ └── key: (1) 211 └── projections 212 └── ARRAY[1, 2, 3, x:1] [as=array:2, outer=(1)] 213 214 norm expect=FoldArray 215 SELECT ARRAY['foo', 'bar'] 216 ---- 217 values 218 ├── columns: array:1!null 219 ├── cardinality: [1 - 1] 220 ├── key: () 221 ├── fd: ()-->(1) 222 └── (ARRAY['foo','bar'],) 223 224 # -------------------------------------------------- 225 # FoldBinary 226 # -------------------------------------------------- 227 # Fold constant. 228 norm expect=FoldBinary 229 SELECT 1::INT + 2::DECIMAL 230 ---- 231 values 232 ├── columns: "?column?":1!null 233 ├── cardinality: [1 - 1] 234 ├── key: () 235 ├── fd: ()-->(1) 236 └── (3,) 237 238 # Don't fold: out of range error. 239 norm expect-not=FoldBinary 240 SELECT 9223372036854775800::INT + 9223372036854775800::INT 241 ---- 242 values 243 ├── columns: "?column?":1 244 ├── cardinality: [1 - 1] 245 ├── key: () 246 ├── fd: ()-->(1) 247 └── (9223372036854775800 + 9223372036854775800,) 248 249 # Fold constant. 250 norm expect=FoldBinary 251 SELECT 1::INT - 2::INT 252 ---- 253 values 254 ├── columns: "?column?":1!null 255 ├── cardinality: [1 - 1] 256 ├── key: () 257 ├── fd: ()-->(1) 258 └── (-1,) 259 260 # Don't fold: out of range error. 261 norm expect-not=FoldBinary 262 SELECT (-9223372036854775800)::INT - 9223372036854775800::INT 263 ---- 264 values 265 ├── columns: "?column?":1 266 ├── cardinality: [1 - 1] 267 ├── key: () 268 ├── fd: ()-->(1) 269 └── (-9223372036854775800 - 9223372036854775800,) 270 271 # Fold constant. 272 norm expect=FoldBinary 273 SELECT 4::INT * 2::INT 274 ---- 275 values 276 ├── columns: "?column?":1!null 277 ├── cardinality: [1 - 1] 278 ├── key: () 279 ├── fd: ()-->(1) 280 └── (8,) 281 282 # Don't fold: out of range error. 283 norm expect-not=FoldBinary 284 SELECT 9223372036854775800::INT * 9223372036854775800::INT 285 ---- 286 values 287 ├── columns: "?column?":1 288 ├── cardinality: [1 - 1] 289 ├── key: () 290 ├── fd: ()-->(1) 291 └── (9223372036854775800 * 9223372036854775800,) 292 293 # Fold constant. 294 norm expect=FoldBinary 295 SELECT 1::FLOAT / 2::FLOAT 296 ---- 297 values 298 ├── columns: "?column?":1!null 299 ├── cardinality: [1 - 1] 300 ├── key: () 301 ├── fd: ()-->(1) 302 └── (0.5,) 303 304 # Don't fold: divide by zero error. 305 norm expect-not=FoldBinary 306 SELECT 1::INT / 0::INT 307 ---- 308 values 309 ├── columns: "?column?":1 310 ├── cardinality: [1 - 1] 311 ├── immutable, side-effects 312 ├── key: () 313 ├── fd: ()-->(1) 314 └── (1 / 0,) 315 316 # Fold constant. 317 norm expect=FoldBinary 318 SELECT B'01' # B'11' 319 ---- 320 values 321 ├── columns: "?column?":1!null 322 ├── cardinality: [1 - 1] 323 ├── key: () 324 ├── fd: ()-->(1) 325 └── (B'10',) 326 327 # Don't fold: cannot mix bit array sizes error. 328 norm expect-not=FoldBinary 329 SELECT B'01' # B'11001001010101' 330 ---- 331 values 332 ├── columns: "?column?":1 333 ├── cardinality: [1 - 1] 334 ├── key: () 335 ├── fd: ()-->(1) 336 └── (B'01' # B'11001001010101',) 337 338 # Fold constant. 339 norm expect=FoldBinary 340 SELECT B'01' | B'11' 341 ---- 342 values 343 ├── columns: "?column?":1!null 344 ├── cardinality: [1 - 1] 345 ├── key: () 346 ├── fd: ()-->(1) 347 └── (B'11',) 348 349 # Don't fold: cannot mix bit array sizes error. 350 norm expect-not=FoldBinary 351 SELECT B'01' | B'11001001010101' 352 ---- 353 values 354 ├── columns: "?column?":1 355 ├── cardinality: [1 - 1] 356 ├── key: () 357 ├── fd: ()-->(1) 358 └── (B'01' | B'11001001010101',) 359 360 # Fold constant. 361 norm expect=FoldBinary 362 SELECT '2000-05-05 10:00:00+03':::TIMESTAMP - '2000-05-06 10:00:00+03':::TIMESTAMP 363 ---- 364 values 365 ├── columns: "?column?":1!null 366 ├── cardinality: [1 - 1] 367 ├── key: () 368 ├── fd: ()-->(1) 369 └── ('-24:00:00',) 370 371 # Fold constant. 372 norm expect=FoldBinary 373 SELECT '2000-05-05 10:00:00+03':::TIMESTAMP - '2000-05-06 10:00:00+03':::TIMESTAMPTZ 374 ---- 375 values 376 ├── columns: "?column?":1!null 377 ├── cardinality: [1 - 1] 378 ├── key: () 379 ├── fd: ()-->(1) 380 └── ('-21:00:00',) 381 382 # Fold constant. 383 norm expect=FoldBinary 384 SELECT ARRAY['a','b','c'] || 'd'::string 385 ---- 386 values 387 ├── columns: "?column?":1!null 388 ├── cardinality: [1 - 1] 389 ├── key: () 390 ├── fd: ()-->(1) 391 └── (ARRAY['a','b','c','d'],) 392 393 # Fold constant. 394 norm expect=FoldBinary 395 SELECT ARRAY['a','b','c'] || ARRAY['d','e','f'] 396 ---- 397 values 398 ├── columns: "?column?":1!null 399 ├── cardinality: [1 - 1] 400 ├── key: () 401 ├── fd: ()-->(1) 402 └── (ARRAY['a','b','c','d','e','f'],) 403 404 # NULL should not be added to the array. 405 norm expect=FoldBinary 406 SELECT ARRAY[1,2,3] || NULL 407 ---- 408 values 409 ├── columns: "?column?":1!null 410 ├── cardinality: [1 - 1] 411 ├── key: () 412 ├── fd: ()-->(1) 413 └── (ARRAY[1,2,3],) 414 415 # Regression test for #34270. 416 norm expect=FoldBinary 417 VALUES ((e'{}' ->> 0) || (e'{}' ->> 0)) 418 ---- 419 values 420 ├── columns: column1:1 421 ├── cardinality: [1 - 1] 422 ├── key: () 423 ├── fd: ()-->(1) 424 └── (NULL,) 425 426 # -------------------------------------------------- 427 # FoldUnary 428 # -------------------------------------------------- 429 norm expect=FoldUnary 430 SELECT -(1:::int) 431 ---- 432 values 433 ├── columns: "?column?":1!null 434 ├── cardinality: [1 - 1] 435 ├── key: () 436 ├── fd: ()-->(1) 437 └── (-1,) 438 439 norm expect=FoldUnary 440 SELECT -(1:::float) 441 ---- 442 values 443 ├── columns: "?column?":1!null 444 ├── cardinality: [1 - 1] 445 ├── key: () 446 ├── fd: ()-->(1) 447 └── (-1.0,) 448 449 # TODO(justin): it would be better if this produced an error in the optimizer 450 # rather than falling back to execution to error. 451 norm expect-not=FoldUnary format=show-all 452 SELECT -((-9223372036854775808)::int) 453 ---- 454 values 455 ├── columns: "?column?":1(int) 456 ├── cardinality: [1 - 1] 457 ├── stats: [rows=1] 458 ├── cost: 0.02 459 ├── key: () 460 ├── fd: ()-->(1) 461 ├── prune: (1) 462 └── tuple [type=tuple{int}] 463 └── unary-minus [type=int] 464 └── const: -9223372036854775808 [type=int] 465 466 norm expect=FoldUnary format=show-all 467 SELECT -(1:::decimal) 468 ---- 469 values 470 ├── columns: "?column?":1(decimal!null) 471 ├── cardinality: [1 - 1] 472 ├── stats: [rows=1] 473 ├── cost: 0.02 474 ├── key: () 475 ├── fd: ()-->(1) 476 ├── prune: (1) 477 └── tuple [type=tuple{decimal}] 478 └── const: -1 [type=decimal] 479 480 norm expect=FoldUnary format=show-all 481 SELECT -('-1d'::interval); 482 ---- 483 values 484 ├── columns: "?column?":1(interval!null) 485 ├── cardinality: [1 - 1] 486 ├── stats: [rows=1] 487 ├── cost: 0.02 488 ├── key: () 489 ├── fd: ()-->(1) 490 ├── prune: (1) 491 └── tuple [type=tuple{interval}] 492 └── const: '1 day' [type=interval] 493 494 # TODO(justin): this seems incorrect but it's consistent with the existing 495 # planner. Revisit this: #26932. 496 norm expect=FoldUnary 497 SELECT -('-9223372036854775808d'::interval); 498 ---- 499 values 500 ├── columns: "?column?":1!null 501 ├── cardinality: [1 - 1] 502 ├── key: () 503 ├── fd: ()-->(1) 504 └── ('-9223372036854775808 days',) 505 506 # Fold constant. 507 norm expect=FoldUnary 508 SELECT ~(500::INT) 509 ---- 510 values 511 ├── columns: "?column?":1!null 512 ├── cardinality: [1 - 1] 513 ├── key: () 514 ├── fd: ()-->(1) 515 └── (-501,) 516 517 # Fold constant. 518 norm expect=FoldUnary 519 SELECT ~('35.231.178.195'::INET) 520 ---- 521 values 522 ├── columns: "?column?":1!null 523 ├── cardinality: [1 - 1] 524 ├── key: () 525 ├── fd: ()-->(1) 526 └── ('220.24.77.60',) 527 528 # -------------------------------------------------- 529 # FoldComparison 530 # -------------------------------------------------- 531 norm expect=FoldComparison 532 SELECT 1::INT < 2::INT 533 ---- 534 values 535 ├── columns: "?column?":1!null 536 ├── cardinality: [1 - 1] 537 ├── key: () 538 ├── fd: ()-->(1) 539 └── (true,) 540 541 norm expect=FoldComparison 542 SELECT 1::INT > 2::INT 543 ---- 544 values 545 ├── columns: "?column?":1!null 546 ├── cardinality: [1 - 1] 547 ├── key: () 548 ├── fd: ()-->(1) 549 └── (false,) 550 551 norm expect=FoldComparison 552 SELECT 10.0::FLOAT <= 20.0::FLOAT 553 ---- 554 values 555 ├── columns: "?column?":1!null 556 ├── cardinality: [1 - 1] 557 ├── key: () 558 ├── fd: ()-->(1) 559 └── (true,) 560 561 norm expect=FoldComparison 562 SELECT 10.0::FLOAT >= 20.0::FLOAT 563 ---- 564 values 565 ├── columns: "?column?":1!null 566 ├── cardinality: [1 - 1] 567 ├── key: () 568 ├── fd: ()-->(1) 569 └── (false,) 570 571 norm expect=FoldComparison 572 SELECT 2.0::DECIMAL = 2::INT 573 ---- 574 values 575 ├── columns: "?column?":1!null 576 ├── cardinality: [1 - 1] 577 ├── key: () 578 ├── fd: ()-->(1) 579 └── (true,) 580 581 norm expect=FoldComparison 582 SELECT 2.0::DECIMAL != 2::INT 583 ---- 584 values 585 ├── columns: "?column?":1!null 586 ├── cardinality: [1 - 1] 587 ├── key: () 588 ├── fd: ()-->(1) 589 └── (false,) 590 591 norm expect=FoldComparison 592 SELECT 100 IS NOT DISTINCT FROM 200 593 ---- 594 values 595 ├── columns: "?column?":1!null 596 ├── cardinality: [1 - 1] 597 ├── key: () 598 ├── fd: ()-->(1) 599 └── (false,) 600 601 norm expect=FoldComparison 602 SELECT 100 IS DISTINCT FROM 200 603 ---- 604 values 605 ├── columns: "?column?":1!null 606 ├── cardinality: [1 - 1] 607 ├── key: () 608 ├── fd: ()-->(1) 609 └── (true,) 610 611 norm expect=FoldComparison 612 SELECT 'foo' IN ('a', 'b', 'c') 613 ---- 614 values 615 ├── columns: "?column?":1!null 616 ├── cardinality: [1 - 1] 617 ├── key: () 618 ├── fd: ()-->(1) 619 └── (false,) 620 621 norm expect=FoldComparison 622 SELECT 'foo' NOT IN ('a', 'b', 'c') 623 ---- 624 values 625 ├── columns: "?column?":1!null 626 ├── cardinality: [1 - 1] 627 ├── key: () 628 ├── fd: ()-->(1) 629 └── (true,) 630 631 norm expect=FoldComparison 632 SELECT 'foo' LIKE 'foobar' 633 ---- 634 values 635 ├── columns: "?column?":1!null 636 ├── cardinality: [1 - 1] 637 ├── key: () 638 ├── fd: ()-->(1) 639 └── (false,) 640 641 norm expect=FoldComparison 642 SELECT 'foo' NOT LIKE 'foobar' 643 ---- 644 values 645 ├── columns: "?column?":1!null 646 ├── cardinality: [1 - 1] 647 ├── key: () 648 ├── fd: ()-->(1) 649 └── (true,) 650 651 norm expect=FoldComparison 652 SELECT 'foo' ILIKE 'FOO%' 653 ---- 654 values 655 ├── columns: "?column?":1!null 656 ├── cardinality: [1 - 1] 657 ├── key: () 658 ├── fd: ()-->(1) 659 └── (true,) 660 661 norm expect=FoldComparison 662 SELECT 'foo' NOT ILIKE 'FOO%' 663 ---- 664 values 665 ├── columns: "?column?":1!null 666 ├── cardinality: [1 - 1] 667 ├── key: () 668 ├── fd: ()-->(1) 669 └── (false,) 670 671 norm expect=FoldComparison 672 SELECT 'monday' SIMILAR TO '_onday' 673 ---- 674 values 675 ├── columns: "?column?":1!null 676 ├── cardinality: [1 - 1] 677 ├── key: () 678 ├── fd: ()-->(1) 679 └── (true,) 680 681 norm expect=FoldComparison 682 SELECT 'monday' NOT SIMILAR TO '_onday' 683 ---- 684 values 685 ├── columns: "?column?":1!null 686 ├── cardinality: [1 - 1] 687 ├── key: () 688 ├── fd: ()-->(1) 689 └── (false,) 690 691 norm expect=FoldComparison 692 SELECT 'tuEsday' ~ 't[uU][eE]sday' 693 ---- 694 values 695 ├── columns: "?column?":1!null 696 ├── cardinality: [1 - 1] 697 ├── key: () 698 ├── fd: ()-->(1) 699 └── (true,) 700 701 norm expect=FoldComparison 702 SELECT 'tuEsday' !~ 't[uU][eE]sday' 703 ---- 704 values 705 ├── columns: "?column?":1!null 706 ├── cardinality: [1 - 1] 707 ├── key: () 708 ├── fd: ()-->(1) 709 └── (false,) 710 711 norm expect=FoldComparison 712 SELECT 'wednesday' ~* 'W.*y' 713 ---- 714 values 715 ├── columns: "?column?":1!null 716 ├── cardinality: [1 - 1] 717 ├── key: () 718 ├── fd: ()-->(1) 719 └── (true,) 720 721 norm expect=FoldComparison 722 SELECT 'wednesday' !~* 'W.*y' 723 ---- 724 values 725 ├── columns: "?column?":1!null 726 ├── cardinality: [1 - 1] 727 ├── key: () 728 ├── fd: ()-->(1) 729 └── (false,) 730 731 norm expect=FoldComparison 732 SELECT '[1, 2]'::JSONB <@ '[1, 2, 3]'::JSONB 733 ---- 734 values 735 ├── columns: "?column?":1!null 736 ├── cardinality: [1 - 1] 737 ├── key: () 738 ├── fd: ()-->(1) 739 └── (true,) 740 741 norm expect=FoldComparison 742 SELECT ('a', 'b', 'c') = ('d', 'e', 'f') 743 ---- 744 values 745 ├── columns: "?column?":1!null 746 ├── cardinality: [1 - 1] 747 ├── key: () 748 ├── fd: ()-->(1) 749 └── (false,) 750 751 # -------------------------------------------------- 752 # FoldCast 753 # -------------------------------------------------- 754 norm expect=FoldCast 755 SELECT 1::int/1 756 ---- 757 values 758 ├── columns: "?column?":1!null 759 ├── cardinality: [1 - 1] 760 ├── key: () 761 ├── fd: ()-->(1) 762 └── (1,) 763 764 # -------------------------------------------------- 765 # FoldFunction 766 # -------------------------------------------------- 767 norm expect=FoldFunction 768 SELECT length('abc'), upper('xyz'), lower('DEF') 769 ---- 770 values 771 ├── columns: length:1!null upper:2!null lower:3!null 772 ├── cardinality: [1 - 1] 773 ├── key: () 774 ├── fd: ()-->(1-3) 775 └── (3, 'XYZ', 'def') 776 777 norm expect=FoldFunction 778 SELECT encode('abc', 'hex'), decode('616263', 'hex') 779 ---- 780 values 781 ├── columns: encode:1!null decode:2!null 782 ├── cardinality: [1 - 1] 783 ├── key: () 784 ├── fd: ()-->(1,2) 785 └── ('616263', '\x616263') 786 787 norm expect=FoldFunction locality=(region=east,dc=east1-b) 788 SELECT crdb_internal.locality_value('dc') 789 ---- 790 values 791 ├── columns: crdb_internal.locality_value:1!null 792 ├── cardinality: [1 - 1] 793 ├── key: () 794 ├── fd: ()-->(1) 795 └── ('east1-b',) 796 797 norm expect=FoldFunction 798 SELECT crdb_internal.locality_value('unk') 799 ---- 800 values 801 ├── columns: crdb_internal.locality_value:1 802 ├── cardinality: [1 - 1] 803 ├── key: () 804 ├── fd: ()-->(1) 805 └── (NULL,) 806 807 norm expect=FoldFunction 808 SELECT ST_Length(ST_GeomFromText('LINESTRING(743238 2967416,743238 2967450)', 2249)); 809 ---- 810 values 811 ├── columns: st_length:1!null 812 ├── cardinality: [1 - 1] 813 ├── key: () 814 ├── fd: ()-->(1) 815 └── (34.0,) 816 817 norm expect-not=FoldFunction 818 SELECT now(), current_user(), current_database() 819 ---- 820 values 821 ├── columns: now:1 current_user:2 current_database:3 822 ├── cardinality: [1 - 1] 823 ├── stable, side-effects 824 ├── key: () 825 ├── fd: ()-->(1-3) 826 └── (now(), current_user(), current_database()) 827 828 # -------------------------------------------------- 829 # FoldIndirection 830 # -------------------------------------------------- 831 # Fold when input is a static array constructor (but elements are not constant). 832 norm expect=FoldIndirection 833 SELECT ARRAY[i, i + 1][1] FROM a 834 ---- 835 project 836 ├── columns: array:7 837 ├── scan a 838 │ └── columns: i:2 839 └── projections 840 └── i:2 [as=array:7, outer=(2)] 841 842 norm expect=FoldIndirection 843 SELECT ARRAY[i, i + 1][2] FROM a 844 ---- 845 project 846 ├── columns: array:7 847 ├── scan a 848 │ └── columns: i:2 849 └── projections 850 └── i:2 + 1 [as=array:7, outer=(2)] 851 852 # Fold when input is a DArray constant. 853 norm expect=FoldIndirection 854 SELECT ARRAY[4, 5, 6][2] FROM a 855 ---- 856 project 857 ├── columns: array:7!null 858 ├── fd: ()-->(7) 859 ├── scan a 860 └── projections 861 └── 5 [as=array:7] 862 863 # Array bounds are out-of-range. 864 norm expect=FoldIndirection 865 SELECT ARRAY[s, 'foo'][0] FROM a 866 ---- 867 project 868 ├── columns: array:7 869 ├── fd: ()-->(7) 870 ├── scan a 871 └── projections 872 └── CAST(NULL AS STRING) [as=array:7] 873 874 norm expect=FoldIndirection 875 SELECT ARRAY[i, i + 1][3] FROM a 876 ---- 877 project 878 ├── columns: array:7 879 ├── fd: ()-->(7) 880 ├── scan a 881 └── projections 882 └── CAST(NULL AS INT8) [as=array:7] 883 884 norm expect=FoldIndirection 885 SELECT ARRAY[4, 5, 6][0] FROM a 886 ---- 887 project 888 ├── columns: array:7 889 ├── fd: ()-->(7) 890 ├── scan a 891 └── projections 892 └── CAST(NULL AS INT8) [as=array:7] 893 894 # Array is dynamically constructed. 895 norm expect-not=FoldIndirection 896 SELECT arr[0] FROM a 897 ---- 898 project 899 ├── columns: arr:7 900 ├── scan a 901 │ └── columns: a.arr:6 902 └── projections 903 └── a.arr:6[0] [as=arr:7, outer=(6)] 904 905 # Regression test for #40404. 906 norm expect=FoldIndirection 907 SELECT (SELECT x[1]) FROM (VALUES(null::oid[])) v(x) 908 ---- 909 values 910 ├── columns: x:3 911 ├── cardinality: [1 - 1] 912 ├── key: () 913 ├── fd: ()-->(3) 914 └── tuple 915 └── subquery 916 └── values 917 ├── columns: x:2 918 ├── cardinality: [1 - 1] 919 ├── key: () 920 ├── fd: ()-->(2) 921 └── (NULL,) 922 923 # -------------------------------------------------- 924 # FoldColumnAccess 925 # -------------------------------------------------- 926 # Fold when input is a static tuple constructor (but elements are not constant). 927 # NOTE: Use constant array access to avoid triggering ColumnAccess::TypeCheck 928 # constant tuple folding. 929 norm expect=FoldColumnAccess 930 SELECT (ARRAY[(('foo', i) AS foo, bar)][1]).foo FROM a 931 ---- 932 project 933 ├── columns: foo:7!null 934 ├── fd: ()-->(7) 935 ├── scan a 936 └── projections 937 └── 'foo' [as=foo:7] 938 939 norm expect=FoldColumnAccess 940 SELECT (ARRAY[(('foo', i) AS foo, bar)][1]).bar FROM a 941 ---- 942 project 943 ├── columns: bar:7 944 ├── scan a 945 │ └── columns: i:2 946 └── projections 947 └── i:2 [as=bar:7, outer=(2)] 948 949 # Fold when input is a constant DTuple. 950 norm expect=FoldColumnAccess 951 SELECT (ARRAY[(('foo', 'bar') AS foo, bar)][1]).foo 952 ---- 953 values 954 ├── columns: foo:1!null 955 ├── cardinality: [1 - 1] 956 ├── key: () 957 ├── fd: ()-->(1) 958 └── ('foo',) 959 960 # -------------------------------------------------- 961 # FoldEqualsAnyNull 962 # -------------------------------------------------- 963 norm expect=FoldEqualsAnyNull 964 SELECT * FROM a WHERE i = ANY (NULL::int[]) 965 ---- 966 values 967 ├── columns: k:1!null i:2!null f:3!null s:4!null j:5!null arr:6!null 968 ├── cardinality: [0 - 0] 969 ├── key: () 970 └── fd: ()-->(1-6)