[jira] [Created] (CALCITE-5119) SqlNode#unparse hides an alias table when using CTE

["Mitsunori Komatsu (Jira)" <ji...@apache.org>]

Mitsunori Komatsu created CALCITE-5119:
------------------------------------------

             Summary: SqlNode#unparse hides an alias table when using CTE
                 Key: CALCITE-5119
                 URL: https://issues.apache.org/jira/browse/CALCITE-5119
             Project: Calcite
          Issue Type: Bug
          Components: core
    Affects Versions: 1.30.0
            Reporter: Mitsunori Komatsu


Hi team,

I found SqlNode#unparse hides an alias table when using CTE.

 
{code:java}
val planner = Frameworks.getPlanner(config)

val sql = """
    WITH cte AS (
      select 42 as i
    )
    SELECT i FROM (
      SELECT [i] from cte
    ) AS [alias]
    ORDER BY [alias].[i]
""".trimIndent()

val node = planner.parse(sql)
println(node.toSqlString { c ->
    c.withDialect(PrestoSqlDialect.DEFAULT)
        .withAlwaysUseParentheses(false)
        .withSubQueryStyle(SqlWriter.SubQueryStyle.HYDE)
        .withClauseStartsLine(false)
        .withClauseEndsLine(false)
}) {code}
This code shows the following
{code:java}
WITH "cte" AS (SELECT 42 AS "i")
(SELECT "i" FROM (SELECT "i" FROM "cte") AS "alias")
ORDER BY "alias"."i"{code}
The `AS "alias"` is inside a block and the last `ORDER BY` can't find `alias` and this query fails with Presto. I think the output should be as follows
{code:java}
WITH "cte" AS (SELECT 42 AS "i")
SELECT "i" FROM (SELECT "i" FROM "cte") AS "alias"
ORDER BY "alias"."i"{code}
 



--
This message was sent by Atlassian Jira
(v8.20.7#820007)