zzwqqq created CALCITE-7550:
-------------------------------
Summary: 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
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)
