[
https://issues.apache.org/jira/browse/CALCITE-7550?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated CALCITE-7550:
------------------------------------
Labels: pull-request-available (was: )
> SqlUpdate and SqlDelete unparse EXISTS subqueries without parentheses
> ---------------------------------------------------------------------
>
> Key: CALCITE-7550
> URL: https://issues.apache.org/jira/browse/CALCITE-7550
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: zzwqqq
> Priority: Major
> Labels: pull-request-available
>
> SqlUpdate can generate invalid SQL when the WHERE condition is an EXISTS
> subquery. SqlDelete has the same issue.
> For example, parsing and unparsing the following SQL:
> {code:sql}
> UPDATE "foodmart"."product"
> SET "product_name" = 'calcite'
> WHERE EXISTS (
> SELECT 1 FROM "foodmart"."product_class")
> {code}
> currently produces:
> {code:sql}
> UPDATE "foodmart"."product" SET "product_name" = 'calcite'
> WHERE EXISTS SELECT 1
> FROM "foodmart"."product_class"
> {code}
> The expected output should keep the parentheses around the SELECT operand of
> EXISTS:
> {code:sql}
> UPDATE "foodmart"."product" SET "product_name" = 'calcite'
> WHERE EXISTS (SELECT 1
> FROM "foodmart"."product_class")
> {code}
> A minimal reproduction is:
> {code:java}
> SqlNode node = SqlParser.create(
> "update \"foodmart\".\"product\"\n"
> + "set \"product_name\" = 'calcite'\n"
> + "where exists (\n"
> + " select 1 from \"foodmart\".\"product_class\")")
> .parseStmt();
> System.out.println(node.toSqlString(CalciteSqlDialect.DEFAULT).getSql());
> {code}
> The likely cause is that SqlUpdate and SqlDelete unparse their WHERE
> condition directly inside a SELECT frame. In contrast, SqlSelectOperator
> unparses its WHERE condition inside a WHERE_LIST frame. In a WHERE_LIST
> frame, a nested SqlSelect used in a predicate is emitted as a sub-query, so
> SELECT statements keep the parentheses around EXISTS subqueries.
> A possible fix is to extract the WHERE unparsing logic used by
> SqlSelectOperator into a shared helper, and call it from SqlSelectOperator,
> SqlUpdate, and SqlDelete. This would make UPDATE and DELETE use the same
> WHERE predicate unparsing behavior as SELECT, instead of special-casing
> EXISTS.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
