Repository navigation
Unparser with PostgreSqlDialect fails after Optimization Pass #21066
Description
Describe the bug
The Unparser has a bug in the way that it treats certain PostgreSQL formatted queries, after optimization, which seems to be because requires_derived_table_alias is true:
I.e, optimizing then unparsing this query with postgresql dialect breaks:
WITH base AS (SELECT name, salary FROM t)
SELECT name,
CASE WHEN SUM(salary) > 0 THEN 1 ELSE 0 END AS x,
CASE WHEN SUM(salary) > 0 THEN SUM(salary) ELSE 0 END AS y
FROM base GROUP BY nameTo Reproduce
Here's a test we can add somewhere that fails:
#[tokio::test]
async fn test_cse_derived_projection_roundtrip() {
let ctx = SessionContext::new();
ctx.sql("CREATE TABLE t (name TEXT, salary DOUBLE) AS VALUES ('a', 1.0), ('b', 2.0)")
.await.unwrap().collect().await.unwrap();
let sql = "\
WITH base AS (SELECT name, salary FROM t) \
SELECT name, \
CASE WHEN SUM(salary) > 0 THEN 1 ELSE 0 END AS x, \
CASE WHEN SUM(salary) > 0 THEN SUM(salary) ELSE 0 END AS y \
FROM base GROUP BY name";
let df = ctx.sql(sql).await.unwrap();
let optimized = ctx.state().optimize(df.logical_plan()).unwrap();
let unparser = Unparser::new(&datafusion_sql::unparser::dialect::PostgreSqlDialect {});
let unparsed = unparser.plan_to_sql(&optimized).unwrap().to_string();
ctx.sql(&unparsed).await.unwrap_or_else(|e| {
panic!("Roundtrip failed.\n\nOriginal: {sql}\nUnparsed: {unparsed}\nError: {e}")
});
}This fails with the following error message on main:
Roundtrip failed.
Original: WITH base AS (SELECT name, salary FROM t) SELECT name, CASE WHEN SUM(salary) > 0 THEN 1 ELSE 0 END AS x, CASE WHEN SUM(salary) > 0 THEN SUM(salary) ELSE 0 END AS y FROM base GROUP BY name
Unparsed: SELECT "base"."name", CASE WHEN "__common_expr_1" THEN 1 ELSE 0 END AS "x", CASE WHEN "__common_expr_1" THEN "sum(base.salary)" ELSE 0.0 END AS "y" FROM (SELECT (sum("base"."salary") > 0.0) AS "__common_expr_1", "base"."name", sum("base"."salary") FROM "t" AS "base" GROUP BY "base"."name") AS "derived_projection"
Error: Schema error: No field named base.name. Valid fields are derived_projection.__common_expr_1, derived_projection.name, derived_projection."sum(base.salary)".
Expected behavior
This should parse correctly
Additional context
No response
Reacted by Adrian Garcia Badaracco
Activity
adriangb commentedon Mar 21, 2026
@cetra3 would you mind including the error that you get when running this example? It's useful to be able to cross reference against other issues.
cetra3 commentedon Mar 22, 2026
Done!
fix: preserve computed names in derived SQL projections (apache#25521)
amitvijapur commentedon Sep 22, 2026
I ran the test from the description against
mainat ee8344c today. TheNo field named base.nameerror is gone: the outer projection now qualifiesnamewithderived_projection, sincerebase_derived_input_exprinunparser/plan.rsrewrites the outer expressions when the dialect requires a derived table alias, and #25521 (merged today) makes the inner select emitsum("base"."salary") AS "sum(base.salary)"so that reference resolves too.The round trip still fails, but on a different check:
check_planinsql/src/statement.rsrejects any field whose name starts with__common_expr, so the SQL that the unparser produces for an optimized plan cannot be planned again by DataFusion. The emitted SQL itself is fine: after a textual rename of__common_expr_the query plans and runs, for this query and for CSE shapes without a CTE, under bothPostgreSqlDialectandDefaultDialect.So the qualifier bug this issue is about is fixed, and what remains is a choice between renaming reserved aliases in the unparser before emission or narrowing
check_planto the top-level schema, which reopens the collision the guard was added for (test_reserved_column_name*insql_integration.rspins the current behaviour). That seems worth a maintainer's call before anyone writes it, and probably a separate issue, since it is not specific to PostgreSQL.fix: preserve computed names in derived SQL projections (apache#25521)
fix: column ids, stage 7: fix decorrelation and remaining id bugs