github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/norm/testdata/rules/project (about) 1 exec-ddl 2 CREATE TABLE a (x INT PRIMARY KEY, y INT, f FLOAT, s STRING) 3 ---- 4 5 exec-ddl 6 CREATE TABLE b (x INT PRIMARY KEY, z INT) 7 ---- 8 9 # -------------------------------------------------- 10 # EliminateProject 11 # -------------------------------------------------- 12 13 # Same order, same names. 14 norm expect=EliminateProject 15 SELECT x, y FROM a 16 ---- 17 scan a 18 ├── columns: x:1!null y:2 19 ├── key: (1) 20 └── fd: (1)-->(2) 21 22 # Different order, aliased names. 23 norm expect=EliminateProject 24 SELECT a.y AS aliasy, a.x FROM a 25 ---- 26 scan a 27 ├── columns: aliasy:2 x:1!null 28 ├── key: (1) 29 └── fd: (1)-->(2) 30 31 # Reordered, duplicate, aliased columns. 32 norm expect=EliminateProject 33 SELECT a.y AS alias1, a.x, a.y AS alias1, a.x FROM a 34 ---- 35 scan a 36 ├── columns: alias1:2 x:1!null alias1:2 x:1!null 37 ├── key: (1) 38 └── fd: (1)-->(2) 39 40 # Added column (projection should not be eliminated). 41 norm expect-not=EliminateProject 42 SELECT *, 1 r FROM a 43 ---- 44 project 45 ├── columns: x:1!null y:2 f:3 s:4 r:5!null 46 ├── key: (1) 47 ├── fd: ()-->(5), (1)-->(2-4) 48 ├── scan a 49 │ ├── columns: x:1!null y:2 f:3 s:4 50 │ ├── key: (1) 51 │ └── fd: (1)-->(2-4) 52 └── projections 53 └── 1 [as=r:5] 54 55 # -------------------------------------------------- 56 # MergeProjects 57 # -------------------------------------------------- 58 59 # Inner project has no synthesized columns. 60 norm expect=MergeProjects 61 SELECT y+1 AS r FROM (SELECT a.y FROM a, b WHERE a.x=b.x) a 62 ---- 63 project 64 ├── columns: r:7 65 ├── inner-join (hash) 66 │ ├── columns: a.x:1!null y:2 b.x:5!null 67 │ ├── key: (5) 68 │ ├── fd: (1)-->(2), (1)==(5), (5)==(1) 69 │ ├── scan a 70 │ │ ├── columns: a.x:1!null y:2 71 │ │ ├── key: (1) 72 │ │ └── fd: (1)-->(2) 73 │ ├── scan b 74 │ │ ├── columns: b.x:5!null 75 │ │ └── key: (5) 76 │ └── filters 77 │ └── a.x:1 = b.x:5 [outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)] 78 └── projections 79 └── y:2 + 1 [as=r:7, outer=(2)] 80 81 # Outer and inner projections have synthesized columns. 82 norm expect=MergeProjects 83 SELECT y1, f+1 FROM (SELECT y+1 AS y1, f FROM a) 84 ---- 85 project 86 ├── columns: y1:5 "?column?":6 87 ├── scan a 88 │ └── columns: y:2 f:3 89 └── projections 90 ├── f:3 + 1.0 [as="?column?":6, outer=(3)] 91 └── y:2 + 1 [as=y1:5, outer=(2)] 92 93 # Multiple synthesized columns in both outer and inner projections. 94 norm expect=MergeProjects 95 SELECT y1, f+1, x2, s||'foo' FROM (SELECT y+1 AS y1, f, s, x*2 AS x2 FROM a) 96 ---- 97 project 98 ├── columns: y1:5 "?column?":7 x2:6!null "?column?":8 99 ├── scan a 100 │ ├── columns: x:1!null y:2 f:3 s:4 101 │ ├── key: (1) 102 │ └── fd: (1)-->(2-4) 103 └── projections 104 ├── f:3 + 1.0 [as="?column?":7, outer=(3)] 105 ├── s:4 || 'foo' [as="?column?":8, outer=(4)] 106 ├── y:2 + 1 [as=y1:5, outer=(2)] 107 └── x:1 * 2 [as=x2:6, outer=(1)] 108 109 # Outer project selects subset of inner columns. 110 norm expect=MergeProjects 111 SELECT y1 FROM (SELECT y+1 AS y1, f*2 AS f2 FROM a) 112 ---- 113 project 114 ├── columns: y1:5 115 ├── scan a 116 │ └── columns: y:2 117 └── projections 118 └── y:2 + 1 [as=y1:5, outer=(2)] 119 120 # Don't merge, since outer depends on inner. 121 norm expect-not=MergeProjects 122 SELECT y1*2, y1/2 FROM (SELECT y+1 AS y1 FROM a) 123 ---- 124 project 125 ├── columns: "?column?":6 "?column?":7 126 ├── project 127 │ ├── columns: y1:5 128 │ ├── scan a 129 │ │ └── columns: y:2 130 │ └── projections 131 │ └── y:2 + 1 [as=y1:5, outer=(2)] 132 └── projections 133 ├── y1:5 * 2 [as="?column?":6, outer=(5)] 134 └── y1:5 / 2 [as="?column?":7, outer=(5)] 135 136 # Discard all inner columns. 137 norm expect=MergeProjects 138 SELECT 1 r FROM (SELECT y+1, x FROM a) a 139 ---- 140 project 141 ├── columns: r:6!null 142 ├── fd: ()-->(6) 143 ├── scan a 144 └── projections 145 └── 1 [as=r:6] 146 147 # -------------------------------------------------- 148 # MergeProjectWithValues 149 # -------------------------------------------------- 150 151 norm expect=MergeProjectWithValues 152 SELECT column1, 3 FROM (VALUES (1, 2)) 153 ---- 154 values 155 ├── columns: column1:1!null "?column?":3!null 156 ├── cardinality: [1 - 1] 157 ├── key: () 158 ├── fd: ()-->(1,3) 159 └── (1, 3) 160 161 # Only passthrough columns. 162 norm expect=MergeProjectWithValues 163 SELECT column1, column3 FROM (VALUES (1, 2, 3)) 164 ---- 165 values 166 ├── columns: column1:1!null column3:3!null 167 ├── cardinality: [1 - 1] 168 ├── key: () 169 ├── fd: ()-->(1,3) 170 └── (1, 3) 171 172 # Only synthesized columns. 173 norm expect=MergeProjectWithValues 174 SELECT 4, 5 FROM (VALUES (1, 2, 3)) 175 ---- 176 values 177 ├── columns: "?column?":4!null "?column?":5!null 178 ├── cardinality: [1 - 1] 179 ├── key: () 180 ├── fd: ()-->(4,5) 181 └── (4, 5) 182 183 # Don't trigger rule when there is more than one Values row. 184 norm expect-not=MergeProjectWithValues 185 SELECT column1, 3 FROM (VALUES (1, 2), (1, 4)) 186 ---- 187 project 188 ├── columns: column1:1!null "?column?":3!null 189 ├── cardinality: [2 - 2] 190 ├── fd: ()-->(3) 191 ├── values 192 │ ├── columns: column1:1!null 193 │ ├── cardinality: [2 - 2] 194 │ ├── (1,) 195 │ └── (1,) 196 └── projections 197 └── 3 [as="?column?":3] 198 199 # Don't trigger rule when Project column depends on Values column. 200 norm expect-not=MergeProjectWithValues 201 SELECT column1+1, 3 FROM (VALUES ($1::int, $2::int)) 202 ---- 203 project 204 ├── columns: "?column?":3 "?column?":4!null 205 ├── cardinality: [1 - 1] 206 ├── has-placeholder 207 ├── key: () 208 ├── fd: ()-->(3,4) 209 ├── values 210 │ ├── columns: column1:1 211 │ ├── cardinality: [1 - 1] 212 │ ├── has-placeholder 213 │ ├── key: () 214 │ ├── fd: ()-->(1) 215 │ └── ($1::INT8,) 216 └── projections 217 ├── column1:1 + 1 [as="?column?":3, outer=(1)] 218 └── 3 [as="?column?":4] 219 220 # -------------------------------------------------- 221 # FoldTupleAccessIntoValues 222 # -------------------------------------------------- 223 224 # Simple case with VALUES operator. 225 norm expect=FoldTupleAccessIntoValues 226 SELECT (tup).@1, (tup).@2 FROM (VALUES ((1,2)), ((3,4))) AS v(tup) 227 ---- 228 values 229 ├── columns: "?column?":2!null "?column?":3!null 230 ├── cardinality: [2 - 2] 231 ├── (1, 2) 232 └── (3, 4) 233 234 # Simple case with unnest function. 235 norm expect=FoldTupleAccessIntoValues 236 SELECT (Tuples).@1, (Tuples).@2 FROM unnest(ARRAY[(1,2),(3,4)]) AS Tuples 237 ---- 238 values 239 ├── columns: "?column?":2!null "?column?":3!null 240 ├── cardinality: [2 - 2] 241 ├── (1, 2) 242 └── (3, 4) 243 244 # Case with tuples containing multiple types. 245 norm expect=FoldTupleAccessIntoValues 246 SELECT (tup).@1, (tup).@2, (tup).@3 FROM (VALUES ((1,'2',3.0)), ((4,'5',NULL::DECIMAL))) AS v(tup) 247 ---- 248 values 249 ├── columns: "?column?":2!null "?column?":3!null "?column?":4 250 ├── cardinality: [2 - 2] 251 ├── (1, '2', 3.0) 252 └── (4, '5', NULL) 253 254 # Case with one tuple field referenced zero times, one field referenced once, 255 # and one field referenced twice. 256 norm expect=FoldTupleAccessIntoValues 257 SELECT (tup).@2, (tup).@3, ARRAY[(tup).@3] FROM (VALUES ((1,2,3))) AS v(tup) 258 ---- 259 values 260 ├── columns: "?column?":2!null "?column?":3!null array:4!null 261 ├── cardinality: [1 - 1] 262 ├── key: () 263 ├── fd: ()-->(2-4) 264 └── (2, 3, ARRAY[3]) 265 266 # Case with tuples of empty tuples. 267 norm expect=FoldTupleAccessIntoValues 268 SELECT (Tuples).@1, (Tuples).@2 FROM unnest(ARRAY[((),()),((),())]) AS Tuples 269 ---- 270 values 271 ├── columns: "?column?":2!null "?column?":3!null 272 ├── cardinality: [2 - 2] 273 ├── ((), ()) 274 └── ((), ()) 275 276 # Case with subquery projection. 277 norm expect=FoldTupleAccessIntoValues 278 SELECT (SELECT (tup).@1 * x FROM b) FROM (VALUES ((1,2)), ((3,4))) AS v(tup) 279 ---- 280 project 281 ├── columns: "?column?":5 282 ├── cardinality: [1 - ] 283 ├── ensure-distinct-on 284 │ ├── columns: "?column?":4 rownum:8!null 285 │ ├── grouping columns: rownum:8!null 286 │ ├── error: "more than one row returned by a subquery used as an expression" 287 │ ├── cardinality: [1 - ] 288 │ ├── key: (8) 289 │ ├── fd: (8)-->(4) 290 │ ├── left-join-apply 291 │ │ ├── columns: "?column?":4 column1_1:6!null rownum:8!null 292 │ │ ├── cardinality: [2 - ] 293 │ │ ├── fd: (8)-->(6) 294 │ │ ├── ordinality 295 │ │ │ ├── columns: column1_1:6!null rownum:8!null 296 │ │ │ ├── cardinality: [2 - 2] 297 │ │ │ ├── key: (8) 298 │ │ │ ├── fd: (8)-->(6) 299 │ │ │ └── values 300 │ │ │ ├── columns: column1_1:6!null 301 │ │ │ ├── cardinality: [2 - 2] 302 │ │ │ ├── (1,) 303 │ │ │ └── (3,) 304 │ │ ├── project 305 │ │ │ ├── columns: "?column?":4 306 │ │ │ ├── outer: (6) 307 │ │ │ ├── scan b 308 │ │ │ │ ├── columns: x:2!null 309 │ │ │ │ └── key: (2) 310 │ │ │ └── projections 311 │ │ │ └── x:2 * column1_1:6 [as="?column?":4, outer=(2,6)] 312 │ │ └── filters (true) 313 │ └── aggregations 314 │ └── const-agg [as="?column?":4, outer=(4)] 315 │ └── "?column?":4 316 └── projections 317 └── "?column?":4 [as="?column?":5, outer=(4)] 318 319 # Case where columns are unnested and then pruned away because the surrounding 320 # project only references an outer column. 321 norm expect=FoldTupleAccessIntoValues 322 SELECT (SELECT ((x).@1) FROM (VALUES ((5,6)),((7,8)))) FROM (VALUES ((1,2)), ((3,4))) v(x); 323 ---- 324 project 325 ├── columns: "?column?":6!null 326 ├── cardinality: [1 - 4] 327 ├── ensure-distinct-on 328 │ ├── columns: "?column?":3!null rownum:9!null 329 │ ├── grouping columns: rownum:9!null 330 │ ├── error: "more than one row returned by a subquery used as an expression" 331 │ ├── cardinality: [1 - 4] 332 │ ├── key: (9) 333 │ ├── fd: (9)-->(3) 334 │ ├── project 335 │ │ ├── columns: "?column?":3!null rownum:9!null 336 │ │ ├── cardinality: [4 - 4] 337 │ │ ├── fd: (9)-->(3) 338 │ │ ├── inner-join (cross) 339 │ │ │ ├── columns: column1_1:7!null rownum:9!null 340 │ │ │ ├── cardinality: [4 - 4] 341 │ │ │ ├── fd: (9)-->(7) 342 │ │ │ ├── ordinality 343 │ │ │ │ ├── columns: column1_1:7!null rownum:9!null 344 │ │ │ │ ├── cardinality: [2 - 2] 345 │ │ │ │ ├── key: (9) 346 │ │ │ │ ├── fd: (9)-->(7) 347 │ │ │ │ └── values 348 │ │ │ │ ├── columns: column1_1:7!null 349 │ │ │ │ ├── cardinality: [2 - 2] 350 │ │ │ │ ├── (1,) 351 │ │ │ │ └── (3,) 352 │ │ │ ├── values 353 │ │ │ │ ├── cardinality: [2 - 2] 354 │ │ │ │ ├── () 355 │ │ │ │ └── () 356 │ │ │ └── filters (true) 357 │ │ └── projections 358 │ │ └── column1_1:7 [as="?column?":3, outer=(7)] 359 │ └── aggregations 360 │ └── const-agg [as="?column?":3, outer=(3)] 361 │ └── "?column?":3 362 └── projections 363 └── "?column?":3 [as="?column?":6, outer=(3)] 364 365 # Case with named tuple access. 366 norm expect=FoldTupleAccessIntoValues 367 SELECT (tup).a, (tup).b 368 FROM (VALUES 369 (((1,2) AS a,b)), 370 (((3,4) AS a,b)) 371 ) v(tup) 372 ---- 373 values 374 ├── columns: a:2!null b:3!null 375 ├── cardinality: [2 - 2] 376 ├── ((1, 2) AS a, b) 377 └── ((3, 4) AS a, b) 378 379 # Case with wildcard tuple access on a named tuple. 380 norm expect=FoldTupleAccessIntoValues 381 SELECT (tup).* 382 FROM (VALUES 383 (((1,2) AS a,b)), 384 (((3,4) AS a,b)) 385 ) v(tup) 386 ---- 387 values 388 ├── columns: a:2!null b:3!null 389 ├── cardinality: [2 - 2] 390 ├── ((1, 2) AS a, b) 391 └── ((3, 4) AS a, b) 392 393 # Case with wildcard tuple access on an unnamed tuple. 394 norm expect=FoldTupleAccessIntoValues 395 SELECT (tup).* 396 FROM (VALUES 397 ((1,2)), 398 ((3,4)) 399 ) v(tup) 400 ---- 401 values 402 ├── columns: "?column?":2!null "?column?":3!null 403 ├── cardinality: [2 - 2] 404 ├── (1, 2) 405 └── (3, 4) 406 407 # No-op case because the Values operator has more than one column. 408 norm expect-not=FoldTupleAccessIntoValues 409 SELECT (col1).@1, (col2).@1 FROM (VALUES ((1,2),(3,4)), ((5,6),(7,8))) AS v(col1, col2) 410 ---- 411 project 412 ├── columns: "?column?":3 "?column?":4 413 ├── cardinality: [2 - 2] 414 ├── values 415 │ ├── columns: column1:1 column2:2 416 │ ├── cardinality: [2 - 2] 417 │ ├── ((1, 2), (3, 4)) 418 │ └── ((5, 6), (7, 8)) 419 └── projections 420 ├── (column1:1).@1 [as="?column?":3, outer=(1)] 421 └── (column2:2).@1 [as="?column?":4, outer=(2)] 422 423 # No-op case because the single column in Values is not of type tuple. 424 norm expect-not=FoldTupleAccessIntoValues 425 SELECT col[1], col[2] FROM unnest(ARRAY[[1,2],[3,4]]) AS col 426 ---- 427 project 428 ├── columns: col:2 col:3 429 ├── cardinality: [2 - 2] 430 ├── values 431 │ ├── columns: unnest:1!null 432 │ ├── cardinality: [2 - 2] 433 │ ├── (ARRAY[1,2],) 434 │ └── (ARRAY[3,4],) 435 └── projections 436 ├── unnest:1[1] [as=col:2, outer=(1)] 437 └── unnest:1[2] [as=col:3, outer=(1)] 438 439 # No-op case because one of the tuple rows in Values can only be determined at 440 # run-time. Put dynamic tuple expression at end of list to ensure that all rows 441 # are checked. 442 norm expect-not=FoldTupleAccessIntoValues 443 SELECT (tup).@1, (tup).@2 FROM (VALUES ((3,4)), ((SELECT (x, z) FROM b))) AS v(tup) 444 ---- 445 project 446 ├── columns: "?column?":5 "?column?":6 447 ├── cardinality: [2 - 2] 448 ├── values 449 │ ├── columns: column1:4 450 │ ├── cardinality: [2 - 2] 451 │ ├── ((3, 4),) 452 │ └── tuple 453 │ └── subquery 454 │ └── max1-row 455 │ ├── columns: "?column?":3 456 │ ├── error: "more than one row returned by a subquery used as an expression" 457 │ ├── cardinality: [0 - 1] 458 │ ├── key: () 459 │ ├── fd: ()-->(3) 460 │ └── project 461 │ ├── columns: "?column?":3 462 │ ├── scan b 463 │ │ ├── columns: x:1!null z:2 464 │ │ ├── key: (1) 465 │ │ └── fd: (1)-->(2) 466 │ └── projections 467 │ └── (x:1, z:2) [as="?column?":3, outer=(1,2)] 468 └── projections 469 ├── (column1:4).@1 [as="?column?":5, outer=(4)] 470 └── (column1:4).@2 [as="?column?":6, outer=(4)] 471 472 # No-op case because the tuple itself is referenced rather than just its fields. 473 norm expect-not=FoldTupleAccessIntoValues 474 SELECT (tup).@1, (tup).@2, ARRAY[tup] FROM (VALUES ((1,2)), ((3,4))) AS v(tup) 475 ---- 476 project 477 ├── columns: "?column?":2 "?column?":3 array:4 478 ├── cardinality: [2 - 2] 479 ├── values 480 │ ├── columns: column1:1 481 │ ├── cardinality: [2 - 2] 482 │ ├── ((1, 2),) 483 │ └── ((3, 4),) 484 └── projections 485 ├── (column1:1).@1 [as="?column?":2, outer=(1)] 486 ├── (column1:1).@2 [as="?column?":3, outer=(1)] 487 └── ARRAY[column1:1] [as=array:4, outer=(1)] 488 489 # No-op case because the tuple itself is referenced. Make sure that a reference 490 # inside the input of a ColumnAccess is detected. 491 norm expect-not=FoldTupleAccessIntoValues 492 SELECT (least(tup, (1,2))).a FROM (VALUES (((1,2) AS a,b), ((3,4) AS a,b))) v(tup) 493 ---- 494 project 495 ├── columns: a:3 496 ├── cardinality: [1 - 1] 497 ├── immutable 498 ├── key: () 499 ├── fd: ()-->(3) 500 ├── values 501 │ ├── columns: column1:1 502 │ ├── cardinality: [1 - 1] 503 │ ├── key: () 504 │ ├── fd: ()-->(1) 505 │ └── (((1, 2) AS a, b),) 506 └── projections 507 └── (least(column1:1, (1, 2))).a [as=a:3, outer=(1), immutable] 508 509 # -------------------------------------------------- 510 # PushColumnRemappingIntoValues 511 # -------------------------------------------------- 512 513 # With clause case. This works because InlineWith creates a simple remapping 514 # projection on the Values output column. 515 norm expect=PushColumnRemappingIntoValues 516 WITH a AS (SELECT x FROM (VALUES (1), (2)) f(x)) SELECT x FROM a 517 ---- 518 values 519 ├── columns: x:2!null 520 ├── cardinality: [2 - 2] 521 ├── (1,) 522 └── (2,) 523 524 # Multiplication by one case. This works because after FoldMultOne and 525 # EliminateCast fire, the x*1 projection does no more than rename its input 526 # column. 527 norm expect=PushColumnRemappingIntoValues 528 SELECT x*1 FROM (VALUES (1), (2)) f(x) 529 ---- 530 values 531 ├── columns: "?column?":2!null 532 ├── cardinality: [2 - 2] 533 ├── (1,) 534 └── (2,) 535 536 # Tuple access case. This works because FoldTupleAccessIntoValues creates new 537 # columns that reference the tuple fields, and so the surrounding Project that 538 # references those fields becomes a remapping of the new columns. 539 norm expect=PushColumnRemappingIntoValues 540 SELECT (tup).@1, (tup).@2 FROM (VALUES ((1,2)), ((3,4))) AS v(tup) 541 ---- 542 values 543 ├── columns: "?column?":2!null "?column?":3!null 544 ├── cardinality: [2 - 2] 545 ├── (1, 2) 546 └── (3, 4) 547 548 # Case with multiple remappings of the same column. 549 norm expect=PushColumnRemappingIntoValues 550 WITH a AS (SELECT x, x FROM (VALUES (1), (2)) f(x)) SELECT * FROM a 551 ---- 552 project 553 ├── columns: x:2!null x:3!null 554 ├── cardinality: [2 - 2] 555 ├── fd: (2)==(3), (3)==(2) 556 ├── values 557 │ ├── columns: x:2!null 558 │ ├── cardinality: [2 - 2] 559 │ ├── (1,) 560 │ └── (2,) 561 └── projections 562 └── x:2 [as=x:3, outer=(2)] 563 564 # Case with a projection on a column only determined at run-time. 565 norm expect=PushColumnRemappingIntoValues 566 WITH a AS (SELECT v FROM (VALUES (1), ((SELECT z FROM b WHERE z=1))) f(v)) SELECT v FROM a 567 ---- 568 values 569 ├── columns: v:4 570 ├── cardinality: [2 - 2] 571 ├── (1,) 572 └── tuple 573 └── subquery 574 └── max1-row 575 ├── columns: z:2!null 576 ├── error: "more than one row returned by a subquery used as an expression" 577 ├── cardinality: [0 - 1] 578 ├── key: () 579 ├── fd: ()-->(2) 580 └── select 581 ├── columns: z:2!null 582 ├── fd: ()-->(2) 583 ├── scan b 584 │ └── columns: z:2 585 └── filters 586 └── z:2 = 1 [outer=(2), constraints=(/2: [/1 - /1]; tight), fd=()-->(2)] 587 588 # Case with a non-VariableExpr reference to a remapped column. 589 norm expect=PushColumnRemappingIntoValues 590 SELECT x*1, x+1 FROM (VALUES (1), (2)) f(x) 591 ---- 592 project 593 ├── columns: "?column?":2!null "?column?":3!null 594 ├── cardinality: [2 - 2] 595 ├── fd: (2)-->(3) 596 ├── values 597 │ ├── columns: "?column?":2!null 598 │ ├── cardinality: [2 - 2] 599 │ ├── (1,) 600 │ └── (2,) 601 └── projections 602 └── "?column?":2 + 1 [as="?column?":3, outer=(2)] 603 604 # Case with a subquery reference to a remapped column. 605 norm expect=PushColumnRemappingIntoValues 606 SELECT 607 x*1, 608 (SELECT * FROM (Values (1), (2), (3), (4)) WHERE x=12) 609 FROM 610 (VALUES (11), (12)) f(x) 611 ---- 612 project 613 ├── columns: "?column?":3!null "?column?":4 614 ├── cardinality: [1 - 8] 615 ├── ensure-distinct-on 616 │ ├── columns: column1:2 "?column?":3!null rownum:5!null 617 │ ├── grouping columns: rownum:5!null 618 │ ├── error: "more than one row returned by a subquery used as an expression" 619 │ ├── cardinality: [1 - 8] 620 │ ├── key: (5) 621 │ ├── fd: (5)-->(2,3) 622 │ ├── left-join (cross) 623 │ │ ├── columns: column1:2 "?column?":3!null rownum:5!null 624 │ │ ├── cardinality: [2 - 8] 625 │ │ ├── fd: (5)-->(3) 626 │ │ ├── ordinality 627 │ │ │ ├── columns: "?column?":3!null rownum:5!null 628 │ │ │ ├── cardinality: [2 - 2] 629 │ │ │ ├── key: (5) 630 │ │ │ ├── fd: (5)-->(3) 631 │ │ │ └── values 632 │ │ │ ├── columns: "?column?":3!null 633 │ │ │ ├── cardinality: [2 - 2] 634 │ │ │ ├── (11,) 635 │ │ │ └── (12,) 636 │ │ ├── values 637 │ │ │ ├── columns: column1:2!null 638 │ │ │ ├── cardinality: [4 - 4] 639 │ │ │ ├── (1,) 640 │ │ │ ├── (2,) 641 │ │ │ ├── (3,) 642 │ │ │ └── (4,) 643 │ │ └── filters 644 │ │ └── "?column?":3 = 12 [outer=(3), constraints=(/3: [/12 - /12]; tight), fd=()-->(3)] 645 │ └── aggregations 646 │ ├── const-agg [as=column1:2, outer=(2)] 647 │ │ └── column1:2 648 │ └── const-agg [as="?column?":3, outer=(3)] 649 │ └── "?column?":3 650 └── projections 651 └── column1:2 [as="?column?":4, outer=(2)] 652 653 # PushColumnRemappingIntoValues should only fold one projection into the 654 # passthrough columns because all the projections refer to the same column. 655 norm expect=PushColumnRemappingIntoValues 656 SELECT x*1*1, x*1 FROM (VALUES (1), (2)) v(x) 657 ---- 658 project 659 ├── columns: "?column?":2!null "?column?":3!null 660 ├── cardinality: [2 - 2] 661 ├── fd: (2)==(3), (3)==(2) 662 ├── values 663 │ ├── columns: "?column?":2!null 664 │ ├── cardinality: [2 - 2] 665 │ ├── (1,) 666 │ └── (2,) 667 └── projections 668 └── "?column?":2 [as="?column?":3, outer=(2)] 669 670 # Case with only one column that can be replaced (The z*1 column can replace the 671 # original z column). 672 norm expect=PushColumnRemappingIntoValues 673 SELECT x, x*1, y, y*1, z*1 FROM (VALUES (1,2,3), (2,3,6)) v(x,y,z) 674 ---- 675 project 676 ├── columns: x:1!null "?column?":4!null y:2!null "?column?":5!null "?column?":6!null 677 ├── cardinality: [2 - 2] 678 ├── fd: (1)==(4), (4)==(1), (2)==(5), (5)==(2) 679 ├── values 680 │ ├── columns: column1:1!null column2:2!null "?column?":6!null 681 │ ├── cardinality: [2 - 2] 682 │ ├── (1, 2, 3) 683 │ └── (2, 3, 6) 684 └── projections 685 ├── column1:1 [as="?column?":4, outer=(1)] 686 └── column2:2 [as="?column?":5, outer=(2)] 687 688 # No-op case because no columns from the input ValuesExpr are being remapped. 689 norm expect-not=PushColumnRemappingIntoValues 690 SELECT (SELECT x FROM (VALUES (1), (2)) f(x)) FROM (VALUES (2), (3)) 691 ---- 692 project 693 ├── columns: x:3 694 ├── cardinality: [2 - 2] 695 ├── fd: ()-->(3) 696 ├── values 697 │ ├── cardinality: [2 - 2] 698 │ ├── () 699 │ └── () 700 └── projections 701 └── subquery [as=x:3, subquery] 702 └── max1-row 703 ├── columns: column1:2!null 704 ├── error: "more than one row returned by a subquery used as an expression" 705 ├── cardinality: [1 - 1] 706 ├── key: () 707 ├── fd: ()-->(2) 708 └── values 709 ├── columns: column1:2!null 710 ├── cardinality: [2 - 2] 711 ├── (1,) 712 └── (2,) 713 714 # No-op case because a passthrough column is being remapped. 715 norm expect-not=PushColumnRemappingIntoValues 716 SELECT x, x*1 FROM (VALUES (1), (2)) v(x) 717 ---- 718 project 719 ├── columns: x:1!null "?column?":2!null 720 ├── cardinality: [2 - 2] 721 ├── fd: (1)==(2), (2)==(1) 722 ├── values 723 │ ├── columns: column1:1!null 724 │ ├── cardinality: [2 - 2] 725 │ ├── (1,) 726 │ └── (2,) 727 └── projections 728 └── column1:1 [as="?column?":2, outer=(1)] 729 730 # No-op case because the Project is on a Scan rather than a Values operator. 731 norm expect-not=PushColumnRemappingIntoValues 732 WITH t AS (SELECT * FROM a) SELECT x FROM t 733 ---- 734 project 735 ├── columns: x:5!null 736 ├── key: (5) 737 ├── scan a 738 │ ├── columns: a.x:1!null 739 │ └── key: (1) 740 └── projections 741 └── a.x:1 [as=x:5, outer=(1)] 742 743 # No-op case with no projections on the Project surrounding the Values operator. 744 # A Project with no projections is created when PruneUnionAllCols fires, and is 745 # then removed by EliminateProject. 746 norm expect-not=PushColumnRemappingIntoValues 747 WITH a AS 748 ( 749 SELECT * FROM (VALUES (1,2)) AS f(x,y) 750 UNION ALL (VALUES (3,4)) 751 ) 752 SELECT x FROM a 753 ---- 754 project 755 ├── columns: x:7!null 756 ├── cardinality: [2 - 2] 757 ├── union-all 758 │ ├── columns: x:5!null 759 │ ├── left columns: column1:1 760 │ ├── right columns: column1:3 761 │ ├── cardinality: [2 - 2] 762 │ ├── values 763 │ │ ├── columns: column1:1!null 764 │ │ ├── cardinality: [1 - 1] 765 │ │ ├── key: () 766 │ │ ├── fd: ()-->(1) 767 │ │ └── (1,) 768 │ └── values 769 │ ├── columns: column1:3!null 770 │ ├── cardinality: [1 - 1] 771 │ ├── key: () 772 │ ├── fd: ()-->(3) 773 │ └── (3,) 774 └── projections 775 └── x:5 [as=x:7, outer=(5)]