Repository navigation
Commit 2424dae
authored
fix: preserve computed names in derived SQL projections (apache#25521)
## Which issue does this PR close?
Related to apache#21066.
## Rationale for this change
An outer query can reference a computed result by its logical output
name while the generated inner query leaves that result unnamed. The
database cannot resolve the outer reference. For example, BigQuery
output refers to `sum_40j1_46j1_id_41` without defining it in the
derived table.
## What changes are included in this PR?
Derived projections now emit matching output names whether or not the
table has an alias. The unparser waits until the final table alias is
known, so a table-column alias list still supplies the names. Explicit
expression aliases and plain column references keep their existing SQL.
## What is the testing strategy for this PR?
The regression exercises PostgreSQL and BigQuery output through the
unparser. Existing column-alias and UNNEST snapshots remain unchanged.
Roundtrip tests still compare the whole logical plan after removing only
unqualified, metadata-free column self-aliases.
### Testing Done
SQLite 3.51.0 executes the emitted BigQuery SQL without rewriting it.
This uses local SQLite, not the Google BigQuery service. The pre-fix
query below was captured from
apache@4abdeef;
the fixed query comes directly from the regression run.
| # | Scenario | Command | Result |
|---|---|---|---|
| 1 | Recorded pre-fix SQL | `python3 - "$log"` below | `no such column:
sum_40j1_46j1_id_41` |
| 2 | Freshly emitted fixed SQL | `python3 - "$log"` below | `[(3,)]` |
Run from this checkout:
```bash
set -euo pipefail
log=$(mktemp)
CARGO_BUILD_JOBS=2 cargo test --locked --profile ci \
-p datafusion -p datafusion-sql --features datafusion-sql/recursive_protection \
--test sql_integration unparse_preserves_derived_aggregate_output_name \
-- --nocapture > "$log" 2>&1
cat "$log"
python3 - "$log" <<'PY'
import sqlite3
import sys
from pathlib import Path
before = (
"SELECT `sum_40j1_46j1_id_41` FROM "
"(SELECT sum(`j1`.`j1_id`) AS `visible`, sum(`j1`.`j1_id`) FROM `j1`)"
)
emitted: list[str] = [
line.removeprefix("BIGQUERY_SQL=")
for line in Path(sys.argv[1]).read_text().splitlines()
if line.startswith("BIGQUERY_SQL=")
]
assert len(emitted) == 1, emitted
print(f"SQLite version: {sqlite3.sqlite_version}")
with sqlite3.connect(database=":memory:") as connection:
connection.executescript(
"CREATE TABLE j1 (j1_id INTEGER); INSERT INTO j1 VALUES (1), (2);"
)
print(f"before SQL: {before}")
try:
connection.execute(before).fetchall()
except sqlite3.OperationalError as error:
assert str(error) == "no such column: sum_40j1_46j1_id_41", error
print(f"before error: {error}")
else:
raise AssertionError("The recorded pre-fix query unexpectedly succeeded.")
print(f"after SQL: {emitted[0]}")
rows = connection.execute(emitted[0]).fetchall()
print(f"after rows: {rows}")
assert rows == [(3,)], rows
PY
```
<details>
<summary>Raw logs</summary>
```text
SQLite version: 3.51.0
before SQL: SELECT `sum_40j1_46j1_id_41` FROM (SELECT sum(`j1`.`j1_id`) AS `visible`, sum(`j1`.`j1_id`) FROM `j1`)
before error: no such column: sum_40j1_46j1_id_41
after SQL: SELECT `sum_40j1_46j1_id_41` FROM (SELECT sum(`j1`.`j1_id`) AS `visible`, sum(`j1`.`j1_id`) AS `sum_40j1_46j1_id_41` FROM `j1`)
after rows: [(3,)]
```
</details>
## Are there any user-facing changes?
Generated derived projections expose the computed column names used by
outer queries, including when the dialect does not require a table
alias. SQL input validation and public APIs are unchanged.
---------
Signed-off-by: 1fanwang <[email protected]>1 parent 4fe342d commit 2424dae
3 files changed
Lines changed: 115 additions & 17 deletions
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
677 | 677 | | |
678 | 678 | | |
679 | 679 | | |
| 680 | + | |
680 | 681 | | |
681 | 682 | | |
682 | 683 | | |
| |||
692 | 693 | | |
693 | 694 | | |
694 | 695 | | |
| 696 | + | |
| 697 | + | |
| 698 | + | |
| 699 | + | |
695 | 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 | + | |
696 | 733 | | |
697 | 734 | | |
698 | 735 | | |
699 | 736 | | |
700 | 737 | | |
701 | | - | |
702 | | - | |
703 | | - | |
704 | | - | |
705 | | - | |
706 | | - | |
| 738 | + | |
707 | 739 | | |
708 | 740 | | |
709 | 741 | | |
| |||
713 | 745 | | |
714 | 746 | | |
715 | 747 | | |
| 748 | + | |
716 | 749 | | |
717 | 750 | | |
718 | 751 | | |
| |||
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
593 | 593 | | |
594 | 594 | | |
595 | 595 | | |
| 596 | + | |
| 597 | + | |
596 | 598 | | |
| 599 | + | |
| 600 | + | |
| 601 | + | |
| 602 | + | |
| 603 | + | |
| 604 | + | |
| 605 | + | |
| 606 | + | |
| 607 | + | |
597 | 608 | | |
598 | 609 | | |
599 | 610 | | |
| |||
2693 | 2704 | | |
2694 | 2705 | | |
2695 | 2706 | | |
2696 | | - | |
2697 | | - | |
2698 | | - | |
2699 | | - | |
2700 | | - | |
2701 | | - | |
2702 | | - | |
2703 | | - | |
2704 | | - | |
2705 | 2707 | | |
2706 | 2708 | | |
2707 | | - | |
| 2709 | + | |
2708 | 2710 | | |
2709 | 2711 | | |
2710 | 2712 | | |
| |||
2715 | 2717 | | |
2716 | 2718 | | |
2717 | 2719 | | |
| 2720 | + | |
| 2721 | + | |
| 2722 | + | |
| 2723 | + | |
| 2724 | + | |
| 2725 | + | |
| 2726 | + | |
| 2727 | + | |
2718 | 2728 | | |
2719 | 2729 | | |
2720 | 2730 | | |
| |||
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
17 | 17 | | |
18 | 18 | | |
19 | 19 | | |
| 20 | + | |
20 | 21 | | |
21 | 22 | | |
22 | 23 | | |
| |||
111 | 112 | | |
112 | 113 | | |
113 | 114 | | |
| 115 | + | |
| 116 | + | |
| 117 | + | |
| 118 | + | |
| 119 | + | |
| 120 | + | |
| 121 | + | |
| 122 | + | |
| 123 | + | |
| 124 | + | |
| 125 | + | |
| 126 | + | |
| 127 | + | |
| 128 | + | |
| 129 | + | |
| 130 | + | |
| 131 | + | |
| 132 | + | |
| 133 | + | |
114 | 134 | | |
115 | 135 | | |
116 | 136 | | |
| |||
254 | 274 | | |
255 | 275 | | |
256 | 276 | | |
257 | | - | |
| 277 | + | |
| 278 | + | |
| 279 | + | |
| 280 | + | |
| 281 | + | |
258 | 282 | | |
259 | 283 | | |
260 | 284 | | |
| |||
392 | 416 | | |
393 | 417 | | |
394 | 418 | | |
| 419 | + | |
| 420 | + | |
| 421 | + | |
| 422 | + | |
| 423 | + | |
| 424 | + | |
| 425 | + | |
| 426 | + | |
| 427 | + | |
| 428 | + | |
| 429 | + | |
| 430 | + | |
| 431 | + | |
| 432 | + | |
| 433 | + | |
| 434 | + | |
| 435 | + | |
| 436 | + | |
| 437 | + | |
| 438 | + | |
| 439 | + | |
| 440 | + | |
| 441 | + | |
| 442 | + | |
| 443 | + | |
| 444 | + | |
| 445 | + | |
| 446 | + | |
| 447 | + | |
| 448 | + | |
| 449 | + | |
395 | 450 | | |
396 | 451 | | |
397 | 452 | | |
| |||
0 commit comments