github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/norm/testdata/rules/inline (about) 1 exec-ddl 2 CREATE TABLE a (k INT PRIMARY KEY, i INT, f FLOAT, s STRING, j JSON) 3 ---- 4 5 exec-ddl 6 CREATE TABLE xy (x INT PRIMARY KEY, y INT) 7 ---- 8 9 exec-ddl 10 CREATE TABLE computed (a INT PRIMARY KEY, b INT, c INT AS (a+b+1) STORED) 11 ---- 12 13 exec-ddl 14 CREATE TABLE b (k INT PRIMARY KEY, i INT, f FLOAT, s STRING NOT NULL, j JSON) 15 ---- 16 17 # -------------------------------------------------- 18 # InlineConstVar 19 # -------------------------------------------------- 20 21 norm expect=InlineConstVar 22 SELECT k FROM b WHERE i=5 AND i IN (1, 2, 3, 4, 5) 23 ---- 24 project 25 ├── columns: k:1!null 26 ├── key: (1) 27 └── select 28 ├── columns: k:1!null i:2!null 29 ├── key: (1) 30 ├── fd: ()-->(2) 31 ├── scan b 32 │ ├── columns: k:1!null i:2 33 │ ├── key: (1) 34 │ └── fd: (1)-->(2) 35 └── filters 36 └── i:2 = 5 [outer=(2), constraints=(/2: [/5 - /5]; tight), fd=()-->(2)] 37 38 norm expect=InlineConstVar 39 SELECT k FROM b WHERE i=8 AND 3 = mod(i, 5) 40 ---- 41 project 42 ├── columns: k:1!null 43 ├── key: (1) 44 └── select 45 ├── columns: k:1!null i:2!null 46 ├── key: (1) 47 ├── fd: ()-->(2) 48 ├── scan b 49 │ ├── columns: k:1!null i:2 50 │ ├── key: (1) 51 │ └── fd: (1)-->(2) 52 └── filters 53 └── i:2 = 8 [outer=(2), constraints=(/2: [/8 - /8]; tight), fd=()-->(2)] 54 55 norm expect=InlineConstVar 56 SELECT k FROM b WHERE i=5 AND i IN (1, 2, 3, 4) 57 ---- 58 values 59 ├── columns: k:1!null 60 ├── cardinality: [0 - 0] 61 ├── key: () 62 └── fd: ()-->(1) 63 64 # Case that requires multiple iterations to fully inline. 65 norm expect=InlineConstVar 66 SELECT * FROM xy WHERE x=y AND y=4 AND x IN (1, 2, 3, 4) 67 ---- 68 select 69 ├── columns: x:1!null y:2!null 70 ├── cardinality: [0 - 1] 71 ├── key: () 72 ├── fd: ()-->(1,2) 73 ├── scan xy 74 │ ├── columns: x:1!null y:2 75 │ ├── key: (1) 76 │ └── fd: (1)-->(2) 77 └── filters 78 ├── x:1 = 4 [outer=(1), constraints=(/1: [/4 - /4]; tight), fd=()-->(1)] 79 └── y:2 = 4 [outer=(2), constraints=(/2: [/4 - /4]; tight), fd=()-->(2)] 80 81 norm expect=InlineConstVar 82 SELECT * FROM xy WHERE x=y AND y=4 AND x=3 83 ---- 84 values 85 ├── columns: x:1!null y:2!null 86 ├── cardinality: [0 - 0] 87 ├── key: () 88 └── fd: ()-->(1,2) 89 90 # Can't inline composite types. 91 norm expect-not=InlineConstVar 92 SELECT * FROM (VALUES (0.0), (0.00), (0.000)) AS v (x) WHERE x = 0 AND x::STRING = '0.00'; 93 ---- 94 select 95 ├── columns: x:1!null 96 ├── cardinality: [0 - 3] 97 ├── fd: ()-->(1) 98 ├── values 99 │ ├── columns: column1:1!null 100 │ ├── cardinality: [3 - 3] 101 │ ├── (0.0,) 102 │ ├── (0.00,) 103 │ └── (0.000,) 104 └── filters 105 ├── column1:1 = 0 [outer=(1), constraints=(/1: [/0 - /0]; tight), fd=()-->(1)] 106 └── column1:1::STRING = '0.00' [outer=(1)] 107 108 # The rule should trigger, but not inline the composite type. 109 norm expect=InlineConstVar 110 SELECT * FROM (VALUES (0.0, 'a'), (0.00, 'b'), (0.000, 'b')) AS v (x, y) WHERE x = 0 AND x::STRING = '0.00' AND y = 'b' AND y IN ('a', 'b'); 111 ---- 112 select 113 ├── columns: x:1!null y:2!null 114 ├── cardinality: [0 - 3] 115 ├── fd: ()-->(1,2) 116 ├── values 117 │ ├── columns: column1:1!null column2:2!null 118 │ ├── cardinality: [3 - 3] 119 │ ├── (0.0, 'a') 120 │ ├── (0.00, 'b') 121 │ └── (0.000, 'b') 122 └── filters 123 ├── column1:1 = 0 [outer=(1), constraints=(/1: [/0 - /0]; tight), fd=()-->(1)] 124 ├── column1:1::STRING = '0.00' [outer=(1)] 125 └── column2:2 = 'b' [outer=(2), constraints=(/2: [/'b' - /'b']; tight), fd=()-->(2)] 126 127 # Ensure that InlineConstVar fires before filter pushdown rules. 128 norm expect=InlineConstVar 129 SELECT * FROM a INNER JOIN xy ON True WHERE y=10 AND i<y 130 ---- 131 inner-join (cross) 132 ├── columns: k:1!null i:2!null f:3 s:4 j:5 x:6!null y:7!null 133 ├── key: (1,6) 134 ├── fd: ()-->(7), (1)-->(2-5) 135 ├── select 136 │ ├── columns: k:1!null i:2!null f:3 s:4 j:5 137 │ ├── key: (1) 138 │ ├── fd: (1)-->(2-5) 139 │ ├── scan a 140 │ │ ├── columns: k:1!null i:2 f:3 s:4 j:5 141 │ │ ├── key: (1) 142 │ │ └── fd: (1)-->(2-5) 143 │ └── filters 144 │ └── i:2 < 10 [outer=(2), constraints=(/2: (/NULL - /9]; tight)] 145 ├── select 146 │ ├── columns: x:6!null y:7!null 147 │ ├── key: (6) 148 │ ├── fd: ()-->(7) 149 │ ├── scan xy 150 │ │ ├── columns: x:6!null y:7 151 │ │ ├── key: (6) 152 │ │ └── fd: (6)-->(7) 153 │ └── filters 154 │ └── y:7 = 10 [outer=(7), constraints=(/7: [/10 - /10]; tight), fd=()-->(7)] 155 └── filters (true) 156 157 # -------------------------------------------------- 158 # InlineProjectConstants 159 # -------------------------------------------------- 160 161 # Inline constants from Project expression. 162 norm expect=InlineProjectConstants 163 UPDATE computed SET a=1, b=2 164 ---- 165 update computed 166 ├── columns: <none> 167 ├── fetch columns: a:4 b:5 c:6 168 ├── update-mapping: 169 │ ├── a_new:7 => a:1 170 │ ├── b_new:8 => b:2 171 │ └── column9:9 => c:3 172 ├── cardinality: [0 - 0] 173 ├── volatile, side-effects, mutations 174 └── project 175 ├── columns: column9:9!null a_new:7!null b_new:8!null a:4!null b:5 c:6 176 ├── key: (4) 177 ├── fd: ()-->(7-9), (4)-->(5,6) 178 ├── scan computed 179 │ ├── columns: a:4!null b:5 c:6 180 │ ├── computed column expressions 181 │ │ └── c:6 182 │ │ └── (a:4 + b:5) + 1 183 │ ├── key: (4) 184 │ └── fd: (4)-->(5,6) 185 └── projections 186 ├── 4 [as=column9:9] 187 ├── 1 [as=a_new:7] 188 └── 2 [as=b_new:8] 189 190 # Inline constants from Values expression. 191 norm expect=InlineProjectConstants 192 SELECT one+two+three+four FROM (VALUES (1, $1:::int, 2, $2:::int)) AS t(one, two, three, four) 193 ---- 194 project 195 ├── columns: "?column?":5 196 ├── cardinality: [1 - 1] 197 ├── has-placeholder 198 ├── key: () 199 ├── fd: ()-->(5) 200 ├── values 201 │ ├── columns: column2:2 column4:4 202 │ ├── cardinality: [1 - 1] 203 │ ├── has-placeholder 204 │ ├── key: () 205 │ ├── fd: ()-->(2,4) 206 │ └── ($1, $2) 207 └── projections 208 └── column4:4 + ((column2:2 + 1) + 2) [as="?column?":5, outer=(2,4)] 209 210 # Multiple constant columns, multiple refs to each, interspersed with other 211 # columns. 212 norm expect=InlineProjectConstants 213 SELECT one+two, x, one*two, y FROM (SELECT x, 1 AS one, y, 2 AS two FROM xy) 214 ---- 215 project 216 ├── columns: "?column?":5!null x:1!null "?column?":6!null y:2 217 ├── key: (1) 218 ├── fd: ()-->(5,6), (1)-->(2) 219 ├── scan xy 220 │ ├── columns: x:1!null y:2 221 │ ├── key: (1) 222 │ └── fd: (1)-->(2) 223 └── projections 224 ├── 3 [as="?column?":5] 225 └── 2 [as="?column?":6] 226 227 # Constant column reference within correlated subquery (which becomes 228 # uncorrelated as a result). 229 norm expect=InlineProjectConstants 230 SELECT EXISTS(SELECT * FROM a WHERE k=one AND i=two) FROM (VALUES (1, 2)) AS t(one, two) 231 ---- 232 values 233 ├── columns: exists:8 234 ├── cardinality: [1 - 1] 235 ├── key: () 236 ├── fd: ()-->(8) 237 └── tuple 238 └── exists 239 └── select 240 ├── columns: k:3!null i:4!null f:5 s:6 j:7 241 ├── cardinality: [0 - 1] 242 ├── key: () 243 ├── fd: ()-->(3-7) 244 ├── scan a 245 │ ├── columns: k:3!null i:4 f:5 s:6 j:7 246 │ ├── key: (3) 247 │ └── fd: (3)-->(4-7) 248 └── filters 249 ├── k:3 = 1 [outer=(3), constraints=(/3: [/1 - /1]; tight), fd=()-->(3)] 250 └── i:4 = 2 [outer=(4), constraints=(/4: [/2 - /2]; tight), fd=()-->(4)] 251 252 # Do not inline constants from Values expression with multiple rows. 253 norm expect-not=InlineProjectConstants 254 SELECT one+two FROM (VALUES (1, 2), (3, 4)) AS t(one, two) 255 ---- 256 project 257 ├── columns: "?column?":3!null 258 ├── cardinality: [2 - 2] 259 ├── values 260 │ ├── columns: column1:1!null column2:2!null 261 │ ├── cardinality: [2 - 2] 262 │ ├── (1, 2) 263 │ └── (3, 4) 264 └── projections 265 └── column1:1 + column2:2 [as="?column?":3, outer=(1,2)] 266 267 # -------------------------------------------------- 268 # InlineSelectConstants 269 # -------------------------------------------------- 270 271 # Inline constants from Project expression. 272 norm expect=InlineSelectConstants 273 SELECT * FROM (SELECT 1 AS one from xy) WHERE one > 0 274 ---- 275 project 276 ├── columns: one:3!null 277 ├── fd: ()-->(3) 278 ├── scan xy 279 └── projections 280 └── 1 [as=one:3] 281 282 # Inline constants from Values expression. 283 norm expect=InlineSelectConstants 284 SELECT * 285 FROM (VALUES ($1:::int, 1, $2:::float, 2)) AS t(one, two, three, four) 286 WHERE one = two OR three = four 287 ---- 288 select 289 ├── columns: one:1 two:2!null three:3 four:4!null 290 ├── cardinality: [0 - 1] 291 ├── has-placeholder 292 ├── key: () 293 ├── fd: ()-->(1-4) 294 ├── values 295 │ ├── columns: column1:1 column2:2!null column3:3 column4:4!null 296 │ ├── cardinality: [1 - 1] 297 │ ├── has-placeholder 298 │ ├── key: () 299 │ ├── fd: ()-->(1-4) 300 │ └── ($1, 1, $2, 2) 301 └── filters 302 └── (column1:1 = 1) OR (column3:3 = 2.0) [outer=(1,3)] 303 304 # Multiple constant columns, multiple refs to each, interspersed with other 305 # columns. 306 norm expect=InlineSelectConstants 307 SELECT * FROM (SELECT x, 1 AS one, y, 2 AS two FROM xy) WHERE x=one AND y=two 308 ---- 309 project 310 ├── columns: x:1!null one:3!null y:2!null two:4!null 311 ├── cardinality: [0 - 1] 312 ├── key: () 313 ├── fd: ()-->(1-4) 314 ├── select 315 │ ├── columns: x:1!null y:2!null 316 │ ├── cardinality: [0 - 1] 317 │ ├── key: () 318 │ ├── fd: ()-->(1,2) 319 │ ├── scan xy 320 │ │ ├── columns: x:1!null y:2 321 │ │ ├── key: (1) 322 │ │ └── fd: (1)-->(2) 323 │ └── filters 324 │ ├── x:1 = 1 [outer=(1), constraints=(/1: [/1 - /1]; tight), fd=()-->(1)] 325 │ └── y:2 = 2 [outer=(2), constraints=(/2: [/2 - /2]; tight), fd=()-->(2)] 326 └── projections 327 ├── 1 [as=one:3] 328 └── 2 [as=two:4] 329 330 # Do not inline constants from Values expression with multiple rows. 331 norm expect-not=InlineSelectConstants 332 SELECT * FROM (VALUES (1, 2), (3, 4)) AS t(one, two) WHERE one=two 333 ---- 334 select 335 ├── columns: one:1!null two:2!null 336 ├── cardinality: [0 - 2] 337 ├── fd: (1)==(2), (2)==(1) 338 ├── values 339 │ ├── columns: column1:1!null column2:2!null 340 │ ├── cardinality: [2 - 2] 341 │ ├── (1, 2) 342 │ └── (3, 4) 343 └── filters 344 └── column1:1 = column2:2 [outer=(1,2), constraints=(/1: (/NULL - ]; /2: (/NULL - ]), fd=(1)==(2), (2)==(1)] 345 346 # -------------------------------------------------- 347 # InlineJoinConstantsLeft + InlineJoinConstantsRight 348 # -------------------------------------------------- 349 norm expect=InlineJoinConstantsLeft 350 SELECT * FROM (SELECT 1 AS one) LEFT JOIN a ON k=one 351 ---- 352 left-join (cross) 353 ├── columns: one:1!null k:2 i:3 f:4 s:5 j:6 354 ├── cardinality: [1 - 1] 355 ├── key: () 356 ├── fd: ()-->(1-6) 357 ├── values 358 │ ├── columns: one:1!null 359 │ ├── cardinality: [1 - 1] 360 │ ├── key: () 361 │ ├── fd: ()-->(1) 362 │ └── (1,) 363 ├── select 364 │ ├── columns: k:2!null i:3 f:4 s:5 j:6 365 │ ├── cardinality: [0 - 1] 366 │ ├── key: () 367 │ ├── fd: ()-->(2-6) 368 │ ├── scan a 369 │ │ ├── columns: k:2!null i:3 f:4 s:5 j:6 370 │ │ ├── key: (2) 371 │ │ └── fd: (2)-->(3-6) 372 │ └── filters 373 │ └── k:2 = 1 [outer=(2), constraints=(/2: [/1 - /1]; tight), fd=()-->(2)] 374 └── filters (true) 375 376 norm expect=InlineJoinConstantsRight 377 SELECT * FROM a FULL JOIN (SELECT 1 AS one) ON k=one 378 ---- 379 full-join (cross) 380 ├── columns: k:1 i:2 f:3 s:4 j:5 one:6 381 ├── cardinality: [1 - ] 382 ├── key: (1) 383 ├── fd: (1)-->(2-6) 384 ├── scan a 385 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 386 │ ├── key: (1) 387 │ └── fd: (1)-->(2-5) 388 ├── values 389 │ ├── columns: one:6!null 390 │ ├── cardinality: [1 - 1] 391 │ ├── key: () 392 │ ├── fd: ()-->(6) 393 │ └── (1,) 394 └── filters 395 └── k:1 = 1 [outer=(1), constraints=(/1: [/1 - /1]; tight), fd=()-->(1)] 396 397 norm expect=(InlineJoinConstantsLeft,InlineJoinConstantsRight) 398 SELECT * FROM (SELECT 1 AS one) INNER JOIN (SELECT 2 AS two) ON one=two 399 ---- 400 values 401 ├── columns: one:1!null two:2!null 402 ├── cardinality: [0 - 0] 403 ├── key: () 404 └── fd: ()-->(1,2) 405 406 # Constant column exists in input, but is not referenced. 407 norm expect-not=(InlineJoinConstantsLeft,InlineJoinConstantsRight) 408 SELECT * FROM a INNER JOIN (SELECT 1 AS one, y FROM xy) ON k=y 409 ---- 410 inner-join (hash) 411 ├── columns: k:1!null i:2 f:3 s:4 j:5 one:8!null y:7!null 412 ├── fd: ()-->(8), (1)-->(2-5), (1)==(7), (7)==(1) 413 ├── scan a 414 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 415 │ ├── key: (1) 416 │ └── fd: (1)-->(2-5) 417 ├── project 418 │ ├── columns: one:8!null y:7 419 │ ├── fd: ()-->(8) 420 │ ├── scan xy 421 │ │ └── columns: y:7 422 │ └── projections 423 │ └── 1 [as=one:8] 424 └── filters 425 └── k:1 = y:7 [outer=(1,7), constraints=(/1: (/NULL - ]; /7: (/NULL - ]), fd=(1)==(7), (7)==(1)] 426 427 # -------------------------------------------------- 428 # PushSelectIntoInlinableProject 429 # -------------------------------------------------- 430 431 # Inline comparison. 432 norm expect=PushSelectIntoInlinableProject 433 SELECT * FROM (SELECT k=1 AS expr FROM a) a WHERE expr IS NULL 434 ---- 435 project 436 ├── columns: expr:6!null 437 ├── select 438 │ ├── columns: k:1!null 439 │ ├── key: (1) 440 │ ├── scan a 441 │ │ ├── columns: k:1!null 442 │ │ └── key: (1) 443 │ └── filters 444 │ └── (k:1 = 1) IS NULL [outer=(1)] 445 └── projections 446 └── k:1 = 1 [as=expr:6, outer=(1)] 447 448 # Inline arithmetic. 449 norm expect=PushSelectIntoInlinableProject 450 SELECT * FROM (SELECT k*2+1 AS expr FROM a) a WHERE expr > 10 451 ---- 452 project 453 ├── columns: expr:6!null 454 ├── select 455 │ ├── columns: k:1!null 456 │ ├── key: (1) 457 │ ├── scan a 458 │ │ ├── columns: k:1!null 459 │ │ └── key: (1) 460 │ └── filters 461 │ └── (k:1 * 2) > 9 [outer=(1)] 462 └── projections 463 └── (k:1 * 2) + 1 [as=expr:6, outer=(1)] 464 465 # Inline boolean logic. 466 norm expect=PushSelectIntoInlinableProject 467 SELECT * FROM (SELECT NOT(k>1 AND k<=5) AS expr FROM a) a WHERE expr 468 ---- 469 project 470 ├── columns: expr:6!null 471 ├── select 472 │ ├── columns: k:1!null 473 │ ├── key: (1) 474 │ ├── scan a 475 │ │ ├── columns: k:1!null 476 │ │ └── key: (1) 477 │ └── filters 478 │ └── (k:1 <= 1) OR (k:1 > 5) [outer=(1), constraints=(/1: (/NULL - /1] [/6 - ]; tight)] 479 └── projections 480 └── (k:1 <= 1) OR (k:1 > 5) [as=expr:6, outer=(1)] 481 482 # Inline constants. 483 norm expect=PushSelectIntoInlinableProject 484 SELECT * FROM (SELECT (f IS NULL OR f != 10.5) AS expr FROM a) a WHERE expr 485 ---- 486 project 487 ├── columns: expr:6 488 ├── select 489 │ ├── columns: f:3 490 │ ├── scan a 491 │ │ └── columns: f:3 492 │ └── filters 493 │ └── (f:3 IS NULL) OR (f:3 != 10.5) [outer=(3), constraints=(/3: [/NULL - /10.499999999999998] [/10.500000000000002 - ]; tight)] 494 └── projections 495 └── (f:3 IS NULL) OR (f:3 != 10.5) [as=expr:6, outer=(3)] 496 497 # Reference the expression to inline multiple times. 498 norm expect=PushSelectIntoInlinableProject 499 SELECT * FROM (SELECT f+1 AS expr FROM a) a WHERE expr=expr 500 ---- 501 project 502 ├── columns: expr:6 503 ├── select 504 │ ├── columns: f:3 505 │ ├── scan a 506 │ │ └── columns: f:3 507 │ └── filters 508 │ └── (f:3 + 1.0) IS DISTINCT FROM CAST(NULL AS FLOAT8) [outer=(3)] 509 └── projections 510 └── f:3 + 1.0 [as=expr:6, outer=(3)] 511 512 # Use outer references in both inlined expression and in referencing expression. 513 norm expect=PushSelectIntoInlinableProject 514 SELECT * FROM a WHERE EXISTS(SELECT * FROM (SELECT (x-i) AS expr FROM xy) WHERE expr > i*i) 515 ---- 516 semi-join (cross) 517 ├── columns: k:1!null i:2 f:3 s:4 j:5 518 ├── key: (1) 519 ├── fd: (1)-->(2-5) 520 ├── scan a 521 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 522 │ ├── key: (1) 523 │ └── fd: (1)-->(2-5) 524 ├── scan xy 525 │ ├── columns: x:6!null 526 │ └── key: (6) 527 └── filters 528 └── (x:6 - i:2) > (i:2 * i:2) [outer=(2,6)] 529 530 exec-ddl 531 CREATE TABLE crdb_internal.zones ( 532 zone_id INT NOT NULL, 533 cli_specifier STRING NULL, 534 config_yaml BYTES NOT NULL, 535 config_protobuf BYTES NOT NULL 536 ) 537 ---- 538 539 # Regression test for #28827. Ensure that inlining is not applied when there 540 # is a correlated subquery in the filter. 541 norm 542 SELECT 543 subq_0.c0 AS c0 544 FROM (SELECT zone_id+1 AS c0, zone_id+2 as c1 FROM crdb_internal.zones) AS subq_0 545 WHERE 546 1 547 >= CASE 548 WHEN subq_0.c1 IS NOT NULL 549 THEN pg_catalog.extract( 550 CAST( 551 CASE 552 WHEN 553 ( 554 EXISTS( 555 SELECT 556 ref_1.config_yaml AS c0, 557 ref_1.config_yaml AS c1, 558 subq_0.c0 AS c2, 559 ref_1.config_yaml AS c3 560 FROM 561 crdb_internal.zones AS ref_1 562 WHERE 563 subq_0.c0 IS NOT NULL 564 LIMIT 565 52 566 ) 567 ) 568 THEN pg_catalog.version() 569 ELSE pg_catalog.version() 570 END 571 AS TEXT 572 ), 573 CAST(pg_catalog.current_date() AS DATE) 574 ) 575 ELSE 1 576 END 577 LIMIT 578 107 579 ---- 580 project 581 ├── columns: c0:6!null 582 ├── cardinality: [0 - 107] 583 ├── stable+volatile, side-effects 584 └── limit 585 ├── columns: c0:6!null c1:7!null 586 ├── cardinality: [0 - 107] 587 ├── stable+volatile, side-effects 588 ├── select 589 │ ├── columns: c0:6!null c1:7!null 590 │ ├── stable+volatile, side-effects 591 │ ├── limit hint: 107.00 592 │ ├── project 593 │ │ ├── columns: c0:6!null c1:7!null 594 │ │ ├── limit hint: 321.00 595 │ │ ├── scan crdb_internal.public.zones 596 │ │ │ ├── columns: crdb_internal.public.zones.zone_id:1!null 597 │ │ │ └── limit hint: 321.00 598 │ │ └── projections 599 │ │ ├── crdb_internal.public.zones.zone_id:1 + 1 [as=c0:6, outer=(1)] 600 │ │ └── crdb_internal.public.zones.zone_id:1 + 2 [as=c1:7, outer=(1)] 601 │ └── filters 602 │ └── le [outer=(6,7), stable+volatile, side-effects, correlated-subquery] 603 │ ├── case 604 │ │ ├── true 605 │ │ ├── when 606 │ │ │ ├── c1:7 IS NOT NULL 607 │ │ │ └── function: extract 608 │ │ │ ├── case 609 │ │ │ │ ├── true 610 │ │ │ │ ├── when 611 │ │ │ │ │ ├── exists 612 │ │ │ │ │ │ └── select 613 │ │ │ │ │ │ ├── columns: ref_1.config_yaml:10!null 614 │ │ │ │ │ │ ├── outer: (6) 615 │ │ │ │ │ │ ├── scan ref_1 616 │ │ │ │ │ │ │ └── columns: ref_1.config_yaml:10!null 617 │ │ │ │ │ │ └── filters 618 │ │ │ │ │ │ └── c0:6 IS NOT NULL [outer=(6), constraints=(/6: (/NULL - ]; tight)] 619 │ │ │ │ │ └── version() 620 │ │ │ │ └── version() 621 │ │ │ └── current_date() 622 │ │ └── 1.0 623 │ └── 1.0 624 └── 107 625 626 # -------------------------------------------------- 627 # InlineProjectInProject 628 # -------------------------------------------------- 629 norm expect=InlineProjectInProject 630 SELECT NOT(expr), i+1 AS r FROM (SELECT k=1 AS expr, i FROM a) 631 ---- 632 project 633 ├── columns: "?column?":7!null r:8 634 ├── scan a 635 │ ├── columns: k:1!null i:2 636 │ ├── key: (1) 637 │ └── fd: (1)-->(2) 638 └── projections 639 ├── k:1 != 1 [as="?column?":7, outer=(1)] 640 └── i:2 + 1 [as=r:8, outer=(2)] 641 642 # Multiple synthesized column references to same inner passthrough column 643 # (should still inline). 644 norm expect=InlineProjectInProject 645 SELECT x+1, x+2, y1+2 FROM (SELECT x, y+1 AS y1 FROM xy) 646 ---- 647 project 648 ├── columns: "?column?":4!null "?column?":5!null "?column?":6 649 ├── scan xy 650 │ ├── columns: x:1!null y:2 651 │ ├── key: (1) 652 │ └── fd: (1)-->(2) 653 └── projections 654 ├── x:1 + 1 [as="?column?":4, outer=(1)] 655 ├── x:1 + 2 [as="?column?":5, outer=(1)] 656 └── (y:2 + 1) + 2 [as="?column?":6, outer=(2)] 657 658 # Synthesized and passthrough references to same inner passthrough column 659 # (should still inline). 660 norm expect=InlineProjectInProject 661 SELECT x+y1 FROM (SELECT x, y+1 AS y1 FROM xy) ORDER BY x 662 ---- 663 project 664 ├── columns: "?column?":4 [hidden: x:1!null] 665 ├── key: (1) 666 ├── fd: (1)-->(4) 667 ├── ordering: +1 668 ├── scan xy 669 │ ├── columns: x:1!null y:2 670 │ ├── key: (1) 671 │ ├── fd: (1)-->(2) 672 │ └── ordering: +1 673 └── projections 674 └── x:1 + (y:2 + 1) [as="?column?":4, outer=(1,2)] 675 676 # Inline multiple expressions. 677 norm expect=InlineProjectInProject 678 SELECT expr+1 AS r, i, expr2 || 'bar' AS s FROM (SELECT k+1 AS expr, s || 'foo' AS expr2, i FROM a) 679 ---- 680 project 681 ├── columns: r:8!null i:2 s:9 682 ├── scan a 683 │ ├── columns: k:1!null i:2 a.s:4 684 │ ├── key: (1) 685 │ └── fd: (1)-->(2,4) 686 └── projections 687 ├── (k:1 + 1) + 1 [as=r:8, outer=(1)] 688 └── (a.s:4 || 'foo') || 'bar' [as=s:9, outer=(4)] 689 690 # Don't inline when there are multiple references. 691 norm expect-not=InlineProjectInProject 692 SELECT expr, expr*2 AS r FROM (SELECT k+1 AS expr FROM a) 693 ---- 694 project 695 ├── columns: expr:6!null r:7!null 696 ├── fd: (6)-->(7) 697 ├── project 698 │ ├── columns: expr:6!null 699 │ ├── scan a 700 │ │ ├── columns: k:1!null 701 │ │ └── key: (1) 702 │ └── projections 703 │ └── k:1 + 1 [as=expr:6, outer=(1)] 704 └── projections 705 └── expr:6 * 2 [as=r:7, outer=(6)] 706 707 # Uncorrelated subquery should not block inlining. 708 norm expect=InlineProjectInProject 709 SELECT EXISTS(SELECT * FROM xy WHERE x=1 OR x=2), expr*2 AS r FROM (SELECT k+1 AS expr FROM a) 710 ---- 711 project 712 ├── columns: exists:9 r:10!null 713 ├── fd: ()-->(9) 714 ├── scan a 715 │ ├── columns: k:1!null 716 │ └── key: (1) 717 └── projections 718 ├── exists [as=exists:9, subquery] 719 │ └── limit 720 │ ├── columns: x:7!null y:8 721 │ ├── cardinality: [0 - 1] 722 │ ├── key: () 723 │ ├── fd: ()-->(7,8) 724 │ ├── select 725 │ │ ├── columns: x:7!null y:8 726 │ │ ├── cardinality: [0 - 2] 727 │ │ ├── key: (7) 728 │ │ ├── fd: (7)-->(8) 729 │ │ ├── limit hint: 1.00 730 │ │ ├── scan xy 731 │ │ │ ├── columns: x:7!null y:8 732 │ │ │ ├── key: (7) 733 │ │ │ ├── fd: (7)-->(8) 734 │ │ │ └── limit hint: 500.00 735 │ │ └── filters 736 │ │ └── (x:7 = 1) OR (x:7 = 2) [outer=(7), constraints=(/7: [/1 - /1] [/2 - /2]; tight)] 737 │ └── 1 738 └── (k:1 + 1) * 2 [as=r:10, outer=(1)] 739 740 # Correlated subquery should be hoisted as usual. 741 norm expect=InlineProjectInProject 742 SELECT EXISTS(SELECT * FROM xy WHERE expr<0) FROM (SELECT k+1 AS expr FROM a) 743 ---- 744 project 745 ├── columns: exists:9!null 746 ├── group-by 747 │ ├── columns: true_agg:11 rownum:13!null 748 │ ├── grouping columns: rownum:13!null 749 │ ├── key: (13) 750 │ ├── fd: (13)-->(11) 751 │ ├── left-join (cross) 752 │ │ ├── columns: expr:6!null true:10 rownum:13!null 753 │ │ ├── fd: (13)-->(6) 754 │ │ ├── ordinality 755 │ │ │ ├── columns: expr:6!null rownum:13!null 756 │ │ │ ├── key: (13) 757 │ │ │ ├── fd: (13)-->(6) 758 │ │ │ └── project 759 │ │ │ ├── columns: expr:6!null 760 │ │ │ ├── scan a 761 │ │ │ │ ├── columns: k:1!null 762 │ │ │ │ └── key: (1) 763 │ │ │ └── projections 764 │ │ │ └── k:1 + 1 [as=expr:6, outer=(1)] 765 │ │ ├── project 766 │ │ │ ├── columns: true:10!null 767 │ │ │ ├── fd: ()-->(10) 768 │ │ │ ├── scan xy 769 │ │ │ └── projections 770 │ │ │ └── true [as=true:10] 771 │ │ └── filters 772 │ │ └── expr:6 < 0 [outer=(6), constraints=(/6: (/NULL - /-1]; tight)] 773 │ └── aggregations 774 │ └── const-not-null-agg [as=true_agg:11, outer=(10)] 775 │ └── true:10 776 └── projections 777 └── true_agg:11 IS NOT NULL [as=exists:9, outer=(11)] 778 779 # After c is replaced with k+2, (k+2) > 2 should be simplified to k > 0. 780 norm 781 SELECT c FROM (SELECT k+2 AS c FROM a) AS t WHERE c > 2; 782 ---- 783 project 784 ├── columns: c:6!null 785 ├── select 786 │ ├── columns: k:1!null 787 │ ├── key: (1) 788 │ ├── scan a 789 │ │ ├── columns: k:1!null 790 │ │ └── key: (1) 791 │ └── filters 792 │ └── k:1 > 0 [outer=(1), constraints=(/1: [/1 - ]; tight)] 793 └── projections 794 └── k:1 + 2 [as=c:6, outer=(1)]