github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/norm/testdata/rules/prune_cols (about) 1 exec-ddl 2 CREATE TABLE a (k INT PRIMARY KEY, i INT, f FLOAT, s STRING) 3 ---- 4 5 exec-ddl 6 CREATE TABLE xy (x INT PRIMARY KEY, y INT) 7 ---- 8 9 exec-ddl 10 CREATE TABLE abcde ( 11 a INT PRIMARY KEY, 12 b INT, 13 c INT, 14 d INT, 15 e INT, 16 UNIQUE INDEX bc (b, c) 17 ) 18 ---- 19 20 exec-ddl 21 CREATE TABLE mutation ( 22 a INT PRIMARY KEY, 23 b INT, 24 c INT, 25 "d:write-only" INT, 26 "e:delete-only" INT, 27 UNIQUE INDEX "idx1:write-only" (b, d), 28 INDEX "idx2:delete-only" (e) 29 ) 30 ---- 31 32 exec-ddl 33 CREATE TABLE family ( 34 a INT PRIMARY KEY, 35 b INT, 36 c INT, 37 d INT, 38 e INT, 39 FAMILY (a, b), 40 FAMILY (c, d), 41 FAMILY (e), 42 INDEX (d) 43 ) 44 ---- 45 46 # -------------------------------------------------- 47 # PruneProjectCols 48 # -------------------------------------------------- 49 50 # Discard some of columns. 51 norm expect=PruneProjectCols 52 SELECT k1*2 FROM (SELECT k+1 AS k1, i+1 FROM a) a 53 ---- 54 project 55 ├── columns: "?column?":7!null 56 ├── scan a 57 │ ├── columns: k:1!null 58 │ └── key: (1) 59 └── projections 60 └── (k:1 + 1) * 2 [as="?column?":7, outer=(1)] 61 62 # Use column values within computed column. 63 norm expect=PruneProjectCols 64 SELECT k+length(s) AS r FROM (SELECT i, k, s || 'foo' AS s FROM a) a 65 ---- 66 project 67 ├── columns: r:6 68 ├── immutable 69 ├── scan a 70 │ ├── columns: k:1!null a.s:4 71 │ ├── key: (1) 72 │ └── fd: (1)-->(4) 73 └── projections 74 └── k:1 + length(a.s:4 || 'foo') [as=r:6, outer=(1,4), immutable] 75 76 # Discard non-computed columns and keep computed column. 77 norm expect=PruneProjectCols 78 SELECT l, l*2, k FROM (SELECT length(s) l, * FROM a) a 79 ---- 80 project 81 ├── columns: l:5 "?column?":6 k:1!null 82 ├── immutable 83 ├── key: (1) 84 ├── fd: (1)-->(5), (5)-->(6) 85 ├── project 86 │ ├── columns: l:5 k:1!null 87 │ ├── immutable 88 │ ├── key: (1) 89 │ ├── fd: (1)-->(5) 90 │ ├── scan a 91 │ │ ├── columns: k:1!null s:4 92 │ │ ├── key: (1) 93 │ │ └── fd: (1)-->(4) 94 │ └── projections 95 │ └── length(s:4) [as=l:5, outer=(4), immutable] 96 └── projections 97 └── l:5 * 2 [as="?column?":6, outer=(5)] 98 99 # Compute column based on another computed column. 100 norm expect=PruneProjectCols 101 SELECT l*l AS r, k FROM (SELECT k, length(s) l, i FROM a) a 102 ---- 103 project 104 ├── columns: r:6 k:1!null 105 ├── immutable 106 ├── key: (1) 107 ├── fd: (1)-->(6) 108 ├── project 109 │ ├── columns: l:5 k:1!null 110 │ ├── immutable 111 │ ├── key: (1) 112 │ ├── fd: (1)-->(5) 113 │ ├── scan a 114 │ │ ├── columns: k:1!null s:4 115 │ │ ├── key: (1) 116 │ │ └── fd: (1)-->(4) 117 │ └── projections 118 │ └── length(s:4) [as=l:5, outer=(4), immutable] 119 └── projections 120 └── l:5 * l:5 [as=r:6, outer=(5)] 121 122 # -------------------------------------------------- 123 # PruneScanCols 124 # -------------------------------------------------- 125 126 # Project subset of columns. 127 norm expect=PruneScanCols 128 SELECT k FROM a 129 ---- 130 scan a 131 ├── columns: k:1!null 132 └── key: (1) 133 134 # Project subset of columns, some used in computed columns. 135 norm expect=PruneScanCols 136 SELECT k, k+1 AS r, i+1 AS s FROM a 137 ---- 138 project 139 ├── columns: k:1!null r:5!null s:6 140 ├── key: (1) 141 ├── fd: (1)-->(5,6) 142 ├── scan a 143 │ ├── columns: k:1!null i:2 144 │ ├── key: (1) 145 │ └── fd: (1)-->(2) 146 └── projections 147 ├── k:1 + 1 [as=r:5, outer=(1)] 148 └── i:2 + 1 [as=s:6, outer=(2)] 149 150 # Use columns only in computed columns. 151 norm expect=PruneScanCols 152 SELECT k+i AS r FROM a 153 ---- 154 project 155 ├── columns: r:5 156 ├── scan a 157 │ ├── columns: k:1!null i:2 158 │ ├── key: (1) 159 │ └── fd: (1)-->(2) 160 └── projections 161 └── k:1 + i:2 [as=r:5, outer=(1,2)] 162 163 # Use no scan columns. 164 norm expect=PruneScanCols 165 SELECT 1 r FROM a 166 ---- 167 project 168 ├── columns: r:5!null 169 ├── fd: ()-->(5) 170 ├── scan a 171 └── projections 172 └── 1 [as=r:5] 173 174 # -------------------------------------------------- 175 # PruneSelectCols 176 # -------------------------------------------------- 177 178 # Columns used only by projection or filter, but not both. 179 norm expect=PruneSelectCols 180 SELECT k FROM a WHERE i<5 181 ---- 182 project 183 ├── columns: k:1!null 184 ├── key: (1) 185 └── select 186 ├── columns: k:1!null i:2!null 187 ├── key: (1) 188 ├── fd: (1)-->(2) 189 ├── scan a 190 │ ├── columns: k:1!null i:2 191 │ ├── key: (1) 192 │ └── fd: (1)-->(2) 193 └── filters 194 └── i:2 < 5 [outer=(2), constraints=(/2: (/NULL - /4]; tight)] 195 196 # Columns used by both projection and filter. 197 norm expect=PruneSelectCols 198 SELECT k, i FROM a WHERE k=1 AND i<5 199 ---- 200 select 201 ├── columns: k:1!null i:2!null 202 ├── cardinality: [0 - 1] 203 ├── key: () 204 ├── fd: ()-->(1,2) 205 ├── scan a 206 │ ├── columns: k:1!null i:2 207 │ ├── key: (1) 208 │ └── fd: (1)-->(2) 209 └── filters 210 ├── k:1 = 1 [outer=(1), constraints=(/1: [/1 - /1]; tight), fd=()-->(1)] 211 └── i:2 < 5 [outer=(2), constraints=(/2: (/NULL - /4]; tight)] 212 213 # No needed select columns. 214 norm expect=PruneSelectCols 215 SELECT 1 r FROM a WHERE $1<'2000-01-01T02:00:00'::timestamp 216 ---- 217 project 218 ├── columns: r:5!null 219 ├── has-placeholder 220 ├── fd: ()-->(5) 221 ├── select 222 │ ├── has-placeholder 223 │ ├── scan a 224 │ └── filters 225 │ └── $1 < '2000-01-01 02:00:00+00:00' 226 └── projections 227 └── 1 [as=r:5] 228 229 # Select columns used in computed columns. 230 norm expect=PruneSelectCols 231 SELECT i-1 AS r, k*k AS t FROM a WHERE k+1<5 AND s||'o'='foo' 232 ---- 233 project 234 ├── columns: r:5 t:6!null 235 ├── select 236 │ ├── columns: k:1!null i:2 s:4 237 │ ├── key: (1) 238 │ ├── fd: (1)-->(2,4) 239 │ ├── scan a 240 │ │ ├── columns: k:1!null i:2 s:4 241 │ │ ├── key: (1) 242 │ │ └── fd: (1)-->(2,4) 243 │ └── filters 244 │ ├── k:1 < 4 [outer=(1), constraints=(/1: (/NULL - /3]; tight)] 245 │ └── (s:4 || 'o') = 'foo' [outer=(4)] 246 └── projections 247 ├── i:2 - 1 [as=r:5, outer=(2)] 248 └── k:1 * k:1 [as=t:6, outer=(1)] 249 250 # Select nested in select. 251 norm expect=PruneSelectCols 252 SELECT i FROM (SELECT k, i, s, f/2.0 f FROM a WHERE k = 5) a2 WHERE i::float = f 253 ---- 254 project 255 ├── columns: i:2 256 ├── cardinality: [0 - 1] 257 ├── key: () 258 ├── fd: ()-->(2) 259 └── select 260 ├── columns: i:2 f:5!null 261 ├── cardinality: [0 - 1] 262 ├── key: () 263 ├── fd: ()-->(2,5) 264 ├── project 265 │ ├── columns: f:5 i:2 266 │ ├── cardinality: [0 - 1] 267 │ ├── key: () 268 │ ├── fd: ()-->(2,5) 269 │ ├── select 270 │ │ ├── columns: k:1!null i:2 a.f:3 271 │ │ ├── cardinality: [0 - 1] 272 │ │ ├── key: () 273 │ │ ├── fd: ()-->(1-3) 274 │ │ ├── scan a 275 │ │ │ ├── columns: k:1!null i:2 a.f:3 276 │ │ │ ├── key: (1) 277 │ │ │ └── fd: (1)-->(2,3) 278 │ │ └── filters 279 │ │ └── k:1 = 5 [outer=(1), constraints=(/1: [/5 - /5]; tight), fd=()-->(1)] 280 │ └── projections 281 │ └── a.f:3 / 2.0 [as=f:5, outer=(3)] 282 └── filters 283 └── f:5 = i:2::FLOAT8 [outer=(2,5), constraints=(/5: (/NULL - ])] 284 285 # Detect PruneSelectCols and PushSelectIntoProject dependency cycle. 286 norm 287 SELECT f, f+1.1 AS r FROM (SELECT f, k FROM a GROUP BY f, k HAVING sum(k)=100) a 288 ---- 289 project 290 ├── columns: f:3 r:6 291 ├── select 292 │ ├── columns: k:1!null f:3 sum:5!null 293 │ ├── key: (1) 294 │ ├── fd: ()-->(5), (1)-->(3) 295 │ ├── group-by 296 │ │ ├── columns: k:1!null f:3 sum:5!null 297 │ │ ├── grouping columns: k:1!null 298 │ │ ├── key: (1) 299 │ │ ├── fd: (1)-->(3,5) 300 │ │ ├── scan a 301 │ │ │ ├── columns: k:1!null f:3 302 │ │ │ ├── key: (1) 303 │ │ │ └── fd: (1)-->(3) 304 │ │ └── aggregations 305 │ │ ├── sum [as=sum:5, outer=(1)] 306 │ │ │ └── k:1 307 │ │ └── const-agg [as=f:3, outer=(3)] 308 │ │ └── f:3 309 │ └── filters 310 │ └── sum:5 = 100 [outer=(5), constraints=(/5: [/100 - /100]; tight), fd=()-->(5)] 311 └── projections 312 └── f:3 + 1.1 [as=r:6, outer=(3)] 313 314 # -------------------------------------------------- 315 # PruneLimitCols 316 # -------------------------------------------------- 317 318 norm expect=PruneLimitCols 319 SELECT i FROM (SELECT i, s FROM a LIMIT 1) 320 ---- 321 limit 322 ├── columns: i:2 323 ├── cardinality: [0 - 1] 324 ├── key: () 325 ├── fd: ()-->(2) 326 ├── scan a 327 │ ├── columns: i:2 328 │ └── limit hint: 1.00 329 └── 1 330 331 # The projection on top of Limit should trickle down and we shouldn't scan f. 332 norm expect=PruneLimitCols 333 SELECT k FROM (SELECT k, i, f FROM a ORDER BY i LIMIT 10) 334 ---- 335 project 336 ├── columns: k:1!null 337 ├── cardinality: [0 - 10] 338 ├── key: (1) 339 └── limit 340 ├── columns: k:1!null i:2 341 ├── internal-ordering: +2 342 ├── cardinality: [0 - 10] 343 ├── key: (1) 344 ├── fd: (1)-->(2) 345 ├── sort 346 │ ├── columns: k:1!null i:2 347 │ ├── key: (1) 348 │ ├── fd: (1)-->(2) 349 │ ├── ordering: +2 350 │ ├── limit hint: 10.00 351 │ └── scan a 352 │ ├── columns: k:1!null i:2 353 │ ├── key: (1) 354 │ └── fd: (1)-->(2) 355 └── 10 356 357 # We should scan k, i, s. 358 norm expect=PruneLimitCols 359 SELECT s FROM (SELECT k, i, f, s FROM a ORDER BY i, k LIMIT 10) 360 ---- 361 project 362 ├── columns: s:4 363 ├── cardinality: [0 - 10] 364 └── limit 365 ├── columns: k:1!null i:2 s:4 366 ├── internal-ordering: +2,+1 367 ├── cardinality: [0 - 10] 368 ├── key: (1) 369 ├── fd: (1)-->(2,4) 370 ├── sort 371 │ ├── columns: k:1!null i:2 s:4 372 │ ├── key: (1) 373 │ ├── fd: (1)-->(2,4) 374 │ ├── ordering: +2,+1 375 │ ├── limit hint: 10.00 376 │ └── scan a 377 │ ├── columns: k:1!null i:2 s:4 378 │ ├── key: (1) 379 │ └── fd: (1)-->(2,4) 380 └── 10 381 382 # We should scan k, i, s. 383 norm expect=PruneLimitCols 384 SELECT k, s FROM (SELECT k, i, f, s FROM a ORDER BY i, k LIMIT 10) 385 ---- 386 project 387 ├── columns: k:1!null s:4 388 ├── cardinality: [0 - 10] 389 ├── key: (1) 390 ├── fd: (1)-->(4) 391 └── limit 392 ├── columns: k:1!null i:2 s:4 393 ├── internal-ordering: +2,+1 394 ├── cardinality: [0 - 10] 395 ├── key: (1) 396 ├── fd: (1)-->(2,4) 397 ├── sort 398 │ ├── columns: k:1!null i:2 s:4 399 │ ├── key: (1) 400 │ ├── fd: (1)-->(2,4) 401 │ ├── ordering: +2,+1 402 │ ├── limit hint: 10.00 403 │ └── scan a 404 │ ├── columns: k:1!null i:2 s:4 405 │ ├── key: (1) 406 │ └── fd: (1)-->(2,4) 407 └── 10 408 409 # Project uses subset of Limit columns, but no additional Project should be 410 # introduced to tree, because it can't be pushed down to Scan. 411 norm 412 SELECT f, f*2.0 AS r FROM (SELECT f, s FROM a GROUP BY f, s LIMIT 5) a 413 ---- 414 project 415 ├── columns: f:3 r:5 416 ├── cardinality: [0 - 5] 417 ├── limit 418 │ ├── columns: f:3 s:4 419 │ ├── cardinality: [0 - 5] 420 │ ├── key: (3,4) 421 │ ├── distinct-on 422 │ │ ├── columns: f:3 s:4 423 │ │ ├── grouping columns: f:3 s:4 424 │ │ ├── key: (3,4) 425 │ │ ├── limit hint: 5.00 426 │ │ └── scan a 427 │ │ ├── columns: f:3 s:4 428 │ │ └── limit hint: 6.02 429 │ └── 5 430 └── projections 431 └── f:3 * 2.0 [as=r:5, outer=(3)] 432 433 # -------------------------------------------------- 434 # PruneOffsetCols 435 # -------------------------------------------------- 436 437 norm expect=PruneOffsetCols 438 SELECT f FROM (SELECT * FROM a OFFSET 1) 439 ---- 440 offset 441 ├── columns: f:3 442 ├── scan a 443 │ └── columns: f:3 444 └── 1 445 446 norm expect=PruneOffsetCols 447 SELECT k FROM (SELECT k, i, f FROM a ORDER BY i OFFSET 10) 448 ---- 449 project 450 ├── columns: k:1!null 451 ├── key: (1) 452 └── offset 453 ├── columns: k:1!null i:2 454 ├── internal-ordering: +2 455 ├── key: (1) 456 ├── fd: (1)-->(2) 457 ├── sort 458 │ ├── columns: k:1!null i:2 459 │ ├── key: (1) 460 │ ├── fd: (1)-->(2) 461 │ ├── ordering: +2 462 │ └── scan a 463 │ ├── columns: k:1!null i:2 464 │ ├── key: (1) 465 │ └── fd: (1)-->(2) 466 └── 10 467 468 # We should scan k, i, s. 469 norm expect=PruneOffsetCols 470 SELECT s FROM (SELECT k, i, f, s FROM a ORDER BY i, k OFFSET 10) 471 ---- 472 project 473 ├── columns: s:4 474 └── offset 475 ├── columns: k:1!null i:2 s:4 476 ├── internal-ordering: +2,+1 477 ├── key: (1) 478 ├── fd: (1)-->(2,4) 479 ├── sort 480 │ ├── columns: k:1!null i:2 s:4 481 │ ├── key: (1) 482 │ ├── fd: (1)-->(2,4) 483 │ ├── ordering: +2,+1 484 │ └── scan a 485 │ ├── columns: k:1!null i:2 s:4 486 │ ├── key: (1) 487 │ └── fd: (1)-->(2,4) 488 └── 10 489 490 # We should scan k, i, s. 491 norm expect=PruneOffsetCols 492 SELECT k, s FROM (SELECT k, i, f, s FROM a ORDER BY i, k OFFSET 10) 493 ---- 494 project 495 ├── columns: k:1!null s:4 496 ├── key: (1) 497 ├── fd: (1)-->(4) 498 └── offset 499 ├── columns: k:1!null i:2 s:4 500 ├── internal-ordering: +2,+1 501 ├── key: (1) 502 ├── fd: (1)-->(2,4) 503 ├── sort 504 │ ├── columns: k:1!null i:2 s:4 505 │ ├── key: (1) 506 │ ├── fd: (1)-->(2,4) 507 │ ├── ordering: +2,+1 508 │ └── scan a 509 │ ├── columns: k:1!null i:2 s:4 510 │ ├── key: (1) 511 │ └── fd: (1)-->(2,4) 512 └── 10 513 514 # Project uses subset of Offset columns, but no additional Project should be 515 # introduced to tree, because it can't be pushed down past Explain. 516 norm 517 SELECT tree, columns 518 FROM 519 ( 520 SELECT * 521 FROM [ EXPLAIN (VERBOSE) SELECT * FROM a ] 522 ORDER BY tree 523 OFFSET 1 524 ) 525 ---- 526 offset 527 ├── columns: tree:12 columns:17 528 ├── internal-ordering: +12 529 ├── sort 530 │ ├── columns: tree:12 columns:17 531 │ ├── ordering: +12 532 │ └── project 533 │ ├── columns: tree:12 columns:17 534 │ ├── explain 535 │ │ ├── columns: tree:5 level:6 node_type:7 field:8 description:9 columns:10 ordering:11 536 │ │ ├── mode: verbose 537 │ │ └── scan a 538 │ │ ├── columns: k:1!null i:2 f:3 s:4 539 │ │ ├── key: (1) 540 │ │ └── fd: (1)-->(2-4) 541 │ └── projections 542 │ ├── tree:5 [as=tree:12, outer=(5)] 543 │ └── columns:10 [as=columns:17, outer=(10)] 544 └── 1 545 546 # -------------------------------------------------- 547 # PruneLimitCols + PruneOffsetCols 548 # -------------------------------------------------- 549 550 norm expect=(PruneLimitCols,PruneOffsetCols) 551 SELECT k FROM (SELECT k, i, f FROM a ORDER BY i LIMIT 10 OFFSET 10) 552 ---- 553 project 554 ├── columns: k:1!null 555 ├── cardinality: [0 - 10] 556 ├── key: (1) 557 └── offset 558 ├── columns: k:1!null i:2 559 ├── internal-ordering: +2 560 ├── cardinality: [0 - 10] 561 ├── key: (1) 562 ├── fd: (1)-->(2) 563 ├── limit 564 │ ├── columns: k:1!null i:2 565 │ ├── internal-ordering: +2 566 │ ├── cardinality: [0 - 20] 567 │ ├── key: (1) 568 │ ├── fd: (1)-->(2) 569 │ ├── ordering: +2 570 │ ├── sort 571 │ │ ├── columns: k:1!null i:2 572 │ │ ├── key: (1) 573 │ │ ├── fd: (1)-->(2) 574 │ │ ├── ordering: +2 575 │ │ ├── limit hint: 20.00 576 │ │ └── scan a 577 │ │ ├── columns: k:1!null i:2 578 │ │ ├── key: (1) 579 │ │ └── fd: (1)-->(2) 580 │ └── 20 581 └── 10 582 583 # We should scan k, i, s. 584 norm expect=(PruneLimitCols,PruneOffsetCols) 585 SELECT s FROM (SELECT k, i, f, s FROM a ORDER BY i, k LIMIT 10 OFFSET 10) 586 ---- 587 project 588 ├── columns: s:4 589 ├── cardinality: [0 - 10] 590 └── offset 591 ├── columns: k:1!null i:2 s:4 592 ├── internal-ordering: +2,+1 593 ├── cardinality: [0 - 10] 594 ├── key: (1) 595 ├── fd: (1)-->(2,4) 596 ├── limit 597 │ ├── columns: k:1!null i:2 s:4 598 │ ├── internal-ordering: +2,+1 599 │ ├── cardinality: [0 - 20] 600 │ ├── key: (1) 601 │ ├── fd: (1)-->(2,4) 602 │ ├── ordering: +2,+1 603 │ ├── sort 604 │ │ ├── columns: k:1!null i:2 s:4 605 │ │ ├── key: (1) 606 │ │ ├── fd: (1)-->(2,4) 607 │ │ ├── ordering: +2,+1 608 │ │ ├── limit hint: 20.00 609 │ │ └── scan a 610 │ │ ├── columns: k:1!null i:2 s:4 611 │ │ ├── key: (1) 612 │ │ └── fd: (1)-->(2,4) 613 │ └── 20 614 └── 10 615 616 # We should scan k, i, s. 617 norm expect=(PruneLimitCols,PruneOffsetCols) 618 SELECT k, s FROM (SELECT k, i, f, s FROM a ORDER BY i, k LIMIT 10 OFFSET 10) 619 ---- 620 project 621 ├── columns: k:1!null s:4 622 ├── cardinality: [0 - 10] 623 ├── key: (1) 624 ├── fd: (1)-->(4) 625 └── offset 626 ├── columns: k:1!null i:2 s:4 627 ├── internal-ordering: +2,+1 628 ├── cardinality: [0 - 10] 629 ├── key: (1) 630 ├── fd: (1)-->(2,4) 631 ├── limit 632 │ ├── columns: k:1!null i:2 s:4 633 │ ├── internal-ordering: +2,+1 634 │ ├── cardinality: [0 - 20] 635 │ ├── key: (1) 636 │ ├── fd: (1)-->(2,4) 637 │ ├── ordering: +2,+1 638 │ ├── sort 639 │ │ ├── columns: k:1!null i:2 s:4 640 │ │ ├── key: (1) 641 │ │ ├── fd: (1)-->(2,4) 642 │ │ ├── ordering: +2,+1 643 │ │ ├── limit hint: 20.00 644 │ │ └── scan a 645 │ │ ├── columns: k:1!null i:2 s:4 646 │ │ ├── key: (1) 647 │ │ └── fd: (1)-->(2,4) 648 │ └── 20 649 └── 10 650 651 # Project filter offset/limit columns, but can't push all the way down to scan. 652 norm 653 SELECT f, f*2.0 AS r FROM (SELECT f, s FROM a GROUP BY f, s OFFSET 5 LIMIT 5) a 654 ---- 655 project 656 ├── columns: f:3 r:5 657 ├── cardinality: [0 - 5] 658 ├── offset 659 │ ├── columns: f:3 s:4 660 │ ├── cardinality: [0 - 5] 661 │ ├── key: (3,4) 662 │ ├── limit 663 │ │ ├── columns: f:3 s:4 664 │ │ ├── cardinality: [0 - 10] 665 │ │ ├── key: (3,4) 666 │ │ ├── distinct-on 667 │ │ │ ├── columns: f:3 s:4 668 │ │ │ ├── grouping columns: f:3 s:4 669 │ │ │ ├── key: (3,4) 670 │ │ │ ├── limit hint: 10.00 671 │ │ │ └── scan a 672 │ │ │ ├── columns: f:3 s:4 673 │ │ │ └── limit hint: 12.07 674 │ │ └── 10 675 │ └── 5 676 └── projections 677 └── f:3 * 2.0 [as=r:5, outer=(3)] 678 679 # -------------------------------------------------- 680 # PruneJoinLeftCols 681 # -------------------------------------------------- 682 683 # Columns used only by projection or on condition, but not both. 684 norm expect=PruneJoinLeftCols 685 SELECT a.i, xy.* FROM a INNER JOIN xy ON a.k=xy.x 686 ---- 687 project 688 ├── columns: i:2 x:5!null y:6 689 ├── key: (5) 690 ├── fd: (5)-->(2,6) 691 └── inner-join (hash) 692 ├── columns: k:1!null i:2 x:5!null y:6 693 ├── key: (5) 694 ├── fd: (1)-->(2), (5)-->(6), (1)==(5), (5)==(1) 695 ├── scan a 696 │ ├── columns: k:1!null i:2 697 │ ├── key: (1) 698 │ └── fd: (1)-->(2) 699 ├── scan xy 700 │ ├── columns: x:5!null y:6 701 │ ├── key: (5) 702 │ └── fd: (5)-->(6) 703 └── filters 704 └── k:1 = x:5 [outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)] 705 706 # Columns used by both projection and on condition, left join. 707 norm expect=PruneJoinLeftCols 708 SELECT a.k, a.i, xy.* FROM a LEFT JOIN xy ON a.k=xy.x AND a.i<5 709 ---- 710 left-join (hash) 711 ├── columns: k:1!null i:2 x:5 y:6 712 ├── key: (1) 713 ├── fd: (1)-->(2,5,6), (5)-->(6) 714 ├── scan a 715 │ ├── columns: k:1!null i:2 716 │ ├── key: (1) 717 │ └── fd: (1)-->(2) 718 ├── scan xy 719 │ ├── columns: x:5!null y:6 720 │ ├── key: (5) 721 │ └── fd: (5)-->(6) 722 └── filters 723 ├── k:1 = x:5 [outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)] 724 └── i:2 < 5 [outer=(2), constraints=(/2: (/NULL - /4]; tight)] 725 726 # Columns needed only by projection, full join. 727 norm expect=PruneJoinLeftCols 728 SELECT a.k+1 AS r, xy.* FROM a FULL JOIN xy ON True 729 ---- 730 project 731 ├── columns: r:7 x:5 y:6 732 ├── fd: (5)-->(6) 733 ├── full-join (cross) 734 │ ├── columns: k:1 x:5 y:6 735 │ ├── key: (1,5) 736 │ ├── fd: (5)-->(6) 737 │ ├── scan a 738 │ │ ├── columns: k:1!null 739 │ │ └── key: (1) 740 │ ├── scan xy 741 │ │ ├── columns: x:5!null y:6 742 │ │ ├── key: (5) 743 │ │ └── fd: (5)-->(6) 744 │ └── filters (true) 745 └── projections 746 └── k:1 + 1 [as=r:7, outer=(1)] 747 748 # No columns needed from left side of join. 749 norm expect=PruneJoinLeftCols 750 SELECT xy.* FROM a, xy 751 ---- 752 inner-join (cross) 753 ├── columns: x:5!null y:6 754 ├── fd: (5)-->(6) 755 ├── scan a 756 ├── scan xy 757 │ ├── columns: x:5!null y:6 758 │ ├── key: (5) 759 │ └── fd: (5)-->(6) 760 └── filters (true) 761 762 # Computed columns. 763 norm expect=PruneJoinLeftCols 764 SELECT a.k+1 AS r, a.i/2 AS s, xy.* FROM a INNER JOIN xy ON a.k*a.k=xy.x AND a.s||'o'='foo' 765 ---- 766 project 767 ├── columns: r:8!null s:9 x:5!null y:6 768 ├── fd: (5)-->(6) 769 ├── inner-join (hash) 770 │ ├── columns: k:1!null i:2 x:5!null y:6 column7:7!null 771 │ ├── key: (1) 772 │ ├── fd: (1)-->(2,7), (5)-->(6), (5)==(7), (7)==(5) 773 │ ├── project 774 │ │ ├── columns: column7:7!null k:1!null i:2 775 │ │ ├── key: (1) 776 │ │ ├── fd: (1)-->(2,7) 777 │ │ ├── select 778 │ │ │ ├── columns: k:1!null i:2 a.s:4 779 │ │ │ ├── key: (1) 780 │ │ │ ├── fd: (1)-->(2,4) 781 │ │ │ ├── scan a 782 │ │ │ │ ├── columns: k:1!null i:2 a.s:4 783 │ │ │ │ ├── key: (1) 784 │ │ │ │ └── fd: (1)-->(2,4) 785 │ │ │ └── filters 786 │ │ │ └── (a.s:4 || 'o') = 'foo' [outer=(4)] 787 │ │ └── projections 788 │ │ └── k:1 * k:1 [as=column7:7, outer=(1)] 789 │ ├── scan xy 790 │ │ ├── columns: x:5!null y:6 791 │ │ ├── key: (5) 792 │ │ └── fd: (5)-->(6) 793 │ └── filters 794 │ └── column7:7 = x:5 [outer=(5,7), constraints=(/5: (/NULL - ]; /7: (/NULL - ]), fd=(5)==(7), (7)==(5)] 795 └── projections 796 ├── k:1 + 1 [as=r:8, outer=(1)] 797 └── i:2 / 2 [as=s:9, outer=(2)] 798 799 # Join that is nested in another join. 800 norm expect=PruneJoinLeftCols 801 SELECT a.k, xy.* 802 FROM 803 ( 804 SELECT * FROM a INNER JOIN xy ON a.k=xy.x 805 ) a 806 INNER JOIN xy 807 ON a.i < xy.y 808 ---- 809 project 810 ├── columns: k:1!null x:7!null y:8!null 811 ├── key: (1,7) 812 ├── fd: (7)-->(8) 813 └── inner-join (cross) 814 ├── columns: k:1!null i:2!null x:5!null x:7!null y:8!null 815 ├── key: (5,7) 816 ├── fd: (1)-->(2), (1)==(5), (5)==(1), (7)-->(8) 817 ├── inner-join (hash) 818 │ ├── columns: k:1!null i:2 x:5!null 819 │ ├── key: (5) 820 │ ├── fd: (1)-->(2), (1)==(5), (5)==(1) 821 │ ├── scan a 822 │ │ ├── columns: k:1!null i:2 823 │ │ ├── key: (1) 824 │ │ └── fd: (1)-->(2) 825 │ ├── scan xy 826 │ │ ├── columns: x:5!null 827 │ │ └── key: (5) 828 │ └── filters 829 │ └── k:1 = x:5 [outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)] 830 ├── scan xy 831 │ ├── columns: x:7!null y:8 832 │ ├── key: (7) 833 │ └── fd: (7)-->(8) 834 └── filters 835 └── i:2 < y:8 [outer=(2,8), constraints=(/2: (/NULL - ]; /8: (/NULL - ])] 836 837 # ApplyJoin operator. 838 norm expect=PruneJoinLeftCols 839 SELECT k, i 840 FROM a 841 WHERE (SELECT k+1 AS r FROM xy WHERE y=k) = 1 842 ---- 843 project 844 ├── columns: k:1!null i:2 845 ├── key: (1) 846 ├── fd: (1)-->(2) 847 └── select 848 ├── columns: k:1!null i:2 r:7!null 849 ├── key: (1) 850 ├── fd: ()-->(7), (1)-->(2) 851 ├── ensure-distinct-on 852 │ ├── columns: k:1!null i:2 r:7 853 │ ├── grouping columns: k:1!null 854 │ ├── error: "more than one row returned by a subquery used as an expression" 855 │ ├── key: (1) 856 │ ├── fd: (1)-->(2,7) 857 │ ├── left-join-apply 858 │ │ ├── columns: k:1!null i:2 y:6 r:7 859 │ │ ├── fd: (1)-->(2) 860 │ │ ├── scan a 861 │ │ │ ├── columns: k:1!null i:2 862 │ │ │ ├── key: (1) 863 │ │ │ └── fd: (1)-->(2) 864 │ │ ├── project 865 │ │ │ ├── columns: r:7 y:6 866 │ │ │ ├── outer: (1) 867 │ │ │ ├── fd: ()-->(7) 868 │ │ │ ├── scan xy 869 │ │ │ │ └── columns: y:6 870 │ │ │ └── projections 871 │ │ │ └── k:1 + 1 [as=r:7, outer=(1)] 872 │ │ └── filters 873 │ │ └── y:6 = k:1 [outer=(1,6), constraints=(/1: (/NULL - ]; /6: (/NULL - ]), fd=(1)==(6), (6)==(1)] 874 │ └── aggregations 875 │ ├── const-agg [as=i:2, outer=(2)] 876 │ │ └── i:2 877 │ └── const-agg [as=r:7, outer=(7)] 878 │ └── r:7 879 └── filters 880 └── r:7 = 1 [outer=(7), constraints=(/7: [/1 - /1]; tight), fd=()-->(7)] 881 882 # SemiJoin operator. 883 norm expect=PruneJoinLeftCols 884 SELECT a.i 885 FROM a 886 WHERE 887 EXISTS(SELECT * FROM xy WHERE a.k=xy.x) AND 888 EXISTS(SELECT * FROM xy WHERE a.k=xy.x) 889 ---- 890 project 891 ├── columns: i:2 892 └── semi-join (hash) 893 ├── columns: k:1!null i:2 894 ├── key: (1) 895 ├── fd: (1)-->(2) 896 ├── semi-join (hash) 897 │ ├── columns: k:1!null i:2 898 │ ├── key: (1) 899 │ ├── fd: (1)-->(2) 900 │ ├── scan a 901 │ │ ├── columns: k:1!null i:2 902 │ │ ├── key: (1) 903 │ │ └── fd: (1)-->(2) 904 │ ├── scan xy 905 │ │ ├── columns: x:7!null 906 │ │ └── key: (7) 907 │ └── filters 908 │ └── k:1 = x:7 [outer=(1,7), constraints=(/1: (/NULL - ]; /7: (/NULL - ]), fd=(1)==(7), (7)==(1)] 909 ├── scan xy 910 │ ├── columns: x:5!null 911 │ └── key: (5) 912 └── filters 913 └── k:1 = x:5 [outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)] 914 915 # AntiJoin operator. 916 norm expect=PruneJoinLeftCols 917 SELECT a.i 918 FROM a 919 WHERE 920 NOT EXISTS(SELECT * FROM xy WHERE a.k=xy.x) AND 921 NOT EXISTS(SELECT * FROM xy WHERE a.k=xy.x) 922 ---- 923 project 924 ├── columns: i:2 925 └── anti-join (hash) 926 ├── columns: k:1!null i:2 927 ├── key: (1) 928 ├── fd: (1)-->(2) 929 ├── anti-join (hash) 930 │ ├── columns: k:1!null i:2 931 │ ├── key: (1) 932 │ ├── fd: (1)-->(2) 933 │ ├── scan a 934 │ │ ├── columns: k:1!null i:2 935 │ │ ├── key: (1) 936 │ │ └── fd: (1)-->(2) 937 │ ├── scan xy 938 │ │ ├── columns: x:7!null 939 │ │ └── key: (7) 940 │ └── filters 941 │ └── k:1 = x:7 [outer=(1,7), constraints=(/1: (/NULL - ]; /7: (/NULL - ]), fd=(1)==(7), (7)==(1)] 942 ├── scan xy 943 │ ├── columns: x:5!null 944 │ └── key: (5) 945 └── filters 946 └── k:1 = x:5 [outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)] 947 948 # -------------------------------------------------- 949 # PruneJoinRightCols 950 # -------------------------------------------------- 951 952 # Columns used only by projection or on condition, but not both. 953 norm expect=PruneJoinRightCols 954 SELECT xy.*, a.i FROM xy INNER JOIN a ON xy.x=a.k 955 ---- 956 project 957 ├── columns: x:1!null y:2 i:4 958 ├── key: (1) 959 ├── fd: (1)-->(2,4) 960 └── inner-join (hash) 961 ├── columns: x:1!null y:2 k:3!null i:4 962 ├── key: (3) 963 ├── fd: (1)-->(2), (3)-->(4), (1)==(3), (3)==(1) 964 ├── scan xy 965 │ ├── columns: x:1!null y:2 966 │ ├── key: (1) 967 │ └── fd: (1)-->(2) 968 ├── scan a 969 │ ├── columns: k:3!null i:4 970 │ ├── key: (3) 971 │ └── fd: (3)-->(4) 972 └── filters 973 └── x:1 = k:3 [outer=(1,3), constraints=(/1: (/NULL - ]; /3: (/NULL - ]), fd=(1)==(3), (3)==(1)] 974 975 # Columns used by both projection and on condition, left join. 976 norm expect=PruneJoinRightCols 977 SELECT xy.*, a.k, a.i FROM xy LEFT JOIN a ON xy.x=a.k AND a.i<xy.x 978 ---- 979 left-join (hash) 980 ├── columns: x:1!null y:2 k:3 i:4 981 ├── key: (1) 982 ├── fd: (1)-->(2-4), (3)-->(4) 983 ├── scan xy 984 │ ├── columns: x:1!null y:2 985 │ ├── key: (1) 986 │ └── fd: (1)-->(2) 987 ├── select 988 │ ├── columns: k:3!null i:4!null 989 │ ├── key: (3) 990 │ ├── fd: (3)-->(4) 991 │ ├── scan a 992 │ │ ├── columns: k:3!null i:4 993 │ │ ├── key: (3) 994 │ │ └── fd: (3)-->(4) 995 │ └── filters 996 │ └── i:4 < k:3 [outer=(3,4), constraints=(/3: (/NULL - ]; /4: (/NULL - ])] 997 └── filters 998 └── x:1 = k:3 [outer=(1,3), constraints=(/1: (/NULL - ]; /3: (/NULL - ]), fd=(1)==(3), (3)==(1)] 999 1000 # Columns needed only by projection, full join. 1001 norm expect=PruneJoinRightCols 1002 SELECT xy.*, a.k+1 AS r FROM xy FULL JOIN a ON True 1003 ---- 1004 project 1005 ├── columns: x:1 y:2 r:7 1006 ├── fd: (1)-->(2) 1007 ├── full-join (cross) 1008 │ ├── columns: x:1 y:2 k:3 1009 │ ├── key: (1,3) 1010 │ ├── fd: (1)-->(2) 1011 │ ├── scan xy 1012 │ │ ├── columns: x:1!null y:2 1013 │ │ ├── key: (1) 1014 │ │ └── fd: (1)-->(2) 1015 │ ├── scan a 1016 │ │ ├── columns: k:3!null 1017 │ │ └── key: (3) 1018 │ └── filters (true) 1019 └── projections 1020 └── k:3 + 1 [as=r:7, outer=(3)] 1021 1022 # No columns needed from right side of join. 1023 norm expect=PruneJoinRightCols 1024 SELECT xy.* FROM xy, a 1025 ---- 1026 inner-join (cross) 1027 ├── columns: x:1!null y:2 1028 ├── fd: (1)-->(2) 1029 ├── scan xy 1030 │ ├── columns: x:1!null y:2 1031 │ ├── key: (1) 1032 │ └── fd: (1)-->(2) 1033 ├── scan a 1034 └── filters (true) 1035 1036 # Computed columns. 1037 norm expect=PruneJoinRightCols 1038 SELECT xy.*, a.k+1 AS r, a.i/2 AS s FROM xy INNER JOIN a ON xy.x=a.k*a.k AND a.s||'o'='foo' 1039 ---- 1040 project 1041 ├── columns: x:1!null y:2 r:8!null s:9 1042 ├── fd: (1)-->(2) 1043 ├── inner-join (hash) 1044 │ ├── columns: x:1!null y:2 k:3!null i:4 column7:7!null 1045 │ ├── key: (3) 1046 │ ├── fd: (1)-->(2), (3)-->(4,7), (1)==(7), (7)==(1) 1047 │ ├── scan xy 1048 │ │ ├── columns: x:1!null y:2 1049 │ │ ├── key: (1) 1050 │ │ └── fd: (1)-->(2) 1051 │ ├── project 1052 │ │ ├── columns: column7:7!null k:3!null i:4 1053 │ │ ├── key: (3) 1054 │ │ ├── fd: (3)-->(4,7) 1055 │ │ ├── select 1056 │ │ │ ├── columns: k:3!null i:4 a.s:6 1057 │ │ │ ├── key: (3) 1058 │ │ │ ├── fd: (3)-->(4,6) 1059 │ │ │ ├── scan a 1060 │ │ │ │ ├── columns: k:3!null i:4 a.s:6 1061 │ │ │ │ ├── key: (3) 1062 │ │ │ │ └── fd: (3)-->(4,6) 1063 │ │ │ └── filters 1064 │ │ │ └── (a.s:6 || 'o') = 'foo' [outer=(6)] 1065 │ │ └── projections 1066 │ │ └── k:3 * k:3 [as=column7:7, outer=(3)] 1067 │ └── filters 1068 │ └── x:1 = column7:7 [outer=(1,7), constraints=(/1: (/NULL - ]; /7: (/NULL - ]), fd=(1)==(7), (7)==(1)] 1069 └── projections 1070 ├── k:3 + 1 [as=r:8, outer=(3)] 1071 └── i:4 / 2 [as=s:9, outer=(4)] 1072 1073 # Join that is nested in another join. 1074 norm expect=PruneJoinRightCols 1075 SELECT a.k, xy.* 1076 FROM xy 1077 INNER JOIN 1078 ( 1079 SELECT * FROM a INNER JOIN xy ON a.k=xy.x 1080 ) a 1081 ON a.y < xy.y 1082 ---- 1083 project 1084 ├── columns: k:3!null x:1!null y:2!null 1085 ├── key: (1,3) 1086 ├── fd: (1)-->(2) 1087 └── inner-join (cross) 1088 ├── columns: x:1!null y:2!null k:3!null x:7!null y:8!null 1089 ├── key: (1,7) 1090 ├── fd: (1)-->(2), (7)-->(8), (3)==(7), (7)==(3) 1091 ├── scan xy 1092 │ ├── columns: x:1!null y:2 1093 │ ├── key: (1) 1094 │ └── fd: (1)-->(2) 1095 ├── inner-join (hash) 1096 │ ├── columns: k:3!null x:7!null y:8 1097 │ ├── key: (7) 1098 │ ├── fd: (7)-->(8), (3)==(7), (7)==(3) 1099 │ ├── scan a 1100 │ │ ├── columns: k:3!null 1101 │ │ └── key: (3) 1102 │ ├── scan xy 1103 │ │ ├── columns: x:7!null y:8 1104 │ │ ├── key: (7) 1105 │ │ └── fd: (7)-->(8) 1106 │ └── filters 1107 │ └── k:3 = x:7 [outer=(3,7), constraints=(/3: (/NULL - ]; /7: (/NULL - ]), fd=(3)==(7), (7)==(3)] 1108 └── filters 1109 └── y:8 < y:2 [outer=(2,8), constraints=(/2: (/NULL - ]; /8: (/NULL - ])] 1110 1111 # -------------------------------------------------- 1112 # PruneJoinLeftCols + PruneJoinRightCols 1113 # -------------------------------------------------- 1114 1115 # Columns not needed by either side of join. 1116 norm expect=(PruneJoinLeftCols,PruneJoinRightCols) 1117 SELECT 1 r FROM a,xy 1118 ---- 1119 project 1120 ├── columns: r:7!null 1121 ├── fd: ()-->(7) 1122 ├── inner-join (cross) 1123 │ ├── scan a 1124 │ ├── scan xy 1125 │ └── filters (true) 1126 └── projections 1127 └── 1 [as=r:7] 1128 1129 # Subset of columns needed by each side of join. 1130 norm expect=(PruneJoinLeftCols,PruneJoinRightCols) 1131 SELECT a.k, xy.x, a.k+xy.x AS r FROM a LEFT JOIN xy ON a.k=xy.x 1132 ---- 1133 project 1134 ├── columns: k:1!null x:5 r:7 1135 ├── key: (1) 1136 ├── fd: (1)-->(5), (1,5)-->(7) 1137 ├── left-join (hash) 1138 │ ├── columns: k:1!null x:5 1139 │ ├── key: (1) 1140 │ ├── fd: (1)-->(5) 1141 │ ├── scan a 1142 │ │ ├── columns: k:1!null 1143 │ │ └── key: (1) 1144 │ ├── scan xy 1145 │ │ ├── columns: x:5!null 1146 │ │ └── key: (5) 1147 │ └── filters 1148 │ └── k:1 = x:5 [outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)] 1149 └── projections 1150 └── k:1 + x:5 [as=r:7, outer=(1,5)] 1151 1152 # -------------------------------------------------- 1153 # PruneAggCols 1154 # -------------------------------------------------- 1155 1156 # Discard all aggregates. 1157 norm expect=PruneAggCols 1158 SELECT s FROM (SELECT s, sum(i), min(s||'foo') FROM a GROUP BY s) a 1159 ---- 1160 distinct-on 1161 ├── columns: s:4 1162 ├── grouping columns: s:4 1163 ├── key: (4) 1164 └── scan a 1165 └── columns: s:4 1166 1167 # Discard subset of aggregates. 1168 norm expect=PruneAggCols 1169 SELECT s, sumi FROM (SELECT sum(i) sumi, s, min(s||'foo') FROM a GROUP BY s) a 1170 ---- 1171 group-by 1172 ├── columns: s:4 sumi:5 1173 ├── grouping columns: s:4 1174 ├── key: (4) 1175 ├── fd: (4)-->(5) 1176 ├── scan a 1177 │ └── columns: i:2 s:4 1178 └── aggregations 1179 └── sum [as=sum:5, outer=(2)] 1180 └── i:2 1181 1182 # No aggregates to discard. 1183 norm expect-not=PruneAggCols 1184 SELECT 1 r FROM (SELECT s FROM a GROUP BY s) a 1185 ---- 1186 project 1187 ├── columns: r:5!null 1188 ├── fd: ()-->(5) 1189 ├── distinct-on 1190 │ ├── columns: s:4 1191 │ ├── grouping columns: s:4 1192 │ ├── key: (4) 1193 │ └── scan a 1194 │ └── columns: s:4 1195 └── projections 1196 └── 1 [as=r:5] 1197 1198 # Scalar GroupBy case. 1199 norm expect=PruneAggCols 1200 SELECT sumi FROM (SELECT sum(i) sumi, min(s||'foo') FROM a) a 1201 ---- 1202 scalar-group-by 1203 ├── columns: sumi:5 1204 ├── cardinality: [1 - 1] 1205 ├── key: () 1206 ├── fd: ()-->(5) 1207 ├── scan a 1208 │ └── columns: i:2 1209 └── aggregations 1210 └── sum [as=sum:5, outer=(2)] 1211 └── i:2 1212 1213 # DistinctOn case. 1214 norm expect=PruneAggCols 1215 SELECT f FROM (SELECT DISTINCT ON (i) f, s FROM a) 1216 ---- 1217 project 1218 ├── columns: f:3 1219 └── distinct-on 1220 ├── columns: i:2 f:3 1221 ├── grouping columns: i:2 1222 ├── key: (2) 1223 ├── fd: (2)-->(3) 1224 ├── scan a 1225 │ └── columns: i:2 f:3 1226 └── aggregations 1227 └── first-agg [as=f:3, outer=(3)] 1228 └── f:3 1229 1230 # EnsureDistinctOn case. 1231 norm expect=PruneAggCols 1232 SELECT max((SELECT y FROM xy WHERE y=i)) FROM a 1233 ---- 1234 scalar-group-by 1235 ├── columns: max:8 1236 ├── cardinality: [1 - 1] 1237 ├── key: () 1238 ├── fd: ()-->(8) 1239 ├── project 1240 │ ├── columns: column7:7 1241 │ ├── ensure-distinct-on 1242 │ │ ├── columns: k:1!null y:6 1243 │ │ ├── grouping columns: k:1!null 1244 │ │ ├── error: "more than one row returned by a subquery used as an expression" 1245 │ │ ├── key: (1) 1246 │ │ ├── fd: (1)-->(6) 1247 │ │ ├── left-join (hash) 1248 │ │ │ ├── columns: k:1!null i:2 y:6 1249 │ │ │ ├── fd: (1)-->(2) 1250 │ │ │ ├── scan a 1251 │ │ │ │ ├── columns: k:1!null i:2 1252 │ │ │ │ ├── key: (1) 1253 │ │ │ │ └── fd: (1)-->(2) 1254 │ │ │ ├── scan xy 1255 │ │ │ │ └── columns: y:6 1256 │ │ │ └── filters 1257 │ │ │ └── y:6 = i:2 [outer=(2,6), constraints=(/2: (/NULL - ]; /6: (/NULL - ]), fd=(2)==(6), (6)==(2)] 1258 │ │ └── aggregations 1259 │ │ └── const-agg [as=y:6, outer=(6)] 1260 │ │ └── y:6 1261 │ └── projections 1262 │ └── y:6 [as=column7:7, outer=(6)] 1263 └── aggregations 1264 └── max [as=max:8, outer=(7)] 1265 └── column7:7 1266 1267 # Columns used only by aggregation, no grouping columns. 1268 norm expect=PruneAggCols 1269 SELECT min(i), max(k), max(k) FROM a ORDER BY max(f) 1270 ---- 1271 scalar-group-by 1272 ├── columns: min:5 max:6 max:6 1273 ├── cardinality: [1 - 1] 1274 ├── key: () 1275 ├── fd: ()-->(5,6) 1276 ├── scan a 1277 │ ├── columns: k:1!null i:2 1278 │ ├── key: (1) 1279 │ └── fd: (1)-->(2) 1280 └── aggregations 1281 ├── min [as=min:5, outer=(2)] 1282 │ └── i:2 1283 └── max [as=max:6, outer=(1)] 1284 └── k:1 1285 1286 # -------------------------------------------------- 1287 # PruneGroupByCols 1288 # -------------------------------------------------- 1289 1290 # Columns used by grouping or aggregation, but not both should not be pruned. 1291 norm expect=PruneGroupByCols 1292 SELECT s, sum(i) FROM a GROUP BY s, s||'foo' 1293 ---- 1294 group-by 1295 ├── columns: s:4 sum:5 1296 ├── grouping columns: s:4 1297 ├── key: (4) 1298 ├── fd: (4)-->(5) 1299 ├── scan a 1300 │ └── columns: i:2 s:4 1301 └── aggregations 1302 └── sum [as=sum:5, outer=(2)] 1303 └── i:2 1304 1305 # Columns used by both grouping and aggregation should not be pruned. 1306 norm expect=PruneGroupByCols 1307 SELECT avg(s::int+i), s, i FROM a GROUP BY s, i, i+1 1308 ---- 1309 group-by 1310 ├── columns: avg:6 s:4 i:2 1311 ├── grouping columns: i:2 s:4 1312 ├── key: (2,4) 1313 ├── fd: (2,4)-->(6) 1314 ├── project 1315 │ ├── columns: column5:5 i:2 s:4 1316 │ ├── fd: (2,4)-->(5) 1317 │ ├── scan a 1318 │ │ └── columns: i:2 s:4 1319 │ └── projections 1320 │ └── i:2 + s:4::INT8 [as=column5:5, outer=(2,4)] 1321 └── aggregations 1322 └── avg [as=avg:6, outer=(5)] 1323 └── column5:5 1324 1325 # Columns used only by groupings, no aggregation columns. 1326 norm expect=PruneGroupByCols 1327 SELECT s, i+1 AS r FROM a GROUP BY i, s, s||'foo' 1328 ---- 1329 project 1330 ├── columns: s:4 r:6 1331 ├── distinct-on 1332 │ ├── columns: i:2 s:4 1333 │ ├── grouping columns: i:2 s:4 1334 │ ├── key: (2,4) 1335 │ └── scan a 1336 │ └── columns: i:2 s:4 1337 └── projections 1338 └── i:2 + 1 [as=r:6, outer=(2)] 1339 1340 # Groupby a groupby. 1341 norm expect=PruneGroupByCols 1342 SELECT min(sm), i FROM (SELECT s, i, sum(k) sm, avg(k) av FROM a GROUP BY i, s) a GROUP BY i, i+1 1343 ---- 1344 group-by 1345 ├── columns: min:7!null i:2 1346 ├── grouping columns: i:2 1347 ├── key: (2) 1348 ├── fd: (2)-->(7) 1349 ├── group-by 1350 │ ├── columns: i:2 s:4 sum:5!null 1351 │ ├── grouping columns: i:2 s:4 1352 │ ├── key: (2,4) 1353 │ ├── fd: (2,4)-->(5) 1354 │ ├── scan a 1355 │ │ ├── columns: k:1!null i:2 s:4 1356 │ │ ├── key: (1) 1357 │ │ └── fd: (1)-->(2,4) 1358 │ └── aggregations 1359 │ └── sum [as=sum:5, outer=(1)] 1360 │ └── k:1 1361 └── aggregations 1362 └── min [as=min:7, outer=(5)] 1363 └── sum:5 1364 1365 # Distinct (GroupBy operator with no aggregates). 1366 norm expect=PruneGroupByCols 1367 SELECT DISTINCT ON (s, s||'foo') s, f FROM a 1368 ---- 1369 distinct-on 1370 ├── columns: s:4 f:3 1371 ├── grouping columns: s:4 1372 ├── key: (4) 1373 ├── fd: (4)-->(3) 1374 ├── scan a 1375 │ └── columns: f:3 s:4 1376 └── aggregations 1377 └── first-agg [as=f:3, outer=(3)] 1378 └── f:3 1379 1380 # ScalarGroupBy case. 1381 norm expect=PruneGroupByCols 1382 SELECT icnt FROM (SELECT count(i+1) AS icnt, count(k+1) FROM a); 1383 ---- 1384 scalar-group-by 1385 ├── columns: icnt:6!null 1386 ├── cardinality: [1 - 1] 1387 ├── key: () 1388 ├── fd: ()-->(6) 1389 ├── project 1390 │ ├── columns: column5:5 1391 │ ├── scan a 1392 │ │ └── columns: i:2 1393 │ └── projections 1394 │ └── i:2 + 1 [as=column5:5, outer=(2)] 1395 └── aggregations 1396 └── count [as=count:6, outer=(5)] 1397 └── column5:5 1398 1399 # -------------------------------------------------- 1400 # PruneValuesCols 1401 # -------------------------------------------------- 1402 1403 # Discard all but first Values column. 1404 norm expect=PruneValuesCols 1405 SELECT column1 FROM (VALUES (1, 2), (3, 4)) a 1406 ---- 1407 values 1408 ├── columns: column1:1!null 1409 ├── cardinality: [2 - 2] 1410 ├── (1,) 1411 └── (3,) 1412 1413 # Discard all but middle Values column. 1414 norm expect=PruneValuesCols 1415 SELECT column2 FROM (VALUES (1, 2, 3), (4, 5, 6)) a 1416 ---- 1417 values 1418 ├── columns: column2:2!null 1419 ├── cardinality: [2 - 2] 1420 ├── (2,) 1421 └── (5,) 1422 1423 # Discard all but last Values column. 1424 norm expect=PruneValuesCols 1425 SELECT column3 FROM (VALUES ('foo', 'bar', 'baz'), ('apple', 'banana', 'cherry')) a 1426 ---- 1427 values 1428 ├── columns: column3:3!null 1429 ├── cardinality: [2 - 2] 1430 ├── ('baz',) 1431 └── ('cherry',) 1432 1433 # Discard all Values columns. 1434 norm expect=PruneValuesCols 1435 SELECT 1 r FROM (VALUES ('foo', 'bar', 'baz'), ('apple', 'banana', 'cherry')) a 1436 ---- 1437 project 1438 ├── columns: r:4!null 1439 ├── cardinality: [2 - 2] 1440 ├── fd: ()-->(4) 1441 ├── values 1442 │ ├── cardinality: [2 - 2] 1443 │ ├── () 1444 │ └── () 1445 └── projections 1446 └── 1 [as=r:4] 1447 1448 # -------------------------------------------------- 1449 # Prune - multiple combined operators 1450 # -------------------------------------------------- 1451 1452 norm 1453 SELECT a.k, xy.y FROM a INNER JOIN xy ON a.k=xy.x WHERE a.i < 5 1454 ---- 1455 project 1456 ├── columns: k:1!null y:6 1457 ├── key: (1) 1458 ├── fd: (1)-->(6) 1459 └── inner-join (hash) 1460 ├── columns: k:1!null i:2!null x:5!null y:6 1461 ├── key: (5) 1462 ├── fd: (1)-->(2), (5)-->(6), (1)==(5), (5)==(1) 1463 ├── select 1464 │ ├── columns: k:1!null i:2!null 1465 │ ├── key: (1) 1466 │ ├── fd: (1)-->(2) 1467 │ ├── scan a 1468 │ │ ├── columns: k:1!null i:2 1469 │ │ ├── key: (1) 1470 │ │ └── fd: (1)-->(2) 1471 │ └── filters 1472 │ └── i:2 < 5 [outer=(2), constraints=(/2: (/NULL - /4]; tight)] 1473 ├── scan xy 1474 │ ├── columns: x:5!null y:6 1475 │ ├── key: (5) 1476 │ └── fd: (5)-->(6) 1477 └── filters 1478 └── k:1 = x:5 [outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)] 1479 1480 norm 1481 SELECT k FROM (SELECT k, min(s) FROM a GROUP BY k HAVING sum(i) > 5) 1482 ---- 1483 project 1484 ├── columns: k:1!null 1485 ├── key: (1) 1486 └── select 1487 ├── columns: k:1!null sum:6!null 1488 ├── key: (1) 1489 ├── fd: (1)-->(6) 1490 ├── group-by 1491 │ ├── columns: k:1!null sum:6 1492 │ ├── grouping columns: k:1!null 1493 │ ├── key: (1) 1494 │ ├── fd: (1)-->(6) 1495 │ ├── scan a 1496 │ │ ├── columns: k:1!null i:2 1497 │ │ ├── key: (1) 1498 │ │ └── fd: (1)-->(2) 1499 │ └── aggregations 1500 │ └── sum [as=sum:6, outer=(2)] 1501 │ └── i:2 1502 └── filters 1503 └── sum:6 > 5 [outer=(6), constraints=(/6: (/5 - ]; tight)] 1504 1505 # -------------------------------------------------- 1506 # PruneOrdinalityCols 1507 # -------------------------------------------------- 1508 norm expect=PruneOrdinalityCols 1509 SELECT i, s FROM a WITH ORDINALITY 1510 ---- 1511 project 1512 ├── columns: i:2 s:4 1513 └── ordinality 1514 ├── columns: i:2 s:4 ordinality:5!null 1515 ├── key: (5) 1516 ├── fd: (5)-->(2,4) 1517 └── scan a 1518 └── columns: i:2 s:4 1519 1520 # With order by. 1521 norm expect=PruneOrdinalityCols 1522 SELECT i, s FROM (SELECT * FROM a ORDER BY f) WITH ORDINALITY 1523 ---- 1524 project 1525 ├── columns: i:2 s:4 1526 └── ordinality 1527 ├── columns: i:2 f:3 s:4 ordinality:5!null 1528 ├── key: (5) 1529 ├── fd: (5)-->(2-4) 1530 └── sort 1531 ├── columns: i:2 f:3 s:4 1532 ├── ordering: +3 1533 └── scan a 1534 └── columns: i:2 f:3 s:4 1535 1536 # -------------------------------------------------- 1537 # PruneExplainCols 1538 # -------------------------------------------------- 1539 norm expect=PruneExplainCols 1540 EXPLAIN SELECT a FROM abcde WHERE b=1 AND c IS NOT NULL ORDER BY c, d 1541 ---- 1542 explain 1543 ├── columns: tree:6 field:7 description:8 1544 └── sort 1545 ├── columns: a:1!null [hidden: c:3!null] 1546 ├── key: (1) 1547 ├── fd: (1)-->(3), (3)-->(1) 1548 ├── ordering: +3 1549 └── project 1550 ├── columns: a:1!null c:3!null 1551 ├── key: (1) 1552 ├── fd: (1)-->(3), (3)-->(1) 1553 └── select 1554 ├── columns: a:1!null b:2!null c:3!null 1555 ├── key: (1) 1556 ├── fd: ()-->(2), (1)-->(3), (3)-->(1) 1557 ├── scan abcde 1558 │ ├── columns: a:1!null b:2 c:3 1559 │ ├── key: (1) 1560 │ └── fd: (1)-->(2,3), (2,3)~~>(1) 1561 └── filters 1562 ├── b:2 = 1 [outer=(2), constraints=(/2: [/1 - /1]; tight), fd=()-->(2)] 1563 └── c:3 IS NOT NULL [outer=(3), constraints=(/3: (/NULL - ]; tight)] 1564 1565 # -------------------------------------------------- 1566 # PruneProjectSetCols 1567 # -------------------------------------------------- 1568 norm expect=PruneProjectSetCols 1569 SELECT a, b, generate_series(c, 10) FROM abcde 1570 ---- 1571 project 1572 ├── columns: a:1!null b:2 generate_series:6 1573 ├── immutable, side-effects 1574 ├── fd: (1)-->(2) 1575 └── project-set 1576 ├── columns: a:1!null b:2 c:3 generate_series:6 1577 ├── immutable, side-effects 1578 ├── fd: (1)-->(2,3), (2,3)~~>(1) 1579 ├── scan abcde 1580 │ ├── columns: a:1!null b:2 c:3 1581 │ ├── key: (1) 1582 │ └── fd: (1)-->(2,3), (2,3)~~>(1) 1583 └── zip 1584 └── generate_series(c:3, 10) [outer=(3), immutable, side-effects] 1585 1586 norm expect=PruneProjectSetCols 1587 SELECT k FROM a WHERE EXISTS(SELECT * FROM ROWS FROM (generate_series(i, k), length(s))) 1588 ---- 1589 distinct-on 1590 ├── columns: k:1!null 1591 ├── grouping columns: k:1!null 1592 ├── immutable, side-effects 1593 ├── key: (1) 1594 └── project-set 1595 ├── columns: k:1!null i:2 s:4 generate_series:5 length:6 1596 ├── immutable, side-effects 1597 ├── fd: (1)-->(2,4) 1598 ├── scan a 1599 │ ├── columns: k:1!null i:2 s:4 1600 │ ├── key: (1) 1601 │ └── fd: (1)-->(2,4) 1602 └── zip 1603 ├── generate_series(i:2, k:1) [outer=(1,2), immutable, side-effects] 1604 └── length(s:4) [outer=(4), immutable] 1605 1606 # -------------------------------------------------- 1607 # PruneWindowInputCols 1608 # -------------------------------------------------- 1609 1610 norm expect=PruneWindowInputCols 1611 SELECT rank() OVER () FROM a 1612 ---- 1613 window partition=() 1614 ├── columns: rank:5 1615 ├── scan a 1616 └── windows 1617 └── rank [as=rank:5] 1618 1619 norm expect=PruneWindowInputCols 1620 SELECT ntile(1) OVER () FROM a 1621 ---- 1622 project 1623 ├── columns: ntile:5 1624 └── window partition=() 1625 ├── columns: ntile:5 ntile_1_arg1:6!null 1626 ├── fd: ()-->(6) 1627 ├── project 1628 │ ├── columns: ntile_1_arg1:6!null 1629 │ ├── fd: ()-->(6) 1630 │ ├── scan a 1631 │ └── projections 1632 │ └── 1 [as=ntile_1_arg1:6] 1633 └── windows 1634 └── ntile [as=ntile:5, outer=(6)] 1635 └── ntile_1_arg1:6 1636 1637 norm expect=PruneWindowInputCols format=show-all 1638 SELECT ntile(i) OVER () FROM a 1639 ---- 1640 project 1641 ├── columns: ntile:5(int) 1642 ├── stats: [rows=1000] 1643 ├── cost: 1060.04 1644 ├── prune: (5) 1645 └── window partition=() 1646 ├── columns: t.public.a.i:2(int) ntile:5(int) 1647 ├── stats: [rows=1000] 1648 ├── cost: 1050.03 1649 ├── prune: (5) 1650 ├── scan t.public.a 1651 │ ├── columns: t.public.a.i:2(int) 1652 │ ├── stats: [rows=1000] 1653 │ ├── cost: 1050.02 1654 │ └── prune: (2) 1655 └── windows 1656 └── ntile [as=ntile:5, type=int, outer=(2)] 1657 └── variable: t.public.a.i:2 [type=int] 1658 1659 # Ensure filter cols don't get pruned. 1660 norm 1661 SELECT 1662 avg(i) FILTER (WHERE true) OVER (), 1663 avg(i) FILTER (WHERE false) OVER () 1664 FROM a 1665 ---- 1666 project 1667 ├── columns: avg:5 avg:6 1668 └── window partition=() 1669 ├── columns: i:2 avg:5 avg:6 avg_1_filter:7!null avg_2_filter:8!null 1670 ├── fd: ()-->(7,8) 1671 ├── project 1672 │ ├── columns: avg_1_filter:7!null avg_2_filter:8!null i:2 1673 │ ├── fd: ()-->(7,8) 1674 │ ├── scan a 1675 │ │ └── columns: i:2 1676 │ └── projections 1677 │ ├── true [as=avg_1_filter:7] 1678 │ └── false [as=avg_2_filter:8] 1679 └── windows 1680 ├── agg-filter [as=avg:5, outer=(2,7)] 1681 │ ├── avg 1682 │ │ └── i:2 1683 │ └── avg_1_filter:7 1684 └── agg-filter [as=avg:6, outer=(2,8)] 1685 ├── avg 1686 │ └── i:2 1687 └── avg_2_filter:8 1688 1689 # -------------------------------------------------- 1690 # PruneWindowOutputCols 1691 # -------------------------------------------------- 1692 1693 norm expect=PruneWindowOutputCols 1694 SELECT x FROM (SELECT ntile(1) OVER () AS x, ntile(2) OVER () FROM a) 1695 ---- 1696 project 1697 ├── columns: x:5 1698 └── window partition=() 1699 ├── columns: ntile:5 ntile_1_arg1:7!null 1700 ├── fd: ()-->(7) 1701 ├── project 1702 │ ├── columns: ntile_1_arg1:7!null 1703 │ ├── fd: ()-->(7) 1704 │ ├── scan a 1705 │ └── projections 1706 │ └── 1 [as=ntile_1_arg1:7] 1707 └── windows 1708 └── ntile [as=ntile:5, outer=(7)] 1709 └── ntile_1_arg1:7 1710 1711 norm expect=(PruneWindowOutputCols,EliminateWindow) 1712 SELECT 1 FROM (SELECT ntile(1) OVER () FROM a) 1713 ---- 1714 project 1715 ├── columns: "?column?":7!null 1716 ├── fd: ()-->(7) 1717 ├── scan a 1718 └── projections 1719 └── 1 [as="?column?":7] 1720 1721 norm expect=(PruneWindowOutputCols,EliminateWindow) 1722 SELECT 1 FROM (SELECT x FROM (SELECT ntile(1) OVER () AS x, ntile(2) OVER () FROM a)) 1723 ---- 1724 project 1725 ├── columns: "?column?":9!null 1726 ├── fd: ()-->(9) 1727 ├── scan a 1728 └── projections 1729 └── 1 [as="?column?":9] 1730 1731 norm expect-not=PruneWindowOutputCols 1732 SELECT round(avg(k) OVER (PARTITION BY f ORDER BY s)) FROM a ORDER BY 1 1733 ---- 1734 sort 1735 ├── columns: round:6 1736 ├── immutable 1737 ├── ordering: +6 1738 └── project 1739 ├── columns: round:6 1740 ├── immutable 1741 ├── window partition=(3) ordering=+4 opt(3) 1742 │ ├── columns: k:1!null f:3 s:4 avg:5 1743 │ ├── key: (1) 1744 │ ├── fd: (1)-->(3,4) 1745 │ ├── scan a 1746 │ │ ├── columns: k:1!null f:3 s:4 1747 │ │ ├── key: (1) 1748 │ │ └── fd: (1)-->(3,4) 1749 │ └── windows 1750 │ └── avg [as=avg:5, outer=(1)] 1751 │ └── k:1 1752 └── projections 1753 └── round(avg:5) [as=round:6, outer=(5), immutable] 1754 1755 norm expect=(PruneWindowInputCols,PruneWindowOutputCols) format=show-all 1756 SELECT x FROM (SELECT ntile(i) OVER () x, ntile(f::int) OVER () y FROM a) 1757 ---- 1758 project 1759 ├── columns: x:5(int) 1760 ├── stats: [rows=1000] 1761 ├── cost: 1060.04 1762 ├── prune: (5) 1763 └── window partition=() 1764 ├── columns: t.public.a.i:2(int) ntile:5(int) 1765 ├── stats: [rows=1000] 1766 ├── cost: 1050.03 1767 ├── prune: (5) 1768 ├── scan t.public.a 1769 │ ├── columns: t.public.a.i:2(int) 1770 │ ├── stats: [rows=1000] 1771 │ ├── cost: 1050.02 1772 │ └── prune: (2) 1773 └── windows 1774 └── ntile [as=ntile:5, type=int, outer=(2)] 1775 └── variable: t.public.a.i:2 [type=int] 1776 1777 1778 # -------------------------------------------------- 1779 # PruneMutationFetchCols + PruneMutationInputCols 1780 # -------------------------------------------------- 1781 1782 # Prune all but the key column. 1783 norm expect=(PruneMutationFetchCols,PruneMutationInputCols) 1784 DELETE FROM a 1785 ---- 1786 delete a 1787 ├── columns: <none> 1788 ├── fetch columns: k:5 1789 ├── cardinality: [0 - 0] 1790 ├── volatile, side-effects, mutations 1791 └── scan a 1792 ├── columns: k:5!null 1793 └── key: (5) 1794 1795 # Prune when computed ordering column is present. 1796 norm expect=(PruneMutationFetchCols,PruneMutationInputCols) 1797 DELETE FROM a WHERE i > 0 ORDER BY i*2 LIMIT 10 1798 ---- 1799 delete a 1800 ├── columns: <none> 1801 ├── fetch columns: k:5 1802 ├── cardinality: [0 - 0] 1803 ├── volatile, side-effects, mutations 1804 └── limit 1805 ├── columns: k:5!null column9:9!null 1806 ├── internal-ordering: +9 1807 ├── cardinality: [0 - 10] 1808 ├── key: (5) 1809 ├── fd: (5)-->(9) 1810 ├── sort 1811 │ ├── columns: k:5!null column9:9!null 1812 │ ├── key: (5) 1813 │ ├── fd: (5)-->(9) 1814 │ ├── ordering: +9 1815 │ ├── limit hint: 10.00 1816 │ └── project 1817 │ ├── columns: column9:9!null k:5!null 1818 │ ├── key: (5) 1819 │ ├── fd: (5)-->(9) 1820 │ ├── select 1821 │ │ ├── columns: k:5!null i:6!null 1822 │ │ ├── key: (5) 1823 │ │ ├── fd: (5)-->(6) 1824 │ │ ├── scan a 1825 │ │ │ ├── columns: k:5!null i:6 1826 │ │ │ ├── key: (5) 1827 │ │ │ └── fd: (5)-->(6) 1828 │ │ └── filters 1829 │ │ └── i:6 > 0 [outer=(6), constraints=(/6: [/1 - ]; tight)] 1830 │ └── projections 1831 │ └── i:6 * 2 [as=column9:9, outer=(6)] 1832 └── 10 1833 1834 # Prune when a secondary index is present on the table. 1835 norm expect=(PruneMutationFetchCols,PruneMutationInputCols) 1836 DELETE FROM abcde WHERE a > 0 1837 ---- 1838 delete abcde 1839 ├── columns: <none> 1840 ├── fetch columns: a:6 b:7 c:8 1841 ├── cardinality: [0 - 0] 1842 ├── volatile, side-effects, mutations 1843 └── select 1844 ├── columns: a:6!null b:7 c:8 1845 ├── key: (6) 1846 ├── fd: (6)-->(7,8), (7,8)~~>(6) 1847 ├── scan abcde 1848 │ ├── columns: a:6!null b:7 c:8 1849 │ ├── key: (6) 1850 │ └── fd: (6)-->(7,8), (7,8)~~>(6) 1851 └── filters 1852 └── a:6 > 0 [outer=(6), constraints=(/6: [/1 - ]; tight)] 1853 1854 # Prune when mutation columns/indexes exist. 1855 norm expect=(PruneMutationFetchCols,PruneMutationInputCols) 1856 DELETE FROM mutation 1857 ---- 1858 delete mutation 1859 ├── columns: <none> 1860 ├── fetch columns: a:6 b:7 d:9 e:10 1861 ├── cardinality: [0 - 0] 1862 ├── volatile, side-effects, mutations 1863 └── scan mutation 1864 ├── columns: a:6!null b:7 d:9 e:10 1865 ├── key: (6) 1866 └── fd: (6)-->(7,9,10) 1867 1868 norm expect=(PruneMutationFetchCols,PruneMutationInputCols) 1869 DELETE FROM a RETURNING k, s 1870 ---- 1871 delete a 1872 ├── columns: k:1!null s:4 1873 ├── fetch columns: k:5 s:8 1874 ├── volatile, side-effects, mutations 1875 ├── key: (1) 1876 ├── fd: (1)-->(4) 1877 └── scan a 1878 ├── columns: k:5!null s:8 1879 ├── key: (5) 1880 └── fd: (5)-->(8) 1881 1882 # Prune secondary family column not needed for the update. 1883 norm expect=(PruneMutationFetchCols,PruneMutationInputCols) 1884 UPDATE family SET b=c WHERE a > 100 1885 ---- 1886 update "family" 1887 ├── columns: <none> 1888 ├── fetch columns: a:6 b:7 1889 ├── update-mapping: 1890 │ └── c:8 => b:2 1891 ├── cardinality: [0 - 0] 1892 ├── volatile, side-effects, mutations 1893 └── select 1894 ├── columns: a:6!null b:7 c:8 1895 ├── key: (6) 1896 ├── fd: (6)-->(7,8) 1897 ├── scan "family" 1898 │ ├── columns: a:6!null b:7 c:8 1899 │ ├── key: (6) 1900 │ └── fd: (6)-->(7,8) 1901 └── filters 1902 └── a:6 > 100 [outer=(6), constraints=(/6: [/101 - ]; tight)] 1903 1904 # Do not prune when key column is updated. 1905 norm expect-not=(PruneMutationFetchCols,PruneMutationInputCols) 1906 UPDATE family SET a=a+1 WHERE a > 100 1907 ---- 1908 update "family" 1909 ├── columns: <none> 1910 ├── fetch columns: a:6 b:7 c:8 d:9 e:10 1911 ├── update-mapping: 1912 │ └── a_new:11 => a:1 1913 ├── cardinality: [0 - 0] 1914 ├── volatile, side-effects, mutations 1915 └── project 1916 ├── columns: a_new:11!null a:6!null b:7 c:8 d:9 e:10 1917 ├── key: (6) 1918 ├── fd: (6)-->(7-11) 1919 ├── select 1920 │ ├── columns: a:6!null b:7 c:8 d:9 e:10 1921 │ ├── key: (6) 1922 │ ├── fd: (6)-->(7-10) 1923 │ ├── scan "family" 1924 │ │ ├── columns: a:6!null b:7 c:8 d:9 e:10 1925 │ │ ├── key: (6) 1926 │ │ └── fd: (6)-->(7-10) 1927 │ └── filters 1928 │ └── a:6 > 100 [outer=(6), constraints=(/6: [/101 - ]; tight)] 1929 └── projections 1930 └── a:6 + 1 [as=a_new:11, outer=(6)] 1931 1932 # Do not prune columns that must be returned. 1933 norm expect=(PruneMutationFetchCols, PruneMutationReturnCols) 1934 UPDATE family SET c=c+1 RETURNING b 1935 ---- 1936 project 1937 ├── columns: b:2 1938 ├── volatile, side-effects, mutations 1939 └── update "family" 1940 ├── columns: a:1!null b:2 1941 ├── fetch columns: a:6 b:7 c:8 d:9 1942 ├── update-mapping: 1943 │ └── c_new:11 => c:3 1944 ├── volatile, side-effects, mutations 1945 ├── key: (1) 1946 ├── fd: (1)-->(2) 1947 └── project 1948 ├── columns: c_new:11 a:6!null b:7 c:8 d:9 1949 ├── key: (6) 1950 ├── fd: (6)-->(7-9), (8)-->(11) 1951 ├── scan "family" 1952 │ ├── columns: a:6!null b:7 c:8 d:9 1953 │ ├── key: (6) 1954 │ └── fd: (6)-->(7-9) 1955 └── projections 1956 └── c:8 + 1 [as=c_new:11, outer=(8)] 1957 1958 # Prune unused upsert columns. 1959 norm expect=PruneMutationInputCols 1960 INSERT INTO a (k, s) VALUES (1, 'foo') ON CONFLICT (k) DO UPDATE SET i=a.i+1 1961 ---- 1962 upsert a 1963 ├── columns: <none> 1964 ├── canary column: 9 1965 ├── fetch columns: k:9 i:10 f:11 s:12 1966 ├── insert-mapping: 1967 │ ├── column1:5 => k:1 1968 │ ├── column7:7 => i:2 1969 │ ├── column8:8 => f:3 1970 │ └── column2:6 => s:4 1971 ├── update-mapping: 1972 │ └── upsert_i:15 => i:2 1973 ├── cardinality: [0 - 0] 1974 ├── volatile, side-effects, mutations 1975 └── project 1976 ├── columns: upsert_i:15 column1:5!null column2:6!null column7:7 column8:8 k:9 i:10 f:11 s:12 1977 ├── cardinality: [1 - 1] 1978 ├── key: () 1979 ├── fd: ()-->(5-12,15) 1980 ├── left-join (cross) 1981 │ ├── columns: column1:5!null column2:6!null column7:7 column8:8 k:9 i:10 f:11 s:12 1982 │ ├── cardinality: [1 - 1] 1983 │ ├── key: () 1984 │ ├── fd: ()-->(5-12) 1985 │ ├── values 1986 │ │ ├── columns: column1:5!null column2:6!null column7:7 column8:8 1987 │ │ ├── cardinality: [1 - 1] 1988 │ │ ├── key: () 1989 │ │ ├── fd: ()-->(5-8) 1990 │ │ └── (1, 'foo', NULL, NULL) 1991 │ ├── select 1992 │ │ ├── columns: k:9!null i:10 f:11 s:12 1993 │ │ ├── cardinality: [0 - 1] 1994 │ │ ├── key: () 1995 │ │ ├── fd: ()-->(9-12) 1996 │ │ ├── scan a 1997 │ │ │ ├── columns: k:9!null i:10 f:11 s:12 1998 │ │ │ ├── key: (9) 1999 │ │ │ └── fd: (9)-->(10-12) 2000 │ │ └── filters 2001 │ │ └── k:9 = 1 [outer=(9), constraints=(/9: [/1 - /1]; tight), fd=()-->(9)] 2002 │ └── filters (true) 2003 └── projections 2004 └── CASE WHEN k:9 IS NULL THEN column7:7 ELSE i:10 + 1 END [as=upsert_i:15, outer=(7,9,10)] 2005 2006 # Prune update columns replaced by upsert columns. 2007 # TODO(andyk): Need to also prune output columns. 2008 norm expect=PruneMutationInputCols expect-not=PruneMutationFetchCols 2009 INSERT INTO a (k, s) VALUES (1, 'foo') ON CONFLICT (k) DO UPDATE SET i=a.i+1 RETURNING * 2010 ---- 2011 upsert a 2012 ├── columns: k:1!null i:2 f:3 s:4 2013 ├── canary column: 9 2014 ├── fetch columns: k:9 i:10 f:11 s:12 2015 ├── insert-mapping: 2016 │ ├── column1:5 => k:1 2017 │ ├── column7:7 => i:2 2018 │ ├── column8:8 => f:3 2019 │ └── column2:6 => s:4 2020 ├── update-mapping: 2021 │ └── upsert_i:15 => i:2 2022 ├── return-mapping: 2023 │ ├── upsert_k:14 => k:1 2024 │ ├── upsert_i:15 => i:2 2025 │ ├── upsert_f:16 => f:3 2026 │ └── upsert_s:17 => s:4 2027 ├── cardinality: [1 - 1] 2028 ├── volatile, side-effects, mutations 2029 ├── key: () 2030 ├── fd: ()-->(1-4) 2031 └── project 2032 ├── columns: upsert_k:14 upsert_i:15 upsert_f:16 upsert_s:17 column1:5!null column2:6!null column7:7 column8:8 k:9 i:10 f:11 s:12 2033 ├── cardinality: [1 - 1] 2034 ├── key: () 2035 ├── fd: ()-->(5-12,14-17) 2036 ├── left-join (cross) 2037 │ ├── columns: column1:5!null column2:6!null column7:7 column8:8 k:9 i:10 f:11 s:12 2038 │ ├── cardinality: [1 - 1] 2039 │ ├── key: () 2040 │ ├── fd: ()-->(5-12) 2041 │ ├── values 2042 │ │ ├── columns: column1:5!null column2:6!null column7:7 column8:8 2043 │ │ ├── cardinality: [1 - 1] 2044 │ │ ├── key: () 2045 │ │ ├── fd: ()-->(5-8) 2046 │ │ └── (1, 'foo', NULL, NULL) 2047 │ ├── select 2048 │ │ ├── columns: k:9!null i:10 f:11 s:12 2049 │ │ ├── cardinality: [0 - 1] 2050 │ │ ├── key: () 2051 │ │ ├── fd: ()-->(9-12) 2052 │ │ ├── scan a 2053 │ │ │ ├── columns: k:9!null i:10 f:11 s:12 2054 │ │ │ ├── key: (9) 2055 │ │ │ └── fd: (9)-->(10-12) 2056 │ │ └── filters 2057 │ │ └── k:9 = 1 [outer=(9), constraints=(/9: [/1 - /1]; tight), fd=()-->(9)] 2058 │ └── filters (true) 2059 └── projections 2060 ├── CASE WHEN k:9 IS NULL THEN column1:5 ELSE k:9 END [as=upsert_k:14, outer=(5,9)] 2061 ├── CASE WHEN k:9 IS NULL THEN column7:7 ELSE i:10 + 1 END [as=upsert_i:15, outer=(7,9,10)] 2062 ├── CASE WHEN k:9 IS NULL THEN column8:8 ELSE f:11 END [as=upsert_f:16, outer=(8,9,11)] 2063 └── CASE WHEN k:9 IS NULL THEN column2:6 ELSE s:12 END [as=upsert_s:17, outer=(6,9,12)] 2064 2065 # Prune column in column family that is not updated. 2066 norm expect=(PruneMutationFetchCols,PruneMutationInputCols) 2067 UPSERT INTO family (a, b) VALUES (1, 2) 2068 ---- 2069 upsert "family" 2070 ├── columns: <none> 2071 ├── canary column: 9 2072 ├── fetch columns: a:9 b:10 2073 ├── insert-mapping: 2074 │ ├── column1:6 => a:1 2075 │ ├── column2:7 => b:2 2076 │ ├── column8:8 => c:3 2077 │ ├── column8:8 => d:4 2078 │ └── column8:8 => e:5 2079 ├── update-mapping: 2080 │ └── column2:7 => b:2 2081 ├── cardinality: [0 - 0] 2082 ├── volatile, side-effects, mutations 2083 └── left-join (cross) 2084 ├── columns: column1:6!null column2:7!null column8:8 a:9 b:10 2085 ├── cardinality: [1 - 1] 2086 ├── key: () 2087 ├── fd: ()-->(6-10) 2088 ├── values 2089 │ ├── columns: column1:6!null column2:7!null column8:8 2090 │ ├── cardinality: [1 - 1] 2091 │ ├── key: () 2092 │ ├── fd: ()-->(6-8) 2093 │ └── (1, 2, NULL) 2094 ├── select 2095 │ ├── columns: a:9!null b:10 2096 │ ├── cardinality: [0 - 1] 2097 │ ├── key: () 2098 │ ├── fd: ()-->(9,10) 2099 │ ├── scan "family" 2100 │ │ ├── columns: a:9!null b:10 2101 │ │ ├── key: (9) 2102 │ │ └── fd: (9)-->(10) 2103 │ └── filters 2104 │ └── a:9 = 1 [outer=(9), constraints=(/9: [/1 - /1]; tight), fd=()-->(9)] 2105 └── filters (true) 2106 2107 norm 2108 INSERT INTO family VALUES (1, 2, 3, 4, 5) ON CONFLICT (a) DO UPDATE SET c = 10 RETURNING e 2109 ---- 2110 project 2111 ├── columns: e:5 2112 ├── cardinality: [1 - 1] 2113 ├── volatile, side-effects, mutations 2114 ├── key: () 2115 ├── fd: ()-->(5) 2116 └── upsert "family" 2117 ├── columns: a:1!null e:5 2118 ├── canary column: 11 2119 ├── fetch columns: a:11 c:13 d:14 e:15 2120 ├── insert-mapping: 2121 │ ├── column1:6 => a:1 2122 │ ├── column2:7 => b:2 2123 │ ├── column3:8 => c:3 2124 │ ├── column4:9 => d:4 2125 │ └── column5:10 => e:5 2126 ├── update-mapping: 2127 │ └── upsert_c:19 => c:3 2128 ├── return-mapping: 2129 │ ├── upsert_a:17 => a:1 2130 │ └── upsert_e:21 => e:5 2131 ├── cardinality: [1 - 1] 2132 ├── volatile, side-effects, mutations 2133 ├── key: () 2134 ├── fd: ()-->(1,5) 2135 └── project 2136 ├── columns: upsert_a:17 upsert_c:19!null upsert_e:21 column1:6!null column2:7!null column3:8!null column4:9!null column5:10!null a:11 c:13 d:14 e:15 2137 ├── cardinality: [1 - 1] 2138 ├── key: () 2139 ├── fd: ()-->(6-11,13-15,17,19,21) 2140 ├── left-join (cross) 2141 │ ├── columns: column1:6!null column2:7!null column3:8!null column4:9!null column5:10!null a:11 c:13 d:14 e:15 2142 │ ├── cardinality: [1 - 1] 2143 │ ├── key: () 2144 │ ├── fd: ()-->(6-11,13-15) 2145 │ ├── values 2146 │ │ ├── columns: column1:6!null column2:7!null column3:8!null column4:9!null column5:10!null 2147 │ │ ├── cardinality: [1 - 1] 2148 │ │ ├── key: () 2149 │ │ ├── fd: ()-->(6-10) 2150 │ │ └── (1, 2, 3, 4, 5) 2151 │ ├── select 2152 │ │ ├── columns: a:11!null c:13 d:14 e:15 2153 │ │ ├── cardinality: [0 - 1] 2154 │ │ ├── key: () 2155 │ │ ├── fd: ()-->(11,13-15) 2156 │ │ ├── scan "family" 2157 │ │ │ ├── columns: a:11!null c:13 d:14 e:15 2158 │ │ │ ├── key: (11) 2159 │ │ │ └── fd: (11)-->(13-15) 2160 │ │ └── filters 2161 │ │ └── a:11 = 1 [outer=(11), constraints=(/11: [/1 - /1]; tight), fd=()-->(11)] 2162 │ └── filters (true) 2163 └── projections 2164 ├── CASE WHEN a:11 IS NULL THEN column1:6 ELSE a:11 END [as=upsert_a:17, outer=(6,11)] 2165 ├── CASE WHEN a:11 IS NULL THEN column3:8 ELSE 10 END [as=upsert_c:19, outer=(8,11)] 2166 └── CASE WHEN a:11 IS NULL THEN column5:10 ELSE e:15 END [as=upsert_e:21, outer=(10,11,15)] 2167 2168 # Do not prune column in same secondary family as updated column. But prune 2169 # non-key column in primary family. 2170 norm expect=(PruneMutationFetchCols,PruneMutationInputCols) 2171 INSERT INTO family VALUES (1, 2, 3, 4) ON CONFLICT (a) DO UPDATE SET d=10 2172 ---- 2173 upsert "family" 2174 ├── columns: <none> 2175 ├── canary column: 11 2176 ├── fetch columns: a:11 c:13 d:14 2177 ├── insert-mapping: 2178 │ ├── column1:6 => a:1 2179 │ ├── column2:7 => b:2 2180 │ ├── column3:8 => c:3 2181 │ ├── column4:9 => d:4 2182 │ └── column10:10 => e:5 2183 ├── update-mapping: 2184 │ └── upsert_d:20 => d:4 2185 ├── cardinality: [0 - 0] 2186 ├── volatile, side-effects, mutations 2187 └── project 2188 ├── columns: upsert_d:20!null column1:6!null column2:7!null column3:8!null column4:9!null column10:10 a:11 c:13 d:14 2189 ├── cardinality: [1 - 1] 2190 ├── key: () 2191 ├── fd: ()-->(6-11,13,14,20) 2192 ├── left-join (cross) 2193 │ ├── columns: column1:6!null column2:7!null column3:8!null column4:9!null column10:10 a:11 c:13 d:14 2194 │ ├── cardinality: [1 - 1] 2195 │ ├── key: () 2196 │ ├── fd: ()-->(6-11,13,14) 2197 │ ├── values 2198 │ │ ├── columns: column1:6!null column2:7!null column3:8!null column4:9!null column10:10 2199 │ │ ├── cardinality: [1 - 1] 2200 │ │ ├── key: () 2201 │ │ ├── fd: ()-->(6-10) 2202 │ │ └── (1, 2, 3, 4, NULL) 2203 │ ├── select 2204 │ │ ├── columns: a:11!null c:13 d:14 2205 │ │ ├── cardinality: [0 - 1] 2206 │ │ ├── key: () 2207 │ │ ├── fd: ()-->(11,13,14) 2208 │ │ ├── scan "family" 2209 │ │ │ ├── columns: a:11!null c:13 d:14 2210 │ │ │ ├── key: (11) 2211 │ │ │ └── fd: (11)-->(13,14) 2212 │ │ └── filters 2213 │ │ └── a:11 = 1 [outer=(11), constraints=(/11: [/1 - /1]; tight), fd=()-->(11)] 2214 │ └── filters (true) 2215 └── projections 2216 └── CASE WHEN a:11 IS NULL THEN column4:9 ELSE 10 END [as=upsert_d:20, outer=(9,11)] 2217 2218 # Prune upsert columns when mutation columns/indexes exist. 2219 norm expect=(PruneMutationInputCols) 2220 INSERT INTO mutation VALUES (1, 2, 3) ON CONFLICT (a) DO UPDATE SET b=10 2221 ---- 2222 upsert mutation 2223 ├── columns: <none> 2224 ├── canary column: 10 2225 ├── fetch columns: a:10 b:11 c:12 d:13 e:14 2226 ├── insert-mapping: 2227 │ ├── column1:6 => a:1 2228 │ ├── column2:7 => b:2 2229 │ ├── column3:8 => c:3 2230 │ └── column9:9 => d:4 2231 ├── update-mapping: 2232 │ ├── upsert_b:17 => b:2 2233 │ └── column9:9 => d:4 2234 ├── cardinality: [0 - 0] 2235 ├── volatile, side-effects, mutations 2236 └── project 2237 ├── columns: upsert_b:17!null column1:6!null column2:7!null column3:8!null column9:9 a:10 b:11 c:12 d:13 e:14 2238 ├── cardinality: [1 - 1] 2239 ├── key: () 2240 ├── fd: ()-->(6-14,17) 2241 ├── left-join (cross) 2242 │ ├── columns: column1:6!null column2:7!null column3:8!null column9:9 a:10 b:11 c:12 d:13 e:14 2243 │ ├── cardinality: [1 - 1] 2244 │ ├── key: () 2245 │ ├── fd: ()-->(6-14) 2246 │ ├── values 2247 │ │ ├── columns: column1:6!null column2:7!null column3:8!null column9:9 2248 │ │ ├── cardinality: [1 - 1] 2249 │ │ ├── key: () 2250 │ │ ├── fd: ()-->(6-9) 2251 │ │ └── (1, 2, 3, NULL) 2252 │ ├── select 2253 │ │ ├── columns: a:10!null b:11 c:12 d:13 e:14 2254 │ │ ├── cardinality: [0 - 1] 2255 │ │ ├── key: () 2256 │ │ ├── fd: ()-->(10-14) 2257 │ │ ├── scan mutation 2258 │ │ │ ├── columns: a:10!null b:11 c:12 d:13 e:14 2259 │ │ │ ├── key: (10) 2260 │ │ │ └── fd: (10)-->(11-14) 2261 │ │ └── filters 2262 │ │ └── a:10 = 1 [outer=(10), constraints=(/10: [/1 - /1]; tight), fd=()-->(10)] 2263 │ └── filters (true) 2264 └── projections 2265 └── CASE WHEN a:10 IS NULL THEN column2:7 ELSE 10 END [as=upsert_b:17, outer=(7,10)] 2266 2267 # ------------------------------------------------------------------------------ 2268 # PruneMutationReturnCols 2269 # ------------------------------------------------------------------------------ 2270 2271 # Create a table with multiple column families the mutations can take advantage of. 2272 exec-ddl 2273 CREATE TABLE returning_test ( 2274 a INT, 2275 b INT, 2276 c STRING, 2277 d INT, 2278 e INT, 2279 f INT, 2280 g INT, 2281 FAMILY (a), 2282 FAMILY (b), 2283 FAMILY (c), 2284 FAMILY (d, e, f, g), 2285 UNIQUE (a) 2286 ) 2287 ---- 2288 2289 # Fetch all the columns for the RETURN expression. 2290 norm 2291 UPDATE returning_test SET a = a + 1 RETURNING * 2292 ---- 2293 project 2294 ├── columns: a:1 b:2 c:3 d:4 e:5 f:6 g:7 2295 ├── volatile, side-effects, mutations 2296 └── update returning_test 2297 ├── columns: a:1 b:2 c:3 d:4 e:5 f:6 g:7 rowid:8!null 2298 ├── fetch columns: a:9 b:10 c:11 d:12 e:13 f:14 g:15 rowid:16 2299 ├── update-mapping: 2300 │ └── a_new:17 => a:1 2301 ├── volatile, side-effects, mutations 2302 ├── key: (8) 2303 ├── fd: (8)-->(1-7) 2304 └── project 2305 ├── columns: a_new:17 a:9 b:10 c:11 d:12 e:13 f:14 g:15 rowid:16!null 2306 ├── key: (16) 2307 ├── fd: (16)-->(9-15), (9)~~>(10-16), (9)-->(17) 2308 ├── scan returning_test 2309 │ ├── columns: a:9 b:10 c:11 d:12 e:13 f:14 g:15 rowid:16!null 2310 │ ├── key: (16) 2311 │ └── fd: (16)-->(9-15), (9)~~>(10-16) 2312 └── projections 2313 └── a:9 + 1 [as=a_new:17, outer=(9)] 2314 2315 2316 # Fetch all the columns in the (d, e, f, g) family as d is being set. 2317 norm 2318 UPDATE returning_test SET d = a + d RETURNING a, d 2319 ---- 2320 project 2321 ├── columns: a:1 d:4 2322 ├── volatile, side-effects, mutations 2323 ├── lax-key: (1,4) 2324 ├── fd: (1)~~>(4) 2325 └── update returning_test 2326 ├── columns: a:1 d:4 rowid:8!null 2327 ├── fetch columns: a:9 d:12 e:13 f:14 g:15 rowid:16 2328 ├── update-mapping: 2329 │ └── d_new:17 => d:4 2330 ├── volatile, side-effects, mutations 2331 ├── key: (8) 2332 ├── fd: (8)-->(1,4), (1)~~>(4,8) 2333 └── project 2334 ├── columns: d_new:17 a:9 d:12 e:13 f:14 g:15 rowid:16!null 2335 ├── key: (16) 2336 ├── fd: (16)-->(9,12-15), (9)~~>(12-16), (9,12)-->(17) 2337 ├── scan returning_test 2338 │ ├── columns: a:9 d:12 e:13 f:14 g:15 rowid:16!null 2339 │ ├── key: (16) 2340 │ └── fd: (16)-->(9,12-15), (9)~~>(12-16) 2341 └── projections 2342 └── a:9 + d:12 [as=d_new:17, outer=(9,12)] 2343 2344 # Fetch only whats being updated (not the (d, e, f, g) family). 2345 norm 2346 UPDATE returning_test SET a = a + d RETURNING a 2347 ---- 2348 project 2349 ├── columns: a:1 2350 ├── volatile, side-effects, mutations 2351 └── update returning_test 2352 ├── columns: a:1 rowid:8!null 2353 ├── fetch columns: a:9 rowid:16 2354 ├── update-mapping: 2355 │ └── a_new:17 => a:1 2356 ├── volatile, side-effects, mutations 2357 ├── key: (8) 2358 ├── fd: (8)-->(1) 2359 └── project 2360 ├── columns: a_new:17 a:9 rowid:16!null 2361 ├── key: (16) 2362 ├── fd: (16)-->(9,17), (9)~~>(16,17) 2363 ├── scan returning_test 2364 │ ├── columns: a:9 d:12 rowid:16!null 2365 │ ├── key: (16) 2366 │ └── fd: (16)-->(9,12), (9)~~>(12,16) 2367 └── projections 2368 └── a:9 + d:12 [as=a_new:17, outer=(9,12)] 2369 2370 # We only fetch the minimal set of columns which is (a, b, c, rowid). 2371 norm 2372 UPDATE returning_test SET (b, a) = (a, a + b) RETURNING a, b, c 2373 ---- 2374 project 2375 ├── columns: a:1 b:2 c:3 2376 ├── volatile, side-effects, mutations 2377 ├── lax-key: (1-3) 2378 ├── fd: (2)~~>(1,3) 2379 └── update returning_test 2380 ├── columns: a:1 b:2 c:3 rowid:8!null 2381 ├── fetch columns: a:9 b:10 c:11 rowid:16 2382 ├── update-mapping: 2383 │ ├── a_new:17 => a:1 2384 │ └── a:9 => b:2 2385 ├── volatile, side-effects, mutations 2386 ├── key: (8) 2387 ├── fd: (8)-->(1-3), (2)~~>(1,3,8) 2388 └── project 2389 ├── columns: a_new:17 a:9 b:10 c:11 rowid:16!null 2390 ├── key: (16) 2391 ├── fd: (16)-->(9-11), (9)~~>(10,11,16), (9,10)-->(17) 2392 ├── scan returning_test 2393 │ ├── columns: a:9 b:10 c:11 rowid:16!null 2394 │ ├── key: (16) 2395 │ └── fd: (16)-->(9-11), (9)~~>(10,11,16) 2396 └── projections 2397 └── a:9 + b:10 [as=a_new:17, outer=(9,10)] 2398 2399 2400 # We apply the PruneMutationReturnCols rule multiple times, to get 2401 # the minimal set of columns which is (a, rowid). Notice how c and b 2402 # are pruned away. 2403 norm 2404 SELECT a FROM [SELECT a, b FROM [UPDATE returning_test SET a = a + 1 RETURNING a, b, c]] 2405 ---- 2406 with &1 2407 ├── columns: a:21 2408 ├── volatile, side-effects, mutations 2409 ├── project 2410 │ ├── columns: returning_test.a:1 returning_test.b:2 returning_test.c:3 2411 │ ├── volatile, side-effects, mutations 2412 │ └── update returning_test 2413 │ ├── columns: returning_test.a:1 returning_test.b:2 returning_test.c:3 rowid:8!null 2414 │ ├── fetch columns: returning_test.a:9 returning_test.b:10 returning_test.c:11 rowid:16 2415 │ ├── update-mapping: 2416 │ │ └── a_new:17 => returning_test.a:1 2417 │ ├── volatile, side-effects, mutations 2418 │ ├── key: (8) 2419 │ ├── fd: (8)-->(1-3) 2420 │ └── project 2421 │ ├── columns: a_new:17 returning_test.a:9 returning_test.b:10 returning_test.c:11 rowid:16!null 2422 │ ├── key: (16) 2423 │ ├── fd: (16)-->(9-11), (9)~~>(10,11,16), (9)-->(17) 2424 │ ├── scan returning_test 2425 │ │ ├── columns: returning_test.a:9 returning_test.b:10 returning_test.c:11 rowid:16!null 2426 │ │ ├── key: (16) 2427 │ │ └── fd: (16)-->(9-11), (9)~~>(10,11,16) 2428 │ └── projections 2429 │ └── returning_test.a:9 + 1 [as=a_new:17, outer=(9)] 2430 └── project 2431 ├── columns: a:21 2432 ├── with-scan &1 2433 │ ├── columns: a:18 2434 │ └── mapping: 2435 │ └── returning_test.a:1 => a:18 2436 └── projections 2437 └── a:18 [as=a:21, outer=(18)] 2438 2439 # We derive the prune cols for the mutation appropriately so we 2440 # can prune away columns even when the mutation is not under a 2441 # projection. Another rule will fire to add the appropriate 2442 # projection when this happens. 2443 norm 2444 SELECT a FROM [SELECT a, b FROM [UPDATE returning_test SET a = a + 1 RETURNING a, b, c] WHERE a > 1] 2445 ---- 2446 with &1 2447 ├── columns: a:21!null 2448 ├── volatile, side-effects, mutations 2449 ├── project 2450 │ ├── columns: returning_test.a:1 returning_test.b:2 returning_test.c:3 2451 │ ├── volatile, side-effects, mutations 2452 │ └── update returning_test 2453 │ ├── columns: returning_test.a:1 returning_test.b:2 returning_test.c:3 rowid:8!null 2454 │ ├── fetch columns: returning_test.a:9 returning_test.b:10 returning_test.c:11 rowid:16 2455 │ ├── update-mapping: 2456 │ │ └── a_new:17 => returning_test.a:1 2457 │ ├── volatile, side-effects, mutations 2458 │ ├── key: (8) 2459 │ ├── fd: (8)-->(1-3) 2460 │ └── project 2461 │ ├── columns: a_new:17 returning_test.a:9 returning_test.b:10 returning_test.c:11 rowid:16!null 2462 │ ├── key: (16) 2463 │ ├── fd: (16)-->(9-11), (9)~~>(10,11,16), (9)-->(17) 2464 │ ├── scan returning_test 2465 │ │ ├── columns: returning_test.a:9 returning_test.b:10 returning_test.c:11 rowid:16!null 2466 │ │ ├── key: (16) 2467 │ │ └── fd: (16)-->(9-11), (9)~~>(10,11,16) 2468 │ └── projections 2469 │ └── returning_test.a:9 + 1 [as=a_new:17, outer=(9)] 2470 └── project 2471 ├── columns: a:21!null 2472 ├── select 2473 │ ├── columns: a:18!null 2474 │ ├── with-scan &1 2475 │ │ ├── columns: a:18 2476 │ │ └── mapping: 2477 │ │ └── returning_test.a:1 => a:18 2478 │ └── filters 2479 │ └── a:18 > 1 [outer=(18), constraints=(/18: [/2 - ]; tight)] 2480 └── projections 2481 └── a:18 [as=a:21, outer=(18)] 2482 2483 norm 2484 SELECT 2485 * 2486 FROM 2487 [SELECT a, b FROM returning_test] AS x 2488 JOIN [SELECT a, b FROM [UPDATE returning_test SET a = a + 1 RETURNING a, b, c] WHERE a > 1] 2489 AS y ON true 2490 ---- 2491 with &2 2492 ├── columns: a:9 b:10 a:31!null b:32 2493 ├── volatile, side-effects, mutations 2494 ├── fd: (9)~~>(10) 2495 ├── project 2496 │ ├── columns: returning_test.a:11 returning_test.b:12 returning_test.c:13 2497 │ ├── volatile, side-effects, mutations 2498 │ └── update returning_test 2499 │ ├── columns: returning_test.a:11 returning_test.b:12 returning_test.c:13 rowid:18!null 2500 │ ├── fetch columns: returning_test.a:19 returning_test.b:20 returning_test.c:21 rowid:26 2501 │ ├── update-mapping: 2502 │ │ └── a_new:27 => returning_test.a:11 2503 │ ├── volatile, side-effects, mutations 2504 │ ├── key: (18) 2505 │ ├── fd: (18)-->(11-13) 2506 │ └── project 2507 │ ├── columns: a_new:27 returning_test.a:19 returning_test.b:20 returning_test.c:21 rowid:26!null 2508 │ ├── key: (26) 2509 │ ├── fd: (26)-->(19-21), (19)~~>(20,21,26), (19)-->(27) 2510 │ ├── scan returning_test 2511 │ │ ├── columns: returning_test.a:19 returning_test.b:20 returning_test.c:21 rowid:26!null 2512 │ │ ├── key: (26) 2513 │ │ └── fd: (26)-->(19-21), (19)~~>(20,21,26) 2514 │ └── projections 2515 │ └── returning_test.a:19 + 1 [as=a_new:27, outer=(19)] 2516 └── inner-join (cross) 2517 ├── columns: a:9 b:10 a:31!null b:32 2518 ├── fd: (9)~~>(10) 2519 ├── project 2520 │ ├── columns: a:9 b:10 2521 │ ├── lax-key: (9,10) 2522 │ ├── fd: (9)~~>(10) 2523 │ ├── scan returning_test 2524 │ │ ├── columns: returning_test.a:1 returning_test.b:2 2525 │ │ ├── lax-key: (1,2) 2526 │ │ └── fd: (1)~~>(2) 2527 │ └── projections 2528 │ ├── returning_test.a:1 [as=a:9, outer=(1)] 2529 │ └── returning_test.b:2 [as=b:10, outer=(2)] 2530 ├── project 2531 │ ├── columns: a:31!null b:32 2532 │ ├── select 2533 │ │ ├── columns: a:28!null b:29 2534 │ │ ├── with-scan &2 2535 │ │ │ ├── columns: a:28 b:29 2536 │ │ │ └── mapping: 2537 │ │ │ ├── returning_test.a:11 => a:28 2538 │ │ │ └── returning_test.b:12 => b:29 2539 │ │ └── filters 2540 │ │ └── a:28 > 1 [outer=(28), constraints=(/28: [/2 - ]; tight)] 2541 │ └── projections 2542 │ ├── a:28 [as=a:31, outer=(28)] 2543 │ └── b:29 [as=b:32, outer=(29)] 2544 └── filters (true) 2545 2546 # Check if the rule works as desired for other mutations. 2547 norm 2548 INSERT INTO returning_test VALUES (1, 2, 'c') ON CONFLICT (a) DO UPDATE SET a = excluded.a + returning_test.a RETURNING a, b, c 2549 ---- 2550 project 2551 ├── columns: a:1 b:2 c:3 2552 ├── cardinality: [1 - 1] 2553 ├── volatile, side-effects, mutations 2554 ├── key: () 2555 ├── fd: ()-->(1-3) 2556 └── upsert returning_test 2557 ├── columns: a:1 b:2 c:3 rowid:8!null 2558 ├── canary column: 21 2559 ├── fetch columns: a:14 b:15 c:16 rowid:21 2560 ├── insert-mapping: 2561 │ ├── column1:9 => a:1 2562 │ ├── column2:10 => b:2 2563 │ ├── column3:11 => c:3 2564 │ ├── column12:12 => d:4 2565 │ ├── column12:12 => e:5 2566 │ ├── column12:12 => f:6 2567 │ ├── column12:12 => g:7 2568 │ └── column13:13 => rowid:8 2569 ├── update-mapping: 2570 │ └── upsert_a:23 => a:1 2571 ├── return-mapping: 2572 │ ├── upsert_a:23 => a:1 2573 │ ├── upsert_b:24 => b:2 2574 │ ├── upsert_c:25 => c:3 2575 │ └── upsert_rowid:30 => rowid:8 2576 ├── cardinality: [1 - 1] 2577 ├── volatile, side-effects, mutations 2578 ├── key: () 2579 ├── fd: ()-->(1-3,8) 2580 └── project 2581 ├── columns: upsert_a:23 upsert_b:24 upsert_c:25 upsert_rowid:30 column1:9!null column2:10!null column3:11!null column12:12 column13:13 a:14 b:15 c:16 rowid:21 2582 ├── cardinality: [1 - 1] 2583 ├── volatile, side-effects 2584 ├── key: () 2585 ├── fd: ()-->(9-16,21,23-25,30) 2586 ├── left-join (cross) 2587 │ ├── columns: column1:9!null column2:10!null column3:11!null column12:12 column13:13 a:14 b:15 c:16 rowid:21 2588 │ ├── cardinality: [1 - 1] 2589 │ ├── volatile, side-effects 2590 │ ├── key: () 2591 │ ├── fd: ()-->(9-16,21) 2592 │ ├── values 2593 │ │ ├── columns: column1:9!null column2:10!null column3:11!null column12:12 column13:13 2594 │ │ ├── cardinality: [1 - 1] 2595 │ │ ├── volatile, side-effects 2596 │ │ ├── key: () 2597 │ │ ├── fd: ()-->(9-13) 2598 │ │ └── (1, 2, 'c', CAST(NULL AS INT8), unique_rowid()) 2599 │ ├── select 2600 │ │ ├── columns: a:14!null b:15 c:16 rowid:21!null 2601 │ │ ├── cardinality: [0 - 1] 2602 │ │ ├── key: () 2603 │ │ ├── fd: ()-->(14-16,21) 2604 │ │ ├── scan returning_test 2605 │ │ │ ├── columns: a:14 b:15 c:16 rowid:21!null 2606 │ │ │ ├── key: (21) 2607 │ │ │ └── fd: (21)-->(14-16), (14)~~>(15,16,21) 2608 │ │ └── filters 2609 │ │ └── a:14 = 1 [outer=(14), constraints=(/14: [/1 - /1]; tight), fd=()-->(14)] 2610 │ └── filters (true) 2611 └── projections 2612 ├── CASE WHEN rowid:21 IS NULL THEN column1:9 ELSE column1:9 + a:14 END [as=upsert_a:23, outer=(9,14,21)] 2613 ├── CASE WHEN rowid:21 IS NULL THEN column2:10 ELSE b:15 END [as=upsert_b:24, outer=(10,15,21)] 2614 ├── CASE WHEN rowid:21 IS NULL THEN column3:11 ELSE c:16 END [as=upsert_c:25, outer=(11,16,21)] 2615 └── CASE WHEN rowid:21 IS NULL THEN column13:13 ELSE rowid:21 END [as=upsert_rowid:30, outer=(13,21)] 2616 2617 norm 2618 DELETE FROM returning_test WHERE a < b + d RETURNING a, b, d 2619 ---- 2620 project 2621 ├── columns: a:1!null b:2 d:4 2622 ├── volatile, side-effects, mutations 2623 ├── key: (1) 2624 ├── fd: (1)-->(2,4) 2625 └── delete returning_test 2626 ├── columns: a:1!null b:2 d:4 rowid:8!null 2627 ├── fetch columns: a:9 b:10 d:12 rowid:16 2628 ├── volatile, side-effects, mutations 2629 ├── key: (8) 2630 ├── fd: (8)-->(1,2,4), (1)-->(2,4,8) 2631 └── select 2632 ├── columns: a:9!null b:10 d:12 rowid:16!null 2633 ├── key: (16) 2634 ├── fd: (16)-->(9,10,12), (9)-->(10,12,16) 2635 ├── scan returning_test 2636 │ ├── columns: a:9 b:10 d:12 rowid:16!null 2637 │ ├── key: (16) 2638 │ └── fd: (16)-->(9,10,12), (9)~~>(10,12,16) 2639 └── filters 2640 └── a:9 < (b:10 + d:12) [outer=(9,10,12), constraints=(/9: (/NULL - ])] 2641 2642 norm 2643 UPSERT INTO returning_test (a, b, c) VALUES (1, 2, 'c') RETURNING a, b, c, d 2644 ---- 2645 project 2646 ├── columns: a:1!null b:2!null c:3!null d:4 2647 ├── cardinality: [1 - 1] 2648 ├── volatile, side-effects, mutations 2649 ├── key: () 2650 ├── fd: ()-->(1-4) 2651 └── upsert returning_test 2652 ├── columns: a:1!null b:2!null c:3!null d:4 rowid:8!null 2653 ├── canary column: 21 2654 ├── fetch columns: a:14 b:15 c:16 d:17 rowid:21 2655 ├── insert-mapping: 2656 │ ├── column1:9 => a:1 2657 │ ├── column2:10 => b:2 2658 │ ├── column3:11 => c:3 2659 │ ├── column12:12 => d:4 2660 │ ├── column12:12 => e:5 2661 │ ├── column12:12 => f:6 2662 │ ├── column12:12 => g:7 2663 │ └── column13:13 => rowid:8 2664 ├── update-mapping: 2665 │ ├── column1:9 => a:1 2666 │ ├── column2:10 => b:2 2667 │ └── column3:11 => c:3 2668 ├── return-mapping: 2669 │ ├── column1:9 => a:1 2670 │ ├── column2:10 => b:2 2671 │ ├── column3:11 => c:3 2672 │ ├── upsert_d:22 => d:4 2673 │ └── upsert_rowid:26 => rowid:8 2674 ├── cardinality: [1 - 1] 2675 ├── volatile, side-effects, mutations 2676 ├── key: () 2677 ├── fd: ()-->(1-4,8) 2678 └── project 2679 ├── columns: upsert_d:22 upsert_rowid:26 column1:9!null column2:10!null column3:11!null column12:12 column13:13 a:14 b:15 c:16 d:17 rowid:21 2680 ├── cardinality: [1 - 1] 2681 ├── volatile, side-effects 2682 ├── key: () 2683 ├── fd: ()-->(9-17,21,22,26) 2684 ├── left-join (hash) 2685 │ ├── columns: column1:9!null column2:10!null column3:11!null column12:12 column13:13 a:14 b:15 c:16 d:17 rowid:21 2686 │ ├── cardinality: [1 - 1] 2687 │ ├── volatile, side-effects 2688 │ ├── key: () 2689 │ ├── fd: ()-->(9-17,21) 2690 │ ├── ensure-upsert-distinct-on 2691 │ │ ├── columns: column1:9!null column2:10!null column3:11!null column12:12 column13:13 2692 │ │ ├── grouping columns: column13:13 2693 │ │ ├── error: "UPSERT or INSERT...ON CONFLICT command cannot affect row a second time" 2694 │ │ ├── cardinality: [1 - 1] 2695 │ │ ├── volatile, side-effects 2696 │ │ ├── key: () 2697 │ │ ├── fd: ()-->(9-13) 2698 │ │ ├── values 2699 │ │ │ ├── columns: column1:9!null column2:10!null column3:11!null column12:12 column13:13 2700 │ │ │ ├── cardinality: [1 - 1] 2701 │ │ │ ├── volatile, side-effects 2702 │ │ │ ├── key: () 2703 │ │ │ ├── fd: ()-->(9-13) 2704 │ │ │ └── (1, 2, 'c', CAST(NULL AS INT8), unique_rowid()) 2705 │ │ └── aggregations 2706 │ │ ├── first-agg [as=column1:9, outer=(9)] 2707 │ │ │ └── column1:9 2708 │ │ ├── first-agg [as=column2:10, outer=(10)] 2709 │ │ │ └── column2:10 2710 │ │ ├── first-agg [as=column3:11, outer=(11)] 2711 │ │ │ └── column3:11 2712 │ │ └── first-agg [as=column12:12, outer=(12)] 2713 │ │ └── column12:12 2714 │ ├── scan returning_test 2715 │ │ ├── columns: a:14 b:15 c:16 d:17 rowid:21!null 2716 │ │ ├── key: (21) 2717 │ │ └── fd: (21)-->(14-17), (14)~~>(15-17,21) 2718 │ └── filters 2719 │ └── column13:13 = rowid:21 [outer=(13,21), constraints=(/13: (/NULL - ]; /21: (/NULL - ]), fd=(13)==(21), (21)==(13)] 2720 └── projections 2721 ├── CASE WHEN rowid:21 IS NULL THEN column12:12 ELSE d:17 END [as=upsert_d:22, outer=(12,17,21)] 2722 └── CASE WHEN rowid:21 IS NULL THEN column13:13 ELSE rowid:21 END [as=upsert_rowid:26, outer=(13,21)] 2723 2724 # Make sure the passthrough columns of an UPDATE ... FROM query are pruned. 2725 norm 2726 UPDATE abcde 2727 SET 2728 b=family.b, c = family.c 2729 FROM 2730 family 2731 WHERE 2732 abcde.a=family.a 2733 RETURNING 2734 abcde.a, family.b, family.c 2735 ---- 2736 update abcde 2737 ├── columns: a:1!null b:12 c:13 2738 ├── fetch columns: abcde.a:6 abcde.b:7 abcde.c:8 abcde.d:9 abcde.e:10 2739 ├── update-mapping: 2740 │ ├── "family".b:12 => abcde.b:2 2741 │ └── "family".c:13 => abcde.c:3 2742 ├── volatile, side-effects, mutations 2743 ├── key: (1) 2744 ├── fd: (1)-->(12,13) 2745 └── inner-join (hash) 2746 ├── columns: abcde.a:6!null abcde.b:7 abcde.c:8 abcde.d:9 abcde.e:10 "family".a:11!null "family".b:12 "family".c:13 2747 ├── key: (11) 2748 ├── fd: (6)-->(7-10), (7,8)~~>(6,9,10), (11)-->(12,13), (6)==(11), (11)==(6) 2749 ├── scan abcde 2750 │ ├── columns: abcde.a:6!null abcde.b:7 abcde.c:8 abcde.d:9 abcde.e:10 2751 │ ├── key: (6) 2752 │ └── fd: (6)-->(7-10), (7,8)~~>(6,9,10) 2753 ├── scan "family" 2754 │ ├── columns: "family".a:11!null "family".b:12 "family".c:13 2755 │ ├── key: (11) 2756 │ └── fd: (11)-->(12,13) 2757 └── filters 2758 └── abcde.a:6 = "family".a:11 [outer=(6,11), constraints=(/6: (/NULL - ]; /11: (/NULL - ]), fd=(6)==(11), (11)==(6)] 2759 2760 # -------------------------------------------------- 2761 # PruneSemiAntiJoinRightCols 2762 # -------------------------------------------------- 2763 2764 # We should only see the `a` column scanned for family. 2765 norm expect=PruneSemiAntiJoinRightCols 2766 SELECT a, b, c FROM abcde WHERE EXISTS (SELECT * FROM family WHERE abcde.a=family.a) 2767 ---- 2768 semi-join (hash) 2769 ├── columns: a:1!null b:2 c:3 2770 ├── key: (1) 2771 ├── fd: (1)-->(2,3), (2,3)~~>(1) 2772 ├── scan abcde 2773 │ ├── columns: abcde.a:1!null abcde.b:2 abcde.c:3 2774 │ ├── key: (1) 2775 │ └── fd: (1)-->(2,3), (2,3)~~>(1) 2776 ├── scan "family" 2777 │ ├── columns: "family".a:6!null 2778 │ └── key: (6) 2779 └── filters 2780 └── abcde.a:1 = "family".a:6 [outer=(1,6), constraints=(/1: (/NULL - ]; /6: (/NULL - ]), fd=(1)==(6), (6)==(1)] 2781 2782 # We should see the `a`, `b` and `c` columns scanned for family. 2783 norm expect=PruneSemiAntiJoinRightCols 2784 SELECT a, b, c FROM abcde WHERE EXISTS (SELECT * FROM family WHERE abcde.a=family.a AND abcde.b > family.b + family.c) 2785 ---- 2786 semi-join (hash) 2787 ├── columns: a:1!null b:2 c:3 2788 ├── key: (1) 2789 ├── fd: (1)-->(2,3), (2,3)~~>(1) 2790 ├── scan abcde 2791 │ ├── columns: abcde.a:1!null abcde.b:2 abcde.c:3 2792 │ ├── key: (1) 2793 │ └── fd: (1)-->(2,3), (2,3)~~>(1) 2794 ├── scan "family" 2795 │ ├── columns: "family".a:6!null "family".b:7 "family".c:8 2796 │ ├── key: (6) 2797 │ └── fd: (6)-->(7,8) 2798 └── filters 2799 ├── abcde.a:1 = "family".a:6 [outer=(1,6), constraints=(/1: (/NULL - ]; /6: (/NULL - ]), fd=(1)==(6), (6)==(1)] 2800 └── abcde.b:2 > ("family".b:7 + "family".c:8) [outer=(2,7,8), constraints=(/2: (/NULL - ])] 2801 2802 norm expect=PruneSemiAntiJoinRightCols 2803 SELECT a, b, c FROM abcde WHERE NOT EXISTS (SELECT * FROM family WHERE abcde.a=family.a) 2804 ---- 2805 anti-join (hash) 2806 ├── columns: a:1!null b:2 c:3 2807 ├── key: (1) 2808 ├── fd: (1)-->(2,3), (2,3)~~>(1) 2809 ├── scan abcde 2810 │ ├── columns: abcde.a:1!null abcde.b:2 abcde.c:3 2811 │ ├── key: (1) 2812 │ └── fd: (1)-->(2,3), (2,3)~~>(1) 2813 ├── scan "family" 2814 │ ├── columns: "family".a:6!null 2815 │ └── key: (6) 2816 └── filters 2817 └── abcde.a:1 = "family".a:6 [outer=(1,6), constraints=(/1: (/NULL - ]; /6: (/NULL - ]), fd=(1)==(6), (6)==(1)] 2818 2819 # Test using multi-level nesting so we don't decorrelate the semi-join. 2820 norm expect=PruneSemiAntiJoinRightCols 2821 SELECT 2822 a, b, c 2823 FROM 2824 abcde 2825 WHERE 2826 EXISTS( 2827 SELECT 2828 * 2829 FROM 2830 "family" 2831 WHERE 2832 abcde.a = "family".a AND EXISTS(SELECT * FROM a WHERE abcde.a = a.k) 2833 ) 2834 ---- 2835 semi-join-apply 2836 ├── columns: a:1!null b:2 c:3 2837 ├── key: (1) 2838 ├── fd: (1)-->(2,3), (2,3)~~>(1) 2839 ├── scan abcde 2840 │ ├── columns: abcde.a:1!null abcde.b:2 abcde.c:3 2841 │ ├── key: (1) 2842 │ └── fd: (1)-->(2,3), (2,3)~~>(1) 2843 ├── semi-join (cross) 2844 │ ├── columns: "family".a:6!null 2845 │ ├── outer: (1) 2846 │ ├── key: (6) 2847 │ ├── scan "family" 2848 │ │ ├── columns: "family".a:6!null 2849 │ │ └── key: (6) 2850 │ ├── scan a 2851 │ │ ├── columns: k:11!null 2852 │ │ └── key: (11) 2853 │ └── filters 2854 │ └── abcde.a:1 = k:11 [outer=(1,11), constraints=(/1: (/NULL - ]; /11: (/NULL - ]), fd=(1)==(11), (11)==(1)] 2855 └── filters 2856 └── abcde.a:1 = "family".a:6 [outer=(1,6), constraints=(/1: (/NULL - ]; /6: (/NULL - ]), fd=(1)==(6), (6)==(1)] 2857 2858 # Test using multi-level nesting so we don't decorrelate the anti-join. 2859 norm expect=PruneSemiAntiJoinRightCols 2860 SELECT 2861 a, b, c 2862 FROM 2863 abcde 2864 WHERE 2865 NOT EXISTS( 2866 SELECT 2867 * 2868 FROM 2869 "family" 2870 WHERE 2871 abcde.a = "family".a AND EXISTS(SELECT * FROM a WHERE abcde.a = a.k) 2872 ) 2873 ---- 2874 anti-join-apply 2875 ├── columns: a:1!null b:2 c:3 2876 ├── key: (1) 2877 ├── fd: (1)-->(2,3), (2,3)~~>(1) 2878 ├── scan abcde 2879 │ ├── columns: abcde.a:1!null abcde.b:2 abcde.c:3 2880 │ ├── key: (1) 2881 │ └── fd: (1)-->(2,3), (2,3)~~>(1) 2882 ├── semi-join (cross) 2883 │ ├── columns: "family".a:6!null 2884 │ ├── outer: (1) 2885 │ ├── key: (6) 2886 │ ├── scan "family" 2887 │ │ ├── columns: "family".a:6!null 2888 │ │ └── key: (6) 2889 │ ├── scan a 2890 │ │ ├── columns: k:11!null 2891 │ │ └── key: (11) 2892 │ └── filters 2893 │ └── abcde.a:1 = k:11 [outer=(1,11), constraints=(/1: (/NULL - ]; /11: (/NULL - ]), fd=(1)==(11), (11)==(1)] 2894 └── filters 2895 └── abcde.a:1 = "family".a:6 [outer=(1,6), constraints=(/1: (/NULL - ]; /6: (/NULL - ]), fd=(1)==(6), (6)==(1)] 2896 2897 norm disable=InlineWith expect=PruneWithScanCols 2898 WITH foo AS (SELECT * FROM a) 2899 SELECT i FROM foo 2900 ---- 2901 with &1 (foo) 2902 ├── columns: i:6 2903 ├── scan a 2904 │ ├── columns: a.k:1!null a.i:2 a.f:3 a.s:4 2905 │ ├── key: (1) 2906 │ └── fd: (1)-->(2-4) 2907 └── with-scan &1 (foo) 2908 ├── columns: i:6 2909 └── mapping: 2910 └── a.i:2 => i:6 2911 2912 norm disable=InlineWith format=show-all expect=PruneWithCols 2913 WITH foo AS (SELECT * FROM a) 2914 SELECT i FROM (SELECT i, 1 AS y FROM foo) ORDER BY y 2915 ---- 2916 with &1 (foo) 2917 ├── columns: i:6(int) 2918 ├── stats: [rows=1000] 2919 ├── cost: 1080.04 2920 ├── prune: (6) 2921 ├── scan t.public.a 2922 │ ├── columns: t.public.a.k:1(int!null) t.public.a.i:2(int) t.public.a.f:3(float) t.public.a.s:4(string) 2923 │ ├── stats: [rows=1000] 2924 │ ├── cost: 1080.02 2925 │ ├── key: (1) 2926 │ ├── fd: (1)-->(2-4) 2927 │ ├── prune: (1-4) 2928 │ └── interesting orderings: (+1) 2929 └── with-scan &1 (foo) 2930 ├── columns: i:6(int) 2931 ├── mapping: 2932 │ └── t.public.a.i:2(int) => i:6(int) 2933 ├── stats: [rows=1000] 2934 ├── cost: 0.01 2935 ├── prune: (6) 2936 └── cte-uses 2937 └── &1: count=1 used-columns=(2) 2938 2939 # -------------------------------------------------- 2940 # PruneUnionAllCols 2941 # -------------------------------------------------- 2942 2943 norm expect=PruneUnionAllCols 2944 SELECT a FROM ( 2945 SELECT a, b FROM abcde 2946 UNION ALL 2947 SELECT * FROM xy 2948 ) 2949 ---- 2950 union-all 2951 ├── columns: a:8!null 2952 ├── left columns: abcde.a:1 2953 ├── right columns: x:6 2954 ├── scan abcde 2955 │ ├── columns: abcde.a:1!null 2956 │ └── key: (1) 2957 └── scan xy 2958 ├── columns: x:6!null 2959 └── key: (6) 2960 2961 norm expect=PruneUnionAllCols 2962 SELECT count(*) FROM ( 2963 SELECT a, b FROM abcde 2964 UNION ALL 2965 SELECT * FROM xy 2966 ) 2967 ---- 2968 scalar-group-by 2969 ├── columns: count:10!null 2970 ├── cardinality: [1 - 1] 2971 ├── key: () 2972 ├── fd: ()-->(10) 2973 ├── union-all 2974 │ ├── scan abcde 2975 │ └── scan xy 2976 └── aggregations 2977 └── count-rows [as=count_rows:10] 2978 2979 norm expect=PruneUnionAllCols 2980 SELECT 1 FROM (SELECT a FROM abcde WHERE a > 3 UNION ALL SELECT a FROM abcde) 2981 ---- 2982 project 2983 ├── columns: "?column?":12!null 2984 ├── fd: ()-->(12) 2985 ├── union-all 2986 │ ├── project 2987 │ │ └── select 2988 │ │ ├── columns: abcde.a:1!null 2989 │ │ ├── key: (1) 2990 │ │ ├── scan abcde 2991 │ │ │ ├── columns: abcde.a:1!null 2992 │ │ │ └── key: (1) 2993 │ │ └── filters 2994 │ │ └── abcde.a:1 > 3 [outer=(1), constraints=(/1: [/4 - ]; tight)] 2995 │ └── scan abcde 2996 └── projections 2997 └── 1 [as="?column?":12] 2998 2999 norm expect=PruneUnionAllCols 3000 SELECT 1 FROM a INNER JOIN (SELECT a, b FROM abcde UNION ALL SELECT * from xy) AS b ON a.i=b.b 3001 ---- 3002 project 3003 ├── columns: "?column?":14!null 3004 ├── fd: ()-->(14) 3005 ├── inner-join (hash) 3006 │ ├── columns: i:2!null b:13!null 3007 │ ├── fd: (2)==(13), (13)==(2) 3008 │ ├── scan a 3009 │ │ └── columns: i:2 3010 │ ├── union-all 3011 │ │ ├── columns: b:13 3012 │ │ ├── left columns: abcde.b:6 3013 │ │ ├── right columns: y:11 3014 │ │ ├── scan abcde 3015 │ │ │ └── columns: abcde.b:6 3016 │ │ └── scan xy 3017 │ │ └── columns: y:11 3018 │ └── filters 3019 │ └── i:2 = b:13 [outer=(2,13), constraints=(/2: (/NULL - ]; /13: (/NULL - ]), fd=(2)==(13), (13)==(2)] 3020 └── projections 3021 └── 1 [as="?column?":14] 3022 3023 # Test that even when one side of the UnionAll input has a greater 3024 # number of prunable columns than the other (neither the top-level 3025 # Project nor the UnionAll need any input columns, but the right-hand 3026 # Scan has a filter and cannot prune column x), a Project is added to 3027 # ensure that both inputs to the UnionAll have the same number of 3028 # columns. 3029 norm expect=PruneUnionAllCols 3030 SELECT 1 FROM ( 3031 SELECT a, b FROM abcde 3032 UNION ALL 3033 SELECT * from xy WHERE x=1 3034 ) 3035 ---- 3036 project 3037 ├── columns: "?column?":10!null 3038 ├── fd: ()-->(10) 3039 ├── union-all 3040 │ ├── scan abcde 3041 │ └── project 3042 │ ├── cardinality: [0 - 1] 3043 │ ├── key: () 3044 │ └── select 3045 │ ├── columns: x:6!null 3046 │ ├── cardinality: [0 - 1] 3047 │ ├── key: () 3048 │ ├── fd: ()-->(6) 3049 │ ├── scan xy 3050 │ │ ├── columns: x:6!null 3051 │ │ └── key: (6) 3052 │ └── filters 3053 │ └── x:6 = 1 [outer=(6), constraints=(/6: [/1 - /1]; tight), fd=()-->(6)] 3054 └── projections 3055 └── 1 [as="?column?":10] 3056 3057 # Regression test for #41772. 3058 3059 exec-ddl 3060 CREATE TABLE table41772 () 3061 ---- 3062 3063 norm 3064 WITH 3065 a AS (SELECT NULL FROM table41772), 3066 b 3067 AS ( 3068 SELECT 3069 * 3070 FROM 3071 (VALUES ((SELECT true FROM table41772), ARRAY[0, 0, 0, 0:::OID])) 3072 AS l (u, v) 3073 UNION ALL 3074 SELECT 3075 * 3076 FROM 3077 (VALUES (NULL, NULL), (false, ARRAY[0:::OID])) 3078 AS r (x, y) 3079 ) 3080 SELECT 3081 NULL 3082 FROM 3083 a, b 3084 WHERE 3085 b.u 3086 ---- 3087 project 3088 ├── columns: "?column?":14 3089 ├── fd: ()-->(14) 3090 ├── inner-join (cross) 3091 │ ├── columns: u:9!null 3092 │ ├── scan table41772 3093 │ ├── union-all 3094 │ │ ├── columns: u:9!null 3095 │ │ ├── left columns: column1:5 3096 │ │ ├── right columns: column1:7 3097 │ │ ├── cardinality: [0 - 3] 3098 │ │ ├── select 3099 │ │ │ ├── columns: column1:5!null 3100 │ │ │ ├── cardinality: [0 - 1] 3101 │ │ │ ├── key: () 3102 │ │ │ ├── fd: ()-->(5) 3103 │ │ │ ├── values 3104 │ │ │ │ ├── columns: column1:5 3105 │ │ │ │ ├── cardinality: [1 - 1] 3106 │ │ │ │ ├── key: () 3107 │ │ │ │ ├── fd: ()-->(5) 3108 │ │ │ │ └── tuple 3109 │ │ │ │ └── subquery 3110 │ │ │ │ └── max1-row 3111 │ │ │ │ ├── columns: bool:4!null 3112 │ │ │ │ ├── error: "more than one row returned by a subquery used as an expression" 3113 │ │ │ │ ├── cardinality: [0 - 1] 3114 │ │ │ │ ├── key: () 3115 │ │ │ │ ├── fd: ()-->(4) 3116 │ │ │ │ └── project 3117 │ │ │ │ ├── columns: bool:4!null 3118 │ │ │ │ ├── fd: ()-->(4) 3119 │ │ │ │ ├── scan table41772 3120 │ │ │ │ └── projections 3121 │ │ │ │ └── true [as=bool:4] 3122 │ │ │ └── filters 3123 │ │ │ └── column1:5 [outer=(5), constraints=(/5: [/true - /true]; tight), fd=()-->(5)] 3124 │ │ └── select 3125 │ │ ├── columns: column1:7!null 3126 │ │ ├── cardinality: [0 - 2] 3127 │ │ ├── fd: ()-->(7) 3128 │ │ ├── values 3129 │ │ │ ├── columns: column1:7 3130 │ │ │ ├── cardinality: [2 - 2] 3131 │ │ │ ├── (NULL,) 3132 │ │ │ └── (false,) 3133 │ │ └── filters 3134 │ │ └── column1:7 [outer=(7), constraints=(/7: [/true - /true]; tight), fd=()-->(7)] 3135 │ └── filters (true) 3136 └── projections 3137 └── NULL [as="?column?":14]