github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/xform/testdata/physprops/limit_hint (about) 1 exec-ddl 2 CREATE TABLE t (x INT PRIMARY KEY, y INT, z INT, index y_idx (y)) 3 ---- 4 5 # t has 200 rows where z=0, 200 where z=1, and 600 where z=2. 6 exec-ddl 7 ALTER TABLE t INJECT STATISTICS ' [ 8 { 9 "columns":[ 10 "z" 11 ], 12 "created_at":"2019-11-11 22:16:04.314619+00:00", 13 "distinct_count":3, 14 "histo_buckets":[ 15 { 16 "distinct_range":0, 17 "num_eq":200, 18 "num_range":0, 19 "upper_bound":"0" 20 }, 21 { 22 "distinct_range":0, 23 "num_eq":200, 24 "num_range":0, 25 "upper_bound":"1" 26 }, 27 { 28 "distinct_range":0, 29 "num_eq":600, 30 "num_range":0, 31 "upper_bound":"2" 32 } 33 ], 34 "histo_col_type":"INT8", 35 "name":"statistics", 36 "null_count":0, 37 "row_count":1000 38 } 39 ]' 40 ---- 41 42 # In order to test how limit hints are propagated through a particular operator, 43 # a limit operator must exist higher in the tree, and all operators between the 44 # limit and the operator targeted by the test must pass a limit hint to their 45 # children. An easy way to construct a plan like this is to set the limit's 46 # child to be an ordinality operator (which passes through limit hints 47 # unchanged), and order the limit by the ordinality column (to prevent 48 # normalization rules from pushing the limit down the tree). 49 50 # -------------------------------------------------- 51 # Offset operator. 52 # -------------------------------------------------- 53 54 opt 55 SELECT * FROM (SELECT * FROM t OFFSET 5) WITH ORDINALITY ORDER BY ordinality LIMIT 10 56 ---- 57 limit 58 ├── columns: x:1!null y:2 z:3 ordinality:4!null 59 ├── internal-ordering: +4 60 ├── cardinality: [0 - 10] 61 ├── key: (1) 62 ├── fd: (1)-->(2-4), (4)-->(1-3) 63 ├── ordering: +4 64 ├── ordinality 65 │ ├── columns: x:1!null y:2 z:3 ordinality:4!null 66 │ ├── key: (1) 67 │ ├── fd: (1)-->(2-4), (4)-->(1-3) 68 │ ├── ordering: +4 69 │ ├── limit hint: 10.00 70 │ └── offset 71 │ ├── columns: x:1!null y:2 z:3 72 │ ├── key: (1) 73 │ ├── fd: (1)-->(2,3) 74 │ ├── limit hint: 10.00 75 │ ├── scan t 76 │ │ ├── columns: x:1!null y:2 z:3 77 │ │ ├── key: (1) 78 │ │ ├── fd: (1)-->(2,3) 79 │ │ └── limit hint: 15.00 80 │ └── 5 81 └── 10 82 83 84 # -------------------------------------------------- 85 # Set operators. 86 # -------------------------------------------------- 87 88 opt 89 SELECT * FROM (SELECT * FROM t UNION SELECT * from t) LIMIT 10 90 ---- 91 limit 92 ├── columns: x:7!null y:8 z:9 93 ├── cardinality: [0 - 10] 94 ├── key: (7-9) 95 ├── union 96 │ ├── columns: x:7!null y:8 z:9 97 │ ├── left columns: t.x:1 t.y:2 t.z:3 98 │ ├── right columns: t.x:4 t.y:5 t.z:6 99 │ ├── key: (7-9) 100 │ ├── limit hint: 10.00 101 │ ├── scan t 102 │ │ ├── columns: t.x:1!null t.y:2 t.z:3 103 │ │ ├── key: (1) 104 │ │ ├── fd: (1)-->(2,3) 105 │ │ └── limit hint: 10.00 106 │ └── scan t 107 │ ├── columns: t.x:4!null t.y:5 t.z:6 108 │ ├── key: (4) 109 │ ├── fd: (4)-->(5,6) 110 │ └── limit hint: 10.00 111 └── 10 112 113 opt 114 SELECT * FROM (SELECT * FROM t UNION ALL SELECT * from t) LIMIT 10 115 ---- 116 limit 117 ├── columns: x:7!null y:8 z:9 118 ├── cardinality: [0 - 10] 119 ├── union-all 120 │ ├── columns: x:7!null y:8 z:9 121 │ ├── left columns: t.x:1 t.y:2 t.z:3 122 │ ├── right columns: t.x:4 t.y:5 t.z:6 123 │ ├── limit hint: 10.00 124 │ ├── scan t 125 │ │ ├── columns: t.x:1!null t.y:2 t.z:3 126 │ │ ├── key: (1) 127 │ │ ├── fd: (1)-->(2,3) 128 │ │ └── limit hint: 10.00 129 │ └── scan t 130 │ ├── columns: t.x:4!null t.y:5 t.z:6 131 │ ├── key: (4) 132 │ ├── fd: (4)-->(5,6) 133 │ └── limit hint: 10.00 134 └── 10 135 136 opt 137 SELECT * FROM (SELECT z FROM t INTERSECT SELECT y from t) LIMIT 10 138 ---- 139 limit 140 ├── columns: z:3 141 ├── cardinality: [0 - 10] 142 ├── key: (3) 143 ├── intersect 144 │ ├── columns: z:3 145 │ ├── left columns: z:3 146 │ ├── right columns: y:5 147 │ ├── key: (3) 148 │ ├── limit hint: 10.00 149 │ ├── scan t 150 │ │ ├── columns: z:3 151 │ │ └── limit hint: 10.00 152 │ └── scan t@y_idx 153 │ ├── columns: y:5 154 │ └── limit hint: 10.00 155 └── 10 156 157 opt 158 SELECT * FROM (SELECT z FROM t INTERSECT ALL SELECT y from t) LIMIT 10 159 ---- 160 limit 161 ├── columns: z:3 162 ├── cardinality: [0 - 10] 163 ├── intersect-all 164 │ ├── columns: z:3 165 │ ├── left columns: z:3 166 │ ├── right columns: y:5 167 │ ├── limit hint: 10.00 168 │ ├── scan t 169 │ │ ├── columns: z:3 170 │ │ └── limit hint: 10.00 171 │ └── scan t@y_idx 172 │ ├── columns: y:5 173 │ └── limit hint: 10.00 174 └── 10 175 176 opt 177 SELECT * FROM (SELECT z FROM t EXCEPT SELECT y from t) LIMIT 10 178 ---- 179 limit 180 ├── columns: z:3 181 ├── cardinality: [0 - 10] 182 ├── key: (3) 183 ├── except 184 │ ├── columns: z:3 185 │ ├── left columns: z:3 186 │ ├── right columns: y:5 187 │ ├── key: (3) 188 │ ├── limit hint: 10.00 189 │ ├── scan t 190 │ │ ├── columns: z:3 191 │ │ └── limit hint: 10.00 192 │ └── scan t@y_idx 193 │ ├── columns: y:5 194 │ └── limit hint: 10.00 195 └── 10 196 197 opt 198 SELECT * FROM (SELECT z FROM t EXCEPT ALL SELECT y from t) LIMIT 10 199 ---- 200 limit 201 ├── columns: z:3 202 ├── cardinality: [0 - 10] 203 ├── except-all 204 │ ├── columns: z:3 205 │ ├── left columns: z:3 206 │ ├── right columns: y:5 207 │ ├── limit hint: 10.00 208 │ ├── scan t 209 │ │ ├── columns: z:3 210 │ │ └── limit hint: 10.00 211 │ └── scan t@y_idx 212 │ ├── columns: y:5 213 │ └── limit hint: 10.00 214 └── 10 215 216 # -------------------------------------------------- 217 # Limit hint depends on statistics. 218 # -------------------------------------------------- 219 220 # Select operator. 221 opt 222 SELECT * FROM t WHERE z=1 LIMIT 10 223 ---- 224 limit 225 ├── columns: x:1!null y:2 z:3!null 226 ├── cardinality: [0 - 10] 227 ├── key: (1) 228 ├── fd: ()-->(3), (1)-->(2) 229 ├── select 230 │ ├── columns: x:1!null y:2 z:3!null 231 │ ├── key: (1) 232 │ ├── fd: ()-->(3), (1)-->(2) 233 │ ├── limit hint: 10.00 234 │ ├── scan t 235 │ │ ├── columns: x:1!null y:2 z:3 236 │ │ ├── key: (1) 237 │ │ ├── fd: (1)-->(2,3) 238 │ │ └── limit hint: 50.00 239 │ └── filters 240 │ └── z:3 = 1 [outer=(3), fd=()-->(3)] 241 └── 10 242 243 # DistinctOn operator. 244 opt 245 SELECT DISTINCT z FROM t LIMIT 1 246 ---- 247 limit 248 ├── columns: z:3 249 ├── cardinality: [0 - 1] 250 ├── key: () 251 ├── fd: ()-->(3) 252 ├── distinct-on 253 │ ├── columns: z:3 254 │ ├── grouping columns: z:3 255 │ ├── key: (3) 256 │ ├── limit hint: 1.00 257 │ └── scan t 258 │ ├── columns: z:3 259 │ └── limit hint: 1.23 260 └── 1 261 262 # No limit hint propagation if number of distinct rows < required number of rows. 263 opt 264 SELECT DISTINCT z FROM t LIMIT 10 265 ---- 266 limit 267 ├── columns: z:3 268 ├── cardinality: [0 - 10] 269 ├── key: (3) 270 ├── distinct-on 271 │ ├── columns: z:3 272 │ ├── grouping columns: z:3 273 │ ├── key: (3) 274 │ ├── limit hint: 10.00 275 │ └── scan t 276 │ └── columns: z:3 277 └── 10 278 279 opt 280 SELECT * FROM t WHERE z=4 LIMIT 10 281 ---- 282 limit 283 ├── columns: x:1!null y:2 z:3!null 284 ├── cardinality: [0 - 10] 285 ├── key: (1) 286 ├── fd: ()-->(3), (1)-->(2) 287 ├── select 288 │ ├── columns: x:1!null y:2 z:3!null 289 │ ├── key: (1) 290 │ ├── fd: ()-->(3), (1)-->(2) 291 │ ├── limit hint: 10.00 292 │ ├── scan t 293 │ │ ├── columns: x:1!null y:2 z:3 294 │ │ ├── key: (1) 295 │ │ └── fd: (1)-->(2,3) 296 │ └── filters 297 │ └── z:3 = 4 [outer=(3), fd=()-->(3)] 298 └── 10 299 300 301 # -------------------------------------------------- 302 # Passing limit hint through unchanged. 303 # -------------------------------------------------- 304 305 # IndexJoin operator. 306 opt 307 SELECT z FROM t@y_idx WITH ORDINALITY ORDER BY ordinality LIMIT 10 308 ---- 309 limit 310 ├── columns: z:3 [hidden: ordinality:4!null] 311 ├── internal-ordering: +4 312 ├── cardinality: [0 - 10] 313 ├── key: (4) 314 ├── fd: (4)-->(3) 315 ├── ordering: +4 316 ├── ordinality 317 │ ├── columns: z:3 ordinality:4!null 318 │ ├── key: (4) 319 │ ├── fd: (4)-->(3) 320 │ ├── ordering: +4 321 │ ├── limit hint: 10.00 322 │ └── index-join t 323 │ ├── columns: z:3 324 │ ├── limit hint: 10.00 325 │ └── scan t@y_idx 326 │ ├── columns: x:1!null 327 │ ├── flags: force-index=y_idx 328 │ ├── key: (1) 329 │ └── limit hint: 10.00 330 └── 10 331 332 # Ordinality operator. 333 opt 334 SELECT * FROM t WITH ORDINALITY ORDER BY ordinality LIMIT 10 335 ---- 336 limit 337 ├── columns: x:1!null y:2 z:3 ordinality:4!null 338 ├── internal-ordering: +4 339 ├── cardinality: [0 - 10] 340 ├── key: (1) 341 ├── fd: (1)-->(2-4), (4)-->(1-3) 342 ├── ordering: +4 343 ├── ordinality 344 │ ├── columns: x:1!null y:2 z:3 ordinality:4!null 345 │ ├── key: (1) 346 │ ├── fd: (1)-->(2-4), (4)-->(1-3) 347 │ ├── ordering: +4 348 │ ├── limit hint: 10.00 349 │ └── scan t 350 │ ├── columns: x:1!null y:2 z:3 351 │ ├── key: (1) 352 │ ├── fd: (1)-->(2,3) 353 │ └── limit hint: 10.00 354 └── 10 355 356 # Project operator. 357 opt 358 SELECT * FROM (SELECT 1 FROM t) WITH ORDINALITY ORDER BY ordinality LIMIT 10 359 ---- 360 limit 361 ├── columns: "?column?":4!null ordinality:5!null 362 ├── internal-ordering: +5 opt(4) 363 ├── cardinality: [0 - 10] 364 ├── key: (5) 365 ├── fd: ()-->(4) 366 ├── ordering: +5 opt(4) [actual: +5] 367 ├── ordinality 368 │ ├── columns: "?column?":4!null ordinality:5!null 369 │ ├── key: (5) 370 │ ├── fd: ()-->(4) 371 │ ├── ordering: +5 opt(4) [actual: +5] 372 │ ├── limit hint: 10.00 373 │ └── project 374 │ ├── columns: "?column?":4!null 375 │ ├── fd: ()-->(4) 376 │ ├── limit hint: 10.00 377 │ ├── scan t@y_idx 378 │ │ └── limit hint: 10.00 379 │ └── projections 380 │ └── 1 [as="?column?":4] 381 └── 10 382 383 # ProjectSet operator. 384 opt 385 SELECT *, generate_series(1, t.x) FROM t LIMIT 10 386 ---- 387 limit 388 ├── columns: x:1!null y:2 z:3 generate_series:4 389 ├── cardinality: [0 - 10] 390 ├── immutable, side-effects 391 ├── fd: (1)-->(2,3) 392 ├── project-set 393 │ ├── columns: x:1!null y:2 z:3 generate_series:4 394 │ ├── immutable, side-effects 395 │ ├── fd: (1)-->(2,3) 396 │ ├── limit hint: 10.00 397 │ ├── scan t 398 │ │ ├── columns: x:1!null y:2 z:3 399 │ │ ├── key: (1) 400 │ │ ├── fd: (1)-->(2,3) 401 │ │ └── limit hint: 10.00 402 │ └── zip 403 │ └── generate_series(1, x:1) [outer=(1), immutable, side-effects] 404 └── 10 405 406 # -------------------------------------------------- 407 # Lookup join. 408 # -------------------------------------------------- 409 410 exec-ddl 411 CREATE TABLE a (k INT PRIMARY KEY, i INT, s STRING, d DECIMAL NOT NULL) 412 ---- 413 414 exec-ddl 415 CREATE TABLE b (x INT, z INT NOT NULL) 416 ---- 417 418 exec-ddl 419 ALTER TABLE a INJECT STATISTICS '[ 420 { 421 "columns": ["k"], 422 "created_at": "2019-02-08 04:10:40.001179+00:00", 423 "row_count": 100000, 424 "distinct_count": 100000 425 } 426 ]' 427 ---- 428 429 exec-ddl 430 ALTER TABLE b INJECT STATISTICS '[ 431 { 432 "columns": ["x"], 433 "created_at": "2019-02-08 04:10:40.001179+00:00", 434 "row_count": 10000, 435 "distinct_count": 1000 436 } 437 ]' 438 ---- 439 440 # Ensure the limit hint is propagated to the lookup join input as a multiple 441 # of the batch size. 442 opt 443 SELECT * FROM a JOIN b ON k=z WHERE x > 0 AND x <= 5000 LIMIT 6003 444 ---- 445 limit 446 ├── columns: k:1!null i:2 s:3 d:4!null x:5!null z:6!null 447 ├── cardinality: [0 - 6003] 448 ├── fd: (1)-->(2-4), (1)==(6), (6)==(1) 449 ├── inner-join (lookup a) 450 │ ├── columns: k:1!null i:2 s:3 d:4!null x:5!null z:6!null 451 │ ├── key columns: [6] = [1] 452 │ ├── lookup columns are key 453 │ ├── fd: (1)-->(2-4), (1)==(6), (6)==(1) 454 │ ├── limit hint: 6003.00 455 │ ├── select 456 │ │ ├── columns: x:5!null z:6!null 457 │ │ ├── limit hint: 6100.00 458 │ │ ├── scan b 459 │ │ │ ├── columns: x:5 z:6!null 460 │ │ │ └── limit hint: 6100.00 461 │ │ └── filters 462 │ │ └── (x:5 > 0) AND (x:5 <= 5000) [outer=(5)] 463 │ └── filters (true) 464 └── 6003 465 466 # The limit hint for the lookup join input must be at least the batch size. 467 opt 468 SELECT * FROM a JOIN b ON k=z WHERE x > 0 AND x <= 5000 LIMIT 3 469 ---- 470 limit 471 ├── columns: k:1!null i:2 s:3 d:4!null x:5!null z:6!null 472 ├── cardinality: [0 - 3] 473 ├── fd: (1)-->(2-4), (1)==(6), (6)==(1) 474 ├── inner-join (lookup a) 475 │ ├── columns: k:1!null i:2 s:3 d:4!null x:5!null z:6!null 476 │ ├── key columns: [6] = [1] 477 │ ├── lookup columns are key 478 │ ├── fd: (1)-->(2-4), (1)==(6), (6)==(1) 479 │ ├── limit hint: 3.00 480 │ ├── select 481 │ │ ├── columns: x:5!null z:6!null 482 │ │ ├── limit hint: 100.00 483 │ │ ├── scan b 484 │ │ │ ├── columns: x:5 z:6!null 485 │ │ │ └── limit hint: 100.00 486 │ │ └── filters 487 │ │ └── (x:5 > 0) AND (x:5 <= 5000) [outer=(5)] 488 │ └── filters (true) 489 └── 3 490 491 # -------------------------------------------------- 492 # Negative limits. 493 # -------------------------------------------------- 494 495 # Regression test for #44683. 496 exec-ddl 497 CREATE TABLE t44683(c0 INT) 498 ---- 499 500 exec-ddl 501 CREATE VIEW v44683(c0) AS SELECT 1 FROM t44683 LIMIT -1 502 ---- 503 504 opt 505 SELECT DISTINCT t44683.c0 FROM t44683, v44683 LIMIT -1; 506 ---- 507 limit 508 ├── columns: c0:1 509 ├── cardinality: [0 - 0] 510 ├── immutable, side-effects 511 ├── key: () 512 ├── fd: ()-->(1) 513 ├── distinct-on 514 │ ├── columns: c0:1 515 │ ├── grouping columns: c0:1 516 │ ├── cardinality: [0 - 0] 517 │ ├── immutable, side-effects 518 │ ├── key: (1) 519 │ ├── limit hint: 1.00 520 │ └── inner-join (cross) 521 │ ├── columns: c0:1 522 │ ├── cardinality: [0 - 0] 523 │ ├── immutable, side-effects 524 │ ├── scan t44683 525 │ │ └── columns: c0:1 526 │ ├── limit 527 │ │ ├── cardinality: [0 - 0] 528 │ │ ├── immutable, side-effects 529 │ │ ├── key: () 530 │ │ ├── scan t44683 531 │ │ │ └── limit hint: 1.00 532 │ │ └── -1 533 │ └── filters (true) 534 └── -1 535 536 exec-ddl 537 CREATE TABLE t0(c0 INT UNIQUE) 538 ---- 539 540 exec-ddl 541 CREATE TABLE t1(c0 INT) 542 ---- 543 544 exec-ddl 545 CREATE VIEW v0(c0) AS SELECT 0 FROM t1 LIMIT -1 546 ---- 547 548 # Regression test for #46187. Ensure that the estimated cost of a lookup join 549 # with a limit hint is finite when the number of output rows is 0. 550 opt 551 SELECT * FROM v0, t0 NATURAL JOIN t1 LIMIT -1 552 ---- 553 project 554 ├── columns: c0:3!null c0:4!null 555 ├── cardinality: [0 - 0] 556 ├── immutable, side-effects 557 ├── key: () 558 ├── fd: ()-->(3,4) 559 └── limit 560 ├── columns: "?column?":3!null t0.c0:4!null t1.c0:6!null 561 ├── cardinality: [0 - 0] 562 ├── immutable, side-effects 563 ├── key: () 564 ├── fd: ()-->(3,4,6) 565 ├── inner-join (lookup t0@t0_c0_key) 566 │ ├── columns: "?column?":3!null t0.c0:4!null t1.c0:6!null 567 │ ├── key columns: [6] = [4] 568 │ ├── lookup columns are key 569 │ ├── cardinality: [0 - 0] 570 │ ├── immutable, side-effects 571 │ ├── fd: ()-->(3), (4)==(6), (6)==(4) 572 │ ├── limit hint: 1.00 573 │ ├── inner-join (cross) 574 │ │ ├── columns: "?column?":3!null t1.c0:6 575 │ │ ├── cardinality: [0 - 0] 576 │ │ ├── immutable, side-effects 577 │ │ ├── fd: ()-->(3) 578 │ │ ├── scan t1 579 │ │ │ └── columns: t1.c0:6 580 │ │ ├── project 581 │ │ │ ├── columns: "?column?":3!null 582 │ │ │ ├── cardinality: [0 - 0] 583 │ │ │ ├── immutable, side-effects 584 │ │ │ ├── key: () 585 │ │ │ ├── fd: ()-->(3) 586 │ │ │ ├── limit 587 │ │ │ │ ├── cardinality: [0 - 0] 588 │ │ │ │ ├── immutable, side-effects 589 │ │ │ │ ├── key: () 590 │ │ │ │ ├── scan t1 591 │ │ │ │ │ └── limit hint: 1.00 592 │ │ │ │ └── -1 593 │ │ │ └── projections 594 │ │ │ └── 0 [as="?column?":3] 595 │ │ └── filters (true) 596 │ └── filters (true) 597 └── -1