UnifiedQueryPlanner should preserve original SQL expression names instead of EXPR$N

[vamsimanohar]

Problem

When executing SQL queries with unnamed aggregate expressions through UnifiedQueryPlanner, Calcite's default column naming produces EXPR$0, EXPR$1, etc. instead of the original SQL expression text.

Example:

SELECT COUNT(*) FROM hits

Returns column name EXPR$0 instead of count(*).

SELECT SUM(advengineid), COUNT(*), AVG(resolutionwidth) FROM hits

Returns EXPR$0, EXPR$1, EXPR$2 instead of sum(advengineid), count(*), avg(resolutionwidth).

Note: Explicitly aliased expressions work correctly — SELECT COUNT(*) AS cnt returns cnt.

Expected Behavior

Match PostgreSQL/MySQL convention — use the original SQL expression text as the column name when no alias is provided:

Engine	SELECT COUNT(*) column name
PostgreSQL	count
MySQL	COUNT(*)
DuckDB	count_star()
Spark SQL	count(1)
Trino/Presto	_col0
Current (Calcite)	EXPR$0

Root Cause

In UnifiedQueryPlanner.plan(), the flow is:

1. Parse SQL → SqlNode (has original expression text like COUNT(*))
2. Validate → SqlNode (still has expression text)
3. Convert to RelNode → Calcite replaces unnamed expressions with EXPR$N

The original expression text is available in the SqlNode (specifically SqlSelect.getSelectList()) but is lost during SqlToRelConverter.

Proposed Fix

In UnifiedQueryPlanner, after parsing but before/after converting to RelNode:

1. Extract the select-list expression names from the SqlNode (e.g., SqlCall.toString() gives COUNT(*))
2. After SqlToRelConverter produces the RelNode, rename any EXPR$N fields back to the original expression text (lowercased to match PostgreSQL convention)

This could be done by wrapping the resulting RelNode in a LogicalProject that renames the fields, or by overriding the RelDataType field names.

Context

This affects the lakehouse SQL endpoint (_lakehouse/sql) in the analytics-engine plugin. The UnifiedQueryService calls UnifiedQueryPlanner.plan() and uses RelNode.getRowType().getFieldNames() to get column names for the response. Since UnifiedQueryPlanner is in the org.opensearch.sql.api package (compiled dependency in dsl-query-executor), downstream consumers cannot fix this without modifying the planner.

Workaround

Downstream consumers can post-process column names (e.g., replace EXPR$N with _colN), but this loses the original expression text.

[dai-chen]

Thanks for reporting the issue! I need to evaluate what's the impact. Currently I found that the SQL standard leaves unnamed column naming as implementation-defined, so Calcite deliberately uses detectable EXPR$N placeholders (with $ to avoid collisions) and expects downstream engines to supply their own presentation names.

To align with our V2 SQL and PPL V3 engines — which both preserve the original expression text — we should add a post-conversion rename step in CalciteNativeStrategy that restores expression names from the SqlNode before it goes out of scope. Edge cases like SELECT *, subqueries, and mixed aliased/unaliased expressions need careful handling.

References:

• Flink SQL: https://nightlies.apache.org/flink/flink-docs-master/docs/sql/reference/queries/overview/#syntax
• Drill SQL: https://drill.apache.org/docs/aggregate-and-aggregate-statistical/

# Flink
Flink SQL> SELECT 'Hello World', 'It''s me';
+-------------+---------+
|      EXPR$0 |  EXPR$1 |
+-------------+---------+
| Hello World | It's me |
+-------------+---------+
1 row in set

# Drill
SELECT AVG(salary) FROM cp.`employee.json` WHERE employee_id IN (1139, 1140, 1141);
|-------------------|
| EXPR$0            |
|-------------------|
| 8433.333333333334 |
|-------------------|
1 row selected (0.208 seconds)

[dai-chen]

Unnamed SELECT-expression column naming across engines

Engine	SELECT count(*)	SELECT Upper(name)	SELECT sum(a)	Style
PostgreSQL	count	upper	sum	Lowercase canonical, no args
MySQL	count(*)	Upper(name)	sum(a)	Verbatim user text
Spark SQL	count(1)	upper(name)	sum(a)	Lowercase canonical, rewrites
Apache Flink	EXPR$0	EXPR$0	EXPR$0	Calcite default (synthetic)
Apache Druid	EXPR$0	EXPR$0	EXPR$0	Calcite default (synthetic)
Apache Drill	EXPR$0	EXPR$0	EXPR$0	Calcite default (synthetic)
Trino / Presto	_col0	_col0	_col0	Synthetic positional
Apache Hive	_c0	_c0	_c0	Synthetic positional
DuckDB	count_star()	upper(name)	sum(a)	Lowercase canonical, custom names
ClickHouse	count()	Upper(name)	sum(a)	Lowercase function, preserved args
OpenSearch V2	count(*)	Upper(name)	sum(a)	Verbatim user text
OpenSearch PPL V3	count()	upper(name)	sum(a)	Verbatim user text
OpenSearch SQL V3 (this PR)	COUNT(*)	UPPER(name)	SUM(a)	Canonical function + bare identifiers

Sources:

• PostgreSQL: SQL Syntax — Identifiers
• MySQL Bug #25224
• Spark: Count.scala
• Trino DESCRIBE OUTPUT
• Flink, Druid, Drill: use Apache Calcite; EXPR$N is Calcite's default per SqlUtil.GENERATED_EXPR_ALIAS_PREFIX
• DuckDB COUNT
• ClickHouse count()

Observations:

1. No cross-engine consensus. Three styles coexist: verbatim (MySQL/V2/PPL V3), lowercase canonical (PostgreSQL/Spark), synthetic (Trino/Hive/Calcite-based defaults).
2. Calcite-based engines that don't override this behavior (Flink, Druid, Drill) emit EXPR$N — the least useful.
3. OpenSearch SQL V3 chose uppercase-canonical to match Calcite's operator convention while keeping user-typed identifier casing intact.