github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/norm/testdata/rules/comp (about) 1 exec-ddl 2 CREATE TABLE a (k INT PRIMARY KEY, i INT, f FLOAT, s STRING, j JSON, d DATE) 3 ---- 4 5 # -------------------------------------------------- 6 # CommuteVarInequality 7 # -------------------------------------------------- 8 9 # Put variables on both sides of comparison operator to avoid matching constant 10 # patterns. 11 norm expect=CommuteVarInequality 12 SELECT * FROM a WHERE 1+i<k AND k-1<=i AND i*i>k AND k/2>=i 13 ---- 14 select 15 ├── columns: k:1!null i:2!null f:3 s:4 j:5 d:6 16 ├── key: (1) 17 ├── fd: (1)-->(2-6) 18 ├── scan a 19 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 d:6 20 │ ├── key: (1) 21 │ └── fd: (1)-->(2-6) 22 └── filters 23 ├── k:1 > (i:2 + 1) [outer=(1,2), constraints=(/1: (/NULL - ])] 24 ├── i:2 >= (k:1 - 1) [outer=(1,2), constraints=(/2: (/NULL - ])] 25 ├── k:1 < (i:2 * i:2) [outer=(1,2), constraints=(/1: (/NULL - ])] 26 └── i:2 <= (k:1 / 2) [outer=(1,2), constraints=(/2: (/NULL - ])] 27 28 # -------------------------------------------------- 29 # CommuteConstInequality 30 # -------------------------------------------------- 31 norm expect=CommuteConstInequality 32 SELECT * FROM a WHERE 5+1<i+k AND 5*5/3<=i*2 AND 5>i AND 'foo'>=s 33 ---- 34 select 35 ├── columns: k:1!null i:2!null f:3 s:4!null j:5 d:6 36 ├── key: (1) 37 ├── fd: (1)-->(2-6) 38 ├── scan a 39 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 d:6 40 │ ├── key: (1) 41 │ └── fd: (1)-->(2-6) 42 └── filters 43 ├── (i:2 + k:1) > 6 [outer=(1,2)] 44 ├── (i:2 * 2) >= 8.3333333333333333333 [outer=(2)] 45 ├── i:2 < 5 [outer=(2), constraints=(/2: (/NULL - /4]; tight)] 46 └── s:4 <= 'foo' [outer=(4), constraints=(/4: (/NULL - /'foo']; tight)] 47 48 norm expect=CommuteConstInequality 49 SELECT * FROM a WHERE length('foo')+1<i+k AND length('bar')<=i*2 50 ---- 51 select 52 ├── columns: k:1!null i:2 f:3 s:4 j:5 d:6 53 ├── key: (1) 54 ├── fd: (1)-->(2-6) 55 ├── scan a 56 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 d:6 57 │ ├── key: (1) 58 │ └── fd: (1)-->(2-6) 59 └── filters 60 ├── (i:2 + k:1) > 4 [outer=(1,2)] 61 └── (i:2 * 2) >= 3 [outer=(2)] 62 63 # Impure function should not be considered constant. 64 norm expect-not=CommuteConstInequality 65 SELECT * FROM a WHERE random()::int>a.i+a.i 66 ---- 67 select 68 ├── columns: k:1!null i:2 f:3 s:4 j:5 d:6 69 ├── volatile, side-effects 70 ├── key: (1) 71 ├── fd: (1)-->(2-6) 72 ├── scan a 73 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 d:6 74 │ ├── key: (1) 75 │ └── fd: (1)-->(2-6) 76 └── filters 77 └── random()::INT8 > (i:2 + i:2) [outer=(2), volatile, side-effects] 78 79 # -------------------------------------------------- 80 # NormalizeCmpPlusConst 81 # -------------------------------------------------- 82 norm expect=NormalizeCmpPlusConst 83 SELECT * 84 FROM a 85 WHERE 86 k+1 = 2 AND 87 (f+f)+2 < 5 AND 88 1::decimal+i >= length('foo') AND 89 i+2+2 > 10 AND 90 '1:00:00'::time + i::interval >= '2:00:00'::time 91 ---- 92 select 93 ├── columns: k:1!null i:2!null f:3 s:4 j:5 d:6 94 ├── cardinality: [0 - 1] 95 ├── key: () 96 ├── fd: ()-->(1-6) 97 ├── scan a 98 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 d:6 99 │ ├── key: (1) 100 │ └── fd: (1)-->(2-6) 101 └── filters 102 ├── (i:2 >= 2) AND (i:2 > 6) [outer=(2), constraints=(/2: [/7 - ]; tight)] 103 ├── k:1 = 1 [outer=(1), constraints=(/1: [/1 - /1]; tight), fd=()-->(1)] 104 ├── (f:3 + f:3) < 3.0 [outer=(3)] 105 └── i:2::INTERVAL >= '01:00:00' [outer=(2)] 106 107 # Try case that should not match pattern because Minus overload is not defined. 108 norm expect-not=NormalizeCmpPlusConst 109 SELECT * FROM a WHERE s::date + '02:00:00'::time = '2000-01-01T02:00:00'::timestamp 110 ---- 111 select 112 ├── columns: k:1!null i:2 f:3 s:4 j:5 d:6 113 ├── key: (1) 114 ├── fd: (1)-->(2-6) 115 ├── scan a 116 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 d:6 117 │ ├── key: (1) 118 │ └── fd: (1)-->(2-6) 119 └── filters 120 └── (s:4::DATE + '02:00:00') = '2000-01-01 02:00:00+00:00' [outer=(4)] 121 122 # -------------------------------------------------- 123 # NormalizeCmpMinusConst 124 # -------------------------------------------------- 125 norm expect=NormalizeCmpMinusConst 126 SELECT * 127 FROM a 128 WHERE 129 k-1 = 2 AND 130 (f+f)-2 < 5 AND 131 i-1::decimal >= length('foo') AND 132 i-2-2 < 10 AND 133 f+i::float-10.0 >= 100.0 AND 134 d-'1w'::interval >= '2018-09-23'::date 135 ---- 136 select 137 ├── columns: k:1!null i:2!null f:3 s:4 j:5 d:6!null 138 ├── cardinality: [0 - 1] 139 ├── key: () 140 ├── fd: ()-->(1-6) 141 ├── scan a 142 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 d:6 143 │ ├── key: (1) 144 │ └── fd: (1)-->(2-6) 145 └── filters 146 ├── (i:2 >= 4) AND (i:2 < 14) [outer=(2), constraints=(/2: [/4 - /13]; tight)] 147 ├── k:1 = 3 [outer=(1), constraints=(/1: [/3 - /3]; tight), fd=()-->(1)] 148 ├── (f:3 + f:3) < 7.0 [outer=(3)] 149 ├── (f:3 + i:2::FLOAT8) >= 110.0 [outer=(2,3)] 150 └── d:6 >= '2018-09-30' [outer=(6), constraints=(/6: [/'2018-09-30' - ]; tight)] 151 152 # Try case that should not match pattern because Plus overload is not defined. 153 norm expect-not=NormalizeCmpMinusConst 154 SELECT * FROM a WHERE s::json - 1 = '[1]'::json 155 ---- 156 select 157 ├── columns: k:1!null i:2 f:3 s:4 j:5 d:6 158 ├── key: (1) 159 ├── fd: (1)-->(2-6) 160 ├── scan a 161 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 d:6 162 │ ├── key: (1) 163 │ └── fd: (1)-->(2-6) 164 └── filters 165 └── (s:4::JSONB - 1) = '[1]' [outer=(4)] 166 167 # -------------------------------------------------- 168 # NormalizeCmpConstMinus 169 # -------------------------------------------------- 170 norm expect=NormalizeCmpConstMinus 171 SELECT * 172 FROM a 173 WHERE 174 1-k = 2 AND 175 2-(f+f) < 5 AND 176 1::decimal-i <= length('foo') AND 177 2-(2-i) > 10 AND 178 10.0-(f+i::float) >= 100.0 179 ---- 180 select 181 ├── columns: k:1!null i:2!null f:3 s:4 j:5 d:6 182 ├── cardinality: [0 - 1] 183 ├── key: () 184 ├── fd: ()-->(1-6) 185 ├── scan a 186 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 d:6 187 │ ├── key: (1) 188 │ └── fd: (1)-->(2-6) 189 └── filters 190 ├── (i:2 >= -2) AND (i:2 > 10) [outer=(2), constraints=(/2: [/11 - ]; tight)] 191 ├── k:1 = -1 [outer=(1), constraints=(/1: [/-1 - /-1]; tight), fd=()-->(1)] 192 ├── (f:3 + f:3) > -3.0 [outer=(3)] 193 └── (f:3 + i:2::FLOAT8) <= -90.0 [outer=(2,3)] 194 195 # Try case that should not match pattern because Minus overload is not defined. 196 norm expect-not=NormalizeCmpConstMinus 197 SELECT * FROM a WHERE '[1, 2]'::json - i = '[1]' 198 ---- 199 select 200 ├── columns: k:1!null i:2 f:3 s:4 j:5 d:6 201 ├── key: (1) 202 ├── fd: (1)-->(2-6) 203 ├── scan a 204 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 d:6 205 │ ├── key: (1) 206 │ └── fd: (1)-->(2-6) 207 └── filters 208 └── ('[1, 2]' - i:2) = '[1]' [outer=(2)] 209 210 # -------------------------------------------------- 211 # NormalizeTupleEquality 212 # -------------------------------------------------- 213 norm expect=NormalizeTupleEquality 214 SELECT * FROM a WHERE (i, f, s) = (1, 3.5, 'foo') 215 ---- 216 select 217 ├── columns: k:1!null i:2!null f:3!null s:4!null j:5 d:6 218 ├── key: (1) 219 ├── fd: ()-->(2-4), (1)-->(5,6) 220 ├── scan a 221 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 d:6 222 │ ├── key: (1) 223 │ └── fd: (1)-->(2-6) 224 └── filters 225 ├── i:2 = 1 [outer=(2), constraints=(/2: [/1 - /1]; tight), fd=()-->(2)] 226 ├── f:3 = 3.5 [outer=(3), constraints=(/3: [/3.5 - /3.5]; tight), fd=()-->(3)] 227 └── s:4 = 'foo' [outer=(4), constraints=(/4: [/'foo' - /'foo']; tight), fd=()-->(4)] 228 229 # Empty tuples. 230 norm expect=NormalizeTupleEquality 231 SELECT * FROM a WHERE () = () 232 ---- 233 scan a 234 ├── columns: k:1!null i:2 f:3 s:4 j:5 d:6 235 ├── key: (1) 236 └── fd: (1)-->(2-6) 237 238 # -------------------------------------------------- 239 # NormalizeTupleEquality, NormalizeNestedAnds 240 # -------------------------------------------------- 241 242 # Nested tuples. 243 norm expect=(NormalizeTupleEquality,NormalizeNestedAnds) 244 SELECT * FROM a WHERE (1, (2, 'foo')) = (k, (i, s)) 245 ---- 246 select 247 ├── columns: k:1!null i:2!null f:3 s:4!null j:5 d:6 248 ├── cardinality: [0 - 1] 249 ├── key: () 250 ├── fd: ()-->(1-6) 251 ├── scan a 252 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 d:6 253 │ ├── key: (1) 254 │ └── fd: (1)-->(2-6) 255 └── filters 256 ├── k:1 = 1 [outer=(1), constraints=(/1: [/1 - /1]; tight), fd=()-->(1)] 257 ├── i:2 = 2 [outer=(2), constraints=(/2: [/2 - /2]; tight), fd=()-->(2)] 258 └── s:4 = 'foo' [outer=(4), constraints=(/4: [/'foo' - /'foo']; tight), fd=()-->(4)] 259 260 # -------------------------------------------------- 261 # FoldNullComparisonLeft, FoldNullComparisonRight 262 # -------------------------------------------------- 263 264 # Use null::type to circumvent type checker constant folding. 265 norm expect=(FoldNullComparisonLeft,FoldNullComparisonRight) 266 SELECT * 267 FROM a 268 WHERE 269 null::int = 1 OR 1 = null::int OR 270 null::int <> 1 OR 1 <> null::int OR 271 null::int > 1 OR 1 > null::int OR 272 null::int >= 1 OR 1 >= null::int OR 273 null::int < 1 OR 1 < null::int OR 274 null::int <= 1 OR 1 <= null::int OR 275 null::string LIKE 'foo' OR 'foo' LIKE null::string OR 276 null::string NOT LIKE 'foo' OR 'foo' NOT LIKE null::string OR 277 null::string ILIKE 'foo' OR 'foo' ILIKE null::string OR 278 null::string NOT ILIKE 'foo' OR 'foo' NOT ILIKE null::string OR 279 null::string SIMILAR TO 'foo' OR 'foo' SIMILAR TO null::string OR 280 null::string NOT SIMILAR TO 'foo' OR 'foo' NOT SIMILAR TO null::string OR 281 null::string ~ 'foo' OR 'foo' ~ null::string OR 282 null::string !~ 'foo' OR 'foo' !~ null::string OR 283 null::string ~* 'foo' OR 'foo' ~* null::string OR 284 null::string !~* 'foo' OR 'foo' !~* null::string OR 285 null::string[] && ARRAY['foo'] OR ARRAY['foo'] && null::string[] OR 286 null::jsonb @> '"foo"' OR '"foo"' <@ null::jsonb OR 287 null::jsonb ? 'foo' OR '{}' ? null::string OR 288 null::jsonb ?| ARRAY['foo'] OR '{}' ?| null::string[] OR 289 null::jsonb ?& ARRAY['foo'] OR '{}' ?& null::string[] 290 ---- 291 values 292 ├── columns: k:1!null i:2!null f:3!null s:4!null j:5!null d:6!null 293 ├── cardinality: [0 - 0] 294 ├── key: () 295 └── fd: ()-->(1-6) 296 297 # -------------------------------------------------- 298 # FoldIsNull 299 # -------------------------------------------------- 300 norm expect=FoldIsNull 301 SELECT NULL IS NULL AS r 302 ---- 303 values 304 ├── columns: r:1!null 305 ├── cardinality: [1 - 1] 306 ├── key: () 307 ├── fd: ()-->(1) 308 └── (true,) 309 310 # -------------------------------------------------- 311 # FoldNonNullIsNull 312 # -------------------------------------------------- 313 norm expect=FoldNonNullIsNull 314 SELECT 1 IS NULL AS r 315 ---- 316 values 317 ├── columns: r:1!null 318 ├── cardinality: [1 - 1] 319 ├── key: () 320 ├── fd: ()-->(1) 321 └── (false,) 322 323 norm expect=FoldNonNullIsNull 324 SELECT (1, 2, 3) IS NOT DISTINCT FROM NULL AS r 325 ---- 326 values 327 ├── columns: r:1!null 328 ├── cardinality: [1 - 1] 329 ├── key: () 330 ├── fd: ()-->(1) 331 └── (false,) 332 333 norm expect=FoldNonNullIsNull 334 SELECT (1, NULL) IS NOT DISTINCT FROM NULL AS r 335 ---- 336 values 337 ├── columns: r:1!null 338 ├── cardinality: [1 - 1] 339 ├── key: () 340 ├── fd: ()-->(1) 341 └── (false,) 342 343 norm expect=FoldNonNullIsNull 344 SELECT (NULL, NULL) IS NOT DISTINCT FROM NULL AS r 345 ---- 346 values 347 ├── columns: r:1!null 348 ├── cardinality: [1 - 1] 349 ├── key: () 350 ├── fd: ()-->(1) 351 └── (false,) 352 353 # -------------------------------------------------- 354 # FoldNullTupleIsTupleNull 355 # -------------------------------------------------- 356 norm expect=FoldNullTupleIsTupleNull 357 SELECT (NULL, NULL) IS NULL AS r 358 ---- 359 values 360 ├── columns: r:1!null 361 ├── cardinality: [1 - 1] 362 ├── key: () 363 ├── fd: ()-->(1) 364 └── (true,) 365 366 norm expect=FoldNullTupleIsTupleNull 367 SELECT () IS NULL AS r 368 ---- 369 values 370 ├── columns: r:1!null 371 ├── cardinality: [1 - 1] 372 ├── key: () 373 ├── fd: ()-->(1) 374 └── (true,) 375 376 norm expect-not=FoldNullTupleIsTupleNull 377 SELECT (k, NULL) IS NULL FROM a 378 ---- 379 project 380 ├── columns: "?column?":7!null 381 ├── scan a 382 │ ├── columns: k:1!null 383 │ └── key: (1) 384 └── projections 385 └── (k:1, NULL) IS NULL [as="?column?":7, outer=(1)] 386 387 # -------------------------------------------------- 388 # FoldNonNullTupleIsTupleNull 389 # -------------------------------------------------- 390 norm expect=FoldNonNullTupleIsTupleNull 391 SELECT (1, 2) IS NULL AS r 392 ---- 393 values 394 ├── columns: r:1!null 395 ├── cardinality: [1 - 1] 396 ├── key: () 397 ├── fd: ()-->(1) 398 └── (false,) 399 400 norm expect=FoldNonNullTupleIsTupleNull 401 SELECT (1, NULL) IS NULL AS r 402 ---- 403 values 404 ├── columns: r:1!null 405 ├── cardinality: [1 - 1] 406 ├── key: () 407 ├── fd: ()-->(1) 408 └── (false,) 409 410 norm expect=FoldNonNullTupleIsTupleNull 411 SELECT (1, k) IS NULL FROM a 412 ---- 413 project 414 ├── columns: "?column?":7!null 415 ├── fd: ()-->(7) 416 ├── scan a 417 └── projections 418 └── false [as="?column?":7] 419 420 norm expect=FoldNonNullTupleIsTupleNull 421 SELECT ((NULL, NULL), NULL) IS NULL AS r 422 ---- 423 values 424 ├── columns: r:1!null 425 ├── cardinality: [1 - 1] 426 ├── key: () 427 ├── fd: ()-->(1) 428 └── (false,) 429 430 norm expect=FoldNonNullTupleIsTupleNull 431 SELECT (ARRAY[NULL, NULL], NULL) IS NULL AS r 432 ---- 433 values 434 ├── columns: r:1!null 435 ├── cardinality: [1 - 1] 436 ├── key: () 437 ├── fd: ()-->(1) 438 └── (false,) 439 440 norm expect-not=FoldNonNullTupleIsTupleNull 441 SELECT (k, NULL) IS NULL FROM a 442 ---- 443 project 444 ├── columns: "?column?":7!null 445 ├── scan a 446 │ ├── columns: k:1!null 447 │ └── key: (1) 448 └── projections 449 └── (k:1, NULL) IS NULL [as="?column?":7, outer=(1)] 450 451 # -------------------------------------------------- 452 # FoldIsNotNull 453 # -------------------------------------------------- 454 norm expect=FoldIsNotNull 455 SELECT NULL IS NOT NULL AS r, NULL IS NOT TRUE AS s 456 ---- 457 values 458 ├── columns: r:1!null s:2!null 459 ├── cardinality: [1 - 1] 460 ├── key: () 461 ├── fd: ()-->(1,2) 462 └── (false, true) 463 464 # -------------------------------------------------- 465 # FoldNonNullIsNotNull 466 # -------------------------------------------------- 467 468 # We could (but do not currently) infer that k IS NOT NULL is always True given 469 # that k is declared NOT NULL. 470 norm expect=FoldNonNullIsNotNull 471 SELECT 1 IS NOT NULL AS r, k IS NOT NULL AS s, i IS NOT NULL AS t FROM a 472 ---- 473 project 474 ├── columns: r:7!null s:8!null t:9!null 475 ├── fd: ()-->(7) 476 ├── scan a 477 │ ├── columns: k:1!null i:2 478 │ ├── key: (1) 479 │ └── fd: (1)-->(2) 480 └── projections 481 ├── true [as=r:7] 482 ├── k:1 IS NOT NULL [as=s:8, outer=(1)] 483 └── i:2 IS NOT NULL [as=t:9, outer=(2)] 484 485 norm expect=FoldNonNullIsNotNull 486 SELECT (1, 2, 3) IS DISTINCT FROM NULL AS r 487 ---- 488 values 489 ├── columns: r:1!null 490 ├── cardinality: [1 - 1] 491 ├── key: () 492 ├── fd: ()-->(1) 493 └── (true,) 494 495 norm expect=FoldNonNullIsNotNull 496 SELECT (1, NULL) IS DISTINCT FROM NULL AS r 497 ---- 498 values 499 ├── columns: r:1!null 500 ├── cardinality: [1 - 1] 501 ├── key: () 502 ├── fd: ()-->(1) 503 └── (true,) 504 505 norm expect=FoldNonNullIsNotNull 506 SELECT (1, NULL) IS DISTINCT FROM NULL AS r 507 ---- 508 values 509 ├── columns: r:1!null 510 ├── cardinality: [1 - 1] 511 ├── key: () 512 ├── fd: ()-->(1) 513 └── (true,) 514 515 # -------------------------------------------------- 516 # FoldNonNullTupleIsTupleNotNull 517 # -------------------------------------------------- 518 norm expect=FoldNonNullTupleIsTupleNotNull 519 SELECT (1, 1) IS NOT NULL AS r 520 ---- 521 values 522 ├── columns: r:1!null 523 ├── cardinality: [1 - 1] 524 ├── key: () 525 ├── fd: ()-->(1) 526 └── (true,) 527 528 norm expect=FoldNonNullTupleIsTupleNotNull 529 SELECT (1, (NULL, NULL)) IS NOT NULL AS r 530 ---- 531 values 532 ├── columns: r:1!null 533 ├── cardinality: [1 - 1] 534 ├── key: () 535 ├── fd: ()-->(1) 536 └── (true,) 537 538 norm expect=FoldNonNullTupleIsTupleNotNull 539 SELECT (1, ARRAY[NULL, NULL]) IS NOT NULL AS r 540 ---- 541 values 542 ├── columns: r:1!null 543 ├── cardinality: [1 - 1] 544 ├── key: () 545 ├── fd: ()-->(1) 546 └── (true,) 547 548 norm expect=FoldNonNullTupleIsTupleNotNull 549 SELECT () IS NOT NULL AS r 550 ---- 551 values 552 ├── columns: r:1!null 553 ├── cardinality: [1 - 1] 554 ├── key: () 555 ├── fd: ()-->(1) 556 └── (true,) 557 558 norm expect-not=FoldNonNullTupleIsTupleNotNull 559 SELECT (1, k) IS NOT NULL FROM a 560 ---- 561 project 562 ├── columns: "?column?":7!null 563 ├── scan a 564 │ ├── columns: k:1!null 565 │ └── key: (1) 566 └── projections 567 └── (1, k:1) IS NOT NULL [as="?column?":7, outer=(1)] 568 569 # -------------------------------------------------- 570 # FoldNullTupleIsTupleNotNull 571 # -------------------------------------------------- 572 norm expect=FoldNullTupleIsTupleNotNull 573 SELECT (1, NULL) IS NOT NULL AS r 574 ---- 575 values 576 ├── columns: r:1!null 577 ├── cardinality: [1 - 1] 578 ├── key: () 579 ├── fd: ()-->(1) 580 └── (false,) 581 582 norm expect=FoldNullTupleIsTupleNotNull 583 SELECT (k, NULL) IS NOT NULL FROM a 584 ---- 585 project 586 ├── columns: "?column?":7!null 587 ├── fd: ()-->(7) 588 ├── scan a 589 └── projections 590 └── false [as="?column?":7] 591 592 norm expect-not=FoldNonNullTupleIsTupleNotNull 593 SELECT (1, k) IS NOT NULL FROM a 594 ---- 595 project 596 ├── columns: "?column?":7!null 597 ├── scan a 598 │ ├── columns: k:1!null 599 │ └── key: (1) 600 └── projections 601 └── (1, k:1) IS NOT NULL [as="?column?":7, outer=(1)] 602 603 # -------------------------------------------------- 604 # CommuteNullIs 605 # -------------------------------------------------- 606 norm expect=CommuteNullIs 607 SELECT NULL IS NOT TRUE AS r, NULL IS TRUE AS s 608 ---- 609 values 610 ├── columns: r:1!null s:2!null 611 ├── cardinality: [1 - 1] 612 ├── key: () 613 ├── fd: ()-->(1,2) 614 └── (true, false) 615 616 # -------------------------------------------------- 617 # NormalizeCmpTimeZoneFunction 618 # -------------------------------------------------- 619 exec-ddl 620 CREATE TABLE t (ts TIMESTAMP, tz TIMESTAMPTZ) 621 ---- 622 623 norm expect=NormalizeCmpTimeZoneFunction 624 SELECT timezone('America/Denver', ts) >= '2020-06-01 12:35:55-07' FROM t 625 ---- 626 project 627 ├── columns: "?column?":4 628 ├── scan t 629 │ └── columns: ts:1 630 └── projections 631 └── ts:1 >= '2020-06-01 13:35:55+00:00' [as="?column?":4, outer=(1)] 632 633 # Apply after commuting the inequality. 634 norm expect=NormalizeCmpTimeZoneFunction 635 SELECT '2020-06-01 12:35:55-07' >= timezone('America/Denver', ts) FROM t 636 ---- 637 project 638 ├── columns: "?column?":4 639 ├── scan t 640 │ └── columns: ts:1 641 └── projections 642 └── ts:1 <= '2020-06-01 13:35:55+00:00' [as="?column?":4, outer=(1)] 643 644 # Don't normalize when the right-hand-side is not a constant. 645 norm expect-not=NormalizeCmpTimeZoneFunction 646 SELECT timezone('America/Denver', ts) >= tz FROM t 647 ---- 648 project 649 ├── columns: "?column?":4 650 ├── immutable 651 ├── scan t 652 │ └── columns: ts:1 tz:2 653 └── projections 654 └── tz:2 <= timezone('America/Denver', ts:1) [as="?column?":4, outer=(1,2), immutable] 655 656 # Don't normalize when the timezone() arguments are constants. 657 norm expect-not=NormalizeCmpTimeZoneFunction 658 SELECT timezone('America/Denver', '2020-06-01 12:35:55'::TIMESTAMP) >= tz FROM t 659 ---- 660 project 661 ├── columns: "?column?":4 662 ├── scan t 663 │ └── columns: tz:2 664 └── projections 665 └── tz:2 <= '2020-06-01 18:35:55+00:00' [as="?column?":4, outer=(2)] 666 667 # -------------------------------------------------- 668 # NormalizeCmpTimeZoneFunctionTZ 669 # -------------------------------------------------- 670 norm expect=NormalizeCmpTimeZoneFunctionTZ 671 SELECT timezone('America/Denver', tz) >= '2020-06-01 12:35:55' FROM t 672 ---- 673 project 674 ├── columns: "?column?":4 675 ├── scan t 676 │ └── columns: tz:2 677 └── projections 678 └── tz:2 >= '2020-06-01 18:35:55+00:00' [as="?column?":4, outer=(2)] 679 680 # Apply after commuting the inequality. 681 norm expect=NormalizeCmpTimeZoneFunctionTZ 682 SELECT '2020-06-01 12:35:55' >= timezone('America/Denver', tz) FROM t 683 ---- 684 project 685 ├── columns: "?column?":4 686 ├── scan t 687 │ └── columns: tz:2 688 └── projections 689 └── tz:2 <= '2020-06-01 18:35:55+00:00' [as="?column?":4, outer=(2)] 690 691 # Don't normalize when the right-hand-side is not a constant. 692 norm expect-not=NormalizeCmpTimeZoneFunctionTZ 693 SELECT timezone('America/Denver', tz) >= ts FROM t 694 ---- 695 project 696 ├── columns: "?column?":4 697 ├── immutable 698 ├── scan t 699 │ └── columns: ts:1 tz:2 700 └── projections 701 └── ts:1 <= timezone('America/Denver', tz:2) [as="?column?":4, outer=(1,2), immutable] 702 703 # Don't normalize when the timezone() arguments are constants. 704 norm expect-not=NormalizeCmpTimeZoneFunctionTZ 705 SELECT timezone('America/Denver', '2020-06-01 12:35:55-07'::TIMESTAMPTZ) >= ts FROM t 706 ---- 707 project 708 ├── columns: "?column?":4 709 ├── scan t 710 │ └── columns: ts:1 711 └── projections 712 └── ts:1 <= '2020-06-01 13:35:55+00:00' [as="?column?":4, outer=(1)]