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