github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/memo/testdata/logprops/project (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 kuv (k INT PRIMARY KEY, u FLOAT, v STRING) 7 ---- 8 9 exec-ddl 10 CREATE TABLE ab (a INT, b INT, UNIQUE (a, b)) 11 ---- 12 13 build 14 SELECT y, x+1 AS a, 1 AS b, x FROM xysd 15 ---- 16 project 17 ├── columns: y:2(int) a:5(int!null) b:6(int!null) x:1(int!null) 18 ├── key: (1) 19 ├── fd: ()-->(6), (1)-->(2,5) 20 ├── prune: (1,2,5,6) 21 ├── interesting orderings: (+1) 22 ├── scan xysd 23 │ ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 24 │ ├── key: (1) 25 │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 26 │ ├── prune: (1-4) 27 │ └── interesting orderings: (+1) (-3,+4,+1) 28 └── projections 29 ├── plus [as=a:5, type=int, outer=(1)] 30 │ ├── variable: x:1 [type=int] 31 │ └── const: 1 [type=int] 32 └── const: 1 [as=b:6, type=int] 33 34 build 35 SELECT s FROM xysd 36 ---- 37 project 38 ├── columns: s:3(string) 39 ├── prune: (3) 40 ├── interesting orderings: (-3) 41 └── scan xysd 42 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 43 ├── key: (1) 44 ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 45 ├── prune: (1-4) 46 └── interesting orderings: (+1) (-3,+4,+1) 47 48 # Propagate outer columns. 49 build 50 SELECT * FROM xysd WHERE (SELECT (SELECT y) FROM kuv WHERE k=x) > 5 51 ---- 52 select 53 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 54 ├── key: (1) 55 ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 56 ├── prune: (3,4) 57 ├── interesting orderings: (+1) (-3,+4,+1) 58 ├── scan xysd 59 │ ├── columns: x:1(int!null) xysd.y:2(int) s:3(string) d:4(decimal!null) 60 │ ├── key: (1) 61 │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 62 │ ├── prune: (1-4) 63 │ └── interesting orderings: (+1) (-3,+4,+1) 64 └── filters 65 └── gt [type=bool, outer=(1,2), correlated-subquery] 66 ├── subquery [type=int] 67 │ └── max1-row 68 │ ├── columns: y:9(int) 69 │ ├── error: "more than one row returned by a subquery used as an expression" 70 │ ├── outer: (1,2) 71 │ ├── cardinality: [0 - 1] 72 │ ├── key: () 73 │ ├── fd: ()-->(9) 74 │ └── project 75 │ ├── columns: y:9(int) 76 │ ├── outer: (1,2) 77 │ ├── cardinality: [0 - 1] 78 │ ├── key: () 79 │ ├── fd: ()-->(9) 80 │ ├── prune: (9) 81 │ ├── select 82 │ │ ├── columns: k:5(int!null) u:6(float) v:7(string) 83 │ │ ├── outer: (1) 84 │ │ ├── cardinality: [0 - 1] 85 │ │ ├── key: () 86 │ │ ├── fd: ()-->(5-7) 87 │ │ ├── prune: (6,7) 88 │ │ ├── interesting orderings: (+5) 89 │ │ ├── scan kuv 90 │ │ │ ├── columns: k:5(int!null) u:6(float) v:7(string) 91 │ │ │ ├── key: (5) 92 │ │ │ ├── fd: (5)-->(6,7) 93 │ │ │ ├── prune: (5-7) 94 │ │ │ └── interesting orderings: (+5) 95 │ │ └── filters 96 │ │ └── eq [type=bool, outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)] 97 │ │ ├── variable: k:5 [type=int] 98 │ │ └── variable: x:1 [type=int] 99 │ └── projections 100 │ └── subquery [as=y:9, type=int, outer=(2), correlated-subquery] 101 │ └── max1-row 102 │ ├── columns: y:8(int) 103 │ ├── error: "more than one row returned by a subquery used as an expression" 104 │ ├── outer: (2) 105 │ ├── cardinality: [1 - 1] 106 │ ├── key: () 107 │ ├── fd: ()-->(8) 108 │ └── project 109 │ ├── columns: y:8(int) 110 │ ├── outer: (2) 111 │ ├── cardinality: [1 - 1] 112 │ ├── key: () 113 │ ├── fd: ()-->(8) 114 │ ├── prune: (8) 115 │ ├── values 116 │ │ ├── cardinality: [1 - 1] 117 │ │ ├── key: () 118 │ │ └── tuple [type=tuple] 119 │ └── projections 120 │ └── variable: xysd.y:2 [as=y:8, type=int, outer=(2)] 121 └── const: 5 [type=int] 122 123 # Pass through cardinality. 124 build 125 SELECT x, y FROM (SELECT * FROM xysd LIMIT 10) 126 ---- 127 project 128 ├── columns: x:1(int!null) y:2(int) 129 ├── cardinality: [0 - 10] 130 ├── key: (1) 131 ├── fd: (1)-->(2) 132 ├── prune: (1,2) 133 ├── interesting orderings: (+1) 134 └── limit 135 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 136 ├── cardinality: [0 - 10] 137 ├── key: (1) 138 ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 139 ├── prune: (1-4) 140 ├── interesting orderings: (+1) (-3,+4,+1) 141 ├── scan xysd 142 │ ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 143 │ ├── key: (1) 144 │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 145 │ ├── limit hint: 10.00 146 │ ├── prune: (1-4) 147 │ └── interesting orderings: (+1) (-3,+4,+1) 148 └── const: 10 [type=int] 149 150 # Constant null and not-null columns. 151 build 152 SELECT 1 AS a, 'foo' AS b, NULL AS c, 1::decimal + NULL AS d, NULL::STRING AS e FROM xysd 153 ---- 154 project 155 ├── columns: a:5(int!null) b:6(string!null) c:7(unknown) d:7(unknown) e:8(string) 156 ├── fd: ()-->(5-8) 157 ├── prune: (5-8) 158 ├── scan xysd 159 │ ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 160 │ ├── key: (1) 161 │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 162 │ ├── prune: (1-4) 163 │ └── interesting orderings: (+1) (-3,+4,+1) 164 └── projections 165 ├── const: 1 [as=a:5, type=int] 166 ├── const: 'foo' [as=b:6, type=string] 167 ├── null [as=c:7, type=unknown] 168 └── cast: STRING [as=e:8, type=string] 169 └── null [type=unknown] 170 171 # Project constant over input with no needed columns and ensure that there is 172 # no key on the output (because it will have duplicates). 173 opt 174 SELECT 1 FROM (SELECT x FROM xysd) 175 ---- 176 project 177 ├── columns: "?column?":5(int!null) 178 ├── fd: ()-->(5) 179 ├── prune: (5) 180 ├── scan xysd@secondary 181 └── projections 182 └── const: 1 [as="?column?":5, type=int] 183 184 # Project simple variable reference after constant folding; should be not-null 185 # if the column it refers to is not-null. 186 norm 187 SELECT CASE WHEN true THEN x END FROM xysd 188 ---- 189 project 190 ├── columns: case:5(int!null) 191 ├── key: (5) 192 ├── prune: (5) 193 ├── scan xysd 194 │ ├── columns: x:1(int!null) 195 │ ├── key: (1) 196 │ ├── prune: (1) 197 │ └── interesting orderings: (+1) 198 └── projections 199 └── variable: x:1 [as=case:5, type=int, outer=(1)] 200 201 # Project correlated subquery. 202 build 203 SELECT k, (SELECT y FROM xysd WHERE x=k) FROM kuv 204 ---- 205 project 206 ├── columns: k:1(int!null) y:8(int) 207 ├── key: (1) 208 ├── fd: (1)-->(8) 209 ├── prune: (1,8) 210 ├── interesting orderings: (+1) 211 ├── scan kuv 212 │ ├── columns: k:1(int!null) u:2(float) v:3(string) 213 │ ├── key: (1) 214 │ ├── fd: (1)-->(2,3) 215 │ ├── prune: (1-3) 216 │ └── interesting orderings: (+1) 217 └── projections 218 └── subquery [as=y:8, type=int, outer=(1), correlated-subquery] 219 └── max1-row 220 ├── columns: xysd.y:5(int) 221 ├── error: "more than one row returned by a subquery used as an expression" 222 ├── outer: (1) 223 ├── cardinality: [0 - 1] 224 ├── key: () 225 ├── fd: ()-->(5) 226 └── project 227 ├── columns: xysd.y:5(int) 228 ├── outer: (1) 229 ├── cardinality: [0 - 1] 230 ├── key: () 231 ├── fd: ()-->(5) 232 ├── prune: (5) 233 └── select 234 ├── columns: x:4(int!null) xysd.y:5(int) s:6(string) d:7(decimal!null) 235 ├── outer: (1) 236 ├── cardinality: [0 - 1] 237 ├── key: () 238 ├── fd: ()-->(4-7) 239 ├── prune: (5-7) 240 ├── interesting orderings: (+4) (-6,+7,+4) 241 ├── scan xysd 242 │ ├── columns: x:4(int!null) xysd.y:5(int) s:6(string) d:7(decimal!null) 243 │ ├── key: (4) 244 │ ├── fd: (4)-->(5-7), (6,7)~~>(4,5) 245 │ ├── prune: (4-7) 246 │ └── interesting orderings: (+4) (-6,+7,+4) 247 └── filters 248 └── eq [type=bool, outer=(1,4), constraints=(/1: (/NULL - ]; /4: (/NULL - ]), fd=(1)==(4), (4)==(1)] 249 ├── variable: x:4 [type=int] 250 └── variable: k:1 [type=int] 251 252 # Project nested correlated subquery. 253 build 254 SELECT k, EXISTS(SELECT EXISTS(SELECT y FROM xysd WHERE x=k) FROM xysd) FROM kuv 255 ---- 256 project 257 ├── columns: k:1(int!null) exists:13(bool) 258 ├── key: (1) 259 ├── fd: (1)-->(13) 260 ├── prune: (1,13) 261 ├── interesting orderings: (+1) 262 ├── scan kuv 263 │ ├── columns: k:1(int!null) u:2(float) v:3(string) 264 │ ├── key: (1) 265 │ ├── fd: (1)-->(2,3) 266 │ ├── prune: (1-3) 267 │ └── interesting orderings: (+1) 268 └── projections 269 └── exists [as=exists:13, type=bool, outer=(1), correlated-subquery] 270 └── project 271 ├── columns: exists:12(bool) 272 ├── outer: (1) 273 ├── fd: ()-->(12) 274 ├── prune: (12) 275 ├── scan xysd 276 │ ├── columns: x:4(int!null) y:5(int) s:6(string) d:7(decimal!null) 277 │ ├── key: (4) 278 │ ├── fd: (4)-->(5-7), (6,7)~~>(4,5) 279 │ ├── prune: (4-7) 280 │ └── interesting orderings: (+4) (-6,+7,+4) 281 └── projections 282 └── exists [as=exists:12, type=bool, outer=(1), correlated-subquery] 283 └── project 284 ├── columns: y:9(int) 285 ├── outer: (1) 286 ├── cardinality: [0 - 1] 287 ├── key: () 288 ├── fd: ()-->(9) 289 ├── prune: (9) 290 └── select 291 ├── columns: x:8(int!null) y:9(int) s:10(string) d:11(decimal!null) 292 ├── outer: (1) 293 ├── cardinality: [0 - 1] 294 ├── key: () 295 ├── fd: ()-->(8-11) 296 ├── prune: (9-11) 297 ├── interesting orderings: (+8) (-10,+11,+8) 298 ├── scan xysd 299 │ ├── columns: x:8(int!null) y:9(int) s:10(string) d:11(decimal!null) 300 │ ├── key: (8) 301 │ ├── fd: (8)-->(9-11), (10,11)~~>(8,9) 302 │ ├── prune: (8-11) 303 │ └── interesting orderings: (+8) (-10,+11,+8) 304 └── filters 305 └── eq [type=bool, outer=(1,8), constraints=(/1: (/NULL - ]; /8: (/NULL - ]), fd=(1)==(8), (8)==(1)] 306 ├── variable: x:8 [type=int] 307 └── variable: k:1 [type=int] 308 309 # We have the FD: y --> y::TEXT. 310 build 311 SELECT y, y::TEXT FROM xysd 312 ---- 313 project 314 ├── columns: y:2(int) y:5(string) 315 ├── fd: (2)-->(5) 316 ├── prune: (2,5) 317 ├── scan xysd 318 │ ├── columns: x:1(int!null) xysd.y:2(int) s:3(string) d:4(decimal!null) 319 │ ├── key: (1) 320 │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 321 │ ├── prune: (1-4) 322 │ └── interesting orderings: (+1) (-3,+4,+1) 323 └── projections 324 └── cast: STRING [as=y:5, type=string, outer=(2)] 325 └── variable: xysd.y:2 [type=int] 326 327 # We don't have the FD: d --> d::TEXT because d is a composite type. 328 # For example, d=1 is equal to d=1.0 but d::TEXT differs. 329 build 330 SELECT d, d::TEXT FROM xysd 331 ---- 332 project 333 ├── columns: d:4(decimal!null) d:5(string!null) 334 ├── prune: (4,5) 335 ├── scan xysd 336 │ ├── columns: x:1(int!null) y:2(int) s:3(string) xysd.d:4(decimal!null) 337 │ ├── key: (1) 338 │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 339 │ ├── prune: (1-4) 340 │ └── interesting orderings: (+1) (-3,+4,+1) 341 └── projections 342 └── cast: STRING [as=d:5, type=string, outer=(4)] 343 └── variable: xysd.d:4 [type=decimal] 344 345 # We have the equality relation between the synthesized column and the column 346 # it refers to. 347 norm 348 SELECT x, CASE WHEN true THEN x END FROM xysd 349 ---- 350 project 351 ├── columns: x:1(int!null) case:5(int!null) 352 ├── key: (1) 353 ├── fd: (1)==(5), (5)==(1) 354 ├── prune: (1,5) 355 ├── interesting orderings: (+1) 356 ├── scan xysd 357 │ ├── columns: x:1(int!null) 358 │ ├── key: (1) 359 │ ├── prune: (1) 360 │ └── interesting orderings: (+1) 361 └── projections 362 └── variable: x:1 [as=case:5, type=int, outer=(1)] 363 364 365 # Verify that a,b form a key. 366 norm 367 SELECT a, b FROM ab WHERE a IS NOT NULL and b IS NOT NULL 368 ---- 369 select 370 ├── columns: a:1(int!null) b:2(int!null) 371 ├── key: (1,2) 372 ├── interesting orderings: (+1,+2) 373 ├── scan ab 374 │ ├── columns: a:1(int) b:2(int) 375 │ ├── lax-key: (1,2) 376 │ ├── prune: (1,2) 377 │ └── interesting orderings: (+1,+2) 378 └── filters 379 ├── is-not [type=bool, outer=(1), constraints=(/1: (/NULL - ]; tight)] 380 │ ├── variable: a:1 [type=int] 381 │ └── null [type=unknown] 382 └── is-not [type=bool, outer=(2), constraints=(/2: (/NULL - ]; tight)] 383 ├── variable: b:2 [type=int] 384 └── null [type=unknown] 385 386 norm 387 SELECT a, b FROM ab WHERE (a, b) IN ((1, 1), (2, 2)) 388 ---- 389 select 390 ├── columns: a:1(int!null) b:2(int!null) 391 ├── cardinality: [0 - 2] 392 ├── key: (1,2) 393 ├── interesting orderings: (+1,+2) 394 ├── scan ab 395 │ ├── columns: a:1(int) b:2(int) 396 │ ├── lax-key: (1,2) 397 │ ├── prune: (1,2) 398 │ └── interesting orderings: (+1,+2) 399 └── filters 400 └── in [type=bool, outer=(1,2), constraints=(/1/2: [/1/1 - /1/1] [/2/2 - /2/2]; /2: [/1 - /1] [/2 - /2]; tight)] 401 ├── tuple [type=tuple{int, int}] 402 │ ├── variable: a:1 [type=int] 403 │ └── variable: b:2 [type=int] 404 └── tuple [type=tuple{tuple{int, int}, tuple{int, int}}] 405 ├── tuple [type=tuple{int, int}] 406 │ ├── const: 1 [type=int] 407 │ └── const: 1 [type=int] 408 └── tuple [type=tuple{int, int}] 409 ├── const: 2 [type=int] 410 └── const: 2 [type=int]