[Proposal] Readable default column names for SQL query results

[haochunchang]

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.
• 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 beautify default column names #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, 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. 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-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 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, 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.
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?

[haochunchang]

take