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