github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/memo/testdata/stats/set (about) 1 exec-ddl 2 CREATE TABLE a (x INT PRIMARY KEY, y INT, s STRING) 3 ---- 4 5 exec-ddl 6 CREATE TABLE b (x INT, z INT NOT NULL, s STRING) 7 ---- 8 9 exec-ddl 10 CREATE TABLE c (x INT, z INT NOT NULL, s STRING) 11 ---- 12 13 exec-ddl 14 ALTER TABLE a INJECT STATISTICS '[ 15 { 16 "columns": ["x"], 17 "created_at": "2018-01-01 1:00:00.00000+00:00", 18 "row_count": 5000, 19 "distinct_count": 5000 20 }, 21 { 22 "columns": ["y"], 23 "created_at": "2018-01-01 1:30:00.00000+00:00", 24 "row_count": 5000, 25 "distinct_count": 400 26 }, 27 { 28 "columns": ["s"], 29 "created_at": "2018-01-01 1:30:00.00000+00:00", 30 "row_count": 5000, 31 "distinct_count": 10 32 }, 33 { 34 "columns": ["y","s"], 35 "created_at": "2018-01-01 1:30:00.00000+00:00", 36 "row_count": 5000, 37 "distinct_count": 1000 38 } 39 ]' 40 ---- 41 42 exec-ddl 43 ALTER TABLE b INJECT STATISTICS '[ 44 { 45 "columns": ["x"], 46 "created_at": "2018-01-01 1:00:00.00000+00:00", 47 "row_count": 10000, 48 "distinct_count": 5000 49 }, 50 { 51 "columns": ["z"], 52 "created_at": "2018-01-01 1:30:00.00000+00:00", 53 "row_count": 10000, 54 "distinct_count": 100 55 }, 56 { 57 "columns": ["s"], 58 "created_at": "2018-01-01 1:30:00.00000+00:00", 59 "row_count": 10000, 60 "distinct_count": 10 61 }, 62 { 63 "columns": ["z","s"], 64 "created_at": "2018-01-01 1:40:00.00000+00:00", 65 "row_count": 10000, 66 "distinct_count": 200 67 }, 68 { 69 "columns": ["rowid"], 70 "created_at": "2018-01-01 1:30:00.00000+00:00", 71 "row_count": 10000, 72 "distinct_count": 10000 73 } 74 ]' 75 ---- 76 77 exec-ddl 78 ALTER TABLE c INJECT STATISTICS '[ 79 { 80 "columns": ["x"], 81 "created_at": "2018-01-01 1:00:00.00000+00:00", 82 "row_count": 10000, 83 "distinct_count": 5000 84 }, 85 { 86 "columns": ["z"], 87 "created_at": "2018-01-01 1:30:00.00000+00:00", 88 "row_count": 10000, 89 "distinct_count": 100 90 }, 91 { 92 "columns": ["s"], 93 "created_at": "2018-01-01 1:30:00.00000+00:00", 94 "row_count": 10000, 95 "distinct_count": 10 96 } 97 ]' 98 ---- 99 100 build 101 SELECT *, x FROM a UNION SELECT *, rowid FROM b 102 ---- 103 union 104 ├── columns: x:8(int) y:9(int) s:10(string) x:11(int!null) 105 ├── left columns: a.x:1(int) a.y:2(int) a.s:3(string) a.x:1(int) 106 ├── right columns: b.x:4(int) z:5(int) b.s:6(string) rowid:7(int) 107 ├── stats: [rows=15000, distinct(8-11)=15000, null(8-11)=0] 108 ├── key: (8-11) 109 ├── scan a 110 │ ├── columns: a.x:1(int!null) a.y:2(int) a.s:3(string) 111 │ ├── stats: [rows=5000, distinct(1-3)=5000, null(1-3)=0] 112 │ ├── key: (1) 113 │ └── fd: (1)-->(2,3) 114 └── scan b 115 ├── columns: b.x:4(int) z:5(int!null) b.s:6(string) rowid:7(int!null) 116 ├── stats: [rows=10000, distinct(4-7)=10000, null(4-7)=0] 117 ├── key: (7) 118 └── fd: (7)-->(4-6) 119 120 build 121 SELECT *, x FROM a UNION ALL SELECT *, rowid FROM b 122 ---- 123 union-all 124 ├── columns: x:8(int) y:9(int) s:10(string) x:11(int!null) 125 ├── left columns: a.x:1(int) a.y:2(int) a.s:3(string) a.x:1(int) 126 ├── right columns: b.x:4(int) z:5(int) b.s:6(string) rowid:7(int) 127 ├── stats: [rows=15000] 128 ├── scan a 129 │ ├── columns: a.x:1(int!null) a.y:2(int) a.s:3(string) 130 │ ├── stats: [rows=5000] 131 │ ├── key: (1) 132 │ └── fd: (1)-->(2,3) 133 └── scan b 134 ├── columns: b.x:4(int) z:5(int!null) b.s:6(string) rowid:7(int!null) 135 ├── stats: [rows=10000] 136 ├── key: (7) 137 └── fd: (7)-->(4-6) 138 139 build 140 SELECT y, s FROM a UNION SELECT z, s FROM c 141 ---- 142 union 143 ├── columns: y:8(int) s:9(string) 144 ├── left columns: a.y:2(int) a.s:3(string) 145 ├── right columns: z:5(int) c.s:6(string) 146 ├── stats: [rows=2000, distinct(8,9)=2000, null(8,9)=0] 147 ├── key: (8,9) 148 ├── project 149 │ ├── columns: a.y:2(int) a.s:3(string) 150 │ ├── stats: [rows=5000, distinct(2,3)=1000, null(2,3)=0] 151 │ └── scan a 152 │ ├── columns: a.x:1(int!null) a.y:2(int) a.s:3(string) 153 │ ├── stats: [rows=5000, distinct(2,3)=1000, null(2,3)=0] 154 │ ├── key: (1) 155 │ └── fd: (1)-->(2,3) 156 └── project 157 ├── columns: z:5(int!null) c.s:6(string) 158 ├── stats: [rows=10000, distinct(5,6)=1000, null(5,6)=0] 159 └── scan c 160 ├── columns: c.x:4(int) z:5(int!null) c.s:6(string) rowid:7(int!null) 161 ├── stats: [rows=10000, distinct(5,6)=1000, null(5,6)=0] 162 ├── key: (7) 163 └── fd: (7)-->(4-6) 164 165 build 166 SELECT y, s FROM a UNION SELECT z, s FROM b 167 ---- 168 union 169 ├── columns: y:8(int) s:9(string) 170 ├── left columns: a.y:2(int) a.s:3(string) 171 ├── right columns: z:5(int) b.s:6(string) 172 ├── stats: [rows=1200, distinct(8,9)=1200, null(8,9)=0] 173 ├── key: (8,9) 174 ├── project 175 │ ├── columns: a.y:2(int) a.s:3(string) 176 │ ├── stats: [rows=5000, distinct(2,3)=1000, null(2,3)=0] 177 │ └── scan a 178 │ ├── columns: a.x:1(int!null) a.y:2(int) a.s:3(string) 179 │ ├── stats: [rows=5000, distinct(2,3)=1000, null(2,3)=0] 180 │ ├── key: (1) 181 │ └── fd: (1)-->(2,3) 182 └── project 183 ├── columns: z:5(int!null) b.s:6(string) 184 ├── stats: [rows=10000, distinct(5,6)=200, null(5,6)=0] 185 └── scan b 186 ├── columns: b.x:4(int) z:5(int!null) b.s:6(string) rowid:7(int!null) 187 ├── stats: [rows=10000, distinct(5,6)=200, null(5,6)=0] 188 ├── key: (7) 189 └── fd: (7)-->(4-6) 190 191 build 192 SELECT y, s FROM a UNION ALL SELECT z, s FROM b 193 ---- 194 union-all 195 ├── columns: y:8(int) s:9(string) 196 ├── left columns: a.y:2(int) a.s:3(string) 197 ├── right columns: z:5(int) b.s:6(string) 198 ├── stats: [rows=15000] 199 ├── project 200 │ ├── columns: a.y:2(int) a.s:3(string) 201 │ ├── stats: [rows=5000] 202 │ └── scan a 203 │ ├── columns: a.x:1(int!null) a.y:2(int) a.s:3(string) 204 │ ├── stats: [rows=5000] 205 │ ├── key: (1) 206 │ └── fd: (1)-->(2,3) 207 └── project 208 ├── columns: z:5(int!null) b.s:6(string) 209 ├── stats: [rows=10000] 210 └── scan b 211 ├── columns: b.x:4(int) z:5(int!null) b.s:6(string) rowid:7(int!null) 212 ├── stats: [rows=10000] 213 ├── key: (7) 214 └── fd: (7)-->(4-6) 215 216 build 217 SELECT x, y, x FROM a INTERSECT SELECT z, x, rowid FROM (SELECT *, rowid FROM b WHERE b.x=1) b 218 ---- 219 intersect 220 ├── columns: x:1(int!null) y:2(int) x:1(int!null) 221 ├── left columns: a.x:1(int!null) y:2(int) a.x:1(int!null) 222 ├── right columns: z:5(int) b.x:4(int) rowid:7(int) 223 ├── stats: [rows=2, distinct(1,2)=2, null(1,2)=0] 224 ├── key: (1,2) 225 ├── project 226 │ ├── columns: a.x:1(int!null) y:2(int) 227 │ ├── stats: [rows=5000, distinct(1,2)=5000, null(1,2)=0] 228 │ ├── key: (1) 229 │ ├── fd: (1)-->(2) 230 │ └── scan a 231 │ ├── columns: a.x:1(int!null) y:2(int) a.s:3(string) 232 │ ├── stats: [rows=5000, distinct(1,2)=5000, null(1,2)=0] 233 │ ├── key: (1) 234 │ └── fd: (1)-->(2,3) 235 └── project 236 ├── columns: b.x:4(int!null) z:5(int!null) rowid:7(int!null) 237 ├── stats: [rows=2, distinct(4,5,7)=2, null(4,5,7)=0] 238 ├── key: (7) 239 ├── fd: ()-->(4), (7)-->(5) 240 └── select 241 ├── columns: b.x:4(int!null) z:5(int!null) b.s:6(string) rowid:7(int!null) 242 ├── stats: [rows=2, distinct(4)=1, null(4)=0, distinct(4,5,7)=2, null(4,5,7)=0] 243 ├── key: (7) 244 ├── fd: ()-->(4), (7)-->(5,6) 245 ├── scan b 246 │ ├── columns: b.x:4(int) z:5(int!null) b.s:6(string) rowid:7(int!null) 247 │ ├── stats: [rows=10000, distinct(4)=5000, null(4)=0, distinct(5)=100, null(5)=0, distinct(7)=10000, null(7)=0, distinct(4,5,7)=10000, null(4,5,7)=0] 248 │ ├── key: (7) 249 │ └── fd: (7)-->(4-6) 250 └── filters 251 └── b.x:4 = 1 [type=bool, outer=(4), constraints=(/4: [/1 - /1]; tight), fd=()-->(4)] 252 253 build 254 SELECT x, y, x FROM a INTERSECT ALL SELECT z, x, rowid FROM (SELECT *, rowid FROM b WHERE b.x=1) b 255 ---- 256 intersect-all 257 ├── columns: x:1(int!null) y:2(int) x:1(int!null) 258 ├── left columns: a.x:1(int!null) y:2(int) a.x:1(int!null) 259 ├── right columns: z:5(int) b.x:4(int) rowid:7(int) 260 ├── stats: [rows=2] 261 ├── project 262 │ ├── columns: a.x:1(int!null) y:2(int) 263 │ ├── stats: [rows=5000] 264 │ ├── key: (1) 265 │ ├── fd: (1)-->(2) 266 │ └── scan a 267 │ ├── columns: a.x:1(int!null) y:2(int) a.s:3(string) 268 │ ├── stats: [rows=5000] 269 │ ├── key: (1) 270 │ └── fd: (1)-->(2,3) 271 └── project 272 ├── columns: b.x:4(int!null) z:5(int!null) rowid:7(int!null) 273 ├── stats: [rows=2] 274 ├── key: (7) 275 ├── fd: ()-->(4), (7)-->(5) 276 └── select 277 ├── columns: b.x:4(int!null) z:5(int!null) b.s:6(string) rowid:7(int!null) 278 ├── stats: [rows=2, distinct(4)=1, null(4)=0] 279 ├── key: (7) 280 ├── fd: ()-->(4), (7)-->(5,6) 281 ├── scan b 282 │ ├── columns: b.x:4(int) z:5(int!null) b.s:6(string) rowid:7(int!null) 283 │ ├── stats: [rows=10000, distinct(4)=5000, null(4)=0, distinct(5)=100, null(5)=0, distinct(7)=10000, null(7)=0] 284 │ ├── key: (7) 285 │ └── fd: (7)-->(4-6) 286 └── filters 287 └── b.x:4 = 1 [type=bool, outer=(4), constraints=(/4: [/1 - /1]; tight), fd=()-->(4)] 288 289 build 290 SELECT y, s FROM a INTERSECT SELECT z, s FROM c 291 ---- 292 intersect 293 ├── columns: y:2(int) s:3(string) 294 ├── left columns: y:2(int) a.s:3(string) 295 ├── right columns: z:5(int) c.s:6(string) 296 ├── stats: [rows=1000, distinct(2,3)=1000, null(2,3)=0] 297 ├── key: (2,3) 298 ├── project 299 │ ├── columns: y:2(int) a.s:3(string) 300 │ ├── stats: [rows=5000, distinct(2,3)=1000, null(2,3)=0] 301 │ └── scan a 302 │ ├── columns: a.x:1(int!null) y:2(int) a.s:3(string) 303 │ ├── stats: [rows=5000, distinct(2,3)=1000, null(2,3)=0] 304 │ ├── key: (1) 305 │ └── fd: (1)-->(2,3) 306 └── project 307 ├── columns: z:5(int!null) c.s:6(string) 308 ├── stats: [rows=10000, distinct(5,6)=1000, null(5,6)=0] 309 └── scan c 310 ├── columns: c.x:4(int) z:5(int!null) c.s:6(string) rowid:7(int!null) 311 ├── stats: [rows=10000, distinct(5,6)=1000, null(5,6)=0] 312 ├── key: (7) 313 └── fd: (7)-->(4-6) 314 315 build 316 SELECT y, s FROM a INTERSECT SELECT z, s FROM b 317 ---- 318 intersect 319 ├── columns: y:2(int) s:3(string) 320 ├── left columns: y:2(int) a.s:3(string) 321 ├── right columns: z:5(int) b.s:6(string) 322 ├── stats: [rows=200, distinct(2,3)=200, null(2,3)=0] 323 ├── key: (2,3) 324 ├── project 325 │ ├── columns: y:2(int) a.s:3(string) 326 │ ├── stats: [rows=5000, distinct(2,3)=1000, null(2,3)=0] 327 │ └── scan a 328 │ ├── columns: a.x:1(int!null) y:2(int) a.s:3(string) 329 │ ├── stats: [rows=5000, distinct(2,3)=1000, null(2,3)=0] 330 │ ├── key: (1) 331 │ └── fd: (1)-->(2,3) 332 └── project 333 ├── columns: z:5(int!null) b.s:6(string) 334 ├── stats: [rows=10000, distinct(5,6)=200, null(5,6)=0] 335 └── scan b 336 ├── columns: b.x:4(int) z:5(int!null) b.s:6(string) rowid:7(int!null) 337 ├── stats: [rows=10000, distinct(5,6)=200, null(5,6)=0] 338 ├── key: (7) 339 └── fd: (7)-->(4-6) 340 341 build 342 SELECT y, s FROM a INTERSECT ALL SELECT z, s FROM b 343 ---- 344 intersect-all 345 ├── columns: y:2(int) s:3(string) 346 ├── left columns: y:2(int) a.s:3(string) 347 ├── right columns: z:5(int) b.s:6(string) 348 ├── stats: [rows=5000] 349 ├── project 350 │ ├── columns: y:2(int) a.s:3(string) 351 │ ├── stats: [rows=5000] 352 │ └── scan a 353 │ ├── columns: a.x:1(int!null) y:2(int) a.s:3(string) 354 │ ├── stats: [rows=5000] 355 │ ├── key: (1) 356 │ └── fd: (1)-->(2,3) 357 └── project 358 ├── columns: z:5(int!null) b.s:6(string) 359 ├── stats: [rows=10000] 360 └── scan b 361 ├── columns: b.x:4(int) z:5(int!null) b.s:6(string) rowid:7(int!null) 362 ├── stats: [rows=10000] 363 ├── key: (7) 364 └── fd: (7)-->(4-6) 365 366 build 367 SELECT x, x, y FROM a EXCEPT SELECT x, z, z FROM (SELECT * FROM b WHERE b.x=1) b 368 ---- 369 except 370 ├── columns: x:1(int!null) x:1(int!null) y:2(int) 371 ├── left columns: a.x:1(int!null) a.x:1(int!null) y:2(int) 372 ├── right columns: b.x:4(int) z:5(int) z:5(int) 373 ├── stats: [rows=5000, distinct(1,2)=5000, null(1,2)=0] 374 ├── key: (1,2) 375 ├── project 376 │ ├── columns: a.x:1(int!null) y:2(int) 377 │ ├── stats: [rows=5000, distinct(1,2)=5000, null(1,2)=0] 378 │ ├── key: (1) 379 │ ├── fd: (1)-->(2) 380 │ └── scan a 381 │ ├── columns: a.x:1(int!null) y:2(int) a.s:3(string) 382 │ ├── stats: [rows=5000, distinct(1,2)=5000, null(1,2)=0] 383 │ ├── key: (1) 384 │ └── fd: (1)-->(2,3) 385 └── project 386 ├── columns: b.x:4(int!null) z:5(int!null) 387 ├── stats: [rows=2, distinct(4,5)=2, null(4,5)=0] 388 ├── fd: ()-->(4) 389 └── project 390 ├── columns: b.x:4(int!null) z:5(int!null) b.s:6(string) 391 ├── stats: [rows=2, distinct(4,5)=2, null(4,5)=0] 392 ├── fd: ()-->(4) 393 └── select 394 ├── columns: b.x:4(int!null) z:5(int!null) b.s:6(string) rowid:7(int!null) 395 ├── stats: [rows=2, distinct(4)=1, null(4)=0, distinct(4,5)=2, null(4,5)=0] 396 ├── key: (7) 397 ├── fd: ()-->(4), (7)-->(5,6) 398 ├── scan b 399 │ ├── columns: b.x:4(int) z:5(int!null) b.s:6(string) rowid:7(int!null) 400 │ ├── stats: [rows=10000, distinct(4)=5000, null(4)=0, distinct(5)=100, null(5)=0, distinct(7)=10000, null(7)=0, distinct(4,5)=10000, null(4,5)=0] 401 │ ├── key: (7) 402 │ └── fd: (7)-->(4-6) 403 └── filters 404 └── b.x:4 = 1 [type=bool, outer=(4), constraints=(/4: [/1 - /1]; tight), fd=()-->(4)] 405 406 build 407 SELECT x, x, y FROM a EXCEPT ALL SELECT x, z, z FROM (SELECT * FROM b WHERE b.x=1) b 408 ---- 409 except-all 410 ├── columns: x:1(int!null) x:1(int!null) y:2(int) 411 ├── left columns: a.x:1(int!null) a.x:1(int!null) y:2(int) 412 ├── right columns: b.x:4(int) z:5(int) z:5(int) 413 ├── stats: [rows=5000] 414 ├── project 415 │ ├── columns: a.x:1(int!null) y:2(int) 416 │ ├── stats: [rows=5000] 417 │ ├── key: (1) 418 │ ├── fd: (1)-->(2) 419 │ └── scan a 420 │ ├── columns: a.x:1(int!null) y:2(int) a.s:3(string) 421 │ ├── stats: [rows=5000] 422 │ ├── key: (1) 423 │ └── fd: (1)-->(2,3) 424 └── project 425 ├── columns: b.x:4(int!null) z:5(int!null) 426 ├── stats: [rows=2] 427 ├── fd: ()-->(4) 428 └── project 429 ├── columns: b.x:4(int!null) z:5(int!null) b.s:6(string) 430 ├── stats: [rows=2] 431 ├── fd: ()-->(4) 432 └── select 433 ├── columns: b.x:4(int!null) z:5(int!null) b.s:6(string) rowid:7(int!null) 434 ├── stats: [rows=2, distinct(4)=1, null(4)=0] 435 ├── key: (7) 436 ├── fd: ()-->(4), (7)-->(5,6) 437 ├── scan b 438 │ ├── columns: b.x:4(int) z:5(int!null) b.s:6(string) rowid:7(int!null) 439 │ ├── stats: [rows=10000, distinct(4)=5000, null(4)=0, distinct(5)=100, null(5)=0, distinct(7)=10000, null(7)=0] 440 │ ├── key: (7) 441 │ └── fd: (7)-->(4-6) 442 └── filters 443 └── b.x:4 = 1 [type=bool, outer=(4), constraints=(/4: [/1 - /1]; tight), fd=()-->(4)] 444 445 build 446 SELECT y, s FROM a EXCEPT SELECT z, s FROM c 447 ---- 448 except 449 ├── columns: y:2(int) s:3(string) 450 ├── left columns: y:2(int) a.s:3(string) 451 ├── right columns: z:5(int) c.s:6(string) 452 ├── stats: [rows=1000, distinct(2,3)=1000, null(2,3)=0] 453 ├── key: (2,3) 454 ├── project 455 │ ├── columns: y:2(int) a.s:3(string) 456 │ ├── stats: [rows=5000, distinct(2,3)=1000, null(2,3)=0] 457 │ └── scan a 458 │ ├── columns: a.x:1(int!null) y:2(int) a.s:3(string) 459 │ ├── stats: [rows=5000, distinct(2,3)=1000, null(2,3)=0] 460 │ ├── key: (1) 461 │ └── fd: (1)-->(2,3) 462 └── project 463 ├── columns: z:5(int!null) c.s:6(string) 464 ├── stats: [rows=10000, distinct(5,6)=1000, null(5,6)=0] 465 └── scan c 466 ├── columns: c.x:4(int) z:5(int!null) c.s:6(string) rowid:7(int!null) 467 ├── stats: [rows=10000, distinct(5,6)=1000, null(5,6)=0] 468 ├── key: (7) 469 └── fd: (7)-->(4-6) 470 471 build 472 SELECT y, s FROM a EXCEPT SELECT z, s FROM b 473 ---- 474 except 475 ├── columns: y:2(int) s:3(string) 476 ├── left columns: y:2(int) a.s:3(string) 477 ├── right columns: z:5(int) b.s:6(string) 478 ├── stats: [rows=1000, distinct(2,3)=1000, null(2,3)=0] 479 ├── key: (2,3) 480 ├── project 481 │ ├── columns: y:2(int) a.s:3(string) 482 │ ├── stats: [rows=5000, distinct(2,3)=1000, null(2,3)=0] 483 │ └── scan a 484 │ ├── columns: a.x:1(int!null) y:2(int) a.s:3(string) 485 │ ├── stats: [rows=5000, distinct(2,3)=1000, null(2,3)=0] 486 │ ├── key: (1) 487 │ └── fd: (1)-->(2,3) 488 └── project 489 ├── columns: z:5(int!null) b.s:6(string) 490 ├── stats: [rows=10000, distinct(5,6)=200, null(5,6)=0] 491 └── scan b 492 ├── columns: b.x:4(int) z:5(int!null) b.s:6(string) rowid:7(int!null) 493 ├── stats: [rows=10000, distinct(5,6)=200, null(5,6)=0] 494 ├── key: (7) 495 └── fd: (7)-->(4-6) 496 497 build 498 SELECT y, s FROM a EXCEPT ALL SELECT z, s FROM b 499 ---- 500 except-all 501 ├── columns: y:2(int) s:3(string) 502 ├── left columns: y:2(int) a.s:3(string) 503 ├── right columns: z:5(int) b.s:6(string) 504 ├── stats: [rows=5000] 505 ├── project 506 │ ├── columns: y:2(int) a.s:3(string) 507 │ ├── stats: [rows=5000] 508 │ └── scan a 509 │ ├── columns: a.x:1(int!null) y:2(int) a.s:3(string) 510 │ ├── stats: [rows=5000] 511 │ ├── key: (1) 512 │ └── fd: (1)-->(2,3) 513 └── project 514 ├── columns: z:5(int!null) b.s:6(string) 515 ├── stats: [rows=10000] 516 └── scan b 517 ├── columns: b.x:4(int) z:5(int!null) b.s:6(string) rowid:7(int!null) 518 ├── stats: [rows=10000] 519 ├── key: (7) 520 └── fd: (7)-->(4-6) 521 522 build 523 SELECT * FROM (SELECT y, s FROM a EXCEPT ALL SELECT z, s FROM b) WHERE y = 5 524 ---- 525 select 526 ├── columns: y:2(int!null) s:3(string) 527 ├── stats: [rows=12.5, distinct(2)=1, null(2)=0] 528 ├── fd: ()-->(2) 529 ├── except-all 530 │ ├── columns: y:2(int) a.s:3(string) 531 │ ├── left columns: y:2(int) a.s:3(string) 532 │ ├── right columns: z:5(int) b.s:6(string) 533 │ ├── stats: [rows=5000, distinct(2)=400, null(2)=0] 534 │ ├── project 535 │ │ ├── columns: y:2(int) a.s:3(string) 536 │ │ ├── stats: [rows=5000, distinct(2)=400, null(2)=0] 537 │ │ └── scan a 538 │ │ ├── columns: a.x:1(int!null) y:2(int) a.s:3(string) 539 │ │ ├── stats: [rows=5000, distinct(2)=400, null(2)=0] 540 │ │ ├── key: (1) 541 │ │ └── fd: (1)-->(2,3) 542 │ └── project 543 │ ├── columns: z:5(int!null) b.s:6(string) 544 │ ├── stats: [rows=10000, distinct(5)=100, null(5)=0] 545 │ └── scan b 546 │ ├── columns: b.x:4(int) z:5(int!null) b.s:6(string) rowid:7(int!null) 547 │ ├── stats: [rows=10000, distinct(5)=100, null(5)=0] 548 │ ├── key: (7) 549 │ └── fd: (7)-->(4-6) 550 └── filters 551 └── y:2 = 5 [type=bool, outer=(2), constraints=(/2: [/5 - /5]; tight), fd=()-->(2)] 552 553 # Bump up null counts. 554 exec-ddl 555 ALTER TABLE b INJECT STATISTICS '[ 556 { 557 "columns": ["x"], 558 "created_at": "2018-01-01 1:00:00.00000+00:00", 559 "row_count": 10000, 560 "distinct_count": 5000, 561 "null_count": 2500 562 }, 563 { 564 "columns": ["z"], 565 "created_at": "2018-01-01 1:30:00.00000+00:00", 566 "row_count": 10000, 567 "distinct_count": 100 568 }, 569 { 570 "columns": ["s"], 571 "created_at": "2018-01-01 1:30:00.00000+00:00", 572 "row_count": 10000, 573 "distinct_count": 10, 574 "null_count": 5000 575 }, 576 { 577 "columns": ["rowid"], 578 "created_at": "2018-01-01 1:30:00.00000+00:00", 579 "row_count": 10000, 580 "distinct_count": 10000 581 } 582 ]' 583 ---- 584 585 exec-ddl 586 ALTER TABLE c INJECT STATISTICS '[ 587 { 588 "columns": ["x"], 589 "created_at": "2018-01-01 1:00:00.00000+00:00", 590 "row_count": 10000, 591 "distinct_count": 5000, 592 "null_count": 1000 593 }, 594 { 595 "columns": ["z"], 596 "created_at": "2018-01-01 1:30:00.00000+00:00", 597 "row_count": 10000, 598 "distinct_count": 100 599 }, 600 { 601 "columns": ["s"], 602 "created_at": "2018-01-01 1:30:00.00000+00:00", 603 "row_count": 10000, 604 "distinct_count": 10, 605 "null_count": 7500 606 } 607 ]' 608 ---- 609 610 build colstat=9 colstat=10 colstat=(9,10) 611 SELECT x,s FROM b UNION SELECT x,s FROM c 612 ---- 613 union 614 ├── columns: x:9(int) s:10(string) 615 ├── left columns: b.x:1(int) b.s:3(string) 616 ├── right columns: c.x:5(int) c.s:7(string) 617 ├── stats: [rows=20000, distinct(9)=10000, null(9)=1, distinct(10)=20, null(10)=1, distinct(9,10)=20000, null(9,10)=1] 618 ├── key: (9,10) 619 ├── project 620 │ ├── columns: b.x:1(int) b.s:3(string) 621 │ ├── stats: [rows=10000, distinct(1)=5000, null(1)=2500, distinct(3)=10, null(3)=5000, distinct(1,3)=10000, null(1,3)=1250] 622 │ └── scan b 623 │ ├── columns: b.x:1(int) b.z:2(int!null) b.s:3(string) b.rowid:4(int!null) 624 │ ├── stats: [rows=10000, distinct(1)=5000, null(1)=2500, distinct(3)=10, null(3)=5000, distinct(1,3)=10000, null(1,3)=1250] 625 │ ├── key: (4) 626 │ └── fd: (4)-->(1-3) 627 └── project 628 ├── columns: c.x:5(int) c.s:7(string) 629 ├── stats: [rows=10000, distinct(5)=5000, null(5)=1000, distinct(7)=10, null(7)=7500, distinct(5,7)=10000, null(5,7)=750] 630 └── scan c 631 ├── columns: c.x:5(int) c.z:6(int!null) c.s:7(string) c.rowid:8(int!null) 632 ├── stats: [rows=10000, distinct(5)=5000, null(5)=1000, distinct(7)=10, null(7)=7500, distinct(5,7)=10000, null(5,7)=750] 633 ├── key: (8) 634 └── fd: (8)-->(5-7) 635 636 build colstat=1 colstat=3 colstat=(1,3) 637 SELECT x,s FROM b INTERSECT SELECT x,s FROM c 638 ---- 639 intersect 640 ├── columns: x:1(int) s:3(string) 641 ├── left columns: b.x:1(int) b.s:3(string) 642 ├── right columns: c.x:5(int) c.s:7(string) 643 ├── stats: [rows=10000, distinct(1)=5000, null(1)=1, distinct(3)=10, null(3)=1, distinct(1,3)=10000, null(1,3)=1] 644 ├── key: (1,3) 645 ├── project 646 │ ├── columns: b.x:1(int) b.s:3(string) 647 │ ├── stats: [rows=10000, distinct(1)=5000, null(1)=2500, distinct(3)=10, null(3)=5000, distinct(1,3)=10000, null(1,3)=1250] 648 │ └── scan b 649 │ ├── columns: b.x:1(int) b.z:2(int!null) b.s:3(string) b.rowid:4(int!null) 650 │ ├── stats: [rows=10000, distinct(1)=5000, null(1)=2500, distinct(3)=10, null(3)=5000, distinct(1,3)=10000, null(1,3)=1250] 651 │ ├── key: (4) 652 │ └── fd: (4)-->(1-3) 653 └── project 654 ├── columns: c.x:5(int) c.s:7(string) 655 ├── stats: [rows=10000, distinct(5)=5000, null(5)=1000, distinct(7)=10, null(7)=7500, distinct(5,7)=10000, null(5,7)=750] 656 └── scan c 657 ├── columns: c.x:5(int) c.z:6(int!null) c.s:7(string) c.rowid:8(int!null) 658 ├── stats: [rows=10000, distinct(5)=5000, null(5)=1000, distinct(7)=10, null(7)=7500, distinct(5,7)=10000, null(5,7)=750] 659 ├── key: (8) 660 └── fd: (8)-->(5-7) 661 662 build colstat=1 colstat=3 colstat=(1,3) 663 SELECT x,s FROM b EXCEPT SELECT x,s FROM c 664 ---- 665 except 666 ├── columns: x:1(int) s:3(string) 667 ├── left columns: b.x:1(int) b.s:3(string) 668 ├── right columns: c.x:5(int) c.s:7(string) 669 ├── stats: [rows=10000, distinct(1)=5000, null(1)=0, distinct(3)=10, null(3)=0, distinct(1,3)=10000, null(1,3)=0] 670 ├── key: (1,3) 671 ├── project 672 │ ├── columns: b.x:1(int) b.s:3(string) 673 │ ├── stats: [rows=10000, distinct(1)=5000, null(1)=2500, distinct(3)=10, null(3)=5000, distinct(1,3)=10000, null(1,3)=1250] 674 │ └── scan b 675 │ ├── columns: b.x:1(int) b.z:2(int!null) b.s:3(string) b.rowid:4(int!null) 676 │ ├── stats: [rows=10000, distinct(1)=5000, null(1)=2500, distinct(3)=10, null(3)=5000, distinct(1,3)=10000, null(1,3)=1250] 677 │ ├── key: (4) 678 │ └── fd: (4)-->(1-3) 679 └── project 680 ├── columns: c.x:5(int) c.s:7(string) 681 ├── stats: [rows=10000, distinct(5)=5000, null(5)=1000, distinct(7)=10, null(7)=7500, distinct(5,7)=10000, null(5,7)=750] 682 └── scan c 683 ├── columns: c.x:5(int) c.z:6(int!null) c.s:7(string) c.rowid:8(int!null) 684 ├── stats: [rows=10000, distinct(5)=5000, null(5)=1000, distinct(7)=10, null(7)=7500, distinct(5,7)=10000, null(5,7)=750] 685 ├── key: (8) 686 └── fd: (8)-->(5-7) 687 688 build 689 SELECT x FROM b UNION SELECT x FROM c 690 ---- 691 union 692 ├── columns: x:9(int) 693 ├── left columns: b.x:1(int) 694 ├── right columns: c.x:5(int) 695 ├── stats: [rows=10000, distinct(9)=10000, null(9)=1] 696 ├── key: (9) 697 ├── project 698 │ ├── columns: b.x:1(int) 699 │ ├── stats: [rows=10000, distinct(1)=5000, null(1)=2500] 700 │ └── scan b 701 │ ├── columns: b.x:1(int) b.z:2(int!null) b.s:3(string) b.rowid:4(int!null) 702 │ ├── stats: [rows=10000, distinct(1)=5000, null(1)=2500] 703 │ ├── key: (4) 704 │ └── fd: (4)-->(1-3) 705 └── project 706 ├── columns: c.x:5(int) 707 ├── stats: [rows=10000, distinct(5)=5000, null(5)=1000] 708 └── scan c 709 ├── columns: c.x:5(int) c.z:6(int!null) c.s:7(string) c.rowid:8(int!null) 710 ├── stats: [rows=10000, distinct(5)=5000, null(5)=1000] 711 ├── key: (8) 712 └── fd: (8)-->(5-7) 713 714 build 715 SELECT x FROM b INTERSECT SELECT x FROM c 716 ---- 717 intersect 718 ├── columns: x:1(int) 719 ├── left columns: b.x:1(int) 720 ├── right columns: c.x:5(int) 721 ├── stats: [rows=5000, distinct(1)=5000, null(1)=1] 722 ├── key: (1) 723 ├── project 724 │ ├── columns: b.x:1(int) 725 │ ├── stats: [rows=10000, distinct(1)=5000, null(1)=2500] 726 │ └── scan b 727 │ ├── columns: b.x:1(int) b.z:2(int!null) b.s:3(string) b.rowid:4(int!null) 728 │ ├── stats: [rows=10000, distinct(1)=5000, null(1)=2500] 729 │ ├── key: (4) 730 │ └── fd: (4)-->(1-3) 731 └── project 732 ├── columns: c.x:5(int) 733 ├── stats: [rows=10000, distinct(5)=5000, null(5)=1000] 734 └── scan c 735 ├── columns: c.x:5(int) c.z:6(int!null) c.s:7(string) c.rowid:8(int!null) 736 ├── stats: [rows=10000, distinct(5)=5000, null(5)=1000] 737 ├── key: (8) 738 └── fd: (8)-->(5-7) 739 740 build 741 SELECT x FROM b EXCEPT SELECT x FROM c 742 ---- 743 except 744 ├── columns: x:1(int) 745 ├── left columns: b.x:1(int) 746 ├── right columns: c.x:5(int) 747 ├── stats: [rows=5000, distinct(1)=5000, null(1)=0] 748 ├── key: (1) 749 ├── project 750 │ ├── columns: b.x:1(int) 751 │ ├── stats: [rows=10000, distinct(1)=5000, null(1)=2500] 752 │ └── scan b 753 │ ├── columns: b.x:1(int) b.z:2(int!null) b.s:3(string) b.rowid:4(int!null) 754 │ ├── stats: [rows=10000, distinct(1)=5000, null(1)=2500] 755 │ ├── key: (4) 756 │ └── fd: (4)-->(1-3) 757 └── project 758 ├── columns: c.x:5(int) 759 ├── stats: [rows=10000, distinct(5)=5000, null(5)=1000] 760 └── scan c 761 ├── columns: c.x:5(int) c.z:6(int!null) c.s:7(string) c.rowid:8(int!null) 762 ├── stats: [rows=10000, distinct(5)=5000, null(5)=1000] 763 ├── key: (8) 764 └── fd: (8)-->(5-7) 765 766 build colstat=9 colstat=10 colstat=(9,10) 767 SELECT x,s FROM b UNION ALL SELECT x,s FROM c 768 ---- 769 union-all 770 ├── columns: x:9(int) s:10(string) 771 ├── left columns: b.x:1(int) b.s:3(string) 772 ├── right columns: c.x:5(int) c.s:7(string) 773 ├── stats: [rows=20000, distinct(9)=10000, null(9)=3500, distinct(10)=20, null(10)=12500, distinct(9,10)=20000, null(9,10)=2000] 774 ├── project 775 │ ├── columns: b.x:1(int) b.s:3(string) 776 │ ├── stats: [rows=10000, distinct(1)=5000, null(1)=2500, distinct(3)=10, null(3)=5000, distinct(1,3)=10000, null(1,3)=1250] 777 │ └── scan b 778 │ ├── columns: b.x:1(int) b.z:2(int!null) b.s:3(string) b.rowid:4(int!null) 779 │ ├── stats: [rows=10000, distinct(1)=5000, null(1)=2500, distinct(3)=10, null(3)=5000, distinct(1,3)=10000, null(1,3)=1250] 780 │ ├── key: (4) 781 │ └── fd: (4)-->(1-3) 782 └── project 783 ├── columns: c.x:5(int) c.s:7(string) 784 ├── stats: [rows=10000, distinct(5)=5000, null(5)=1000, distinct(7)=10, null(7)=7500, distinct(5,7)=10000, null(5,7)=750] 785 └── scan c 786 ├── columns: c.x:5(int) c.z:6(int!null) c.s:7(string) c.rowid:8(int!null) 787 ├── stats: [rows=10000, distinct(5)=5000, null(5)=1000, distinct(7)=10, null(7)=7500, distinct(5,7)=10000, null(5,7)=750] 788 ├── key: (8) 789 └── fd: (8)-->(5-7) 790 791 build colstat=1 colstat=3 colstat=(1,3) 792 SELECT x,s FROM b INTERSECT ALL SELECT x,s FROM c 793 ---- 794 intersect-all 795 ├── columns: x:1(int) s:3(string) 796 ├── left columns: b.x:1(int) b.s:3(string) 797 ├── right columns: c.x:5(int) c.s:7(string) 798 ├── stats: [rows=10000, distinct(1)=5000, null(1)=1000, distinct(3)=10, null(3)=5000, distinct(1,3)=10000, null(1,3)=750] 799 ├── project 800 │ ├── columns: b.x:1(int) b.s:3(string) 801 │ ├── stats: [rows=10000, distinct(1)=5000, null(1)=2500, distinct(3)=10, null(3)=5000, distinct(1,3)=10000, null(1,3)=1250] 802 │ └── scan b 803 │ ├── columns: b.x:1(int) b.z:2(int!null) b.s:3(string) b.rowid:4(int!null) 804 │ ├── stats: [rows=10000, distinct(1)=5000, null(1)=2500, distinct(3)=10, null(3)=5000, distinct(1,3)=10000, null(1,3)=1250] 805 │ ├── key: (4) 806 │ └── fd: (4)-->(1-3) 807 └── project 808 ├── columns: c.x:5(int) c.s:7(string) 809 ├── stats: [rows=10000, distinct(5)=5000, null(5)=1000, distinct(7)=10, null(7)=7500, distinct(5,7)=10000, null(5,7)=750] 810 └── scan c 811 ├── columns: c.x:5(int) c.z:6(int!null) c.s:7(string) c.rowid:8(int!null) 812 ├── stats: [rows=10000, distinct(5)=5000, null(5)=1000, distinct(7)=10, null(7)=7500, distinct(5,7)=10000, null(5,7)=750] 813 ├── key: (8) 814 └── fd: (8)-->(5-7) 815 816 build colstat=1 colstat=3 colstat=(1,3) 817 SELECT x,s FROM b EXCEPT ALL SELECT x,s FROM c 818 ---- 819 except-all 820 ├── columns: x:1(int) s:3(string) 821 ├── left columns: b.x:1(int) b.s:3(string) 822 ├── right columns: c.x:5(int) c.s:7(string) 823 ├── stats: [rows=10000, distinct(1)=5000, null(1)=1500, distinct(3)=10, null(3)=0, distinct(1,3)=10000, null(1,3)=500] 824 ├── project 825 │ ├── columns: b.x:1(int) b.s:3(string) 826 │ ├── stats: [rows=10000, distinct(1)=5000, null(1)=2500, distinct(3)=10, null(3)=5000, distinct(1,3)=10000, null(1,3)=1250] 827 │ └── scan b 828 │ ├── columns: b.x:1(int) b.z:2(int!null) b.s:3(string) b.rowid:4(int!null) 829 │ ├── stats: [rows=10000, distinct(1)=5000, null(1)=2500, distinct(3)=10, null(3)=5000, distinct(1,3)=10000, null(1,3)=1250] 830 │ ├── key: (4) 831 │ └── fd: (4)-->(1-3) 832 └── project 833 ├── columns: c.x:5(int) c.s:7(string) 834 ├── stats: [rows=10000, distinct(5)=5000, null(5)=1000, distinct(7)=10, null(7)=7500, distinct(5,7)=10000, null(5,7)=750] 835 └── scan c 836 ├── columns: c.x:5(int) c.z:6(int!null) c.s:7(string) c.rowid:8(int!null) 837 ├── stats: [rows=10000, distinct(5)=5000, null(5)=1000, distinct(7)=10, null(7)=7500, distinct(5,7)=10000, null(5,7)=750] 838 ├── key: (8) 839 └── fd: (8)-->(5-7) 840 841 # Regression test for #35715. 842 opt colstat=(5,2) 843 SELECT * FROM 844 ((VALUES (NULL, true) EXCEPT (VALUES (1, NULL)))) AS t(a, b) 845 WHERE a IS NULL and b 846 ---- 847 except 848 ├── columns: a:5(int) b:2(bool!null) 849 ├── left columns: column1:5(int) column2:2(bool!null) 850 ├── right columns: column1:3(int) column2:4(bool) 851 ├── cardinality: [1 - 1] 852 ├── stats: [rows=1, distinct(2,5)=1, null(2,5)=0] 853 ├── key: (2,5) 854 ├── values 855 │ ├── columns: column2:2(bool!null) column1:5(int) 856 │ ├── cardinality: [1 - 1] 857 │ ├── stats: [rows=1, distinct(2,5)=1, null(2,5)=0] 858 │ ├── key: () 859 │ ├── fd: ()-->(2,5) 860 │ └── (true, NULL) [type=tuple{bool, int}] 861 └── values 862 ├── columns: column1:3(int!null) column2:4(bool!null) 863 ├── cardinality: [0 - 0] 864 ├── stats: [rows=0, distinct(3,4)=0, null(3,4)=0] 865 ├── key: () 866 └── fd: ()-->(3,4) 867 868 # Regression test for #36147 and #36157. 869 opt 870 SELECT * FROM 871 ((VALUES (NULL, NULL), (NULL, 1), (2, NULL)) EXCEPT (VALUES (1, 2), (2, 3), (3, 4))) 872 WHERE column1 IS NULL 873 ---- 874 except 875 ├── columns: column1:1(int) column2:2(int) 876 ├── left columns: column1:1(int) column2:2(int) 877 ├── right columns: column1:3(int) column2:4(int) 878 ├── cardinality: [0 - 3] 879 ├── stats: [rows=2, distinct(1,2)=2, null(1,2)=0.666666667] 880 ├── key: (1,2) 881 ├── select 882 │ ├── columns: column1:1(int) column2:2(int) 883 │ ├── cardinality: [0 - 3] 884 │ ├── stats: [rows=2, distinct(1)=1, null(1)=2, distinct(1,2)=2, null(1,2)=0.666666667] 885 │ ├── fd: ()-->(1) 886 │ ├── values 887 │ │ ├── columns: column1:1(int) column2:2(int) 888 │ │ ├── cardinality: [3 - 3] 889 │ │ ├── stats: [rows=3, distinct(1)=2, null(1)=2, distinct(1,2)=3, null(1,2)=1] 890 │ │ ├── (NULL, NULL) [type=tuple{int, int}] 891 │ │ ├── (NULL, 1) [type=tuple{int, int}] 892 │ │ └── (2, NULL) [type=tuple{int, int}] 893 │ └── filters 894 │ └── column1:1 IS NULL [type=bool, outer=(1), constraints=(/1: [/NULL - /NULL]; tight), fd=()-->(1)] 895 └── select 896 ├── columns: column1:3(int!null) column2:4(int!null) 897 ├── cardinality: [0 - 3] 898 ├── stats: [rows=1, distinct(3)=1, null(3)=0, distinct(3,4)=1, null(3,4)=0] 899 ├── fd: ()-->(3) 900 ├── values 901 │ ├── columns: column1:3(int!null) column2:4(int!null) 902 │ ├── cardinality: [3 - 3] 903 │ ├── stats: [rows=3, distinct(3)=3, null(3)=0, distinct(4)=3, null(4)=0, distinct(3,4)=3, null(3,4)=0] 904 │ ├── (1, 2) [type=tuple{int, int}] 905 │ ├── (2, 3) [type=tuple{int, int}] 906 │ └── (3, 4) [type=tuple{int, int}] 907 └── filters 908 └── column1:3 IS NULL [type=bool, outer=(3), constraints=(/3: [/NULL - /NULL]; tight), fd=()-->(3)] 909 910 # Make sure that we estimate at least 1 row for the intersect. 911 opt 912 VALUES (1) INTERSECT VALUES (NULL) ORDER BY 1 913 ---- 914 sort 915 ├── columns: column1:1(int) 916 ├── cardinality: [0 - 1] 917 ├── stats: [rows=1, distinct(1)=1, null(1)=0] 918 ├── key: (1) 919 ├── ordering: +1 920 └── intersect 921 ├── columns: column1:1(int) 922 ├── left columns: column1:1(int) 923 ├── right columns: column1:2(int) 924 ├── cardinality: [0 - 1] 925 ├── stats: [rows=1, distinct(1)=1, null(1)=0] 926 ├── key: (1) 927 ├── values 928 │ ├── columns: column1:1(int!null) 929 │ ├── cardinality: [1 - 1] 930 │ ├── stats: [rows=1, distinct(1)=1, null(1)=0] 931 │ ├── key: () 932 │ ├── fd: ()-->(1) 933 │ └── (1,) [type=tuple{int}] 934 └── values 935 ├── columns: column1:2(int) 936 ├── cardinality: [1 - 1] 937 ├── stats: [rows=1, distinct(2)=1, null(2)=1] 938 ├── key: () 939 ├── fd: ()-->(2) 940 └── (NULL,) [type=tuple{int}]