JiajunBernoulli commented on code in PR #3397:
URL: https://github.com/apache/calcite/pull/3397#discussion_r1313773990
##########
core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java:
##########
@@ -7027,6 +7027,136 @@ private void checkLiteral2(String expression, String
expected) {
.ok(expected);
}
+ @Test void testMerge() {
+ final String sql1 = "merge into \"DEPT\" as \"t\"\n"
+ + "using \"DEPT\" as \"s\"\n"
+ + "on \"t\".\"DEPTNO\" = \"s\".\"DEPTNO\"\n"
+ + "when matched then\n"
+ + "update set \"DNAME\" = \"s\".\"DNAME\"\n"
+ + "when not matched then\n"
+ + "insert (DEPTNO, DNAME, LOC)\n"
+ + "values (\"s\".\"DEPTNO\" + 1, lower(\"s\".\"DNAME\"),
upper(\"s\".\"LOC\"))";
+ final String expected1 = "MERGE INTO \"SCOTT\".\"DEPT\" AS \"DEPT0\"\n"
+ + "USING \"SCOTT\".\"DEPT\"\n"
+ + "ON \"DEPT\".\"DEPTNO\" = \"DEPT0\".\"DEPTNO\"\n"
+ + "WHEN MATCHED THEN UPDATE SET \"DNAME\" = \"DEPT\".\"DNAME\"\n"
+ + "WHEN NOT MATCHED THEN INSERT (\"DEPTNO\", \"DNAME\", \"LOC\") "
+ + "VALUES CAST(\"DEPT\".\"DEPTNO\" + 1 AS TINYINT),\n"
+ + "LOWER(\"DEPT\".\"DNAME\"),\n"
+ + "UPPER(\"DEPT\".\"LOC\")";
+ sql(sql1)
+ .schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
+ .ok(expected1);
+
+ // without insert columns
+ final String sql2 = "merge into \"DEPT\" as \"t\"\n"
+ + "using \"DEPT\" as \"s\"\n"
+ + "on \"t\".\"DEPTNO\" = \"s\".\"DEPTNO\"\n"
+ + "when matched then\n"
+ + "update set \"DNAME\" = \"s\".\"DNAME\"\n"
+ + "when not matched then insert\n"
+ + "values (\"s\".\"DEPTNO\" + 1, lower(\"s\".\"DNAME\"),
upper(\"s\".\"LOC\"))";
+ final String expected2 = expected1;
+ sql(sql2)
+ .schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
+ .ok(expected2);
+
+ // reorder insert columns
+ final String sql3 = "merge into \"DEPT\" as \"t\"\n"
+ + "using \"DEPT\" as \"s\"\n"
+ + "on \"t\".\"DEPTNO\" = \"s\".\"DEPTNO\"\n"
+ + "when matched then\n"
+ + "update set \"DNAME\" = \"s\".\"DNAME\"\n"
+ + "when not matched then\n"
+ + "insert (DEPTNO, LOC, DNAME)\n"
+ + "values (\"s\".\"DEPTNO\" + 1, lower(\"s\".\"DNAME\"), 'abc')";
+ final String expected3 = "MERGE INTO \"SCOTT\".\"DEPT\" AS \"DEPT0\"\n"
+ + "USING \"SCOTT\".\"DEPT\"\n"
+ + "ON \"DEPT\".\"DEPTNO\" = \"DEPT0\".\"DEPTNO\"\n"
+ + "WHEN MATCHED THEN UPDATE SET \"DNAME\" = \"DEPT\".\"DNAME\"\n"
+ + "WHEN NOT MATCHED THEN INSERT (\"DEPTNO\", \"DNAME\", \"LOC\") "
+ + "VALUES CAST(\"DEPT\".\"DEPTNO\" + 1 AS TINYINT),\n"
+ + "'abc',\n"
+ + "LOWER(\"DEPT\".\"DNAME\")";
+ sql(sql3)
+ .schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
+ .ok(expected3);
+
+ // without WHEN NOT MATCHED THEN
+ final String sql4 = "merge into \"DEPT\" as \"t\"\n"
+ + "using \"DEPT\" as \"s\"\n"
+ + "on \"t\".\"DEPTNO\" = \"s\".\"DEPTNO\"\n"
+ + "when matched then\n"
+ + "update set \"DNAME\" = \"s\".\"DNAME\"";
+ final String expected4 = "MERGE INTO \"SCOTT\".\"DEPT\" AS \"DEPT0\"\n"
+ + "USING \"SCOTT\".\"DEPT\"\n"
+ + "ON \"DEPT\".\"DEPTNO\" = \"DEPT0\".\"DEPTNO\"\n"
+ + "WHEN MATCHED THEN UPDATE SET \"DNAME\" = \"DEPT\".\"DNAME\"";
+ sql(sql4)
+ .schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
+ .ok(expected4);
+
+ // without WHEN MATCHED THEN
+ final String sql5 = "merge into \"DEPT\" as \"t\"\n"
+ + "using \"DEPT\" as \"s\"\n"
+ + "on \"t\".\"DEPTNO\" = \"s\".\"DEPTNO\"\n"
+ + "when not matched then\n"
+ + "insert (DEPTNO, DNAME, LOC)\n"
+ + "values (\"s\".\"DEPTNO\" + 1, lower(\"s\".\"DNAME\"),
upper(\"s\".\"LOC\"))";
+ final String expected5 = "MERGE INTO \"SCOTT\".\"DEPT\" AS \"DEPT0\"\n"
+ + "USING \"SCOTT\".\"DEPT\"\n"
+ + "ON \"DEPT\".\"DEPTNO\" = \"DEPT0\".\"DEPTNO\"\n"
+ + "WHEN NOT MATCHED THEN INSERT (\"DEPTNO\", \"DNAME\", \"LOC\") "
+ + "VALUES CAST(\"DEPT\".\"DEPTNO\" + 1 AS TINYINT),\n"
+ + "LOWER(\"DEPT\".\"DNAME\"),\n"
+ + "UPPER(\"DEPT\".\"LOC\")";
+ sql(sql5)
+ .schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
+ .ok(expected5);
+
+ // using query
+ final String sql6 = "merge into \"DEPT\" as \"t\"\n"
+ + "using (select * from \"DEPT\" where \"DEPTNO\" <> 5) as \"s\"\n"
+ + "on \"t\".\"DEPTNO\" = \"s\".\"DEPTNO\"\n"
+ + "when not matched then\n"
Review Comment:
Can you add it `WHEN MATCHED THEN`?
##########
core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java:
##########
@@ -7027,6 +7027,136 @@ private void checkLiteral2(String expression, String
expected) {
.ok(expected);
}
+ @Test void testMerge() {
+ final String sql1 = "merge into \"DEPT\" as \"t\"\n"
+ + "using \"DEPT\" as \"s\"\n"
+ + "on \"t\".\"DEPTNO\" = \"s\".\"DEPTNO\"\n"
+ + "when matched then\n"
+ + "update set \"DNAME\" = \"s\".\"DNAME\"\n"
+ + "when not matched then\n"
+ + "insert (DEPTNO, DNAME, LOC)\n"
+ + "values (\"s\".\"DEPTNO\" + 1, lower(\"s\".\"DNAME\"),
upper(\"s\".\"LOC\"))";
+ final String expected1 = "MERGE INTO \"SCOTT\".\"DEPT\" AS \"DEPT0\"\n"
+ + "USING \"SCOTT\".\"DEPT\"\n"
+ + "ON \"DEPT\".\"DEPTNO\" = \"DEPT0\".\"DEPTNO\"\n"
+ + "WHEN MATCHED THEN UPDATE SET \"DNAME\" = \"DEPT\".\"DNAME\"\n"
+ + "WHEN NOT MATCHED THEN INSERT (\"DEPTNO\", \"DNAME\", \"LOC\") "
+ + "VALUES CAST(\"DEPT\".\"DEPTNO\" + 1 AS TINYINT),\n"
+ + "LOWER(\"DEPT\".\"DNAME\"),\n"
+ + "UPPER(\"DEPT\".\"LOC\")";
+ sql(sql1)
+ .schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
+ .ok(expected1);
+
+ // without insert columns
+ final String sql2 = "merge into \"DEPT\" as \"t\"\n"
+ + "using \"DEPT\" as \"s\"\n"
+ + "on \"t\".\"DEPTNO\" = \"s\".\"DEPTNO\"\n"
+ + "when matched then\n"
+ + "update set \"DNAME\" = \"s\".\"DNAME\"\n"
+ + "when not matched then insert\n"
+ + "values (\"s\".\"DEPTNO\" + 1, lower(\"s\".\"DNAME\"),
upper(\"s\".\"LOC\"))";
+ final String expected2 = expected1;
+ sql(sql2)
+ .schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
+ .ok(expected2);
+
+ // reorder insert columns
+ final String sql3 = "merge into \"DEPT\" as \"t\"\n"
+ + "using \"DEPT\" as \"s\"\n"
+ + "on \"t\".\"DEPTNO\" = \"s\".\"DEPTNO\"\n"
+ + "when matched then\n"
+ + "update set \"DNAME\" = \"s\".\"DNAME\"\n"
+ + "when not matched then\n"
+ + "insert (DEPTNO, LOC, DNAME)\n"
+ + "values (\"s\".\"DEPTNO\" + 1, lower(\"s\".\"DNAME\"), 'abc')";
+ final String expected3 = "MERGE INTO \"SCOTT\".\"DEPT\" AS \"DEPT0\"\n"
+ + "USING \"SCOTT\".\"DEPT\"\n"
+ + "ON \"DEPT\".\"DEPTNO\" = \"DEPT0\".\"DEPTNO\"\n"
+ + "WHEN MATCHED THEN UPDATE SET \"DNAME\" = \"DEPT\".\"DNAME\"\n"
+ + "WHEN NOT MATCHED THEN INSERT (\"DEPTNO\", \"DNAME\", \"LOC\") "
+ + "VALUES CAST(\"DEPT\".\"DEPTNO\" + 1 AS TINYINT),\n"
+ + "'abc',\n"
+ + "LOWER(\"DEPT\".\"DNAME\")";
+ sql(sql3)
+ .schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
+ .ok(expected3);
+
+ // without WHEN NOT MATCHED THEN
+ final String sql4 = "merge into \"DEPT\" as \"t\"\n"
+ + "using \"DEPT\" as \"s\"\n"
+ + "on \"t\".\"DEPTNO\" = \"s\".\"DEPTNO\"\n"
+ + "when matched then\n"
+ + "update set \"DNAME\" = \"s\".\"DNAME\"";
+ final String expected4 = "MERGE INTO \"SCOTT\".\"DEPT\" AS \"DEPT0\"\n"
+ + "USING \"SCOTT\".\"DEPT\"\n"
+ + "ON \"DEPT\".\"DEPTNO\" = \"DEPT0\".\"DEPTNO\"\n"
+ + "WHEN MATCHED THEN UPDATE SET \"DNAME\" = \"DEPT\".\"DNAME\"";
+ sql(sql4)
+ .schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
+ .ok(expected4);
+
+ // without WHEN MATCHED THEN
+ final String sql5 = "merge into \"DEPT\" as \"t\"\n"
+ + "using \"DEPT\" as \"s\"\n"
+ + "on \"t\".\"DEPTNO\" = \"s\".\"DEPTNO\"\n"
+ + "when not matched then\n"
+ + "insert (DEPTNO, DNAME, LOC)\n"
+ + "values (\"s\".\"DEPTNO\" + 1, lower(\"s\".\"DNAME\"),
upper(\"s\".\"LOC\"))";
+ final String expected5 = "MERGE INTO \"SCOTT\".\"DEPT\" AS \"DEPT0\"\n"
+ + "USING \"SCOTT\".\"DEPT\"\n"
+ + "ON \"DEPT\".\"DEPTNO\" = \"DEPT0\".\"DEPTNO\"\n"
+ + "WHEN NOT MATCHED THEN INSERT (\"DEPTNO\", \"DNAME\", \"LOC\") "
+ + "VALUES CAST(\"DEPT\".\"DEPTNO\" + 1 AS TINYINT),\n"
+ + "LOWER(\"DEPT\".\"DNAME\"),\n"
+ + "UPPER(\"DEPT\".\"LOC\")";
+ sql(sql5)
+ .schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
+ .ok(expected5);
+
+ // using query
+ final String sql6 = "merge into \"DEPT\" as \"t\"\n"
+ + "using (select * from \"DEPT\" where \"DEPTNO\" <> 5) as \"s\"\n"
+ + "on \"t\".\"DEPTNO\" = \"s\".\"DEPTNO\"\n"
+ + "when not matched then\n"
Review Comment:
Can you add `WHEN MATCHED THEN`?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
