github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/norm/testdata/rules/groupby (about) 1 exec-ddl 2 CREATE TABLE a 3 ( 4 k INT PRIMARY KEY, 5 i INT NOT NULL, 6 f FLOAT, 7 s STRING NOT NULL, 8 j JSON, 9 UNIQUE INDEX si_idx (s DESC, i) STORING (j), 10 UNIQUE INDEX fi_idx (f, i) 11 ) 12 ---- 13 14 exec-ddl 15 CREATE TABLE xy 16 ( 17 x INT PRIMARY KEY, 18 y INT 19 ) 20 ---- 21 22 exec-ddl 23 CREATE TABLE fks 24 ( 25 k INT PRIMARY KEY, 26 v INT, 27 r1 INT NOT NULL REFERENCES xy(x), 28 r2 INT REFERENCES xy(x) 29 ) 30 ---- 31 32 exec-ddl 33 CREATE TABLE abc 34 ( 35 a INT, 36 b INT, 37 c INT, 38 PRIMARY KEY (a,b,c) 39 ) 40 ---- 41 42 exec-ddl 43 CREATE TABLE uvwz 44 ( 45 u INT NOT NULL, 46 v INT NOT NULL, 47 w INT NOT NULL, 48 z INT NOT NULL, 49 50 UNIQUE INDEX (u,v), 51 UNIQUE INDEX (v,w) 52 ) 53 ---- 54 55 exec-ddl 56 CREATE TABLE s ( 57 s STRING PRIMARY KEY 58 ) 59 ---- 60 61 exec-ddl 62 CREATE TABLE nullablecols ( 63 c1 INT, 64 c2 INT, 65 c3 INT, 66 UNIQUE (c1), 67 UNIQUE (c2,c3) 68 ) 69 ---- 70 71 exec-ddl 72 CREATE TABLE xyzbs 73 ( 74 x INT PRIMARY KEY, 75 y INT, 76 z INT NOT NULL, 77 b BOOL NOT NULL, 78 s TEXT, 79 INDEX (y), 80 INDEX (s) 81 ) 82 ---- 83 84 # -------------------------------------------------- 85 # ConvertGroupByToDistinct 86 # -------------------------------------------------- 87 norm expect=ConvertGroupByToDistinct 88 SELECT s, f FROM a GROUP BY s, f 89 ---- 90 distinct-on 91 ├── columns: s:4!null f:3 92 ├── grouping columns: f:3 s:4!null 93 ├── key: (3,4) 94 └── scan a 95 └── columns: f:3 s:4!null 96 97 # Group by not converted to DistinctOn because it has an aggregation. 98 norm expect-not=ConvertGroupByToDistinct 99 SELECT s, f, sum(f) FROM a GROUP BY s, f 100 ---- 101 group-by 102 ├── columns: s:4!null f:3 sum:6 103 ├── grouping columns: f:3 s:4!null 104 ├── key: (3,4) 105 ├── fd: (3,4)-->(6) 106 ├── scan a 107 │ └── columns: f:3 s:4!null 108 └── aggregations 109 └── sum [as=sum:6, outer=(3)] 110 └── f:3 111 112 # -------------------------------------------------- 113 # EliminateJoinUnderGroupByLeft 114 # -------------------------------------------------- 115 116 # Simple DistinctOn case with a LeftJoin on an equality between primary keys. 117 norm expect=EliminateJoinUnderGroupByLeft 118 SELECT DISTINCT ON (x) x, y FROM xy LEFT JOIN fks ON x=k 119 ---- 120 scan xy 121 ├── columns: x:1!null y:2 122 ├── key: (1) 123 └── fd: (1)-->(2) 124 125 # RightJoin case. The RightJoin is turned into a LeftJoin, so 126 # EliminateJoinUnderGroupByLeft matches it. 127 norm expect=EliminateJoinUnderGroupByLeft 128 SELECT DISTINCT ON (x) x, y FROM fks RIGHT JOIN xy ON x=k 129 ---- 130 scan xy 131 ├── columns: x:5!null y:6 132 ├── key: (5) 133 └── fd: (5)-->(6) 134 135 # InnerJoin case. The Values operator in the join guarantees cardinality of at 136 # least one, so rows from the left input are guaranteed to be included in the 137 # join at least once. 138 norm expect=EliminateJoinUnderGroupByLeft 139 SELECT k, max(r1) FROM fks INNER JOIN (SELECT * FROM (VALUES (1), (2)) f(t)) ON True GROUP BY k 140 ---- 141 group-by 142 ├── columns: k:1!null max:6!null 143 ├── grouping columns: k:1!null 144 ├── key: (1) 145 ├── fd: (1)-->(6) 146 ├── scan fks 147 │ ├── columns: k:1!null r1:3!null 148 │ ├── key: (1) 149 │ └── fd: (1)-->(3) 150 └── aggregations 151 └── max [as=max:6, outer=(3)] 152 └── r1:3 153 154 # Case with ScalarGroupBy with a sum aggregate that doesn't ignore duplicates. 155 # The join can be eliminated because r1 is a foreign key referencing x, which 156 # implies that the rows of fks are not being duplicated by the join. 157 norm expect=EliminateJoinUnderGroupByLeft 158 SELECT sum(k) FROM fks LEFT JOIN xy ON x=r1 159 ---- 160 scalar-group-by 161 ├── columns: sum:7 162 ├── cardinality: [1 - 1] 163 ├── key: () 164 ├── fd: ()-->(7) 165 ├── scan fks 166 │ ├── columns: k:1!null 167 │ └── key: (1) 168 └── aggregations 169 └── sum [as=sum:7, outer=(1)] 170 └── k:1 171 172 # LeftJoin case with possible duplicate rows. The rule can fire because the 173 # output of the max aggregate is not affected by duplicate rows. 174 norm expect=EliminateJoinUnderGroupByLeft 175 SELECT x, max(y) FROM xy LEFT JOIN fks ON True GROUP BY x 176 ---- 177 group-by 178 ├── columns: x:1!null max:7 179 ├── grouping columns: x:1!null 180 ├── key: (1) 181 ├── fd: (1)-->(7) 182 ├── scan xy 183 │ ├── columns: x:1!null y:2 184 │ ├── key: (1) 185 │ └── fd: (1)-->(2) 186 └── aggregations 187 └── max [as=max:7, outer=(2)] 188 └── y:2 189 190 # LeftJoin case with a not-null foreign key equality filter and a sum aggregate. 191 norm expect=EliminateJoinUnderGroupByLeft 192 SELECT k, sum(r1) FROM fks LEFT JOIN xy ON x=r1 GROUP BY k 193 ---- 194 group-by 195 ├── columns: k:1!null sum:7!null 196 ├── grouping columns: k:1!null 197 ├── key: (1) 198 ├── fd: (1)-->(7) 199 ├── scan fks 200 │ ├── columns: k:1!null r1:3!null 201 │ ├── key: (1) 202 │ └── fd: (1)-->(3) 203 └── aggregations 204 └── sum [as=sum:7, outer=(3)] 205 └── r1:3 206 207 # The LeftJoin guarantees that all left rows will be included in the output, and 208 # since k is a key column, no rows from xy will be duplicated. Therefore the sum 209 # aggregate will not be affected by join removal. 210 norm expect=EliminateJoinUnderGroupByLeft 211 SELECT x, sum(y) FROM xy LEFT JOIN fks ON x=k GROUP BY x 212 ---- 213 group-by 214 ├── columns: x:1!null sum:7 215 ├── grouping columns: x:1!null 216 ├── key: (1) 217 ├── fd: (1)-->(7) 218 ├── scan xy 219 │ ├── columns: x:1!null y:2 220 │ ├── key: (1) 221 │ └── fd: (1)-->(2) 222 └── aggregations 223 └── sum [as=sum:7, outer=(2)] 224 └── y:2 225 226 # The LeftJoin guarantees that all left rows will be included in the output, and 227 # since r2 is a foreign key referencing x, it is guaranteed that no left rows 228 # will be matched more than once. Therefore, the sum aggregate will be 229 # unaffected by join removal. 230 norm expect=EliminateJoinUnderGroupByLeft 231 SELECT k, sum(r1) FROM fks LEFT JOIN xy ON x=r2 GROUP BY k 232 ---- 233 group-by 234 ├── columns: k:1!null sum:7!null 235 ├── grouping columns: k:1!null 236 ├── key: (1) 237 ├── fd: (1)-->(7) 238 ├── scan fks 239 │ ├── columns: k:1!null r1:3!null 240 │ ├── key: (1) 241 │ └── fd: (1)-->(3) 242 └── aggregations 243 └── sum [as=sum:7, outer=(3)] 244 └── r1:3 245 246 # InnerJoin case. Because r1 is a non-null foreign key that references x, the 247 # join output is guaranteed to include every left row exactly once. 248 norm expect=EliminateJoinUnderGroupByLeft 249 SELECT k, sum(r1) FROM fks INNER JOIN xy ON x=r1 GROUP BY k 250 ---- 251 group-by 252 ├── columns: k:1!null sum:7!null 253 ├── grouping columns: k:1!null 254 ├── key: (1) 255 ├── fd: (1)-->(7) 256 ├── scan fks 257 │ ├── columns: k:1!null r1:3!null 258 │ ├── key: (1) 259 │ └── fd: (1)-->(3) 260 └── aggregations 261 └── sum [as=sum:7, outer=(3)] 262 └── r1:3 263 264 # Case with an ordering on left columns. 265 norm expect=EliminateJoinUnderGroupByLeft 266 SELECT max(y) FROM xy LEFT JOIN fks ON x = k GROUP BY x ORDER BY x 267 ---- 268 group-by 269 ├── columns: max:7 [hidden: x:1!null] 270 ├── grouping columns: x:1!null 271 ├── key: (1) 272 ├── fd: (1)-->(7) 273 ├── ordering: +1 274 ├── scan xy 275 │ ├── columns: x:1!null y:2 276 │ ├── key: (1) 277 │ ├── fd: (1)-->(2) 278 │ └── ordering: +1 279 └── aggregations 280 └── max [as=max:7, outer=(2)] 281 └── y:2 282 283 # No-op case because the InnerJoin will return no rows if fks is empty. 284 norm expect-not=EliminateJoinUnderGroupByLeft 285 SELECT DISTINCT ON (x) x, y FROM xy INNER JOIN fks ON True 286 ---- 287 distinct-on 288 ├── columns: x:1!null y:2 289 ├── grouping columns: x:1!null 290 ├── key: (1) 291 ├── fd: (1)-->(2) 292 ├── inner-join (cross) 293 │ ├── columns: x:1!null y:2 294 │ ├── fd: (1)-->(2) 295 │ ├── scan xy 296 │ │ ├── columns: x:1!null y:2 297 │ │ ├── key: (1) 298 │ │ └── fd: (1)-->(2) 299 │ ├── scan fks 300 │ └── filters (true) 301 └── aggregations 302 └── first-agg [as=y:2, outer=(2)] 303 └── y:2 304 305 # No-op case because the DistinctOn is using columns from the right input. 306 norm expect-not=EliminateJoinUnderGroupByLeft 307 SELECT DISTINCT ON (x) y, k FROM xy LEFT JOIN fks ON True 308 ---- 309 distinct-on 310 ├── columns: y:2 k:3 [hidden: x:1!null] 311 ├── grouping columns: x:1!null 312 ├── key: (1) 313 ├── fd: (1)-->(2,3) 314 ├── left-join (cross) 315 │ ├── columns: x:1!null y:2 k:3 316 │ ├── key: (1,3) 317 │ ├── fd: (1)-->(2) 318 │ ├── scan xy 319 │ │ ├── columns: x:1!null y:2 320 │ │ ├── key: (1) 321 │ │ └── fd: (1)-->(2) 322 │ ├── scan fks 323 │ │ ├── columns: k:3!null 324 │ │ └── key: (3) 325 │ └── filters (true) 326 └── aggregations 327 ├── first-agg [as=y:2, outer=(2)] 328 │ └── y:2 329 └── first-agg [as=k:3, outer=(3)] 330 └── k:3 331 332 # No-op case because an InnerJoin on true may create duplicate rows that will 333 # affect the output of the sum on r1. 334 norm expect-not=EliminateJoinUnderGroupByLeft 335 SELECT k, sum(r1) FROM fks INNER JOIN xy ON True GROUP BY k 336 ---- 337 group-by 338 ├── columns: k:1!null sum:7!null 339 ├── grouping columns: k:1!null 340 ├── key: (1) 341 ├── fd: (1)-->(7) 342 ├── inner-join (cross) 343 │ ├── columns: k:1!null r1:3!null 344 │ ├── fd: (1)-->(3) 345 │ ├── scan fks 346 │ │ ├── columns: k:1!null r1:3!null 347 │ │ ├── key: (1) 348 │ │ └── fd: (1)-->(3) 349 │ ├── scan xy 350 │ └── filters (true) 351 └── aggregations 352 └── sum [as=sum:7, outer=(3)] 353 └── r1:3 354 355 # No-op case with a foreign key equality filter and a sum aggregate. No-op 356 # because r2 is nullable and therefore the InnerJoin may filter out rows. 357 norm expect-not=EliminateJoinUnderGroupByLeft 358 SELECT k, sum(r1) FROM fks INNER JOIN xy ON x=r2 GROUP BY k 359 ---- 360 group-by 361 ├── columns: k:1!null sum:7!null 362 ├── grouping columns: k:1!null 363 ├── key: (1) 364 ├── fd: (1)-->(7) 365 ├── inner-join (hash) 366 │ ├── columns: k:1!null r1:3!null r2:4!null x:5!null 367 │ ├── key: (1) 368 │ ├── fd: (1)-->(3,4), (4)==(5), (5)==(4) 369 │ ├── scan fks 370 │ │ ├── columns: k:1!null r1:3!null r2:4 371 │ │ ├── key: (1) 372 │ │ └── fd: (1)-->(3,4) 373 │ ├── scan xy 374 │ │ ├── columns: x:5!null 375 │ │ └── key: (5) 376 │ └── filters 377 │ └── x:5 = r2:4 [outer=(4,5), constraints=(/4: (/NULL - ]; /5: (/NULL - ]), fd=(4)==(5), (5)==(4)] 378 └── aggregations 379 └── sum [as=sum:7, outer=(3)] 380 └── r1:3 381 382 # No-op case because the ordering includes a column from the right input. 383 norm expect-not=EliminateJoinUnderGroupByLeft 384 SELECT x, max(y) FROM xy LEFT JOIN fks ON True GROUP BY x, k ORDER BY x, k 385 ---- 386 group-by 387 ├── columns: x:1!null max:7 [hidden: k:3] 388 ├── grouping columns: x:1!null k:3 389 ├── key: (1,3) 390 ├── fd: (1,3)-->(7) 391 ├── ordering: +1,+3 392 ├── sort 393 │ ├── columns: x:1!null y:2 k:3 394 │ ├── key: (1,3) 395 │ ├── fd: (1)-->(2) 396 │ ├── ordering: +1,+3 397 │ └── left-join (cross) 398 │ ├── columns: x:1!null y:2 k:3 399 │ ├── key: (1,3) 400 │ ├── fd: (1)-->(2) 401 │ ├── scan xy 402 │ │ ├── columns: x:1!null y:2 403 │ │ ├── key: (1) 404 │ │ └── fd: (1)-->(2) 405 │ ├── scan fks 406 │ │ ├── columns: k:3!null 407 │ │ └── key: (3) 408 │ └── filters (true) 409 └── aggregations 410 └── max [as=max:7, outer=(2)] 411 └── y:2 412 413 # Currently a no-op case even though we could hypothetically remove the join, 414 # since the presence of a not-null foreign key in fks implies that either both 415 # tables will have a cardinality of at least one, or both will have a 416 # cardinality of zero. 417 norm expect-not=EliminateJoinUnderGroupByLeft 418 SELECT DISTINCT ON (k) k, v FROM fks INNER JOIN xy ON True 419 ---- 420 distinct-on 421 ├── columns: k:1!null v:2 422 ├── grouping columns: k:1!null 423 ├── key: (1) 424 ├── fd: (1)-->(2) 425 ├── inner-join (cross) 426 │ ├── columns: k:1!null v:2 427 │ ├── fd: (1)-->(2) 428 │ ├── scan fks 429 │ │ ├── columns: k:1!null v:2 430 │ │ ├── key: (1) 431 │ │ └── fd: (1)-->(2) 432 │ ├── scan xy 433 │ └── filters (true) 434 └── aggregations 435 └── first-agg [as=v:2, outer=(2)] 436 └── v:2 437 438 # -------------------------------------------------- 439 # EliminateJoinUnderGroupByRight 440 # -------------------------------------------------- 441 442 # InnerJoin case. 443 norm expect=EliminateJoinUnderGroupByRight 444 SELECT k, sum(r1) FROM xy INNER JOIN fks ON x = r1 GROUP BY k 445 ---- 446 group-by 447 ├── columns: k:3!null sum:7!null 448 ├── grouping columns: k:3!null 449 ├── key: (3) 450 ├── fd: (3)-->(7) 451 ├── scan fks 452 │ ├── columns: k:3!null r1:5!null 453 │ ├── key: (3) 454 │ └── fd: (3)-->(5) 455 └── aggregations 456 └── sum [as=sum:7, outer=(5)] 457 └── r1:5 458 459 # No-op case because columns from the right side of a left join are being used. 460 norm expect-not=EliminateJoinUnderGroupByRight 461 SELECT max(r1) FROM xy LEFT JOIN fks ON x = r1 462 ---- 463 scalar-group-by 464 ├── columns: max:7 465 ├── cardinality: [1 - 1] 466 ├── key: () 467 ├── fd: ()-->(7) 468 ├── left-join (hash) 469 │ ├── columns: x:1!null r1:5 470 │ ├── scan xy 471 │ │ ├── columns: x:1!null 472 │ │ └── key: (1) 473 │ ├── scan fks 474 │ │ └── columns: r1:5!null 475 │ └── filters 476 │ └── x:1 = r1:5 [outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)] 477 └── aggregations 478 └── max [as=max:7, outer=(5)] 479 └── r1:5 480 481 # -------------------------------------------------- 482 # EliminateDistinct 483 # -------------------------------------------------- 484 norm expect=EliminateDistinct 485 SELECT DISTINCT k FROM a 486 ---- 487 scan a 488 ├── columns: k:1!null 489 └── key: (1) 490 491 norm expect=EliminateDistinct 492 SELECT DISTINCT s, i FROM a 493 ---- 494 scan a 495 ├── columns: s:4!null i:2!null 496 └── key: (2,4) 497 498 norm expect=EliminateDistinct 499 SELECT DISTINCT ON (s, i) k, i, f FROM a 500 ---- 501 scan a 502 ├── columns: k:1!null i:2!null f:3 503 ├── key: (1) 504 └── fd: (1)-->(2,3), (2,3)~~>(1) 505 506 # Strict superset of key. 507 norm expect=EliminateDistinct 508 SELECT DISTINCT s, i, f FROM a 509 ---- 510 scan a 511 ├── columns: s:4!null i:2!null f:3 512 ├── key: (2,4) 513 └── fd: (2,4)-->(3), (2,3)~~>(4) 514 515 # Distinct not eliminated because columns aren't superset of any weak key. 516 norm expect-not=EliminateDistinct 517 SELECT DISTINCT i FROM a 518 ---- 519 distinct-on 520 ├── columns: i:2!null 521 ├── grouping columns: i:2!null 522 ├── key: (2) 523 └── scan a 524 └── columns: i:2!null 525 526 # Distinct not eliminated despite a unique index on (f, i) because f is nullable. 527 norm expect-not=EliminateDistinct 528 SELECT DISTINCT f, i FROM a 529 ---- 530 distinct-on 531 ├── columns: f:3 i:2!null 532 ├── grouping columns: i:2!null f:3 533 ├── key: (2,3) 534 └── scan a 535 ├── columns: i:2!null f:3 536 └── lax-key: (2,3) 537 538 # Regression test for #40295. Ensure that the DistinctOn is replaced with a 539 # Project operator to keep the correct number of output columns. 540 exec-ddl 541 CREATE TABLE table0 (col0 REGTYPE); 542 ---- 543 544 exec-ddl 545 CREATE TABLE table1 (col0 REGCLASS, col1 REGTYPE, col2 INT4); 546 ---- 547 548 norm expect=EliminateDistinct 549 SELECT 550 ( 551 SELECT 552 t1.col2 553 FROM 554 table1 AS t1 555 JOIN table0 AS t0 ON 556 t1.col1 = t0.col0 557 AND t1.col0 = t0.col0 558 GROUP BY 559 t1.col2 560 HAVING 561 NULL 562 ); 563 ---- 564 values 565 ├── columns: col2:7 566 ├── cardinality: [1 - 1] 567 ├── key: () 568 ├── fd: ()-->(7) 569 └── tuple 570 └── subquery 571 └── values 572 ├── columns: t1.col2:3!null 573 ├── cardinality: [0 - 0] 574 ├── key: () 575 └── fd: ()-->(3) 576 577 # EnsureDistinctOn case. 578 # EliminateMax1Row is disabled to ensure that an EnsureDistinctOn operator is 579 # created. 580 norm expect=EliminateDistinct disable=EliminateMax1Row 581 SELECT (SELECT y FROM xy WHERE x=k AND k=5) FROM a 582 ---- 583 project 584 ├── columns: y:8 585 ├── left-join (cross) 586 │ ├── columns: k:1!null x:6 xy.y:7 587 │ ├── key: (1) 588 │ ├── fd: (1)-->(6,7) 589 │ ├── scan a 590 │ │ ├── columns: k:1!null 591 │ │ └── key: (1) 592 │ ├── select 593 │ │ ├── columns: x:6!null xy.y:7 594 │ │ ├── cardinality: [0 - 1] 595 │ │ ├── key: () 596 │ │ ├── fd: ()-->(6,7) 597 │ │ ├── scan xy 598 │ │ │ ├── columns: x:6!null xy.y:7 599 │ │ │ ├── key: (6) 600 │ │ │ └── fd: (6)-->(7) 601 │ │ └── filters 602 │ │ └── x:6 = 5 [outer=(6), constraints=(/6: [/5 - /5]; tight), fd=()-->(6)] 603 │ └── filters 604 │ └── k:1 = 5 [outer=(1), constraints=(/1: [/5 - /5]; tight), fd=()-->(1)] 605 └── projections 606 └── xy.y:7 [as=y:8, outer=(7)] 607 608 # -------------------------------------------------- 609 # EliminateGroupByProject 610 # -------------------------------------------------- 611 norm expect=EliminateGroupByProject 612 SELECT min(s) FROM (SELECT i, s FROM (SELECT * FROM a UNION SELECT * FROM a)) GROUP BY i 613 ---- 614 project 615 ├── columns: min:16!null 616 └── group-by 617 ├── columns: i:12!null min:16!null 618 ├── grouping columns: i:12!null 619 ├── key: (12) 620 ├── fd: (12)-->(16) 621 ├── union 622 │ ├── columns: k:11!null i:12!null f:13 s:14!null j:15 623 │ ├── left columns: a.k:1 a.i:2 a.f:3 a.s:4 a.j:5 624 │ ├── right columns: a.k:6 a.i:7 a.f:8 a.s:9 a.j:10 625 │ ├── key: (11-15) 626 │ ├── scan a 627 │ │ ├── columns: a.k:1!null a.i:2!null a.f:3 a.s:4!null a.j:5 628 │ │ ├── key: (1) 629 │ │ └── fd: (1)-->(2-5), (2,4)-->(1,3,5), (2,3)~~>(1,4,5) 630 │ └── scan a 631 │ ├── columns: a.k:6!null a.i:7!null a.f:8 a.s:9!null a.j:10 632 │ ├── key: (6) 633 │ └── fd: (6)-->(7-10), (7,9)-->(6,8,10), (7,8)~~>(6,9,10) 634 └── aggregations 635 └── min [as=min:16, outer=(14)] 636 └── s:14 637 638 # ScalarGroupBy case. 639 norm expect=EliminateGroupByProject 640 SELECT min(s) FROM (SELECT i, s FROM (SELECT * FROM a UNION SELECT * FROM a)) 641 ---- 642 scalar-group-by 643 ├── columns: min:16 644 ├── cardinality: [1 - 1] 645 ├── key: () 646 ├── fd: ()-->(16) 647 ├── union 648 │ ├── columns: k:11!null i:12!null f:13 s:14!null j:15 649 │ ├── left columns: a.k:1 a.i:2 a.f:3 a.s:4 a.j:5 650 │ ├── right columns: a.k:6 a.i:7 a.f:8 a.s:9 a.j:10 651 │ ├── key: (11-15) 652 │ ├── scan a 653 │ │ ├── columns: a.k:1!null a.i:2!null a.f:3 a.s:4!null a.j:5 654 │ │ ├── key: (1) 655 │ │ └── fd: (1)-->(2-5), (2,4)-->(1,3,5), (2,3)~~>(1,4,5) 656 │ └── scan a 657 │ ├── columns: a.k:6!null a.i:7!null a.f:8 a.s:9!null a.j:10 658 │ ├── key: (6) 659 │ └── fd: (6)-->(7-10), (7,9)-->(6,8,10), (7,8)~~>(6,9,10) 660 └── aggregations 661 └── min [as=min:16, outer=(14)] 662 └── s:14 663 664 # DistinctOn case. 665 norm expect=EliminateGroupByProject 666 SELECT DISTINCT ON (i) s FROM (SELECT i, s, f FROM (SELECT * FROM a UNION SELECT * FROM a)) 667 ---- 668 distinct-on 669 ├── columns: s:14!null [hidden: i:12!null] 670 ├── grouping columns: i:12!null 671 ├── key: (12) 672 ├── fd: (12)-->(14) 673 ├── union 674 │ ├── columns: k:11!null i:12!null f:13 s:14!null j:15 675 │ ├── left columns: a.k:1 a.i:2 a.f:3 a.s:4 a.j:5 676 │ ├── right columns: a.k:6 a.i:7 a.f:8 a.s:9 a.j:10 677 │ ├── key: (11-15) 678 │ ├── scan a 679 │ │ ├── columns: a.k:1!null a.i:2!null a.f:3 a.s:4!null a.j:5 680 │ │ ├── key: (1) 681 │ │ └── fd: (1)-->(2-5), (2,4)-->(1,3,5), (2,3)~~>(1,4,5) 682 │ └── scan a 683 │ ├── columns: a.k:6!null a.i:7!null a.f:8 a.s:9!null a.j:10 684 │ ├── key: (6) 685 │ └── fd: (6)-->(7-10), (7,9)-->(6,8,10), (7,8)~~>(6,9,10) 686 └── aggregations 687 └── first-agg [as=s:14, outer=(14)] 688 └── s:14 689 690 # EnsureDistinctOn case. 691 # EliminateMax1Row is disabled to ensure that an EnsureDistinctOn operator is 692 # created. 693 norm expect=EliminateGroupByProject disable=EliminateMax1Row 694 SELECT (SELECT y FROM xy WHERE x+y=k) FROM a 695 ---- 696 project 697 ├── columns: y:8 698 ├── ensure-distinct-on 699 │ ├── columns: k:1!null xy.y:7 700 │ ├── grouping columns: k:1!null 701 │ ├── error: "more than one row returned by a subquery used as an expression" 702 │ ├── key: (1) 703 │ ├── fd: (1)-->(7) 704 │ ├── left-join (hash) 705 │ │ ├── columns: k:1!null xy.y:7 column9:9 706 │ │ ├── scan a 707 │ │ │ ├── columns: k:1!null 708 │ │ │ └── key: (1) 709 │ │ ├── project 710 │ │ │ ├── columns: column9:9 xy.y:7 711 │ │ │ ├── scan xy 712 │ │ │ │ ├── columns: x:6!null xy.y:7 713 │ │ │ │ ├── key: (6) 714 │ │ │ │ └── fd: (6)-->(7) 715 │ │ │ └── projections 716 │ │ │ └── x:6 + xy.y:7 [as=column9:9, outer=(6,7)] 717 │ │ └── filters 718 │ │ └── k:1 = column9:9 [outer=(1,9), constraints=(/1: (/NULL - ]; /9: (/NULL - ]), fd=(1)==(9), (9)==(1)] 719 │ └── aggregations 720 │ └── const-agg [as=xy.y:7, outer=(7)] 721 │ └── xy.y:7 722 └── projections 723 └── xy.y:7 [as=y:8, outer=(7)] 724 725 # UpsertDistinctOn case. 726 norm expect=EliminateGroupByProject 727 INSERT INTO nullablecols (rowid, c1, c2, c3) 728 SELECT i, i, i, i FROM (SELECT * FROM a WHERE EXISTS(SELECT * FROM a) AND k>0) 729 ON CONFLICT (c1) DO NOTHING 730 ---- 731 insert nullablecols 732 ├── columns: <none> 733 ├── insert-mapping: 734 │ ├── i:6 => c1:1 735 │ ├── i:6 => c2:2 736 │ ├── i:6 => c3:3 737 │ └── i:6 => rowid:4 738 ├── cardinality: [0 - 0] 739 ├── volatile, side-effects, mutations 740 └── upsert-distinct-on 741 ├── columns: i:6!null 742 ├── grouping columns: i:6!null 743 ├── key: (6) 744 └── select 745 ├── columns: k:5!null i:6!null c1:15 rowid:18 746 ├── key: (5) 747 ├── fd: ()-->(15,18), (5)-->(6) 748 ├── left-join (hash) 749 │ ├── columns: k:5!null i:6!null c1:15 rowid:18 750 │ ├── key: (5,18) 751 │ ├── fd: (5)-->(6), (18)-->(15), (15)~~>(18) 752 │ ├── select 753 │ │ ├── columns: k:5!null i:6!null 754 │ │ ├── key: (5) 755 │ │ ├── fd: (5)-->(6) 756 │ │ ├── scan a 757 │ │ │ ├── columns: k:5!null i:6!null 758 │ │ │ ├── key: (5) 759 │ │ │ └── fd: (5)-->(6) 760 │ │ └── filters 761 │ │ ├── exists [subquery] 762 │ │ │ └── limit 763 │ │ │ ├── columns: k:10!null i:11!null f:12 s:13!null j:14 764 │ │ │ ├── cardinality: [0 - 1] 765 │ │ │ ├── key: () 766 │ │ │ ├── fd: ()-->(10-14) 767 │ │ │ ├── scan a 768 │ │ │ │ ├── columns: k:10!null i:11!null f:12 s:13!null j:14 769 │ │ │ │ ├── key: (10) 770 │ │ │ │ ├── fd: (10)-->(11-14), (11,13)-->(10,12,14), (11,12)~~>(10,13,14) 771 │ │ │ │ └── limit hint: 1.00 772 │ │ │ └── 1 773 │ │ └── k:5 > 0 [outer=(5), constraints=(/5: [/1 - ]; tight)] 774 │ ├── scan nullablecols 775 │ │ ├── columns: c1:15 rowid:18!null 776 │ │ ├── key: (18) 777 │ │ └── fd: (18)-->(15), (15)~~>(18) 778 │ └── filters 779 │ └── i:6 = c1:15 [outer=(6,15), constraints=(/6: (/NULL - ]; /15: (/NULL - ]), fd=(6)==(15), (15)==(6)] 780 └── filters 781 └── rowid:18 IS NULL [outer=(18), constraints=(/18: [/NULL - /NULL]; tight), fd=()-->(18)] 782 783 # EnsureUpsertDistinctOn case. 784 norm expect=EliminateGroupByProject 785 INSERT INTO nullablecols (rowid, c1, c2, c3) 786 SELECT i, i, i, i FROM (SELECT * FROM a WHERE EXISTS(SELECT * FROM a) AND k>0) 787 ON CONFLICT (c1) DO UPDATE SET c3=1 788 ---- 789 upsert nullablecols 790 ├── columns: <none> 791 ├── canary column: 18 792 ├── fetch columns: c1:15 c2:16 c3:17 rowid:18 793 ├── insert-mapping: 794 │ ├── i:6 => c1:1 795 │ ├── i:6 => c2:2 796 │ ├── i:6 => c3:3 797 │ └── i:6 => rowid:4 798 ├── update-mapping: 799 │ └── upsert_c3:22 => c3:3 800 ├── cardinality: [0 - 0] 801 ├── volatile, side-effects, mutations 802 └── project 803 ├── columns: upsert_c3:22!null i:6!null c1:15 c2:16 c3:17 rowid:18 804 ├── key: (6,18) 805 ├── fd: (18)-->(15-17), (15)~~>(16-18), (16,17)~~>(15,18), (6,18)-->(22) 806 ├── left-join (hash) 807 │ ├── columns: i:6!null c1:15 c2:16 c3:17 rowid:18 808 │ ├── key: (6,18) 809 │ ├── fd: (18)-->(15-17), (15)~~>(16-18), (16,17)~~>(15,18) 810 │ ├── ensure-upsert-distinct-on 811 │ │ ├── columns: i:6!null 812 │ │ ├── grouping columns: i:6!null 813 │ │ ├── error: "UPSERT or INSERT...ON CONFLICT command cannot affect row a second time" 814 │ │ ├── key: (6) 815 │ │ └── select 816 │ │ ├── columns: k:5!null i:6!null 817 │ │ ├── key: (5) 818 │ │ ├── fd: (5)-->(6) 819 │ │ ├── scan a 820 │ │ │ ├── columns: k:5!null i:6!null 821 │ │ │ ├── key: (5) 822 │ │ │ └── fd: (5)-->(6) 823 │ │ └── filters 824 │ │ ├── exists [subquery] 825 │ │ │ └── limit 826 │ │ │ ├── columns: k:10!null i:11!null f:12 s:13!null j:14 827 │ │ │ ├── cardinality: [0 - 1] 828 │ │ │ ├── key: () 829 │ │ │ ├── fd: ()-->(10-14) 830 │ │ │ ├── scan a 831 │ │ │ │ ├── columns: k:10!null i:11!null f:12 s:13!null j:14 832 │ │ │ │ ├── key: (10) 833 │ │ │ │ ├── fd: (10)-->(11-14), (11,13)-->(10,12,14), (11,12)~~>(10,13,14) 834 │ │ │ │ └── limit hint: 1.00 835 │ │ │ └── 1 836 │ │ └── k:5 > 0 [outer=(5), constraints=(/5: [/1 - ]; tight)] 837 │ ├── scan nullablecols 838 │ │ ├── columns: c1:15 c2:16 c3:17 rowid:18!null 839 │ │ ├── key: (18) 840 │ │ └── fd: (18)-->(15-17), (15)~~>(16-18), (16,17)~~>(15,18) 841 │ └── filters 842 │ └── i:6 = c1:15 [outer=(6,15), constraints=(/6: (/NULL - ]; /15: (/NULL - ]), fd=(6)==(15), (15)==(6)] 843 └── projections 844 └── CASE WHEN rowid:18 IS NULL THEN i:6 ELSE 1 END [as=upsert_c3:22, outer=(6,18)] 845 846 # Don't eliminate project if it computes extra column(s). 847 norm expect-not=EliminateGroupByProject 848 SELECT min(s) FROM (SELECT i+1 AS i2, s FROM a) GROUP BY i2 849 ---- 850 project 851 ├── columns: min:7!null 852 └── group-by 853 ├── columns: i2:6!null min:7!null 854 ├── grouping columns: i2:6!null 855 ├── key: (6) 856 ├── fd: (6)-->(7) 857 ├── project 858 │ ├── columns: i2:6!null s:4!null 859 │ ├── scan a 860 │ │ ├── columns: i:2!null s:4!null 861 │ │ └── key: (2,4) 862 │ └── projections 863 │ └── i:2 + 1 [as=i2:6, outer=(2)] 864 └── aggregations 865 └── min [as=min:7, outer=(4)] 866 └── s:4 867 868 # -------------------------------------------------- 869 # ReduceGroupingCols 870 # -------------------------------------------------- 871 norm expect=ReduceGroupingCols 872 SELECT k, min(i), f, s FROM a GROUP BY s, f, k 873 ---- 874 group-by 875 ├── columns: k:1!null min:6!null f:3 s:4!null 876 ├── grouping columns: k:1!null 877 ├── key: (1) 878 ├── fd: (1)-->(3,4,6) 879 ├── scan a 880 │ ├── columns: k:1!null i:2!null f:3 s:4!null 881 │ ├── key: (1) 882 │ └── fd: (1)-->(2-4), (2,4)-->(1,3), (2,3)~~>(1,4) 883 └── aggregations 884 ├── min [as=min:6, outer=(2)] 885 │ └── i:2 886 ├── const-agg [as=f:3, outer=(3)] 887 │ └── f:3 888 └── const-agg [as=s:4, outer=(4)] 889 └── s:4 890 891 norm expect=ReduceGroupingCols 892 SELECT k, sum(DISTINCT i), f, s FROM a, xy GROUP BY s, f, k 893 ---- 894 group-by 895 ├── columns: k:1!null sum:8!null f:3 s:4!null 896 ├── grouping columns: k:1!null 897 ├── key: (1) 898 ├── fd: (1)-->(3,4,8) 899 ├── inner-join (cross) 900 │ ├── columns: k:1!null i:2!null f:3 s:4!null 901 │ ├── fd: (1)-->(2-4), (2,4)-->(1,3), (2,3)~~>(1,4) 902 │ ├── scan a 903 │ │ ├── columns: k:1!null i:2!null f:3 s:4!null 904 │ │ ├── key: (1) 905 │ │ └── fd: (1)-->(2-4), (2,4)-->(1,3), (2,3)~~>(1,4) 906 │ ├── scan xy 907 │ └── filters (true) 908 └── aggregations 909 ├── agg-distinct [as=sum:8, outer=(2)] 910 │ └── sum 911 │ └── i:2 912 ├── const-agg [as=f:3, outer=(3)] 913 │ └── f:3 914 └── const-agg [as=s:4, outer=(4)] 915 └── s:4 916 917 # Eliminated columns are not part of projection. 918 norm expect=ReduceGroupingCols 919 SELECT min(f) FROM a GROUP BY i, s, k 920 ---- 921 project 922 ├── columns: min:6 923 └── group-by 924 ├── columns: i:2!null s:4!null min:6 925 ├── grouping columns: i:2!null s:4!null 926 ├── key: (2,4) 927 ├── fd: (2,4)-->(6) 928 ├── scan a 929 │ ├── columns: i:2!null f:3 s:4!null 930 │ ├── key: (2,4) 931 │ └── fd: (2,4)-->(3), (2,3)~~>(4) 932 └── aggregations 933 └── min [as=min:6, outer=(3)] 934 └── f:3 935 936 # All grouping columns eliminated. 937 norm expect=ReduceGroupingCols 938 SELECT sum(f), i FROM a GROUP BY k, i, f HAVING k=1 939 ---- 940 group-by 941 ├── columns: sum:6 i:2!null 942 ├── cardinality: [0 - 1] 943 ├── key: () 944 ├── fd: ()-->(2,6) 945 ├── select 946 │ ├── columns: k:1!null i:2!null f:3 947 │ ├── cardinality: [0 - 1] 948 │ ├── key: () 949 │ ├── fd: ()-->(1-3) 950 │ ├── scan a 951 │ │ ├── columns: k:1!null i:2!null f:3 952 │ │ ├── key: (1) 953 │ │ └── fd: (1)-->(2,3), (2,3)~~>(1) 954 │ └── filters 955 │ └── k:1 = 1 [outer=(1), constraints=(/1: [/1 - /1]; tight), fd=()-->(1)] 956 └── aggregations 957 ├── sum [as=sum:6, outer=(3)] 958 │ └── f:3 959 └── const-agg [as=i:2, outer=(2)] 960 └── i:2 961 962 norm expect=ReduceGroupingCols 963 SELECT DISTINCT ON (k, f, s) i, f, x FROM a JOIN xy ON i=y 964 ---- 965 distinct-on 966 ├── columns: i:2!null f:3 x:6!null [hidden: k:1!null] 967 ├── grouping columns: k:1!null 968 ├── key: (1) 969 ├── fd: (1)-->(2,3,6), (2,3)~~>(1), (6)-->(2) 970 ├── inner-join (hash) 971 │ ├── columns: k:1!null i:2!null f:3 x:6!null y:7!null 972 │ ├── key: (1,6) 973 │ ├── fd: (1)-->(2,3), (2,3)~~>(1), (6)-->(7), (2)==(7), (7)==(2) 974 │ ├── scan a 975 │ │ ├── columns: k:1!null i:2!null f:3 976 │ │ ├── key: (1) 977 │ │ └── fd: (1)-->(2,3), (2,3)~~>(1) 978 │ ├── scan xy 979 │ │ ├── columns: x:6!null y:7 980 │ │ ├── key: (6) 981 │ │ └── fd: (6)-->(7) 982 │ └── filters 983 │ └── i:2 = y:7 [outer=(2,7), constraints=(/2: (/NULL - ]; /7: (/NULL - ]), fd=(2)==(7), (7)==(2)] 984 └── aggregations 985 ├── first-agg [as=i:2, outer=(2)] 986 │ └── i:2 987 ├── first-agg [as=x:6, outer=(6)] 988 │ └── x:6 989 └── const-agg [as=f:3, outer=(3)] 990 └── f:3 991 992 # -------------------------------------------------- 993 # ReduceNotNullGroupingCols 994 # -------------------------------------------------- 995 996 # UpsertDistinctOn should reduce non-nullable constant grouping column. 997 norm expect=ReduceNotNullGroupingCols 998 INSERT INTO xy (x) 999 SELECT y FROM xy WHERE y=0 1000 ON CONFLICT (x) DO NOTHING 1001 ---- 1002 insert xy 1003 ├── columns: <none> 1004 ├── insert-mapping: 1005 │ ├── y:4 => x:1 1006 │ └── column5:5 => y:2 1007 ├── cardinality: [0 - 0] 1008 ├── volatile, side-effects, mutations 1009 └── project 1010 ├── columns: y:4!null column5:5 1011 ├── cardinality: [0 - 1] 1012 ├── key: () 1013 ├── fd: ()-->(4,5) 1014 └── limit 1015 ├── columns: y:4!null column5:5 x:6 1016 ├── cardinality: [0 - 1] 1017 ├── key: () 1018 ├── fd: ()-->(4-6) 1019 ├── select 1020 │ ├── columns: y:4!null column5:5 x:6 1021 │ ├── fd: ()-->(4-6) 1022 │ ├── limit hint: 1.00 1023 │ ├── left-join (hash) 1024 │ │ ├── columns: y:4!null column5:5 x:6 1025 │ │ ├── fd: ()-->(4,5) 1026 │ │ ├── limit hint: 1.00 1027 │ │ ├── project 1028 │ │ │ ├── columns: column5:5 y:4!null 1029 │ │ │ ├── fd: ()-->(4,5) 1030 │ │ │ ├── select 1031 │ │ │ │ ├── columns: y:4!null 1032 │ │ │ │ ├── fd: ()-->(4) 1033 │ │ │ │ ├── scan xy 1034 │ │ │ │ │ └── columns: y:4 1035 │ │ │ │ └── filters 1036 │ │ │ │ └── y:4 = 0 [outer=(4), constraints=(/4: [/0 - /0]; tight), fd=()-->(4)] 1037 │ │ │ └── projections 1038 │ │ │ └── CAST(NULL AS INT8) [as=column5:5] 1039 │ │ ├── scan xy 1040 │ │ │ ├── columns: x:6!null 1041 │ │ │ └── key: (6) 1042 │ │ └── filters 1043 │ │ └── y:4 = x:6 [outer=(4,6), constraints=(/4: (/NULL - ]; /6: (/NULL - ]), fd=(4)==(6), (6)==(4)] 1044 │ └── filters 1045 │ └── x:6 IS NULL [outer=(6), constraints=(/6: [/NULL - /NULL]; tight), fd=()-->(6)] 1046 └── 1 1047 1048 # EnsureUpsertDistinctOn should reduce non-nullable constant grouping column. 1049 norm expect=ReduceNotNullGroupingCols 1050 INSERT INTO xy (x) 1051 SELECT y FROM xy WHERE y=0 1052 ON CONFLICT (x) DO UPDATE SET y=1 1053 ---- 1054 upsert xy 1055 ├── columns: <none> 1056 ├── canary column: 6 1057 ├── fetch columns: x:6 y:7 1058 ├── insert-mapping: 1059 │ ├── y:4 => x:1 1060 │ └── column5:5 => y:2 1061 ├── update-mapping: 1062 │ └── upsert_y:10 => y:2 1063 ├── cardinality: [0 - 0] 1064 ├── volatile, side-effects, mutations 1065 └── project 1066 ├── columns: upsert_y:10 y:4!null column5:5 x:6 y:7 1067 ├── cardinality: [0 - 1] 1068 ├── key: () 1069 ├── fd: ()-->(4-7,10) 1070 ├── left-join (hash) 1071 │ ├── columns: y:4!null column5:5 x:6 y:7 1072 │ ├── cardinality: [0 - 1] 1073 │ ├── key: () 1074 │ ├── fd: ()-->(4-7) 1075 │ ├── max1-row 1076 │ │ ├── columns: y:4!null column5:5 1077 │ │ ├── error: "UPSERT or INSERT...ON CONFLICT command cannot affect row a second time" 1078 │ │ ├── cardinality: [0 - 1] 1079 │ │ ├── key: () 1080 │ │ ├── fd: ()-->(4,5) 1081 │ │ └── project 1082 │ │ ├── columns: column5:5 y:4!null 1083 │ │ ├── fd: ()-->(4,5) 1084 │ │ ├── select 1085 │ │ │ ├── columns: y:4!null 1086 │ │ │ ├── fd: ()-->(4) 1087 │ │ │ ├── scan xy 1088 │ │ │ │ └── columns: y:4 1089 │ │ │ └── filters 1090 │ │ │ └── y:4 = 0 [outer=(4), constraints=(/4: [/0 - /0]; tight), fd=()-->(4)] 1091 │ │ └── projections 1092 │ │ └── CAST(NULL AS INT8) [as=column5:5] 1093 │ ├── scan xy 1094 │ │ ├── columns: x:6!null y:7 1095 │ │ ├── key: (6) 1096 │ │ └── fd: (6)-->(7) 1097 │ └── filters 1098 │ └── y:4 = x:6 [outer=(4,6), constraints=(/4: (/NULL - ]; /6: (/NULL - ]), fd=(4)==(6), (6)==(4)] 1099 └── projections 1100 └── CASE WHEN x:6 IS NULL THEN column5:5 ELSE 1 END [as=upsert_y:10, outer=(5,6)] 1101 1102 # UpsertDistinctOn should not reduce nullable constant grouping column. 1103 norm expect-not=ReduceNotNullGroupingCols 1104 INSERT INTO xy (x) 1105 SELECT y FROM xy WHERE y IS NULL 1106 ON CONFLICT (x) DO NOTHING 1107 ---- 1108 insert xy 1109 ├── columns: <none> 1110 ├── insert-mapping: 1111 │ ├── y:4 => x:1 1112 │ └── column5:5 => y:2 1113 ├── cardinality: [0 - 0] 1114 ├── volatile, side-effects, mutations 1115 └── upsert-distinct-on 1116 ├── columns: y:4 column5:5 1117 ├── grouping columns: y:4 1118 ├── lax-key: (4) 1119 ├── fd: ()-->(4,5) 1120 ├── select 1121 │ ├── columns: y:4 column5:5 x:6 1122 │ ├── fd: ()-->(4-6) 1123 │ ├── left-join (hash) 1124 │ │ ├── columns: y:4 column5:5 x:6 1125 │ │ ├── fd: ()-->(4,5) 1126 │ │ ├── project 1127 │ │ │ ├── columns: column5:5 y:4 1128 │ │ │ ├── fd: ()-->(4,5) 1129 │ │ │ ├── select 1130 │ │ │ │ ├── columns: y:4 1131 │ │ │ │ ├── fd: ()-->(4) 1132 │ │ │ │ ├── scan xy 1133 │ │ │ │ │ └── columns: y:4 1134 │ │ │ │ └── filters 1135 │ │ │ │ └── y:4 IS NULL [outer=(4), constraints=(/4: [/NULL - /NULL]; tight), fd=()-->(4)] 1136 │ │ │ └── projections 1137 │ │ │ └── CAST(NULL AS INT8) [as=column5:5] 1138 │ │ ├── scan xy 1139 │ │ │ ├── columns: x:6!null 1140 │ │ │ └── key: (6) 1141 │ │ └── filters 1142 │ │ └── y:4 = x:6 [outer=(4,6), constraints=(/4: (/NULL - ]; /6: (/NULL - ]), fd=(4)==(6), (6)==(4)] 1143 │ └── filters 1144 │ └── x:6 IS NULL [outer=(6), constraints=(/6: [/NULL - /NULL]; tight), fd=()-->(6)] 1145 └── aggregations 1146 └── first-agg [as=column5:5, outer=(5)] 1147 └── column5:5 1148 1149 # EnsureUpsertDistinctOn should not reduce nullable constant grouping column. 1150 norm expect-not=ReduceNotNullGroupingCols 1151 INSERT INTO xy (x) 1152 SELECT y FROM xy WHERE y IS NULL 1153 ON CONFLICT (x) DO UPDATE SET y=1 1154 ---- 1155 upsert xy 1156 ├── columns: <none> 1157 ├── canary column: 6 1158 ├── fetch columns: x:6 y:7 1159 ├── insert-mapping: 1160 │ ├── y:4 => x:1 1161 │ └── column5:5 => y:2 1162 ├── update-mapping: 1163 │ └── upsert_y:10 => y:2 1164 ├── cardinality: [0 - 0] 1165 ├── volatile, side-effects, mutations 1166 └── project 1167 ├── columns: upsert_y:10 y:4 column5:5 x:6 y:7 1168 ├── lax-key: (4,6) 1169 ├── fd: ()-->(4,5), (6)-->(7), (6)-->(10) 1170 ├── left-join (hash) 1171 │ ├── columns: y:4 column5:5 x:6 y:7 1172 │ ├── lax-key: (4,6) 1173 │ ├── fd: ()-->(4,5), (6)-->(7) 1174 │ ├── ensure-upsert-distinct-on 1175 │ │ ├── columns: y:4 column5:5 1176 │ │ ├── grouping columns: y:4 1177 │ │ ├── error: "UPSERT or INSERT...ON CONFLICT command cannot affect row a second time" 1178 │ │ ├── lax-key: (4) 1179 │ │ ├── fd: ()-->(4,5) 1180 │ │ ├── project 1181 │ │ │ ├── columns: column5:5 y:4 1182 │ │ │ ├── fd: ()-->(4,5) 1183 │ │ │ ├── select 1184 │ │ │ │ ├── columns: y:4 1185 │ │ │ │ ├── fd: ()-->(4) 1186 │ │ │ │ ├── scan xy 1187 │ │ │ │ │ └── columns: y:4 1188 │ │ │ │ └── filters 1189 │ │ │ │ └── y:4 IS NULL [outer=(4), constraints=(/4: [/NULL - /NULL]; tight), fd=()-->(4)] 1190 │ │ │ └── projections 1191 │ │ │ └── CAST(NULL AS INT8) [as=column5:5] 1192 │ │ └── aggregations 1193 │ │ └── first-agg [as=column5:5, outer=(5)] 1194 │ │ └── column5:5 1195 │ ├── scan xy 1196 │ │ ├── columns: x:6!null y:7 1197 │ │ ├── key: (6) 1198 │ │ └── fd: (6)-->(7) 1199 │ └── filters 1200 │ └── y:4 = x:6 [outer=(4,6), constraints=(/4: (/NULL - ]; /6: (/NULL - ]), fd=(4)==(6), (6)==(4)] 1201 └── projections 1202 └── CASE WHEN x:6 IS NULL THEN column5:5 ELSE 1 END [as=upsert_y:10, outer=(5,6)] 1203 1204 # Test removal of 2/3 grouping columns. 1205 norm expect=ReduceNotNullGroupingCols 1206 INSERT INTO abc (a, b, c) 1207 SELECT 1, b, 2 FROM abc 1208 ON CONFLICT (a, b, c) DO UPDATE SET a=1 1209 ---- 1210 upsert abc 1211 ├── columns: <none> 1212 ├── canary column: 9 1213 ├── fetch columns: a:9 b:10 c:11 1214 ├── insert-mapping: 1215 │ ├── "?column?":7 => a:1 1216 │ ├── b:5 => b:2 1217 │ └── "?column?":8 => c:3 1218 ├── update-mapping: 1219 │ └── "?column?":7 => a:1 1220 ├── cardinality: [0 - 0] 1221 ├── volatile, side-effects, mutations 1222 └── left-join (hash) 1223 ├── columns: b:5!null "?column?":7!null "?column?":8!null a:9 b:10 c:11 1224 ├── key: (5) 1225 ├── fd: ()-->(7,8), (5)-->(9-11) 1226 ├── ensure-upsert-distinct-on 1227 │ ├── columns: b:5!null "?column?":7!null "?column?":8!null 1228 │ ├── grouping columns: b:5!null 1229 │ ├── error: "UPSERT or INSERT...ON CONFLICT command cannot affect row a second time" 1230 │ ├── key: (5) 1231 │ ├── fd: ()-->(7,8) 1232 │ ├── project 1233 │ │ ├── columns: "?column?":7!null "?column?":8!null b:5!null 1234 │ │ ├── fd: ()-->(7,8) 1235 │ │ ├── scan abc 1236 │ │ │ └── columns: b:5!null 1237 │ │ └── projections 1238 │ │ ├── 1 [as="?column?":7] 1239 │ │ └── 2 [as="?column?":8] 1240 │ └── aggregations 1241 │ ├── const-agg [as="?column?":7, outer=(7)] 1242 │ │ └── "?column?":7 1243 │ └── const-agg [as="?column?":8, outer=(8)] 1244 │ └── "?column?":8 1245 ├── scan abc 1246 │ ├── columns: a:9!null b:10!null c:11!null 1247 │ └── key: (9-11) 1248 └── filters 1249 ├── "?column?":7 = a:9 [outer=(7,9), constraints=(/7: (/NULL - ]; /9: (/NULL - ]), fd=(7)==(9), (9)==(7)] 1250 ├── b:5 = b:10 [outer=(5,10), constraints=(/5: (/NULL - ]; /10: (/NULL - ]), fd=(5)==(10), (10)==(5)] 1251 └── "?column?":8 = c:11 [outer=(8,11), constraints=(/8: (/NULL - ]; /11: (/NULL - ]), fd=(8)==(11), (11)==(8)] 1252 1253 # Test removal of not-null column, but not nullable column. 1254 norm expect=ReduceNotNullGroupingCols 1255 INSERT INTO abc 1256 SELECT NULL, b, c FROM abc WHERE b=1 1257 ON CONFLICT (a, b, c) DO UPDATE SET c=2 1258 ---- 1259 upsert abc 1260 ├── columns: <none> 1261 ├── canary column: 8 1262 ├── fetch columns: a:8 b:9 c:10 1263 ├── insert-mapping: 1264 │ ├── "?column?":7 => a:1 1265 │ ├── b:5 => b:2 1266 │ └── c:6 => c:3 1267 ├── update-mapping: 1268 │ └── upsert_c:14 => c:3 1269 ├── cardinality: [0 - 0] 1270 ├── volatile, side-effects, mutations 1271 └── project 1272 ├── columns: upsert_c:14!null b:5!null c:6!null "?column?":7 a:8 b:9 c:10 1273 ├── lax-key: (6-10) 1274 ├── fd: ()-->(5,7), (6,8)-->(14) 1275 ├── left-join (hash) 1276 │ ├── columns: b:5!null c:6!null "?column?":7 a:8 b:9 c:10 1277 │ ├── lax-key: (6-10) 1278 │ ├── fd: ()-->(5,7) 1279 │ ├── ensure-upsert-distinct-on 1280 │ │ ├── columns: b:5!null c:6!null "?column?":7 1281 │ │ ├── grouping columns: c:6!null "?column?":7 1282 │ │ ├── error: "UPSERT or INSERT...ON CONFLICT command cannot affect row a second time" 1283 │ │ ├── lax-key: (6,7) 1284 │ │ ├── fd: ()-->(5,7) 1285 │ │ ├── project 1286 │ │ │ ├── columns: "?column?":7 b:5!null c:6!null 1287 │ │ │ ├── fd: ()-->(5,7) 1288 │ │ │ ├── select 1289 │ │ │ │ ├── columns: b:5!null c:6!null 1290 │ │ │ │ ├── fd: ()-->(5) 1291 │ │ │ │ ├── scan abc 1292 │ │ │ │ │ └── columns: b:5!null c:6!null 1293 │ │ │ │ └── filters 1294 │ │ │ │ └── b:5 = 1 [outer=(5), constraints=(/5: [/1 - /1]; tight), fd=()-->(5)] 1295 │ │ │ └── projections 1296 │ │ │ └── CAST(NULL AS INT8) [as="?column?":7] 1297 │ │ └── aggregations 1298 │ │ └── const-agg [as=b:5, outer=(5)] 1299 │ │ └── b:5 1300 │ ├── scan abc 1301 │ │ ├── columns: a:8!null b:9!null c:10!null 1302 │ │ └── key: (8-10) 1303 │ └── filters 1304 │ ├── "?column?":7 = a:8 [outer=(7,8), constraints=(/7: (/NULL - ]; /8: (/NULL - ]), fd=(7)==(8), (8)==(7)] 1305 │ ├── b:5 = b:9 [outer=(5,9), constraints=(/5: (/NULL - ]; /9: (/NULL - ]), fd=(5)==(9), (9)==(5)] 1306 │ └── c:6 = c:10 [outer=(6,10), constraints=(/6: (/NULL - ]; /10: (/NULL - ]), fd=(6)==(10), (10)==(6)] 1307 └── projections 1308 └── CASE WHEN a:8 IS NULL THEN c:6 ELSE 2 END [as=upsert_c:14, outer=(6,8)] 1309 1310 # -------------------------------------------------- 1311 # EliminateAggDistinctForKeys 1312 # -------------------------------------------------- 1313 1314 # ScalarGroupBy with key argument. Only the first aggregation can be 1315 # simplified. 1316 norm expect=EliminateAggDistinctForKeys 1317 SELECT sum(DISTINCT k), sum(DISTINCT i) FROM a 1318 ---- 1319 scalar-group-by 1320 ├── columns: sum:6 sum:7 1321 ├── cardinality: [1 - 1] 1322 ├── key: () 1323 ├── fd: ()-->(6,7) 1324 ├── scan a 1325 │ ├── columns: k:1!null i:2!null 1326 │ ├── key: (1) 1327 │ └── fd: (1)-->(2) 1328 └── aggregations 1329 ├── sum [as=sum:6, outer=(1)] 1330 │ └── k:1 1331 └── agg-distinct [as=sum:7, outer=(2)] 1332 └── sum 1333 └── i:2 1334 1335 norm expect=EliminateAggDistinctForKeys 1336 SELECT string_agg(DISTINCT s, ', ') FROM s 1337 ---- 1338 scalar-group-by 1339 ├── columns: string_agg:3 1340 ├── cardinality: [1 - 1] 1341 ├── key: () 1342 ├── fd: ()-->(3) 1343 ├── project 1344 │ ├── columns: column2:2!null s:1!null 1345 │ ├── key: (1) 1346 │ ├── fd: ()-->(2) 1347 │ ├── scan s 1348 │ │ ├── columns: s:1!null 1349 │ │ └── key: (1) 1350 │ └── projections 1351 │ └── ', ' [as=column2:2] 1352 └── aggregations 1353 └── string-agg [as=string_agg:3, outer=(1,2)] 1354 ├── s:1 1355 └── column2:2 1356 1357 # GroupBy with key argument. 1358 norm expect=EliminateAggDistinctForKeys 1359 SELECT sum(DISTINCT k) FROM a GROUP BY i 1360 ---- 1361 project 1362 ├── columns: sum:6!null 1363 └── group-by 1364 ├── columns: i:2!null sum:6!null 1365 ├── grouping columns: i:2!null 1366 ├── key: (2) 1367 ├── fd: (2)-->(6) 1368 ├── scan a 1369 │ ├── columns: k:1!null i:2!null 1370 │ ├── key: (1) 1371 │ └── fd: (1)-->(2) 1372 └── aggregations 1373 └── sum [as=sum:6, outer=(1)] 1374 └── k:1 1375 1376 # GroupBy with no key. 1377 norm expect-not=EliminateAggDistinctForKeys 1378 SELECT sum(DISTINCT a) FROM abc GROUP BY b 1379 ---- 1380 project 1381 ├── columns: sum:4!null 1382 └── group-by 1383 ├── columns: b:2!null sum:4!null 1384 ├── grouping columns: b:2!null 1385 ├── key: (2) 1386 ├── fd: (2)-->(4) 1387 ├── scan abc 1388 │ └── columns: a:1!null b:2!null 1389 └── aggregations 1390 └── agg-distinct [as=sum:4, outer=(1)] 1391 └── sum 1392 └── a:1 1393 1394 # GroupBy with composite key formed by argument plus grouping columns. 1395 norm expect=EliminateAggDistinctForKeys 1396 SELECT sum(DISTINCT a) FROM abc GROUP BY b, c 1397 ---- 1398 project 1399 ├── columns: sum:4!null 1400 └── group-by 1401 ├── columns: b:2!null c:3!null sum:4!null 1402 ├── grouping columns: b:2!null c:3!null 1403 ├── key: (2,3) 1404 ├── fd: (2,3)-->(4) 1405 ├── scan abc 1406 │ ├── columns: a:1!null b:2!null c:3!null 1407 │ └── key: (1-3) 1408 └── aggregations 1409 └── sum [as=sum:4, outer=(1)] 1410 └── a:1 1411 1412 # GroupBy with multiple aggregations simplified. 1413 norm expect=EliminateAggDistinctForKeys 1414 SELECT sum(DISTINCT i), avg(DISTINCT f) FROM a GROUP BY k 1415 ---- 1416 project 1417 ├── columns: sum:6!null avg:7 1418 └── group-by 1419 ├── columns: k:1!null sum:6!null avg:7 1420 ├── grouping columns: k:1!null 1421 ├── key: (1) 1422 ├── fd: (1)-->(6,7) 1423 ├── scan a 1424 │ ├── columns: k:1!null i:2!null f:3 1425 │ ├── key: (1) 1426 │ └── fd: (1)-->(2,3), (2,3)~~>(1) 1427 └── aggregations 1428 ├── sum [as=sum:6, outer=(2)] 1429 │ └── i:2 1430 └── avg [as=avg:7, outer=(3)] 1431 └── f:3 1432 1433 # GroupBy where only some aggregations are simplified (the table has 1434 # keys u,v and v,w). 1435 norm expect=EliminateAggDistinctForKeys 1436 SELECT sum(DISTINCT u), stddev(DISTINCT w), avg(DISTINCT z) FROM uvwz GROUP BY v 1437 ---- 1438 project 1439 ├── columns: sum:6!null stddev:7 avg:8!null 1440 └── group-by 1441 ├── columns: v:2!null sum:6!null stddev:7 avg:8!null 1442 ├── grouping columns: v:2!null 1443 ├── key: (2) 1444 ├── fd: (2)-->(6-8) 1445 ├── scan uvwz 1446 │ ├── columns: u:1!null v:2!null w:3!null z:4!null 1447 │ ├── key: (2,3) 1448 │ └── fd: (1,2)-->(3,4), (2,3)-->(1,4) 1449 └── aggregations 1450 ├── sum [as=sum:6, outer=(1)] 1451 │ └── u:1 1452 ├── std-dev [as=stddev:7, outer=(3)] 1453 │ └── w:3 1454 └── agg-distinct [as=avg:8, outer=(4)] 1455 └── avg 1456 └── z:4 1457 1458 # -------------------------------------------------- 1459 # EliminateAggFilteredDistinctForKeys 1460 # -------------------------------------------------- 1461 1462 # ScalarGroupBy with key argument. Only the first aggregation can be 1463 # simplified. 1464 norm expect=EliminateAggFilteredDistinctForKeys 1465 SELECT sum(DISTINCT k) FILTER (WHERE k > 0), sum(DISTINCT i) FILTER (WHERE i > 0) FROM a 1466 ---- 1467 scalar-group-by 1468 ├── columns: sum:7 sum:9 1469 ├── cardinality: [1 - 1] 1470 ├── key: () 1471 ├── fd: ()-->(7,9) 1472 ├── project 1473 │ ├── columns: column6:6!null column8:8!null k:1!null i:2!null 1474 │ ├── key: (1) 1475 │ ├── fd: (1)-->(2,6), (2)-->(8) 1476 │ ├── scan a 1477 │ │ ├── columns: k:1!null i:2!null 1478 │ │ ├── key: (1) 1479 │ │ └── fd: (1)-->(2) 1480 │ └── projections 1481 │ ├── k:1 > 0 [as=column6:6, outer=(1)] 1482 │ └── i:2 > 0 [as=column8:8, outer=(2)] 1483 └── aggregations 1484 ├── agg-filter [as=sum:7, outer=(1,6)] 1485 │ ├── sum 1486 │ │ └── k:1 1487 │ └── column6:6 1488 └── agg-filter [as=sum:9, outer=(2,8)] 1489 ├── agg-distinct 1490 │ └── sum 1491 │ └── i:2 1492 └── column8:8 1493 1494 norm expect=EliminateAggFilteredDistinctForKeys 1495 SELECT string_agg(DISTINCT s, ',') FILTER (WHERE s > 'a') FROM s 1496 ---- 1497 scalar-group-by 1498 ├── columns: string_agg:4 1499 ├── cardinality: [1 - 1] 1500 ├── key: () 1501 ├── fd: ()-->(4) 1502 ├── project 1503 │ ├── columns: column2:2!null s:1!null 1504 │ ├── key: (1) 1505 │ ├── fd: ()-->(2) 1506 │ ├── select 1507 │ │ ├── columns: s:1!null 1508 │ │ ├── key: (1) 1509 │ │ ├── scan s 1510 │ │ │ ├── columns: s:1!null 1511 │ │ │ └── key: (1) 1512 │ │ └── filters 1513 │ │ └── s:1 > 'a' [outer=(1), constraints=(/1: [/e'a\x00' - ]; tight)] 1514 │ └── projections 1515 │ └── ',' [as=column2:2] 1516 └── aggregations 1517 └── string-agg [as=string_agg:4, outer=(1,2)] 1518 ├── s:1 1519 └── column2:2 1520 1521 # GroupBy with key argument. 1522 norm expect=EliminateAggFilteredDistinctForKeys 1523 SELECT sum(DISTINCT k) FILTER (WHERE f > 0) FROM a GROUP BY i 1524 ---- 1525 project 1526 ├── columns: sum:7 1527 └── group-by 1528 ├── columns: i:2!null sum:7 1529 ├── grouping columns: i:2!null 1530 ├── key: (2) 1531 ├── fd: (2)-->(7) 1532 ├── project 1533 │ ├── columns: column6:6 k:1!null i:2!null 1534 │ ├── key: (1) 1535 │ ├── fd: (1)-->(2,6) 1536 │ ├── scan a 1537 │ │ ├── columns: k:1!null i:2!null f:3 1538 │ │ ├── key: (1) 1539 │ │ └── fd: (1)-->(2,3), (2,3)~~>(1) 1540 │ └── projections 1541 │ └── f:3 > 0.0 [as=column6:6, outer=(3)] 1542 └── aggregations 1543 └── agg-filter [as=sum:7, outer=(1,6)] 1544 ├── sum 1545 │ └── k:1 1546 └── column6:6 1547 1548 # GroupBy with no key. 1549 norm expect-not=EliminateAggFilteredDistinctForKeys 1550 SELECT sum(DISTINCT a) FILTER (WHERE c > 0) FROM abc GROUP BY b 1551 ---- 1552 project 1553 ├── columns: sum:5 1554 └── group-by 1555 ├── columns: b:2!null sum:5 1556 ├── grouping columns: b:2!null 1557 ├── key: (2) 1558 ├── fd: (2)-->(5) 1559 ├── project 1560 │ ├── columns: column4:4!null a:1!null b:2!null 1561 │ ├── scan abc 1562 │ │ ├── columns: a:1!null b:2!null c:3!null 1563 │ │ └── key: (1-3) 1564 │ └── projections 1565 │ └── c:3 > 0 [as=column4:4, outer=(3)] 1566 └── aggregations 1567 └── agg-filter [as=sum:5, outer=(1,4)] 1568 ├── agg-distinct 1569 │ └── sum 1570 │ └── a:1 1571 └── column4:4 1572 1573 # GroupBy with composite key formed by argument plus grouping columns. 1574 norm expect=EliminateAggFilteredDistinctForKeys 1575 SELECT sum(DISTINCT a) FILTER (WHERE c > 0) FROM abc GROUP BY b, c 1576 ---- 1577 project 1578 ├── columns: sum:5 1579 └── group-by 1580 ├── columns: b:2!null c:3!null sum:5 1581 ├── grouping columns: b:2!null c:3!null 1582 ├── key: (2,3) 1583 ├── fd: (2,3)-->(5) 1584 ├── project 1585 │ ├── columns: column4:4!null a:1!null b:2!null c:3!null 1586 │ ├── key: (1-3) 1587 │ ├── fd: (3)-->(4) 1588 │ ├── scan abc 1589 │ │ ├── columns: a:1!null b:2!null c:3!null 1590 │ │ └── key: (1-3) 1591 │ └── projections 1592 │ └── c:3 > 0 [as=column4:4, outer=(3)] 1593 └── aggregations 1594 └── agg-filter [as=sum:5, outer=(1,4)] 1595 ├── sum 1596 │ └── a:1 1597 └── column4:4 1598 1599 # GroupBy with multiple aggregations simplified. 1600 norm expect=EliminateAggFilteredDistinctForKeys 1601 SELECT sum(DISTINCT i) FILTER (WHERE f > 0), avg(DISTINCT f) FILTER (WHERE i > 0) FROM a GROUP BY k 1602 ---- 1603 project 1604 ├── columns: sum:7 avg:9 1605 └── group-by 1606 ├── columns: k:1!null sum:7 avg:9 1607 ├── grouping columns: k:1!null 1608 ├── key: (1) 1609 ├── fd: (1)-->(7,9) 1610 ├── project 1611 │ ├── columns: column6:6 column8:8!null k:1!null i:2!null f:3 1612 │ ├── key: (1) 1613 │ ├── fd: (1)-->(2,3,6), (2,3)~~>(1), (2)-->(8) 1614 │ ├── scan a 1615 │ │ ├── columns: k:1!null i:2!null f:3 1616 │ │ ├── key: (1) 1617 │ │ └── fd: (1)-->(2,3), (2,3)~~>(1) 1618 │ └── projections 1619 │ ├── f:3 > 0.0 [as=column6:6, outer=(3)] 1620 │ └── i:2 > 0 [as=column8:8, outer=(2)] 1621 └── aggregations 1622 ├── agg-filter [as=sum:7, outer=(2,6)] 1623 │ ├── sum 1624 │ │ └── i:2 1625 │ └── column6:6 1626 └── agg-filter [as=avg:9, outer=(3,8)] 1627 ├── avg 1628 │ └── f:3 1629 └── column8:8 1630 1631 # GroupBy where only some aggregations are simplified (the table has 1632 # keys u,v and v,w). 1633 norm expect=EliminateAggFilteredDistinctForKeys 1634 SELECT 1635 sum(DISTINCT u) FILTER (WHERE u > 0), 1636 stddev(DISTINCT w) FILTER (WHERE w > 0), 1637 avg(DISTINCT z) FILTER (WHERE z > 0) 1638 FROM uvwz 1639 GROUP BY v 1640 ---- 1641 project 1642 ├── columns: sum:7 stddev:9 avg:11 1643 └── group-by 1644 ├── columns: v:2!null sum:7 stddev:9 avg:11 1645 ├── grouping columns: v:2!null 1646 ├── key: (2) 1647 ├── fd: (2)-->(7,9,11) 1648 ├── project 1649 │ ├── columns: column6:6!null column8:8!null column10:10!null u:1!null v:2!null w:3!null z:4!null 1650 │ ├── key: (2,3) 1651 │ ├── fd: (1,2)-->(3,4), (2,3)-->(1,4), (1)-->(6), (3)-->(8), (4)-->(10) 1652 │ ├── scan uvwz 1653 │ │ ├── columns: u:1!null v:2!null w:3!null z:4!null 1654 │ │ ├── key: (2,3) 1655 │ │ └── fd: (1,2)-->(3,4), (2,3)-->(1,4) 1656 │ └── projections 1657 │ ├── u:1 > 0 [as=column6:6, outer=(1)] 1658 │ ├── w:3 > 0 [as=column8:8, outer=(3)] 1659 │ └── z:4 > 0 [as=column10:10, outer=(4)] 1660 └── aggregations 1661 ├── agg-filter [as=sum:7, outer=(1,6)] 1662 │ ├── sum 1663 │ │ └── u:1 1664 │ └── column6:6 1665 ├── agg-filter [as=stddev:9, outer=(3,8)] 1666 │ ├── std-dev 1667 │ │ └── w:3 1668 │ └── column8:8 1669 └── agg-filter [as=avg:11, outer=(4,10)] 1670 ├── agg-distinct 1671 │ └── avg 1672 │ └── z:4 1673 └── column10:10 1674 1675 # -------------------------------------------------- 1676 # EliminateDistinctNoColumns 1677 # -------------------------------------------------- 1678 1679 norm expect=EliminateDistinctNoColumns 1680 SELECT DISTINCT ON (a) a, b FROM abc WHERE a = 1 1681 ---- 1682 limit 1683 ├── columns: a:1!null b:2!null 1684 ├── cardinality: [0 - 1] 1685 ├── key: () 1686 ├── fd: ()-->(1,2) 1687 ├── select 1688 │ ├── columns: a:1!null b:2!null 1689 │ ├── fd: ()-->(1) 1690 │ ├── limit hint: 1.00 1691 │ ├── scan abc 1692 │ │ ├── columns: a:1!null b:2!null 1693 │ │ └── limit hint: 100.00 1694 │ └── filters 1695 │ └── a:1 = 1 [outer=(1), constraints=(/1: [/1 - /1]; tight), fd=()-->(1)] 1696 └── 1 1697 1698 norm expect=EliminateDistinctNoColumns 1699 SELECT DISTINCT ON (b) b, c FROM abc WHERE b = 1 ORDER BY b, c 1700 ---- 1701 limit 1702 ├── columns: b:2!null c:3!null 1703 ├── internal-ordering: +3 opt(2) 1704 ├── cardinality: [0 - 1] 1705 ├── key: () 1706 ├── fd: ()-->(2,3) 1707 ├── sort 1708 │ ├── columns: b:2!null c:3!null 1709 │ ├── fd: ()-->(2) 1710 │ ├── ordering: +3 opt(2) [actual: +3] 1711 │ ├── limit hint: 1.00 1712 │ └── select 1713 │ ├── columns: b:2!null c:3!null 1714 │ ├── fd: ()-->(2) 1715 │ ├── scan abc 1716 │ │ └── columns: b:2!null c:3!null 1717 │ └── filters 1718 │ └── b:2 = 1 [outer=(2), constraints=(/2: [/1 - /1]; tight), fd=()-->(2)] 1719 └── 1 1720 1721 norm expect=EliminateDistinctNoColumns 1722 INSERT INTO a (k, i, s) SELECT 1, i, 'foo' FROM a WHERE i = 1 1723 ON CONFLICT (s, i) DO NOTHING 1724 ---- 1725 insert a 1726 ├── columns: <none> 1727 ├── insert-mapping: 1728 │ ├── "?column?":11 => k:1 1729 │ ├── i:7 => i:2 1730 │ ├── column13:13 => f:3 1731 │ ├── "?column?":12 => s:4 1732 │ └── column14:14 => j:5 1733 ├── cardinality: [0 - 0] 1734 ├── volatile, side-effects, mutations 1735 └── project 1736 ├── columns: i:7!null "?column?":11!null "?column?":12!null column13:13 column14:14 1737 ├── cardinality: [0 - 1] 1738 ├── key: () 1739 ├── fd: ()-->(7,11-14) 1740 └── limit 1741 ├── columns: i:7!null "?column?":11!null "?column?":12!null column13:13 column14:14 i:16 s:18 1742 ├── cardinality: [0 - 1] 1743 ├── key: () 1744 ├── fd: ()-->(7,11-14,16,18) 1745 ├── select 1746 │ ├── columns: i:7!null "?column?":11!null "?column?":12!null column13:13 column14:14 i:16 s:18 1747 │ ├── fd: ()-->(7,11-14,18) 1748 │ ├── limit hint: 1.00 1749 │ ├── left-join (hash) 1750 │ │ ├── columns: i:7!null "?column?":11!null "?column?":12!null column13:13 column14:14 i:16 s:18 1751 │ │ ├── fd: ()-->(7,11-14) 1752 │ │ ├── limit hint: 1.00 1753 │ │ ├── project 1754 │ │ │ ├── columns: column13:13 column14:14 "?column?":11!null "?column?":12!null i:7!null 1755 │ │ │ ├── fd: ()-->(7,11-14) 1756 │ │ │ ├── select 1757 │ │ │ │ ├── columns: i:7!null 1758 │ │ │ │ ├── fd: ()-->(7) 1759 │ │ │ │ ├── scan a 1760 │ │ │ │ │ └── columns: i:7!null 1761 │ │ │ │ └── filters 1762 │ │ │ │ └── i:7 = 1 [outer=(7), constraints=(/7: [/1 - /1]; tight), fd=()-->(7)] 1763 │ │ │ └── projections 1764 │ │ │ ├── CAST(NULL AS FLOAT8) [as=column13:13] 1765 │ │ │ ├── CAST(NULL AS JSONB) [as=column14:14] 1766 │ │ │ ├── 1 [as="?column?":11] 1767 │ │ │ └── 'foo' [as="?column?":12] 1768 │ │ ├── select 1769 │ │ │ ├── columns: i:16!null s:18!null 1770 │ │ │ ├── key: (16) 1771 │ │ │ ├── fd: ()-->(18) 1772 │ │ │ ├── scan a 1773 │ │ │ │ ├── columns: i:16!null s:18!null 1774 │ │ │ │ └── key: (16,18) 1775 │ │ │ └── filters 1776 │ │ │ └── s:18 = 'foo' [outer=(18), constraints=(/18: [/'foo' - /'foo']; tight), fd=()-->(18)] 1777 │ │ └── filters 1778 │ │ └── i:7 = i:16 [outer=(7,16), constraints=(/7: (/NULL - ]; /16: (/NULL - ]), fd=(7)==(16), (16)==(7)] 1779 │ └── filters 1780 │ └── s:18 IS NULL [outer=(18), constraints=(/18: [/NULL - /NULL]; tight), fd=()-->(18)] 1781 └── 1 1782 1783 # -------------------------------------------------- 1784 # EliminateEnsureDistinctNoColumns 1785 # -------------------------------------------------- 1786 1787 # EnsureDistinctOn case. 1788 norm expect=EliminateEnsureDistinctNoColumns 1789 SELECT (SELECT x FROM xy WHERE y=i) FROM a WHERE k=5 1790 ---- 1791 project 1792 ├── columns: x:8 1793 ├── cardinality: [0 - 1] 1794 ├── key: () 1795 ├── fd: ()-->(8) 1796 ├── max1-row 1797 │ ├── columns: k:1!null i:2!null xy.x:6 y:7 1798 │ ├── error: "more than one row returned by a subquery used as an expression" 1799 │ ├── cardinality: [0 - 1] 1800 │ ├── key: () 1801 │ ├── fd: ()-->(1,2,6,7) 1802 │ └── left-join (hash) 1803 │ ├── columns: k:1!null i:2!null xy.x:6 y:7 1804 │ ├── key: (6) 1805 │ ├── fd: ()-->(1,2), (6)-->(7) 1806 │ ├── select 1807 │ │ ├── columns: k:1!null i:2!null 1808 │ │ ├── cardinality: [0 - 1] 1809 │ │ ├── key: () 1810 │ │ ├── fd: ()-->(1,2) 1811 │ │ ├── scan a 1812 │ │ │ ├── columns: k:1!null i:2!null 1813 │ │ │ ├── key: (1) 1814 │ │ │ └── fd: (1)-->(2) 1815 │ │ └── filters 1816 │ │ └── k:1 = 5 [outer=(1), constraints=(/1: [/5 - /5]; tight), fd=()-->(1)] 1817 │ ├── scan xy 1818 │ │ ├── columns: xy.x:6!null y:7 1819 │ │ ├── key: (6) 1820 │ │ └── fd: (6)-->(7) 1821 │ └── filters 1822 │ └── y:7 = i:2 [outer=(2,7), constraints=(/2: (/NULL - ]; /7: (/NULL - ]), fd=(2)==(7), (7)==(2)] 1823 └── projections 1824 └── xy.x:6 [as=x:8, outer=(6)] 1825 1826 # EnsureUpsertDistinctOn case. 1827 norm expect=EliminateEnsureDistinctNoColumns 1828 INSERT INTO a (k, i, s) SELECT 1, i, 'foo' FROM a WHERE i = 1 1829 ON CONFLICT (s, i) DO UPDATE SET f=1.1 1830 ---- 1831 upsert a 1832 ├── columns: <none> 1833 ├── canary column: 15 1834 ├── fetch columns: k:15 i:16 f:17 s:18 j:19 1835 ├── insert-mapping: 1836 │ ├── "?column?":11 => k:1 1837 │ ├── i:7 => i:2 1838 │ ├── column13:13 => f:3 1839 │ ├── "?column?":12 => s:4 1840 │ └── column14:14 => j:5 1841 ├── update-mapping: 1842 │ └── upsert_f:23 => f:3 1843 ├── cardinality: [0 - 0] 1844 ├── volatile, side-effects, mutations 1845 └── project 1846 ├── columns: upsert_f:23 i:7!null "?column?":11!null "?column?":12!null column13:13 column14:14 k:15 i:16 f:17 s:18 j:19 1847 ├── cardinality: [0 - 1] 1848 ├── key: () 1849 ├── fd: ()-->(7,11-19,23) 1850 ├── left-join (hash) 1851 │ ├── columns: i:7!null "?column?":11!null "?column?":12!null column13:13 column14:14 k:15 i:16 f:17 s:18 j:19 1852 │ ├── cardinality: [0 - 1] 1853 │ ├── key: () 1854 │ ├── fd: ()-->(7,11-19) 1855 │ ├── max1-row 1856 │ │ ├── columns: i:7!null "?column?":11!null "?column?":12!null column13:13 column14:14 1857 │ │ ├── error: "UPSERT or INSERT...ON CONFLICT command cannot affect row a second time" 1858 │ │ ├── cardinality: [0 - 1] 1859 │ │ ├── key: () 1860 │ │ ├── fd: ()-->(7,11-14) 1861 │ │ └── project 1862 │ │ ├── columns: column13:13 column14:14 "?column?":11!null "?column?":12!null i:7!null 1863 │ │ ├── fd: ()-->(7,11-14) 1864 │ │ ├── select 1865 │ │ │ ├── columns: i:7!null 1866 │ │ │ ├── fd: ()-->(7) 1867 │ │ │ ├── scan a 1868 │ │ │ │ └── columns: i:7!null 1869 │ │ │ └── filters 1870 │ │ │ └── i:7 = 1 [outer=(7), constraints=(/7: [/1 - /1]; tight), fd=()-->(7)] 1871 │ │ └── projections 1872 │ │ ├── CAST(NULL AS FLOAT8) [as=column13:13] 1873 │ │ ├── CAST(NULL AS JSONB) [as=column14:14] 1874 │ │ ├── 1 [as="?column?":11] 1875 │ │ └── 'foo' [as="?column?":12] 1876 │ ├── scan a 1877 │ │ ├── columns: k:15!null i:16!null f:17 s:18!null j:19 1878 │ │ ├── key: (15) 1879 │ │ └── fd: (15)-->(16-19), (16,18)-->(15,17,19), (16,17)~~>(15,18,19) 1880 │ └── filters 1881 │ ├── i:7 = i:16 [outer=(7,16), constraints=(/7: (/NULL - ]; /16: (/NULL - ]), fd=(7)==(16), (16)==(7)] 1882 │ └── "?column?":12 = s:18 [outer=(12,18), constraints=(/12: (/NULL - ]; /18: (/NULL - ]), fd=(12)==(18), (18)==(12)] 1883 └── projections 1884 └── CASE WHEN k:15 IS NULL THEN column13:13 ELSE 1.1 END [as=upsert_f:23, outer=(13,15)] 1885 1886 # -------------------------------------------------- 1887 # EliminateDistinctOnValues 1888 # -------------------------------------------------- 1889 1890 # Eliminate DistinctOn when its immediate input is a Values operator. 1891 norm expect=EliminateDistinctOnValues 1892 SELECT DISTINCT ON (x) * FROM (VALUES (1), (2)) t(x) 1893 ---- 1894 values 1895 ├── columns: x:1!null 1896 ├── cardinality: [2 - 2] 1897 ├── (1,) 1898 └── (2,) 1899 1900 # Eliminate DistinctOn when Values operator is below Project, Select, and 1901 # LeftJoin operators. 1902 norm expect=EliminateDistinctOnValues 1903 SELECT DISTINCT ON (x, y, z) *, x+1 1904 FROM (VALUES (1, 2, 3), (4, 5, 6)) t(x, y, z) 1905 LEFT JOIN (SELECT a, b, c FROM abc) 1906 ON a=x AND b=y AND c=z 1907 WHERE x > 100 OR b > 100 1908 ---- 1909 project 1910 ├── columns: x:1!null y:2!null z:3!null a:4 b:5 c:6 "?column?":7!null 1911 ├── fd: (1)-->(7) 1912 ├── select 1913 │ ├── columns: column1:1!null column2:2!null column3:3!null a:4 b:5 c:6 1914 │ ├── left-join (hash) 1915 │ │ ├── columns: column1:1!null column2:2!null column3:3!null a:4 b:5 c:6 1916 │ │ ├── cardinality: [2 - ] 1917 │ │ ├── values 1918 │ │ │ ├── columns: column1:1!null column2:2!null column3:3!null 1919 │ │ │ ├── cardinality: [2 - 2] 1920 │ │ │ ├── (1, 2, 3) 1921 │ │ │ └── (4, 5, 6) 1922 │ │ ├── scan abc 1923 │ │ │ ├── columns: a:4!null b:5!null c:6!null 1924 │ │ │ └── key: (4-6) 1925 │ │ └── filters 1926 │ │ ├── a:4 = column1:1 [outer=(1,4), constraints=(/1: (/NULL - ]; /4: (/NULL - ]), fd=(1)==(4), (4)==(1)] 1927 │ │ ├── b:5 = column2:2 [outer=(2,5), constraints=(/2: (/NULL - ]; /5: (/NULL - ]), fd=(2)==(5), (5)==(2)] 1928 │ │ └── c:6 = column3:3 [outer=(3,6), constraints=(/3: (/NULL - ]; /6: (/NULL - ]), fd=(3)==(6), (6)==(3)] 1929 │ └── filters 1930 │ └── (column1:1 > 100) OR (b:5 > 100) [outer=(1,5)] 1931 └── projections 1932 └── column1:1 + 1 [as="?column?":7, outer=(1)] 1933 1934 # Right input of left join does not have a key, so left side may have dups. 1935 norm expect-not=EliminateDistinctOnValues 1936 SELECT DISTINCT ON (x) * 1937 FROM (VALUES (1), (2)) t(x) 1938 LEFT JOIN (SELECT a FROM abc) 1939 ON a=x 1940 ---- 1941 distinct-on 1942 ├── columns: x:1!null a:2 1943 ├── grouping columns: column1:1!null 1944 ├── cardinality: [1 - ] 1945 ├── key: (1) 1946 ├── fd: (1)-->(2) 1947 ├── left-join (hash) 1948 │ ├── columns: column1:1!null a:2 1949 │ ├── cardinality: [2 - ] 1950 │ ├── values 1951 │ │ ├── columns: column1:1!null 1952 │ │ ├── cardinality: [2 - 2] 1953 │ │ ├── (1,) 1954 │ │ └── (2,) 1955 │ ├── scan abc 1956 │ │ └── columns: a:2!null 1957 │ └── filters 1958 │ └── a:2 = column1:1 [outer=(1,2), constraints=(/1: (/NULL - ]; /2: (/NULL - ]), fd=(1)==(2), (2)==(1)] 1959 └── aggregations 1960 └── first-agg [as=a:2, outer=(2)] 1961 └── a:2 1962 1963 # Left join does not join on all columns of the right input's key, so dups are 1964 # possible. 1965 norm expect-not=EliminateDistinctOnValues 1966 SELECT DISTINCT ON (x, y) * 1967 FROM (VALUES (1, 2), (3, 4)) t(x, y) 1968 LEFT JOIN (SELECT * FROM abc) 1969 ON x=a AND y=c 1970 ---- 1971 distinct-on 1972 ├── columns: x:1!null y:2!null a:3 b:4 c:5 1973 ├── grouping columns: column1:1!null column2:2!null 1974 ├── cardinality: [1 - ] 1975 ├── key: (1,2) 1976 ├── fd: (1,2)-->(3-5) 1977 ├── left-join (hash) 1978 │ ├── columns: column1:1!null column2:2!null a:3 b:4 c:5 1979 │ ├── cardinality: [2 - ] 1980 │ ├── values 1981 │ │ ├── columns: column1:1!null column2:2!null 1982 │ │ ├── cardinality: [2 - 2] 1983 │ │ ├── (1, 2) 1984 │ │ └── (3, 4) 1985 │ ├── scan abc 1986 │ │ ├── columns: a:3!null b:4!null c:5!null 1987 │ │ └── key: (3-5) 1988 │ └── filters 1989 │ ├── column1:1 = a:3 [outer=(1,3), constraints=(/1: (/NULL - ]; /3: (/NULL - ]), fd=(1)==(3), (3)==(1)] 1990 │ └── column2:2 = c:5 [outer=(2,5), constraints=(/2: (/NULL - ]; /5: (/NULL - ]), fd=(2)==(5), (5)==(2)] 1991 └── aggregations 1992 ├── first-agg [as=a:3, outer=(3)] 1993 │ └── a:3 1994 ├── first-agg [as=b:4, outer=(4)] 1995 │ └── b:4 1996 └── first-agg [as=c:5, outer=(5)] 1997 └── c:5 1998 1999 # Grouping columns are not passthrough Project columns. 2000 norm expect-not=EliminateDistinctOnValues 2001 SELECT DISTINCT ON (y) * 2002 FROM (SELECT x, x+1 AS y FROM (VALUES (1), (2)) t(x)) 2003 ---- 2004 distinct-on 2005 ├── columns: x:1!null y:2!null 2006 ├── grouping columns: y:2!null 2007 ├── cardinality: [1 - 2] 2008 ├── key: (2) 2009 ├── fd: (1)-->(2), (2)-->(1) 2010 ├── project 2011 │ ├── columns: y:2!null column1:1!null 2012 │ ├── cardinality: [2 - 2] 2013 │ ├── fd: (1)-->(2) 2014 │ ├── values 2015 │ │ ├── columns: column1:1!null 2016 │ │ ├── cardinality: [2 - 2] 2017 │ │ ├── (1,) 2018 │ │ └── (2,) 2019 │ └── projections 2020 │ └── column1:1 + 1 [as=y:2, outer=(1)] 2021 └── aggregations 2022 └── first-agg [as=column1:1, outer=(1)] 2023 └── column1:1 2024 2025 # Grouping columns are on the right side of a LeftJoin. 2026 norm expect-not=EliminateDistinctOnValues 2027 SELECT DISTINCT ON (x) * 2028 FROM (SELECT k FROM a) 2029 LEFT JOIN (VALUES (1), (2)) t(x) 2030 ON k=x 2031 ---- 2032 distinct-on 2033 ├── columns: k:1!null x:6 2034 ├── grouping columns: column1:6 2035 ├── key: (6) 2036 ├── fd: (6)-->(1) 2037 ├── left-join (hash) 2038 │ ├── columns: k:1!null column1:6 2039 │ ├── scan a 2040 │ │ ├── columns: k:1!null 2041 │ │ └── key: (1) 2042 │ ├── values 2043 │ │ ├── columns: column1:6!null 2044 │ │ ├── cardinality: [2 - 2] 2045 │ │ ├── (1,) 2046 │ │ └── (2,) 2047 │ └── filters 2048 │ └── k:1 = column1:6 [outer=(1,6), constraints=(/1: (/NULL - ]; /6: (/NULL - ]), fd=(1)==(6), (6)==(1)] 2049 └── aggregations 2050 └── first-agg [as=k:1, outer=(1)] 2051 └── k:1 2052 2053 # DistinctOn with multiple grouping columns should be eliminated when there are 2054 # not duplicate rows. 2055 norm expect=EliminateDistinctOnValues 2056 SELECT DISTINCT ON (b, c) * FROM (VALUES (1, 1, 1, 1), (1, 2, 2, 1)) t(a, b, c, d) 2057 ---- 2058 values 2059 ├── columns: a:1!null b:2!null c:3!null d:4!null 2060 ├── cardinality: [2 - 2] 2061 ├── (1, 1, 1, 1) 2062 └── (1, 2, 2, 1) 2063 2064 # Composite string type should be considered as not distinct. 2065 norm expect-not=EliminateDistinctOnValues 2066 SELECT DISTINCT ON (x) * FROM (VALUES ('ä' COLLATE en), (e'a\u0308' COLLATE en)) t(x) 2067 ---- 2068 distinct-on 2069 ├── columns: x:1!null 2070 ├── grouping columns: column1:1!null 2071 ├── cardinality: [1 - 2] 2072 ├── key: (1) 2073 └── values 2074 ├── columns: column1:1!null 2075 ├── cardinality: [2 - 2] 2076 ├── (e'\u00E4' COLLATE en,) 2077 └── (e'a\u0308' COLLATE en,) 2078 2079 # Composite decimal type should be considered as not distinct. 2080 norm expect-not=EliminateDistinctOnValues 2081 SELECT DISTINCT ON (x) * FROM (VALUES (1.0::decimal), (1.00::decimal)) t(x) 2082 ---- 2083 distinct-on 2084 ├── columns: x:1!null 2085 ├── grouping columns: column1:1!null 2086 ├── cardinality: [1 - 2] 2087 ├── key: (1) 2088 └── values 2089 ├── columns: column1:1!null 2090 ├── cardinality: [2 - 2] 2091 ├── (1.0,) 2092 └── (1.00,) 2093 2094 # Non-constant value should be considered as not distinct. 2095 norm expect-not=EliminateDistinctOnValues 2096 SELECT DISTINCT ON (x) * FROM (VALUES (1), (unique_rowid())) t(x) 2097 ---- 2098 distinct-on 2099 ├── columns: x:1 2100 ├── grouping columns: column1:1 2101 ├── cardinality: [1 - 2] 2102 ├── volatile, side-effects 2103 ├── key: (1) 2104 └── values 2105 ├── columns: column1:1 2106 ├── cardinality: [2 - 2] 2107 ├── volatile, side-effects 2108 ├── (1,) 2109 └── (unique_rowid(),) 2110 2111 # Tuple values are not handled. 2112 norm expect-not=EliminateDistinctOnValues 2113 SELECT DISTINCT ON (x) * FROM (VALUES ((1, 2, 3)), ((1, 2, 3))) t(x) 2114 ---- 2115 distinct-on 2116 ├── columns: x:1 2117 ├── grouping columns: column1:1 2118 ├── cardinality: [1 - 2] 2119 ├── key: (1) 2120 └── values 2121 ├── columns: column1:1 2122 ├── cardinality: [2 - 2] 2123 ├── ((1, 2, 3),) 2124 └── ((1, 2, 3),) 2125 2126 # DistinctOn should not be eliminated when there are duplicate rows. 2127 norm expect-not=EliminateDistinctOnValues 2128 SELECT DISTINCT ON (y, z) * FROM (VALUES (1, 1, 1), (2, 1, 1)) t(x, y, z) 2129 ---- 2130 distinct-on 2131 ├── columns: x:1!null y:2!null z:3!null 2132 ├── grouping columns: column2:2!null column3:3!null 2133 ├── cardinality: [1 - 2] 2134 ├── key: (2,3) 2135 ├── fd: (2,3)-->(1) 2136 ├── values 2137 │ ├── columns: column1:1!null column2:2!null column3:3!null 2138 │ ├── cardinality: [2 - 2] 2139 │ ├── (1, 1, 1) 2140 │ └── (2, 1, 1) 2141 └── aggregations 2142 └── first-agg [as=column1:1, outer=(1)] 2143 └── column1:1 2144 2145 # DistinctOn treats NULL values as not distinct, so it can't be eliminated when 2146 # there are duplicate NULL values. 2147 norm expect-not=EliminateDistinctOnValues 2148 SELECT DISTINCT ON (x) * FROM (VALUES (NULL), (NULL)) t(x) 2149 ---- 2150 distinct-on 2151 ├── columns: x:1 2152 ├── grouping columns: column1:1 2153 ├── cardinality: [1 - 2] 2154 ├── key: (1) 2155 └── values 2156 ├── columns: column1:1 2157 ├── cardinality: [2 - 2] 2158 ├── (NULL,) 2159 └── (NULL,) 2160 2161 # UpsertDistinctOn treats NULL values as distinct, so it can be eliminated. 2162 norm expect=EliminateDistinctOnValues 2163 INSERT INTO a (k, s, i) VALUES (1, NULL, NULL), (1, NULL, NULL) 2164 ON CONFLICT (s, i) DO NOTHING 2165 ---- 2166 insert a 2167 ├── columns: <none> 2168 ├── insert-mapping: 2169 │ ├── column1:6 => k:1 2170 │ ├── column3:8 => i:2 2171 │ ├── column9:9 => f:3 2172 │ ├── column2:7 => s:4 2173 │ └── column10:10 => j:5 2174 ├── cardinality: [0 - 0] 2175 ├── volatile, side-effects, mutations 2176 └── project 2177 ├── columns: column1:6!null column2:7 column3:8 column9:9 column10:10 2178 ├── fd: ()-->(9,10) 2179 └── select 2180 ├── columns: column1:6!null column2:7 column3:8 column9:9 column10:10 i:12 s:14 2181 ├── fd: ()-->(9,10,14) 2182 ├── left-join (hash) 2183 │ ├── columns: column1:6!null column2:7 column3:8 column9:9 column10:10 i:12 s:14 2184 │ ├── cardinality: [2 - ] 2185 │ ├── fd: ()-->(9,10) 2186 │ ├── project 2187 │ │ ├── columns: column9:9 column10:10 column1:6!null column2:7 column3:8 2188 │ │ ├── cardinality: [2 - 2] 2189 │ │ ├── fd: ()-->(9,10) 2190 │ │ ├── values 2191 │ │ │ ├── columns: column1:6!null column2:7 column3:8 2192 │ │ │ ├── cardinality: [2 - 2] 2193 │ │ │ ├── (1, NULL, NULL) 2194 │ │ │ └── (1, NULL, NULL) 2195 │ │ └── projections 2196 │ │ ├── CAST(NULL AS FLOAT8) [as=column9:9] 2197 │ │ └── CAST(NULL AS JSONB) [as=column10:10] 2198 │ ├── scan a 2199 │ │ ├── columns: i:12!null s:14!null 2200 │ │ └── key: (12,14) 2201 │ └── filters 2202 │ ├── column2:7 = s:14 [outer=(7,14), constraints=(/7: (/NULL - ]; /14: (/NULL - ]), fd=(7)==(14), (14)==(7)] 2203 │ └── column3:8 = i:12 [outer=(8,12), constraints=(/8: (/NULL - ]; /12: (/NULL - ]), fd=(8)==(12), (12)==(8)] 2204 └── filters 2205 └── s:14 IS NULL [outer=(14), constraints=(/14: [/NULL - /NULL]; tight), fd=()-->(14)] 2206 2207 # EnsureUpsertDistinctOn treats NULL values as distinct, so it can be eliminated. 2208 norm expect=EliminateDistinctOnValues 2209 INSERT INTO a (k, s, i) VALUES (1, NULL, NULL), (1, NULL, NULL) 2210 ON CONFLICT (s, i) DO UPDATE SET f=1.0 2211 ---- 2212 upsert a 2213 ├── columns: <none> 2214 ├── canary column: 11 2215 ├── fetch columns: k:11 i:12 f:13 s:14 j:15 2216 ├── insert-mapping: 2217 │ ├── column1:6 => k:1 2218 │ ├── column3:8 => i:2 2219 │ ├── column9:9 => f:3 2220 │ ├── column2:7 => s:4 2221 │ └── column10:10 => j:5 2222 ├── update-mapping: 2223 │ └── upsert_f:19 => f:3 2224 ├── cardinality: [0 - 0] 2225 ├── volatile, side-effects, mutations 2226 └── project 2227 ├── columns: upsert_f:19 column1:6!null column2:7 column3:8 column9:9 column10:10 k:11 i:12 f:13 s:14 j:15 2228 ├── cardinality: [2 - ] 2229 ├── fd: ()-->(9,10), (11)-->(12-15), (12,14)-->(11,13,15), (12,13)~~>(11,14,15) 2230 ├── left-join (hash) 2231 │ ├── columns: column1:6!null column2:7 column3:8 column9:9 column10:10 k:11 i:12 f:13 s:14 j:15 2232 │ ├── cardinality: [2 - ] 2233 │ ├── fd: ()-->(9,10), (11)-->(12-15), (12,14)-->(11,13,15), (12,13)~~>(11,14,15) 2234 │ ├── project 2235 │ │ ├── columns: column9:9 column10:10 column1:6!null column2:7 column3:8 2236 │ │ ├── cardinality: [2 - 2] 2237 │ │ ├── fd: ()-->(9,10) 2238 │ │ ├── values 2239 │ │ │ ├── columns: column1:6!null column2:7 column3:8 2240 │ │ │ ├── cardinality: [2 - 2] 2241 │ │ │ ├── (1, NULL, NULL) 2242 │ │ │ └── (1, NULL, NULL) 2243 │ │ └── projections 2244 │ │ ├── CAST(NULL AS FLOAT8) [as=column9:9] 2245 │ │ └── CAST(NULL AS JSONB) [as=column10:10] 2246 │ ├── scan a 2247 │ │ ├── columns: k:11!null i:12!null f:13 s:14!null j:15 2248 │ │ ├── key: (11) 2249 │ │ └── fd: (11)-->(12-15), (12,14)-->(11,13,15), (12,13)~~>(11,14,15) 2250 │ └── filters 2251 │ ├── column3:8 = i:12 [outer=(8,12), constraints=(/8: (/NULL - ]; /12: (/NULL - ]), fd=(8)==(12), (12)==(8)] 2252 │ └── column2:7 = s:14 [outer=(7,14), constraints=(/7: (/NULL - ]; /14: (/NULL - ]), fd=(7)==(14), (14)==(7)] 2253 └── projections 2254 └── CASE WHEN k:11 IS NULL THEN column9:9 ELSE 1.0 END [as=upsert_f:19, outer=(9,11)] 2255 2256 # EnsureUpsertDistinctOn is not removed when there are duplicates. 2257 norm expect-not=EliminateDistinctOnValues 2258 INSERT INTO a (k, s, i) VALUES (1, 'foo', 1), (2, 'bar', 2), (3, 'foo', 1) 2259 ON CONFLICT (s, i) DO UPDATE SET f=1.0 2260 ---- 2261 upsert a 2262 ├── columns: <none> 2263 ├── canary column: 11 2264 ├── fetch columns: k:11 i:12 f:13 s:14 j:15 2265 ├── insert-mapping: 2266 │ ├── column1:6 => k:1 2267 │ ├── column3:8 => i:2 2268 │ ├── column9:9 => f:3 2269 │ ├── column2:7 => s:4 2270 │ └── column10:10 => j:5 2271 ├── update-mapping: 2272 │ └── upsert_f:19 => f:3 2273 ├── cardinality: [0 - 0] 2274 ├── volatile, side-effects, mutations 2275 └── project 2276 ├── columns: upsert_f:19 column1:6!null column2:7!null column3:8!null column9:9 column10:10 k:11 i:12 f:13 s:14 j:15 2277 ├── cardinality: [1 - ] 2278 ├── key: (7,8) 2279 ├── fd: ()-->(9,10), (7,8)-->(6,11-15,19), (11)-->(12-15), (12,14)-->(11,13,15), (12,13)~~>(11,14,15) 2280 ├── left-join (hash) 2281 │ ├── columns: column1:6!null column2:7!null column3:8!null column9:9 column10:10 k:11 i:12 f:13 s:14 j:15 2282 │ ├── cardinality: [1 - ] 2283 │ ├── key: (7,8) 2284 │ ├── fd: ()-->(9,10), (7,8)-->(6,11-15), (11)-->(12-15), (12,14)-->(11,13,15), (12,13)~~>(11,14,15) 2285 │ ├── ensure-upsert-distinct-on 2286 │ │ ├── columns: column1:6!null column2:7!null column3:8!null column9:9 column10:10 2287 │ │ ├── grouping columns: column2:7!null column3:8!null 2288 │ │ ├── error: "UPSERT or INSERT...ON CONFLICT command cannot affect row a second time" 2289 │ │ ├── cardinality: [1 - 3] 2290 │ │ ├── key: (7,8) 2291 │ │ ├── fd: ()-->(9,10), (7,8)-->(6,9,10) 2292 │ │ ├── project 2293 │ │ │ ├── columns: column9:9 column10:10 column1:6!null column2:7!null column3:8!null 2294 │ │ │ ├── cardinality: [3 - 3] 2295 │ │ │ ├── fd: ()-->(9,10) 2296 │ │ │ ├── values 2297 │ │ │ │ ├── columns: column1:6!null column2:7!null column3:8!null 2298 │ │ │ │ ├── cardinality: [3 - 3] 2299 │ │ │ │ ├── (1, 'foo', 1) 2300 │ │ │ │ ├── (2, 'bar', 2) 2301 │ │ │ │ └── (3, 'foo', 1) 2302 │ │ │ └── projections 2303 │ │ │ ├── CAST(NULL AS FLOAT8) [as=column9:9] 2304 │ │ │ └── CAST(NULL AS JSONB) [as=column10:10] 2305 │ │ └── aggregations 2306 │ │ ├── first-agg [as=column1:6, outer=(6)] 2307 │ │ │ └── column1:6 2308 │ │ ├── first-agg [as=column9:9, outer=(9)] 2309 │ │ │ └── column9:9 2310 │ │ └── first-agg [as=column10:10, outer=(10)] 2311 │ │ └── column10:10 2312 │ ├── scan a 2313 │ │ ├── columns: k:11!null i:12!null f:13 s:14!null j:15 2314 │ │ ├── key: (11) 2315 │ │ └── fd: (11)-->(12-15), (12,14)-->(11,13,15), (12,13)~~>(11,14,15) 2316 │ └── filters 2317 │ ├── column3:8 = i:12 [outer=(8,12), constraints=(/8: (/NULL - ]; /12: (/NULL - ]), fd=(8)==(12), (12)==(8)] 2318 │ └── column2:7 = s:14 [outer=(7,14), constraints=(/7: (/NULL - ]; /14: (/NULL - ]), fd=(7)==(14), (14)==(7)] 2319 └── projections 2320 └── CASE WHEN k:11 IS NULL THEN column9:9 ELSE 1.0 END [as=upsert_f:19, outer=(9,11)] 2321 2322 # DO NOTHING case where all distinct ops can be removed. 2323 norm expect=EliminateDistinctOnValues 2324 INSERT INTO a (k, s, i, f) VALUES (1, 'foo', 1, 1.0), (2, 'bar', 2, 2.0), (3, 'foo', 2, 1.0) 2325 ON CONFLICT DO NOTHING 2326 ---- 2327 insert a 2328 ├── columns: <none> 2329 ├── insert-mapping: 2330 │ ├── column1:6 => k:1 2331 │ ├── column3:8 => i:2 2332 │ ├── column4:9 => f:3 2333 │ ├── column2:7 => s:4 2334 │ └── column10:10 => j:5 2335 ├── cardinality: [0 - 0] 2336 ├── volatile, side-effects, mutations 2337 └── project 2338 ├── columns: column1:6!null column2:7!null column3:8!null column4:9!null column10:10 2339 ├── fd: ()-->(10) 2340 └── select 2341 ├── columns: column1:6!null column2:7!null column3:8!null column4:9!null column10:10 k:11 i:17 s:19 i:22 f:23 2342 ├── fd: ()-->(10,11,19,22) 2343 ├── left-join (hash) 2344 │ ├── columns: column1:6!null column2:7!null column3:8!null column4:9!null column10:10 k:11 i:17 s:19 i:22 f:23 2345 │ ├── fd: ()-->(10,11,19) 2346 │ ├── select 2347 │ │ ├── columns: column1:6!null column2:7!null column3:8!null column4:9!null column10:10 k:11 i:17 s:19 2348 │ │ ├── fd: ()-->(10,11,19) 2349 │ │ ├── left-join (hash) 2350 │ │ │ ├── columns: column1:6!null column2:7!null column3:8!null column4:9!null column10:10 k:11 i:17 s:19 2351 │ │ │ ├── fd: ()-->(10,11) 2352 │ │ │ ├── select 2353 │ │ │ │ ├── columns: column1:6!null column2:7!null column3:8!null column4:9!null column10:10 k:11 2354 │ │ │ │ ├── fd: ()-->(10,11) 2355 │ │ │ │ ├── left-join (hash) 2356 │ │ │ │ │ ├── columns: column1:6!null column2:7!null column3:8!null column4:9!null column10:10 k:11 2357 │ │ │ │ │ ├── cardinality: [3 - ] 2358 │ │ │ │ │ ├── fd: ()-->(10) 2359 │ │ │ │ │ ├── project 2360 │ │ │ │ │ │ ├── columns: column10:10 column1:6!null column2:7!null column3:8!null column4:9!null 2361 │ │ │ │ │ │ ├── cardinality: [3 - 3] 2362 │ │ │ │ │ │ ├── fd: ()-->(10) 2363 │ │ │ │ │ │ ├── values 2364 │ │ │ │ │ │ │ ├── columns: column1:6!null column2:7!null column3:8!null column4:9!null 2365 │ │ │ │ │ │ │ ├── cardinality: [3 - 3] 2366 │ │ │ │ │ │ │ ├── (1, 'foo', 1, 1.0) 2367 │ │ │ │ │ │ │ ├── (2, 'bar', 2, 2.0) 2368 │ │ │ │ │ │ │ └── (3, 'foo', 2, 1.0) 2369 │ │ │ │ │ │ └── projections 2370 │ │ │ │ │ │ └── CAST(NULL AS JSONB) [as=column10:10] 2371 │ │ │ │ │ ├── scan a 2372 │ │ │ │ │ │ ├── columns: k:11!null 2373 │ │ │ │ │ │ └── key: (11) 2374 │ │ │ │ │ └── filters 2375 │ │ │ │ │ └── column1:6 = k:11 [outer=(6,11), constraints=(/6: (/NULL - ]; /11: (/NULL - ]), fd=(6)==(11), (11)==(6)] 2376 │ │ │ │ └── filters 2377 │ │ │ │ └── k:11 IS NULL [outer=(11), constraints=(/11: [/NULL - /NULL]; tight), fd=()-->(11)] 2378 │ │ │ ├── scan a 2379 │ │ │ │ ├── columns: i:17!null s:19!null 2380 │ │ │ │ └── key: (17,19) 2381 │ │ │ └── filters 2382 │ │ │ ├── column2:7 = s:19 [outer=(7,19), constraints=(/7: (/NULL - ]; /19: (/NULL - ]), fd=(7)==(19), (19)==(7)] 2383 │ │ │ └── column3:8 = i:17 [outer=(8,17), constraints=(/8: (/NULL - ]; /17: (/NULL - ]), fd=(8)==(17), (17)==(8)] 2384 │ │ └── filters 2385 │ │ └── s:19 IS NULL [outer=(19), constraints=(/19: [/NULL - /NULL]; tight), fd=()-->(19)] 2386 │ ├── scan a 2387 │ │ ├── columns: i:22!null f:23 2388 │ │ └── lax-key: (22,23) 2389 │ └── filters 2390 │ ├── column4:9 = f:23 [outer=(9,23), constraints=(/9: (/NULL - ]; /23: (/NULL - ]), fd=(9)==(23), (23)==(9)] 2391 │ └── column3:8 = i:22 [outer=(8,22), constraints=(/8: (/NULL - ]; /22: (/NULL - ]), fd=(8)==(22), (22)==(8)] 2392 └── filters 2393 └── i:22 IS NULL [outer=(22), constraints=(/22: [/NULL - /NULL]; tight), fd=()-->(22)] 2394 2395 # DO NOTHING case where one distinct op can be removed (k), but two others 2396 # can't: (s, i) and (f, i). 2397 norm expect=EliminateDistinctOnValues 2398 INSERT INTO a (k, s, f) VALUES (1, 'foo', 1.0), (2, 'bar', 2.0), (3, 'foo', 1.0) 2399 ON CONFLICT DO NOTHING 2400 ---- 2401 insert a 2402 ├── columns: <none> 2403 ├── insert-mapping: 2404 │ ├── column1:6 => k:1 2405 │ ├── column9:9 => i:2 2406 │ ├── column3:8 => f:3 2407 │ ├── column2:7 => s:4 2408 │ └── column10:10 => j:5 2409 ├── cardinality: [0 - 0] 2410 ├── volatile, side-effects, mutations 2411 └── upsert-distinct-on 2412 ├── columns: column1:6!null column2:7!null column3:8!null column9:9 column10:10 2413 ├── grouping columns: column3:8!null column9:9 2414 ├── lax-key: (8,9) 2415 ├── fd: ()-->(9,10), (7,9)~~>(6,8), (8,9)~~>(6,7,10) 2416 ├── select 2417 │ ├── columns: column1:6!null column2:7!null column3:8!null column9:9 column10:10 i:22 f:23 2418 │ ├── lax-key: (7,9,22,23) 2419 │ ├── fd: ()-->(9,10,22), (7,9)~~>(6,8) 2420 │ ├── left-join (hash) 2421 │ │ ├── columns: column1:6!null column2:7!null column3:8!null column9:9 column10:10 i:22 f:23 2422 │ │ ├── lax-key: (7,9,22,23) 2423 │ │ ├── fd: ()-->(9,10), (7,9)~~>(6,8) 2424 │ │ ├── upsert-distinct-on 2425 │ │ │ ├── columns: column1:6!null column2:7!null column3:8!null column9:9 column10:10 2426 │ │ │ ├── grouping columns: column2:7!null column9:9 2427 │ │ │ ├── lax-key: (7,9) 2428 │ │ │ ├── fd: ()-->(9,10), (7,9)~~>(6,8,10) 2429 │ │ │ ├── select 2430 │ │ │ │ ├── columns: column1:6!null column2:7!null column3:8!null column9:9 column10:10 k:11 i:17 s:19 2431 │ │ │ │ ├── fd: ()-->(9-11,19) 2432 │ │ │ │ ├── left-join (hash) 2433 │ │ │ │ │ ├── columns: column1:6!null column2:7!null column3:8!null column9:9 column10:10 k:11 i:17 s:19 2434 │ │ │ │ │ ├── fd: ()-->(9-11) 2435 │ │ │ │ │ ├── select 2436 │ │ │ │ │ │ ├── columns: column1:6!null column2:7!null column3:8!null column9:9 column10:10 k:11 2437 │ │ │ │ │ │ ├── fd: ()-->(9-11) 2438 │ │ │ │ │ │ ├── left-join (hash) 2439 │ │ │ │ │ │ │ ├── columns: column1:6!null column2:7!null column3:8!null column9:9 column10:10 k:11 2440 │ │ │ │ │ │ │ ├── cardinality: [3 - ] 2441 │ │ │ │ │ │ │ ├── fd: ()-->(9,10) 2442 │ │ │ │ │ │ │ ├── project 2443 │ │ │ │ │ │ │ │ ├── columns: column9:9 column10:10 column1:6!null column2:7!null column3:8!null 2444 │ │ │ │ │ │ │ │ ├── cardinality: [3 - 3] 2445 │ │ │ │ │ │ │ │ ├── fd: ()-->(9,10) 2446 │ │ │ │ │ │ │ │ ├── values 2447 │ │ │ │ │ │ │ │ │ ├── columns: column1:6!null column2:7!null column3:8!null 2448 │ │ │ │ │ │ │ │ │ ├── cardinality: [3 - 3] 2449 │ │ │ │ │ │ │ │ │ ├── (1, 'foo', 1.0) 2450 │ │ │ │ │ │ │ │ │ ├── (2, 'bar', 2.0) 2451 │ │ │ │ │ │ │ │ │ └── (3, 'foo', 1.0) 2452 │ │ │ │ │ │ │ │ └── projections 2453 │ │ │ │ │ │ │ │ ├── CAST(NULL AS INT8) [as=column9:9] 2454 │ │ │ │ │ │ │ │ └── CAST(NULL AS JSONB) [as=column10:10] 2455 │ │ │ │ │ │ │ ├── scan a 2456 │ │ │ │ │ │ │ │ ├── columns: k:11!null 2457 │ │ │ │ │ │ │ │ └── key: (11) 2458 │ │ │ │ │ │ │ └── filters 2459 │ │ │ │ │ │ │ └── column1:6 = k:11 [outer=(6,11), constraints=(/6: (/NULL - ]; /11: (/NULL - ]), fd=(6)==(11), (11)==(6)] 2460 │ │ │ │ │ │ └── filters 2461 │ │ │ │ │ │ └── k:11 IS NULL [outer=(11), constraints=(/11: [/NULL - /NULL]; tight), fd=()-->(11)] 2462 │ │ │ │ │ ├── scan a 2463 │ │ │ │ │ │ ├── columns: i:17!null s:19!null 2464 │ │ │ │ │ │ └── key: (17,19) 2465 │ │ │ │ │ └── filters 2466 │ │ │ │ │ ├── column2:7 = s:19 [outer=(7,19), constraints=(/7: (/NULL - ]; /19: (/NULL - ]), fd=(7)==(19), (19)==(7)] 2467 │ │ │ │ │ └── column9:9 = i:17 [outer=(9,17), constraints=(/9: (/NULL - ]; /17: (/NULL - ]), fd=(9)==(17), (17)==(9)] 2468 │ │ │ │ └── filters 2469 │ │ │ │ └── s:19 IS NULL [outer=(19), constraints=(/19: [/NULL - /NULL]; tight), fd=()-->(19)] 2470 │ │ │ └── aggregations 2471 │ │ │ ├── first-agg [as=column1:6, outer=(6)] 2472 │ │ │ │ └── column1:6 2473 │ │ │ ├── first-agg [as=column3:8, outer=(8)] 2474 │ │ │ │ └── column3:8 2475 │ │ │ └── first-agg [as=column10:10, outer=(10)] 2476 │ │ │ └── column10:10 2477 │ │ ├── scan a 2478 │ │ │ ├── columns: i:22!null f:23 2479 │ │ │ └── lax-key: (22,23) 2480 │ │ └── filters 2481 │ │ ├── column3:8 = f:23 [outer=(8,23), constraints=(/8: (/NULL - ]; /23: (/NULL - ]), fd=(8)==(23), (23)==(8)] 2482 │ │ └── column9:9 = i:22 [outer=(9,22), constraints=(/9: (/NULL - ]; /22: (/NULL - ]), fd=(9)==(22), (22)==(9)] 2483 │ └── filters 2484 │ └── i:22 IS NULL [outer=(22), constraints=(/22: [/NULL - /NULL]; tight), fd=()-->(22)] 2485 └── aggregations 2486 ├── first-agg [as=column1:6, outer=(6)] 2487 │ └── column1:6 2488 ├── first-agg [as=column2:7, outer=(7)] 2489 │ └── column2:7 2490 └── first-agg [as=column10:10, outer=(10)] 2491 └── column10:10 2492 2493 # DO NOTHING case where innermost distinct op cannot be removed (because it 2494 # groups on a non-constant column). Ensure that outer distinct ops can still be 2495 # removed. 2496 norm 2497 INSERT INTO a (k, s, i, f) VALUES (unique_rowid(), 'foo', 1, 1.0), (unique_rowid(), 'bar', 2, 2.0) 2498 ON CONFLICT DO NOTHING 2499 ---- 2500 insert a 2501 ├── columns: <none> 2502 ├── insert-mapping: 2503 │ ├── column1:6 => k:1 2504 │ ├── column3:8 => i:2 2505 │ ├── column4:9 => f:3 2506 │ ├── column2:7 => s:4 2507 │ └── column10:10 => j:5 2508 ├── cardinality: [0 - 0] 2509 ├── volatile, side-effects, mutations 2510 └── project 2511 ├── columns: column1:6 column2:7!null column3:8!null column4:9!null column10:10 2512 ├── volatile, side-effects 2513 ├── fd: ()-->(10), (6)~~>(7-9) 2514 └── select 2515 ├── columns: column1:6 column2:7!null column3:8!null column4:9!null column10:10 i:17 s:19 i:22 f:23 2516 ├── volatile, side-effects 2517 ├── lax-key: (6,17,19,22,23) 2518 ├── fd: ()-->(10,19,22), (6)~~>(7-9) 2519 ├── left-join (hash) 2520 │ ├── columns: column1:6 column2:7!null column3:8!null column4:9!null column10:10 i:17 s:19 i:22 f:23 2521 │ ├── volatile, side-effects 2522 │ ├── lax-key: (6,17,19,22,23) 2523 │ ├── fd: ()-->(10,19), (6)~~>(7-9) 2524 │ ├── select 2525 │ │ ├── columns: column1:6 column2:7!null column3:8!null column4:9!null column10:10 i:17 s:19 2526 │ │ ├── volatile, side-effects 2527 │ │ ├── lax-key: (6,17,19) 2528 │ │ ├── fd: ()-->(10,19), (6)~~>(7-9) 2529 │ │ ├── left-join (hash) 2530 │ │ │ ├── columns: column1:6 column2:7!null column3:8!null column4:9!null column10:10 i:17 s:19 2531 │ │ │ ├── volatile, side-effects 2532 │ │ │ ├── lax-key: (6,17,19) 2533 │ │ │ ├── fd: ()-->(10), (6)~~>(7-9) 2534 │ │ │ ├── upsert-distinct-on 2535 │ │ │ │ ├── columns: column1:6 column2:7!null column3:8!null column4:9!null column10:10 2536 │ │ │ │ ├── grouping columns: column1:6 2537 │ │ │ │ ├── volatile, side-effects 2538 │ │ │ │ ├── lax-key: (6) 2539 │ │ │ │ ├── fd: ()-->(10), (6)~~>(7-10) 2540 │ │ │ │ ├── select 2541 │ │ │ │ │ ├── columns: column1:6 column2:7!null column3:8!null column4:9!null column10:10 k:11 2542 │ │ │ │ │ ├── volatile, side-effects 2543 │ │ │ │ │ ├── fd: ()-->(10,11) 2544 │ │ │ │ │ ├── left-join (hash) 2545 │ │ │ │ │ │ ├── columns: column1:6 column2:7!null column3:8!null column4:9!null column10:10 k:11 2546 │ │ │ │ │ │ ├── cardinality: [2 - ] 2547 │ │ │ │ │ │ ├── volatile, side-effects 2548 │ │ │ │ │ │ ├── fd: ()-->(10) 2549 │ │ │ │ │ │ ├── project 2550 │ │ │ │ │ │ │ ├── columns: column10:10 column1:6 column2:7!null column3:8!null column4:9!null 2551 │ │ │ │ │ │ │ ├── cardinality: [2 - 2] 2552 │ │ │ │ │ │ │ ├── volatile, side-effects 2553 │ │ │ │ │ │ │ ├── fd: ()-->(10) 2554 │ │ │ │ │ │ │ ├── values 2555 │ │ │ │ │ │ │ │ ├── columns: column1:6 column2:7!null column3:8!null column4:9!null 2556 │ │ │ │ │ │ │ │ ├── cardinality: [2 - 2] 2557 │ │ │ │ │ │ │ │ ├── volatile, side-effects 2558 │ │ │ │ │ │ │ │ ├── (unique_rowid(), 'foo', 1, 1.0) 2559 │ │ │ │ │ │ │ │ └── (unique_rowid(), 'bar', 2, 2.0) 2560 │ │ │ │ │ │ │ └── projections 2561 │ │ │ │ │ │ │ └── CAST(NULL AS JSONB) [as=column10:10] 2562 │ │ │ │ │ │ ├── scan a 2563 │ │ │ │ │ │ │ ├── columns: k:11!null 2564 │ │ │ │ │ │ │ └── key: (11) 2565 │ │ │ │ │ │ └── filters 2566 │ │ │ │ │ │ └── column1:6 = k:11 [outer=(6,11), constraints=(/6: (/NULL - ]; /11: (/NULL - ]), fd=(6)==(11), (11)==(6)] 2567 │ │ │ │ │ └── filters 2568 │ │ │ │ │ └── k:11 IS NULL [outer=(11), constraints=(/11: [/NULL - /NULL]; tight), fd=()-->(11)] 2569 │ │ │ │ └── aggregations 2570 │ │ │ │ ├── first-agg [as=column2:7, outer=(7)] 2571 │ │ │ │ │ └── column2:7 2572 │ │ │ │ ├── first-agg [as=column3:8, outer=(8)] 2573 │ │ │ │ │ └── column3:8 2574 │ │ │ │ ├── first-agg [as=column4:9, outer=(9)] 2575 │ │ │ │ │ └── column4:9 2576 │ │ │ │ └── first-agg [as=column10:10, outer=(10)] 2577 │ │ │ │ └── column10:10 2578 │ │ │ ├── scan a 2579 │ │ │ │ ├── columns: i:17!null s:19!null 2580 │ │ │ │ └── key: (17,19) 2581 │ │ │ └── filters 2582 │ │ │ ├── column2:7 = s:19 [outer=(7,19), constraints=(/7: (/NULL - ]; /19: (/NULL - ]), fd=(7)==(19), (19)==(7)] 2583 │ │ │ └── column3:8 = i:17 [outer=(8,17), constraints=(/8: (/NULL - ]; /17: (/NULL - ]), fd=(8)==(17), (17)==(8)] 2584 │ │ └── filters 2585 │ │ └── s:19 IS NULL [outer=(19), constraints=(/19: [/NULL - /NULL]; tight), fd=()-->(19)] 2586 │ ├── scan a 2587 │ │ ├── columns: i:22!null f:23 2588 │ │ └── lax-key: (22,23) 2589 │ └── filters 2590 │ ├── column4:9 = f:23 [outer=(9,23), constraints=(/9: (/NULL - ]; /23: (/NULL - ]), fd=(9)==(23), (23)==(9)] 2591 │ └── column3:8 = i:22 [outer=(8,22), constraints=(/8: (/NULL - ]; /22: (/NULL - ]), fd=(8)==(22), (22)==(8)] 2592 └── filters 2593 └── i:22 IS NULL [outer=(22), constraints=(/22: [/NULL - /NULL]; tight), fd=()-->(22)] 2594 2595 # DO NOTHING case with explicit conflict columns (only add upsert-distinct-on 2596 # for one index). 2597 norm expect-not=EliminateDistinctOnValues 2598 INSERT INTO a (k, s, i) SELECT i, 'foo', i FROM a 2599 ON CONFLICT (s, i) DO NOTHING 2600 ---- 2601 insert a 2602 ├── columns: <none> 2603 ├── insert-mapping: 2604 │ ├── i:7 => k:1 2605 │ ├── i:7 => i:2 2606 │ ├── column12:12 => f:3 2607 │ ├── "?column?":11 => s:4 2608 │ └── column13:13 => j:5 2609 ├── cardinality: [0 - 0] 2610 ├── volatile, side-effects, mutations 2611 └── upsert-distinct-on 2612 ├── columns: i:7!null "?column?":11!null column12:12 column13:13 2613 ├── grouping columns: i:7!null 2614 ├── key: (7) 2615 ├── fd: ()-->(11-13) 2616 ├── select 2617 │ ├── columns: i:7!null "?column?":11!null column12:12 column13:13 i:15 s:17 2618 │ ├── fd: ()-->(11-13,17) 2619 │ ├── left-join (hash) 2620 │ │ ├── columns: i:7!null "?column?":11!null column12:12 column13:13 i:15 s:17 2621 │ │ ├── fd: ()-->(11-13) 2622 │ │ ├── project 2623 │ │ │ ├── columns: column12:12 column13:13 "?column?":11!null i:7!null 2624 │ │ │ ├── fd: ()-->(11-13) 2625 │ │ │ ├── scan a 2626 │ │ │ │ └── columns: i:7!null 2627 │ │ │ └── projections 2628 │ │ │ ├── CAST(NULL AS FLOAT8) [as=column12:12] 2629 │ │ │ ├── CAST(NULL AS JSONB) [as=column13:13] 2630 │ │ │ └── 'foo' [as="?column?":11] 2631 │ │ ├── select 2632 │ │ │ ├── columns: i:15!null s:17!null 2633 │ │ │ ├── key: (15) 2634 │ │ │ ├── fd: ()-->(17) 2635 │ │ │ ├── scan a 2636 │ │ │ │ ├── columns: i:15!null s:17!null 2637 │ │ │ │ └── key: (15,17) 2638 │ │ │ └── filters 2639 │ │ │ └── s:17 = 'foo' [outer=(17), constraints=(/17: [/'foo' - /'foo']; tight), fd=()-->(17)] 2640 │ │ └── filters 2641 │ │ └── i:7 = i:15 [outer=(7,15), constraints=(/7: (/NULL - ]; /15: (/NULL - ]), fd=(7)==(15), (15)==(7)] 2642 │ └── filters 2643 │ └── s:17 IS NULL [outer=(17), constraints=(/17: [/NULL - /NULL]; tight), fd=()-->(17)] 2644 └── aggregations 2645 ├── first-agg [as=column12:12, outer=(12)] 2646 │ └── column12:12 2647 ├── first-agg [as=column13:13, outer=(13)] 2648 │ └── column13:13 2649 └── const-agg [as="?column?":11, outer=(11)] 2650 └── "?column?":11 2651 2652 # -------------------------------------------------- 2653 # PushAggDistinctIntoScalarGroupBy 2654 # -------------------------------------------------- 2655 2656 # SUM case. 2657 norm expect=PushAggDistinctIntoScalarGroupBy 2658 SELECT sum(DISTINCT y) FROM xyzbs 2659 ---- 2660 scalar-group-by 2661 ├── columns: sum:6 2662 ├── cardinality: [1 - 1] 2663 ├── key: () 2664 ├── fd: ()-->(6) 2665 ├── distinct-on 2666 │ ├── columns: y:2 2667 │ ├── grouping columns: y:2 2668 │ ├── key: (2) 2669 │ └── scan xyzbs 2670 │ └── columns: y:2 2671 └── aggregations 2672 └── sum [as=sum:6, outer=(2)] 2673 └── y:2 2674 2675 # COUNT case. Expecting an index scan because opt command is used. 2676 opt expect=PushAggDistinctIntoScalarGroupBy 2677 SELECT count(DISTINCT y) FROM xyzbs 2678 ---- 2679 scalar-group-by 2680 ├── columns: count:6!null 2681 ├── cardinality: [1 - 1] 2682 ├── key: () 2683 ├── fd: ()-->(6) 2684 ├── distinct-on 2685 │ ├── columns: y:2 2686 │ ├── grouping columns: y:2 2687 │ ├── internal-ordering: +2 2688 │ ├── key: (2) 2689 │ └── scan xyzbs@secondary 2690 │ ├── columns: y:2 2691 │ └── ordering: +2 2692 └── aggregations 2693 └── count [as=count:6, outer=(2)] 2694 └── y:2 2695 2696 # AVG case. 2697 norm expect=PushAggDistinctIntoScalarGroupBy 2698 SELECT avg(DISTINCT y) FROM xyzbs 2699 ---- 2700 scalar-group-by 2701 ├── columns: avg:6 2702 ├── cardinality: [1 - 1] 2703 ├── key: () 2704 ├── fd: ()-->(6) 2705 ├── distinct-on 2706 │ ├── columns: y:2 2707 │ ├── grouping columns: y:2 2708 │ ├── key: (2) 2709 │ └── scan xyzbs 2710 │ └── columns: y:2 2711 └── aggregations 2712 └── avg [as=avg:6, outer=(2)] 2713 └── y:2 2714 2715 # JSON_AGG case. 2716 norm expect=PushAggDistinctIntoScalarGroupBy 2717 SELECT json_agg(DISTINCT y) FROM xyzbs 2718 ---- 2719 scalar-group-by 2720 ├── columns: json_agg:6 2721 ├── cardinality: [1 - 1] 2722 ├── key: () 2723 ├── fd: ()-->(6) 2724 ├── distinct-on 2725 │ ├── columns: y:2 2726 │ ├── grouping columns: y:2 2727 │ ├── key: (2) 2728 │ └── scan xyzbs 2729 │ └── columns: y:2 2730 └── aggregations 2731 └── json-agg [as=json_agg:6, outer=(2)] 2732 └── y:2 2733 2734 # CORR case. 2735 # Multiple input arguments for aggregate function. 2736 norm expect=PushAggDistinctIntoScalarGroupBy 2737 SELECT corr(DISTINCT y, z) FROM xyzbs 2738 ---- 2739 scalar-group-by 2740 ├── columns: corr:6 2741 ├── cardinality: [1 - 1] 2742 ├── key: () 2743 ├── fd: ()-->(6) 2744 ├── distinct-on 2745 │ ├── columns: y:2 z:3!null 2746 │ ├── grouping columns: y:2 z:3!null 2747 │ ├── key: (2,3) 2748 │ └── scan xyzbs 2749 │ └── columns: y:2 z:3!null 2750 └── aggregations 2751 └── corr [as=corr:6, outer=(2,3)] 2752 ├── y:2 2753 └── z:3 2754 2755 # STRING_AGG case. 2756 # Multiple input arguments for aggregate function. 2757 norm expect=PushAggDistinctIntoScalarGroupBy 2758 SELECT string_agg(DISTINCT s, '-') FROM xyzbs 2759 ---- 2760 scalar-group-by 2761 ├── columns: string_agg:7 2762 ├── cardinality: [1 - 1] 2763 ├── key: () 2764 ├── fd: ()-->(7) 2765 ├── distinct-on 2766 │ ├── columns: s:5 column6:6!null 2767 │ ├── grouping columns: s:5 2768 │ ├── key: (5) 2769 │ ├── fd: ()-->(6) 2770 │ ├── project 2771 │ │ ├── columns: column6:6!null s:5 2772 │ │ ├── fd: ()-->(6) 2773 │ │ ├── scan xyzbs 2774 │ │ │ └── columns: s:5 2775 │ │ └── projections 2776 │ │ └── '-' [as=column6:6] 2777 │ └── aggregations 2778 │ └── const-agg [as=column6:6, outer=(6)] 2779 │ └── column6:6 2780 └── aggregations 2781 └── string-agg [as=string_agg:7, outer=(5,6)] 2782 ├── s:5 2783 └── column6:6 2784 2785 # STRING_AGG case with an ORDER BY. 2786 # Multiple input arguments for aggregate function. 2787 norm expect=PushAggDistinctIntoScalarGroupBy 2788 SELECT string_agg(DISTINCT s, '-') FROM (SELECT s FROM xyzbs ORDER BY s) 2789 ---- 2790 scalar-group-by 2791 ├── columns: string_agg:7 2792 ├── internal-ordering: +5 opt(6) 2793 ├── cardinality: [1 - 1] 2794 ├── key: () 2795 ├── fd: ()-->(7) 2796 ├── sort 2797 │ ├── columns: s:5 column6:6!null 2798 │ ├── key: (5) 2799 │ ├── fd: ()-->(6) 2800 │ ├── ordering: +5 opt(6) [actual: +5] 2801 │ └── distinct-on 2802 │ ├── columns: s:5 column6:6!null 2803 │ ├── grouping columns: s:5 2804 │ ├── key: (5) 2805 │ ├── fd: ()-->(6) 2806 │ ├── project 2807 │ │ ├── columns: column6:6!null s:5 2808 │ │ ├── fd: ()-->(6) 2809 │ │ ├── scan xyzbs 2810 │ │ │ └── columns: s:5 2811 │ │ └── projections 2812 │ │ └── '-' [as=column6:6] 2813 │ └── aggregations 2814 │ └── const-agg [as=column6:6, outer=(6)] 2815 │ └── column6:6 2816 └── aggregations 2817 └── string-agg [as=string_agg:7, outer=(5,6)] 2818 ├── s:5 2819 └── column6:6 2820 2821 # No-op case where the same aggregate function is called on different 2822 # columns. 2823 norm expect-not=PushAggDistinctIntoScalarGroupBy 2824 SELECT count(DISTINCT y), count(DISTINCT z) FROM xyzbs 2825 ---- 2826 scalar-group-by 2827 ├── columns: count:6!null count:7!null 2828 ├── cardinality: [1 - 1] 2829 ├── key: () 2830 ├── fd: ()-->(6,7) 2831 ├── scan xyzbs 2832 │ └── columns: y:2 z:3!null 2833 └── aggregations 2834 ├── agg-distinct [as=count:6, outer=(2)] 2835 │ └── count 2836 │ └── y:2 2837 └── agg-distinct [as=count:7, outer=(3)] 2838 └── count 2839 └── z:3 2840 2841 # No-op case where different aggregate functions are called on the same 2842 # column. 2843 norm expect-not=PushAggDistinctIntoScalarGroupBy 2844 SELECT count(DISTINCT y), sum(DISTINCT y) FROM xyzbs 2845 ---- 2846 scalar-group-by 2847 ├── columns: count:6!null sum:7 2848 ├── cardinality: [1 - 1] 2849 ├── key: () 2850 ├── fd: ()-->(6,7) 2851 ├── scan xyzbs 2852 │ └── columns: y:2 2853 └── aggregations 2854 ├── agg-distinct [as=count:6, outer=(2)] 2855 │ └── count 2856 │ └── y:2 2857 └── agg-distinct [as=sum:7, outer=(2)] 2858 └── sum 2859 └── y:2 2860 2861 # No-op cases where EliminateAggDistinct removes the AggDistinct before 2862 # PushAggDistinctIntoScalarGroupBy is applied. Applies to MAX, MIN, BOOL_AND, 2863 # and BOOL_OR. 2864 norm expect-not=PushAggDistinctIntoScalarGroupBy 2865 SELECT max(DISTINCT y) FROM xyzbs 2866 ---- 2867 scalar-group-by 2868 ├── columns: max:6 2869 ├── cardinality: [1 - 1] 2870 ├── key: () 2871 ├── fd: ()-->(6) 2872 ├── scan xyzbs 2873 │ └── columns: y:2 2874 └── aggregations 2875 └── max [as=max:6, outer=(2)] 2876 └── y:2 2877 2878 norm expect-not=PushAggDistinctIntoScalarGroupBy 2879 SELECT bool_and(DISTINCT b) FROM xyzbs 2880 ---- 2881 scalar-group-by 2882 ├── columns: bool_and:6 2883 ├── cardinality: [1 - 1] 2884 ├── key: () 2885 ├── fd: ()-->(6) 2886 ├── scan xyzbs 2887 │ └── columns: b:4!null 2888 └── aggregations 2889 └── bool-and [as=bool_and:6, outer=(4)] 2890 └── b:4 2891 2892 # -------------------------------------------------- 2893 # PushAggFilterIntoScalarGroupBy 2894 # -------------------------------------------------- 2895 2896 # SUM case. 2897 norm expect=PushAggFilterIntoScalarGroupBy 2898 SELECT sum(y) FILTER (WHERE y < 50) FROM xyzbs 2899 ---- 2900 scalar-group-by 2901 ├── columns: sum:7 2902 ├── cardinality: [1 - 1] 2903 ├── key: () 2904 ├── fd: ()-->(7) 2905 ├── select 2906 │ ├── columns: y:2!null 2907 │ ├── scan xyzbs 2908 │ │ └── columns: y:2 2909 │ └── filters 2910 │ └── y:2 < 50 [outer=(2), constraints=(/2: (/NULL - /49]; tight)] 2911 └── aggregations 2912 └── sum [as=sum:7, outer=(2)] 2913 └── y:2 2914 2915 # COUNT case. Expecting an index scan because opt command is used. 2916 opt expect=PushAggFilterIntoScalarGroupBy 2917 SELECT count(y) FILTER (WHERE y < 50) FROM xyzbs 2918 ---- 2919 scalar-group-by 2920 ├── columns: count:7!null 2921 ├── cardinality: [1 - 1] 2922 ├── key: () 2923 ├── fd: ()-->(7) 2924 ├── scan xyzbs@secondary 2925 │ ├── columns: y:2!null 2926 │ └── constraint: /2/1: (/NULL - /49] 2927 └── aggregations 2928 └── count-rows [as=count:7] 2929 2930 # AVG case. 2931 norm expect=PushAggFilterIntoScalarGroupBy 2932 SELECT avg(y) FILTER (WHERE y < 50) FROM xyzbs 2933 ---- 2934 scalar-group-by 2935 ├── columns: avg:7 2936 ├── cardinality: [1 - 1] 2937 ├── key: () 2938 ├── fd: ()-->(7) 2939 ├── select 2940 │ ├── columns: y:2!null 2941 │ ├── scan xyzbs 2942 │ │ └── columns: y:2 2943 │ └── filters 2944 │ └── y:2 < 50 [outer=(2), constraints=(/2: (/NULL - /49]; tight)] 2945 └── aggregations 2946 └── avg [as=avg:7, outer=(2)] 2947 └── y:2 2948 2949 # MAX case. 2950 norm expect=PushAggFilterIntoScalarGroupBy 2951 SELECT max(y) FILTER (WHERE y < 50) FROM xyzbs 2952 ---- 2953 scalar-group-by 2954 ├── columns: max:7 2955 ├── cardinality: [1 - 1] 2956 ├── key: () 2957 ├── fd: ()-->(7) 2958 ├── select 2959 │ ├── columns: y:2!null 2960 │ ├── scan xyzbs 2961 │ │ └── columns: y:2 2962 │ └── filters 2963 │ └── y:2 < 50 [outer=(2), constraints=(/2: (/NULL - /49]; tight)] 2964 └── aggregations 2965 └── max [as=max:7, outer=(2)] 2966 └── y:2 2967 2968 # BOOL_AND case. 2969 norm expect=PushAggFilterIntoScalarGroupBy 2970 SELECT bool_and(b) FILTER (WHERE b) FROM xyzbs 2971 ---- 2972 scalar-group-by 2973 ├── columns: bool_and:6 2974 ├── cardinality: [1 - 1] 2975 ├── key: () 2976 ├── fd: ()-->(6) 2977 ├── select 2978 │ ├── columns: b:4!null 2979 │ ├── fd: ()-->(4) 2980 │ ├── scan xyzbs 2981 │ │ └── columns: b:4!null 2982 │ └── filters 2983 │ └── b:4 [outer=(4), constraints=(/4: [/true - /true]; tight), fd=()-->(4)] 2984 └── aggregations 2985 └── bool-and [as=bool_and:6, outer=(4)] 2986 └── b:4 2987 2988 # JSON_AGG case. 2989 norm expect=PushAggFilterIntoScalarGroupBy 2990 SELECT json_agg(y) FILTER (WHERE y < 50) FROM xyzbs 2991 ---- 2992 scalar-group-by 2993 ├── columns: json_agg:7 2994 ├── cardinality: [1 - 1] 2995 ├── key: () 2996 ├── fd: ()-->(7) 2997 ├── select 2998 │ ├── columns: y:2!null 2999 │ ├── scan xyzbs 3000 │ │ └── columns: y:2 3001 │ └── filters 3002 │ └── y:2 < 50 [outer=(2), constraints=(/2: (/NULL - /49]; tight)] 3003 └── aggregations 3004 └── json-agg [as=json_agg:7, outer=(2)] 3005 └── y:2 3006 3007 # CORR case. 3008 # Multiple input arguments for aggregate function. 3009 norm expect=PushAggFilterIntoScalarGroupBy 3010 SELECT corr(y, z) FILTER (WHERE y < 50) FROM xyzbs 3011 ---- 3012 scalar-group-by 3013 ├── columns: corr:7 3014 ├── cardinality: [1 - 1] 3015 ├── key: () 3016 ├── fd: ()-->(7) 3017 ├── select 3018 │ ├── columns: y:2!null z:3!null 3019 │ ├── scan xyzbs 3020 │ │ └── columns: y:2 z:3!null 3021 │ └── filters 3022 │ └── y:2 < 50 [outer=(2), constraints=(/2: (/NULL - /49]; tight)] 3023 └── aggregations 3024 └── corr [as=corr:7, outer=(2,3)] 3025 ├── y:2 3026 └── z:3 3027 3028 # STRING_AGG case. 3029 # Multiple input arguments for aggregate function. 3030 norm expect=PushAggFilterIntoScalarGroupBy 3031 SELECT string_agg(s, '-') FILTER (WHERE s < 'abc') FROM xyzbs 3032 ---- 3033 scalar-group-by 3034 ├── columns: string_agg:8 3035 ├── cardinality: [1 - 1] 3036 ├── key: () 3037 ├── fd: ()-->(8) 3038 ├── project 3039 │ ├── columns: column6:6!null s:5!null 3040 │ ├── fd: ()-->(6) 3041 │ ├── select 3042 │ │ ├── columns: s:5!null 3043 │ │ ├── scan xyzbs 3044 │ │ │ └── columns: s:5 3045 │ │ └── filters 3046 │ │ └── s:5 < 'abc' [outer=(5), constraints=(/5: (/NULL - /'abc'); tight)] 3047 │ └── projections 3048 │ └── '-' [as=column6:6] 3049 └── aggregations 3050 └── string-agg [as=string_agg:8, outer=(5,6)] 3051 ├── s:5 3052 └── column6:6 3053 3054 # STRING_AGG case with an ORDER BY. 3055 # Expecting an index scan because opt command is used. 3056 # Multiple input arguments for aggregate function. 3057 opt expect=PushAggFilterIntoScalarGroupBy 3058 SELECT string_agg(s, '-') FILTER (WHERE s < 'abc') FROM (SELECT s FROM xyzbs ORDER BY s) 3059 ---- 3060 scalar-group-by 3061 ├── columns: string_agg:8 3062 ├── internal-ordering: +5 opt(6) 3063 ├── cardinality: [1 - 1] 3064 ├── key: () 3065 ├── fd: ()-->(8) 3066 ├── project 3067 │ ├── columns: column6:6!null s:5!null 3068 │ ├── fd: ()-->(6) 3069 │ ├── ordering: +5 opt(6) [actual: +5] 3070 │ ├── scan xyzbs@secondary 3071 │ │ ├── columns: s:5!null 3072 │ │ ├── constraint: /5/1: (/NULL - /'abc') 3073 │ │ └── ordering: +5 3074 │ └── projections 3075 │ └── '-' [as=column6:6] 3076 └── aggregations 3077 └── string-agg [as=string_agg:8, outer=(5,6)] 3078 ├── s:5 3079 └── column6:6 3080 3081 # Case with multiple conditions. 3082 norm expect=PushAggFilterIntoScalarGroupBy 3083 SELECT count(y) FILTER (WHERE y < 50 AND z > 5) FROM xyzbs 3084 ---- 3085 scalar-group-by 3086 ├── columns: count:7!null 3087 ├── cardinality: [1 - 1] 3088 ├── key: () 3089 ├── fd: ()-->(7) 3090 ├── select 3091 │ ├── columns: y:2!null z:3!null 3092 │ ├── scan xyzbs 3093 │ │ └── columns: y:2 z:3!null 3094 │ └── filters 3095 │ ├── y:2 < 50 [outer=(2), constraints=(/2: (/NULL - /49]; tight)] 3096 │ └── z:3 > 5 [outer=(3), constraints=(/3: [/6 - ]; tight)] 3097 └── aggregations 3098 └── count-rows [as=count:7] 3099 3100 # No-op case where the same aggregate function is called on different 3101 # columns. 3102 norm expect-not=PushAggFilterIntoScalarGroupBy 3103 SELECT count(y) FILTER (WHERE y < 50), count(z) FILTER (WHERE z > 50) FROM xyzbs 3104 ---- 3105 scalar-group-by 3106 ├── columns: count:7!null count:9!null 3107 ├── cardinality: [1 - 1] 3108 ├── key: () 3109 ├── fd: ()-->(7,9) 3110 ├── project 3111 │ ├── columns: column6:6 column8:8!null y:2 z:3!null 3112 │ ├── fd: (2)-->(6), (3)-->(8) 3113 │ ├── scan xyzbs 3114 │ │ └── columns: y:2 z:3!null 3115 │ └── projections 3116 │ ├── y:2 < 50 [as=column6:6, outer=(2)] 3117 │ └── z:3 > 50 [as=column8:8, outer=(3)] 3118 └── aggregations 3119 ├── agg-filter [as=count:7, outer=(2,6)] 3120 │ ├── count 3121 │ │ └── y:2 3122 │ └── column6:6 3123 └── agg-filter [as=count:9, outer=(3,8)] 3124 ├── count 3125 │ └── z:3 3126 └── column8:8 3127 3128 # No-op case where different aggregate functions are called on the same 3129 # column. 3130 norm expect-not=PushAggFilterIntoScalarGroupBy 3131 SELECT count(y) FILTER (WHERE y < 50), sum(y) FILTER (WHERE y < 50) FROM xyzbs 3132 ---- 3133 scalar-group-by 3134 ├── columns: count:7!null sum:8 3135 ├── cardinality: [1 - 1] 3136 ├── key: () 3137 ├── fd: ()-->(7,8) 3138 ├── project 3139 │ ├── columns: column6:6 y:2 3140 │ ├── fd: (2)-->(6) 3141 │ ├── scan xyzbs 3142 │ │ └── columns: y:2 3143 │ └── projections 3144 │ └── y:2 < 50 [as=column6:6, outer=(2)] 3145 └── aggregations 3146 ├── agg-filter [as=count:7, outer=(2,6)] 3147 │ ├── count 3148 │ │ └── y:2 3149 │ └── column6:6 3150 └── agg-filter [as=sum:8, outer=(2,6)] 3151 ├── sum 3152 │ └── y:2 3153 └── column6:6 3154 3155 # -------------------------------------------------- 3156 # ConvertCountToCountRows 3157 # -------------------------------------------------- 3158 3159 # ScalarGroupBy cases. 3160 norm expect=ConvertCountToCountRows 3161 SELECT count(z) FROM xyzbs 3162 ---- 3163 scalar-group-by 3164 ├── columns: count:6!null 3165 ├── cardinality: [1 - 1] 3166 ├── key: () 3167 ├── fd: ()-->(6) 3168 ├── scan xyzbs 3169 └── aggregations 3170 └── count-rows [as=count:6] 3171 3172 norm expect=ConvertCountToCountRows 3173 SELECT count(1) FROM xyzbs 3174 ---- 3175 scalar-group-by 3176 ├── columns: count:7!null 3177 ├── cardinality: [1 - 1] 3178 ├── key: () 3179 ├── fd: ()-->(7) 3180 ├── scan xyzbs 3181 └── aggregations 3182 └── count-rows [as=count:7] 3183 3184 norm expect=ConvertCountToCountRows 3185 SELECT count(1 + z) FROM xyzbs 3186 ---- 3187 scalar-group-by 3188 ├── columns: count:7!null 3189 ├── cardinality: [1 - 1] 3190 ├── key: () 3191 ├── fd: ()-->(7) 3192 ├── scan xyzbs 3193 └── aggregations 3194 └── count-rows [as=count:7] 3195 3196 # GroupBy cases. 3197 norm expect=ConvertCountToCountRows 3198 SELECT count(z) FROM xyzbs GROUP BY s 3199 ---- 3200 project 3201 ├── columns: count:6!null 3202 └── group-by 3203 ├── columns: s:5 count:6!null 3204 ├── grouping columns: s:5 3205 ├── key: (5) 3206 ├── fd: (5)-->(6) 3207 ├── scan xyzbs 3208 │ └── columns: s:5 3209 └── aggregations 3210 └── count-rows [as=count:6] 3211 3212 norm expect=ConvertCountToCountRows 3213 SELECT count(1) FROM xyzbs GROUP BY s 3214 ---- 3215 project 3216 ├── columns: count:7!null 3217 └── group-by 3218 ├── columns: s:5 count:7!null 3219 ├── grouping columns: s:5 3220 ├── key: (5) 3221 ├── fd: (5)-->(7) 3222 ├── scan xyzbs 3223 │ └── columns: s:5 3224 └── aggregations 3225 └── count-rows [as=count:7] 3226 3227 norm expect=ConvertCountToCountRows 3228 SELECT count(1+z) FROM xyzbs GROUP BY s 3229 ---- 3230 project 3231 ├── columns: count:7!null 3232 └── group-by 3233 ├── columns: s:5 count:7!null 3234 ├── grouping columns: s:5 3235 ├── key: (5) 3236 ├── fd: (5)-->(7) 3237 ├── scan xyzbs 3238 │ └── columns: s:5 3239 └── aggregations 3240 └── count-rows [as=count:7] 3241 3242 # Case with multiple aggregate functions. 3243 # Expecting to activate on z and b but not y, because y can be null. 3244 norm expect=ConvertCountToCountRows 3245 SELECT count(y), corr(y, z), count(z), sum(y), count(b) FROM xyzbs 3246 ---- 3247 scalar-group-by 3248 ├── columns: count:6!null corr:7 count:8!null sum:9 count:10!null 3249 ├── cardinality: [1 - 1] 3250 ├── key: () 3251 ├── fd: ()-->(6-10) 3252 ├── scan xyzbs 3253 │ └── columns: y:2 z:3!null 3254 └── aggregations 3255 ├── count [as=count:6, outer=(2)] 3256 │ └── y:2 3257 ├── corr [as=corr:7, outer=(2,3)] 3258 │ ├── y:2 3259 │ └── z:3 3260 ├── count-rows [as=count:8] 3261 ├── sum [as=sum:9, outer=(2)] 3262 │ └── y:2 3263 └── count-rows [as=count:10] 3264 3265 # No-op case because y can contain nulls. 3266 norm expect-not=ConvertCountToCountRows 3267 SELECT count(y) FROM xyzbs 3268 ---- 3269 scalar-group-by 3270 ├── columns: count:6!null 3271 ├── cardinality: [1 - 1] 3272 ├── key: () 3273 ├── fd: ()-->(6) 3274 ├── scan xyzbs 3275 │ └── columns: y:2 3276 └── aggregations 3277 └── count [as=count:6, outer=(2)] 3278 └── y:2 3279 3280 # No-op case because the DISTINCT requires the count input column, so the count 3281 # can't be eliminated. 3282 norm 3283 SELECT count(DISTINCT y) FROM xyzbs GROUP BY z 3284 ---- 3285 project 3286 ├── columns: count:6!null 3287 └── group-by 3288 ├── columns: z:3!null count:6!null 3289 ├── grouping columns: z:3!null 3290 ├── key: (3) 3291 ├── fd: (3)-->(6) 3292 ├── scan xyzbs 3293 │ └── columns: y:2 z:3!null 3294 └── aggregations 3295 └── agg-distinct [as=count:6, outer=(2)] 3296 └── count 3297 └── y:2 3298 3299 # -------------------------------------------------- 3300 # FoldGroupingOperators 3301 # -------------------------------------------------- 3302 3303 # Case with sum aggregate. 3304 norm expect=FoldGroupingOperators 3305 SELECT sum(s) FROM (SELECT sum(x) FROM xy GROUP BY y) AS f(s) 3306 ---- 3307 scalar-group-by 3308 ├── columns: sum:4 3309 ├── cardinality: [1 - 1] 3310 ├── key: () 3311 ├── fd: ()-->(4) 3312 ├── scan xy 3313 │ ├── columns: x:1!null 3314 │ └── key: (1) 3315 └── aggregations 3316 └── sum [as=sum:4, outer=(1)] 3317 └── x:1 3318 3319 # Case with count-rows aggregate. 3320 norm expect=FoldGroupingOperators 3321 SELECT sum_int(c) FROM (SELECT count(x) FROM xy GROUP BY y) AS f(c) 3322 ---- 3323 scalar-group-by 3324 ├── columns: sum_int:4!null 3325 ├── cardinality: [1 - 1] 3326 ├── key: () 3327 ├── fd: ()-->(4) 3328 ├── scan xy 3329 └── aggregations 3330 └── count-rows [as=sum_int:4] 3331 3332 # Case with a count aggregate. 3333 norm expect=FoldGroupingOperators 3334 SELECT sum_int(cnt) FROM (SELECT count(c2) FROM nullablecols GROUP BY c1) AS f(cnt) 3335 ---- 3336 scalar-group-by 3337 ├── columns: sum_int:6!null 3338 ├── cardinality: [1 - 1] 3339 ├── key: () 3340 ├── fd: ()-->(6) 3341 ├── scan nullablecols 3342 │ └── columns: c2:2 3343 └── aggregations 3344 └── count [as=sum_int:6, outer=(2)] 3345 └── c2:2 3346 3347 # Case with max aggregate. 3348 norm expect=FoldGroupingOperators 3349 SELECT max(m) FROM (SELECT max(x) FROM xy GROUP BY y) AS f(m) 3350 ---- 3351 scalar-group-by 3352 ├── columns: max:4 3353 ├── cardinality: [1 - 1] 3354 ├── key: () 3355 ├── fd: ()-->(4) 3356 ├── scan xy 3357 │ ├── columns: x:1!null 3358 │ └── key: (1) 3359 └── aggregations 3360 └── max [as=max:4, outer=(1)] 3361 └── x:1 3362 3363 # Case with bit_and aggregate. 3364 norm expect=FoldGroupingOperators 3365 SELECT bit_and(b) FROM (SELECT bit_and(x) FROM xy GROUP BY y) AS f(b) 3366 ---- 3367 scalar-group-by 3368 ├── columns: bit_and:4 3369 ├── cardinality: [1 - 1] 3370 ├── key: () 3371 ├── fd: ()-->(4) 3372 ├── scan xy 3373 │ ├── columns: x:1!null 3374 │ └── key: (1) 3375 └── aggregations 3376 └── bit-and-agg [as=bit_and:4, outer=(1)] 3377 └── x:1 3378 3379 # Case with multiple aggregates. 3380 norm expect=FoldGroupingOperators 3381 SELECT max(m), sum(s), sum_int(c) 3382 FROM (SELECT sum(b), count(c), max(b) FROM abc GROUP BY a) 3383 AS f(s, c, m) 3384 ---- 3385 scalar-group-by 3386 ├── columns: max:7 sum:8 sum_int:9!null 3387 ├── cardinality: [1 - 1] 3388 ├── key: () 3389 ├── fd: ()-->(7-9) 3390 ├── scan abc 3391 │ └── columns: b:2!null 3392 └── aggregations 3393 ├── max [as=max:7, outer=(2)] 3394 │ └── b:2 3395 ├── sum [as=sum:8, outer=(2)] 3396 │ └── b:2 3397 └── count-rows [as=sum_int:9] 3398 3399 # GroupBy on GroupBy case where the inner grouping columns determine the outer 3400 # grouping columns, but they do not intersect. 3401 norm expect=FoldGroupingOperators 3402 SELECT sum(s) FROM (SELECT y, sum(x) AS s FROM xy GROUP BY x) GROUP BY y 3403 ---- 3404 project 3405 ├── columns: sum:4!null 3406 └── group-by 3407 ├── columns: y:2 sum:4!null 3408 ├── grouping columns: y:2 3409 ├── key: (2) 3410 ├── fd: (2)-->(4) 3411 ├── scan xy 3412 │ ├── columns: x:1!null y:2 3413 │ ├── key: (1) 3414 │ └── fd: (1)-->(2) 3415 └── aggregations 3416 └── sum [as=sum:4, outer=(1)] 3417 └── x:1 3418 3419 # GroupBy on GroupBy case with multiple-column grouping. 3420 norm expect=FoldGroupingOperators 3421 SELECT sum(s) FROM (SELECT a, sum(c) AS s FROM abc GROUP BY a, b) GROUP BY a 3422 ---- 3423 project 3424 ├── columns: sum:5!null 3425 └── group-by 3426 ├── columns: a:1!null sum:5!null 3427 ├── grouping columns: a:1!null 3428 ├── key: (1) 3429 ├── fd: (1)-->(5) 3430 ├── scan abc 3431 │ └── columns: a:1!null c:3!null 3432 └── aggregations 3433 └── sum [as=sum:5, outer=(3)] 3434 └── c:3 3435 3436 # No-op case with an AvgOp. Note: this query actually could be folded if the 3437 # groups were known to be of the same size. 3438 norm expect-not=FoldGroupingOperators 3439 SELECT sum(a) FROM (SELECT avg(x) FROM xy GROUP BY y) AS f(a) 3440 ---- 3441 scalar-group-by 3442 ├── columns: sum:4 3443 ├── cardinality: [1 - 1] 3444 ├── key: () 3445 ├── fd: ()-->(4) 3446 ├── group-by 3447 │ ├── columns: y:2 avg:3!null 3448 │ ├── grouping columns: y:2 3449 │ ├── key: (2) 3450 │ ├── fd: (2)-->(3) 3451 │ ├── scan xy 3452 │ │ ├── columns: x:1!null y:2 3453 │ │ ├── key: (1) 3454 │ │ └── fd: (1)-->(2) 3455 │ └── aggregations 3456 │ └── avg [as=avg:3, outer=(1)] 3457 │ └── x:1 3458 └── aggregations 3459 └── sum [as=sum:4, outer=(3)] 3460 └── avg:3 3461 3462 # No-op case with several valid aggregate pairs and one invalid pair. 3463 norm expect-not=FoldGroupingOperators 3464 SELECT sum(c), sum(s), max(s) FROM (SELECT sum(x), count(x) FROM xy GROUP BY y) AS f(s, c) 3465 ---- 3466 scalar-group-by 3467 ├── columns: sum:5 sum:6 max:7 3468 ├── cardinality: [1 - 1] 3469 ├── key: () 3470 ├── fd: ()-->(5-7) 3471 ├── group-by 3472 │ ├── columns: y:2 sum:3!null count:4!null 3473 │ ├── grouping columns: y:2 3474 │ ├── key: (2) 3475 │ ├── fd: (2)-->(3,4) 3476 │ ├── scan xy 3477 │ │ ├── columns: x:1!null y:2 3478 │ │ ├── key: (1) 3479 │ │ └── fd: (1)-->(2) 3480 │ └── aggregations 3481 │ ├── sum [as=sum:3, outer=(1)] 3482 │ │ └── x:1 3483 │ └── count-rows [as=count:4] 3484 └── aggregations 3485 ├── sum [as=sum:5, outer=(4)] 3486 │ └── count:4 3487 ├── sum [as=sum:6, outer=(3)] 3488 │ └── sum:3 3489 └── max [as=max:7, outer=(3)] 3490 └── sum:3 3491 3492 # No-op case because the outer grouping columns are not functionally determined 3493 # by the inner grouping columns in the functional dependencies of the input of 3494 # the inner grouping operator. 3495 norm expect-not=FoldGroupingOperators 3496 SELECT max(m) FROM (SELECT max(x) AS m, sum(x) AS s FROM xy GROUP BY y) GROUP BY s 3497 ---- 3498 project 3499 ├── columns: max:5!null 3500 └── group-by 3501 ├── columns: sum:4!null max:5!null 3502 ├── grouping columns: sum:4!null 3503 ├── key: (4) 3504 ├── fd: (4)-->(5) 3505 ├── group-by 3506 │ ├── columns: y:2 max:3!null sum:4!null 3507 │ ├── grouping columns: y:2 3508 │ ├── key: (2) 3509 │ ├── fd: (2)-->(3,4) 3510 │ ├── scan xy 3511 │ │ ├── columns: x:1!null y:2 3512 │ │ ├── key: (1) 3513 │ │ └── fd: (1)-->(2) 3514 │ └── aggregations 3515 │ ├── max [as=max:3, outer=(1)] 3516 │ │ └── x:1 3517 │ └── sum [as=sum:4, outer=(1)] 3518 │ └── x:1 3519 └── aggregations 3520 └── max [as=max:5, outer=(3)] 3521 └── max:3 3522 3523 # No-op case because one of the grouping operators has an internal ordering. The 3524 # array_agg ensures that the GroupBy has an internal ordering. 3525 norm expect-not=FoldGroupingOperators 3526 SELECT sum(s) FROM (SELECT sum(z) AS s, array_agg(z) FROM (SELECT * FROM uvwz ORDER BY w DESC) GROUP BY u) 3527 ---- 3528 scalar-group-by 3529 ├── columns: sum:8 3530 ├── cardinality: [1 - 1] 3531 ├── key: () 3532 ├── fd: ()-->(8) 3533 ├── group-by 3534 │ ├── columns: u:1!null sum:6!null 3535 │ ├── grouping columns: u:1!null 3536 │ ├── internal-ordering: -3 opt(1) 3537 │ ├── key: (1) 3538 │ ├── fd: (1)-->(6) 3539 │ ├── sort 3540 │ │ ├── columns: u:1!null w:3!null z:4!null 3541 │ │ ├── ordering: -3 opt(1) [actual: -3] 3542 │ │ └── scan uvwz 3543 │ │ └── columns: u:1!null w:3!null z:4!null 3544 │ └── aggregations 3545 │ └── sum [as=sum:6, outer=(4)] 3546 │ └── z:4 3547 └── aggregations 3548 └── sum [as=sum:8, outer=(6)] 3549 └── sum:6