You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by "macroguo-ghy (via GitHub)" <gi...@apache.org> on 2023/08/27 14:41:52 UTC
[GitHub] [calcite] macroguo-ghy opened a new pull request, #3397: [CALCITE-5836] Implement Rel2Sql for MERGE
macroguo-ghy opened a new pull request, #3397:
URL: https://github.com/apache/calcite/pull/3397
Support MERGE INTO cluase for JDBC adapter
--
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: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [calcite] sonarcloud[bot] commented on pull request #3397: [CALCITE-5836] Implement Rel2Sql for MERGE
Posted by "sonarcloud[bot] (via GitHub)" <gi...@apache.org>.
sonarcloud[bot] commented on PR #3397:
URL: https://github.com/apache/calcite/pull/3397#issuecomment-1709406924
Kudos, SonarCloud Quality Gate passed! [](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3397)
[](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=BUG) [](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=BUG) [0 Bugs](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=BUG)
[](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=VULNERABILITY) [](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=VULNERABILITY) [0 Vulnerabilities](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=VULNERABILITY)
[](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=3397&resolved=false&types=SECURITY_HOTSPOT) [](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=3397&resolved=false&types=SECURITY_HOTSPOT) [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=3397&resolved=false&types=SECURITY_HOTSPOT)
[](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=CODE_SMELL) [](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=CODE_SMELL) [7 Code Smells](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=CODE_SMELL)
[](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3397&metric=new_coverage&view=list) [97.8% Coverage](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3397&metric=new_coverage&view=list)
[](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3397&metric=new_duplicated_lines_density&view=list) [0.0% Duplication](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3397&metric=new_duplicated_lines_density&view=list)
--
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: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [calcite] sonarcloud[bot] commented on pull request #3397: [CALCITE-5836] Implement Rel2Sql for MERGE
Posted by "sonarcloud[bot] (via GitHub)" <gi...@apache.org>.
sonarcloud[bot] commented on PR #3397:
URL: https://github.com/apache/calcite/pull/3397#issuecomment-1694928447
Kudos, SonarCloud Quality Gate passed! [](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3397)
[](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=BUG) [](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=BUG) [0 Bugs](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=BUG)
[](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=VULNERABILITY) [](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=VULNERABILITY) [0 Vulnerabilities](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=VULNERABILITY)
[](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=3397&resolved=false&types=SECURITY_HOTSPOT) [](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=3397&resolved=false&types=SECURITY_HOTSPOT) [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=3397&resolved=false&types=SECURITY_HOTSPOT)
[](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=CODE_SMELL) [](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=CODE_SMELL) [7 Code Smells](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=CODE_SMELL)
[](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3397&metric=new_coverage&view=list) [97.8% Coverage](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3397&metric=new_coverage&view=list)
[](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3397&metric=new_duplicated_lines_density&view=list) [0.0% Duplication](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3397&metric=new_duplicated_lines_density&view=list)
--
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: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [calcite] JiajunBernoulli merged pull request #3397: [CALCITE-5836] Implement Rel2Sql for MERGE
Posted by "JiajunBernoulli (via GitHub)" <gi...@apache.org>.
JiajunBernoulli merged PR #3397:
URL: https://github.com/apache/calcite/pull/3397
--
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: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [calcite] JiajunBernoulli commented on pull request #3397: [CALCITE-5836] Implement Rel2Sql for MERGE
Posted by "JiajunBernoulli (via GitHub)" <gi...@apache.org>.
JiajunBernoulli commented on PR #3397:
URL: https://github.com/apache/calcite/pull/3397#issuecomment-1711491610
Please squash your commits.
--
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: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [calcite] JiajunBernoulli commented on a diff in pull request #3397: [CALCITE-5836] Implement Rel2Sql for MERGE
Posted by "JiajunBernoulli (via GitHub)" <gi...@apache.org>.
JiajunBernoulli commented on code in PR #3397:
URL: https://github.com/apache/calcite/pull/3397#discussion_r1319723668
##########
core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java:
##########
@@ -1084,7 +1085,62 @@ public Result visit(TableModify modify) {
return result(sqlDelete, input.clauses, modify, null);
}
- case MERGE:
+ case MERGE: {
+ final Result input = visitInput(modify, 0);
+ final SqlSelect select = input.asSelect();
+ // When querying with both the `WHEN MATCHED THEN UPDATE` and
+ // `WHEN NOT MATCHED THEN INSERT` clauses, the selectList consists of three parts:
+ // the insert expression, the target table reference, and the update expression.
+ // When querying with the `WHEN MATCHED THEN UPDATE` clause, the selectList will not
+ // include the update expression.
+ // However, when querying with the `WHEN NOT MATCHED THEN INSERT` clause,
+ // the expression list will only contain the insert expression.
+ final SqlNodeList selectList = SqlUtil.stripListAs(select.getSelectList());
+ final SqlJoin join = requireNonNull((SqlJoin) select.getFrom());
+ final SqlNode condition = requireNonNull(join.getCondition());
+ final SqlNode source = join.getLeft();
+
+ SqlUpdate update = null;
+ List<String> updateColumnList =
Review Comment:
Some variables can be `final`, please use final as much as possible.
--
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: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [calcite] sonarcloud[bot] commented on pull request #3397: [CALCITE-5836] Implement Rel2Sql for MERGE
Posted by "sonarcloud[bot] (via GitHub)" <gi...@apache.org>.
sonarcloud[bot] commented on PR #3397:
URL: https://github.com/apache/calcite/pull/3397#issuecomment-1712875423
Kudos, SonarCloud Quality Gate passed! [](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3397)
[](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=BUG) [](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=BUG) [0 Bugs](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=BUG)
[](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=VULNERABILITY) [](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=VULNERABILITY) [0 Vulnerabilities](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=VULNERABILITY)
[](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=3397&resolved=false&types=SECURITY_HOTSPOT) [](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=3397&resolved=false&types=SECURITY_HOTSPOT) [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=3397&resolved=false&types=SECURITY_HOTSPOT)
[](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=CODE_SMELL) [](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=CODE_SMELL) [7 Code Smells](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=CODE_SMELL)
[](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3397&metric=new_coverage&view=list) [97.8% Coverage](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3397&metric=new_coverage&view=list)
[](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3397&metric=new_duplicated_lines_density&view=list) [0.0% Duplication](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3397&metric=new_duplicated_lines_density&view=list)
--
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: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [calcite] JiajunBernoulli commented on pull request #3397: [CALCITE-5836] Implement Rel2Sql for MERGE
Posted by "JiajunBernoulli (via GitHub)" <gi...@apache.org>.
JiajunBernoulli commented on PR #3397:
URL: https://github.com/apache/calcite/pull/3397#issuecomment-1720386367
Close #3310
--
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: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [calcite] sonarcloud[bot] commented on pull request #3397: [CALCITE-5836] Implement Rel2Sql for MERGE
Posted by "sonarcloud[bot] (via GitHub)" <gi...@apache.org>.
sonarcloud[bot] commented on PR #3397:
URL: https://github.com/apache/calcite/pull/3397#issuecomment-1694701399
Kudos, SonarCloud Quality Gate passed! [](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3397)
[](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=BUG) [](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=BUG) [0 Bugs](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=BUG)
[](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=VULNERABILITY) [](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=VULNERABILITY) [0 Vulnerabilities](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=VULNERABILITY)
[](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=3397&resolved=false&types=SECURITY_HOTSPOT) [](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=3397&resolved=false&types=SECURITY_HOTSPOT) [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=3397&resolved=false&types=SECURITY_HOTSPOT)
[](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=CODE_SMELL) [](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=CODE_SMELL) [7 Code Smells](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=CODE_SMELL)
[](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3397&metric=new_coverage&view=list) [97.8% Coverage](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3397&metric=new_coverage&view=list)
[](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3397&metric=new_duplicated_lines_density&view=list) [0.0% Duplication](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3397&metric=new_duplicated_lines_density&view=list)
--
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: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [calcite] JiajunBernoulli commented on pull request #3397: [CALCITE-5836] Implement Rel2Sql for MERGE
Posted by "JiajunBernoulli (via GitHub)" <gi...@apache.org>.
JiajunBernoulli commented on PR #3397:
URL: https://github.com/apache/calcite/pull/3397#issuecomment-1711491000
If there are no other suggestions, I will merge it tomorrow.
--
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: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [calcite] JiajunBernoulli commented on a diff in pull request #3397: [CALCITE-5836] Implement Rel2Sql for MERGE
Posted by "JiajunBernoulli (via GitHub)" <gi...@apache.org>.
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: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [calcite] sonarcloud[bot] commented on pull request #3397: [CALCITE-5836] Implement Rel2Sql for MERGE
Posted by "sonarcloud[bot] (via GitHub)" <gi...@apache.org>.
sonarcloud[bot] commented on PR #3397:
URL: https://github.com/apache/calcite/pull/3397#issuecomment-1712771007
Kudos, SonarCloud Quality Gate passed! [](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3397)
[](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=BUG) [](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=BUG) [0 Bugs](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=BUG)
[](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=VULNERABILITY) [](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=VULNERABILITY) [0 Vulnerabilities](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=VULNERABILITY)
[](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=3397&resolved=false&types=SECURITY_HOTSPOT) [](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=3397&resolved=false&types=SECURITY_HOTSPOT) [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=3397&resolved=false&types=SECURITY_HOTSPOT)
[](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=CODE_SMELL) [](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=CODE_SMELL) [7 Code Smells](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=CODE_SMELL)
[](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3397&metric=new_coverage&view=list) [97.8% Coverage](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3397&metric=new_coverage&view=list)
[](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3397&metric=new_duplicated_lines_density&view=list) [0.0% Duplication](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3397&metric=new_duplicated_lines_density&view=list)
--
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: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [calcite] macroguo-ghy commented on a diff in pull request #3397: [CALCITE-5836] Implement Rel2Sql for MERGE
Posted by "macroguo-ghy (via GitHub)" <gi...@apache.org>.
macroguo-ghy commented on code in PR #3397:
URL: https://github.com/apache/calcite/pull/3397#discussion_r1320783812
##########
core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java:
##########
@@ -1084,7 +1085,62 @@ public Result visit(TableModify modify) {
return result(sqlDelete, input.clauses, modify, null);
}
- case MERGE:
+ case MERGE: {
+ final Result input = visitInput(modify, 0);
+ final SqlSelect select = input.asSelect();
+ // When querying with both the `WHEN MATCHED THEN UPDATE` and
+ // `WHEN NOT MATCHED THEN INSERT` clauses, the selectList consists of three parts:
+ // the insert expression, the target table reference, and the update expression.
+ // When querying with the `WHEN MATCHED THEN UPDATE` clause, the selectList will not
+ // include the update expression.
+ // However, when querying with the `WHEN NOT MATCHED THEN INSERT` clause,
+ // the expression list will only contain the insert expression.
+ final SqlNodeList selectList = SqlUtil.stripListAs(select.getSelectList());
+ final SqlJoin join = requireNonNull((SqlJoin) select.getFrom());
+ final SqlNode condition = requireNonNull(join.getCondition());
+ final SqlNode source = join.getLeft();
+
+ SqlUpdate update = null;
+ List<String> updateColumnList =
Review Comment:
I have fixed this and squashed the commits.
--
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: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [calcite] macroguo-ghy commented on pull request #3397: [CALCITE-5836] Implement Rel2Sql for MERGE
Posted by "macroguo-ghy (via GitHub)" <gi...@apache.org>.
macroguo-ghy commented on PR #3397:
URL: https://github.com/apache/calcite/pull/3397#issuecomment-1711174100
Hi @JiajunBernoulli, Do you want to continue with the code review? If you think this PR is ok, do you consider merging it?
--
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: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [calcite] sonarcloud[bot] commented on pull request #3397: [CALCITE-5836] Implement Rel2Sql for MERGE
Posted by "sonarcloud[bot] (via GitHub)" <gi...@apache.org>.
sonarcloud[bot] commented on PR #3397:
URL: https://github.com/apache/calcite/pull/3397#issuecomment-1694690511
Kudos, SonarCloud Quality Gate passed! [](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3397)
[](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=BUG) [](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=BUG) [0 Bugs](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=BUG)
[](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=VULNERABILITY) [](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=VULNERABILITY) [0 Vulnerabilities](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=VULNERABILITY)
[](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=3397&resolved=false&types=SECURITY_HOTSPOT) [](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=3397&resolved=false&types=SECURITY_HOTSPOT) [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=3397&resolved=false&types=SECURITY_HOTSPOT)
[](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=CODE_SMELL) [](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=CODE_SMELL) [5 Code Smells](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3397&resolved=false&types=CODE_SMELL)
[](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3397&metric=new_coverage&view=list) [95.2% Coverage](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3397&metric=new_coverage&view=list)
[](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3397&metric=new_duplicated_lines_density&view=list) [0.0% Duplication](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3397&metric=new_duplicated_lines_density&view=list)
--
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: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [calcite] JiajunBernoulli commented on a diff in pull request #3397: [CALCITE-5836] Implement Rel2Sql for MERGE
Posted by "JiajunBernoulli (via GitHub)" <gi...@apache.org>.
JiajunBernoulli commented on code in PR #3397:
URL: https://github.com/apache/calcite/pull/3397#discussion_r1319723668
##########
core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java:
##########
@@ -1084,7 +1085,62 @@ public Result visit(TableModify modify) {
return result(sqlDelete, input.clauses, modify, null);
}
- case MERGE:
+ case MERGE: {
+ final Result input = visitInput(modify, 0);
+ final SqlSelect select = input.asSelect();
+ // When querying with both the `WHEN MATCHED THEN UPDATE` and
+ // `WHEN NOT MATCHED THEN INSERT` clauses, the selectList consists of three parts:
+ // the insert expression, the target table reference, and the update expression.
+ // When querying with the `WHEN MATCHED THEN UPDATE` clause, the selectList will not
+ // include the update expression.
+ // However, when querying with the `WHEN NOT MATCHED THEN INSERT` clause,
+ // the expression list will only contain the insert expression.
+ final SqlNodeList selectList = SqlUtil.stripListAs(select.getSelectList());
+ final SqlJoin join = requireNonNull((SqlJoin) select.getFrom());
+ final SqlNode condition = requireNonNull(join.getCondition());
+ final SqlNode source = join.getLeft();
+
+ SqlUpdate update = null;
+ List<String> updateColumnList =
Review Comment:
Some variables can be `final`, please use `final` as much as possible.
--
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: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [calcite] macroguo-ghy commented on pull request #3397: [CALCITE-5836] Implement Rel2Sql for MERGE
Posted by "macroguo-ghy (via GitHub)" <gi...@apache.org>.
macroguo-ghy commented on PR #3397:
URL: https://github.com/apache/calcite/pull/3397#issuecomment-1709395195
I made a rebase to solve problem of hundred of commits in this PR.
--
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: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org