haochunchang opened a new pull request, #26022:
URL: https://github.com/apache/datafusion/pull/26022
## Which issue does this PR close?
- Closes #25903.
## Rationale for this change
An unaliased column in a SQL result is named with the planner's internal
lookup key, so headers include type wrappers, table qualifiers, and default
window frames:
| Query item | Current name | With this PR (option on) |
| --- | --- | --- |
| `t.a + 1` | `t.a + Int64(1)` | `a + 1` |
| `(a + b) * c` | `t.a + t.b * t.c` | `(a + b) * c` |
| `avg(c) FILTER (WHERE c > 5)` | `avg(t.c) FILTER (WHERE t.c > Int64(5))` |
`avg(c) FILTER (WHERE c > 5)` |
| `row_number() OVER (ORDER BY a)` | `row_number() ORDER BY [t.a ASC NULLS
LAST] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` | `row_number() OVER
(ORDER BY a)` |
The name is also the key that the planner uses to find columns, so it can't
simply be shortened (#2027, #5174, #10274). This PR implements the proposal in
#25903: an opt-in setting that renames only the output columns of a top-level
query, after planning, and leaves every name inside the plan unchanged.
## What changes are included in this PR?
- **New option** `datafusion.sql_parser.pretty_column_names`, default
`false`. When it's `false`, planning reads one config value and does nothing
else.
- **Labeling step** (`datafusion/sql/src/column_labels.rs`), called from the
`Statement::Query` branch of `sql_statement_to_plan`:
- Traces each output column, by field index, down to the expression that
produced it (through `Projection`, `Aggregate`, `Window`, `SubqueryAlias`,
`Filter`, `Sort`, `Limit`, `Distinct::All`, `Union`, and `ON` joins), and
renders it with `Expr::human_display`.
- Renders window functions as `name(args) OVER (...)` and leaves out
default parts: the default frame (same tie check as the planner), constant sort
keys, `ASC`, and the default `NULLS` ordering.
- Aliases the expressions of the top `Projection` in place. It adds a
projection only when the top node is something else, such as `Sort` or `Limit`.
- **A column keeps its current name** when:
- it has an explicit alias;
- it's a column reference typed in the outermost `SELECT`;
- it can't be traced (`USING`/`NATURAL` joins, `UNNEST`, `VALUES`,
recursive CTEs, subquery expressions, `DISTINCT ON`);
- its label collides with another output column's name, for example
`SELECT t1.a + 1, t2.a + 1` or `SELECT 1, '1'`.
- **`Expr::human_display` fixes** (in `SqlDisplay`): renders scalar
functions (`coalesce(NULL, 1)`), adds parentheses by operator precedence (`(a +
b) * c`, `a - (b - c)`), and prints `CUBE` instead of `ROLLUP` for
`GroupingSet::Cube`.
- **New `Sort::human_display` and `SortListDisplay`** render sort keys in
SQL syntax. `UdafHumanDisplayBuilder` now uses them, and `SqlDisplay` for
`FILTER` and `ORDER BY`.
- **Docs**: config reference, plus a section in
`output-field-name-semantic.md` with the rules that apply when the option is on.
### Not affected
`CREATE VIEW`, `CREATE TABLE AS`, `INSERT ... SELECT`, `COPY`, `DESCRIBE`,
and the DataFrame API keep today's names. References by the current name keep
working, for example `ORDER BY "t.a + Int64(1)"`, because names inside the plan
don't change.
### Decisions where the proposal was silent
- **Collision handling:** labels are dropped one column at a time, repeating
until no labeled column collides, so turning the option on never makes a
working query fail.
- **`count(*) OVER (...)`:** the planner plans it as `count(1)` and aliases
it back to `count(*) ...`. The label keeps `count(*)`.
- **Window frame tie check** for a window over an aggregate uses the
aggregate's input schema, because that's the schema the planner used to choose
the default frame.
## What is the testing strategy for this PR?
- `datafusion/sqllogictest/test_files/pretty_column_names.slt`: one
`EXPLAIN` case for each row in the proposal's tables. Labels show up as `AS
<label>` in the top projection. It also covers `ORDER BY` by current name and
by position, `HAVING`, `DISTINCT`, `DISTINCT ON`, `UNNEST`, `USING` joins, set
operations, recursive CTEs, subqueries, windows ordered by a unique key,
collisions, and that CTAS, views, and `DESCRIBE` keep stored names.
- `datafusion/core/tests/sql/select.rs`: `pretty_column_names_in_results`
checks the printed headers with the option off and on.
- `datafusion/expr` unit tests and doctests for precedence, scalar
functions, `CUBE`, and `Sort::human_display`.
### Benchmarks
`datafusion/core/benches/sql_planner.rs`, option off vs on, on the same
build, run back to back on an M-series laptop. The `sql_planner` bench needs
clickbench data, so the clickbench cases were left out locally. Changes under
about 5% were noise on this machine: tpch and tpcds showed 4–5% "improvements".
| Benchmark | Off | On | Change |
| --- | --- | --- | --- |
| `logical_select_all_from_1000` | 11.11 ms | 11.46 ms | no change detected |
| `physical_select_all_from_1000` | 28.75 ms | 25.38 ms | noise |
| `physical_select_aggregates_from_200` | 7.40 ms | 9.34 ms | +26% |
| `logical_wide_aggregate_100_exprs` | 1.48 ms | 1.65 ms | +12% |
| `physical_plan_tpch_all` | 28.71 ms | 27.38 ms | noise |
| `physical_plan_tpcds_all` | 498.31 ms | 479.64 ms | noise |
The remaining cost on wide aggregates comes from renaming itself. With the
option off, `optimize_projections` removes the projection above the `Aggregate`
because it only passes columns through. With aliases it has to stay, through
the optimizer and as a `ProjectionExec` that renames columns without copying
them. Labeling adds about 1.8 µs per column. A first version added a separate
projection on top and measured +38% on `physical_select_aggregates_from_200`.
Aliasing in place removed the extra optimizer work.
## Are there any user-facing changes?
- New config option `datafusion.sql_parser.pretty_column_names`, default
`false`. With the default, result column names don't change.
- **Physical `EXPLAIN` text changes even when the option is off.**
`AggregateExec` aggregate names come from `human_display`, so `FILTER` and
`ORDER BY` clauses now use SQL syntax:
- before: `min(t.a) FILTER (WHERE t.a > Float32(1))`, `ORDER BY [t.c ASC
NULLS LAST]`
- after: `min(t.a) FILTER (WHERE t.a > 1)`, `ORDER BY t.c`
`EXPLAIN FORMAT TREE` output changes the same way.
- New public API (additive): `Sort::human_display` and `SortListDisplay` in
`datafusion_expr::expr`.
Follow-up: turn the option on in `datafusion-cli`, where #2027 first
reported unreadable headers.
🤖 Generated with [Claude Code](https://claude.com/claude-code)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
