github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/norm/testdata/rules/window (about) 1 exec-ddl 2 CREATE TABLE a (k INT PRIMARY KEY, i INT, f FLOAT, s STRING, j JSON) 3 ---- 4 5 # -------------------------------------------------- 6 # ReduceWindowPartitionCols 7 # -------------------------------------------------- 8 9 norm expect=ReduceWindowPartitionCols 10 SELECT rank() OVER (PARTITION BY k, i) FROM a 11 ---- 12 project 13 ├── columns: rank:6 14 └── window partition=(1) 15 ├── columns: k:1!null rank:6 16 ├── key: (1) 17 ├── scan a 18 │ ├── columns: k:1!null 19 │ └── key: (1) 20 └── windows 21 └── rank [as=rank:6] 22 23 norm expect=ReduceWindowPartitionCols 24 SELECT rank() OVER (PARTITION BY i, i+1) FROM a 25 ---- 26 project 27 ├── columns: rank:6 28 └── window partition=(2) 29 ├── columns: i:2 rank:6 30 ├── scan a 31 │ └── columns: i:2 32 └── windows 33 └── rank [as=rank:6] 34 35 # -------------------------------------------------- 36 # SimplifyWindowOrdering 37 # -------------------------------------------------- 38 39 norm expect=SimplifyWindowOrdering 40 SELECT rank() OVER (ORDER BY k, i) FROM a 41 ---- 42 project 43 ├── columns: rank:6 44 └── window partition=() ordering=+1 45 ├── columns: k:1!null rank:6 46 ├── key: (1) 47 ├── scan a 48 │ ├── columns: k:1!null 49 │ └── key: (1) 50 └── windows 51 └── rank [as=rank:6] 52 53 # We can simplify the ordering with the knowledge that within any partition 54 # the set of partition cols is held constant. 55 56 # TODO(justin): ensure these are fixed once we handle framing. 57 norm 58 SELECT rank() OVER (PARTITION BY k ORDER BY i) FROM a 59 ---- 60 project 61 ├── columns: rank:6 62 └── window partition=(1) 63 ├── columns: k:1!null rank:6 64 ├── key: (1) 65 ├── scan a 66 │ ├── columns: k:1!null 67 │ └── key: (1) 68 └── windows 69 └── rank [as=rank:6] 70 71 norm expect=SimplifyWindowOrdering 72 SELECT rank() OVER (PARTITION BY i ORDER BY f, i+1) FROM a 73 ---- 74 project 75 ├── columns: rank:6 76 └── window partition=(2) ordering=+3 opt(2,7) 77 ├── columns: i:2 f:3 rank:6 78 ├── scan a 79 │ └── columns: i:2 f:3 80 └── windows 81 └── rank [as=rank:6] 82 83 norm expect=SimplifyWindowOrdering 84 SELECT rank() OVER (PARTITION BY f ORDER BY i) FROM a 85 ---- 86 project 87 ├── columns: rank:6 88 └── window partition=(3) ordering=+2 opt(3) 89 ├── columns: i:2 f:3 rank:6 90 ├── scan a 91 │ └── columns: i:2 f:3 92 └── windows 93 └── rank [as=rank:6] 94 95 # PushSelectIntoWindow 96 97 norm expect=PushSelectIntoWindow 98 SELECT * FROM (SELECT i, rank() OVER (PARTITION BY i) FROM a) WHERE i > 4 99 ---- 100 window partition=(2) 101 ├── columns: i:2!null rank:6 102 ├── select 103 │ ├── columns: i:2!null 104 │ ├── scan a 105 │ │ └── columns: i:2 106 │ └── filters 107 │ └── i:2 > 4 [outer=(2), constraints=(/2: [/5 - ]; tight)] 108 └── windows 109 └── rank [as=rank:6] 110 111 # Only push down filters bound by the partition cols. 112 norm expect=PushSelectIntoWindow 113 SELECT * FROM (SELECT i, s, rank() OVER (PARTITION BY i) FROM a) WHERE i > 4 AND s = 'foo' 114 ---- 115 select 116 ├── columns: i:2!null s:4!null rank:6 117 ├── fd: ()-->(4) 118 ├── window partition=(2) 119 │ ├── columns: i:2!null s:4 rank:6 120 │ ├── select 121 │ │ ├── columns: i:2!null s:4 122 │ │ ├── scan a 123 │ │ │ └── columns: i:2 s:4 124 │ │ └── filters 125 │ │ └── i:2 > 4 [outer=(2), constraints=(/2: [/5 - ]; tight)] 126 │ └── windows 127 │ └── rank [as=rank:6] 128 └── filters 129 └── s:4 = 'foo' [outer=(4), constraints=(/4: [/'foo' - /'foo']; tight), fd=()-->(4)] 130 131 # Multiple partition cols. 132 norm expect=PushSelectIntoWindow 133 SELECT * FROM (SELECT i, s, f, rank() OVER (PARTITION BY i, f) FROM a) WHERE i > 4 AND f = 3.0 AND s = 'foo' 134 ---- 135 select 136 ├── columns: i:2!null s:4!null f:3!null rank:6 137 ├── fd: ()-->(3,4) 138 ├── window partition=(2) 139 │ ├── columns: i:2!null f:3!null s:4 rank:6 140 │ ├── fd: ()-->(3) 141 │ ├── select 142 │ │ ├── columns: i:2!null f:3!null s:4 143 │ │ ├── fd: ()-->(3) 144 │ │ ├── scan a 145 │ │ │ └── columns: i:2 f:3 s:4 146 │ │ └── filters 147 │ │ ├── i:2 > 4 [outer=(2), constraints=(/2: [/5 - ]; tight)] 148 │ │ └── f:3 = 3.0 [outer=(3), constraints=(/3: [/3.0 - /3.0]; tight), fd=()-->(3)] 149 │ └── windows 150 │ └── rank [as=rank:6] 151 └── filters 152 └── s:4 = 'foo' [outer=(4), constraints=(/4: [/'foo' - /'foo']; tight), fd=()-->(4)] 153 154 # This is not really correct, but there isn't a precedent for rejecting such filters. 155 # TODO(justin): consider revisiting this and not pushing this filter down. 156 norm expect=PushSelectIntoWindow 157 SELECT * FROM (SELECT i, s, f, rank() OVER (PARTITION BY i, f) FROM a) WHERE random() < 0.5 158 ---- 159 window partition=(2,3) 160 ├── columns: i:2 s:4 f:3 rank:6 161 ├── volatile, side-effects 162 ├── select 163 │ ├── columns: i:2 f:3 s:4 164 │ ├── volatile, side-effects 165 │ ├── scan a 166 │ │ └── columns: i:2 f:3 s:4 167 │ └── filters 168 │ └── random() < 0.5 [volatile, side-effects] 169 └── windows 170 └── rank [as=rank:6] 171 172 # Can't push down a filter on an ordering column. 173 norm expect-not=PushSelectIntoWindow 174 SELECT * FROM (SELECT f, rank() OVER (PARTITION BY i ORDER BY f) FROM a) WHERE f > 4 175 ---- 176 project 177 ├── columns: f:3!null rank:6 178 └── select 179 ├── columns: i:2 f:3!null rank:6 180 ├── window partition=(2) ordering=+3 opt(2) 181 │ ├── columns: i:2 f:3 rank:6 182 │ ├── scan a 183 │ │ └── columns: i:2 f:3 184 │ └── windows 185 │ └── rank [as=rank:6] 186 └── filters 187 └── f:3 > 4.0 [outer=(3), constraints=(/3: [/4.000000000000001 - ]; tight)] 188 189 # Can't push down a filter on an arbitrary column. 190 norm expect-not=PushSelectIntoWindow 191 SELECT * FROM (SELECT s, rank() OVER (PARTITION BY i ORDER BY f) FROM a) WHERE s = 'foo' 192 ---- 193 project 194 ├── columns: s:4!null rank:6 195 ├── fd: ()-->(4) 196 └── select 197 ├── columns: i:2 f:3 s:4!null rank:6 198 ├── fd: ()-->(4) 199 ├── window partition=(2) ordering=+3 opt(2) 200 │ ├── columns: i:2 f:3 s:4 rank:6 201 │ ├── scan a 202 │ │ └── columns: i:2 f:3 s:4 203 │ └── windows 204 │ └── rank [as=rank:6] 205 └── filters 206 └── s:4 = 'foo' [outer=(4), constraints=(/4: [/'foo' - /'foo']; tight), fd=()-->(4)] 207 208 # Can push down filters on columns in the closure of the partition columns. 209 norm expect=PushSelectIntoWindow 210 SELECT * FROM (SELECT i, rank() OVER (PARTITION BY k ORDER BY f) FROM a) WHERE i = 3 211 ---- 212 project 213 ├── columns: i:2!null rank:6 214 ├── fd: ()-->(2) 215 └── window partition=(1) 216 ├── columns: k:1!null i:2!null rank:6 217 ├── key: (1) 218 ├── fd: ()-->(2) 219 ├── select 220 │ ├── columns: k:1!null i:2!null 221 │ ├── key: (1) 222 │ ├── fd: ()-->(2) 223 │ ├── scan a 224 │ │ ├── columns: k:1!null i:2 225 │ │ ├── key: (1) 226 │ │ └── fd: (1)-->(2) 227 │ └── filters 228 │ └── i:2 = 3 [outer=(2), constraints=(/2: [/3 - /3]; tight), fd=()-->(2)] 229 └── windows 230 └── rank [as=rank:6] 231 232 norm expect=PushSelectIntoWindow 233 SELECT * FROM (SELECT i, f, rank() OVER (PARTITION BY k ORDER BY f) FROM a) WHERE i*f::int = 3 234 ---- 235 project 236 ├── columns: i:2 f:3 rank:6 237 └── window partition=(1) 238 ├── columns: k:1!null i:2 f:3 rank:6 239 ├── key: (1) 240 ├── fd: (1)-->(2,3) 241 ├── select 242 │ ├── columns: k:1!null i:2 f:3 243 │ ├── key: (1) 244 │ ├── fd: (1)-->(2,3) 245 │ ├── scan a 246 │ │ ├── columns: k:1!null i:2 f:3 247 │ │ ├── key: (1) 248 │ │ └── fd: (1)-->(2,3) 249 │ └── filters 250 │ └── (i:2 * f:3::INT8) = 3 [outer=(2,3)] 251 └── windows 252 └── rank [as=rank:6] 253 254 norm expect-not=PushSelectIntoWindow 255 SELECT * FROM (SELECT i, f, rank() OVER (PARTITION BY k ORDER BY f) AS rnk FROM a) WHERE rnk = 3 256 ---- 257 project 258 ├── columns: i:2 f:3 rnk:6!null 259 ├── fd: ()-->(6) 260 └── select 261 ├── columns: k:1!null i:2 f:3 rank:6!null 262 ├── key: (1) 263 ├── fd: ()-->(6), (1)-->(2,3) 264 ├── window partition=(1) 265 │ ├── columns: k:1!null i:2 f:3 rank:6 266 │ ├── key: (1) 267 │ ├── fd: (1)-->(2,3) 268 │ ├── scan a 269 │ │ ├── columns: k:1!null i:2 f:3 270 │ │ ├── key: (1) 271 │ │ └── fd: (1)-->(2,3) 272 │ └── windows 273 │ └── rank [as=rank:6] 274 └── filters 275 └── rank:6 = 3 [outer=(6), constraints=(/6: [/3 - /3]; tight), fd=()-->(6)] 276 277 # -------------------------------------------------- 278 # PushLimitIntoWindow 279 # -------------------------------------------------- 280 281 norm 282 SELECT rank() OVER () FROM a LIMIT 10 283 ---- 284 window partition=() 285 ├── columns: rank:6 286 ├── cardinality: [0 - 10] 287 ├── limit 288 │ ├── cardinality: [0 - 10] 289 │ ├── scan a 290 │ │ └── limit hint: 10.00 291 │ └── 10 292 └── windows 293 └── rank [as=rank:6] 294 295 norm 296 SELECT rank() OVER (PARTITION BY i) FROM a LIMIT 10 297 ---- 298 project 299 ├── columns: rank:6 300 ├── cardinality: [0 - 10] 301 └── window partition=(2) 302 ├── columns: i:2 rank:6 303 ├── cardinality: [0 - 10] 304 ├── limit 305 │ ├── columns: i:2 306 │ ├── internal-ordering: +2 307 │ ├── cardinality: [0 - 10] 308 │ ├── sort 309 │ │ ├── columns: i:2 310 │ │ ├── ordering: +2 311 │ │ ├── limit hint: 10.00 312 │ │ └── scan a 313 │ │ └── columns: i:2 314 │ └── 10 315 └── windows 316 └── rank [as=rank:6] 317 318 norm 319 SELECT rank() OVER (PARTITION BY i ORDER BY f) FROM a LIMIT 10 320 ---- 321 project 322 ├── columns: rank:6 323 ├── cardinality: [0 - 10] 324 └── window partition=(2) ordering=+3 opt(2) 325 ├── columns: i:2 f:3 rank:6 326 ├── cardinality: [0 - 10] 327 ├── limit 328 │ ├── columns: i:2 f:3 329 │ ├── internal-ordering: +2,+3 330 │ ├── cardinality: [0 - 10] 331 │ ├── sort 332 │ │ ├── columns: i:2 f:3 333 │ │ ├── ordering: +2,+3 334 │ │ ├── limit hint: 10.00 335 │ │ └── scan a 336 │ │ └── columns: i:2 f:3 337 │ └── 10 338 └── windows 339 └── rank [as=rank:6] 340 341 norm 342 SELECT 343 rank() OVER (PARTITION BY i ORDER BY f), 344 dense_rank() OVER (PARTITION BY i ORDER BY f) 345 FROM a LIMIT 10 346 ---- 347 project 348 ├── columns: rank:6 dense_rank:7 349 ├── cardinality: [0 - 10] 350 └── window partition=(2) ordering=+3 opt(2) 351 ├── columns: i:2 f:3 rank:6 dense_rank:7 352 ├── cardinality: [0 - 10] 353 ├── limit 354 │ ├── columns: i:2 f:3 355 │ ├── internal-ordering: +2,+3 356 │ ├── cardinality: [0 - 10] 357 │ ├── sort 358 │ │ ├── columns: i:2 f:3 359 │ │ ├── ordering: +2,+3 360 │ │ ├── limit hint: 10.00 361 │ │ └── scan a 362 │ │ └── columns: i:2 f:3 363 │ └── 10 364 └── windows 365 ├── rank [as=rank:6] 366 └── dense-rank [as=dense_rank:7] 367 368 # Can't push the limit down, because the window function used is not 369 # "prefix-safe". 370 norm expect-not=PushLimitIntoWindow 371 SELECT avg(k) OVER () FROM a LIMIT 10 372 ---- 373 project 374 ├── columns: avg:6 375 ├── cardinality: [0 - 10] 376 └── limit 377 ├── columns: k:1!null avg:6 378 ├── cardinality: [0 - 10] 379 ├── key: (1) 380 ├── window partition=() 381 │ ├── columns: k:1!null avg:6 382 │ ├── key: (1) 383 │ ├── limit hint: 10.00 384 │ ├── scan a 385 │ │ ├── columns: k:1!null 386 │ │ └── key: (1) 387 │ └── windows 388 │ └── avg [as=avg:6, outer=(1)] 389 │ └── k:1 390 └── 10 391 392 # Can't push the limit down, because the limit operator's ordering does not 393 # agree with the window function's ordering. 394 norm expect-not=PushLimitIntoWindow 395 SELECT rank() OVER (ORDER BY i) FROM a ORDER BY f LIMIT 10 396 ---- 397 project 398 ├── columns: rank:6 [hidden: f:3] 399 ├── cardinality: [0 - 10] 400 ├── ordering: +3 401 └── limit 402 ├── columns: i:2 f:3 rank:6 403 ├── internal-ordering: +3 404 ├── cardinality: [0 - 10] 405 ├── ordering: +3 406 ├── sort 407 │ ├── columns: i:2 f:3 rank:6 408 │ ├── ordering: +3 409 │ ├── limit hint: 10.00 410 │ └── window partition=() ordering=+2 411 │ ├── columns: i:2 f:3 rank:6 412 │ ├── scan a 413 │ │ └── columns: i:2 f:3 414 │ └── windows 415 │ └── rank [as=rank:6] 416 └── 10 417 418 # The limit should become stronger as it gets pushed down (going from +f to 419 # +f,+i), because the new limit needs to match the window function's ordering, 420 # rather than its own (weaker) ordering. 421 norm 422 SELECT rank() OVER (ORDER BY f, i) FROM a ORDER BY f LIMIT 10 423 ---- 424 sort 425 ├── columns: rank:6 [hidden: f:3] 426 ├── cardinality: [0 - 10] 427 ├── ordering: +3 428 └── project 429 ├── columns: f:3 rank:6 430 ├── cardinality: [0 - 10] 431 └── window partition=() ordering=+3,+2 432 ├── columns: i:2 f:3 rank:6 433 ├── cardinality: [0 - 10] 434 ├── limit 435 │ ├── columns: i:2 f:3 436 │ ├── internal-ordering: +3,+2 437 │ ├── cardinality: [0 - 10] 438 │ ├── sort 439 │ │ ├── columns: i:2 f:3 440 │ │ ├── ordering: +3,+2 441 │ │ ├── limit hint: 10.00 442 │ │ └── scan a 443 │ │ └── columns: i:2 f:3 444 │ └── 10 445 └── windows 446 └── rank [as=rank:6] 447 448 # Here we would only be able to push below the rank(), and not the avg(k). This 449 # is not profitable because we still have to do the partitioning and ordering 450 # for the one we were unable to push the limit below, which is the expensive 451 # part. 452 norm 453 SELECT 454 rank() OVER (PARTITION BY i ORDER BY f), 455 avg(k) OVER (PARTITION BY i ORDER BY f) 456 FROM 457 a 458 LIMIT 459 10 460 ---- 461 project 462 ├── columns: rank:6 avg:7 463 ├── cardinality: [0 - 10] 464 └── limit 465 ├── columns: k:1!null i:2 f:3 rank:6 avg:7 466 ├── cardinality: [0 - 10] 467 ├── key: (1) 468 ├── fd: (1)-->(2,3) 469 ├── window partition=(2) ordering=+3 opt(2) 470 │ ├── columns: k:1!null i:2 f:3 rank:6 avg:7 471 │ ├── key: (1) 472 │ ├── fd: (1)-->(2,3) 473 │ ├── limit hint: 10.00 474 │ ├── scan a 475 │ │ ├── columns: k:1!null i:2 f:3 476 │ │ ├── key: (1) 477 │ │ └── fd: (1)-->(2,3) 478 │ └── windows 479 │ ├── rank [as=rank:6] 480 │ └── avg [as=avg:7, outer=(1)] 481 │ └── k:1 482 └── 10 483 484 exec-ddl 485 CREATE TABLE wxyz (w INT PRIMARY KEY, x INT, y INT, z INT) 486 ---- 487 488 norm expect-not=PushLimitIntoWindow 489 SELECT *, rank() OVER (PARTITION BY z ORDER BY y) FROM wxyz ORDER BY y LIMIT 2 490 ---- 491 limit 492 ├── columns: w:1!null x:2 y:3 z:4 rank:5 493 ├── internal-ordering: +3 494 ├── cardinality: [0 - 2] 495 ├── key: (1) 496 ├── fd: (1)-->(2-4) 497 ├── ordering: +3 498 ├── sort 499 │ ├── columns: w:1!null x:2 y:3 z:4 rank:5 500 │ ├── key: (1) 501 │ ├── fd: (1)-->(2-4) 502 │ ├── ordering: +3 503 │ ├── limit hint: 2.00 504 │ └── window partition=(4) ordering=+3 opt(4) 505 │ ├── columns: w:1!null x:2 y:3 z:4 rank:5 506 │ ├── key: (1) 507 │ ├── fd: (1)-->(2-4) 508 │ ├── scan wxyz 509 │ │ ├── columns: w:1!null x:2 y:3 z:4 510 │ │ ├── key: (1) 511 │ │ └── fd: (1)-->(2-4) 512 │ └── windows 513 │ └── rank [as=rank:5] 514 └── 2 515 516 norm expect=PushLimitIntoWindow 517 SELECT *, rank() OVER (PARTITION BY w ORDER BY y) FROM wxyz ORDER BY y LIMIT 2 518 ---- 519 sort 520 ├── columns: w:1!null x:2 y:3 z:4 rank:5 521 ├── cardinality: [0 - 2] 522 ├── key: (1) 523 ├── fd: (1)-->(2-4) 524 ├── ordering: +3 525 └── window partition=(1) 526 ├── columns: w:1!null x:2 y:3 z:4 rank:5 527 ├── cardinality: [0 - 2] 528 ├── key: (1) 529 ├── fd: (1)-->(2-4) 530 ├── limit 531 │ ├── columns: w:1!null x:2 y:3 z:4 532 │ ├── internal-ordering: +3,+1 533 │ ├── cardinality: [0 - 2] 534 │ ├── key: (1) 535 │ ├── fd: (1)-->(2-4) 536 │ ├── sort 537 │ │ ├── columns: w:1!null x:2 y:3 z:4 538 │ │ ├── key: (1) 539 │ │ ├── fd: (1)-->(2-4) 540 │ │ ├── ordering: +3,+1 541 │ │ ├── limit hint: 2.00 542 │ │ └── scan wxyz 543 │ │ ├── columns: w:1!null x:2 y:3 z:4 544 │ │ ├── key: (1) 545 │ │ └── fd: (1)-->(2-4) 546 │ └── 2 547 └── windows 548 └── rank [as=rank:5] 549 550 norm expect=PushLimitIntoWindow 551 SELECT *, rank() OVER (PARTITION BY w ORDER BY y) FROM wxyz ORDER BY w, y LIMIT 2 552 ---- 553 sort 554 ├── columns: w:1!null x:2 y:3 z:4 rank:5 555 ├── cardinality: [0 - 2] 556 ├── key: (1) 557 ├── fd: (1)-->(2-4) 558 ├── ordering: +1 559 └── window partition=(1) 560 ├── columns: w:1!null x:2 y:3 z:4 rank:5 561 ├── cardinality: [0 - 2] 562 ├── key: (1) 563 ├── fd: (1)-->(2-4) 564 ├── limit 565 │ ├── columns: w:1!null x:2 y:3 z:4 566 │ ├── internal-ordering: +1 567 │ ├── cardinality: [0 - 2] 568 │ ├── key: (1) 569 │ ├── fd: (1)-->(2-4) 570 │ ├── scan wxyz 571 │ │ ├── columns: w:1!null x:2 y:3 z:4 572 │ │ ├── key: (1) 573 │ │ ├── fd: (1)-->(2-4) 574 │ │ ├── ordering: +1 575 │ │ └── limit hint: 2.00 576 │ └── 2 577 └── windows 578 └── rank [as=rank:5] 579 580 norm expect=PushLimitIntoWindow 581 SELECT *, rank() OVER (PARTITION BY w ORDER BY y) FROM wxyz ORDER BY w LIMIT 2 582 ---- 583 sort 584 ├── columns: w:1!null x:2 y:3 z:4 rank:5 585 ├── cardinality: [0 - 2] 586 ├── key: (1) 587 ├── fd: (1)-->(2-4) 588 ├── ordering: +1 589 └── window partition=(1) 590 ├── columns: w:1!null x:2 y:3 z:4 rank:5 591 ├── cardinality: [0 - 2] 592 ├── key: (1) 593 ├── fd: (1)-->(2-4) 594 ├── limit 595 │ ├── columns: w:1!null x:2 y:3 z:4 596 │ ├── internal-ordering: +1 597 │ ├── cardinality: [0 - 2] 598 │ ├── key: (1) 599 │ ├── fd: (1)-->(2-4) 600 │ ├── scan wxyz 601 │ │ ├── columns: w:1!null x:2 y:3 z:4 602 │ │ ├── key: (1) 603 │ │ ├── fd: (1)-->(2-4) 604 │ │ ├── ordering: +1 605 │ │ └── limit hint: 2.00 606 │ └── 2 607 └── windows 608 └── rank [as=rank:5] 609 610 norm expect=PushLimitIntoWindow 611 SELECT *, rank() OVER (PARTITION BY w ORDER BY y) FROM wxyz ORDER BY y, w LIMIT 2 612 ---- 613 sort 614 ├── columns: w:1!null x:2 y:3 z:4 rank:5 615 ├── cardinality: [0 - 2] 616 ├── key: (1) 617 ├── fd: (1)-->(2-4) 618 ├── ordering: +3,+1 619 └── window partition=(1) 620 ├── columns: w:1!null x:2 y:3 z:4 rank:5 621 ├── cardinality: [0 - 2] 622 ├── key: (1) 623 ├── fd: (1)-->(2-4) 624 ├── limit 625 │ ├── columns: w:1!null x:2 y:3 z:4 626 │ ├── internal-ordering: +3,+1 627 │ ├── cardinality: [0 - 2] 628 │ ├── key: (1) 629 │ ├── fd: (1)-->(2-4) 630 │ ├── sort 631 │ │ ├── columns: w:1!null x:2 y:3 z:4 632 │ │ ├── key: (1) 633 │ │ ├── fd: (1)-->(2-4) 634 │ │ ├── ordering: +3,+1 635 │ │ ├── limit hint: 2.00 636 │ │ └── scan wxyz 637 │ │ ├── columns: w:1!null x:2 y:3 z:4 638 │ │ ├── key: (1) 639 │ │ └── fd: (1)-->(2-4) 640 │ └── 2 641 └── windows 642 └── rank [as=rank:5] 643 644 norm expect=PushLimitIntoWindow 645 SELECT *, rank() OVER (PARTITION BY w, z ORDER BY y) FROM wxyz ORDER BY w, z LIMIT 2 646 ---- 647 sort 648 ├── columns: w:1!null x:2 y:3 z:4 rank:5 649 ├── cardinality: [0 - 2] 650 ├── key: (1) 651 ├── fd: (1)-->(2-4) 652 ├── ordering: +1 653 └── window partition=(1) 654 ├── columns: w:1!null x:2 y:3 z:4 rank:5 655 ├── cardinality: [0 - 2] 656 ├── key: (1) 657 ├── fd: (1)-->(2-4) 658 ├── limit 659 │ ├── columns: w:1!null x:2 y:3 z:4 660 │ ├── internal-ordering: +1 661 │ ├── cardinality: [0 - 2] 662 │ ├── key: (1) 663 │ ├── fd: (1)-->(2-4) 664 │ ├── scan wxyz 665 │ │ ├── columns: w:1!null x:2 y:3 z:4 666 │ │ ├── key: (1) 667 │ │ ├── fd: (1)-->(2-4) 668 │ │ ├── ordering: +1 669 │ │ └── limit hint: 2.00 670 │ └── 2 671 └── windows 672 └── rank [as=rank:5] 673 674 norm 675 SELECT *, rank() OVER (PARTITION BY x, z ORDER BY y) FROM wxyz ORDER BY z, x LIMIT 2 676 ---- 677 sort 678 ├── columns: w:1!null x:2 y:3 z:4 rank:5 679 ├── cardinality: [0 - 2] 680 ├── key: (1) 681 ├── fd: (1)-->(2-4) 682 ├── ordering: +4,+2 683 └── window partition=(2,4) ordering=+3 opt(2,4) 684 ├── columns: w:1!null x:2 y:3 z:4 rank:5 685 ├── cardinality: [0 - 2] 686 ├── key: (1) 687 ├── fd: (1)-->(2-4) 688 ├── limit 689 │ ├── columns: w:1!null x:2 y:3 z:4 690 │ ├── internal-ordering: +4,+2,+3 691 │ ├── cardinality: [0 - 2] 692 │ ├── key: (1) 693 │ ├── fd: (1)-->(2-4) 694 │ ├── sort 695 │ │ ├── columns: w:1!null x:2 y:3 z:4 696 │ │ ├── key: (1) 697 │ │ ├── fd: (1)-->(2-4) 698 │ │ ├── ordering: +4,+2,+3 699 │ │ ├── limit hint: 2.00 700 │ │ └── scan wxyz 701 │ │ ├── columns: w:1!null x:2 y:3 z:4 702 │ │ ├── key: (1) 703 │ │ └── fd: (1)-->(2-4) 704 │ └── 2 705 └── windows 706 └── rank [as=rank:5] 707 708 norm expect=PushLimitIntoWindow 709 SELECT *, rank() OVER (PARTITION BY z ORDER BY y) FROM wxyz ORDER BY z, y LIMIT 2 710 ---- 711 sort 712 ├── columns: w:1!null x:2 y:3 z:4 rank:5 713 ├── cardinality: [0 - 2] 714 ├── key: (1) 715 ├── fd: (1)-->(2-4) 716 ├── ordering: +4,+3 717 └── window partition=(4) ordering=+3 opt(4) 718 ├── columns: w:1!null x:2 y:3 z:4 rank:5 719 ├── cardinality: [0 - 2] 720 ├── key: (1) 721 ├── fd: (1)-->(2-4) 722 ├── limit 723 │ ├── columns: w:1!null x:2 y:3 z:4 724 │ ├── internal-ordering: +4,+3 725 │ ├── cardinality: [0 - 2] 726 │ ├── key: (1) 727 │ ├── fd: (1)-->(2-4) 728 │ ├── sort 729 │ │ ├── columns: w:1!null x:2 y:3 z:4 730 │ │ ├── key: (1) 731 │ │ ├── fd: (1)-->(2-4) 732 │ │ ├── ordering: +4,+3 733 │ │ ├── limit hint: 2.00 734 │ │ └── scan wxyz 735 │ │ ├── columns: w:1!null x:2 y:3 z:4 736 │ │ ├── key: (1) 737 │ │ └── fd: (1)-->(2-4) 738 │ └── 2 739 └── windows 740 └── rank [as=rank:5] 741 742 norm expect-not=PushLimitIntoWindow 743 SELECT *, rank() OVER (PARTITION BY z ORDER BY y) FROM wxyz ORDER BY y LIMIT 2 744 ---- 745 limit 746 ├── columns: w:1!null x:2 y:3 z:4 rank:5 747 ├── internal-ordering: +3 748 ├── cardinality: [0 - 2] 749 ├── key: (1) 750 ├── fd: (1)-->(2-4) 751 ├── ordering: +3 752 ├── sort 753 │ ├── columns: w:1!null x:2 y:3 z:4 rank:5 754 │ ├── key: (1) 755 │ ├── fd: (1)-->(2-4) 756 │ ├── ordering: +3 757 │ ├── limit hint: 2.00 758 │ └── window partition=(4) ordering=+3 opt(4) 759 │ ├── columns: w:1!null x:2 y:3 z:4 rank:5 760 │ ├── key: (1) 761 │ ├── fd: (1)-->(2-4) 762 │ ├── scan wxyz 763 │ │ ├── columns: w:1!null x:2 y:3 z:4 764 │ │ ├── key: (1) 765 │ │ └── fd: (1)-->(2-4) 766 │ └── windows 767 │ └── rank [as=rank:5] 768 └── 2 769 770 norm expect=PushLimitIntoWindow 771 SELECT *, rank() OVER (PARTITION BY w, z ORDER BY y) FROM wxyz ORDER BY w, z, y LIMIT 2 772 ---- 773 sort 774 ├── columns: w:1!null x:2 y:3 z:4 rank:5 775 ├── cardinality: [0 - 2] 776 ├── key: (1) 777 ├── fd: (1)-->(2-4) 778 ├── ordering: +1 779 └── window partition=(1) 780 ├── columns: w:1!null x:2 y:3 z:4 rank:5 781 ├── cardinality: [0 - 2] 782 ├── key: (1) 783 ├── fd: (1)-->(2-4) 784 ├── limit 785 │ ├── columns: w:1!null x:2 y:3 z:4 786 │ ├── internal-ordering: +1 787 │ ├── cardinality: [0 - 2] 788 │ ├── key: (1) 789 │ ├── fd: (1)-->(2-4) 790 │ ├── scan wxyz 791 │ │ ├── columns: w:1!null x:2 y:3 z:4 792 │ │ ├── key: (1) 793 │ │ ├── fd: (1)-->(2-4) 794 │ │ ├── ordering: +1 795 │ │ └── limit hint: 2.00 796 │ └── 2 797 └── windows 798 └── rank [as=rank:5] 799 800 norm 801 SELECT *, rank() OVER (PARTITION BY w, z ORDER BY y) FROM wxyz ORDER BY z, w, y LIMIT 2 802 ---- 803 sort 804 ├── columns: w:1!null x:2 y:3 z:4 rank:5 805 ├── cardinality: [0 - 2] 806 ├── key: (1) 807 ├── fd: (1)-->(2-4) 808 ├── ordering: +4,+1 809 └── window partition=(1) 810 ├── columns: w:1!null x:2 y:3 z:4 rank:5 811 ├── cardinality: [0 - 2] 812 ├── key: (1) 813 ├── fd: (1)-->(2-4) 814 ├── limit 815 │ ├── columns: w:1!null x:2 y:3 z:4 816 │ ├── internal-ordering: +4,+1 817 │ ├── cardinality: [0 - 2] 818 │ ├── key: (1) 819 │ ├── fd: (1)-->(2-4) 820 │ ├── sort 821 │ │ ├── columns: w:1!null x:2 y:3 z:4 822 │ │ ├── key: (1) 823 │ │ ├── fd: (1)-->(2-4) 824 │ │ ├── ordering: +4,+1 825 │ │ ├── limit hint: 2.00 826 │ │ └── scan wxyz 827 │ │ ├── columns: w:1!null x:2 y:3 z:4 828 │ │ ├── key: (1) 829 │ │ └── fd: (1)-->(2-4) 830 │ └── 2 831 └── windows 832 └── rank [as=rank:5] 833 834 norm 835 SELECT *, rank() OVER (PARTITION BY w ORDER BY y) FROM wxyz ORDER BY z LIMIT 2 836 ---- 837 sort 838 ├── columns: w:1!null x:2 y:3 z:4 rank:5 839 ├── cardinality: [0 - 2] 840 ├── key: (1) 841 ├── fd: (1)-->(2-4) 842 ├── ordering: +4 843 └── window partition=(1) 844 ├── columns: w:1!null x:2 y:3 z:4 rank:5 845 ├── cardinality: [0 - 2] 846 ├── key: (1) 847 ├── fd: (1)-->(2-4) 848 ├── limit 849 │ ├── columns: w:1!null x:2 y:3 z:4 850 │ ├── internal-ordering: +4,+1 851 │ ├── cardinality: [0 - 2] 852 │ ├── key: (1) 853 │ ├── fd: (1)-->(2-4) 854 │ ├── sort 855 │ │ ├── columns: w:1!null x:2 y:3 z:4 856 │ │ ├── key: (1) 857 │ │ ├── fd: (1)-->(2-4) 858 │ │ ├── ordering: +4,+1 859 │ │ ├── limit hint: 2.00 860 │ │ └── scan wxyz 861 │ │ ├── columns: w:1!null x:2 y:3 z:4 862 │ │ ├── key: (1) 863 │ │ └── fd: (1)-->(2-4) 864 │ └── 2 865 └── windows 866 └── rank [as=rank:5]