haochunchang opened a new issue, #25903:
URL: https://github.com/apache/datafusion/issues/25903
Related: #2027, #5174, #10274, #3222
### Is your feature request related to a problem or challenge?
When a SQL `SELECT` item has no alias, DataFusion names the output column
with
the planner's internal lookup key. The headers are hard to read and often
very
wide:
| Query item | Column name today
|
| -------------------------------- |
----------------------------------------------------------------------------------------------
|
| `1 + 2` | `Int64(1) + Int64(2)`
|
| `row_number() OVER (ORDER BY a)` | `row_number() ORDER BY [t.a ASC NULLS
LAST] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` |
#### Why the names look like this
`Expr::schema_name()` has two jobs:
- **Display label.** It becomes the column header that you see in query
results.
- **Lookup key.** The planner uses it to find columns. For example, a parent
plan finds an aggregate's output with
`Column::from_name(expr.schema_name())` in `datafusion/expr/src/utils.rs`.
The key needs to be unique and unambiguous, so it includes type wrappers
(`Int64(1)` versus `Utf8("1")`) and table qualifiers (`t.a`). Those same
details
make headers hard to read. Shortening the key risks giving two different
expressions the same key. For example, CAST is left out of names (#3222), so
`SELECT CAST(a AS DOUBLE), a::VARCHAR` fails with a unique-name error.
### Describe the solution you'd like
Add an opt-in setting that gives each unaliased output column of a SQL query
a
readable label. The label is computed once, after the query is planned, and
applied as an alias on the query result. The internal key doesn't
change, so name resolution, views, and the DataFrame API keep working, and
query
execution speed doesn't change.
#### Proposed behavior
The current names in these tables come from running the queries on the `main`
branch.
##### Expressions
| Query item | Current name
| Proposed name |
| ------------------------------------------- |
---------------------------------------------------------------- |
--------------------------------------- |
| `1`, `'foo'` | `Int64(1)`, `Utf8("foo")`
| `1`, `foo` |
| `1 + 2` | `Int64(1) + Int64(2)`
| `1 + 2` |
| `(a + b) * c` | `t.a + t.b * t.c`
| `(a + b) * c` |
| `t.a + 1` | `t.a + Int64(1)`
| `a + 1` |
| `coalesce(NULL, 1)` | `coalesce(NULL,Int64(1))`
| `coalesce(NULL, 1)` |
| `CASE WHEN a > 0 THEN 'pos' ELSE 'neg' END` | `CASE WHEN t.a > Int64(0)
THEN Utf8("pos") ELSE Utf8("neg") END` | `CASE WHEN a > 0 THEN pos ELSE neg
END` |
##### Aggregate functions
| Query item | Current name |
Proposed name |
| ----------------------------- | ---------------------------------------- |
----------------------------- |
| `sum(a)`, `count(DISTINCT b)` | `sum(t.a)`, `count(DISTINCT t.b)` |
`sum(a)`, `count(DISTINCT b)` |
| `avg(c) FILTER (WHERE c > 5)` | `avg(t.c) FILTER (WHERE t.c > Int64(5))` |
`avg(c) FILTER (WHERE c > 5)` |
| `count(*)` | `count(*)` |
`count(*)` (planner alias) |
| `count(1)` | `count(Int64(1))` |
`count(1)` |
##### Window functions
| Query item
| Current name
| Proposed name
|
|
--------------------------------------------------------------------------- |
------------------------------------------------------------------------------------------------
| --------------------------------------------------------------------------- |
| `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)`
|
| `sum(a) OVER (PARTITION BY b)`
| `sum(t.a) PARTITION BY [t.b] ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED
FOLLOWING` | `sum(a) OVER (PARTITION BY b)`
|
| `sum(a) OVER (ORDER BY a DESC NULLS FIRST)`
| `sum(t.a) ORDER BY [t.a DESC NULLS FIRST] RANGE BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW` | `sum(a) OVER (ORDER BY a DESC)`
|
| `sum(a) OVER (ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT
ROW)` | `sum(t.a) ORDER BY [t.a ASC NULLS LAST] ROWS BETWEEN UNBOUNDED
PRECEDING AND CURRENT ROW` | `sum(a) OVER (ORDER BY a ROWS BETWEEN
UNBOUNDED PRECEDING AND CURRENT ROW)` |
| `sum(b) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)`
| `sum(t.b) ORDER BY [UInt64(1) ASC NULLS LAST] RANGE BETWEEN UNBOUNDED
PRECEDING AND CURRENT ROW` | `sum(b) OVER ()`
|
##### Columns from CTEs, derived tables, and views
| Query | Current
name | Proposed name |
| -------------------------------------------------------------- |
---------------- | --------------------------- |
| `WITH x AS (SELECT sum(a) FROM t GROUP BY b) SELECT * FROM x` |
`sum(t.a)` | `sum(a)` |
| `SELECT * FROM (SELECT a + 1 FROM t)` | `t.a +
Int64(1)` | `a + 1` |
| `SELECT * FROM v`, where `v` is a view over `SELECT a + 1 ...` | `t.a +
Int64(1)` | `a + 1` |
| `SELECT x FROM (SELECT a + 1 AS x FROM t)` | `x`
| `x` (explicit alias) |
| `SELECT "t.a + Int64(1)" FROM (SELECT a + 1 FROM t)` | `t.a +
Int64(1)` | `t.a + Int64(1)` (as typed) |
#### Naming rules
- Literals are plain values, and column references drop their qualifier.
Operators and function arguments use SQL spacing, with parentheses where
operator precedence needs them.
- Aggregate `FILTER` and `ORDER BY` clauses follow the same rules.
- Window functions use SQL `OVER (...)` syntax without default parts. See
[Rendering window functions](#rendering-window-functions).
- CAST is left out, as it is today.
- Explicit aliases, at any level, and columns that you name in the outermost
`SELECT` keep their names. Columns from `*` and computed expressions get
labels.
#### Scope
Readable labels apply only to the result of a top-level SQL query statement.
Labels are added after the whole query plan is built, so every column inside
the plan keeps its current name, and these references keep working:
- Outer queries that reference an inner column by its current name, such as
`"t.a + Int64(1)"`.
- `ORDER BY`, `HAVING`, and `GROUP BY` references, including `ORDER BY` by
the
current name, such as `ORDER BY "t.a + Int64(1)"`.
These parts of DataFusion keep today's names:
- `CREATE VIEW`, `CREATE TABLE AS`, `INSERT ... SELECT`, `COPY`, and
`DESCRIBE`. Stored column names don't change.
- The DataFrame API.
#### Design
##### Where labels are applied
Labels are a final step in the `Statement::Query` branch of
`sql_statement_to_plan` in `datafusion/sql/src/statement.rs`:
1. The SQL planner builds the complete query plan as it does today, including
`ORDER BY`, `HAVING`, `QUALIFY`, `DISTINCT`, and `UNNEST`.
2. For each output column of the plan, DataFusion computes a label with
`label_for(plan, column)`.
3. If at least one label differs from the current name, DataFusion adds a
projection on top of the plan that aliases each column to its label.
To keep the name that you typed for an explicit column reference, DataFusion
collects the identifiers that appear as plain column items in the outermost
`SELECT` list. An output column that is a plain column reference with one of
those names keeps its name.
##### Tracing each column to its origin
A label comes from the expression that produced a column, which is often
lower
in the plan than the outermost projection. The SQL planner replaces aggregate
and window calls with key-named column references (`aggregate()` and
`rebase_expr` in `datafusion/sql/src/select.rs`), so the outermost projection
for `SELECT sum(a) + 1 FROM t` refers to a column named `sum(t.a)`. Columns
from
`SELECT *` are produced inside the CTE, derived table, or view. Views need no
special case, because `LogicalPlanBuilder::scan_with_filters` expands a view
into its own plan.
`label_for(plan, column)` follows the column down through the plan and
returns
a label or `None`:
| Plan node
| Trace step
|
|
--------------------------------------------------------------------------------------------------------------------
|
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|
| `Projection`
| Find the expression at the column's
index. An explicit alias stops the trace and keeps its name. A column reference
continues into the input. Other expressions are rendered, and each column
inside them is traced recursively. |
| `Aggregate`
| Render the grouping or aggregate
expression at the column's index.
|
| `Window`
| Render the window expression at the
column's index.
|
| `SubqueryAlias`, `Filter`, `Sort`, `Limit`, `Distinct::All`
| Continue into the input at the same
field index.
|
| `Join` with qualified columns
| Continue into the side that owns
the field index.
|
| `Union`
| Continue into the first branch. SQL
takes output names from the first branch.
|
| `TableScan`
| Return the plain column name.
|
| `Join` with `USING` or `NATURAL`, `Distinct::On`, `Unnest`,
`RecursiveQuery`, `Values`, `EmptyRelation`, `Extension` | Return `None`.
|
The trace follows field indexes instead of names, so it doesn't depend on how
each node qualifies its fields. If the trace returns `None` for any column
inside an expression, the whole label is `None`. A column with a `None` label
keeps its current name.
##### Why labels are computed before optimization
The optimizer folds `1 + 2` into the constant `3` but keeps the original
name.
Tracing the plan before optimization keeps the label `1 + 2`.
##### Why collisions fall back to current names
Two different expressions can have the same readable label. For example,
`SELECT t1.a + 1, t2.a + 1` produces `a + 1` twice, and `SELECT 1, '1'`
produces `1` twice. The planner requires unique names in a projection, so
aliasing both columns to the same label fails. Keeping the current name for
colliding columns means the setting never makes a working query fail.
##### Rendering window functions
The window label is `name(args) OVER (PARTITION BY ... ORDER BY ... frame)`,
with each part left out when it equals its default:
| Part | Left out when
|
| ------------------------------ |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
|
| Window frame | It equals the planner's default for this
`ORDER BY`: `WindowFrame::new`, with the same tie check that
`datafusion/sql/src/expr/function.rs` uses. |
| Constant `ORDER BY` keys | Always. The planner adds `ORDER BY 1`
when a `RANGE` frame has no `ORDER BY` (`WindowFrame::regularize_order_bys`),
and a constant key doesn't change the result. |
| `ASC` | Always, because it's the default sort
direction.
|
| `NULLS LAST` and `NULLS FIRST` | The combination is `ASC NULLS LAST` or
`DESC NULLS FIRST`.
|
| `PARTITION BY` and `ORDER BY` | The list is empty.
|
An explicit `ROWS` frame on an `ORDER BY` that can have ties stays in the
label, because tied rows give different results than the default `RANGE`
frame.
A named window, such as `OVER w`, is expanded before planning, so its label
shows the full `OVER (...)` definition.
#### Performance
##### Query execution
Execution speed doesn't change:
- The label is an alias. `SELECT a + 1 AS "a + 1"` runs exactly like
`SELECT a + 1 AS x`, which you can write today.
- The labeling projection usually merges into the query's own projection
(`merge_consecutive_projections`). Otherwise it's a rename-only
`ProjectionExec` that reuses the input arrays without copying them.
- A column whose label equals its current name gets no alias. For example,
`SELECT * FROM big_table` has the same plan with the setting on or off.
##### Query planning
The labeling step runs once per query. Its cost is one trace per output
column,
as deep as the plan, with no lookups by column name.
##### Benchmarks
Compare the setting on and off with `logical_select_all_from_1000`,
`physical_select_all_from_1000`, `physical_select_aggregates_from_200`,
`logical_wide_aggregate_100_exprs`, `physical_plan_tpch_all`, and
`physical_plan_tpcds_all` in `datafusion/core/benches/sql_planner.rs`. Also
add
a query benchmark with an aggregate, `ORDER BY`, and `LIMIT`, to confirm that
TopK and limit pushdown still apply.
#### Compatibility
##### Configuration
Add a boolean option in the `datafusion.sql_parser` namespace, next to
`enable_ident_normalization` and `default_null_ordering` in
`datafusion/common/src/config.rs`. The default is `false`. While the option
is
`false`, DataFusion behaves exactly as it does today, and the only planning
cost is reading one configuration value.
##### Rollout
1. Turn on the option in `datafusion-cli`, where #2027 first reported
unreadable headers.
2. Decide separately whether to change the default. Changing it is a breaking
change for client code that reads columns by name.
##### What changes when you turn on the option
| Affected area
| Effect
| Impact |
|
----------------------------------------------------------------------------------------------------------------------
| -------------------------------------------------- | ------ |
| Client code that reads result columns by name, such as `df["sum(t.a)"]` in
Python, JDBC, ADBC, Flight SQL, or Ballista | Lookups by the old name fail
| High |
| Code that calls `ctx.sql()` and then uses DataFrame methods with generated
names, such as `.select(col("sum(t.a)"))` | Lookups by the old name fail
| High |
| Tests that print table headers in `.rs` and `.snap` files (about 172
header lines contain type wrappers) | Snapshots change
| Medium |
| `EXPLAIN` output in `.slt` files
| The top projection shows the new
aliases | Medium |
| Substrait and protobuf round-trip tests
| Top-level output names change
| Low |
| Unparser (plan to SQL)
| Output includes `AS "<label>"`,
which is valid SQL | Low |
Some required code changes also affect output when the option is off. The
`SqlDisplay` and `UdafHumanDisplayBuilder` changes below change the text of
`EXPLAIN FORMAT TREE`, which uses `Expr::human_display()`.
#### Required code changes
- In `SqlDisplay` (`datafusion/expr/src/expr.rs`):
- Add a `ScalarFunction` case. Today this case falls back to the generic
`Display` form, so `coalesce(NULL, 1)` renders as
`coalesce(NULL, Int64(1))`.
- Add parentheses by operator precedence in the `BinaryExpr` case. Today
`(a + b) * c` renders as `a + b * c`.
- Fix `GroupingSet::Cube`, which prints `ROLLUP (` instead of `CUBE (`.
- In `UdafHumanDisplayBuilder` (`datafusion/expr/src/udaf.rs`), render the
`FILTER` clause with `SqlDisplay` instead of `Display`, and render the
`ORDER BY` clause in SQL syntax instead of with `schema_name_from_sorts`.
Aggregate functions that override `AggregateUDFImpl::human_display` keep
their own format.
- Add window rendering to the labeling code. It needs the input schema for
the
tie check.
- Add a helper that removes qualifiers from an expression by using
`transform`
before rendering.
- Add `label_for` and the labeling step in the `Statement::Query` branch of
`sql_statement_to_plan`.
- Add tests for each row in [Proposed behavior](#proposed-behavior), and for
`ORDER BY` by the current name, `ORDER BY` by position, `HAVING`,
`DISTINCT ON`, `UNNEST`, joins with `USING`, set operations, recursive
CTEs,
and window functions ordered by a unique key.
- Update
`docs/source/contributor-guide/specification/output-field-name-semantic.md`.
The proposal replaces three of its rules for SQL results when the option is
on:
- Compound column names drop their qualifier (`a + b`, not `t.a + t.b`).
- Operators get parentheses only where precedence needs them (`1 + 2`, not
`(1 + 2)`).
- Negation renders as `(- a)`, the current `SqlDisplay` form.
DataFrame output keeps the rules in the specification.
### Describe alternatives you've considered
- **Shorten `schema_name()` itself.** Every place that resolves columns by
name
would see the new names, and different expressions would collide more
often.
#2027, #5174, and #10274 stalled on this problem.
- **Use a fixed name, such as Postgres `?column?` or Hive `_c0`.** These
names
keep headers short, but they drop what each column contains. `?column?`
also
repeats, so it needs the same collision handling.
- **Attach the label as field metadata instead of renaming.** The labeling
step
would add a metadata key, such as `datafusion.label`, and keep every column
name unchanged. `datafusion-cli` and `DataFrame::show()` would display the
label. This alternative changes no names, so it has no compatibility impact
and no collisions. The trade-off: clients that don't read the metadata,
such
as Python, JDBC, ADBC, and Flight SQL, still see today's names.
- **Label each `SELECT` block during planning.** A flag in `PlannerContext`
would mark the outermost block. `query_to_plan` clones the context for
every
nested query, so each nested call site would have to clear the flag, and
labels could reach `ORDER BY` and `HAVING` resolution in the same block.
- **Separate identity from names completely.** Every expression would get an
opaque identifier, as Spark does with expression IDs, which is a large
change
to `Column` and `DFSchema`. This proposal is a step in that direction, and
name-based lookups can move to comparing expressions one at a time, as
`ORDER BY` already does.
### Additional context
#### Complaints not addressed
- **Width.** A label is as long as the expression that it describes. A long
expression, or a window function with an explicit frame, still produces a
wide header.
- **The example in #2027.** `coalesce(NULL, NULL, NULL, NULL)` already has no
type wrappers today. The label adds a space after each comma, so it's 3
characters wider.
#### Out of scope
These queries fail today because two expressions share a lookup key. Labels
don't fix them:
- `SELECT 1, 1`
- `SELECT CAST(a AS DOUBLE), a::VARCHAR`
#### Open questions
1. **Renaming or metadata.** Rename the column, or attach the label as
metadata? See
[Describe alternatives you've
considered](#describe-alternatives-youve-considered).
2. **Option name and namespace.** Is
`datafusion.sql_parser.pretty_column_names`
a good name, or does the option belong in another namespace, such as
`datafusion.format`?
3. **SQL followed by DataFrame calls.** With the option on, a DataFrame
returned by `ctx.sql()` uses the labels as column names. Is documenting
this
enough?
4. **Collision handling.** Should colliding columns keep their current names
one at a time, as proposed, or should the whole query keep current names
when any label collides? Numbered suffixes, such as `a + 1` and `a + 1_1`,
are a third option.
5. **Session-dependent labels.** A window label shows its `NULLS` clause when
`default_null_ordering` isn't the default, so the same query can get
different headers in different sessions. Is that acceptable?
6. **Custom window display.** Aggregate functions that override
`window_function_display_name` lose that format in window labels. Should
window functions get a `human_display` method, which changes the public
API?
--
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]
