Repository navigation
Description
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())indatafusion/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
FILTERandORDER BYclauses follow the same rules. - Window functions use SQL
OVER (...)syntax without default parts. See Rendering window functions. - CAST is left out, as it is today.
- Explicit aliases, at any level, and columns that you name in the outermost
SELECTkeep 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, andGROUP BYreferences, includingORDER BYby the current name, such asORDER BY "t.a + Int64(1)".
These parts of DataFusion keep today's names:
CREATE VIEW,CREATE TABLE AS,INSERT ... SELECT,COPY, andDESCRIBE. 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:
- The SQL planner builds the complete query plan as it does today, including
ORDER BY,HAVING,QUALIFY,DISTINCT, andUNNEST. - For each output column of the plan, DataFusion computes a label with
label_for(plan, column). - 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 likeSELECT 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-onlyProjectionExecthat reuses the input arrays without copying them. - A column whose label equals its current name gets no alias. For example,
SELECT * FROM big_tablehas 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
- Turn on the option in
datafusion-cli, where beautify default column names #2027 first reported unreadable headers. - 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
ScalarFunctioncase. Today this case falls back to the genericDisplayform, socoalesce(NULL, 1)renders ascoalesce(NULL, Int64(1)). - Add parentheses by operator precedence in the
BinaryExprcase. Today(a + b) * crenders asa + b * c. - Fix
GroupingSet::Cube, which printsROLLUP (instead ofCUBE (.
- Add a
-
In
UdafHumanDisplayBuilder(datafusion/expr/src/udaf.rs), render theFILTERclause withSqlDisplayinstead ofDisplay, and render theORDER BYclause in SQL syntax instead of withschema_name_from_sorts. Aggregate functions that overrideAggregateUDFImpl::human_displaykeep 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
transformbefore rendering. -
Add
label_forand the labeling step in theStatement::Querybranch ofsql_statement_to_plan. -
Add tests for each row in Proposed behavior, and for
ORDER BYby the current name,ORDER BYby position,HAVING,DISTINCT ON,UNNEST, joins withUSING, 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, nott.a + t.b). - Operators get parentheses only where precedence needs them (
1 + 2, not(1 + 2)). - Negation renders as
(- a), the currentSqlDisplayform.
DataFrame output keeps the rules in the specification.
- Compound column names drop their qualifier (
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. beautify default column names #2027, cast expression may cause duplicate column name error #5174, and Better name display for CAST #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-cliandDataFrame::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
SELECTblock during planning. A flag inPlannerContextwould mark the outermost block.query_to_planclones the context for every nested query, so each nested call site would have to clear the flag, and labels could reachORDER BYandHAVINGresolution 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
ColumnandDFSchema. This proposal is a step in that direction, and name-based lookups can move to comparing expressions one at a time, asORDER BYalready 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 beautify default column names #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, 1SELECT CAST(a AS DOUBLE), a::VARCHAR
Open questions
- Renaming or metadata. Rename the column, or attach the label as metadata? See Describe alternatives you've considered.
- Option name and namespace. Is
datafusion.sql_parser.pretty_column_namesa good name, or does the option belong in another namespace, such asdatafusion.format? - 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? - 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 + 1anda + 1_1, are a third option. - Session-dependent labels. A window label shows its
NULLSclause whendefault_null_orderingisn't the default, so the same query can get different headers in different sessions. Is that acceptable? - Custom window display. Aggregate functions that override
window_function_display_namelose that format in window labels. Should window functions get ahuman_displaymethod, which changes the public API?
Activity
haochunchang commentedon Sep 30, 2026
take
haochunchang commentedon Oct 4, 2026
Proposed change of direction: labels as field metadata, not renames
I'd like to switch the proposal to the metadata approach (open question 1). Column names stay exactly as they are today. The readable label goes into Arrow field metadata, and display tools show it in the header.
How labels are computed doesn't change: the
label_fortrace, the labeling rules, window rendering, and theSqlDisplayfixes all stay. Only the final step changes.Why
Renaming causes the hardest problems in the current proposal:
df["sum(t.a)"]in clients or DataFrame calls afterctx.sql().SELECT t1.a + 1, t2.a + 1, need a fallback rule.With metadata, no name changes, so neither problem exists. Open questions 1, 3, and 4 go away.
Trade-off: clients that don't read field metadata, such as pandas and JDBC, keep showing today's names.
How it works
The labeling step aliases each labeled column to its own name with
Expr::alias_with_metadata, adding two keys:datafusion.label: the label, such assum(a).datafusion.label_of: the column name, such assum(t.a).Display tools show the label only when the field name still equals
label_of. Field metadata survives later aliases, so without this check,with_column_renamed("sum(t.a)", "total")would still displaysum(a).DataFusion already carries alias metadata through the optimizers into
ProjectionExec. A local prototype confirmed that the labels reach every batch fromcollect()and thatORDER BY ... LIMITstill plans asTopK.Where labels show
datafusion-clitable output andDataFrame::show()show labels.datafusion-cliCSV and JSON output keep column names, because programs parse them.Compatibility
The option stays opt-in and is turned on first in
datafusion-cli. With it on:MemTable::try_newwith a hand-built schema.EXPLAINshows<name> AS <name>on the top projection.write_parquet()stores the label metadata in the file.Open questions
datafusion.sql_parser.column_labelsright?ARROW:FLIGHT:SQL:REMARKSas the JDBC column label. Should Flight SQL servers copy the label there?truelater, since no names change?Questions 5 and 6 from the description are unchanged. Renaming moves to "alternatives considered". A
DataFrame::with_display_names()helper could be built on the metadata later.If this direction sounds right, I'll update the issue description to match.
feat: add opt-in readable column names for SQL results
feat: add opt-in readable column names for SQL results
feat: add opt-in readable column names for SQL results
feat: add opt-in readable column names for SQL results
feat: add opt-in readable column names for SQL results