github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/norm/testdata/rules/scalar (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 xy (x INT PRIMARY KEY, y INT) 7 ---- 8 9 # -------------------------------------------------- 10 # CommuteVar 11 # -------------------------------------------------- 12 13 # Put variables on both sides of comparison operator to avoid matching constant 14 # patterns. 15 norm expect=CommuteVar 16 SELECT 17 (1+i) = k AS r, 18 (2-k) <> i AS s, 19 (i+1) IS NOT DISTINCT FROM k AS t, 20 (i-1) IS DISTINCT FROM k AS u, 21 22 (i*2) + k AS v, 23 (i+2) * k AS w, 24 (i^2) & k AS x, 25 (i^2) | k AS y, 26 (i*i) # k AS z 27 FROM a 28 ---- 29 project 30 ├── columns: r:7 s:8 t:9!null u:10!null v:11 w:12 x:13 y:14 z:15 31 ├── scan a 32 │ ├── columns: k:1!null i:2 33 │ ├── key: (1) 34 │ └── fd: (1)-->(2) 35 └── projections 36 ├── k:1 = (i:2 + 1) [as=r:7, outer=(1,2)] 37 ├── i:2 != (2 - k:1) [as=s:8, outer=(1,2)] 38 ├── k:1 IS NOT DISTINCT FROM (i:2 + 1) [as=t:9, outer=(1,2)] 39 ├── k:1 IS DISTINCT FROM (i:2 - 1) [as=u:10, outer=(1,2)] 40 ├── k:1 + (i:2 * 2) [as=v:11, outer=(1,2)] 41 ├── k:1 * (i:2 + 2) [as=w:12, outer=(1,2)] 42 ├── k:1 & (i:2 ^ 2) [as=x:13, outer=(1,2)] 43 ├── k:1 | (i:2 ^ 2) [as=y:14, outer=(1,2)] 44 └── k:1 # (i:2 * i:2) [as=z:15, outer=(1,2)] 45 46 # -------------------------------------------------- 47 # CommuteConst 48 # -------------------------------------------------- 49 norm expect=CommuteConst 50 SELECT 51 (length('foo')+1) = (i+k) AS r, 52 length('bar') <> (i*2) AS s, 53 5 IS NOT DISTINCT FROM (1-k) AS t, 54 (10::decimal+1::int) IS DISTINCT FROM k AS u, 55 56 1 + f AS v, 57 (5*length('foo')) * (i*i) AS w, 58 (100 ^ 2) & (i+i) AS x, 59 length('foo')+1 | (i+i) AS y, 60 1-length('foo') # (k^2) AS z 61 FROM a 62 ---- 63 project 64 ├── columns: r:7 s:8 t:9!null u:10!null v:11 w:12 x:13 y:14 z:15!null 65 ├── scan a 66 │ ├── columns: k:1!null i:2 f:3 67 │ ├── key: (1) 68 │ └── fd: (1)-->(2,3) 69 └── projections 70 ├── (i:2 + k:1) = 4 [as=r:7, outer=(1,2)] 71 ├── (i:2 * 2) != 3 [as=s:8, outer=(2)] 72 ├── (1 - k:1) IS NOT DISTINCT FROM 5 [as=t:9, outer=(1)] 73 ├── k:1 IS DISTINCT FROM 11 [as=u:10, outer=(1)] 74 ├── f:3 + 1.0 [as=v:11, outer=(3)] 75 ├── (i:2 * i:2) * 15 [as=w:12, outer=(2)] 76 ├── (i:2 + i:2) & 10000 [as=x:13, outer=(2)] 77 ├── (i:2 + i:2) | 4 [as=y:14, outer=(2)] 78 └── (k:1 ^ 2) # -2 [as=z:15, outer=(1)] 79 80 # -------------------------------------------------- 81 # EliminateCoalesce 82 # -------------------------------------------------- 83 norm expect=EliminateCoalesce 84 SELECT COALESCE(i) FROM a 85 ---- 86 project 87 ├── columns: coalesce:7 88 ├── scan a 89 │ └── columns: i:2 90 └── projections 91 └── i:2 [as=coalesce:7, outer=(2)] 92 93 norm expect=EliminateCoalesce 94 SELECT COALESCE(NULL) FROM a 95 ---- 96 project 97 ├── columns: coalesce:7 98 ├── fd: ()-->(7) 99 ├── scan a 100 └── projections 101 └── NULL [as=coalesce:7] 102 103 # -------------------------------------------------- 104 # SimplifyCoalesce 105 # -------------------------------------------------- 106 107 norm expect=SimplifyCoalesce 108 SELECT COALESCE(NULL, 'foo', s) FROM a 109 ---- 110 project 111 ├── columns: coalesce:7!null 112 ├── fd: ()-->(7) 113 ├── scan a 114 └── projections 115 └── 'foo' [as=coalesce:7] 116 117 norm expect=SimplifyCoalesce 118 SELECT COALESCE(NULL, NULL, s, s || 'foo') FROM a 119 ---- 120 project 121 ├── columns: coalesce:7 122 ├── scan a 123 │ └── columns: s:4 124 └── projections 125 └── COALESCE(s:4, s:4 || 'foo') [as=coalesce:7, outer=(4)] 126 127 # Trailing null can't be removed. 128 norm 129 SELECT COALESCE(i, NULL, NULL) FROM a 130 ---- 131 project 132 ├── columns: coalesce:7 133 ├── scan a 134 │ └── columns: i:2 135 └── projections 136 └── COALESCE(i:2, NULL, NULL) [as=coalesce:7, outer=(2)] 137 138 norm expect=SimplifyCoalesce 139 SELECT COALESCE((1, 2, 3), (2, 3, 4)) FROM a 140 ---- 141 project 142 ├── columns: coalesce:7!null 143 ├── fd: ()-->(7) 144 ├── scan a 145 └── projections 146 └── (1, 2, 3) [as=coalesce:7] 147 148 149 # -------------------------------------------------- 150 # EliminateCast 151 # -------------------------------------------------- 152 norm expect=EliminateCast 153 SELECT 154 i::int, arr::int[], '[1, 2]'::jsonb::json, null::char(2)::bit, s::string::text 155 FROM a 156 ---- 157 project 158 ├── columns: i:7 arr:8 jsonb:9!null bit:10 s:11 159 ├── fd: ()-->(9,10) 160 ├── scan a 161 │ └── columns: a.i:2 a.s:4 a.arr:6 162 └── projections 163 ├── a.i:2 [as=i:7, outer=(2)] 164 ├── a.arr:6 [as=arr:8, outer=(6)] 165 ├── '[1, 2]' [as=jsonb:9] 166 ├── CAST(NULL AS BIT) [as=bit:10] 167 └── a.s:4 [as=s:11, outer=(4)] 168 169 # Shouldn't eliminate these casts. 170 norm 171 SELECT 172 i::float, 173 arr::decimal[], 174 s::json, 175 s::varchar(2), 176 i::smallint::int8, 177 s::char::varchar, 178 ARRAY[i, 2]::OIDVECTOR, 179 ARRAY[i, 2]::INT2VECTOR 180 FROM a 181 ---- 182 project 183 ├── columns: i:7 arr:8 s:9 s:10 i:11 s:12 array:13 array:14 184 ├── scan a 185 │ └── columns: a.i:2 a.s:4 a.arr:6 186 └── projections 187 ├── a.i:2::FLOAT8 [as=i:7, outer=(2)] 188 ├── a.arr:6::DECIMAL[] [as=arr:8, outer=(6)] 189 ├── a.s:4::JSONB [as=s:9, outer=(4)] 190 ├── a.s:4::VARCHAR(2) [as=s:10, outer=(4)] 191 ├── a.i:2::INT2::INT8 [as=i:11, outer=(2)] 192 ├── a.s:4::CHAR::VARCHAR [as=s:12, outer=(4)] 193 ├── ARRAY[a.i:2, 2]::OIDVECTOR [as=array:13, outer=(2)] 194 └── ARRAY[a.i:2, 2]::INT2VECTOR [as=array:14, outer=(2)] 195 196 # -------------------------------------------------- 197 # NormalizeInConst 198 # -------------------------------------------------- 199 norm expect=NormalizeInConst 200 SELECT i IN (2, 1, 1, null, 3, 4.00, 4.0, null, 3.0) AS r FROM a 201 ---- 202 project 203 ├── columns: r:7 204 ├── scan a 205 │ └── columns: i:2 206 └── projections 207 └── i:2 IN (NULL, 1, 2, 3, 4) [as=r:7, outer=(2)] 208 209 # Single value. 210 norm expect-not=NormalizeInConst 211 SELECT s NOT IN ('foo') AS r FROM a 212 ---- 213 project 214 ├── columns: r:7 215 ├── scan a 216 │ └── columns: s:4 217 └── projections 218 └── s:4 NOT IN ('foo',) [as=r:7, outer=(4)] 219 220 # Don't sort, since the list is not constant. 221 norm expect-not=NormalizeInConst 222 SELECT s NOT IN ('foo', s || 'foo', 'bar', length(s)::string, NULL) AS r FROM a 223 ---- 224 project 225 ├── columns: r:7 226 ├── immutable 227 ├── scan a 228 │ └── columns: s:4 229 └── projections 230 └── s:4 NOT IN ('foo', s:4 || 'foo', 'bar', length(s:4)::STRING, NULL) [as=r:7, outer=(4), immutable] 231 232 # Regression test #36031. 233 norm expect-not=NormalizeInConst 234 SELECT 235 true 236 IN ( 237 NULL, 238 NULL, 239 ( 240 '201.249.149.90/18':::INET::INET 241 & '97a7:3650:3dd8:d4e9:35fe:6cfb:a714:1c17/61':::INET::INET 242 )::INET 243 << 'e22f:2067:2ed2:7b07:b167:206f:f17b:5b7d/82':::INET::INET 244 ) 245 ---- 246 values 247 ├── columns: "?column?":1 248 ├── cardinality: [1 - 1] 249 ├── key: () 250 ├── fd: ()-->(1) 251 └── (true IN (NULL, NULL, ('201.249.149.90/18' & '97a7:3650:3dd8:d4e9:35fe:6cfb:a714:1c17/61') << 'e22f:2067:2ed2:7b07:b167:206f:f17b:5b7d/82'),) 252 253 # -------------------------------------------------- 254 # EliminateExistsZeroRows 255 # -------------------------------------------------- 256 257 norm expect=EliminateExistsZeroRows 258 SELECT EXISTS(SELECT * FROM (VALUES (1)) WHERE false) 259 ---- 260 values 261 ├── columns: exists:2!null 262 ├── cardinality: [1 - 1] 263 ├── key: () 264 ├── fd: ()-->(2) 265 └── (false,) 266 267 # -------------------------------------------------- 268 # EliminateExistsProject 269 # -------------------------------------------------- 270 norm expect=EliminateExistsProject 271 SELECT * FROM a WHERE EXISTS(SELECT i+1, i*k FROM a) 272 ---- 273 select 274 ├── columns: k:1!null i:2 f:3 s:4 j:5 arr:6 275 ├── key: (1) 276 ├── fd: (1)-->(2-6) 277 ├── scan a 278 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 arr:6 279 │ ├── key: (1) 280 │ └── fd: (1)-->(2-6) 281 └── filters 282 └── exists [subquery] 283 └── limit 284 ├── columns: k:7!null i:8 285 ├── cardinality: [0 - 1] 286 ├── key: () 287 ├── fd: ()-->(7,8) 288 ├── scan a 289 │ ├── columns: k:7!null i:8 290 │ ├── key: (7) 291 │ ├── fd: (7)-->(8) 292 │ └── limit hint: 1.00 293 └── 1 294 295 # -------------------------------------------------- 296 # EliminateExistsGroupBy 297 # -------------------------------------------------- 298 299 # Scalar group by shouldn't get eliminated. 300 norm expect-not=EliminateExistsGroupBy 301 SELECT * FROM a WHERE EXISTS(SELECT max(s) FROM a WHERE False) 302 ---- 303 select 304 ├── columns: k:1!null i:2 f:3 s:4 j:5 arr:6 305 ├── key: (1) 306 ├── fd: (1)-->(2-6) 307 ├── scan a 308 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 arr:6 309 │ ├── key: (1) 310 │ └── fd: (1)-->(2-6) 311 └── filters 312 └── exists [subquery] 313 └── scalar-group-by 314 ├── columns: max:13 315 ├── cardinality: [1 - 1] 316 ├── key: () 317 ├── fd: ()-->(13) 318 ├── values 319 │ ├── columns: s:10!null 320 │ ├── cardinality: [0 - 0] 321 │ ├── key: () 322 │ └── fd: ()-->(10) 323 └── aggregations 324 └── max [as=max:13, outer=(10)] 325 └── s:10 326 327 norm expect=EliminateExistsGroupBy 328 SELECT * FROM a WHERE EXISTS(SELECT DISTINCT s FROM a) 329 ---- 330 select 331 ├── columns: k:1!null i:2 f:3 s:4 j:5 arr:6 332 ├── key: (1) 333 ├── fd: (1)-->(2-6) 334 ├── scan a 335 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 arr:6 336 │ ├── key: (1) 337 │ └── fd: (1)-->(2-6) 338 └── filters 339 └── exists [subquery] 340 └── limit 341 ├── columns: s:10 342 ├── cardinality: [0 - 1] 343 ├── key: () 344 ├── fd: ()-->(10) 345 ├── scan a 346 │ ├── columns: s:10 347 │ └── limit hint: 1.00 348 └── 1 349 350 norm expect=EliminateExistsGroupBy 351 SELECT * FROM a WHERE EXISTS(SELECT DISTINCT ON (i) s FROM a) 352 ---- 353 select 354 ├── columns: k:1!null i:2 f:3 s:4 j:5 arr:6 355 ├── key: (1) 356 ├── fd: (1)-->(2-6) 357 ├── scan a 358 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 arr:6 359 │ ├── key: (1) 360 │ └── fd: (1)-->(2-6) 361 └── filters 362 └── exists [subquery] 363 └── limit 364 ├── columns: i:8 s:10 365 ├── cardinality: [0 - 1] 366 ├── key: () 367 ├── fd: ()-->(8,10) 368 ├── scan a 369 │ ├── columns: i:8 s:10 370 │ └── limit hint: 1.00 371 └── 1 372 373 # Ensure that EliminateExistsGroupBy does not activate for an EnsureDistinctOn. 374 norm expect-not=EliminateExistsGroupBy 375 SELECT * FROM a WHERE EXISTS(SELECT (SELECT y FROM xy WHERE y=k) FROM a) 376 ---- 377 select 378 ├── columns: k:1!null i:2 f:3 s:4 j:5 arr:6 379 ├── key: (1) 380 ├── fd: (1)-->(2-6) 381 ├── scan a 382 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 arr:6 383 │ ├── key: (1) 384 │ └── fd: (1)-->(2-6) 385 └── filters 386 └── exists [subquery] 387 └── limit 388 ├── columns: k:7!null xy.y:14 389 ├── cardinality: [0 - 1] 390 ├── key: () 391 ├── fd: ()-->(7,14) 392 ├── ensure-distinct-on 393 │ ├── columns: k:7!null xy.y:14 394 │ ├── grouping columns: k:7!null 395 │ ├── error: "more than one row returned by a subquery used as an expression" 396 │ ├── key: (7) 397 │ ├── fd: (7)-->(14) 398 │ ├── limit hint: 1.00 399 │ ├── left-join (hash) 400 │ │ ├── columns: k:7!null xy.y:14 401 │ │ ├── scan a 402 │ │ │ ├── columns: k:7!null 403 │ │ │ └── key: (7) 404 │ │ ├── scan xy 405 │ │ │ └── columns: xy.y:14 406 │ │ └── filters 407 │ │ └── xy.y:14 = k:7 [outer=(7,14), constraints=(/7: (/NULL - ]; /14: (/NULL - ]), fd=(7)==(14), (14)==(7)] 408 │ └── aggregations 409 │ └── const-agg [as=xy.y:14, outer=(14)] 410 │ └── xy.y:14 411 └── 1 412 413 # -------------------------------------------------- 414 # EliminateExistsGroupBy + EliminateExistsProject 415 # -------------------------------------------------- 416 norm expect=(EliminateExistsGroupBy,EliminateExistsProject) 417 SELECT * FROM a WHERE EXISTS(SELECT max(s) FROM a GROUP BY i) 418 ---- 419 select 420 ├── columns: k:1!null i:2 f:3 s:4 j:5 arr:6 421 ├── key: (1) 422 ├── fd: (1)-->(2-6) 423 ├── scan a 424 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 arr:6 425 │ ├── key: (1) 426 │ └── fd: (1)-->(2-6) 427 └── filters 428 └── exists [subquery] 429 └── limit 430 ├── columns: i:8 s:10 431 ├── cardinality: [0 - 1] 432 ├── key: () 433 ├── fd: ()-->(8,10) 434 ├── scan a 435 │ ├── columns: i:8 s:10 436 │ └── limit hint: 1.00 437 └── 1 438 439 # -------------------------------------------------- 440 # IntroduceExistsLimit 441 # -------------------------------------------------- 442 norm expect=IntroduceExistsLimit 443 SELECT * FROM a WHERE EXISTS(SELECT i FROM a) 444 ---- 445 select 446 ├── columns: k:1!null i:2 f:3 s:4 j:5 arr:6 447 ├── key: (1) 448 ├── fd: (1)-->(2-6) 449 ├── scan a 450 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 arr:6 451 │ ├── key: (1) 452 │ └── fd: (1)-->(2-6) 453 └── filters 454 └── exists [subquery] 455 └── limit 456 ├── columns: i:8 457 ├── cardinality: [0 - 1] 458 ├── key: () 459 ├── fd: ()-->(8) 460 ├── scan a 461 │ ├── columns: i:8 462 │ └── limit hint: 1.00 463 └── 1 464 465 # Don't introduce a limit on correlated subqueries (when HasOuterCols is true). 466 norm expect-not=IntroduceExistsLimit 467 SELECT * FROM a a1 WHERE EXISTS(SELECT i FROM a a2 where a1.i = a2.i) 468 ---- 469 semi-join (hash) 470 ├── columns: k:1!null i:2 f:3 s:4 j:5 arr:6 471 ├── key: (1) 472 ├── fd: (1)-->(2-6) 473 ├── scan a1 474 │ ├── columns: a1.k:1!null a1.i:2 a1.f:3 a1.s:4 a1.j:5 a1.arr:6 475 │ ├── key: (1) 476 │ └── fd: (1)-->(2-6) 477 ├── scan a2 478 │ └── columns: a2.i:8 479 └── filters 480 └── a1.i:2 = a2.i:8 [outer=(2,8), constraints=(/2: (/NULL - ]; /8: (/NULL - ]), fd=(2)==(8), (8)==(2)] 481 482 # Don't introduce a limit when the subquery has one row. 483 norm expect-not=IntroduceExistsLimit 484 SELECT * FROM a WHERE EXISTS(SELECT * FROM (VALUES (1))) 485 ---- 486 select 487 ├── columns: k:1!null i:2 f:3 s:4 j:5 arr:6 488 ├── key: (1) 489 ├── fd: (1)-->(2-6) 490 ├── scan a 491 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 arr:6 492 │ ├── key: (1) 493 │ └── fd: (1)-->(2-6) 494 └── filters 495 └── exists [subquery] 496 └── values 497 ├── columns: column1:7!null 498 ├── cardinality: [1 - 1] 499 ├── key: () 500 ├── fd: ()-->(7) 501 └── (1,) 502 503 # -------------------------------------------------- 504 # EliminateExistsLimit 505 # -------------------------------------------------- 506 norm expect=EliminateExistsLimit 507 SELECT * FROM a a1 WHERE EXISTS(SELECT i FROM a a2 where a1.i = a2.i LIMIT 1) 508 ---- 509 semi-join (hash) 510 ├── columns: k:1!null i:2 f:3 s:4 j:5 arr:6 511 ├── key: (1) 512 ├── fd: (1)-->(2-6) 513 ├── scan a1 514 │ ├── columns: a1.k:1!null a1.i:2 a1.f:3 a1.s:4 a1.j:5 a1.arr:6 515 │ ├── key: (1) 516 │ └── fd: (1)-->(2-6) 517 ├── scan a2 518 │ └── columns: a2.i:8 519 └── filters 520 └── a1.i:2 = a2.i:8 [outer=(2,8), constraints=(/2: (/NULL - ]; /8: (/NULL - ]), fd=(2)==(8), (8)==(2)] 521 522 norm expect=EliminateExistsLimit 523 SELECT * FROM a a1 WHERE NOT EXISTS(SELECT i FROM a a2 where a1.i = a2.i LIMIT 1) 524 ---- 525 anti-join (hash) 526 ├── columns: k:1!null i:2 f:3 s:4 j:5 arr:6 527 ├── key: (1) 528 ├── fd: (1)-->(2-6) 529 ├── scan a1 530 │ ├── columns: a1.k:1!null a1.i:2 a1.f:3 a1.s:4 a1.j:5 a1.arr:6 531 │ ├── key: (1) 532 │ └── fd: (1)-->(2-6) 533 ├── scan a2 534 │ └── columns: a2.i:8 535 └── filters 536 └── a1.i:2 = a2.i:8 [outer=(2,8), constraints=(/2: (/NULL - ]; /8: (/NULL - ]), fd=(2)==(8), (8)==(2)] 537 538 # Don't eliminate a non-positive limit. 539 norm expect-not=EliminateExistsLimit 540 SELECT * FROM a a1 WHERE EXISTS(SELECT i FROM a a2 where a1.i = a2.i LIMIT 0) 541 ---- 542 values 543 ├── columns: k:1!null i:2!null f:3!null s:4!null j:5!null arr:6!null 544 ├── cardinality: [0 - 0] 545 ├── key: () 546 └── fd: ()-->(1-6) 547 548 # Don't eliminate a limit from a non-correlated subquery. 549 norm expect-not=EliminateExistsLimit 550 SELECT * FROM a WHERE EXISTS(SELECT * FROM a LIMIT 1) 551 ---- 552 select 553 ├── columns: k:1!null i:2 f:3 s:4 j:5 arr:6 554 ├── key: (1) 555 ├── fd: (1)-->(2-6) 556 ├── scan a 557 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 arr:6 558 │ ├── key: (1) 559 │ └── fd: (1)-->(2-6) 560 └── filters 561 └── exists [subquery] 562 └── limit 563 ├── columns: k:7!null i:8 f:9 s:10 j:11 arr:12 564 ├── cardinality: [0 - 1] 565 ├── key: () 566 ├── fd: ()-->(7-12) 567 ├── scan a 568 │ ├── columns: k:7!null i:8 f:9 s:10 j:11 arr:12 569 │ ├── key: (7) 570 │ ├── fd: (7)-->(8-12) 571 │ └── limit hint: 1.00 572 └── 1 573 574 # -------------------------------------------------- 575 # NormalizeJSONFieldAccess 576 # -------------------------------------------------- 577 norm expect=NormalizeJSONFieldAccess 578 SELECT * FROM a WHERE j->'a' = '"b"'::JSON 579 ---- 580 select 581 ├── columns: k:1!null i:2 f:3 s:4 j:5 arr:6 582 ├── key: (1) 583 ├── fd: (1)-->(2-6) 584 ├── scan a 585 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 arr:6 586 │ ├── key: (1) 587 │ └── fd: (1)-->(2-6) 588 └── filters 589 └── j:5 @> '{"a": "b"}' [outer=(5)] 590 591 norm expect=NormalizeJSONFieldAccess 592 SELECT * FROM a WHERE j->'a'->'x' = '"b"'::JSON 593 ---- 594 select 595 ├── columns: k:1!null i:2 f:3 s:4 j:5 arr:6 596 ├── key: (1) 597 ├── fd: (1)-->(2-6) 598 ├── scan a 599 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 arr:6 600 │ ├── key: (1) 601 │ └── fd: (1)-->(2-6) 602 └── filters 603 └── j:5 @> '{"a": {"x": "b"}}' [outer=(5)] 604 605 # The transformation is not valid in this case. 606 norm expect-not=NormalizeJSONFieldAccess 607 SELECT * FROM a WHERE j->2 = '"b"'::JSON 608 ---- 609 select 610 ├── columns: k:1!null i:2 f:3 s:4 j:5 arr:6 611 ├── key: (1) 612 ├── fd: (1)-->(2-6) 613 ├── scan a 614 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 arr:6 615 │ ├── key: (1) 616 │ └── fd: (1)-->(2-6) 617 └── filters 618 └── (j:5->2) = '"b"' [outer=(5)] 619 620 # The transformation is not valid in this case, since j->'a' could be an array. 621 norm expect-not=NormalizeJSONFieldAccess 622 SELECT * FROM a WHERE j->'a' @> '"b"'::JSON 623 ---- 624 select 625 ├── columns: k:1!null i:2 f:3 s:4 j:5 arr:6 626 ├── key: (1) 627 ├── fd: (1)-->(2-6) 628 ├── scan a 629 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 arr:6 630 │ ├── key: (1) 631 │ └── fd: (1)-->(2-6) 632 └── filters 633 └── (j:5->'a') @> '"b"' [outer=(5)] 634 635 # The transformation is not valid in this case, since containment doesn't imply 636 # equality for non-scalars. 637 norm 638 SELECT j->'a' = '["b"]'::JSON, j->'a' = '{"b": "c"}'::JSON FROM a 639 ---- 640 project 641 ├── columns: "?column?":7 "?column?":8 642 ├── scan a 643 │ └── columns: j:5 644 └── projections 645 ├── (j:5->'a') = '["b"]' [as="?column?":7, outer=(5)] 646 └── (j:5->'a') = '{"b": "c"}' [as="?column?":8, outer=(5)] 647 648 # -------------------------------------------------- 649 # NormalizeJSONContains 650 # -------------------------------------------------- 651 652 norm expect=NormalizeJSONContains 653 SELECT * FROM a WHERE j->'a' @> '{"x": "b"}'::JSON 654 ---- 655 select 656 ├── columns: k:1!null i:2 f:3 s:4 j:5 arr:6 657 ├── key: (1) 658 ├── fd: (1)-->(2-6) 659 ├── scan a 660 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 arr:6 661 │ ├── key: (1) 662 │ └── fd: (1)-->(2-6) 663 └── filters 664 └── j:5 @> '{"a": {"x": "b"}}' [outer=(5)] 665 666 # -------------------------------------------------- 667 # SimplifyCaseWhenConstValue 668 # -------------------------------------------------- 669 670 norm expect=SimplifyCaseWhenConstValue 671 SELECT CASE 1 WHEN 1 THEN 'one' END 672 ---- 673 values 674 ├── columns: case:1!null 675 ├── cardinality: [1 - 1] 676 ├── key: () 677 ├── fd: ()-->(1) 678 └── ('one',) 679 680 norm expect=SimplifyCaseWhenConstValue 681 SELECT CASE WHEN 1 = 1 THEN 'one' END 682 ---- 683 values 684 ├── columns: case:1!null 685 ├── cardinality: [1 - 1] 686 ├── key: () 687 ├── fd: ()-->(1) 688 └── ('one',) 689 690 norm expect=SimplifyCaseWhenConstValue 691 SELECT CASE false WHEN 0 = 1 THEN 'one' END 692 ---- 693 values 694 ├── columns: case:1!null 695 ├── cardinality: [1 - 1] 696 ├── key: () 697 ├── fd: ()-->(1) 698 └── ('one',) 699 700 norm expect=SimplifyCaseWhenConstValue 701 SELECT CASE 1 WHEN 2 THEN 'one' END 702 ---- 703 values 704 ├── columns: case:1 705 ├── cardinality: [1 - 1] 706 ├── key: () 707 ├── fd: ()-->(1) 708 └── (NULL,) 709 710 norm expect=SimplifyCaseWhenConstValue 711 SELECT CASE 1 WHEN 2 THEN 'one' ELSE NULL END 712 ---- 713 values 714 ├── columns: case:1 715 ├── cardinality: [1 - 1] 716 ├── key: () 717 ├── fd: ()-->(1) 718 └── (NULL,) 719 720 # Regression test for #34930. 721 norm expect=SimplifyCaseWhenConstValue 722 SELECT 723 CASE 724 WHEN true THEN NULL 725 ELSE -0.41697856420581636 726 END 727 - CASE WHEN NULL THEN 1.4034371360919229 ELSE ln(NULL) END 728 ---- 729 values 730 ├── columns: "?column?":1 731 ├── cardinality: [1 - 1] 732 ├── key: () 733 ├── fd: ()-->(1) 734 └── (NULL,) 735 736 # Regression test for #35246. 737 norm expect=SimplifyCaseWhenConstValue 738 SELECT 739 CASE WHEN true THEN NULL ELSE 'foo' END || 740 CASE WHEN true THEN NULL ELSE 'bar' END 741 ---- 742 values 743 ├── columns: "?column?":1 744 ├── cardinality: [1 - 1] 745 ├── key: () 746 ├── fd: ()-->(1) 747 └── (NULL,) 748 749 # Verify that a true condition does not remove non-constant expressions 750 # proceeding it. 751 norm expect=SimplifyCaseWhenConstValue 752 SELECT 753 CASE 1 754 WHEN k THEN 'one' 755 WHEN 1 THEN 'two' 756 WHEN 1 THEN 'three' 757 ELSE 'four' 758 END 759 FROM 760 a 761 ---- 762 project 763 ├── columns: case:7!null 764 ├── scan a 765 │ ├── columns: k:1!null 766 │ └── key: (1) 767 └── projections 768 └── CASE 1 WHEN k:1 THEN 'one' ELSE 'two' END [as=case:7, outer=(1)] 769 770 norm expect=SimplifyCaseWhenConstValue 771 SELECT 772 CASE WHEN k = 1 THEN 'one' WHEN true THEN 'two' END 773 FROM 774 a 775 ---- 776 project 777 ├── columns: case:7!null 778 ├── scan a 779 │ ├── columns: k:1!null 780 │ └── key: (1) 781 └── projections 782 └── CASE WHEN k:1 = 1 THEN 'one' ELSE 'two' END [as=case:7, outer=(1)] 783 784 norm expect=SimplifyCaseWhenConstValue 785 SELECT CASE 1 WHEN 2 THEN 'one' ELSE 'three' END 786 ---- 787 values 788 ├── columns: case:1!null 789 ├── cardinality: [1 - 1] 790 ├── key: () 791 ├── fd: ()-->(1) 792 └── ('three',) 793 794 norm expect=SimplifyCaseWhenConstValue 795 SELECT 796 CASE 1 797 WHEN 2 THEN 'one' 798 WHEN k THEN 'two' 799 WHEN 1 THEN 'three' 800 WHEN 1 THEN 'four' 801 END 802 FROM 803 a 804 ---- 805 project 806 ├── columns: case:7!null 807 ├── scan a 808 │ ├── columns: k:1!null 809 │ └── key: (1) 810 └── projections 811 └── CASE 1 WHEN k:1 THEN 'two' ELSE 'three' END [as=case:7, outer=(1)] 812 813 norm expect=SimplifyCaseWhenConstValue 814 SELECT 815 CASE 1 816 WHEN 2 THEN 'one' 817 WHEN 1 THEN 'three' 818 WHEN 1 THEN 'four' 819 ELSE 'five' 820 END 821 ---- 822 values 823 ├── columns: case:1!null 824 ├── cardinality: [1 - 1] 825 ├── key: () 826 ├── fd: ()-->(1) 827 └── ('three',) 828 829 norm expect=SimplifyCaseWhenConstValue 830 SELECT 831 CASE NULL 832 WHEN true THEN 'one' 833 WHEN false THEN 'two' 834 WHEN NULL THEN 'three' 835 ELSE 'four' 836 END 837 ---- 838 values 839 ├── columns: case:1!null 840 ├── cardinality: [1 - 1] 841 ├── key: () 842 ├── fd: ()-->(1) 843 └── ('four',) 844 845 norm expect=SimplifyCaseWhenConstValue 846 SELECT CASE WHEN false THEN 'one' WHEN true THEN 'two' END 847 ---- 848 values 849 ├── columns: case:1!null 850 ├── cardinality: [1 - 1] 851 ├── key: () 852 ├── fd: ()-->(1) 853 └── ('two',) 854 855 # -------------------------------------------------- 856 # UnifyComparisonTypes 857 # -------------------------------------------------- 858 859 exec-ddl 860 CREATE TABLE e 861 ( 862 k INT PRIMARY KEY, 863 i INT, 864 t TIMESTAMP, 865 tz TIMESTAMPTZ, 866 d DATE, 867 INDEX (i), 868 INDEX (t), 869 INDEX (tz), 870 INDEX (d) 871 ) 872 ---- 873 874 ## -------------------------------------------------- 875 ## INT / FLOAT / DECIMAL 876 ## -------------------------------------------------- 877 878 # Compare how we can generate spans with and without the rule enabled. 879 norm expect=UnifyComparisonTypes 880 SELECT * FROM e WHERE k > '1.0'::FLOAT 881 ---- 882 select 883 ├── columns: k:1!null i:2 t:3 tz:4 d:5 884 ├── key: (1) 885 ├── fd: (1)-->(2-5) 886 ├── scan e 887 │ ├── columns: k:1!null i:2 t:3 tz:4 d:5 888 │ ├── key: (1) 889 │ └── fd: (1)-->(2-5) 890 └── filters 891 └── k:1 > 1 [outer=(1), constraints=(/1: [/2 - ]; tight)] 892 893 norm disable=UnifyComparisonTypes 894 SELECT * FROM e WHERE k > '1.0'::FLOAT 895 ---- 896 select 897 ├── columns: k:1!null i:2 t:3 tz:4 d:5 898 ├── key: (1) 899 ├── fd: (1)-->(2-5) 900 ├── scan e 901 │ ├── columns: k:1!null i:2 t:3 tz:4 d:5 902 │ ├── key: (1) 903 │ └── fd: (1)-->(2-5) 904 └── filters 905 └── k:1 > 1.0 [outer=(1), constraints=(/1: (/NULL - ])] 906 907 # Ensure the rest of normalization does its work and we move things around appropriately. 908 norm expect=UnifyComparisonTypes 909 SELECT * FROM e WHERE '1.0'::FLOAT > k 910 ---- 911 select 912 ├── columns: k:1!null i:2 t:3 tz:4 d:5 913 ├── key: (1) 914 ├── fd: (1)-->(2-5) 915 ├── scan e 916 │ ├── columns: k:1!null i:2 t:3 tz:4 d:5 917 │ ├── key: (1) 918 │ └── fd: (1)-->(2-5) 919 └── filters 920 └── k:1 < 1 [outer=(1), constraints=(/1: (/NULL - /0]; tight)] 921 922 norm expect=UnifyComparisonTypes 923 SELECT * FROM e WHERE k - 1 = 2::DECIMAL 924 ---- 925 select 926 ├── columns: k:1!null i:2 t:3 tz:4 d:5 927 ├── cardinality: [0 - 1] 928 ├── key: () 929 ├── fd: ()-->(1-5) 930 ├── scan e 931 │ ├── columns: k:1!null i:2 t:3 tz:4 d:5 932 │ ├── key: (1) 933 │ └── fd: (1)-->(2-5) 934 └── filters 935 └── k:1 = 3 [outer=(1), constraints=(/1: [/3 - /3]; tight), fd=()-->(1)] 936 937 # TODO(justin): we should theoretically be able to generate constraints in this 938 # case. 939 norm expect-not=UnifyComparisonTypes 940 SELECT * FROM e WHERE k > '1.1'::FLOAT 941 ---- 942 select 943 ├── columns: k:1!null i:2 t:3 tz:4 d:5 944 ├── key: (1) 945 ├── fd: (1)-->(2-5) 946 ├── scan e 947 │ ├── columns: k:1!null i:2 t:3 tz:4 d:5 948 │ ├── key: (1) 949 │ └── fd: (1)-->(2-5) 950 └── filters 951 └── k:1 > 1.1 [outer=(1), constraints=(/1: (/NULL - ])] 952 953 # -0 can generate spans 954 norm expect=UnifyComparisonTypes 955 SELECT * FROM e WHERE k > '-0'::FLOAT 956 ---- 957 select 958 ├── columns: k:1!null i:2 t:3 tz:4 d:5 959 ├── key: (1) 960 ├── fd: (1)-->(2-5) 961 ├── scan e 962 │ ├── columns: k:1!null i:2 t:3 tz:4 d:5 963 │ ├── key: (1) 964 │ └── fd: (1)-->(2-5) 965 └── filters 966 └── k:1 > 0 [outer=(1), constraints=(/1: [/1 - ]; tight)] 967 968 # NaN cannot generate spans. 969 norm expect-not=UnifyComparisonTypes 970 SELECT * FROM e WHERE k > 'NaN'::FLOAT 971 ---- 972 select 973 ├── columns: k:1!null i:2 t:3 tz:4 d:5 974 ├── key: (1) 975 ├── fd: (1)-->(2-5) 976 ├── scan e 977 │ ├── columns: k:1!null i:2 t:3 tz:4 d:5 978 │ ├── key: (1) 979 │ └── fd: (1)-->(2-5) 980 └── filters 981 └── k:1 > NaN [outer=(1), constraints=(/1: (/NULL - ])] 982 983 # IS/IS NOT 984 # We do not do the unification here (the rule matches on Const and NULL is its 985 # own operator), but this is fine because when an explicit NULL is involved we 986 # can generate spans anyway. 987 norm expect-not=UnifyComparisonTypes format=show-all 988 SELECT k FROM e WHERE i IS NOT DISTINCT FROM NULL::FLOAT 989 ---- 990 project 991 ├── columns: k:1(int!null) 992 ├── stats: [rows=10] 993 ├── cost: 1080.14 994 ├── key: (1) 995 ├── prune: (1) 996 ├── interesting orderings: (+1) 997 └── select 998 ├── columns: t.public.e.k:1(int!null) t.public.e.i:2(int) 999 ├── stats: [rows=10, distinct(2)=1, null(2)=10] 1000 ├── cost: 1080.03 1001 ├── key: (1) 1002 ├── fd: ()-->(2) 1003 ├── prune: (1) 1004 ├── interesting orderings: (+1) (+2,+1) 1005 ├── scan t.public.e 1006 │ ├── columns: t.public.e.k:1(int!null) t.public.e.i:2(int) 1007 │ ├── stats: [rows=1000, distinct(1)=1000, null(1)=0, distinct(2)=100, null(2)=10] 1008 │ ├── cost: 1070.02 1009 │ ├── key: (1) 1010 │ ├── fd: (1)-->(2) 1011 │ ├── prune: (1,2) 1012 │ └── interesting orderings: (+1) (+2,+1) 1013 └── filters 1014 └── is [type=bool, outer=(2), constraints=(/2: [/NULL - /NULL]; tight), fd=()-->(2)] 1015 ├── variable: t.public.e.i:2 [type=int] 1016 └── null [type=float] 1017 1018 norm expect-not=UnifyComparisonTypes format=show-all 1019 SELECT k FROM e WHERE i IS DISTINCT FROM NULL::FLOAT 1020 ---- 1021 project 1022 ├── columns: k:1(int!null) 1023 ├── stats: [rows=990] 1024 ├── cost: 1089.94 1025 ├── key: (1) 1026 ├── prune: (1) 1027 ├── interesting orderings: (+1) 1028 └── select 1029 ├── columns: t.public.e.k:1(int!null) t.public.e.i:2(int!null) 1030 ├── stats: [rows=990, distinct(2)=100, null(2)=0] 1031 ├── cost: 1080.03 1032 ├── key: (1) 1033 ├── fd: (1)-->(2) 1034 ├── prune: (1) 1035 ├── interesting orderings: (+1) (+2,+1) 1036 ├── scan t.public.e 1037 │ ├── columns: t.public.e.k:1(int!null) t.public.e.i:2(int) 1038 │ ├── stats: [rows=1000, distinct(1)=1000, null(1)=0, distinct(2)=100, null(2)=10] 1039 │ ├── cost: 1070.02 1040 │ ├── key: (1) 1041 │ ├── fd: (1)-->(2) 1042 │ ├── prune: (1,2) 1043 │ └── interesting orderings: (+1) (+2,+1) 1044 └── filters 1045 └── is-not [type=bool, outer=(2), constraints=(/2: (/NULL - ]; tight)] 1046 ├── variable: t.public.e.i:2 [type=int] 1047 └── null [type=float] 1048 1049 norm expect=UnifyComparisonTypes 1050 SELECT * FROM e WHERE k IS NOT DISTINCT FROM '1.0'::FLOAT 1051 ---- 1052 select 1053 ├── columns: k:1!null i:2 t:3 tz:4 d:5 1054 ├── cardinality: [0 - 1] 1055 ├── key: () 1056 ├── fd: ()-->(1-5) 1057 ├── scan e 1058 │ ├── columns: k:1!null i:2 t:3 tz:4 d:5 1059 │ ├── key: (1) 1060 │ └── fd: (1)-->(2-5) 1061 └── filters 1062 └── k:1 IS NOT DISTINCT FROM 1 [outer=(1), constraints=(/1: [/1 - /1]; tight), fd=()-->(1)] 1063 1064 ## -------------------------------------------------- 1065 ## TIMESTAMP / TIMESTAMPTZ / DATE 1066 ## -------------------------------------------------- 1067 1068 norm disable=UnifyComparisonTypes 1069 SELECT k FROM e WHERE tz > '2017-11-12 07:35:01+00:00'::TIMESTAMP 1070 ---- 1071 project 1072 ├── columns: k:1!null 1073 ├── key: (1) 1074 └── select 1075 ├── columns: k:1!null tz:4!null 1076 ├── key: (1) 1077 ├── fd: (1)-->(4) 1078 ├── scan e 1079 │ ├── columns: k:1!null tz:4 1080 │ ├── key: (1) 1081 │ └── fd: (1)-->(4) 1082 └── filters 1083 └── tz:4 > '2017-11-12 07:35:01+00:00' [outer=(4), constraints=(/4: (/NULL - ])] 1084 1085 norm expect=UnifyComparisonTypes 1086 SELECT k FROM e WHERE tz > '2017-11-12 07:35:01+00:00'::TIMESTAMP 1087 ---- 1088 project 1089 ├── columns: k:1!null 1090 ├── key: (1) 1091 └── select 1092 ├── columns: k:1!null tz:4!null 1093 ├── key: (1) 1094 ├── fd: (1)-->(4) 1095 ├── scan e 1096 │ ├── columns: k:1!null tz:4 1097 │ ├── key: (1) 1098 │ └── fd: (1)-->(4) 1099 └── filters 1100 └── tz:4 > '2017-11-12 07:35:01+00:00' [outer=(4), constraints=(/4: [/'2017-11-12 07:35:01.000001+00:00' - ]; tight)] 1101 1102 # Common case arising from constant folding: the folding here results in a 1103 # TIMESTAMP, but we would still like to be able to generate DATE spans. 1104 norm 1105 SELECT k FROM e WHERE d > '2018-07-01' AND d < '2018-07-01'::DATE + '1w'::INTERVAL 1106 ---- 1107 project 1108 ├── columns: k:1!null 1109 ├── key: (1) 1110 └── select 1111 ├── columns: k:1!null d:5!null 1112 ├── key: (1) 1113 ├── fd: (1)-->(5) 1114 ├── scan e 1115 │ ├── columns: k:1!null d:5 1116 │ ├── key: (1) 1117 │ └── fd: (1)-->(5) 1118 └── filters 1119 └── (d:5 > '2018-07-01') AND (d:5 < '2018-07-08') [outer=(5), constraints=(/5: [/'2018-07-02' - /'2018-07-07']; tight)] 1120 1121 # A case where we can theoretically generate spans, but do not. 1122 # TODO(justin): modify the logic to allow us to create spans in this case. 1123 norm 1124 SELECT k FROM e WHERE d > '2018-07-01' AND d < '2018-07-01'::DATE + '1w1s'::INTERVAL 1125 ---- 1126 project 1127 ├── columns: k:1!null 1128 ├── key: (1) 1129 └── select 1130 ├── columns: k:1!null d:5!null 1131 ├── key: (1) 1132 ├── fd: (1)-->(5) 1133 ├── scan e 1134 │ ├── columns: k:1!null d:5 1135 │ ├── key: (1) 1136 │ └── fd: (1)-->(5) 1137 └── filters 1138 ├── d:5 > '2018-07-01' [outer=(5), constraints=(/5: [/'2018-07-02' - ]; tight)] 1139 └── d:5 < '2018-07-08 00:00:01+00:00' [outer=(5), constraints=(/5: (/NULL - ])] 1140 1141 # NULL value. 1142 norm 1143 SELECT k FROM e WHERE tz > NULL::TIMESTAMP 1144 ---- 1145 values 1146 ├── columns: k:1!null 1147 ├── cardinality: [0 - 0] 1148 ├── key: () 1149 └── fd: ()-->(1) 1150 1151 # Working in concert with other norm rules 1152 norm 1153 SELECT k FROM e WHERE d - '1w'::INTERVAL > '2018-07-01'::DATE 1154 ---- 1155 project 1156 ├── columns: k:1!null 1157 ├── key: (1) 1158 └── select 1159 ├── columns: k:1!null d:5!null 1160 ├── key: (1) 1161 ├── fd: (1)-->(5) 1162 ├── scan e 1163 │ ├── columns: k:1!null d:5 1164 │ ├── key: (1) 1165 │ └── fd: (1)-->(5) 1166 └── filters 1167 └── d:5 > '2018-07-08' [outer=(5), constraints=(/5: [/'2018-07-09' - ]; tight)] 1168 1169 # -------------------------------------------------- 1170 # InlineAnyValuesSingleCol 1171 # -------------------------------------------------- 1172 1173 norm expect=InlineAnyValuesSingleCol 1174 SELECT k FROM a WHERE k IN (VALUES (1), (2), (3)) 1175 ---- 1176 select 1177 ├── columns: k:1!null 1178 ├── cardinality: [0 - 3] 1179 ├── key: (1) 1180 ├── scan a 1181 │ ├── columns: k:1!null 1182 │ └── key: (1) 1183 └── filters 1184 └── k:1 IN (1, 2, 3) [outer=(1), constraints=(/1: [/1 - /1] [/2 - /2] [/3 - /3]; tight)] 1185 1186 norm expect=InlineAnyValuesSingleCol 1187 SELECT k FROM a WHERE k IN (VALUES ((SELECT k*i FROM a)), (2), (3)) 1188 ---- 1189 select 1190 ├── columns: k:1!null 1191 ├── key: (1) 1192 ├── scan a 1193 │ ├── columns: k:1!null 1194 │ └── key: (1) 1195 └── filters 1196 └── in [outer=(1), subquery] 1197 ├── k:1 1198 └── tuple 1199 ├── subquery 1200 │ └── max1-row 1201 │ ├── columns: "?column?":13 1202 │ ├── error: "more than one row returned by a subquery used as an expression" 1203 │ ├── cardinality: [0 - 1] 1204 │ ├── key: () 1205 │ ├── fd: ()-->(13) 1206 │ └── project 1207 │ ├── columns: "?column?":13 1208 │ ├── scan a 1209 │ │ ├── columns: k:7!null i:8 1210 │ │ ├── key: (7) 1211 │ │ └── fd: (7)-->(8) 1212 │ └── projections 1213 │ └── k:7 * i:8 [as="?column?":13, outer=(7,8)] 1214 ├── 2 1215 └── 3 1216 1217 # -------------------------------------------------- 1218 # InlineAnyValuesMultiCol 1219 # -------------------------------------------------- 1220 1221 norm expect=InlineAnyValuesMultiCol 1222 SELECT k FROM a WHERE (k, i) IN (VALUES (1, 1), (2, 2), (3, 3)) 1223 ---- 1224 project 1225 ├── columns: k:1!null 1226 ├── cardinality: [0 - 3] 1227 ├── key: (1) 1228 └── select 1229 ├── columns: k:1!null i:2!null 1230 ├── cardinality: [0 - 3] 1231 ├── key: (1) 1232 ├── fd: (1)-->(2) 1233 ├── scan a 1234 │ ├── columns: k:1!null i:2 1235 │ ├── key: (1) 1236 │ └── fd: (1)-->(2) 1237 └── filters 1238 └── (k:1, i:2) IN ((1, 1), (2, 2), (3, 3)) [outer=(1,2), constraints=(/1/2: [/1/1 - /1/1] [/2/2 - /2/2] [/3/3 - /3/3]; /2: [/1 - /1] [/2 - /2] [/3 - /3]; tight)] 1239 1240 # The rule should not fire if the columns are not in the right order. 1241 norm expect-not=InlineAnyValuesMultiCol 1242 SELECT k FROM a WHERE (k, i) IN (SELECT b, a FROM (VALUES (1, 1), (2, 2), (3, 3)) AS v(a,b)) 1243 ---- 1244 project 1245 ├── columns: k:1!null 1246 ├── key: (1) 1247 └── semi-join (hash) 1248 ├── columns: k:1!null column10:10 1249 ├── key: (1) 1250 ├── fd: (1)-->(10) 1251 ├── project 1252 │ ├── columns: column10:10 k:1!null 1253 │ ├── key: (1) 1254 │ ├── fd: (1)-->(10) 1255 │ ├── scan a 1256 │ │ ├── columns: k:1!null i:2 1257 │ │ ├── key: (1) 1258 │ │ └── fd: (1)-->(2) 1259 │ └── projections 1260 │ └── (k:1, i:2) [as=column10:10, outer=(1,2)] 1261 ├── project 1262 │ ├── columns: column9:9!null 1263 │ ├── cardinality: [3 - 3] 1264 │ ├── values 1265 │ │ ├── columns: column1:7!null column2:8!null 1266 │ │ ├── cardinality: [3 - 3] 1267 │ │ ├── (1, 1) 1268 │ │ ├── (2, 2) 1269 │ │ └── (3, 3) 1270 │ └── projections 1271 │ └── (column2:8, column1:7) [as=column9:9, outer=(7,8)] 1272 └── filters 1273 └── column10:10 = column9:9 [outer=(9,10), constraints=(/9: (/NULL - ]; /10: (/NULL - ]), fd=(9)==(10), (10)==(9)] 1274 1275 # -------------------------------------------------- 1276 # SimplifyEqualsAnyTuple 1277 # -------------------------------------------------- 1278 1279 norm expect=SimplifyEqualsAnyTuple 1280 SELECT k FROM a WHERE k = ANY (1, 2, 3) 1281 ---- 1282 select 1283 ├── columns: k:1!null 1284 ├── cardinality: [0 - 3] 1285 ├── key: (1) 1286 ├── scan a 1287 │ ├── columns: k:1!null 1288 │ └── key: (1) 1289 └── filters 1290 └── k:1 IN (1, 2, 3) [outer=(1), constraints=(/1: [/1 - /1] [/2 - /2] [/3 - /3]; tight)] 1291 1292 norm expect=SimplifyEqualsAnyTuple 1293 SELECT k FROM a WHERE k = ANY () 1294 ---- 1295 values 1296 ├── columns: k:1!null 1297 ├── cardinality: [0 - 0] 1298 ├── key: () 1299 └── fd: ()-->(1) 1300 1301 # -------------------------------------------------- 1302 # SimplifyAnyScalarArray 1303 # -------------------------------------------------- 1304 1305 norm expect=SimplifyAnyScalarArray 1306 SELECT k FROM a WHERE k > ANY ARRAY[1, 2, 3] 1307 ---- 1308 select 1309 ├── columns: k:1!null 1310 ├── key: (1) 1311 ├── scan a 1312 │ ├── columns: k:1!null 1313 │ └── key: (1) 1314 └── filters 1315 └── k:1 > ANY (1, 2, 3) [outer=(1)] 1316 1317 norm expect-not=SimplifyAnyScalarArray 1318 SELECT k FROM a WHERE k > ANY ARRAY[1, 2, 3, i] 1319 ---- 1320 project 1321 ├── columns: k:1!null 1322 ├── key: (1) 1323 └── select 1324 ├── columns: k:1!null i:2 1325 ├── key: (1) 1326 ├── fd: (1)-->(2) 1327 ├── scan a 1328 │ ├── columns: k:1!null i:2 1329 │ ├── key: (1) 1330 │ └── fd: (1)-->(2) 1331 └── filters 1332 └── k:1 > ANY ARRAY[1, 2, 3, i:2] [outer=(1,2)] 1333 1334 norm expect=SimplifyAnyScalarArray 1335 SELECT k FROM a WHERE k > ANY ARRAY[]:::INT[] 1336 ---- 1337 select 1338 ├── columns: k:1!null 1339 ├── key: (1) 1340 ├── scan a 1341 │ ├── columns: k:1!null 1342 │ └── key: (1) 1343 └── filters 1344 └── k:1 > ANY () [outer=(1)] 1345 1346 # -------------------------------------------------- 1347 # SimplifyEqualsAnyTuple + SimplifyAnyScalarArray 1348 # -------------------------------------------------- 1349 1350 norm expect=(SimplifyAnyScalarArray,SimplifyEqualsAnyTuple) 1351 SELECT k FROM a WHERE k = ANY ARRAY[1, 2, 3] 1352 ---- 1353 select 1354 ├── columns: k:1!null 1355 ├── cardinality: [0 - 3] 1356 ├── key: (1) 1357 ├── scan a 1358 │ ├── columns: k:1!null 1359 │ └── key: (1) 1360 └── filters 1361 └── k:1 IN (1, 2, 3) [outer=(1), constraints=(/1: [/1 - /1] [/2 - /2] [/3 - /3]; tight)] 1362 1363 norm expect=(SimplifyAnyScalarArray,SimplifyEqualsAnyTuple) 1364 SELECT k FROM a WHERE k = ANY ARRAY[]:::INT[] 1365 ---- 1366 values 1367 ├── columns: k:1!null 1368 ├── cardinality: [0 - 0] 1369 ├── key: () 1370 └── fd: ()-->(1) 1371 1372 # TODO(justin): fold casts. 1373 norm 1374 SELECT k FROM a WHERE k = ANY '{1,2,3}'::INT[] 1375 ---- 1376 select 1377 ├── columns: k:1!null 1378 ├── cardinality: [0 - 3] 1379 ├── key: (1) 1380 ├── scan a 1381 │ ├── columns: k:1!null 1382 │ └── key: (1) 1383 └── filters 1384 └── k:1 IN (1, 2, 3) [outer=(1), constraints=(/1: [/1 - /1] [/2 - /2] [/3 - /3]; tight)] 1385 1386 # -------------------------------------------------- 1387 # FoldCollate 1388 # -------------------------------------------------- 1389 1390 norm expect=FoldCollate 1391 SELECT 'hello' COLLATE en_u_ks_level1 1392 ---- 1393 values 1394 ├── columns: "?column?":1!null 1395 ├── cardinality: [1 - 1] 1396 ├── key: () 1397 ├── fd: ()-->(1) 1398 └── ('hello' COLLATE en_u_ks_level1,) 1399 1400 norm expect=FoldCollate 1401 SELECT ('hello' COLLATE en_u_ks_level1) COLLATE en_u_ks_level1 1402 ---- 1403 values 1404 ├── columns: "?column?":1!null 1405 ├── cardinality: [1 - 1] 1406 ├── key: () 1407 ├── fd: ()-->(1) 1408 └── ('hello' COLLATE en_u_ks_level1,) 1409 1410 norm expect=FoldCollate 1411 SELECT ('hello' COLLATE en) COLLATE en_u_ks_level1 1412 ---- 1413 values 1414 ├── columns: "?column?":1!null 1415 ├── cardinality: [1 - 1] 1416 ├── key: () 1417 ├── fd: ()-->(1) 1418 └── ('hello' COLLATE en_u_ks_level1,) 1419 1420 norm expect-not=FoldCollate 1421 SELECT s COLLATE en_u_ks_level1 FROM a 1422 ---- 1423 project 1424 ├── columns: s:7 1425 ├── scan a 1426 │ └── columns: a.s:4 1427 └── projections 1428 └── a.s:4 COLLATE en_u_ks_level1 [as=s:7, outer=(4)] 1429 1430 # -------------------------------------------------- 1431 # NormalizeArrayFlattenToAgg 1432 # -------------------------------------------------- 1433 1434 norm expect=NormalizeArrayFlattenToAgg 1435 SELECT ARRAY(SELECT k FROM a WHERE a.k = b.k) FROM a AS b 1436 ---- 1437 project 1438 ├── columns: array:14 1439 ├── group-by 1440 │ ├── columns: b.k:1!null a.k:7!null array_agg:15!null 1441 │ ├── grouping columns: b.k:1!null 1442 │ ├── key: (7) 1443 │ ├── fd: (1)==(7), (7)==(1), (7)-->(15), (1)-->(7,15) 1444 │ ├── inner-join (hash) 1445 │ │ ├── columns: b.k:1!null a.k:7!null 1446 │ │ ├── key: (7) 1447 │ │ ├── fd: (1)==(7), (7)==(1) 1448 │ │ ├── scan b 1449 │ │ │ ├── columns: b.k:1!null 1450 │ │ │ └── key: (1) 1451 │ │ ├── scan a 1452 │ │ │ ├── columns: a.k:7!null 1453 │ │ │ └── key: (7) 1454 │ │ └── filters 1455 │ │ └── a.k:7 = b.k:1 [outer=(1,7), constraints=(/1: (/NULL - ]; /7: (/NULL - ]), fd=(1)==(7), (7)==(1)] 1456 │ └── aggregations 1457 │ ├── array-agg [as=array_agg:15, outer=(7)] 1458 │ │ └── a.k:7 1459 │ └── any-not-null-agg [as=a.k:7, outer=(7)] 1460 │ └── a.k:7 1461 └── projections 1462 └── COALESCE(CASE WHEN a.k:7 IS NOT NULL THEN array_agg:15 END, ARRAY[]) [as=array:14, outer=(7,15)] 1463 1464 # Ensure ordering is maintained. 1465 norm expect=NormalizeArrayFlattenToAgg 1466 SELECT ARRAY(SELECT k FROM a WHERE a.i = b.i ORDER BY a.k) FROM a AS b 1467 ---- 1468 project 1469 ├── columns: array:14 1470 ├── group-by 1471 │ ├── columns: b.k:1!null a.k:7 array_agg:15 1472 │ ├── grouping columns: b.k:1!null 1473 │ ├── internal-ordering: +7 opt(8) 1474 │ ├── key: (1) 1475 │ ├── fd: (1)-->(7,15) 1476 │ ├── sort 1477 │ │ ├── columns: b.k:1!null b.i:2 a.k:7 a.i:8 1478 │ │ ├── key: (1,7) 1479 │ │ ├── fd: (1)-->(2), (7)-->(8) 1480 │ │ ├── ordering: +7 opt(8) [actual: +7] 1481 │ │ └── left-join (hash) 1482 │ │ ├── columns: b.k:1!null b.i:2 a.k:7 a.i:8 1483 │ │ ├── key: (1,7) 1484 │ │ ├── fd: (1)-->(2), (7)-->(8) 1485 │ │ ├── scan b 1486 │ │ │ ├── columns: b.k:1!null b.i:2 1487 │ │ │ ├── key: (1) 1488 │ │ │ └── fd: (1)-->(2) 1489 │ │ ├── scan a 1490 │ │ │ ├── columns: a.k:7!null a.i:8 1491 │ │ │ ├── key: (7) 1492 │ │ │ └── fd: (7)-->(8) 1493 │ │ └── filters 1494 │ │ └── a.i:8 = b.i:2 [outer=(2,8), constraints=(/2: (/NULL - ]; /8: (/NULL - ]), fd=(2)==(8), (8)==(2)] 1495 │ └── aggregations 1496 │ ├── array-agg [as=array_agg:15, outer=(7)] 1497 │ │ └── a.k:7 1498 │ └── any-not-null-agg [as=a.k:7, outer=(7)] 1499 │ └── a.k:7 1500 └── projections 1501 └── COALESCE(CASE WHEN a.k:7 IS NOT NULL THEN array_agg:15 END, ARRAY[]) [as=array:14, outer=(7,15)] 1502 1503 norm expect=NormalizeArrayFlattenToAgg 1504 SELECT ARRAY(SELECT generate_series(1, a.k) ORDER BY 1 DESC) FROM a 1505 ---- 1506 project 1507 ├── columns: array:9 1508 ├── immutable, side-effects 1509 ├── group-by 1510 │ ├── columns: k:1!null canary:10 array_agg:11 1511 │ ├── grouping columns: k:1!null 1512 │ ├── internal-ordering: -7 1513 │ ├── immutable, side-effects 1514 │ ├── key: (1) 1515 │ ├── fd: (1)-->(10,11) 1516 │ ├── sort 1517 │ │ ├── columns: k:1!null generate_series:7 canary:10 1518 │ │ ├── immutable, side-effects 1519 │ │ ├── ordering: -7 1520 │ │ └── left-join-apply 1521 │ │ ├── columns: k:1!null generate_series:7 canary:10 1522 │ │ ├── immutable, side-effects 1523 │ │ ├── scan a 1524 │ │ │ ├── columns: k:1!null 1525 │ │ │ └── key: (1) 1526 │ │ ├── project 1527 │ │ │ ├── columns: canary:10!null generate_series:7 1528 │ │ │ ├── outer: (1) 1529 │ │ │ ├── immutable, side-effects 1530 │ │ │ ├── fd: ()-->(10) 1531 │ │ │ ├── project-set 1532 │ │ │ │ ├── columns: generate_series:7 1533 │ │ │ │ ├── outer: (1) 1534 │ │ │ │ ├── immutable, side-effects 1535 │ │ │ │ ├── values 1536 │ │ │ │ │ ├── cardinality: [1 - 1] 1537 │ │ │ │ │ ├── key: () 1538 │ │ │ │ │ └── () 1539 │ │ │ │ └── zip 1540 │ │ │ │ └── generate_series(1, k:1) [outer=(1), immutable, side-effects] 1541 │ │ │ └── projections 1542 │ │ │ └── true [as=canary:10] 1543 │ │ └── filters (true) 1544 │ └── aggregations 1545 │ ├── array-agg [as=array_agg:11, outer=(7)] 1546 │ │ └── generate_series:7 1547 │ └── any-not-null-agg [as=canary:10, outer=(10)] 1548 │ └── canary:10 1549 └── projections 1550 └── COALESCE(CASE WHEN canary:10 IS NOT NULL THEN array_agg:11 END, ARRAY[]) [as=array:9, outer=(10,11)] 1551 1552 # Uncorrelated ArrayFlatten inside a correlated ArrayFlatten. 1553 norm expect=NormalizeArrayFlattenToAgg 1554 SELECT ARRAY(SELECT ARRAY(SELECT k FROM a)[1] FROM a as b WHERE b.k = c.k) FROM a AS c 1555 ---- 1556 project 1557 ├── columns: array:21 1558 ├── group-by 1559 │ ├── columns: c.k:1!null canary:22!null array_agg:23 1560 │ ├── grouping columns: c.k:1!null 1561 │ ├── key: (1) 1562 │ ├── fd: ()-->(22), (1)-->(22,23) 1563 │ ├── inner-join (hash) 1564 │ │ ├── columns: c.k:1!null b.k:7!null array:19 canary:22!null 1565 │ │ ├── key: (7) 1566 │ │ ├── fd: ()-->(19,22), (1)==(7), (7)==(1) 1567 │ │ ├── scan c 1568 │ │ │ ├── columns: c.k:1!null 1569 │ │ │ └── key: (1) 1570 │ │ ├── project 1571 │ │ │ ├── columns: canary:22!null array:19 b.k:7!null 1572 │ │ │ ├── key: (7) 1573 │ │ │ ├── fd: ()-->(19,22) 1574 │ │ │ ├── scan b 1575 │ │ │ │ ├── columns: b.k:7!null 1576 │ │ │ │ └── key: (7) 1577 │ │ │ └── projections 1578 │ │ │ ├── true [as=canary:22] 1579 │ │ │ └── indirection [as=array:19, subquery] 1580 │ │ │ ├── array-flatten 1581 │ │ │ │ └── scan a 1582 │ │ │ │ ├── columns: k:13!null 1583 │ │ │ │ └── key: (13) 1584 │ │ │ └── 1 1585 │ │ └── filters 1586 │ │ └── b.k:7 = c.k:1 [outer=(1,7), constraints=(/1: (/NULL - ]; /7: (/NULL - ]), fd=(1)==(7), (7)==(1)] 1587 │ └── aggregations 1588 │ ├── array-agg [as=array_agg:23, outer=(19)] 1589 │ │ └── array:19 1590 │ └── any-not-null-agg [as=canary:22, outer=(22)] 1591 │ └── canary:22 1592 └── projections 1593 └── COALESCE(CASE WHEN canary:22 IS NOT NULL THEN array_agg:23 END, ARRAY[]) [as=array:21, outer=(22,23)] 1594 1595 # Correlated ArrayFlatten inside another correlated ArrayFlatten. 1596 norm expect=NormalizeArrayFlattenToAgg 1597 SELECT ARRAY(SELECT ARRAY(SELECT k FROM a WHERE a.k = b.k)[1] FROM a as b WHERE b.k = c.k) FROM a AS c 1598 ---- 1599 project 1600 ├── columns: array:23 1601 ├── group-by 1602 │ ├── columns: c.k:1!null canary:24 array_agg:25 1603 │ ├── grouping columns: c.k:1!null 1604 │ ├── key: (1) 1605 │ ├── fd: (1)-->(24,25) 1606 │ ├── left-join-apply 1607 │ │ ├── columns: c.k:1!null array:20 canary:24 1608 │ │ ├── key: (1) 1609 │ │ ├── fd: (1)-->(20,24) 1610 │ │ ├── scan c 1611 │ │ │ ├── columns: c.k:1!null 1612 │ │ │ └── key: (1) 1613 │ │ ├── project 1614 │ │ │ ├── columns: canary:24!null array:20 1615 │ │ │ ├── outer: (1) 1616 │ │ │ ├── cardinality: [0 - 1] 1617 │ │ │ ├── key: () 1618 │ │ │ ├── fd: ()-->(20,24) 1619 │ │ │ ├── group-by 1620 │ │ │ │ ├── columns: a.k:13!null array_agg:21!null 1621 │ │ │ │ ├── outer: (1) 1622 │ │ │ │ ├── cardinality: [0 - 1] 1623 │ │ │ │ ├── key: () 1624 │ │ │ │ ├── fd: ()-->(13,21) 1625 │ │ │ │ ├── inner-join (hash) 1626 │ │ │ │ │ ├── columns: b.k:7!null a.k:13!null 1627 │ │ │ │ │ ├── outer: (1) 1628 │ │ │ │ │ ├── cardinality: [0 - 1] 1629 │ │ │ │ │ ├── key: () 1630 │ │ │ │ │ ├── fd: ()-->(7,13) 1631 │ │ │ │ │ ├── select 1632 │ │ │ │ │ │ ├── columns: b.k:7!null 1633 │ │ │ │ │ │ ├── outer: (1) 1634 │ │ │ │ │ │ ├── cardinality: [0 - 1] 1635 │ │ │ │ │ │ ├── key: () 1636 │ │ │ │ │ │ ├── fd: ()-->(7) 1637 │ │ │ │ │ │ ├── scan b 1638 │ │ │ │ │ │ │ ├── columns: b.k:7!null 1639 │ │ │ │ │ │ │ └── key: (7) 1640 │ │ │ │ │ │ └── filters 1641 │ │ │ │ │ │ └── b.k:7 = c.k:1 [outer=(1,7), constraints=(/1: (/NULL - ]; /7: (/NULL - ]), fd=(1)==(7), (7)==(1)] 1642 │ │ │ │ │ ├── scan a 1643 │ │ │ │ │ │ ├── columns: a.k:13!null 1644 │ │ │ │ │ │ └── key: (13) 1645 │ │ │ │ │ └── filters 1646 │ │ │ │ │ └── a.k:13 = b.k:7 [outer=(7,13), constraints=(/7: (/NULL - ]; /13: (/NULL - ]), fd=(7)==(13), (13)==(7)] 1647 │ │ │ │ └── aggregations 1648 │ │ │ │ ├── array-agg [as=array_agg:21, outer=(13)] 1649 │ │ │ │ │ └── a.k:13 1650 │ │ │ │ └── any-not-null-agg [as=a.k:13, outer=(13)] 1651 │ │ │ │ └── a.k:13 1652 │ │ │ └── projections 1653 │ │ │ ├── true [as=canary:24] 1654 │ │ │ └── COALESCE(CASE WHEN a.k:13 IS NOT NULL THEN array_agg:21 END, ARRAY[])[1] [as=array:20, outer=(13,21)] 1655 │ │ └── filters (true) 1656 │ └── aggregations 1657 │ ├── array-agg [as=array_agg:25, outer=(20)] 1658 │ │ └── array:20 1659 │ └── any-not-null-agg [as=canary:24, outer=(24)] 1660 │ └── canary:24 1661 └── projections 1662 └── COALESCE(CASE WHEN canary:24 IS NOT NULL THEN array_agg:25 END, ARRAY[]) [as=array:23, outer=(24,25)] 1663 1664 # Shouldn't trigger if there's no correlation. 1665 norm expect-not=NormalizeArrayFlattenToAgg 1666 SELECT ARRAY(SELECT k FROM a) FROM a 1667 ---- 1668 project 1669 ├── columns: array:13 1670 ├── fd: ()-->(13) 1671 ├── scan a 1672 └── projections 1673 └── array-flatten [as=array:13, subquery] 1674 └── scan a 1675 ├── columns: k:7!null 1676 └── key: (7) 1677 1678 exec-ddl 1679 CREATE TABLE pg_class ( 1680 oid OID NULL, 1681 relname NAME NOT NULL, 1682 relnamespace OID NULL, 1683 reltype OID NULL, 1684 reloftype OID NULL, 1685 relowner OID NULL, 1686 relam OID NULL, 1687 relfilenode OID NULL, 1688 reltablespace OID NULL, 1689 relpages INT4 NULL, 1690 reltuples FLOAT4 NULL, 1691 relallvisible INT4 NULL, 1692 reltoastrelid OID NULL, 1693 relhasindex BOOL NULL, 1694 relisshared BOOL NULL, 1695 relpersistence CHAR NULL, 1696 relistemp BOOL NULL, 1697 relkind CHAR NULL, 1698 relnatts INT2 NULL, 1699 relchecks INT2 NULL, 1700 relhasoids BOOL NULL, 1701 relhaspkey BOOL NULL, 1702 relhasrules BOOL NULL, 1703 relhastriggers BOOL NULL, 1704 relhassubclass BOOL NULL, 1705 relfrozenxid INT8 NULL, 1706 relacl STRING[] NULL, 1707 reloptions STRING[] NULL 1708 ) 1709 ---- 1710 1711 exec-ddl 1712 CREATE TABLE pg_inherits ( 1713 inhrelid OID NULL, 1714 inhparent OID NULL, 1715 inhseqno INT4 NULL 1716 ) 1717 ---- 1718 1719 # Regression test for #38867. 1720 norm expect=NormalizeArrayFlattenToAgg 1721 SELECT ( 1722 SELECT 1723 ARRAY ( 1724 SELECT c.relname 1725 FROM pg_inherits AS i JOIN pg_class AS c ON c.oid = i.inhparent 1726 WHERE i.inhrelid = rel.oid 1727 ORDER BY inhseqno 1728 ) 1729 ) 1730 FROM pg_class AS rel 1731 ---- 1732 project 1733 ├── columns: array:66 1734 ├── inner-join-apply 1735 │ ├── columns: rel.oid:1 array_agg:63 array:64 1736 │ ├── scan rel 1737 │ │ └── columns: rel.oid:1 1738 │ ├── inner-join-apply 1739 │ │ ├── columns: array_agg:63 array:64 1740 │ │ ├── outer: (1) 1741 │ │ ├── cardinality: [1 - 1] 1742 │ │ ├── key: () 1743 │ │ ├── fd: ()-->(63,64) 1744 │ │ ├── project 1745 │ │ │ ├── columns: array_agg:63 1746 │ │ │ ├── outer: (1) 1747 │ │ │ ├── cardinality: [1 - 1] 1748 │ │ │ ├── key: () 1749 │ │ │ ├── fd: ()-->(63) 1750 │ │ │ ├── group-by 1751 │ │ │ │ ├── columns: inhrelid:30 array_agg:65 1752 │ │ │ │ ├── internal-ordering: +32 opt(30) 1753 │ │ │ │ ├── outer: (1) 1754 │ │ │ │ ├── cardinality: [1 - 1] 1755 │ │ │ │ ├── key: () 1756 │ │ │ │ ├── fd: ()-->(30,65) 1757 │ │ │ │ ├── sort 1758 │ │ │ │ │ ├── columns: inhrelid:30 inhparent:31 inhseqno:32 c.oid:34 c.relname:35 1759 │ │ │ │ │ ├── outer: (1) 1760 │ │ │ │ │ ├── cardinality: [1 - ] 1761 │ │ │ │ │ ├── fd: (31)==(34), (34)==(31) 1762 │ │ │ │ │ ├── ordering: +32 opt(30) [actual: +32] 1763 │ │ │ │ │ └── left-join (cross) 1764 │ │ │ │ │ ├── columns: inhrelid:30 inhparent:31 inhseqno:32 c.oid:34 c.relname:35 1765 │ │ │ │ │ ├── outer: (1) 1766 │ │ │ │ │ ├── cardinality: [1 - ] 1767 │ │ │ │ │ ├── fd: (31)==(34), (34)==(31) 1768 │ │ │ │ │ ├── values 1769 │ │ │ │ │ │ ├── cardinality: [1 - 1] 1770 │ │ │ │ │ │ ├── key: () 1771 │ │ │ │ │ │ └── () 1772 │ │ │ │ │ ├── inner-join (hash) 1773 │ │ │ │ │ │ ├── columns: inhrelid:30 inhparent:31!null inhseqno:32 c.oid:34!null c.relname:35!null 1774 │ │ │ │ │ │ ├── fd: (31)==(34), (34)==(31) 1775 │ │ │ │ │ │ ├── scan i 1776 │ │ │ │ │ │ │ └── columns: inhrelid:30 inhparent:31 inhseqno:32 1777 │ │ │ │ │ │ ├── scan c 1778 │ │ │ │ │ │ │ └── columns: c.oid:34 c.relname:35!null 1779 │ │ │ │ │ │ └── filters 1780 │ │ │ │ │ │ └── c.oid:34 = inhparent:31 [outer=(31,34), constraints=(/31: (/NULL - ]; /34: (/NULL - ]), fd=(31)==(34), (34)==(31)] 1781 │ │ │ │ │ └── filters 1782 │ │ │ │ │ └── inhrelid:30 = rel.oid:1 [outer=(1,30), constraints=(/1: (/NULL - ]; /30: (/NULL - ]), fd=(1)==(30), (30)==(1)] 1783 │ │ │ │ └── aggregations 1784 │ │ │ │ ├── array-agg [as=array_agg:65, outer=(35)] 1785 │ │ │ │ │ └── c.relname:35 1786 │ │ │ │ └── any-not-null-agg [as=inhrelid:30, outer=(30)] 1787 │ │ │ │ └── inhrelid:30 1788 │ │ │ └── projections 1789 │ │ │ └── CASE WHEN inhrelid:30 IS NOT NULL THEN array_agg:65 END [as=array_agg:63, outer=(30,65)] 1790 │ │ ├── values 1791 │ │ │ ├── columns: array:64 1792 │ │ │ ├── outer: (63) 1793 │ │ │ ├── cardinality: [1 - 1] 1794 │ │ │ ├── key: () 1795 │ │ │ ├── fd: ()-->(64) 1796 │ │ │ └── (COALESCE(array_agg:63, ARRAY[]),) 1797 │ │ └── filters (true) 1798 │ └── filters (true) 1799 └── projections 1800 └── array:64 [as=array:66, outer=(64)] 1801 1802 # -------------------------------------------------- 1803 # SimplifySameVarEqualities 1804 # -------------------------------------------------- 1805 1806 norm expect=(SimplifySameVarEqualities,SimplifySelectFilters) 1807 SELECT k FROM a WHERE k = k 1808 ---- 1809 scan a 1810 ├── columns: k:1!null 1811 └── key: (1) 1812 1813 norm expect=(SimplifySameVarEqualities,SimplifySelectFilters) 1814 SELECT k FROM a WHERE k >= k 1815 ---- 1816 scan a 1817 ├── columns: k:1!null 1818 └── key: (1) 1819 1820 norm expect=(SimplifySameVarEqualities,SimplifySelectFilters) 1821 SELECT k FROM a WHERE k <= k 1822 ---- 1823 scan a 1824 ├── columns: k:1!null 1825 └── key: (1) 1826 1827 norm expect=(SimplifySameVarEqualities,SimplifyJoinFilters) 1828 SELECT a.k FROM a FULL OUTER JOIN xy ON a.k = a.k 1829 ---- 1830 full-join (cross) 1831 ├── columns: k:1 1832 ├── scan a 1833 │ ├── columns: k:1!null 1834 │ └── key: (1) 1835 ├── scan xy 1836 └── filters 1837 └── k:1 IS DISTINCT FROM CAST(NULL AS INT8) [outer=(1), constraints=(/1: (/NULL - ]; tight)] 1838 1839 norm expect=(SimplifySameVarEqualities,SimplifyJoinFilters) 1840 SELECT a.k FROM a FULL OUTER JOIN xy ON a.k >= a.k 1841 ---- 1842 full-join (cross) 1843 ├── columns: k:1 1844 ├── scan a 1845 │ ├── columns: k:1!null 1846 │ └── key: (1) 1847 ├── scan xy 1848 └── filters 1849 └── k:1 IS DISTINCT FROM CAST(NULL AS INT8) [outer=(1), constraints=(/1: (/NULL - ]; tight)] 1850 1851 norm expect=(SimplifySameVarEqualities,SimplifyJoinFilters) 1852 SELECT a.k FROM a FULL OUTER JOIN xy ON a.k <= a.k 1853 ---- 1854 full-join (cross) 1855 ├── columns: k:1 1856 ├── scan a 1857 │ ├── columns: k:1!null 1858 │ └── key: (1) 1859 ├── scan xy 1860 └── filters 1861 └── k:1 IS DISTINCT FROM CAST(NULL AS INT8) [outer=(1), constraints=(/1: (/NULL - ]; tight)] 1862 1863 norm expect=SimplifySameVarEqualities 1864 SELECT k = k FROM a 1865 ---- 1866 project 1867 ├── columns: "?column?":7 1868 ├── scan a 1869 │ ├── columns: k:1!null 1870 │ └── key: (1) 1871 └── projections 1872 └── (k:1 IS DISTINCT FROM CAST(NULL AS INT8)) OR CAST(NULL AS BOOL) [as="?column?":7, outer=(1)] 1873 1874 # -------------------------------------------------- 1875 # SimplifySameVarInequalities 1876 # -------------------------------------------------- 1877 1878 norm expect=(SimplifySameVarInequalities,SimplifySelectFilters) 1879 SELECT k FROM a WHERE k != k 1880 ---- 1881 values 1882 ├── columns: k:1!null 1883 ├── cardinality: [0 - 0] 1884 ├── key: () 1885 └── fd: ()-->(1) 1886 1887 norm expect=(SimplifySameVarInequalities,SimplifySelectFilters) 1888 SELECT k FROM a WHERE k > k 1889 ---- 1890 values 1891 ├── columns: k:1!null 1892 ├── cardinality: [0 - 0] 1893 ├── key: () 1894 └── fd: ()-->(1) 1895 1896 norm expect=(SimplifySameVarInequalities,SimplifySelectFilters) 1897 SELECT k FROM a WHERE k < k 1898 ---- 1899 values 1900 ├── columns: k:1!null 1901 ├── cardinality: [0 - 0] 1902 ├── key: () 1903 └── fd: ()-->(1) 1904 1905 norm expect=(SimplifySameVarInequalities,SimplifyJoinFilters) 1906 SELECT a.k FROM a FULL OUTER JOIN xy ON a.k != a.k 1907 ---- 1908 full-join (cross) 1909 ├── columns: k:1 1910 ├── scan a 1911 │ ├── columns: k:1!null 1912 │ └── key: (1) 1913 ├── scan xy 1914 └── filters 1915 └── false 1916 1917 norm expect=(SimplifySameVarInequalities,SimplifyJoinFilters) 1918 SELECT a.k FROM a FULL OUTER JOIN xy ON a.k > a.k 1919 ---- 1920 full-join (cross) 1921 ├── columns: k:1 1922 ├── scan a 1923 │ ├── columns: k:1!null 1924 │ └── key: (1) 1925 ├── scan xy 1926 └── filters 1927 └── false 1928 1929 norm expect=(SimplifySameVarInequalities,SimplifyJoinFilters) 1930 SELECT a.k FROM a FULL OUTER JOIN xy ON a.k < a.k 1931 ---- 1932 full-join (cross) 1933 ├── columns: k:1 1934 ├── scan a 1935 │ ├── columns: k:1!null 1936 │ └── key: (1) 1937 ├── scan xy 1938 └── filters 1939 └── false 1940 1941 norm expect=SimplifySameVarInequalities 1942 SELECT k != k FROM a 1943 ---- 1944 project 1945 ├── columns: "?column?":7 1946 ├── scan a 1947 │ ├── columns: k:1!null 1948 │ └── key: (1) 1949 └── projections 1950 └── (k:1 IS NOT DISTINCT FROM CAST(NULL AS INT8)) AND CAST(NULL AS BOOL) [as="?column?":7, outer=(1)]