This is an automated email from the ASF dual-hosted git repository.
xiong pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new 8a96095a64 [CALCITE-6401] JDBC adapter cannot push down JOIN with
condition includes IS TRUE、IS NULL、Dynamic parameter、CAST、Literal comparison
8a96095a64 is described below
commit 8a96095a64bc1cc955438d219d5f1fbcbc5762b7
Author: Xiong Duan <[email protected]>
AuthorDate: Thu Jul 4 22:26:29 2024 +0800
[CALCITE-6401] JDBC adapter cannot push down JOIN with condition includes
IS TRUE、IS NULL、Dynamic parameter、CAST、Literal comparison
---
.../org/apache/calcite/adapter/jdbc/JdbcRules.java | 30 ++--
.../org/apache/calcite/test/JdbcAdapterTest.java | 158 +++++++++++++++++++++
2 files changed, 173 insertions(+), 15 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcRules.java
b/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcRules.java
index 86503ca492..846dc9c4e4 100644
--- a/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcRules.java
+++ b/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcRules.java
@@ -54,7 +54,6 @@ import org.apache.calcite.rel.metadata.RelMetadataQuery;
import org.apache.calcite.rel.rel2sql.SqlImplementor;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rex.RexCall;
-import org.apache.calcite.rex.RexInputRef;
import org.apache.calcite.rex.RexLiteral;
import org.apache.calcite.rex.RexNode;
import org.apache.calcite.rex.RexProgram;
@@ -338,33 +337,34 @@ public class JdbcRules {
private static boolean canJoinOnCondition(RexNode node) {
final List<RexNode> operands;
switch (node.getKind()) {
+ case DYNAMIC_PARAM:
+ case INPUT_REF:
case LITERAL:
// literal on a join condition would be TRUE or FALSE
return true;
case AND:
case OR:
- operands = ((RexCall) node).getOperands();
- for (RexNode operand : operands) {
- if (!canJoinOnCondition(operand)) {
- return false;
- }
- }
- return true;
-
+ case IS_NULL:
+ case IS_NOT_NULL:
+ case IS_TRUE:
+ case IS_NOT_TRUE:
+ case IS_FALSE:
+ case IS_NOT_FALSE:
case EQUALS:
- case IS_NOT_DISTINCT_FROM:
case NOT_EQUALS:
case GREATER_THAN:
case GREATER_THAN_OR_EQUAL:
case LESS_THAN:
case LESS_THAN_OR_EQUAL:
+ case IS_NOT_DISTINCT_FROM:
+ case CAST:
operands = ((RexCall) node).getOperands();
- if ((operands.get(0) instanceof RexInputRef)
- && (operands.get(1) instanceof RexInputRef)) {
- return true;
+ for (RexNode operand : operands) {
+ if (!canJoinOnCondition(operand)) {
+ return false;
+ }
}
- // fall through
-
+ return true;
default:
return false;
}
diff --git a/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
b/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
index 8cfee5377e..d1c62a4c85 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
@@ -206,6 +206,164 @@ class JdbcAdapterTest {
+ "ORDER BY \"EMPNO\" NULLS LAST");
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6401";>[CALCITE-6401]
+ * JDBC adapter cannot push down JOIN with condition
+ * includes IS TRUE、IS NULL、Dynamic parameter、CAST、Literal comparison</a>. */
+ @Test void testJoinConditionPushDownIsTrue() {
+ CalciteAssert.model(JdbcTest.SCOTT_MODEL)
+ .query("select emp1.empno\n"
+ + "from scott.emp as emp1 join scott.emp as emp2 on\n"
+ + "(emp1.empno = emp2.empno) is true\n"
+ + " and (emp1.ename = emp2.ename) is not true")
+ .explainContains("PLAN=JdbcToEnumerableConverter\n"
+ + " JdbcProject(EMPNO=[$0])\n"
+ + " JdbcJoin(condition=[AND(=($0, $2), IS NOT TRUE(=($1,
$3)))], joinType=[inner])\n"
+ + " JdbcProject(EMPNO=[$0], ENAME=[$1])\n"
+ + " JdbcTableScan(table=[[SCOTT, EMP]])\n"
+ + " JdbcProject(EMPNO=[$0], ENAME=[$1])\n"
+ + " JdbcTableScan(table=[[SCOTT, EMP]])\n\n")
+ .runs()
+ .enable(CalciteAssert.DB == CalciteAssert.DatabaseInstance.HSQLDB)
+ .planHasSql("SELECT \"t\".\"EMPNO\"\n"
+ + "FROM (SELECT \"EMPNO\", \"ENAME\"\n"
+ + "FROM \"SCOTT\".\"EMP\") AS \"t\"\n"
+ + "INNER JOIN (SELECT \"EMPNO\", \"ENAME\"\n"
+ + "FROM \"SCOTT\".\"EMP\") AS \"t0\" ON \"t\".\"EMPNO\" =
\"t0\".\"EMPNO\" AND \"t\".\"ENAME\" = \"t0\".\"ENAME\" IS NOT TRUE");
+ }
+
+ @Test void testJoinConditionPushDownIsFalse() {
+ CalciteAssert.model(JdbcTest.SCOTT_MODEL)
+ .query("select emp1.empno\n"
+ + "from scott.emp as emp1 join scott.emp as emp2 on\n"
+ + "(emp1.empno = emp2.empno) is false\n"
+ + " and (emp1.ename = emp2.ename) is not false")
+ .explainContains("PLAN=JdbcToEnumerableConverter\n"
+ + " JdbcProject(EMPNO=[$0])\n"
+ + " JdbcJoin(condition=[AND(<>($0, $2), IS NOT FALSE(=($1,
$3)))], joinType=[inner])\n"
+ + " JdbcProject(EMPNO=[$0], ENAME=[$1])\n"
+ + " JdbcTableScan(table=[[SCOTT, EMP]])\n"
+ + " JdbcProject(EMPNO=[$0], ENAME=[$1])\n"
+ + " JdbcTableScan(table=[[SCOTT, EMP]])\n\n")
+ .runs()
+ .enable(CalciteAssert.DB == CalciteAssert.DatabaseInstance.HSQLDB)
+ .planHasSql("SELECT \"t\".\"EMPNO\"\n"
+ + "FROM (SELECT \"EMPNO\", \"ENAME\"\n"
+ + "FROM \"SCOTT\".\"EMP\") AS \"t\"\n"
+ + "INNER JOIN (SELECT \"EMPNO\", \"ENAME\"\n"
+ + "FROM \"SCOTT\".\"EMP\") AS \"t0\" ON \"t\".\"EMPNO\" <>
\"t0\".\"EMPNO\" AND \"t\".\"ENAME\" = \"t0\".\"ENAME\" IS NOT FALSE");
+ }
+
+ @Test void testJoinConditionPushDownIsNull() {
+ CalciteAssert.model(JdbcTest.SCOTT_MODEL)
+ .query("select emp1.empno\n"
+ + "from scott.emp as emp1 join scott.emp as emp2 on\n"
+ + "(emp1.empno = emp2.empno or emp1.ename is null)\n"
+ + " and (emp1.ename = emp2.ename or emp2.empno is null)")
+ .explainContains("PLAN=JdbcToEnumerableConverter\n"
+ + " JdbcProject(EMPNO=[$0])\n"
+ + " JdbcJoin(condition=[AND(OR(IS NULL($1), =($0, $2)), =($1,
$3))], joinType=[inner])\n"
+ + " JdbcProject(EMPNO=[$0], ENAME=[$1])\n"
+ + " JdbcTableScan(table=[[SCOTT, EMP]])\n"
+ + " JdbcProject(EMPNO=[$0], ENAME=[$1])\n"
+ + " JdbcTableScan(table=[[SCOTT, EMP]])\n\n")
+ .runs()
+ .enable(CalciteAssert.DB == CalciteAssert.DatabaseInstance.HSQLDB)
+ .planHasSql("SELECT \"t\".\"EMPNO\"\n"
+ + "FROM (SELECT \"EMPNO\", \"ENAME\"\n"
+ + "FROM \"SCOTT\".\"EMP\") AS \"t\"\n"
+ + "INNER JOIN (SELECT \"EMPNO\", \"ENAME\"\n"
+ + "FROM \"SCOTT\".\"EMP\") AS \"t0\" ON (\"t\".\"ENAME\" IS NULL
OR \"t\".\"EMPNO\" = \"t0\".\"EMPNO\") AND \"t\".\"ENAME\" = \"t0\".\"ENAME\"");
+ }
+
+ @Test void testJoinConditionPushDownIsNotNull() {
+ CalciteAssert.model(JdbcTest.SCOTT_MODEL)
+ .query("select emp1.empno\n"
+ + "from scott.emp as emp1 join scott.emp as emp2 on\n"
+ + "(emp1.empno = emp2.empno and emp1.ename is not null)\n"
+ + " or (emp1.ename = emp2.ename and emp2.empno is not null)")
+ .explainContains("PLAN=JdbcToEnumerableConverter\n"
+ + " JdbcProject(EMPNO=[$0])\n"
+ + " JdbcJoin(condition=[OR(AND(=($0, $2), IS NOT NULL($1)),
=($1, $3))], joinType=[inner])\n"
+ + " JdbcProject(EMPNO=[$0], ENAME=[$1])\n"
+ + " JdbcTableScan(table=[[SCOTT, EMP]])\n"
+ + " JdbcProject(EMPNO=[$0], ENAME=[$1])\n"
+ + " JdbcTableScan(table=[[SCOTT, EMP]])\n\n")
+ .runs()
+ .enable(CalciteAssert.DB == CalciteAssert.DatabaseInstance.HSQLDB)
+ .planHasSql("SELECT \"t\".\"EMPNO\"\n"
+ + "FROM (SELECT \"EMPNO\", \"ENAME\"\n"
+ + "FROM \"SCOTT\".\"EMP\") AS \"t\"\n"
+ + "INNER JOIN (SELECT \"EMPNO\", \"ENAME\"\n"
+ + "FROM \"SCOTT\".\"EMP\") AS \"t0\" ON \"t\".\"EMPNO\" =
\"t0\".\"EMPNO\" AND \"t\".\"ENAME\" IS NOT NULL OR \"t\".\"ENAME\" =
\"t0\".\"ENAME\"");
+ }
+
+ @Test void testJoinConditionPushDownLiteral() {
+ CalciteAssert.model(JdbcTest.SCOTT_MODEL)
+ .query("select emp1.empno\n"
+ + "from scott.emp as emp1 join scott.emp as emp2 on\n"
+ + "(emp1.empno = emp2.empno and emp1.ename = 'empename') or\n"
+ + "(emp1.ename = emp2.ename and emp2.empno = 5)")
+ .explainContains("PLAN=JdbcToEnumerableConverter\n"
+ + " JdbcProject(EMPNO=[$0])\n"
+ + " JdbcJoin(condition=[OR(AND(=($0, $2), =($1, 'empename')),
AND(=($1, $3), =(CAST($2):INTEGER NOT NULL, 5)))], joinType=[inner])\n"
+ + " JdbcProject(EMPNO=[$0], ENAME=[$1])\n"
+ + " JdbcTableScan(table=[[SCOTT, EMP]])\n"
+ + " JdbcProject(EMPNO=[$0], ENAME=[$1])\n"
+ + " JdbcTableScan(table=[[SCOTT, EMP]])\n\n")
+ .runs()
+ .enable(CalciteAssert.DB == CalciteAssert.DatabaseInstance.HSQLDB)
+ .planHasSql("SELECT \"t\".\"EMPNO\"\n"
+ + "FROM (SELECT \"EMPNO\", \"ENAME\"\nFROM \"SCOTT\".\"EMP\") AS
\"t\"\n"
+ + "INNER JOIN (SELECT \"EMPNO\", \"ENAME\"\n"
+ + "FROM \"SCOTT\".\"EMP\") AS \"t0\" ON \"t\".\"EMPNO\" =
\"t0\".\"EMPNO\" AND \"t\".\"ENAME\" = 'empename' OR \"t\".\"ENAME\" =
\"t0\".\"ENAME\" AND CAST(\"t0\".\"EMPNO\" AS INTEGER) = 5");
+ }
+
+ @Test void testJoinConditionPushDownCast() {
+ CalciteAssert.model(JdbcTest.SCOTT_MODEL)
+ .query("select emp1.empno\n"
+ + "from scott.emp as emp1 join scott.emp as emp2 on\n"
+ + "(emp1.empno = emp2.empno and emp1.ename = 'empename') or\n"
+ + "(emp1.ename = emp2.ename and emp2.empno = 5)")
+ .explainContains("PLAN=JdbcToEnumerableConverter\n"
+ + " JdbcProject(EMPNO=[$0])\n"
+ + " JdbcJoin(condition=[OR(AND(=($0, $2), =($1, 'empename')),
AND(=($1, $3), =(CAST($2):INTEGER NOT NULL, 5)))], joinType=[inner])\n"
+ + " JdbcProject(EMPNO=[$0], ENAME=[$1])\n"
+ + " JdbcTableScan(table=[[SCOTT, EMP]])\n"
+ + " JdbcProject(EMPNO=[$0], ENAME=[$1])\n"
+ + " JdbcTableScan(table=[[SCOTT, EMP]])\n\n")
+ .runs()
+ .enable(CalciteAssert.DB == CalciteAssert.DatabaseInstance.HSQLDB)
+ .planHasSql("SELECT \"t\".\"EMPNO\"\n"
+ + "FROM (SELECT \"EMPNO\", \"ENAME\"\n"
+ + "FROM \"SCOTT\".\"EMP\") AS \"t\"\n"
+ + "INNER JOIN (SELECT \"EMPNO\", \"ENAME\"\n"
+ + "FROM \"SCOTT\".\"EMP\") AS \"t0\" ON \"t\".\"EMPNO\" =
\"t0\".\"EMPNO\" AND \"t\".\"ENAME\" = 'empename' OR \"t\".\"ENAME\" =
\"t0\".\"ENAME\" AND CAST(\"t0\".\"EMPNO\" AS INTEGER) = 5");
+ }
+
+ @Test void testJoinConditionPushDownDynamicParam() {
+ CalciteAssert.model(JdbcTest.SCOTT_MODEL)
+ .query("select emp1.empno\n"
+ + "from scott.emp as emp1 join scott.emp as emp2 on\n"
+ + "(emp1.empno = emp2.empno and emp1.ename = 'empename') or\n"
+ + "(emp1.ename = emp2.ename and emp2.empno = ?)")
+ .consumesPreparedStatement(p -> p.setInt(1, 5))
+ .explainContains("PLAN=JdbcToEnumerableConverter\n"
+ + " JdbcProject(EMPNO=[$0])\n"
+ + " JdbcJoin(condition=[OR(AND(=($0, $2), =($1, 'empename')),
AND(=($1, $3), =($2, ?0)))], joinType=[inner])\n"
+ + " JdbcProject(EMPNO=[$0], ENAME=[$1])\n"
+ + " JdbcTableScan(table=[[SCOTT, EMP]])\n"
+ + " JdbcProject(EMPNO=[$0], ENAME=[$1])\n"
+ + " JdbcTableScan(table=[[SCOTT, EMP]])\n\n")
+ .runs()
+ .enable(CalciteAssert.DB == CalciteAssert.DatabaseInstance.HSQLDB)
+ .planHasSql("SELECT \"t\".\"EMPNO\"\n"
+ + "FROM (SELECT \"EMPNO\", \"ENAME\"\n"
+ + "FROM \"SCOTT\".\"EMP\") AS \"t\"\n"
+ + "INNER JOIN (SELECT \"EMPNO\", \"ENAME\"\n"
+ + "FROM \"SCOTT\".\"EMP\") AS \"t0\" ON \"t\".\"EMPNO\" =
\"t0\".\"EMPNO\" AND \"t\".\"ENAME\" = 'empename' OR \"t\".\"ENAME\" =
\"t0\".\"ENAME\" AND \"t0\".\"EMPNO\" = ?");
+ }
+
/** Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-3751";>[CALCITE-3751]
* JDBC adapter wrongly pushes ORDER BY into sub-query</a>. */
