github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/memo/testdata/logprops/join (about) 1 exec-ddl 2 CREATE TABLE xysd (x INT PRIMARY KEY, y INT, s STRING, d DECIMAL NOT NULL, UNIQUE (s DESC, d)) 3 ---- 4 5 exec-ddl 6 CREATE TABLE uv (u INT, v INT NOT NULL) 7 ---- 8 9 exec-ddl 10 CREATE TABLE mn (m INT PRIMARY KEY, n INT, UNIQUE (n)) 11 ---- 12 13 exec-ddl 14 CREATE TABLE fk ( 15 k INT PRIMARY KEY, 16 v INT, 17 r1 INT NOT NULL REFERENCES xysd(x), 18 r2 INT REFERENCES xysd(x) 19 ) 20 ---- 21 22 exec-ddl 23 CREATE TABLE abc (a INT, b INT, c INT, PRIMARY KEY (a, b, c)) 24 ---- 25 26 exec-ddl 27 CREATE TABLE ref ( 28 r1 INT NOT NULL, 29 r2 INT, 30 r3 INT NOT NULL, 31 FOREIGN KEY (r1, r2, r3) REFERENCES abc(a, b, c) 32 ) 33 ---- 34 35 # Inner-join. 36 build 37 SELECT *, rowid FROM xysd INNER JOIN uv ON x=u 38 ---- 39 inner-join (hash) 40 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) u:5(int!null) v:6(int!null) rowid:7(int!null) 41 ├── key: (7) 42 ├── fd: (1)-->(2-4), (3,4)~~>(1,2), (7)-->(5,6), (1)==(5), (5)==(1) 43 ├── prune: (2-4,6,7) 44 ├── interesting orderings: (+1) (-3,+4,+1) (+7) 45 ├── multiplicity: left-rows(zero-or-more), right-rows(one-or-zero) 46 ├── scan xysd 47 │ ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 48 │ ├── key: (1) 49 │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 50 │ ├── prune: (1-4) 51 │ └── interesting orderings: (+1) (-3,+4,+1) 52 ├── scan uv 53 │ ├── columns: u:5(int) v:6(int!null) rowid:7(int!null) 54 │ ├── key: (7) 55 │ ├── fd: (7)-->(5,6) 56 │ ├── prune: (5-7) 57 │ └── interesting orderings: (+7) 58 └── filters 59 └── eq [type=bool, outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)] 60 ├── variable: x:1 [type=int] 61 └── variable: u:5 [type=int] 62 63 # Inner-join-apply. 64 opt 65 SELECT (SELECT (VALUES (x), (y))) FROM xysd 66 ---- 67 project 68 ├── columns: column1:7(int) 69 ├── prune: (7) 70 ├── inner-join-apply 71 │ ├── columns: x:1(int!null) y:2(int) column1:5(int) column1:6(int) 72 │ ├── key: (1) 73 │ ├── fd: (1)-->(2,5,6) 74 │ ├── prune: (6) 75 │ ├── interesting orderings: (+1) 76 │ ├── scan xysd 77 │ │ ├── columns: x:1(int!null) y:2(int) 78 │ │ ├── key: (1) 79 │ │ ├── fd: (1)-->(2) 80 │ │ ├── prune: (1,2) 81 │ │ └── interesting orderings: (+1) 82 │ ├── inner-join-apply 83 │ │ ├── columns: column1:5(int) column1:6(int) 84 │ │ ├── outer: (1,2) 85 │ │ ├── cardinality: [1 - 1] 86 │ │ ├── key: () 87 │ │ ├── fd: ()-->(5,6) 88 │ │ ├── prune: (6) 89 │ │ ├── max1-row 90 │ │ │ ├── columns: column1:5(int) 91 │ │ │ ├── error: "more than one row returned by a subquery used as an expression" 92 │ │ │ ├── outer: (1,2) 93 │ │ │ ├── cardinality: [1 - 1] 94 │ │ │ ├── key: () 95 │ │ │ ├── fd: ()-->(5) 96 │ │ │ └── values 97 │ │ │ ├── columns: column1:5(int) 98 │ │ │ ├── outer: (1,2) 99 │ │ │ ├── cardinality: [2 - 2] 100 │ │ │ ├── prune: (5) 101 │ │ │ ├── tuple [type=tuple{int}] 102 │ │ │ │ └── variable: x:1 [type=int] 103 │ │ │ └── tuple [type=tuple{int}] 104 │ │ │ └── variable: y:2 [type=int] 105 │ │ ├── values 106 │ │ │ ├── columns: column1:6(int) 107 │ │ │ ├── outer: (5) 108 │ │ │ ├── cardinality: [1 - 1] 109 │ │ │ ├── key: () 110 │ │ │ ├── fd: ()-->(6) 111 │ │ │ ├── prune: (6) 112 │ │ │ └── tuple [type=tuple{int}] 113 │ │ │ └── variable: column1:5 [type=int] 114 │ │ └── filters (true) 115 │ └── filters (true) 116 └── projections 117 └── variable: column1:6 [as=column1:7, type=int, outer=(6)] 118 119 # Inner-join-apply nested in inner-join-apply with outer column references to 120 # each parent. 121 opt 122 SELECT * FROM xysd WHERE (SELECT v FROM uv WHERE (SELECT n FROM mn WHERE n=v)=x)=x 123 ---- 124 project 125 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 126 ├── key: (1) 127 ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 128 ├── prune: (1-4) 129 └── select 130 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) v:6(int!null) 131 ├── key: (1) 132 ├── fd: (1)-->(2-4), (3,4)~~>(1,2), (1)==(6), (6)==(1) 133 ├── prune: (2-4) 134 ├── ensure-distinct-on 135 │ ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) v:6(int) 136 │ ├── grouping columns: x:1(int!null) 137 │ ├── error: "more than one row returned by a subquery used as an expression" 138 │ ├── key: (1) 139 │ ├── fd: (1)-->(2-4,6), (3,4)~~>(1,2) 140 │ ├── prune: (2-4,6) 141 │ ├── left-join (hash) 142 │ │ ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) v:6(int) n:9(int) 143 │ │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2), (6)==(9), (9)==(6) 144 │ │ ├── prune: (2-4) 145 │ │ ├── reject-nulls: (6,9) 146 │ │ ├── interesting orderings: (+1) (-3,+4,+1) (+9) 147 │ │ ├── multiplicity: left-rows(one-or-more), right-rows(one-or-zero) 148 │ │ ├── scan xysd 149 │ │ │ ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 150 │ │ │ ├── key: (1) 151 │ │ │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 152 │ │ │ ├── prune: (1-4) 153 │ │ │ └── interesting orderings: (+1) (-3,+4,+1) 154 │ │ ├── inner-join (hash) 155 │ │ │ ├── columns: v:6(int!null) n:9(int!null) 156 │ │ │ ├── fd: (6)==(9), (9)==(6) 157 │ │ │ ├── interesting orderings: (+9) 158 │ │ │ ├── multiplicity: left-rows(one-or-zero), right-rows(zero-or-more) 159 │ │ │ ├── scan uv 160 │ │ │ │ ├── columns: v:6(int!null) 161 │ │ │ │ └── prune: (6) 162 │ │ │ ├── scan mn 163 │ │ │ │ ├── columns: n:9(int) 164 │ │ │ │ ├── lax-key: (9) 165 │ │ │ │ ├── prune: (9) 166 │ │ │ │ └── interesting orderings: (+9) 167 │ │ │ └── filters 168 │ │ │ └── eq [type=bool, outer=(6,9), constraints=(/6: (/NULL - ]; /9: (/NULL - ]), fd=(6)==(9), (9)==(6)] 169 │ │ │ ├── variable: n:9 [type=int] 170 │ │ │ └── variable: v:6 [type=int] 171 │ │ └── filters 172 │ │ └── eq [type=bool, outer=(1,9), constraints=(/1: (/NULL - ]; /9: (/NULL - ]), fd=(1)==(9), (9)==(1)] 173 │ │ ├── variable: x:1 [type=int] 174 │ │ └── variable: n:9 [type=int] 175 │ └── aggregations 176 │ ├── const-agg [as=y:2, type=int, outer=(2)] 177 │ │ └── variable: y:2 [type=int] 178 │ ├── const-agg [as=s:3, type=string, outer=(3)] 179 │ │ └── variable: s:3 [type=string] 180 │ ├── const-agg [as=d:4, type=decimal, outer=(4)] 181 │ │ └── variable: d:4 [type=decimal] 182 │ └── const-agg [as=v:6, type=int, outer=(6)] 183 │ └── variable: v:6 [type=int] 184 └── filters 185 └── eq [type=bool, outer=(1,6), constraints=(/1: (/NULL - ]; /6: (/NULL - ]), fd=(1)==(6), (6)==(1)] 186 ├── variable: x:1 [type=int] 187 └── variable: v:6 [type=int] 188 189 # Inner-join nested in inner-join-apply with outer column reference to top-level 190 # inner-join-apply. 191 opt 192 SELECT * FROM xysd WHERE (SELECT v FROM uv WHERE (SELECT m FROM mn WHERE m=y)=x)=x 193 ---- 194 project 195 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 196 ├── key: (1) 197 ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 198 ├── prune: (1-4) 199 └── select 200 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) v:6(int!null) 201 ├── key: (1) 202 ├── fd: (1)-->(2-4), (3,4)~~>(1,2), (1)==(6), (6)==(1) 203 ├── prune: (2-4) 204 ├── ensure-distinct-on 205 │ ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) v:6(int) 206 │ ├── grouping columns: x:1(int!null) 207 │ ├── error: "more than one row returned by a subquery used as an expression" 208 │ ├── key: (1) 209 │ ├── fd: (1)-->(2-4,6), (3,4)~~>(1,2) 210 │ ├── prune: (2-4,6) 211 │ ├── right-join (hash) 212 │ │ ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) v:6(int) m:8(int) 213 │ │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 214 │ │ ├── prune: (3,4,6) 215 │ │ ├── reject-nulls: (6,8) 216 │ │ ├── interesting orderings: (+1) (-3,+4,+1) (+8) 217 │ │ ├── inner-join (cross) 218 │ │ │ ├── columns: v:6(int!null) m:8(int!null) 219 │ │ │ ├── prune: (6,8) 220 │ │ │ ├── interesting orderings: (+8) 221 │ │ │ ├── scan uv 222 │ │ │ │ ├── columns: v:6(int!null) 223 │ │ │ │ └── prune: (6) 224 │ │ │ ├── scan mn 225 │ │ │ │ ├── columns: m:8(int!null) 226 │ │ │ │ ├── key: (8) 227 │ │ │ │ ├── prune: (8) 228 │ │ │ │ └── interesting orderings: (+8) 229 │ │ │ └── filters (true) 230 │ │ ├── scan xysd 231 │ │ │ ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 232 │ │ │ ├── key: (1) 233 │ │ │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 234 │ │ │ ├── prune: (1-4) 235 │ │ │ └── interesting orderings: (+1) (-3,+4,+1) 236 │ │ └── filters 237 │ │ ├── eq [type=bool, outer=(1,2), constraints=(/1: (/NULL - ]; /2: (/NULL - ]), fd=(1)==(2), (2)==(1)] 238 │ │ │ ├── variable: x:1 [type=int] 239 │ │ │ └── variable: y:2 [type=int] 240 │ │ └── eq [type=bool, outer=(1,8), constraints=(/1: (/NULL - ]; /8: (/NULL - ]), fd=(1)==(8), (8)==(1)] 241 │ │ ├── variable: x:1 [type=int] 242 │ │ └── variable: m:8 [type=int] 243 │ └── aggregations 244 │ ├── const-agg [as=y:2, type=int, outer=(2)] 245 │ │ └── variable: y:2 [type=int] 246 │ ├── const-agg [as=s:3, type=string, outer=(3)] 247 │ │ └── variable: s:3 [type=string] 248 │ ├── const-agg [as=d:4, type=decimal, outer=(4)] 249 │ │ └── variable: d:4 [type=decimal] 250 │ └── const-agg [as=v:6, type=int, outer=(6)] 251 │ └── variable: v:6 [type=int] 252 └── filters 253 └── eq [type=bool, outer=(1,6), constraints=(/1: (/NULL - ]; /6: (/NULL - ]), fd=(1)==(6), (6)==(1)] 254 ├── variable: x:1 [type=int] 255 └── variable: v:6 [type=int] 256 257 # Left-join. 258 build 259 SELECT *, rowid FROM xysd LEFT JOIN uv ON x=u 260 ---- 261 left-join (hash) 262 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) u:5(int) v:6(int) rowid:7(int) 263 ├── key: (1,7) 264 ├── fd: (1)-->(2-4), (3,4)~~>(1,2), (7)-->(5,6) 265 ├── prune: (2-4,6,7) 266 ├── reject-nulls: (5-7) 267 ├── interesting orderings: (+1) (-3,+4,+1) (+7) 268 ├── multiplicity: left-rows(one-or-more), right-rows(one-or-zero) 269 ├── scan xysd 270 │ ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 271 │ ├── key: (1) 272 │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 273 │ ├── prune: (1-4) 274 │ └── interesting orderings: (+1) (-3,+4,+1) 275 ├── scan uv 276 │ ├── columns: u:5(int) v:6(int!null) rowid:7(int!null) 277 │ ├── key: (7) 278 │ ├── fd: (7)-->(5,6) 279 │ ├── prune: (5-7) 280 │ └── interesting orderings: (+7) 281 └── filters 282 └── eq [type=bool, outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)] 283 ├── variable: x:1 [type=int] 284 └── variable: u:5 [type=int] 285 286 # Left-join-apply. 287 opt 288 SELECT * FROM xysd WHERE (SELECT u FROM uv WHERE u=x) IS NULL 289 ---- 290 project 291 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 292 ├── key: (1) 293 ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 294 ├── prune: (1-4) 295 └── select 296 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) u:5(int) 297 ├── key: (1) 298 ├── fd: ()-->(5), (1)-->(2-4), (3,4)~~>(1,2) 299 ├── prune: (2-4) 300 ├── ensure-distinct-on 301 │ ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) u:5(int) 302 │ ├── grouping columns: x:1(int!null) 303 │ ├── error: "more than one row returned by a subquery used as an expression" 304 │ ├── key: (1) 305 │ ├── fd: (1)-->(2-5), (3,4)~~>(1,2) 306 │ ├── prune: (2-5) 307 │ ├── left-join (hash) 308 │ │ ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) u:5(int) 309 │ │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 310 │ │ ├── prune: (2-4) 311 │ │ ├── reject-nulls: (5) 312 │ │ ├── interesting orderings: (+1) (-3,+4,+1) 313 │ │ ├── multiplicity: left-rows(one-or-more), right-rows(one-or-zero) 314 │ │ ├── scan xysd 315 │ │ │ ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 316 │ │ │ ├── key: (1) 317 │ │ │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 318 │ │ │ ├── prune: (1-4) 319 │ │ │ └── interesting orderings: (+1) (-3,+4,+1) 320 │ │ ├── scan uv 321 │ │ │ ├── columns: u:5(int) 322 │ │ │ └── prune: (5) 323 │ │ └── filters 324 │ │ └── eq [type=bool, outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)] 325 │ │ ├── variable: u:5 [type=int] 326 │ │ └── variable: x:1 [type=int] 327 │ └── aggregations 328 │ ├── const-agg [as=y:2, type=int, outer=(2)] 329 │ │ └── variable: y:2 [type=int] 330 │ ├── const-agg [as=s:3, type=string, outer=(3)] 331 │ │ └── variable: s:3 [type=string] 332 │ ├── const-agg [as=d:4, type=decimal, outer=(4)] 333 │ │ └── variable: d:4 [type=decimal] 334 │ └── const-agg [as=u:5, type=int, outer=(5)] 335 │ └── variable: u:5 [type=int] 336 └── filters 337 └── is [type=bool, outer=(5), constraints=(/5: [/NULL - /NULL]; tight), fd=()-->(5)] 338 ├── variable: u:5 [type=int] 339 └── null [type=unknown] 340 341 # Right-join. 342 build 343 SELECT *, rowid FROM xysd RIGHT JOIN uv ON x=u 344 ---- 345 right-join (hash) 346 ├── columns: x:1(int) y:2(int) s:3(string) d:4(decimal) u:5(int) v:6(int!null) rowid:7(int!null) 347 ├── key: (7) 348 ├── fd: (1)-->(2-4), (3,4)~~>(1,2), (7)-->(1-6) 349 ├── prune: (2-4,6,7) 350 ├── reject-nulls: (1-4) 351 ├── interesting orderings: (+1) (-3,+4,+1) (+7) 352 ├── scan xysd 353 │ ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 354 │ ├── key: (1) 355 │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 356 │ ├── prune: (1-4) 357 │ └── interesting orderings: (+1) (-3,+4,+1) 358 ├── scan uv 359 │ ├── columns: u:5(int) v:6(int!null) rowid:7(int!null) 360 │ ├── key: (7) 361 │ ├── fd: (7)-->(5,6) 362 │ ├── prune: (5-7) 363 │ └── interesting orderings: (+7) 364 └── filters 365 └── eq [type=bool, outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)] 366 ├── variable: x:1 [type=int] 367 └── variable: u:5 [type=int] 368 369 # Full-join. 370 build 371 SELECT *, rowid FROM xysd FULL JOIN uv ON x=u 372 ---- 373 full-join (hash) 374 ├── columns: x:1(int) y:2(int) s:3(string) d:4(decimal) u:5(int) v:6(int) rowid:7(int) 375 ├── key: (1,7) 376 ├── fd: (1)-->(2-4), (3,4)~~>(1,2), (7)-->(5,6) 377 ├── prune: (2-4,6,7) 378 ├── reject-nulls: (1-7) 379 ├── interesting orderings: (+1) (-3,+4,+1) (+7) 380 ├── multiplicity: left-rows(one-or-more), right-rows(exactly-one) 381 ├── scan xysd 382 │ ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 383 │ ├── key: (1) 384 │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 385 │ ├── prune: (1-4) 386 │ └── interesting orderings: (+1) (-3,+4,+1) 387 ├── scan uv 388 │ ├── columns: u:5(int) v:6(int!null) rowid:7(int!null) 389 │ ├── key: (7) 390 │ ├── fd: (7)-->(5,6) 391 │ ├── prune: (5-7) 392 │ └── interesting orderings: (+7) 393 └── filters 394 └── eq [type=bool, outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)] 395 ├── variable: x:1 [type=int] 396 └── variable: u:5 [type=int] 397 398 # Semi-join. 399 opt 400 SELECT * FROM xysd WHERE EXISTS(SELECT * FROM uv WHERE x=u) 401 ---- 402 project 403 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 404 ├── key: (1) 405 ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 406 ├── prune: (2-4) 407 └── inner-join (lookup xysd) 408 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) u:5(int!null) 409 ├── key columns: [5] = [1] 410 ├── lookup columns are key 411 ├── key: (5) 412 ├── fd: (1)-->(2-4), (3,4)~~>(1,2), (1)==(5), (5)==(1) 413 ├── distinct-on 414 │ ├── columns: u:5(int) 415 │ ├── grouping columns: u:5(int) 416 │ ├── key: (5) 417 │ └── scan uv 418 │ ├── columns: u:5(int) 419 │ └── prune: (5) 420 └── filters (true) 421 422 # Semi-join-apply. 423 opt 424 SELECT * FROM xysd WHERE EXISTS(SELECT * FROM uv WHERE v=x OFFSET 1) 425 ---- 426 semi-join-apply 427 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 428 ├── key: (1) 429 ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 430 ├── prune: (2-4) 431 ├── interesting orderings: (+1) (-3,+4,+1) 432 ├── scan xysd 433 │ ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 434 │ ├── key: (1) 435 │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 436 │ ├── prune: (1-4) 437 │ └── interesting orderings: (+1) (-3,+4,+1) 438 ├── offset 439 │ ├── columns: v:6(int!null) 440 │ ├── outer: (1) 441 │ ├── fd: ()-->(6) 442 │ ├── select 443 │ │ ├── columns: v:6(int!null) 444 │ │ ├── outer: (1) 445 │ │ ├── fd: ()-->(6) 446 │ │ ├── scan uv 447 │ │ │ ├── columns: v:6(int!null) 448 │ │ │ └── prune: (6) 449 │ │ └── filters 450 │ │ └── eq [type=bool, outer=(1,6), constraints=(/1: (/NULL - ]; /6: (/NULL - ]), fd=(1)==(6), (6)==(1)] 451 │ │ ├── variable: v:6 [type=int] 452 │ │ └── variable: x:1 [type=int] 453 │ └── const: 1 [type=int] 454 └── filters (true) 455 456 # Semi-join nested in semi-join with outer column reference to top-level join. 457 opt 458 SELECT * FROM xysd WHERE EXISTS(SELECT * FROM uv WHERE EXISTS(SELECT * FROM mn WHERE x=m AND x=v)) 459 ---- 460 semi-join-apply 461 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 462 ├── key: (1) 463 ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 464 ├── prune: (2-4) 465 ├── interesting orderings: (+1) (-3,+4,+1) 466 ├── scan xysd 467 │ ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 468 │ ├── key: (1) 469 │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 470 │ ├── prune: (1-4) 471 │ └── interesting orderings: (+1) (-3,+4,+1) 472 ├── semi-join (cross) 473 │ ├── columns: v:6(int!null) 474 │ ├── outer: (1) 475 │ ├── fd: ()-->(6) 476 │ ├── scan uv 477 │ │ ├── columns: v:6(int!null) 478 │ │ └── prune: (6) 479 │ ├── scan mn 480 │ │ ├── columns: m:8(int!null) 481 │ │ ├── key: (8) 482 │ │ ├── prune: (8) 483 │ │ └── interesting orderings: (+8) 484 │ └── filters 485 │ ├── eq [type=bool, outer=(1,8), constraints=(/1: (/NULL - ]; /8: (/NULL - ]), fd=(1)==(8), (8)==(1)] 486 │ │ ├── variable: x:1 [type=int] 487 │ │ └── variable: m:8 [type=int] 488 │ └── eq [type=bool, outer=(1,6), constraints=(/1: (/NULL - ]; /6: (/NULL - ]), fd=(1)==(6), (6)==(1)] 489 │ ├── variable: x:1 [type=int] 490 │ └── variable: v:6 [type=int] 491 └── filters (true) 492 493 # Anti-join. 494 opt 495 SELECT * FROM xysd WHERE NOT EXISTS(SELECT * FROM uv WHERE x=u) 496 ---- 497 anti-join (hash) 498 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 499 ├── key: (1) 500 ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 501 ├── prune: (2-4) 502 ├── interesting orderings: (+1) (-3,+4,+1) 503 ├── scan xysd 504 │ ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 505 │ ├── key: (1) 506 │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 507 │ ├── prune: (1-4) 508 │ └── interesting orderings: (+1) (-3,+4,+1) 509 ├── scan uv 510 │ ├── columns: u:5(int) 511 │ └── prune: (5) 512 └── filters 513 └── eq [type=bool, outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)] 514 ├── variable: x:1 [type=int] 515 └── variable: u:5 [type=int] 516 517 # Anti-join-apply. 518 opt 519 SELECT * FROM xysd WHERE NOT EXISTS(SELECT * FROM uv WHERE v=x OFFSET 1) 520 ---- 521 anti-join-apply 522 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 523 ├── key: (1) 524 ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 525 ├── prune: (2-4) 526 ├── interesting orderings: (+1) (-3,+4,+1) 527 ├── scan xysd 528 │ ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 529 │ ├── key: (1) 530 │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 531 │ ├── prune: (1-4) 532 │ └── interesting orderings: (+1) (-3,+4,+1) 533 ├── offset 534 │ ├── columns: v:6(int!null) 535 │ ├── outer: (1) 536 │ ├── fd: ()-->(6) 537 │ ├── select 538 │ │ ├── columns: v:6(int!null) 539 │ │ ├── outer: (1) 540 │ │ ├── fd: ()-->(6) 541 │ │ ├── scan uv 542 │ │ │ ├── columns: v:6(int!null) 543 │ │ │ └── prune: (6) 544 │ │ └── filters 545 │ │ └── eq [type=bool, outer=(1,6), constraints=(/1: (/NULL - ]; /6: (/NULL - ]), fd=(1)==(6), (6)==(1)] 546 │ │ ├── variable: v:6 [type=int] 547 │ │ └── variable: x:1 [type=int] 548 │ └── const: 1 [type=int] 549 └── filters (true) 550 551 # Cross-join. 552 build 553 SELECT * FROM xysd, uv 554 ---- 555 project 556 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) u:5(int) v:6(int!null) 557 ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 558 ├── prune: (1-6) 559 ├── interesting orderings: (+1) (-3,+4,+1) 560 └── inner-join (cross) 561 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) u:5(int) v:6(int!null) rowid:7(int!null) 562 ├── key: (1,7) 563 ├── fd: (1)-->(2-4), (3,4)~~>(1,2), (7)-->(5,6) 564 ├── prune: (1-7) 565 ├── interesting orderings: (+1) (-3,+4,+1) (+7) 566 ├── scan xysd 567 │ ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 568 │ ├── key: (1) 569 │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 570 │ ├── prune: (1-4) 571 │ └── interesting orderings: (+1) (-3,+4,+1) 572 ├── scan uv 573 │ ├── columns: u:5(int) v:6(int!null) rowid:7(int!null) 574 │ ├── key: (7) 575 │ ├── fd: (7)-->(5,6) 576 │ ├── prune: (5-7) 577 │ └── interesting orderings: (+7) 578 └── filters (true) 579 580 # Self-join. 581 build 582 SELECT * FROM xysd, xysd AS xysd 583 ---- 584 inner-join (cross) 585 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) x:5(int!null) y:6(int) s:7(string) d:8(decimal!null) 586 ├── key: (1,5) 587 ├── fd: (1)-->(2-4), (3,4)~~>(1,2), (5)-->(6-8), (7,8)~~>(5,6) 588 ├── prune: (1-8) 589 ├── interesting orderings: (+1) (-3,+4,+1) (+5) (-7,+8,+5) 590 ├── scan xysd 591 │ ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 592 │ ├── key: (1) 593 │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 594 │ ├── prune: (1-4) 595 │ └── interesting orderings: (+1) (-3,+4,+1) 596 ├── scan xysd 597 │ ├── columns: x:5(int!null) y:6(int) s:7(string) d:8(decimal!null) 598 │ ├── key: (5) 599 │ ├── fd: (5)-->(6-8), (7,8)~~>(5,6) 600 │ ├── prune: (5-8) 601 │ └── interesting orderings: (+5) (-7,+8,+5) 602 └── filters (true) 603 604 # Propagate outer columns. 605 build 606 SELECT * FROM xysd WHERE EXISTS(SELECT * FROM (SELECT x) INNER JOIN (SELECT y) ON x::string = s) 607 ---- 608 select 609 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 610 ├── key: (1) 611 ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 612 ├── prune: (4) 613 ├── interesting orderings: (+1) (-3,+4,+1) 614 ├── scan xysd 615 │ ├── columns: xysd.x:1(int!null) xysd.y:2(int) s:3(string) d:4(decimal!null) 616 │ ├── key: (1) 617 │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 618 │ ├── prune: (1-4) 619 │ └── interesting orderings: (+1) (-3,+4,+1) 620 └── filters 621 └── exists [type=bool, outer=(1-3), correlated-subquery] 622 └── inner-join (cross) 623 ├── columns: x:5(int) y:6(int) 624 ├── outer: (1-3) 625 ├── cardinality: [0 - 1] 626 ├── key: () 627 ├── fd: ()-->(5,6) 628 ├── prune: (6) 629 ├── multiplicity: left-rows(one-or-zero), right-rows(one-or-zero) 630 ├── project 631 │ ├── columns: x:5(int) 632 │ ├── outer: (1) 633 │ ├── cardinality: [1 - 1] 634 │ ├── key: () 635 │ ├── fd: ()-->(5) 636 │ ├── prune: (5) 637 │ ├── values 638 │ │ ├── cardinality: [1 - 1] 639 │ │ ├── key: () 640 │ │ └── tuple [type=tuple] 641 │ └── projections 642 │ └── variable: xysd.x:1 [as=x:5, type=int, outer=(1)] 643 ├── project 644 │ ├── columns: y:6(int) 645 │ ├── outer: (2) 646 │ ├── cardinality: [1 - 1] 647 │ ├── key: () 648 │ ├── fd: ()-->(6) 649 │ ├── prune: (6) 650 │ ├── values 651 │ │ ├── cardinality: [1 - 1] 652 │ │ ├── key: () 653 │ │ └── tuple [type=tuple] 654 │ └── projections 655 │ └── variable: xysd.y:2 [as=y:6, type=int, outer=(2)] 656 └── filters 657 └── eq [type=bool, outer=(3,5)] 658 ├── cast: STRING [type=string] 659 │ └── variable: x:5 [type=int] 660 └── variable: s:3 [type=string] 661 662 # Calculate semi-join cardinality when left side has non-zero cardinality. 663 opt 664 SELECT * FROM (SELECT count(*) cnt FROM xysd) WHERE EXISTS(SELECT * FROM uv WHERE cnt=1) 665 ---- 666 project 667 ├── columns: cnt:5(int!null) 668 ├── cardinality: [0 - 1] 669 ├── key: () 670 ├── fd: ()-->(5) 671 └── inner-join (cross) 672 ├── columns: count_rows:5(int!null) 673 ├── cardinality: [0 - 1] 674 ├── key: () 675 ├── fd: ()-->(5) 676 ├── multiplicity: left-rows(one-or-zero), right-rows(one-or-zero) 677 ├── select 678 │ ├── columns: count_rows:5(int!null) 679 │ ├── cardinality: [0 - 1] 680 │ ├── key: () 681 │ ├── fd: ()-->(5) 682 │ ├── scalar-group-by 683 │ │ ├── columns: count_rows:5(int!null) 684 │ │ ├── cardinality: [1 - 1] 685 │ │ ├── key: () 686 │ │ ├── fd: ()-->(5) 687 │ │ ├── prune: (5) 688 │ │ ├── scan xysd@secondary 689 │ │ └── aggregations 690 │ │ └── count-rows [as=count_rows:5, type=int] 691 │ └── filters 692 │ └── eq [type=bool, outer=(5), constraints=(/5: [/1 - /1]; tight), fd=()-->(5)] 693 │ ├── variable: count_rows:5 [type=int] 694 │ └── const: 1 [type=int] 695 ├── scan uv 696 │ ├── limit: 1 697 │ └── key: () 698 └── filters (true) 699 700 # Calculate semi-join-apply cardinality. 701 expr 702 (SemiJoinApply 703 (FakeRel 704 [ 705 (OutputCols [ (NewColumn "a" "int") ]) 706 (Cardinality "0-10") 707 ] 708 ) 709 (FakeRel 710 [ 711 (OutputCols [ (NewColumn "a" "int") ]) 712 ] 713 ) 714 [ ] 715 [ ] 716 ) 717 ---- 718 semi-join-apply 719 ├── columns: a:1(int) 720 ├── cardinality: [0 - 10] 721 ├── fake-rel 722 │ ├── columns: a:1(int) 723 │ └── cardinality: [0 - 10] 724 ├── fake-rel 725 │ ├── columns: a:2(int) 726 │ └── cardinality: [0 - 0] 727 └── filters (true) 728 729 # Calculate anti-join cardinality when left side has non-zero cardinality. 730 opt 731 SELECT * FROM (SELECT * FROM (VALUES (1), (2))) WHERE NOT EXISTS(SELECT * FROM uv WHERE u=column1) 732 ---- 733 anti-join (hash) 734 ├── columns: column1:1(int!null) 735 ├── cardinality: [0 - 2] 736 ├── values 737 │ ├── columns: column1:1(int!null) 738 │ ├── cardinality: [2 - 2] 739 │ ├── prune: (1) 740 │ ├── tuple [type=tuple{int}] 741 │ │ └── const: 1 [type=int] 742 │ └── tuple [type=tuple{int}] 743 │ └── const: 2 [type=int] 744 ├── scan uv 745 │ ├── columns: u:2(int) 746 │ └── prune: (2) 747 └── filters 748 └── eq [type=bool, outer=(1,2), constraints=(/1: (/NULL - ]; /2: (/NULL - ]), fd=(1)==(2), (2)==(1)] 749 ├── variable: u:2 [type=int] 750 └── variable: column1:1 [type=int] 751 752 # Calculate anti-join-apply cardinality. 753 expr 754 (AntiJoinApply 755 (FakeRel 756 [ 757 (OutputCols [ (NewColumn "a" "int") ]) 758 (Cardinality "0-10") 759 ] 760 ) 761 (FakeRel 762 [ 763 (OutputCols [ (NewColumn "a" "int") ]) 764 ] 765 ) 766 [ ] 767 [ ] 768 ) 769 ---- 770 anti-join-apply 771 ├── columns: a:1(int) 772 ├── cardinality: [0 - 10] 773 ├── fake-rel 774 │ ├── columns: a:1(int) 775 │ └── cardinality: [0 - 10] 776 ├── fake-rel 777 │ ├── columns: a:2(int) 778 │ └── cardinality: [0 - 0] 779 └── filters (true) 780 781 # Calculate inner-join cardinality. 782 build 783 SELECT * FROM (VALUES (1), (2)) INNER JOIN (SELECT * FROM uv LIMIT 2) ON True 784 ---- 785 inner-join (cross) 786 ├── columns: column1:1(int!null) u:2(int) v:3(int!null) 787 ├── cardinality: [0 - 4] 788 ├── prune: (1-3) 789 ├── values 790 │ ├── columns: column1:1(int!null) 791 │ ├── cardinality: [2 - 2] 792 │ ├── prune: (1) 793 │ ├── tuple [type=tuple{int}] 794 │ │ └── const: 1 [type=int] 795 │ └── tuple [type=tuple{int}] 796 │ └── const: 2 [type=int] 797 ├── limit 798 │ ├── columns: u:2(int) v:3(int!null) 799 │ ├── cardinality: [0 - 2] 800 │ ├── prune: (2,3) 801 │ ├── project 802 │ │ ├── columns: u:2(int) v:3(int!null) 803 │ │ ├── limit hint: 2.00 804 │ │ ├── prune: (2,3) 805 │ │ └── scan uv 806 │ │ ├── columns: u:2(int) v:3(int!null) rowid:4(int!null) 807 │ │ ├── key: (4) 808 │ │ ├── fd: (4)-->(2,3) 809 │ │ ├── limit hint: 2.00 810 │ │ ├── prune: (2-4) 811 │ │ └── interesting orderings: (+4) 812 │ └── const: 2 [type=int] 813 └── filters 814 └── true [type=bool] 815 816 # Calculate left-join cardinality. 817 build 818 SELECT * FROM (VALUES (1), (2), (3)) LEFT JOIN (SELECT * FROM uv LIMIT 2) ON True 819 ---- 820 left-join (cross) 821 ├── columns: column1:1(int!null) u:2(int) v:3(int) 822 ├── cardinality: [3 - 6] 823 ├── prune: (1-3) 824 ├── reject-nulls: (2,3) 825 ├── multiplicity: left-rows(one-or-more), right-rows(zero-or-more) 826 ├── values 827 │ ├── columns: column1:1(int!null) 828 │ ├── cardinality: [3 - 3] 829 │ ├── prune: (1) 830 │ ├── tuple [type=tuple{int}] 831 │ │ └── const: 1 [type=int] 832 │ ├── tuple [type=tuple{int}] 833 │ │ └── const: 2 [type=int] 834 │ └── tuple [type=tuple{int}] 835 │ └── const: 3 [type=int] 836 ├── limit 837 │ ├── columns: u:2(int) v:3(int!null) 838 │ ├── cardinality: [0 - 2] 839 │ ├── prune: (2,3) 840 │ ├── project 841 │ │ ├── columns: u:2(int) v:3(int!null) 842 │ │ ├── limit hint: 2.00 843 │ │ ├── prune: (2,3) 844 │ │ └── scan uv 845 │ │ ├── columns: u:2(int) v:3(int!null) rowid:4(int!null) 846 │ │ ├── key: (4) 847 │ │ ├── fd: (4)-->(2,3) 848 │ │ ├── limit hint: 2.00 849 │ │ ├── prune: (2-4) 850 │ │ └── interesting orderings: (+4) 851 │ └── const: 2 [type=int] 852 └── filters 853 └── true [type=bool] 854 855 # Calculate right-join cardinality. 856 build 857 SELECT * FROM (SELECT * FROM uv LIMIT 2) RIGHT JOIN (VALUES (1), (2), (3)) ON True 858 ---- 859 right-join (cross) 860 ├── columns: u:1(int) v:2(int) column1:4(int!null) 861 ├── cardinality: [3 - 6] 862 ├── prune: (1,2,4) 863 ├── reject-nulls: (1,2) 864 ├── limit 865 │ ├── columns: u:1(int) v:2(int!null) 866 │ ├── cardinality: [0 - 2] 867 │ ├── prune: (1,2) 868 │ ├── project 869 │ │ ├── columns: u:1(int) v:2(int!null) 870 │ │ ├── limit hint: 2.00 871 │ │ ├── prune: (1,2) 872 │ │ └── scan uv 873 │ │ ├── columns: u:1(int) v:2(int!null) rowid:3(int!null) 874 │ │ ├── key: (3) 875 │ │ ├── fd: (3)-->(1,2) 876 │ │ ├── limit hint: 2.00 877 │ │ ├── prune: (1-3) 878 │ │ └── interesting orderings: (+3) 879 │ └── const: 2 [type=int] 880 ├── values 881 │ ├── columns: column1:4(int!null) 882 │ ├── cardinality: [3 - 3] 883 │ ├── prune: (4) 884 │ ├── tuple [type=tuple{int}] 885 │ │ └── const: 1 [type=int] 886 │ ├── tuple [type=tuple{int}] 887 │ │ └── const: 2 [type=int] 888 │ └── tuple [type=tuple{int}] 889 │ └── const: 3 [type=int] 890 └── filters 891 └── true [type=bool] 892 893 # Calculate full-join cardinality. 894 build 895 SELECT * FROM (VALUES (NULL), (NULL)) a FULL JOIN (VALUES (NULL), (NULL)) b ON True 896 ---- 897 full-join (cross) 898 ├── columns: column1:1(unknown) column1:2(unknown) 899 ├── cardinality: [2 - 4] 900 ├── prune: (1,2) 901 ├── reject-nulls: (1,2) 902 ├── multiplicity: left-rows(one-or-more), right-rows(one-or-more) 903 ├── values 904 │ ├── columns: column1:1(unknown) 905 │ ├── cardinality: [2 - 2] 906 │ ├── prune: (1) 907 │ ├── tuple [type=tuple{unknown}] 908 │ │ └── null [type=unknown] 909 │ └── tuple [type=tuple{unknown}] 910 │ └── null [type=unknown] 911 ├── values 912 │ ├── columns: column1:2(unknown) 913 │ ├── cardinality: [2 - 2] 914 │ ├── prune: (2) 915 │ ├── tuple [type=tuple{unknown}] 916 │ │ └── null [type=unknown] 917 │ └── tuple [type=tuple{unknown}] 918 │ └── null [type=unknown] 919 └── filters 920 └── true [type=bool] 921 922 # Calculate full-join cardinality when both sides have an empty key (#44029). 923 build 924 SELECT * FROM (VALUES (1, 2)) a(a1,a2) FULL JOIN (VALUES (3, 4)) b(b1,b2) ON a1=b1 925 ---- 926 full-join (hash) 927 ├── columns: a1:1(int) a2:2(int) b1:3(int) b2:4(int) 928 ├── cardinality: [1 - 2] 929 ├── prune: (2,4) 930 ├── reject-nulls: (1-4) 931 ├── multiplicity: left-rows(exactly-one), right-rows(exactly-one) 932 ├── values 933 │ ├── columns: column1:1(int!null) column2:2(int!null) 934 │ ├── cardinality: [1 - 1] 935 │ ├── key: () 936 │ ├── fd: ()-->(1,2) 937 │ ├── prune: (1,2) 938 │ └── tuple [type=tuple{int, int}] 939 │ ├── const: 1 [type=int] 940 │ └── const: 2 [type=int] 941 ├── values 942 │ ├── columns: column1:3(int!null) column2:4(int!null) 943 │ ├── cardinality: [1 - 1] 944 │ ├── key: () 945 │ ├── fd: ()-->(3,4) 946 │ ├── prune: (3,4) 947 │ └── tuple [type=tuple{int, int}] 948 │ ├── const: 3 [type=int] 949 │ └── const: 4 [type=int] 950 └── filters 951 └── eq [type=bool, outer=(1,3), constraints=(/1: (/NULL - ]; /3: (/NULL - ]), fd=(1)==(3), (3)==(1)] 952 ├── variable: column1:1 [type=int] 953 └── variable: column1:3 [type=int] 954 955 # Calculate full-join cardinality with false filter. 956 build 957 SELECT * FROM (VALUES (NULL), (NULL)) a FULL JOIN (VALUES (NULL), (NULL)) b ON a.column1=b.column1 958 ---- 959 full-join (cross) 960 ├── columns: column1:1(unknown) column1:2(unknown) 961 ├── cardinality: [2 - 4] 962 ├── prune: (1,2) 963 ├── reject-nulls: (1,2) 964 ├── multiplicity: left-rows(one-or-more), right-rows(one-or-more) 965 ├── values 966 │ ├── columns: column1:1(unknown) 967 │ ├── cardinality: [2 - 2] 968 │ ├── prune: (1) 969 │ ├── tuple [type=tuple{unknown}] 970 │ │ └── null [type=unknown] 971 │ └── tuple [type=tuple{unknown}] 972 │ └── null [type=unknown] 973 ├── values 974 │ ├── columns: column1:2(unknown) 975 │ ├── cardinality: [2 - 2] 976 │ ├── prune: (2) 977 │ ├── tuple [type=tuple{unknown}] 978 │ │ └── null [type=unknown] 979 │ └── tuple [type=tuple{unknown}] 980 │ └── null [type=unknown] 981 └── filters 982 └── cast: BOOL [type=bool] 983 └── null [type=unknown] 984 985 # Calculate full-join cardinality of one input with unknown cardinality. 986 build 987 SELECT * FROM xysd FULL JOIN (SELECT * FROM (VALUES (1), (2))) ON True 988 ---- 989 full-join (cross) 990 ├── columns: x:1(int) y:2(int) s:3(string) d:4(decimal) column1:5(int) 991 ├── cardinality: [2 - ] 992 ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 993 ├── prune: (1-5) 994 ├── reject-nulls: (1-5) 995 ├── interesting orderings: (+1) (-3,+4,+1) 996 ├── multiplicity: left-rows(one-or-more), right-rows(one-or-more) 997 ├── scan xysd 998 │ ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 999 │ ├── key: (1) 1000 │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 1001 │ ├── prune: (1-4) 1002 │ └── interesting orderings: (+1) (-3,+4,+1) 1003 ├── values 1004 │ ├── columns: column1:5(int!null) 1005 │ ├── cardinality: [2 - 2] 1006 │ ├── prune: (5) 1007 │ ├── tuple [type=tuple{int}] 1008 │ │ └── const: 1 [type=int] 1009 │ └── tuple [type=tuple{int}] 1010 │ └── const: 2 [type=int] 1011 └── filters 1012 └── true [type=bool] 1013 1014 # Keys on both sides of full-join. 1015 build 1016 SELECT * FROM (SELECT * FROM xysd LIMIT 1) FULL JOIN (SELECT * FROM xysd LIMIT 1) ON True 1017 ---- 1018 full-join (cross) 1019 ├── columns: x:1(int) y:2(int) s:3(string) d:4(decimal) x:5(int) y:6(int) s:7(string) d:8(decimal) 1020 ├── cardinality: [0 - 2] 1021 ├── prune: (1-8) 1022 ├── reject-nulls: (1-8) 1023 ├── interesting orderings: (+1) (-3,+4,+1) (+5) (-7,+8,+5) 1024 ├── multiplicity: left-rows(exactly-one), right-rows(exactly-one) 1025 ├── limit 1026 │ ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 1027 │ ├── cardinality: [0 - 1] 1028 │ ├── key: () 1029 │ ├── fd: ()-->(1-4) 1030 │ ├── prune: (1-4) 1031 │ ├── interesting orderings: (+1) (-3,+4,+1) 1032 │ ├── scan xysd 1033 │ │ ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 1034 │ │ ├── key: (1) 1035 │ │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 1036 │ │ ├── limit hint: 1.00 1037 │ │ ├── prune: (1-4) 1038 │ │ └── interesting orderings: (+1) (-3,+4,+1) 1039 │ └── const: 1 [type=int] 1040 ├── limit 1041 │ ├── columns: x:5(int!null) y:6(int) s:7(string) d:8(decimal!null) 1042 │ ├── cardinality: [0 - 1] 1043 │ ├── key: () 1044 │ ├── fd: ()-->(5-8) 1045 │ ├── prune: (5-8) 1046 │ ├── interesting orderings: (+5) (-7,+8,+5) 1047 │ ├── scan xysd 1048 │ │ ├── columns: x:5(int!null) y:6(int) s:7(string) d:8(decimal!null) 1049 │ │ ├── key: (5) 1050 │ │ ├── fd: (5)-->(6-8), (7,8)~~>(5,6) 1051 │ │ ├── limit hint: 1.00 1052 │ │ ├── prune: (5-8) 1053 │ │ └── interesting orderings: (+5) (-7,+8,+5) 1054 │ └── const: 1 [type=int] 1055 └── filters 1056 └── true [type=bool] 1057 1058 # Nullable FD determinant on right side of left-join becomes lax. 1059 build 1060 SELECT * FROM xysd LEFT JOIN (SELECT u, sum(v) FROM uv GROUP BY u) ON u IS NOT NULL 1061 ---- 1062 left-join (cross) 1063 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) u:5(int) sum:8(decimal) 1064 ├── key: (1,5) 1065 ├── fd: (1)-->(2-4), (3,4)~~>(1,2), (5)~~>(8), (1,5)-->(8) 1066 ├── prune: (1-4,8) 1067 ├── reject-nulls: (5,8) 1068 ├── interesting orderings: (+1) (-3,+4,+1) 1069 ├── multiplicity: left-rows(one-or-more), right-rows(zero-or-more) 1070 ├── scan xysd 1071 │ ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 1072 │ ├── key: (1) 1073 │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 1074 │ ├── prune: (1-4) 1075 │ └── interesting orderings: (+1) (-3,+4,+1) 1076 ├── group-by 1077 │ ├── columns: u:5(int) sum:8(decimal!null) 1078 │ ├── grouping columns: u:5(int) 1079 │ ├── key: (5) 1080 │ ├── fd: (5)-->(8) 1081 │ ├── prune: (8) 1082 │ ├── project 1083 │ │ ├── columns: u:5(int) v:6(int!null) 1084 │ │ ├── prune: (5,6) 1085 │ │ └── scan uv 1086 │ │ ├── columns: u:5(int) v:6(int!null) rowid:7(int!null) 1087 │ │ ├── key: (7) 1088 │ │ ├── fd: (7)-->(5,6) 1089 │ │ ├── prune: (5-7) 1090 │ │ └── interesting orderings: (+7) 1091 │ └── aggregations 1092 │ └── sum [as=sum:8, type=decimal, outer=(6)] 1093 │ └── variable: v:6 [type=int] 1094 └── filters 1095 └── is-not [type=bool, outer=(5), constraints=(/5: (/NULL - ]; tight)] 1096 ├── variable: u:5 [type=int] 1097 └── null [type=unknown] 1098 1099 # Not-null FD determinant on right side of left-join stays strict. 1100 build 1101 SELECT * FROM xysd LEFT JOIN (SELECT u, sum(v) FROM uv WHERE u IS NOT NULL GROUP BY u) ON True 1102 ---- 1103 left-join (cross) 1104 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) u:5(int) sum:8(decimal) 1105 ├── key: (1,5) 1106 ├── fd: (1)-->(2-4), (3,4)~~>(1,2), (5)-->(8) 1107 ├── prune: (1-4,8) 1108 ├── reject-nulls: (5,8) 1109 ├── interesting orderings: (+1) (-3,+4,+1) 1110 ├── multiplicity: left-rows(one-or-more), right-rows(zero-or-more) 1111 ├── scan xysd 1112 │ ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 1113 │ ├── key: (1) 1114 │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 1115 │ ├── prune: (1-4) 1116 │ └── interesting orderings: (+1) (-3,+4,+1) 1117 ├── group-by 1118 │ ├── columns: u:5(int!null) sum:8(decimal!null) 1119 │ ├── grouping columns: u:5(int!null) 1120 │ ├── key: (5) 1121 │ ├── fd: (5)-->(8) 1122 │ ├── prune: (8) 1123 │ ├── project 1124 │ │ ├── columns: u:5(int!null) v:6(int!null) 1125 │ │ ├── prune: (5,6) 1126 │ │ └── select 1127 │ │ ├── columns: u:5(int!null) v:6(int!null) rowid:7(int!null) 1128 │ │ ├── key: (7) 1129 │ │ ├── fd: (7)-->(5,6) 1130 │ │ ├── prune: (6,7) 1131 │ │ ├── interesting orderings: (+7) 1132 │ │ ├── scan uv 1133 │ │ │ ├── columns: u:5(int) v:6(int!null) rowid:7(int!null) 1134 │ │ │ ├── key: (7) 1135 │ │ │ ├── fd: (7)-->(5,6) 1136 │ │ │ ├── prune: (5-7) 1137 │ │ │ └── interesting orderings: (+7) 1138 │ │ └── filters 1139 │ │ └── is-not [type=bool, outer=(5), constraints=(/5: (/NULL - ]; tight)] 1140 │ │ ├── variable: u:5 [type=int] 1141 │ │ └── null [type=unknown] 1142 │ └── aggregations 1143 │ └── sum [as=sum:8, type=decimal, outer=(6)] 1144 │ └── variable: v:6 [type=int] 1145 └── filters 1146 └── true [type=bool] 1147 1148 # Nullable FD determinant on left side of right-join becomes lax. 1149 build 1150 SELECT * FROM (SELECT u, sum(v) FROM uv GROUP BY u) RIGHT JOIN xysd ON u IS NOT NULL 1151 ---- 1152 right-join (cross) 1153 ├── columns: u:1(int) sum:4(decimal) x:5(int!null) y:6(int) s:7(string) d:8(decimal!null) 1154 ├── key: (1,5) 1155 ├── fd: (5)-->(6-8), (7,8)~~>(5,6), (1)~~>(4), (1,5)-->(4) 1156 ├── prune: (4-8) 1157 ├── reject-nulls: (1,4) 1158 ├── interesting orderings: (+5) (-7,+8,+5) 1159 ├── group-by 1160 │ ├── columns: u:1(int) sum:4(decimal!null) 1161 │ ├── grouping columns: u:1(int) 1162 │ ├── key: (1) 1163 │ ├── fd: (1)-->(4) 1164 │ ├── prune: (4) 1165 │ ├── project 1166 │ │ ├── columns: u:1(int) v:2(int!null) 1167 │ │ ├── prune: (1,2) 1168 │ │ └── scan uv 1169 │ │ ├── columns: u:1(int) v:2(int!null) rowid:3(int!null) 1170 │ │ ├── key: (3) 1171 │ │ ├── fd: (3)-->(1,2) 1172 │ │ ├── prune: (1-3) 1173 │ │ └── interesting orderings: (+3) 1174 │ └── aggregations 1175 │ └── sum [as=sum:4, type=decimal, outer=(2)] 1176 │ └── variable: v:2 [type=int] 1177 ├── scan xysd 1178 │ ├── columns: x:5(int!null) y:6(int) s:7(string) d:8(decimal!null) 1179 │ ├── key: (5) 1180 │ ├── fd: (5)-->(6-8), (7,8)~~>(5,6) 1181 │ ├── prune: (5-8) 1182 │ └── interesting orderings: (+5) (-7,+8,+5) 1183 └── filters 1184 └── is-not [type=bool, outer=(1), constraints=(/1: (/NULL - ]; tight)] 1185 ├── variable: u:1 [type=int] 1186 └── null [type=unknown] 1187 1188 # Not-null FD determinant on left side of right-join stays strict. 1189 build 1190 SELECT * FROM (SELECT u, sum(v) FROM uv WHERE u IS NOT NULL GROUP BY u) RIGHT JOIN xysd ON True 1191 ---- 1192 right-join (cross) 1193 ├── columns: u:1(int) sum:4(decimal) x:5(int!null) y:6(int) s:7(string) d:8(decimal!null) 1194 ├── key: (1,5) 1195 ├── fd: (1)-->(4), (5)-->(6-8), (7,8)~~>(5,6) 1196 ├── prune: (4-8) 1197 ├── reject-nulls: (1,4) 1198 ├── interesting orderings: (+5) (-7,+8,+5) 1199 ├── group-by 1200 │ ├── columns: u:1(int!null) sum:4(decimal!null) 1201 │ ├── grouping columns: u:1(int!null) 1202 │ ├── key: (1) 1203 │ ├── fd: (1)-->(4) 1204 │ ├── prune: (4) 1205 │ ├── project 1206 │ │ ├── columns: u:1(int!null) v:2(int!null) 1207 │ │ ├── prune: (1,2) 1208 │ │ └── select 1209 │ │ ├── columns: u:1(int!null) v:2(int!null) rowid:3(int!null) 1210 │ │ ├── key: (3) 1211 │ │ ├── fd: (3)-->(1,2) 1212 │ │ ├── prune: (2,3) 1213 │ │ ├── interesting orderings: (+3) 1214 │ │ ├── scan uv 1215 │ │ │ ├── columns: u:1(int) v:2(int!null) rowid:3(int!null) 1216 │ │ │ ├── key: (3) 1217 │ │ │ ├── fd: (3)-->(1,2) 1218 │ │ │ ├── prune: (1-3) 1219 │ │ │ └── interesting orderings: (+3) 1220 │ │ └── filters 1221 │ │ └── is-not [type=bool, outer=(1), constraints=(/1: (/NULL - ]; tight)] 1222 │ │ ├── variable: u:1 [type=int] 1223 │ │ └── null [type=unknown] 1224 │ └── aggregations 1225 │ └── sum [as=sum:4, type=decimal, outer=(2)] 1226 │ └── variable: v:2 [type=int] 1227 ├── scan xysd 1228 │ ├── columns: x:5(int!null) y:6(int) s:7(string) d:8(decimal!null) 1229 │ ├── key: (5) 1230 │ ├── fd: (5)-->(6-8), (7,8)~~>(5,6) 1231 │ ├── prune: (5-8) 1232 │ └── interesting orderings: (+5) (-7,+8,+5) 1233 └── filters 1234 └── true [type=bool] 1235 1236 # Nullable FD determinant on right side of full-join becomes lax. 1237 build 1238 SELECT * FROM xysd FULL JOIN (SELECT u, sum(v) FROM uv GROUP BY u) ON u IS NOT NULL 1239 ---- 1240 full-join (cross) 1241 ├── columns: x:1(int) y:2(int) s:3(string) d:4(decimal) u:5(int) sum:8(decimal) 1242 ├── key: (1,5) 1243 ├── fd: (1)-->(2-4), (3,4)~~>(1,2), (5)~~>(8), (1,5)-->(8) 1244 ├── prune: (1-4,8) 1245 ├── reject-nulls: (1-5,8) 1246 ├── interesting orderings: (+1) (-3,+4,+1) 1247 ├── multiplicity: left-rows(one-or-more), right-rows(one-or-more) 1248 ├── scan xysd 1249 │ ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 1250 │ ├── key: (1) 1251 │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 1252 │ ├── prune: (1-4) 1253 │ └── interesting orderings: (+1) (-3,+4,+1) 1254 ├── group-by 1255 │ ├── columns: u:5(int) sum:8(decimal!null) 1256 │ ├── grouping columns: u:5(int) 1257 │ ├── key: (5) 1258 │ ├── fd: (5)-->(8) 1259 │ ├── prune: (8) 1260 │ ├── project 1261 │ │ ├── columns: u:5(int) v:6(int!null) 1262 │ │ ├── prune: (5,6) 1263 │ │ └── scan uv 1264 │ │ ├── columns: u:5(int) v:6(int!null) rowid:7(int!null) 1265 │ │ ├── key: (7) 1266 │ │ ├── fd: (7)-->(5,6) 1267 │ │ ├── prune: (5-7) 1268 │ │ └── interesting orderings: (+7) 1269 │ └── aggregations 1270 │ └── sum [as=sum:8, type=decimal, outer=(6)] 1271 │ └── variable: v:6 [type=int] 1272 └── filters 1273 └── is-not [type=bool, outer=(5), constraints=(/5: (/NULL - ]; tight)] 1274 ├── variable: u:5 [type=int] 1275 └── null [type=unknown] 1276 1277 # Nullable FD determinant on left side of full-join becomes lax. 1278 build 1279 SELECT * FROM (SELECT u, sum(v) FROM uv GROUP BY u) FULL JOIN xysd ON u IS NOT NULL 1280 ---- 1281 full-join (cross) 1282 ├── columns: u:1(int) sum:4(decimal) x:5(int) y:6(int) s:7(string) d:8(decimal) 1283 ├── key: (1,5) 1284 ├── fd: (5)-->(6-8), (7,8)~~>(5,6), (1)~~>(4), (1,5)-->(4) 1285 ├── prune: (4-8) 1286 ├── reject-nulls: (1,4-8) 1287 ├── interesting orderings: (+5) (-7,+8,+5) 1288 ├── multiplicity: left-rows(one-or-more), right-rows(one-or-more) 1289 ├── group-by 1290 │ ├── columns: u:1(int) sum:4(decimal!null) 1291 │ ├── grouping columns: u:1(int) 1292 │ ├── key: (1) 1293 │ ├── fd: (1)-->(4) 1294 │ ├── prune: (4) 1295 │ ├── project 1296 │ │ ├── columns: u:1(int) v:2(int!null) 1297 │ │ ├── prune: (1,2) 1298 │ │ └── scan uv 1299 │ │ ├── columns: u:1(int) v:2(int!null) rowid:3(int!null) 1300 │ │ ├── key: (3) 1301 │ │ ├── fd: (3)-->(1,2) 1302 │ │ ├── prune: (1-3) 1303 │ │ └── interesting orderings: (+3) 1304 │ └── aggregations 1305 │ └── sum [as=sum:4, type=decimal, outer=(2)] 1306 │ └── variable: v:2 [type=int] 1307 ├── scan xysd 1308 │ ├── columns: x:5(int!null) y:6(int) s:7(string) d:8(decimal!null) 1309 │ ├── key: (5) 1310 │ ├── fd: (5)-->(6-8), (7,8)~~>(5,6) 1311 │ ├── prune: (5-8) 1312 │ └── interesting orderings: (+5) (-7,+8,+5) 1313 └── filters 1314 └── is-not [type=bool, outer=(1), constraints=(/1: (/NULL - ]; tight)] 1315 ├── variable: u:1 [type=int] 1316 └── null [type=unknown] 1317 1318 # Merge join (inner). 1319 expr 1320 (MergeJoin 1321 (Scan [ (Table "xysd") (Cols "x,y,s,d") ]) 1322 (Sort (Scan [ (Table "uv") (Cols "u,v,rowid") ])) 1323 [ ] 1324 [ 1325 (JoinType "inner-join") 1326 (LeftEq "+x") 1327 (RightEq "+u") 1328 (LeftOrdering "+x") 1329 (RightOrdering "+u") 1330 ] 1331 ) 1332 ---- 1333 inner-join (merge) 1334 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) u:5(int!null) v:6(int!null) rowid:7(int!null) 1335 ├── left ordering: +1 1336 ├── right ordering: +5 1337 ├── key: (7) 1338 ├── fd: (1)-->(2-4), (3,4)~~>(1,2), (7)-->(5,6), (1)==(5), (5)==(1) 1339 ├── scan xysd 1340 │ ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 1341 │ ├── key: (1) 1342 │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 1343 │ ├── ordering: +1 1344 │ ├── prune: (1-4) 1345 │ └── interesting orderings: (+1) (-3,+4,+1) 1346 ├── sort 1347 │ ├── columns: u:5(int) v:6(int!null) rowid:7(int!null) 1348 │ ├── key: (7) 1349 │ ├── fd: (7)-->(5,6) 1350 │ ├── ordering: +5 1351 │ └── scan uv 1352 │ ├── columns: u:5(int) v:6(int!null) rowid:7(int!null) 1353 │ ├── key: (7) 1354 │ └── fd: (7)-->(5,6) 1355 └── filters (true) 1356 1357 # Merge join (left). 1358 expr 1359 (MergeJoin 1360 (Scan [ (Table "xysd") (Cols "x,y,s,d") ]) 1361 (Sort (Scan [ (Table "uv") (Cols "u,v,rowid") ])) 1362 [ ] 1363 [ 1364 (JoinType "left-join") 1365 (LeftEq "+x") 1366 (RightEq "+u") 1367 (LeftOrdering "+x") 1368 (RightOrdering "+u") 1369 ] 1370 ) 1371 ---- 1372 left-join (merge) 1373 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) u:5(int) v:6(int) rowid:7(int) 1374 ├── left ordering: +1 1375 ├── right ordering: +5 1376 ├── key: (1,7) 1377 ├── fd: (1)-->(2-4), (3,4)~~>(1,2), (7)-->(5,6) 1378 ├── scan xysd 1379 │ ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 1380 │ ├── key: (1) 1381 │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 1382 │ ├── ordering: +1 1383 │ ├── prune: (1-4) 1384 │ └── interesting orderings: (+1) (-3,+4,+1) 1385 ├── sort 1386 │ ├── columns: u:5(int) v:6(int!null) rowid:7(int!null) 1387 │ ├── key: (7) 1388 │ ├── fd: (7)-->(5,6) 1389 │ ├── ordering: +5 1390 │ └── scan uv 1391 │ ├── columns: u:5(int) v:6(int!null) rowid:7(int!null) 1392 │ ├── key: (7) 1393 │ └── fd: (7)-->(5,6) 1394 └── filters (true) 1395 1396 # Merge join (right) with remaining ON condition. 1397 expr 1398 (MergeJoin 1399 (Scan [ (Table "xysd") (Cols "x,y,s,d") ]) 1400 (Sort (Scan [ (Table "uv") (Cols "u,v,rowid") ])) 1401 [ (Gt (Var "y") (Var "v")) ] 1402 [ 1403 (JoinType "right-join") 1404 (LeftEq "+x") 1405 (RightEq "+u") 1406 (LeftOrdering "+x") 1407 (RightOrdering "+u") 1408 ] 1409 ) 1410 ---- 1411 right-join (merge) 1412 ├── columns: x:1(int) y:2(int) s:3(string) d:4(decimal) u:5(int) v:6(int!null) rowid:7(int!null) 1413 ├── left ordering: +1 1414 ├── right ordering: +5 1415 ├── key: (7) 1416 ├── fd: (1)-->(2-4), (3,4)~~>(1,2), (7)-->(1-6) 1417 ├── scan xysd 1418 │ ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 1419 │ ├── key: (1) 1420 │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 1421 │ ├── ordering: +1 1422 │ ├── prune: (1-4) 1423 │ └── interesting orderings: (+1) (-3,+4,+1) 1424 ├── sort 1425 │ ├── columns: u:5(int) v:6(int!null) rowid:7(int!null) 1426 │ ├── key: (7) 1427 │ ├── fd: (7)-->(5,6) 1428 │ ├── ordering: +5 1429 │ └── scan uv 1430 │ ├── columns: u:5(int) v:6(int!null) rowid:7(int!null) 1431 │ ├── key: (7) 1432 │ └── fd: (7)-->(5,6) 1433 └── filters 1434 └── gt [type=bool, outer=(2,6), constraints=(/2: (/NULL - ]; /6: (/NULL - ])] 1435 ├── variable: y:2 [type=int] 1436 └── variable: v:6 [type=int] 1437 1438 # Regression test #36183. 1439 opt 1440 SELECT (SELECT m FROM 1441 (SELECT * FROM (SELECT * FROM [INSERT INTO uv VALUES (1, 2) RETURNING *] WHERE false) JOIN (SELECT * FROM uv WHERE false) ON true) 1442 JOIN (SELECT * FROM mn WHERE uv.u IN (SELECT n FROM mn)) ON true 1443 ) FROM uv 1444 ---- 1445 with &1 1446 ├── columns: m:19(int) 1447 ├── volatile, side-effects, mutations 1448 ├── fd: ()-->(19) 1449 ├── prune: (19) 1450 ├── project 1451 │ ├── columns: uv.u:4(int!null) uv.v:5(int!null) 1452 │ ├── cardinality: [1 - 1] 1453 │ ├── volatile, side-effects, mutations 1454 │ ├── key: () 1455 │ ├── fd: ()-->(4,5) 1456 │ ├── prune: (4,5) 1457 │ └── insert uv 1458 │ ├── columns: uv.u:4(int!null) uv.v:5(int!null) rowid:6(int!null) 1459 │ ├── insert-mapping: 1460 │ │ ├── column1:7 => uv.u:4 1461 │ │ ├── column2:8 => uv.v:5 1462 │ │ └── column9:9 => rowid:6 1463 │ ├── cardinality: [1 - 1] 1464 │ ├── volatile, side-effects, mutations 1465 │ ├── key: () 1466 │ ├── fd: ()-->(4-6) 1467 │ └── values 1468 │ ├── columns: column1:7(int!null) column2:8(int!null) column9:9(int) 1469 │ ├── cardinality: [1 - 1] 1470 │ ├── volatile, side-effects 1471 │ ├── key: () 1472 │ ├── fd: ()-->(7-9) 1473 │ ├── prune: (7-9) 1474 │ └── tuple [type=tuple{int, int, int}] 1475 │ ├── const: 1 [type=int] 1476 │ ├── const: 2 [type=int] 1477 │ └── function: unique_rowid [type=int] 1478 └── project 1479 ├── columns: m:19(int) 1480 ├── fd: ()-->(19) 1481 ├── prune: (19) 1482 ├── scan uv 1483 └── projections 1484 └── subquery [as=m:19, type=int, subquery] 1485 └── values 1486 ├── columns: mn.m:15(int!null) 1487 ├── cardinality: [0 - 0] 1488 ├── key: () 1489 ├── fd: ()-->(15) 1490 └── prune: (15) 1491 1492 # Regression test #40456. 1493 opt 1494 SELECT NULL 1495 FROM uv 1496 WHERE NOT EXISTS(SELECT uv.u); 1497 ---- 1498 values 1499 ├── columns: "?column?":5(unknown!null) 1500 ├── cardinality: [0 - 0] 1501 ├── key: () 1502 ├── fd: ()-->(5) 1503 └── prune: (5) 1504 1505 # Regression test #43651: outer join with empty key. 1506 opt 1507 SELECT a FROM 1508 (VALUES (NULL)) AS t1(a) 1509 FULL JOIN 1510 (VALUES ('23:59:59.999999':::TIME)) AS t2(b) 1511 ON false 1512 ---- 1513 full-join (cross) 1514 ├── columns: a:1(unknown) 1515 ├── cardinality: [2 - 2] 1516 ├── prune: (1) 1517 ├── reject-nulls: (1) 1518 ├── multiplicity: left-rows(exactly-one), right-rows(exactly-one) 1519 ├── values 1520 │ ├── columns: column1:1(unknown) 1521 │ ├── cardinality: [1 - 1] 1522 │ ├── key: () 1523 │ ├── fd: ()-->(1) 1524 │ ├── prune: (1) 1525 │ └── tuple [type=tuple{unknown}] 1526 │ └── null [type=unknown] 1527 ├── values 1528 │ ├── cardinality: [1 - 1] 1529 │ ├── key: () 1530 │ └── tuple [type=tuple] 1531 └── filters 1532 └── false [type=bool] 1533 1534 exec-ddl 1535 CREATE TABLE t1 (x INT, y INT) 1536 ---- 1537 1538 exec-ddl 1539 CREATE TABLE t2 (x INT, y INT) 1540 ---- 1541 1542 # Outer join when both sides have a key. Because x can still have NULL values, 1543 # we cannot say that the outer join has a strict key. For example, this is a 1544 # possible valid result for this query: 1545 # t1.x | t1.y | t2.x | t2.y 1546 # -----+------+------+------ 1547 # 1 | 1 | 1 | 2 1548 # NULL | 1 | NULL | NULL 1549 # NULL | NULL | NULL | 2 1550 # Here (t1.x, t2.x) is a lax key but not a strict key. 1551 opt 1552 SELECT * FROM 1553 (SELECT * FROM (SELECT DISTINCT ON (x) x, y FROM t1) WHERE y IS NOT NULL) AS t1 1554 FULL JOIN 1555 (SELECT * FROM (SELECT DISTINCT ON (x) x, y FROM t2) WHERE y IS NOT NULL) AS t2 1556 ON t1.x = t2.x 1557 ---- 1558 full-join (hash) 1559 ├── columns: x:1(int) y:2(int) x:4(int) y:5(int) 1560 ├── lax-key: (1,4) 1561 ├── fd: (1)~~>(2), (4)~~>(5), (1,4)~~>(2,5) 1562 ├── reject-nulls: (1,2,4,5) 1563 ├── multiplicity: left-rows(exactly-one), right-rows(exactly-one) 1564 ├── select 1565 │ ├── columns: t1.x:1(int) t1.y:2(int!null) 1566 │ ├── key: (1) 1567 │ ├── fd: (1)-->(2) 1568 │ ├── distinct-on 1569 │ │ ├── columns: t1.x:1(int) t1.y:2(int) 1570 │ │ ├── grouping columns: t1.x:1(int) 1571 │ │ ├── key: (1) 1572 │ │ ├── fd: (1)-->(2) 1573 │ │ ├── prune: (2) 1574 │ │ ├── scan t1 1575 │ │ │ ├── columns: t1.x:1(int) t1.y:2(int) 1576 │ │ │ └── prune: (1,2) 1577 │ │ └── aggregations 1578 │ │ └── first-agg [as=t1.y:2, type=int, outer=(2)] 1579 │ │ └── variable: t1.y:2 [type=int] 1580 │ └── filters 1581 │ └── is-not [type=bool, outer=(2), constraints=(/2: (/NULL - ]; tight)] 1582 │ ├── variable: t1.y:2 [type=int] 1583 │ └── null [type=unknown] 1584 ├── select 1585 │ ├── columns: t2.x:4(int) t2.y:5(int!null) 1586 │ ├── key: (4) 1587 │ ├── fd: (4)-->(5) 1588 │ ├── distinct-on 1589 │ │ ├── columns: t2.x:4(int) t2.y:5(int) 1590 │ │ ├── grouping columns: t2.x:4(int) 1591 │ │ ├── key: (4) 1592 │ │ ├── fd: (4)-->(5) 1593 │ │ ├── prune: (5) 1594 │ │ ├── scan t2 1595 │ │ │ ├── columns: t2.x:4(int) t2.y:5(int) 1596 │ │ │ └── prune: (4,5) 1597 │ │ └── aggregations 1598 │ │ └── first-agg [as=t2.y:5, type=int, outer=(5)] 1599 │ │ └── variable: t2.y:5 [type=int] 1600 │ └── filters 1601 │ └── is-not [type=bool, outer=(5), constraints=(/5: (/NULL - ]; tight)] 1602 │ ├── variable: t2.y:5 [type=int] 1603 │ └── null [type=unknown] 1604 └── filters 1605 └── eq [type=bool, outer=(1,4), constraints=(/1: (/NULL - ]; /4: (/NULL - ]), fd=(1)==(4), (4)==(1)] 1606 ├── variable: t1.x:1 [type=int] 1607 └── variable: t2.x:4 [type=int] 1608 1609 # InnerJoin with an equality between one key column and one non-key column. 1610 # Neither input is guaranteed a match for every row. Rows from uv will not be 1611 # duplicated because the x column is unique. Rows from xysd may be duplicated 1612 # because the v column is not unique. 1613 norm 1614 SELECT * FROM xysd INNER JOIN uv ON x=v 1615 ---- 1616 inner-join (hash) 1617 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) u:5(int) v:6(int!null) 1618 ├── fd: (1)-->(2-4), (3,4)~~>(1,2), (1)==(6), (6)==(1) 1619 ├── prune: (2-5) 1620 ├── interesting orderings: (+1) (-3,+4,+1) 1621 ├── multiplicity: left-rows(zero-or-more), right-rows(one-or-zero) 1622 ├── scan xysd 1623 │ ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 1624 │ ├── key: (1) 1625 │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 1626 │ ├── prune: (1-4) 1627 │ └── interesting orderings: (+1) (-3,+4,+1) 1628 ├── scan uv 1629 │ ├── columns: u:5(int) v:6(int!null) 1630 │ └── prune: (5,6) 1631 └── filters 1632 └── eq [type=bool, outer=(1,6), constraints=(/1: (/NULL - ]; /6: (/NULL - ]), fd=(1)==(6), (6)==(1)] 1633 ├── variable: x:1 [type=int] 1634 └── variable: v:6 [type=int] 1635 1636 # InnerJoin with a not-null foreign key equality. Since the foreign key is 1637 # not-null, rows from the fk table are guaranteed a match. Since x is a key 1638 # column, rows from the fk table will not be duplicated. 1639 norm 1640 SELECT * FROM fk INNER JOIN xysd ON x = r1 1641 ---- 1642 inner-join (hash) 1643 ├── columns: k:1(int!null) v:2(int) r1:3(int!null) r2:4(int) x:5(int!null) y:6(int) s:7(string) d:8(decimal!null) 1644 ├── key: (1) 1645 ├── fd: (1)-->(2-4), (5)-->(6-8), (7,8)~~>(5,6), (3)==(5), (5)==(3) 1646 ├── prune: (1,2,4,6-8) 1647 ├── interesting orderings: (+1) (+3,+1) (+4,+1) (+5) (-7,+8,+5) 1648 ├── multiplicity: left-rows(exactly-one), right-rows(zero-or-more) 1649 ├── scan fk 1650 │ ├── columns: k:1(int!null) v:2(int) r1:3(int!null) r2:4(int) 1651 │ ├── key: (1) 1652 │ ├── fd: (1)-->(2-4) 1653 │ ├── prune: (1-4) 1654 │ └── interesting orderings: (+1) (+3,+1) (+4,+1) 1655 ├── scan xysd 1656 │ ├── columns: x:5(int!null) y:6(int) s:7(string) d:8(decimal!null) 1657 │ ├── key: (5) 1658 │ ├── fd: (5)-->(6-8), (7,8)~~>(5,6) 1659 │ ├── prune: (5-8) 1660 │ └── interesting orderings: (+5) (-7,+8,+5) 1661 └── filters 1662 └── eq [type=bool, outer=(3,5), constraints=(/3: (/NULL - ]; /5: (/NULL - ]), fd=(3)==(5), (5)==(3)] 1663 ├── variable: x:5 [type=int] 1664 └── variable: r1:3 [type=int] 1665 1666 # InnerJoin with a nullable foreign key equality condition. 1667 norm 1668 SELECT * FROM fk INNER JOIN xysd ON x = r2 1669 ---- 1670 inner-join (hash) 1671 ├── columns: k:1(int!null) v:2(int) r1:3(int!null) r2:4(int!null) x:5(int!null) y:6(int) s:7(string) d:8(decimal!null) 1672 ├── key: (1) 1673 ├── fd: (1)-->(2-4), (5)-->(6-8), (7,8)~~>(5,6), (4)==(5), (5)==(4) 1674 ├── prune: (1-3,6-8) 1675 ├── interesting orderings: (+1) (+3,+1) (+4,+1) (+5) (-7,+8,+5) 1676 ├── multiplicity: left-rows(one-or-zero), right-rows(zero-or-more) 1677 ├── scan fk 1678 │ ├── columns: k:1(int!null) v:2(int) r1:3(int!null) r2:4(int) 1679 │ ├── key: (1) 1680 │ ├── fd: (1)-->(2-4) 1681 │ ├── prune: (1-4) 1682 │ └── interesting orderings: (+1) (+3,+1) (+4,+1) 1683 ├── scan xysd 1684 │ ├── columns: x:5(int!null) y:6(int) s:7(string) d:8(decimal!null) 1685 │ ├── key: (5) 1686 │ ├── fd: (5)-->(6-8), (7,8)~~>(5,6) 1687 │ ├── prune: (5-8) 1688 │ └── interesting orderings: (+5) (-7,+8,+5) 1689 └── filters 1690 └── eq [type=bool, outer=(4,5), constraints=(/4: (/NULL - ]; /5: (/NULL - ]), fd=(4)==(5), (5)==(4)] 1691 ├── variable: x:5 [type=int] 1692 └── variable: r2:4 [type=int] 1693 1694 # Cross join. Rows from fk are guaranteed matches because the not-null foreign 1695 # key implies that xysd has at least one row whenever fk does. 1696 norm 1697 SELECT * FROM fk CROSS JOIN xysd 1698 ---- 1699 inner-join (cross) 1700 ├── columns: k:1(int!null) v:2(int) r1:3(int!null) r2:4(int) x:5(int!null) y:6(int) s:7(string) d:8(decimal!null) 1701 ├── key: (1,5) 1702 ├── fd: (1)-->(2-4), (5)-->(6-8), (7,8)~~>(5,6) 1703 ├── prune: (1-8) 1704 ├── interesting orderings: (+1) (+3,+1) (+4,+1) (+5) (-7,+8,+5) 1705 ├── multiplicity: left-rows(one-or-more), right-rows(zero-or-more) 1706 ├── scan fk 1707 │ ├── columns: k:1(int!null) v:2(int) r1:3(int!null) r2:4(int) 1708 │ ├── key: (1) 1709 │ ├── fd: (1)-->(2-4) 1710 │ ├── prune: (1-4) 1711 │ └── interesting orderings: (+1) (+3,+1) (+4,+1) 1712 ├── scan xysd 1713 │ ├── columns: x:5(int!null) y:6(int) s:7(string) d:8(decimal!null) 1714 │ ├── key: (5) 1715 │ ├── fd: (5)-->(6-8), (7,8)~~>(5,6) 1716 │ ├── prune: (5-8) 1717 │ └── interesting orderings: (+5) (-7,+8,+5) 1718 └── filters (true) 1719 1720 # LeftJoin case with a not-null foreign key. Since fk rows are all guaranteed 1721 # exactly one match, xysd will not be null-extended and the LeftJoin can 1722 # therefore be simplified. 1723 norm 1724 SELECT * FROM fk LEFT JOIN xysd ON x = r1 1725 ---- 1726 inner-join (hash) 1727 ├── columns: k:1(int!null) v:2(int) r1:3(int!null) r2:4(int) x:5(int!null) y:6(int) s:7(string) d:8(decimal!null) 1728 ├── key: (1) 1729 ├── fd: (1)-->(2-4), (5)-->(6-8), (7,8)~~>(5,6), (3)==(5), (5)==(3) 1730 ├── prune: (1,2,4,6-8) 1731 ├── interesting orderings: (+1) (+3,+1) (+4,+1) (+5) (-7,+8,+5) 1732 ├── multiplicity: left-rows(exactly-one), right-rows(zero-or-more) 1733 ├── scan fk 1734 │ ├── columns: k:1(int!null) v:2(int) r1:3(int!null) r2:4(int) 1735 │ ├── key: (1) 1736 │ ├── fd: (1)-->(2-4) 1737 │ ├── prune: (1-4) 1738 │ └── interesting orderings: (+1) (+3,+1) (+4,+1) 1739 ├── scan xysd 1740 │ ├── columns: x:5(int!null) y:6(int) s:7(string) d:8(decimal!null) 1741 │ ├── key: (5) 1742 │ ├── fd: (5)-->(6-8), (7,8)~~>(5,6) 1743 │ ├── prune: (5-8) 1744 │ └── interesting orderings: (+5) (-7,+8,+5) 1745 └── filters 1746 └── eq [type=bool, outer=(3,5), constraints=(/3: (/NULL - ]; /5: (/NULL - ]), fd=(3)==(5), (5)==(3)] 1747 ├── variable: x:5 [type=int] 1748 └── variable: r1:3 [type=int] 1749 1750 1751 # LeftJoin case with a nullable foreign key. The LeftJoin cannot be simplified 1752 # because a nullable foreign key is not guaranteed matches. 1753 norm 1754 SELECT * FROM fk LEFT JOIN xysd ON x = r2 1755 ---- 1756 left-join (hash) 1757 ├── columns: k:1(int!null) v:2(int) r1:3(int!null) r2:4(int) x:5(int) y:6(int) s:7(string) d:8(decimal) 1758 ├── key: (1) 1759 ├── fd: (1)-->(2-8), (5)-->(6-8), (7,8)~~>(5,6) 1760 ├── prune: (1-3,6-8) 1761 ├── reject-nulls: (5-8) 1762 ├── interesting orderings: (+1) (+3,+1) (+4,+1) (+5) (-7,+8,+5) 1763 ├── multiplicity: left-rows(exactly-one), right-rows(zero-or-more) 1764 ├── scan fk 1765 │ ├── columns: k:1(int!null) v:2(int) r1:3(int!null) r2:4(int) 1766 │ ├── key: (1) 1767 │ ├── fd: (1)-->(2-4) 1768 │ ├── prune: (1-4) 1769 │ └── interesting orderings: (+1) (+3,+1) (+4,+1) 1770 ├── scan xysd 1771 │ ├── columns: x:5(int!null) y:6(int) s:7(string) d:8(decimal!null) 1772 │ ├── key: (5) 1773 │ ├── fd: (5)-->(6-8), (7,8)~~>(5,6) 1774 │ ├── prune: (5-8) 1775 │ └── interesting orderings: (+5) (-7,+8,+5) 1776 └── filters 1777 └── eq [type=bool, outer=(4,5), constraints=(/4: (/NULL - ]; /5: (/NULL - ]), fd=(4)==(5), (5)==(4)] 1778 ├── variable: x:5 [type=int] 1779 └── variable: r2:4 [type=int] 1780 1781 # FullJoin with equality between key columns. The FullJoin adds back any rows 1782 # that are filtered out, and the equality between key columns ensures that no 1783 # rows are duplicated. Note that both sides may be null-extended. 1784 norm 1785 SELECT * FROM mn FULL JOIN xysd ON m = x 1786 ---- 1787 full-join (hash) 1788 ├── columns: m:1(int) n:2(int) x:3(int) y:4(int) s:5(string) d:6(decimal) 1789 ├── key: (1,3) 1790 ├── fd: (1)-->(2), (2)~~>(1), (3)-->(4-6), (5,6)~~>(3,4) 1791 ├── prune: (2,4-6) 1792 ├── reject-nulls: (1-6) 1793 ├── interesting orderings: (+1) (+2,+1) (+3) (-5,+6,+3) 1794 ├── multiplicity: left-rows(exactly-one), right-rows(exactly-one) 1795 ├── scan mn 1796 │ ├── columns: m:1(int!null) n:2(int) 1797 │ ├── key: (1) 1798 │ ├── fd: (1)-->(2), (2)~~>(1) 1799 │ ├── prune: (1,2) 1800 │ └── interesting orderings: (+1) (+2,+1) 1801 ├── scan xysd 1802 │ ├── columns: x:3(int!null) y:4(int) s:5(string) d:6(decimal!null) 1803 │ ├── key: (3) 1804 │ ├── fd: (3)-->(4-6), (5,6)~~>(3,4) 1805 │ ├── prune: (3-6) 1806 │ └── interesting orderings: (+3) (-5,+6,+3) 1807 └── filters 1808 └── eq [type=bool, outer=(1,3), constraints=(/1: (/NULL - ]; /3: (/NULL - ]), fd=(1)==(3), (3)==(1)] 1809 ├── variable: m:1 [type=int] 1810 └── variable: x:3 [type=int] 1811 1812 # Self-join case. Since the condition is equating a key column with itself, 1813 # every row from both inputs is guaranteed to be included in the join output 1814 # exactly once. 1815 norm 1816 SELECT * FROM xysd INNER JOIN xysd AS a ON xysd.x = a.x 1817 ---- 1818 inner-join (hash) 1819 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) x:5(int!null) y:6(int) s:7(string) d:8(decimal!null) 1820 ├── key: (5) 1821 ├── fd: (1)-->(2-4), (3,4)~~>(1,2), (5)-->(6-8), (7,8)~~>(5,6), (1)==(5), (5)==(1) 1822 ├── prune: (2-4,6-8) 1823 ├── interesting orderings: (+1) (-3,+4,+1) (+5) (-7,+8,+5) 1824 ├── multiplicity: left-rows(exactly-one), right-rows(exactly-one) 1825 ├── scan xysd 1826 │ ├── columns: xysd.x:1(int!null) xysd.y:2(int) xysd.s:3(string) xysd.d:4(decimal!null) 1827 │ ├── key: (1) 1828 │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 1829 │ ├── prune: (1-4) 1830 │ └── interesting orderings: (+1) (-3,+4,+1) 1831 ├── scan a 1832 │ ├── columns: a.x:5(int!null) a.y:6(int) a.s:7(string) a.d:8(decimal!null) 1833 │ ├── key: (5) 1834 │ ├── fd: (5)-->(6-8), (7,8)~~>(5,6) 1835 │ ├── prune: (5-8) 1836 │ └── interesting orderings: (+5) (-7,+8,+5) 1837 └── filters 1838 └── eq [type=bool, outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)] 1839 ├── variable: xysd.x:1 [type=int] 1840 └── variable: a.x:5 [type=int] 1841 1842 # Case with a values cross join in the input of an InnerJoin. 1843 norm 1844 SELECT * FROM 1845 fk INNER JOIN (SELECT * FROM xysd CROSS JOIN (VALUES (1), (2))) ON r1 = x 1846 ---- 1847 inner-join (hash) 1848 ├── columns: k:1(int!null) v:2(int) r1:3(int!null) r2:4(int) x:5(int!null) y:6(int) s:7(string) d:8(decimal!null) column1:9(int!null) 1849 ├── fd: (1)-->(2-4), (5)-->(6-8), (7,8)~~>(5,6), (3)==(5), (5)==(3) 1850 ├── prune: (1,2,4,6-9) 1851 ├── interesting orderings: (+1) (+3,+1) (+4,+1) (+5) (-7,+8,+5) 1852 ├── multiplicity: left-rows(one-or-more), right-rows(zero-or-more) 1853 ├── scan fk 1854 │ ├── columns: k:1(int!null) v:2(int) r1:3(int!null) r2:4(int) 1855 │ ├── key: (1) 1856 │ ├── fd: (1)-->(2-4) 1857 │ ├── prune: (1-4) 1858 │ └── interesting orderings: (+1) (+3,+1) (+4,+1) 1859 ├── inner-join (cross) 1860 │ ├── columns: x:5(int!null) y:6(int) s:7(string) d:8(decimal!null) column1:9(int!null) 1861 │ ├── fd: (5)-->(6-8), (7,8)~~>(5,6) 1862 │ ├── prune: (5-9) 1863 │ ├── interesting orderings: (+5) (-7,+8,+5) 1864 │ ├── multiplicity: left-rows(one-or-more), right-rows(zero-or-more) 1865 │ ├── scan xysd 1866 │ │ ├── columns: x:5(int!null) y:6(int) s:7(string) d:8(decimal!null) 1867 │ │ ├── key: (5) 1868 │ │ ├── fd: (5)-->(6-8), (7,8)~~>(5,6) 1869 │ │ ├── prune: (5-8) 1870 │ │ └── interesting orderings: (+5) (-7,+8,+5) 1871 │ ├── values 1872 │ │ ├── columns: column1:9(int!null) 1873 │ │ ├── cardinality: [2 - 2] 1874 │ │ ├── prune: (9) 1875 │ │ ├── tuple [type=tuple{int}] 1876 │ │ │ └── const: 1 [type=int] 1877 │ │ └── tuple [type=tuple{int}] 1878 │ │ └── const: 2 [type=int] 1879 │ └── filters (true) 1880 └── filters 1881 └── eq [type=bool, outer=(3,5), constraints=(/3: (/NULL - ]; /5: (/NULL - ]), fd=(3)==(5), (5)==(3)] 1882 ├── variable: r1:3 [type=int] 1883 └── variable: x:5 [type=int] 1884 1885 # Case with a self-join in the input of an InnerJoin. 1886 norm 1887 SELECT * FROM fk 1888 INNER JOIN (SELECT * FROM xysd INNER JOIN xysd AS a ON xysd.x = a.x) f(x) ON r1 = f.x 1889 ---- 1890 inner-join (hash) 1891 ├── columns: k:1(int!null) v:2(int) r1:3(int!null) r2:4(int) x:5(int!null) y:6(int) s:7(string) d:8(decimal!null) x:9(int!null) y:10(int) s:11(string) d:12(decimal!null) 1892 ├── key: (1) 1893 ├── fd: (1)-->(2-4), (5)-->(6-8), (7,8)~~>(5,6), (9)-->(10-12), (11,12)~~>(9,10), (5)==(3,9), (9)==(3,5), (3)==(5,9) 1894 ├── prune: (1,2,4,6-8,10-12) 1895 ├── interesting orderings: (+1) (+3,+1) (+4,+1) (+5) (-7,+8,+5) (+9) (-11,+12,+9) 1896 ├── multiplicity: left-rows(one-or-zero), right-rows(zero-or-more) 1897 ├── scan fk 1898 │ ├── columns: k:1(int!null) v:2(int) r1:3(int!null) r2:4(int) 1899 │ ├── key: (1) 1900 │ ├── fd: (1)-->(2-4) 1901 │ ├── prune: (1-4) 1902 │ └── interesting orderings: (+1) (+3,+1) (+4,+1) 1903 ├── inner-join (hash) 1904 │ ├── columns: xysd.x:5(int!null) xysd.y:6(int) xysd.s:7(string) xysd.d:8(decimal!null) a.x:9(int!null) a.y:10(int) a.s:11(string) a.d:12(decimal!null) 1905 │ ├── key: (9) 1906 │ ├── fd: (5)-->(6-8), (7,8)~~>(5,6), (9)-->(10-12), (11,12)~~>(9,10), (5)==(9), (9)==(5) 1907 │ ├── prune: (6-8,10-12) 1908 │ ├── interesting orderings: (+5) (-7,+8,+5) (+9) (-11,+12,+9) 1909 │ ├── multiplicity: left-rows(exactly-one), right-rows(exactly-one) 1910 │ ├── scan xysd 1911 │ │ ├── columns: xysd.x:5(int!null) xysd.y:6(int) xysd.s:7(string) xysd.d:8(decimal!null) 1912 │ │ ├── key: (5) 1913 │ │ ├── fd: (5)-->(6-8), (7,8)~~>(5,6) 1914 │ │ ├── prune: (5-8) 1915 │ │ └── interesting orderings: (+5) (-7,+8,+5) 1916 │ ├── scan a 1917 │ │ ├── columns: a.x:9(int!null) a.y:10(int) a.s:11(string) a.d:12(decimal!null) 1918 │ │ ├── key: (9) 1919 │ │ ├── fd: (9)-->(10-12), (11,12)~~>(9,10) 1920 │ │ ├── prune: (9-12) 1921 │ │ └── interesting orderings: (+9) (-11,+12,+9) 1922 │ └── filters 1923 │ └── eq [type=bool, outer=(5,9), constraints=(/5: (/NULL - ]; /9: (/NULL - ]), fd=(5)==(9), (9)==(5)] 1924 │ ├── variable: xysd.x:5 [type=int] 1925 │ └── variable: a.x:9 [type=int] 1926 └── filters 1927 └── eq [type=bool, outer=(3,5), constraints=(/3: (/NULL - ]; /5: (/NULL - ]), fd=(3)==(5), (5)==(3)] 1928 ├── variable: r1:3 [type=int] 1929 └── variable: xysd.x:5 [type=int] 1930 1931 # Case with an equality with a synthesized column. 1932 norm 1933 SELECT * FROM mn LEFT JOIN xysd ON y = (n * 2) 1934 ---- 1935 project 1936 ├── columns: m:1(int!null) n:2(int) x:3(int) y:4(int) s:5(string) d:6(decimal) 1937 ├── key: (1,3) 1938 ├── fd: (1)-->(2), (2)~~>(1), (3)-->(4-6), (5,6)~~>(3,4) 1939 ├── prune: (1-6) 1940 ├── reject-nulls: (3-6) 1941 ├── interesting orderings: (+1) (+2,+1) (+3) (-5,+6,+3) 1942 └── left-join (hash) 1943 ├── columns: m:1(int!null) n:2(int) x:3(int) y:4(int) s:5(string) d:6(decimal) column7:7(int) 1944 ├── key: (1,3) 1945 ├── fd: (1)-->(2), (2)~~>(1), (2)-->(7), (3)-->(4-6), (5,6)~~>(3,4) 1946 ├── prune: (1-3,5,6) 1947 ├── reject-nulls: (3-6) 1948 ├── interesting orderings: (+1) (+2,+1) (+3) (-5,+6,+3) 1949 ├── multiplicity: left-rows(one-or-more), right-rows(zero-or-more) 1950 ├── project 1951 │ ├── columns: column7:7(int) m:1(int!null) n:2(int) 1952 │ ├── key: (1) 1953 │ ├── fd: (1)-->(2), (2)~~>(1), (2)-->(7) 1954 │ ├── prune: (1,2,7) 1955 │ ├── interesting orderings: (+1) (+2,+1) 1956 │ ├── scan mn 1957 │ │ ├── columns: m:1(int!null) n:2(int) 1958 │ │ ├── key: (1) 1959 │ │ ├── fd: (1)-->(2), (2)~~>(1) 1960 │ │ ├── prune: (1,2) 1961 │ │ └── interesting orderings: (+1) (+2,+1) 1962 │ └── projections 1963 │ └── mult [as=column7:7, type=int, outer=(2)] 1964 │ ├── variable: n:2 [type=int] 1965 │ └── const: 2 [type=int] 1966 ├── scan xysd 1967 │ ├── columns: x:3(int!null) y:4(int) s:5(string) d:6(decimal!null) 1968 │ ├── key: (3) 1969 │ ├── fd: (3)-->(4-6), (5,6)~~>(3,4) 1970 │ ├── prune: (3-6) 1971 │ └── interesting orderings: (+3) (-5,+6,+3) 1972 └── filters 1973 └── eq [type=bool, outer=(4,7), constraints=(/4: (/NULL - ]; /7: (/NULL - ]), fd=(4)==(7), (7)==(4)] 1974 ├── variable: column7:7 [type=int] 1975 └── variable: y:4 [type=int] 1976 1977 # Case with columns that don't come from base tables. 1978 norm 1979 SELECT * FROM (SELECT * FROM uv UNION (SELECT * FROM uv)) f(v1, v2) INNER JOIN xysd ON v2 = x 1980 ---- 1981 inner-join (hash) 1982 ├── columns: v1:7(int) v2:8(int!null) x:9(int!null) y:10(int) s:11(string) d:12(decimal!null) 1983 ├── key: (7,9) 1984 ├── fd: (9)-->(10-12), (11,12)~~>(9,10), (8)==(9), (9)==(8) 1985 ├── prune: (10-12) 1986 ├── interesting orderings: (+9) (-11,+12,+9) 1987 ├── multiplicity: left-rows(one-or-zero), right-rows(zero-or-more) 1988 ├── union 1989 │ ├── columns: u:7(int) v:8(int!null) 1990 │ ├── left columns: uv.u:1(int) uv.v:2(int) 1991 │ ├── right columns: uv.u:4(int) uv.v:5(int) 1992 │ ├── key: (7,8) 1993 │ ├── scan uv 1994 │ │ ├── columns: uv.u:1(int) uv.v:2(int!null) 1995 │ │ └── prune: (1,2) 1996 │ └── scan uv 1997 │ ├── columns: uv.u:4(int) uv.v:5(int!null) 1998 │ └── prune: (4,5) 1999 ├── scan xysd 2000 │ ├── columns: x:9(int!null) y:10(int) s:11(string) d:12(decimal!null) 2001 │ ├── key: (9) 2002 │ ├── fd: (9)-->(10-12), (11,12)~~>(9,10) 2003 │ ├── prune: (9-12) 2004 │ └── interesting orderings: (+9) (-11,+12,+9) 2005 └── filters 2006 └── eq [type=bool, outer=(8,9), constraints=(/8: (/NULL - ]; /9: (/NULL - ]), fd=(8)==(9), (9)==(8)] 2007 ├── variable: v:8 [type=int] 2008 └── variable: x:9 [type=int] 2009 2010 # Self-join case with different columns. 2011 norm 2012 SELECT * FROM xysd INNER JOIN xysd AS a ON xysd.x = a.y 2013 ---- 2014 inner-join (hash) 2015 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) x:5(int!null) y:6(int!null) s:7(string) d:8(decimal!null) 2016 ├── key: (5) 2017 ├── fd: (1)-->(2-4), (3,4)~~>(1,2), (5)-->(6-8), (7,8)~~>(5,6), (1)==(6), (6)==(1) 2018 ├── prune: (2-5,7,8) 2019 ├── interesting orderings: (+1) (-3,+4,+1) (+5) (-7,+8,+5) 2020 ├── multiplicity: left-rows(zero-or-more), right-rows(one-or-zero) 2021 ├── scan xysd 2022 │ ├── columns: xysd.x:1(int!null) xysd.y:2(int) xysd.s:3(string) xysd.d:4(decimal!null) 2023 │ ├── key: (1) 2024 │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 2025 │ ├── prune: (1-4) 2026 │ └── interesting orderings: (+1) (-3,+4,+1) 2027 ├── scan a 2028 │ ├── columns: a.x:5(int!null) a.y:6(int) a.s:7(string) a.d:8(decimal!null) 2029 │ ├── key: (5) 2030 │ ├── fd: (5)-->(6-8), (7,8)~~>(5,6) 2031 │ ├── prune: (5-8) 2032 │ └── interesting orderings: (+5) (-7,+8,+5) 2033 └── filters 2034 └── eq [type=bool, outer=(1,6), constraints=(/1: (/NULL - ]; /6: (/NULL - ]), fd=(1)==(6), (6)==(1)] 2035 ├── variable: xysd.x:1 [type=int] 2036 └── variable: a.y:6 [type=int] 2037 2038 # Case with an equality between a not-null foreign key and an unreferenced 2039 # column. 2040 norm 2041 SELECT * FROM fk INNER JOIN xysd ON r1 = y 2042 ---- 2043 inner-join (hash) 2044 ├── columns: k:1(int!null) v:2(int) r1:3(int!null) r2:4(int) x:5(int!null) y:6(int!null) s:7(string) d:8(decimal!null) 2045 ├── key: (1,5) 2046 ├── fd: (1)-->(2-4), (5)-->(6-8), (7,8)~~>(5,6), (3)==(6), (6)==(3) 2047 ├── prune: (1,2,4,5,7,8) 2048 ├── interesting orderings: (+1) (+3,+1) (+4,+1) (+5) (-7,+8,+5) 2049 ├── scan fk 2050 │ ├── columns: k:1(int!null) v:2(int) r1:3(int!null) r2:4(int) 2051 │ ├── key: (1) 2052 │ ├── fd: (1)-->(2-4) 2053 │ ├── prune: (1-4) 2054 │ └── interesting orderings: (+1) (+3,+1) (+4,+1) 2055 ├── scan xysd 2056 │ ├── columns: x:5(int!null) y:6(int) s:7(string) d:8(decimal!null) 2057 │ ├── key: (5) 2058 │ ├── fd: (5)-->(6-8), (7,8)~~>(5,6) 2059 │ ├── prune: (5-8) 2060 │ └── interesting orderings: (+5) (-7,+8,+5) 2061 └── filters 2062 └── eq [type=bool, outer=(3,6), constraints=(/3: (/NULL - ]; /6: (/NULL - ]), fd=(3)==(6), (6)==(3)] 2063 ├── variable: r1:3 [type=int] 2064 └── variable: y:6 [type=int] 2065 2066 # Case where left table has a foreign key that references a table that isn't 2067 # from the right input. 2068 norm 2069 SELECT * FROM fk INNER JOIN mn ON k = m 2070 ---- 2071 inner-join (hash) 2072 ├── columns: k:1(int!null) v:2(int) r1:3(int!null) r2:4(int) m:5(int!null) n:6(int) 2073 ├── key: (5) 2074 ├── fd: (1)-->(2-4), (5)-->(6), (6)~~>(5), (1)==(5), (5)==(1) 2075 ├── prune: (2-4,6) 2076 ├── interesting orderings: (+1) (+3,+1) (+4,+1) (+5) (+6,+5) 2077 ├── multiplicity: left-rows(one-or-zero), right-rows(one-or-zero) 2078 ├── scan fk 2079 │ ├── columns: k:1(int!null) v:2(int) r1:3(int!null) r2:4(int) 2080 │ ├── key: (1) 2081 │ ├── fd: (1)-->(2-4) 2082 │ ├── prune: (1-4) 2083 │ └── interesting orderings: (+1) (+3,+1) (+4,+1) 2084 ├── scan mn 2085 │ ├── columns: m:5(int!null) n:6(int) 2086 │ ├── key: (5) 2087 │ ├── fd: (5)-->(6), (6)~~>(5) 2088 │ ├── prune: (5,6) 2089 │ └── interesting orderings: (+5) (+6,+5) 2090 └── filters 2091 └── eq [type=bool, outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)] 2092 ├── variable: k:1 [type=int] 2093 └── variable: m:5 [type=int] 2094 2095 # Case with a match-simple foreign key with one nullable column. 2096 norm 2097 SELECT * 2098 FROM ref 2099 INNER JOIN abc 2100 ON (r1, r2, r3) = (a, b, c) 2101 ---- 2102 inner-join (hash) 2103 ├── columns: r1:1(int!null) r2:2(int!null) r3:3(int!null) a:5(int!null) b:6(int!null) c:7(int!null) 2104 ├── fd: (1)==(5), (5)==(1), (2)==(6), (6)==(2), (3)==(7), (7)==(3) 2105 ├── interesting orderings: (+1,+2,+3) (+5,+6,+7) 2106 ├── multiplicity: left-rows(one-or-zero), right-rows(zero-or-more) 2107 ├── scan ref 2108 │ ├── columns: r1:1(int!null) r2:2(int) r3:3(int!null) 2109 │ ├── prune: (1-3) 2110 │ └── interesting orderings: (+1,+2,+3) 2111 ├── scan abc 2112 │ ├── columns: a:5(int!null) b:6(int!null) c:7(int!null) 2113 │ ├── key: (5-7) 2114 │ ├── prune: (5-7) 2115 │ └── interesting orderings: (+5,+6,+7) 2116 └── filters 2117 ├── eq [type=bool, outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)] 2118 │ ├── variable: r1:1 [type=int] 2119 │ └── variable: a:5 [type=int] 2120 ├── eq [type=bool, outer=(2,6), constraints=(/2: (/NULL - ]; /6: (/NULL - ]), fd=(2)==(6), (6)==(2)] 2121 │ ├── variable: r2:2 [type=int] 2122 │ └── variable: b:6 [type=int] 2123 └── eq [type=bool, outer=(3,7), constraints=(/3: (/NULL - ]; /7: (/NULL - ]), fd=(3)==(7), (7)==(3)] 2124 ├── variable: r3:3 [type=int] 2125 └── variable: c:7 [type=int] 2126 2127 # Case with a not-null multi-column foreign key. 2128 norm 2129 SELECT * 2130 FROM (SELECT r1, r2, r3 FROM ref WHERE r2 IS NOT NULL) 2131 INNER JOIN abc 2132 ON (r1, r2, r3) = (a, b, c) 2133 ---- 2134 inner-join (hash) 2135 ├── columns: r1:1(int!null) r2:2(int!null) r3:3(int!null) a:5(int!null) b:6(int!null) c:7(int!null) 2136 ├── fd: (1)==(5), (5)==(1), (2)==(6), (6)==(2), (3)==(7), (7)==(3) 2137 ├── interesting orderings: (+1,+2,+3) (+5,+6,+7) 2138 ├── multiplicity: left-rows(exactly-one), right-rows(zero-or-more) 2139 ├── select 2140 │ ├── columns: r1:1(int!null) r2:2(int!null) r3:3(int!null) 2141 │ ├── prune: (1,3) 2142 │ ├── interesting orderings: (+1,+2,+3) 2143 │ ├── scan ref 2144 │ │ ├── columns: r1:1(int!null) r2:2(int) r3:3(int!null) 2145 │ │ ├── prune: (1-3) 2146 │ │ └── interesting orderings: (+1,+2,+3) 2147 │ └── filters 2148 │ └── is-not [type=bool, outer=(2), constraints=(/2: (/NULL - ]; tight)] 2149 │ ├── variable: r2:2 [type=int] 2150 │ └── null [type=unknown] 2151 ├── scan abc 2152 │ ├── columns: a:5(int!null) b:6(int!null) c:7(int!null) 2153 │ ├── key: (5-7) 2154 │ ├── prune: (5-7) 2155 │ └── interesting orderings: (+5,+6,+7) 2156 └── filters 2157 ├── eq [type=bool, outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)] 2158 │ ├── variable: r1:1 [type=int] 2159 │ └── variable: a:5 [type=int] 2160 ├── eq [type=bool, outer=(2,6), constraints=(/2: (/NULL - ]; /6: (/NULL - ]), fd=(2)==(6), (6)==(2)] 2161 │ ├── variable: r2:2 [type=int] 2162 │ └── variable: b:6 [type=int] 2163 └── eq [type=bool, outer=(3,7), constraints=(/3: (/NULL - ]; /7: (/NULL - ]), fd=(3)==(7), (7)==(3)] 2164 ├── variable: r3:3 [type=int] 2165 └── variable: c:7 [type=int]