[ https://issues.apache.org/jira/browse/CALCITE-5119?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17529161#comment-17529161 ]
Jiajun Xie commented on CALCITE-5119: ------------------------------------- Parsed SqlNode need to be validated, because the CTE will be rewritten as subquery. Do you want to keep CTE?[~komamitsu] > 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 > Priority: Major > > 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 outputs the following SQL > {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)