github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/memo/testdata/logprops/select (about) 1 exec-ddl 2 CREATE TABLE xy (x INT PRIMARY KEY, y INT) 3 ---- 4 5 exec-ddl 6 CREATE TABLE kuv (k INT PRIMARY KEY, u FLOAT, v STRING) 7 ---- 8 9 build 10 SELECT * FROM xy WHERE x=1 11 ---- 12 select 13 ├── columns: x:1(int!null) y:2(int) 14 ├── cardinality: [0 - 1] 15 ├── key: () 16 ├── fd: ()-->(1,2) 17 ├── prune: (2) 18 ├── interesting orderings: (+1) 19 ├── scan xy 20 │ ├── columns: x:1(int!null) y:2(int) 21 │ ├── key: (1) 22 │ ├── fd: (1)-->(2) 23 │ ├── prune: (1,2) 24 │ └── interesting orderings: (+1) 25 └── filters 26 └── eq [type=bool, outer=(1), constraints=(/1: [/1 - /1]; tight), fd=()-->(1)] 27 ├── variable: x:1 [type=int] 28 └── const: 1 [type=int] 29 30 build 31 SELECT * FROM xy,kuv WHERE xy.x=kuv.k 32 ---- 33 select 34 ├── columns: x:1(int!null) y:2(int) k:3(int!null) u:4(float) v:5(string) 35 ├── key: (3) 36 ├── fd: (1)-->(2), (3)-->(4,5), (1)==(3), (3)==(1) 37 ├── prune: (2,4,5) 38 ├── interesting orderings: (+1) (+3) 39 ├── inner-join (cross) 40 │ ├── columns: x:1(int!null) y:2(int) k:3(int!null) u:4(float) v:5(string) 41 │ ├── key: (1,3) 42 │ ├── fd: (1)-->(2), (3)-->(4,5) 43 │ ├── prune: (1-5) 44 │ ├── interesting orderings: (+1) (+3) 45 │ ├── scan xy 46 │ │ ├── columns: x:1(int!null) y:2(int) 47 │ │ ├── key: (1) 48 │ │ ├── fd: (1)-->(2) 49 │ │ ├── prune: (1,2) 50 │ │ └── interesting orderings: (+1) 51 │ ├── scan kuv 52 │ │ ├── columns: k:3(int!null) u:4(float) v:5(string) 53 │ │ ├── key: (3) 54 │ │ ├── fd: (3)-->(4,5) 55 │ │ ├── prune: (3-5) 56 │ │ └── interesting orderings: (+3) 57 │ └── filters (true) 58 └── filters 59 └── eq [type=bool, outer=(1,3), constraints=(/1: (/NULL - ]; /3: (/NULL - ]), fd=(1)==(3), (3)==(1)] 60 ├── variable: x:1 [type=int] 61 └── variable: k:3 [type=int] 62 63 # Propagate outer columns. 64 build 65 SELECT * FROM xy WHERE EXISTS(SELECT * FROM (SELECT * FROM kuv WHERE k=y) WHERE k=x) 66 ---- 67 select 68 ├── columns: x:1(int!null) y:2(int) 69 ├── key: (1) 70 ├── fd: (1)-->(2) 71 ├── interesting orderings: (+1) 72 ├── scan xy 73 │ ├── columns: x:1(int!null) y:2(int) 74 │ ├── key: (1) 75 │ ├── fd: (1)-->(2) 76 │ ├── prune: (1,2) 77 │ └── interesting orderings: (+1) 78 └── filters 79 └── exists [type=bool, outer=(1,2), correlated-subquery] 80 └── select 81 ├── columns: k:3(int!null) u:4(float) v:5(string) 82 ├── outer: (1,2) 83 ├── cardinality: [0 - 1] 84 ├── key: () 85 ├── fd: ()-->(3-5) 86 ├── prune: (4,5) 87 ├── interesting orderings: (+3) 88 ├── select 89 │ ├── columns: k:3(int!null) u:4(float) v:5(string) 90 │ ├── outer: (2) 91 │ ├── cardinality: [0 - 1] 92 │ ├── key: () 93 │ ├── fd: ()-->(3-5) 94 │ ├── prune: (4,5) 95 │ ├── interesting orderings: (+3) 96 │ ├── scan kuv 97 │ │ ├── columns: k:3(int!null) u:4(float) v:5(string) 98 │ │ ├── key: (3) 99 │ │ ├── fd: (3)-->(4,5) 100 │ │ ├── prune: (3-5) 101 │ │ └── interesting orderings: (+3) 102 │ └── filters 103 │ └── eq [type=bool, outer=(2,3), constraints=(/2: (/NULL - ]; /3: (/NULL - ]), fd=(2)==(3), (3)==(2)] 104 │ ├── variable: k:3 [type=int] 105 │ └── variable: y:2 [type=int] 106 └── filters 107 └── eq [type=bool, outer=(1,3), constraints=(/1: (/NULL - ]; /3: (/NULL - ]), fd=(1)==(3), (3)==(1)] 108 ├── variable: k:3 [type=int] 109 └── variable: x:1 [type=int] 110 111 # Reduce min cardinality. 112 build 113 SELECT count(*) FROM xy HAVING count(*) = 5 114 ---- 115 select 116 ├── columns: count:3(int!null) 117 ├── cardinality: [0 - 1] 118 ├── key: () 119 ├── fd: ()-->(3) 120 ├── scalar-group-by 121 │ ├── columns: count_rows:3(int!null) 122 │ ├── cardinality: [1 - 1] 123 │ ├── key: () 124 │ ├── fd: ()-->(3) 125 │ ├── prune: (3) 126 │ ├── project 127 │ │ └── scan xy 128 │ │ ├── columns: x:1(int!null) y:2(int) 129 │ │ ├── key: (1) 130 │ │ ├── fd: (1)-->(2) 131 │ │ ├── prune: (1,2) 132 │ │ └── interesting orderings: (+1) 133 │ └── aggregations 134 │ └── count-rows [as=count_rows:3, type=int] 135 └── filters 136 └── eq [type=bool, outer=(3), constraints=(/3: [/5 - /5]; tight), fd=()-->(3)] 137 ├── variable: count_rows:3 [type=int] 138 └── const: 5 [type=int] 139 140 build 141 SELECT * FROM xy WITH ORDINALITY 142 ---- 143 ordinality 144 ├── columns: x:1(int!null) y:2(int) ordinality:3(int!null) 145 ├── key: (1) 146 ├── fd: (1)-->(2,3), (3)-->(1,2) 147 ├── prune: (1,2) 148 └── scan xy 149 ├── columns: x:1(int!null) y:2(int) 150 ├── key: (1) 151 ├── fd: (1)-->(2) 152 ├── prune: (1,2) 153 └── interesting orderings: (+1) 154 155 # Verify not-null column deduction from constraints. 156 exec-ddl 157 CREATE TABLE abcd (a INT NOT NULL, b INT NOT NULL, c INT, d INT) 158 ---- 159 160 build 161 SELECT * FROM abcd WHERE true 162 ---- 163 project 164 ├── columns: a:1(int!null) b:2(int!null) c:3(int) d:4(int) 165 ├── prune: (1-4) 166 └── select 167 ├── columns: a:1(int!null) b:2(int!null) c:3(int) d:4(int) rowid:5(int!null) 168 ├── key: (5) 169 ├── fd: (5)-->(1-4) 170 ├── prune: (1-5) 171 ├── interesting orderings: (+5) 172 ├── scan abcd 173 │ ├── columns: a:1(int!null) b:2(int!null) c:3(int) d:4(int) rowid:5(int!null) 174 │ ├── key: (5) 175 │ ├── fd: (5)-->(1-4) 176 │ ├── prune: (1-5) 177 │ └── interesting orderings: (+5) 178 └── filters 179 └── true [type=bool] 180 181 build 182 SELECT * FROM abcd WHERE c IS NOT NULL 183 ---- 184 project 185 ├── columns: a:1(int!null) b:2(int!null) c:3(int!null) d:4(int) 186 ├── prune: (1-4) 187 └── select 188 ├── columns: a:1(int!null) b:2(int!null) c:3(int!null) d:4(int) rowid:5(int!null) 189 ├── key: (5) 190 ├── fd: (5)-->(1-4) 191 ├── prune: (1,2,4,5) 192 ├── interesting orderings: (+5) 193 ├── scan abcd 194 │ ├── columns: a:1(int!null) b:2(int!null) c:3(int) d:4(int) rowid:5(int!null) 195 │ ├── key: (5) 196 │ ├── fd: (5)-->(1-4) 197 │ ├── prune: (1-5) 198 │ └── interesting orderings: (+5) 199 └── filters 200 └── is-not [type=bool, outer=(3), constraints=(/3: (/NULL - ]; tight)] 201 ├── variable: c:3 [type=int] 202 └── null [type=unknown] 203 204 build 205 SELECT * FROM abcd WHERE c = d 206 ---- 207 project 208 ├── columns: a:1(int!null) b:2(int!null) c:3(int!null) d:4(int!null) 209 ├── fd: (3)==(4), (4)==(3) 210 ├── prune: (1-4) 211 └── select 212 ├── columns: a:1(int!null) b:2(int!null) c:3(int!null) d:4(int!null) rowid:5(int!null) 213 ├── key: (5) 214 ├── fd: (5)-->(1-4), (3)==(4), (4)==(3) 215 ├── prune: (1,2,5) 216 ├── interesting orderings: (+5) 217 ├── scan abcd 218 │ ├── columns: a:1(int!null) b:2(int!null) c:3(int) d:4(int) rowid:5(int!null) 219 │ ├── key: (5) 220 │ ├── fd: (5)-->(1-4) 221 │ ├── prune: (1-5) 222 │ └── interesting orderings: (+5) 223 └── filters 224 └── eq [type=bool, outer=(3,4), constraints=(/3: (/NULL - ]; /4: (/NULL - ]), fd=(3)==(4), (4)==(3)] 225 ├── variable: c:3 [type=int] 226 └── variable: d:4 [type=int] 227 228 build 229 SELECT * FROM abcd WHERE a > c 230 ---- 231 project 232 ├── columns: a:1(int!null) b:2(int!null) c:3(int!null) d:4(int) 233 ├── prune: (1-4) 234 └── select 235 ├── columns: a:1(int!null) b:2(int!null) c:3(int!null) d:4(int) rowid:5(int!null) 236 ├── key: (5) 237 ├── fd: (5)-->(1-4) 238 ├── prune: (2,4,5) 239 ├── interesting orderings: (+5) 240 ├── scan abcd 241 │ ├── columns: a:1(int!null) b:2(int!null) c:3(int) d:4(int) rowid:5(int!null) 242 │ ├── key: (5) 243 │ ├── fd: (5)-->(1-4) 244 │ ├── prune: (1-5) 245 │ └── interesting orderings: (+5) 246 └── filters 247 └── gt [type=bool, outer=(1,3), constraints=(/1: (/NULL - ]; /3: (/NULL - ])] 248 ├── variable: a:1 [type=int] 249 └── variable: c:3 [type=int] 250 251 build 252 SELECT * FROM (SELECT * FROM abcd WHERE a = c) WHERE b < d 253 ---- 254 select 255 ├── columns: a:1(int!null) b:2(int!null) c:3(int!null) d:4(int!null) 256 ├── fd: (1)==(3), (3)==(1) 257 ├── prune: (1,3) 258 ├── project 259 │ ├── columns: a:1(int!null) b:2(int!null) c:3(int!null) d:4(int) 260 │ ├── fd: (1)==(3), (3)==(1) 261 │ ├── prune: (1-4) 262 │ └── select 263 │ ├── columns: a:1(int!null) b:2(int!null) c:3(int!null) d:4(int) rowid:5(int!null) 264 │ ├── key: (5) 265 │ ├── fd: (5)-->(1-4), (1)==(3), (3)==(1) 266 │ ├── prune: (2,4,5) 267 │ ├── interesting orderings: (+5) 268 │ ├── scan abcd 269 │ │ ├── columns: a:1(int!null) b:2(int!null) c:3(int) d:4(int) rowid:5(int!null) 270 │ │ ├── key: (5) 271 │ │ ├── fd: (5)-->(1-4) 272 │ │ ├── prune: (1-5) 273 │ │ └── interesting orderings: (+5) 274 │ └── filters 275 │ └── eq [type=bool, outer=(1,3), constraints=(/1: (/NULL - ]; /3: (/NULL - ]), fd=(1)==(3), (3)==(1)] 276 │ ├── variable: a:1 [type=int] 277 │ └── variable: c:3 [type=int] 278 └── filters 279 └── lt [type=bool, outer=(2,4), constraints=(/2: (/NULL - ]; /4: (/NULL - ])] 280 ├── variable: b:2 [type=int] 281 └── variable: d:4 [type=int] 282 283 # Test outer column in select filter that is part of a not-null constraint. 284 build 285 SELECT * FROM abcd WHERE (SELECT count(*) FROM xy WHERE y = b) > 0 286 ---- 287 project 288 ├── columns: a:1(int!null) b:2(int!null) c:3(int) d:4(int) 289 ├── prune: (1-4) 290 └── select 291 ├── columns: a:1(int!null) b:2(int!null) c:3(int) d:4(int) rowid:5(int!null) 292 ├── key: (5) 293 ├── fd: (5)-->(1-4) 294 ├── prune: (1,3-5) 295 ├── interesting orderings: (+5) 296 ├── scan abcd 297 │ ├── columns: a:1(int!null) b:2(int!null) c:3(int) d:4(int) rowid:5(int!null) 298 │ ├── key: (5) 299 │ ├── fd: (5)-->(1-4) 300 │ ├── prune: (1-5) 301 │ └── interesting orderings: (+5) 302 └── filters 303 └── gt [type=bool, outer=(2), correlated-subquery] 304 ├── subquery [type=int] 305 │ └── max1-row 306 │ ├── columns: count_rows:8(int!null) 307 │ ├── error: "more than one row returned by a subquery used as an expression" 308 │ ├── outer: (2) 309 │ ├── cardinality: [1 - 1] 310 │ ├── key: () 311 │ ├── fd: ()-->(8) 312 │ └── scalar-group-by 313 │ ├── columns: count_rows:8(int!null) 314 │ ├── outer: (2) 315 │ ├── cardinality: [1 - 1] 316 │ ├── key: () 317 │ ├── fd: ()-->(8) 318 │ ├── prune: (8) 319 │ ├── project 320 │ │ ├── outer: (2) 321 │ │ └── select 322 │ │ ├── columns: x:6(int!null) y:7(int!null) 323 │ │ ├── outer: (2) 324 │ │ ├── key: (6) 325 │ │ ├── fd: ()-->(7) 326 │ │ ├── prune: (6) 327 │ │ ├── interesting orderings: (+6) 328 │ │ ├── scan xy 329 │ │ │ ├── columns: x:6(int!null) y:7(int) 330 │ │ │ ├── key: (6) 331 │ │ │ ├── fd: (6)-->(7) 332 │ │ │ ├── prune: (6,7) 333 │ │ │ └── interesting orderings: (+6) 334 │ │ └── filters 335 │ │ └── eq [type=bool, outer=(2,7), constraints=(/2: (/NULL - ]; /7: (/NULL - ]), fd=(2)==(7), (7)==(2)] 336 │ │ ├── variable: y:7 [type=int] 337 │ │ └── variable: b:2 [type=int] 338 │ └── aggregations 339 │ └── count-rows [as=count_rows:8, type=int] 340 └── const: 0 [type=int] 341 342 # Sequences always have a single row when selected from. 343 exec-ddl 344 CREATE SEQUENCE x 345 ---- 346 347 build 348 SELECT * FROM x 349 ---- 350 sequence-select x 351 ├── columns: last_value:1(int!null) log_cnt:2(int!null) is_called:3(bool!null) 352 ├── cardinality: [1 - 1] 353 ├── key: () 354 └── fd: ()-->(1-3) 355 356 # Test that cardinality is set for constrained keys, but not for other columns. 357 norm 358 SELECT * FROM xy WHERE x IN (1, 2, 4, 6, 7, 9) 359 ---- 360 select 361 ├── columns: x:1(int!null) y:2(int) 362 ├── cardinality: [0 - 6] 363 ├── key: (1) 364 ├── fd: (1)-->(2) 365 ├── prune: (2) 366 ├── interesting orderings: (+1) 367 ├── scan xy 368 │ ├── columns: x:1(int!null) y:2(int) 369 │ ├── key: (1) 370 │ ├── fd: (1)-->(2) 371 │ ├── prune: (1,2) 372 │ └── interesting orderings: (+1) 373 └── filters 374 └── in [type=bool, outer=(1), constraints=(/1: [/1 - /1] [/2 - /2] [/4 - /4] [/6 - /6] [/7 - /7] [/9 - /9]; tight)] 375 ├── variable: x:1 [type=int] 376 └── tuple [type=tuple{int, int, int, int, int, int}] 377 ├── const: 1 [type=int] 378 ├── const: 2 [type=int] 379 ├── const: 4 [type=int] 380 ├── const: 6 [type=int] 381 ├── const: 7 [type=int] 382 └── const: 9 [type=int] 383 384 norm 385 SELECT * FROM xy WHERE x > 0 AND x <= 10 386 ---- 387 select 388 ├── columns: x:1(int!null) y:2(int) 389 ├── cardinality: [0 - 10] 390 ├── key: (1) 391 ├── fd: (1)-->(2) 392 ├── prune: (2) 393 ├── interesting orderings: (+1) 394 ├── scan xy 395 │ ├── columns: x:1(int!null) y:2(int) 396 │ ├── key: (1) 397 │ ├── fd: (1)-->(2) 398 │ ├── prune: (1,2) 399 │ └── interesting orderings: (+1) 400 └── filters 401 └── range [type=bool, outer=(1), constraints=(/1: [/1 - /10]; tight)] 402 └── and [type=bool] 403 ├── gt [type=bool] 404 │ ├── variable: x:1 [type=int] 405 │ └── const: 0 [type=int] 406 └── le [type=bool] 407 ├── variable: x:1 [type=int] 408 └── const: 10 [type=int] 409 410 norm 411 SELECT * FROM xy WHERE y > 0 AND y <= 10 412 ---- 413 select 414 ├── columns: x:1(int!null) y:2(int!null) 415 ├── key: (1) 416 ├── fd: (1)-->(2) 417 ├── prune: (1) 418 ├── interesting orderings: (+1) 419 ├── scan xy 420 │ ├── columns: x:1(int!null) y:2(int) 421 │ ├── key: (1) 422 │ ├── fd: (1)-->(2) 423 │ ├── prune: (1,2) 424 │ └── interesting orderings: (+1) 425 └── filters 426 └── range [type=bool, outer=(2), constraints=(/2: [/1 - /10]; tight)] 427 └── and [type=bool] 428 ├── gt [type=bool] 429 │ ├── variable: y:2 [type=int] 430 │ └── const: 0 [type=int] 431 └── le [type=bool] 432 ├── variable: y:2 [type=int] 433 └── const: 10 [type=int]