Repository navigation
Commit abec311
authored
refactor(unparser): centralize aggregate-scope rendering in the SQL unparser (apache#23789)
## Which issue does this PR close?
- Closes apache#23668.
## Rationale for this change
- When printing a GROUP BY query, the unparser sometimes wraps the
aggregate's input in an inner subquery (`... FROM (SELECT ...)`).
- Table aliases like `cs` only exist inside that subquery. Any clause
outside it (SELECT, GROUP BY, HAVING, QUALIFY, ORDER BY) must use the
subquery's output columns, not the aliases, so the unparser drops the
alias.
- Each clause does that dropping on its own, so it is easy to miss one.
- ORDER BY on an aggregate that is not in the SELECT list was missed: it
printed `ORDER BY round(sum("cs"."total_revenue"), 2)`, while the SELECT
list in the same query correctly printed `sum("total_revenue")`.
- DataFusion reads that SQL back fine, but stricter databases reject it
because `cs` is out of scope there.
## What changes are included in this PR?
I moved the rule into one helper, `UnparserAggScope`, that checks once
per aggregate whether the input is an inner subquery and then prepares
expressions for each clause. SELECT, GROUP BY, HAVING, QUALIFY, and both
ORDER BY paths now go through it, which fixes the ORDER BY case. The
window-over-aggregate path is left as-is with a comment: it is only
reachable from hand-built plans, since a window in SQL always sits
inside a SELECT that already drops the alias. Only ORDER BY output
changes; every already-correct case stays the same.
## Are these changes tested?
Yes. New tests in `datafusion/core/tests/sql/unparser.rs` cover the
inner-subquery shape for a window sorting by an aggregate, ORDER BY on
an unselected aggregate (the fixed case), and a top-level ORDER BY (the
second sort path). Existing unparser tests and the TPC-H/Clickbench
roundtrips still pass.
## Are there any user-facing changes?
ORDER BY over an aggregate is now printed without an out-of-scope table
qualifier, so the generated SQL is valid for stricter databases. No API
changes.1 parent 9e3c71f commit abec311
2 files changed
Lines changed: 231 additions & 55 deletions
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
468 | 468 | | |
469 | 469 | | |
470 | 470 | | |
| 471 | + | |
| 472 | + | |
| 473 | + | |
| 474 | + | |
| 475 | + | |
| 476 | + | |
| 477 | + | |
| 478 | + | |
| 479 | + | |
| 480 | + | |
| 481 | + | |
| 482 | + | |
| 483 | + | |
| 484 | + | |
| 485 | + | |
| 486 | + | |
| 487 | + | |
| 488 | + | |
| 489 | + | |
| 490 | + | |
| 491 | + | |
| 492 | + | |
| 493 | + | |
| 494 | + | |
| 495 | + | |
| 496 | + | |
| 497 | + | |
| 498 | + | |
| 499 | + | |
| 500 | + | |
| 501 | + | |
| 502 | + | |
| 503 | + | |
| 504 | + | |
| 505 | + | |
| 506 | + | |
| 507 | + | |
| 508 | + | |
| 509 | + | |
| 510 | + | |
| 511 | + | |
| 512 | + | |
| 513 | + | |
| 514 | + | |
| 515 | + | |
| 516 | + | |
| 517 | + | |
| 518 | + | |
| 519 | + | |
| 520 | + | |
| 521 | + | |
| 522 | + | |
| 523 | + | |
471 | 524 | | |
472 | 525 | | |
473 | 526 | | |
| |||
612 | 665 | | |
613 | 666 | | |
614 | 667 | | |
| 668 | + | |
| 669 | + | |
| 670 | + | |
| 671 | + | |
| 672 | + | |
| 673 | + | |
| 674 | + | |
| 675 | + | |
| 676 | + | |
| 677 | + | |
| 678 | + | |
| 679 | + | |
| 680 | + | |
| 681 | + | |
| 682 | + | |
| 683 | + | |
| 684 | + | |
| 685 | + | |
| 686 | + | |
| 687 | + | |
| 688 | + | |
| 689 | + | |
| 690 | + | |
| 691 | + | |
| 692 | + | |
| 693 | + | |
| 694 | + | |
| 695 | + | |
| 696 | + | |
| 697 | + | |
| 698 | + | |
| 699 | + | |
| 700 | + | |
| 701 | + | |
| 702 | + | |
| 703 | + | |
| 704 | + | |
| 705 | + | |
| 706 | + | |
| 707 | + | |
| 708 | + | |
| 709 | + | |
| 710 | + | |
| 711 | + | |
| 712 | + | |
| 713 | + | |
| 714 | + | |
| 715 | + | |
| 716 | + | |
| 717 | + | |
| 718 | + | |
| 719 | + | |
| 720 | + | |
| 721 | + | |
| 722 | + | |
| 723 | + | |
| 724 | + | |
| 725 | + | |
| 726 | + | |
| 727 | + | |
| 728 | + | |
| 729 | + | |
| 730 | + | |
| 731 | + | |
| 732 | + | |
| 733 | + | |
| 734 | + | |
| 735 | + | |
| 736 | + | |
| 737 | + | |
| 738 | + | |
| 739 | + | |
| 740 | + | |
| 741 | + | |
| 742 | + | |
| 743 | + | |
| 744 | + | |
| 745 | + | |
| 746 | + | |
| 747 | + | |
| 748 | + | |
| 749 | + | |
615 | 750 | | |
616 | 751 | | |
617 | 752 | | |
| |||
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
101 | 101 | | |
102 | 102 | | |
103 | 103 | | |
| 104 | + | |
| 105 | + | |
| 106 | + | |
| 107 | + | |
| 108 | + | |
| 109 | + | |
| 110 | + | |
| 111 | + | |
| 112 | + | |
| 113 | + | |
| 114 | + | |
| 115 | + | |
| 116 | + | |
| 117 | + | |
| 118 | + | |
| 119 | + | |
| 120 | + | |
| 121 | + | |
| 122 | + | |
| 123 | + | |
| 124 | + | |
| 125 | + | |
| 126 | + | |
| 127 | + | |
| 128 | + | |
| 129 | + | |
| 130 | + | |
| 131 | + | |
| 132 | + | |
| 133 | + | |
| 134 | + | |
| 135 | + | |
| 136 | + | |
| 137 | + | |
| 138 | + | |
| 139 | + | |
| 140 | + | |
| 141 | + | |
| 142 | + | |
| 143 | + | |
| 144 | + | |
| 145 | + | |
| 146 | + | |
| 147 | + | |
| 148 | + | |
| 149 | + | |
| 150 | + | |
| 151 | + | |
| 152 | + | |
| 153 | + | |
| 154 | + | |
| 155 | + | |
| 156 | + | |
| 157 | + | |
| 158 | + | |
| 159 | + | |
| 160 | + | |
| 161 | + | |
| 162 | + | |
| 163 | + | |
| 164 | + | |
| 165 | + | |
| 166 | + | |
104 | 167 | | |
105 | 168 | | |
106 | 169 | | |
| |||
312 | 375 | | |
313 | 376 | | |
314 | 377 | | |
315 | | - | |
316 | | - | |
| 378 | + | |
317 | 379 | | |
318 | 380 | | |
319 | 381 | | |
320 | | - | |
321 | | - | |
322 | | - | |
323 | | - | |
324 | | - | |
325 | | - | |
| 382 | + | |
| 383 | + | |
326 | 384 | | |
327 | 385 | | |
328 | 386 | | |
| |||
333 | 391 | | |
334 | 392 | | |
335 | 393 | | |
336 | | - | |
337 | | - | |
338 | | - | |
339 | | - | |
340 | | - | |
341 | | - | |
| 394 | + | |
342 | 395 | | |
343 | 396 | | |
344 | 397 | | |
| |||
379 | 432 | | |
380 | 433 | | |
381 | 434 | | |
382 | | - | |
383 | | - | |
384 | | - | |
385 | | - | |
386 | | - | |
387 | | - | |
388 | | - | |
389 | | - | |
390 | | - | |
391 | | - | |
392 | | - | |
393 | | - | |
394 | 435 | | |
395 | 436 | | |
396 | 437 | | |
| |||
429 | 470 | | |
430 | 471 | | |
431 | 472 | | |
| 473 | + | |
| 474 | + | |
| 475 | + | |
| 476 | + | |
| 477 | + | |
| 478 | + | |
| 479 | + | |
| 480 | + | |
| 481 | + | |
| 482 | + | |
| 483 | + | |
| 484 | + | |
| 485 | + | |
432 | 486 | | |
433 | 487 | | |
434 | 488 | | |
| |||
592 | 646 | | |
593 | 647 | | |
594 | 648 | | |
| 649 | + | |
| 650 | + | |
| 651 | + | |
595 | 652 | | |
596 | 653 | | |
597 | 654 | | |
| |||
977 | 1034 | | |
978 | 1035 | | |
979 | 1036 | | |
980 | | - | |
| 1037 | + | |
981 | 1038 | | |
982 | 1039 | | |
983 | 1040 | | |
| |||
1028 | 1085 | | |
1029 | 1086 | | |
1030 | 1087 | | |
1031 | | - | |
1032 | | - | |
1033 | | - | |
1034 | | - | |
1035 | | - | |
1036 | | - | |
| 1088 | + | |
| 1089 | + | |
1037 | 1090 | | |
1038 | 1091 | | |
1039 | 1092 | | |
1040 | 1093 | | |
1041 | | - | |
1042 | | - | |
1043 | | - | |
1044 | | - | |
1045 | | - | |
1046 | | - | |
1047 | | - | |
| 1094 | + | |
| 1095 | + | |
1048 | 1096 | | |
1049 | 1097 | | |
1050 | 1098 | | |
| |||
1130 | 1178 | | |
1131 | 1179 | | |
1132 | 1180 | | |
1133 | | - | |
| 1181 | + | |
| 1182 | + | |
| 1183 | + | |
| 1184 | + | |
| 1185 | + | |
1134 | 1186 | | |
1135 | 1187 | | |
1136 | 1188 | | |
| |||
1146 | 1198 | | |
1147 | 1199 | | |
1148 | 1200 | | |
1149 | | - | |
1150 | | - | |
| 1201 | + | |
1151 | 1202 | | |
1152 | 1203 | | |
1153 | 1204 | | |
| |||
1156 | 1207 | | |
1157 | 1208 | | |
1158 | 1209 | | |
1159 | | - | |
1160 | | - | |
1161 | | - | |
1162 | | - | |
1163 | | - | |
1164 | | - | |
| 1210 | + | |
1165 | 1211 | | |
1166 | 1212 | | |
1167 | 1213 | | |
| |||
1171 | 1217 | | |
1172 | 1218 | | |
1173 | 1219 | | |
1174 | | - | |
1175 | | - | |
1176 | | - | |
1177 | | - | |
1178 | | - | |
1179 | | - | |
| 1220 | + | |
1180 | 1221 | | |
1181 | 1222 | | |
1182 | 1223 | | |
| |||
0 commit comments