[
https://issues.apache.org/jira/browse/CALCITE-7585?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18086687#comment-18086687
]
zzwqqq commented on CALCITE-7585:
---------------------------------
Thanks [~jensen]. I opened this issue first to track the MERGE case that came
up during the review of [https://github.com/apache/calcite/pull/4969].
This has now been confirmed. SqlMerge has the same unparsing problem as
SqlUpdate and SqlDelete had in
https://issues.apache.org/jira/browse/CALCITE-7550: when an EXISTS predicate
appears in the MERGE ON clause, the SELECT operand of EXISTS is emitted without
parentheses.
For example, parsing and unparsing the following SQL:
{code:sql}
MERGE INTO "DEPT" AS "t"
USING "DEPT" AS "s"
ON EXISTS (
SELECT 1 FROM "EMP" AS "e"
WHERE "e"."DEPTNO" = "s"."DEPTNO")
WHEN MATCHED THEN
UPDATE SET "DNAME" = "s"."DNAME"
{code}
currently produces:
{code:sql}
MERGE INTO "SCOTT"."DEPT" AS "DEPT0"
USING "SCOTT"."DEPT"
ON EXISTS SELECT *
FROM "SCOTT"."EMP"
WHERE "DEPTNO" = "DEPT"."DEPTNO"
WHEN MATCHED THEN UPDATE SET "DNAME" = "DEPT"."DNAME"
{code}
The expected output should keep the parentheses around the SELECT operand of
EXISTS:
{code:sql}
MERGE INTO "SCOTT"."DEPT" AS "DEPT0"
USING "SCOTT"."DEPT"
ON EXISTS (SELECT *
FROM "SCOTT"."EMP"
WHERE "DEPTNO" = "DEPT"."DEPTNO")
WHEN MATCHED THEN UPDATE SET "DNAME" = "DEPT"."DNAME"
{code}
A minimal reproduction is to add the code in
org.apache.calcite.rel.rel2sql.RelToSqlConverterTest#testMerge:
{code:java}
final String sql = "merge into \"DEPT\" as \"t\"\n"
+ "using \"DEPT\" as \"s\"\n"
+ "on exists (select 1 from \"EMP\" as \"e\" where \"e\".\"DEPTNO\" =
\"s\".\"DEPTNO\")\n"
+ "when matched then\n"
+ "update set \"DNAME\" = \"s\".\"DNAME\"";
final String expected = "MERGE INTO \"SCOTT\".\"DEPT\" AS \"DEPT0\"\n"
+ "USING \"SCOTT\".\"DEPT\"\n"
+ "ON EXISTS (SELECT *\n"
+ "FROM \"SCOTT\".\"EMP\"\n"
+ "WHERE \"DEPTNO\" = \"DEPT\".\"DEPTNO\")\n"
+ "WHEN MATCHED THEN UPDATE SET \"DNAME\" = \"DEPT\".\"DNAME\"";
sql(sql)
.schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
.ok(expected);
{code}
Once [https://github.com/apache/calcite/pull/4969] is merged, I will follow up
on this issue separately.
> SqlMerge unparse EXISTS predicates in ON clause without parentheses
> -------------------------------------------------------------------
>
> Key: CALCITE-7585
> URL: https://issues.apache.org/jira/browse/CALCITE-7585
> Project: Calcite
> Issue Type: Bug
> Reporter: zzwqqq
> Assignee: zzwqqq
> Priority: Major
>
> https://github.com/apache/calcite/pull/4969 fixes unparsing of EXISTS
> subqueries in UPDATE and DELETE statements.
> During review, it was pointed out that MERGE may have a similar problem
> because EXISTS predicates can appear in the ON clause.
> This has not been fully confirmed yet. This issue is opened to track the
> potential MERGE case separately. If confirmed, MERGE should use the same
> parenthesized unparsing behavior and include regression tests.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
