You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@iotdb.apache.org by ca...@apache.org on 2024/12/09 10:23:23 UTC

(iotdb) branch beyyes/fix_mergesort_exist_null updated: support cross join with filter

This is an automated email from the ASF dual-hosted git repository.

caogaofei pushed a commit to branch beyyes/fix_mergesort_exist_null
in repository https://gitbox.apache.org/repos/asf/iotdb.git


The following commit(s) were added to refs/heads/beyyes/fix_mergesort_exist_null by this push:
     new 077b51338a1 support cross join with filter
077b51338a1 is described below

commit 077b51338a13c0744aeb37bd8d714a8f35e085cb
Author: Beyyes <cg...@foxmail.com>
AuthorDate: Mon Dec 9 18:14:47 2024 +0800

    support cross join with filter
---
 .../db/it/IoTDBMultiIDsWithAttributesTableIT.java  | 51 +++++++++++---
 .../relational/analyzer/StatementAnalyzer.java     | 78 ++++++++++------------
 .../plan/relational/planner/node/JoinNode.java     |  3 +-
 .../optimizations/PushPredicateIntoTableScan.java  |  3 +-
 .../plan/relational/analyzer/JoinTest.java         | 37 +---------
 5 files changed, 82 insertions(+), 90 deletions(-)

diff --git a/integration-test/src/test/java/org/apache/iotdb/relational/it/db/it/IoTDBMultiIDsWithAttributesTableIT.java b/integration-test/src/test/java/org/apache/iotdb/relational/it/db/it/IoTDBMultiIDsWithAttributesTableIT.java
index a02889a4777..5956055065c 100644
--- a/integration-test/src/test/java/org/apache/iotdb/relational/it/db/it/IoTDBMultiIDsWithAttributesTableIT.java
+++ b/integration-test/src/test/java/org/apache/iotdb/relational/it/db/it/IoTDBMultiIDsWithAttributesTableIT.java
@@ -34,7 +34,6 @@ import java.sql.Connection;
 import java.sql.Statement;
 import java.util.Arrays;
 
-import static org.apache.iotdb.db.it.utils.TestUtils.tableAssertTestFail;
 import static org.apache.iotdb.db.it.utils.TestUtils.tableResultSetEqualTest;
 import static org.junit.Assert.fail;
 
@@ -1383,7 +1382,7 @@ public class IoTDBMultiIDsWithAttributesTableIT {
   // ==================================================================
   // no filter
   @Test
-  public void innerJoinTest1() {
+  public void selfTimeColumnInnerJoinTest1() {
     String[] expectedHeader =
         new String[] {"time", "device", "level", "num", "device", "attr2", "num", "str"};
     String[] retArray =
@@ -1420,7 +1419,7 @@ public class IoTDBMultiIDsWithAttributesTableIT {
 
   // has filter
   @Test
-  public void innerJoinTest2() {
+  public void selfTimeColumnInnerJoinTest2() {
     String[] expectedHeader =
         new String[] {"time", "device", "level", "t1_num_add", "device", "attr2", "num", "str"};
     String[] retArray =
@@ -1510,7 +1509,7 @@ public class IoTDBMultiIDsWithAttributesTableIT {
 
   // no filter
   @Test
-  public void fullOuterJoinTest1() {
+  public void timeColumnFullOuterJoinTest1() {
     expectedHeader =
         new String[] {"time", "device", "level", "num", "device", "attr2", "num", "str"};
     retArray =
@@ -1682,7 +1681,7 @@ public class IoTDBMultiIDsWithAttributesTableIT {
 
   // has filter
   @Test
-  public void fullOuterJoinTest2() {
+  public void timeColumnFullOuterJoinTest2() {
     expectedHeader =
         new String[] {"time", "device", "level", "t1_num_add", "device", "attr2", "num", "str"};
     retArray =
@@ -1785,7 +1784,7 @@ public class IoTDBMultiIDsWithAttributesTableIT {
   }
 
   @Test
-  public void innerJoinTest() {
+  public void twoTableTimeColumnInnerJoinTest() {
     expectedHeader = new String[] {"time", "device1", "value1", "device2", "value2"};
     sql =
         "SELECT "
@@ -1826,9 +1825,43 @@ public class IoTDBMultiIDsWithAttributesTableIT {
     tableResultSetEqualTest(sql, expectedHeader, retArray, DATABASE_NAME);
 
     sql =
-        "select table1.s1 from table1 t1 join table2 t2 on t1.time = t2.time and t1.device = t2.device";
-    tableAssertTestFail(
-        sql, "701: Only support time column equi-join in current version", DATABASE_NAME);
+        "SELECT "
+            + "  t1.time, "
+            + "  t1.device as device1, "
+            + "  t1.value as value1, "
+            + "  t2.device as device2, "
+            + "  t2.value as value2 "
+            + "FROM "
+            + "  tableA t1 cross join tableB t2 "
+            + "where t1.time = t2.time and t1.device = t2.device order by t1.time";
+    tableResultSetEqualTest(sql, expectedHeader, retArray, DATABASE_NAME);
+
+    sql =
+        "SELECT "
+            + "  t1.time, "
+            + "  t1.device as device1, "
+            + "  t1.value as value1, "
+            + "  t2.device as device2, "
+            + "  t2.value as value2 "
+            + "FROM "
+            + "  tableA t1 JOIN tableB t2 "
+            + "ON t1.time = t2.time and t1.device = t2.device order by t1.time";
+    tableResultSetEqualTest(sql, expectedHeader, retArray, DATABASE_NAME);
+
+    expectedHeader = new String[] {"time", "device", "value1", "value2"};
+    sql =
+        "SELECT "
+            + "  time, device, "
+            + "  t1.value as value1, "
+            + "  t2.value as value2 "
+            + "FROM "
+            + "  tableA t1 JOIN tableB t2 "
+            + "USING(time, device) ORDER BY time";
+    retArray =
+        new String[] {
+          "2020-01-01T00:00:03.000Z,d1,3,30,", "2020-01-01T00:00:05.000Z,d2,5,50,",
+        };
+    tableResultSetEqualTest(sql, expectedHeader, retArray, DATABASE_NAME);
   }
 
   public static String[] buildHeaders(int length) {
diff --git a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/StatementAnalyzer.java b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/StatementAnalyzer.java
index fe3cb9bff43..0c87591d8ed 100644
--- a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/StatementAnalyzer.java
+++ b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/StatementAnalyzer.java
@@ -51,7 +51,6 @@ import org.apache.iotdb.db.queryengine.plan.relational.sql.ast.AllRows;
 import org.apache.iotdb.db.queryengine.plan.relational.sql.ast.AlterDB;
 import org.apache.iotdb.db.queryengine.plan.relational.sql.ast.AlterPipe;
 import org.apache.iotdb.db.queryengine.plan.relational.sql.ast.AstVisitor;
-import org.apache.iotdb.db.queryengine.plan.relational.sql.ast.ComparisonExpression;
 import org.apache.iotdb.db.queryengine.plan.relational.sql.ast.CountDevice;
 import org.apache.iotdb.db.queryengine.plan.relational.sql.ast.CreateDB;
 import org.apache.iotdb.db.queryengine.plan.relational.sql.ast.CreateIndex;
@@ -187,7 +186,6 @@ import static java.util.Collections.emptyList;
 import static java.util.Locale.ENGLISH;
 import static java.util.Objects.requireNonNull;
 import static org.apache.iotdb.commons.schema.table.TsTable.TABLE_ALLOWED_PROPERTIES;
-import static org.apache.iotdb.commons.schema.table.TsTable.TIME_COLUMN_NAME;
 import static org.apache.iotdb.commons.udf.builtin.relational.TableBuiltinScalarFunction.DATE_BIN;
 import static org.apache.iotdb.db.queryengine.execution.warnings.StandardWarningCode.REDUNDANT_ORDER_BY;
 import static org.apache.iotdb.db.queryengine.plan.execution.config.TableConfigTaskVisitor.DATABASE_NOT_SPECIFIED;
@@ -227,12 +225,6 @@ public class StatementAnalyzer {
 
   private final CorrelationSupport correlationSupport;
 
-  public static final String ONLY_SUPPORT_TIME_COLUMN_EQUI_JOIN =
-      "Only support time column equi-join in current version";
-
-  public static final String ONLY_SUPPORT_TIME_COLUMN_IN_USING_CLAUSE =
-      "Only support time column as the parameter in JOIN USING";
-
   public StatementAnalyzer(
       StatementAnalyzerFactory statementAnalyzerFactory,
       Analysis analysis,
@@ -2046,12 +2038,12 @@ public class StatementAnalyzer {
           createAndAssignScope(
               node, scope, left.getRelationType().joinWith(right.getRelationType()));
 
-      if (node.getType() == Join.Type.CROSS || node.getType() == LEFT || node.getType() == RIGHT) {
+      if (node.getType() == LEFT || node.getType() == RIGHT) {
         throw new SemanticException(
             String.format(
                 "%s JOIN is not supported, only support INNER JOIN in current version.",
                 node.getType()));
-      } else if (node.getType() == Join.Type.IMPLICIT) {
+      } else if (node.getType() == Join.Type.CROSS || node.getType() == Join.Type.IMPLICIT) {
         return output;
       }
       if (criteria instanceof JoinOn) {
@@ -2099,39 +2091,39 @@ public class StatementAnalyzer {
         throw new SemanticException("Natural join not supported");
       }
 
-      if (criteria instanceof JoinOn) {
-        JoinOn joinOn = (JoinOn) criteria;
-        Expression expression = joinOn.getExpression();
-        if (!(expression instanceof ComparisonExpression)) {
-          throw new SemanticException(ONLY_SUPPORT_TIME_COLUMN_EQUI_JOIN);
-        }
-        ComparisonExpression comparisonExpression = (ComparisonExpression) expression;
-        if (comparisonExpression.getOperator() != ComparisonExpression.Operator.EQUAL) {
-          throw new SemanticException(ONLY_SUPPORT_TIME_COLUMN_EQUI_JOIN);
-        }
-        checkArgument(
-            comparisonExpression.getLeft() instanceof DereferenceExpression,
-            ONLY_SUPPORT_TIME_COLUMN_EQUI_JOIN);
-        checkArgument(
-            comparisonExpression.getRight() instanceof DereferenceExpression,
-            ONLY_SUPPORT_TIME_COLUMN_EQUI_JOIN);
-        DereferenceExpression left = (DereferenceExpression) comparisonExpression.getLeft();
-        if (!left.getField().isPresent()
-            || !left.getField().get().equals(new Identifier(TIME_COLUMN_NAME))) {
-          throw new SemanticException(ONLY_SUPPORT_TIME_COLUMN_EQUI_JOIN);
-        }
-        DereferenceExpression right = (DereferenceExpression) comparisonExpression.getLeft();
-        if (!right.getField().isPresent()
-            || !right.getField().get().equals(new Identifier(TIME_COLUMN_NAME))) {
-          throw new SemanticException(ONLY_SUPPORT_TIME_COLUMN_EQUI_JOIN);
-        }
-      } else if (criteria instanceof JoinUsing) {
-        List<Identifier> identifiers = ((JoinUsing) criteria).getColumns();
-        if (identifiers.size() != 1
-            || !identifiers.get(0).equals(new Identifier(TIME_COLUMN_NAME))) {
-          throw new SemanticException(ONLY_SUPPORT_TIME_COLUMN_IN_USING_CLAUSE);
-        }
-      }
+      //      if (criteria instanceof JoinOn) {
+      //        JoinOn joinOn = (JoinOn) criteria;
+      //        Expression expression = joinOn.getExpression();
+      //        if (!(expression instanceof ComparisonExpression)) {
+      //          throw new SemanticException(ONLY_SUPPORT_ONE_COLUMN_EQUI_JOIN);
+      //        }
+      //        ComparisonExpression comparisonExpression = (ComparisonExpression) expression;
+      //        if (comparisonExpression.getOperator() != ComparisonExpression.Operator.EQUAL) {
+      //          throw new SemanticException(ONLY_SUPPORT_ONE_COLUMN_EQUI_JOIN);
+      //        }
+      //        checkArgument(
+      //            comparisonExpression.getLeft() instanceof DereferenceExpression,
+      //                ONLY_SUPPORT_ONE_COLUMN_EQUI_JOIN);
+      //        checkArgument(
+      //            comparisonExpression.getRight() instanceof DereferenceExpression,
+      //                ONLY_SUPPORT_ONE_COLUMN_EQUI_JOIN);
+      //        DereferenceExpression left = (DereferenceExpression) comparisonExpression.getLeft();
+      //        if (!left.getField().isPresent()
+      //            || !left.getField().get().equals(new Identifier(TIME_COLUMN_NAME))) {
+      //          throw new SemanticException(ONLY_SUPPORT_ONE_COLUMN_EQUI_JOIN);
+      //        }
+      //        DereferenceExpression right = (DereferenceExpression)
+      // comparisonExpression.getLeft();
+      //        if (!right.getField().isPresent()
+      //            || !right.getField().get().equals(new Identifier(TIME_COLUMN_NAME))) {
+      //          throw new SemanticException(ONLY_SUPPORT_ONE_COLUMN_EQUI_JOIN);
+      //        }
+      //      } else if (criteria instanceof JoinUsing) {
+      //        List<Identifier> identifiers = ((JoinUsing) criteria).getColumns();
+      //        if (identifiers.size() != 1) {
+      //          throw new SemanticException(ONLY_SUPPORT_TIME_COLUMN_IN_USING_CLAUSE);
+      //        }
+      //      }
     }
 
     private Scope analyzeJoinUsing(
diff --git a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/node/JoinNode.java b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/node/JoinNode.java
index 44084f38544..3ad0ef00052 100644
--- a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/node/JoinNode.java
+++ b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/node/JoinNode.java
@@ -120,8 +120,7 @@ public class JoinNode extends TwoChildProcessNode {
     //        !(criteria.isEmpty() && rightHashSymbol.isPresent()),
     //        "Right hash symbol is only valid in an equijoin");
 
-    // todo: Remove this check after supporting join on multiple columns.
-    checkArgument(criteria.size() <= 1, "Only support Join on one column for now.");
+    // checkArgument(criteria.size() <= 1, "Only support Join on one column for now.");
     criteria.forEach(
         equiJoinClause ->
             checkArgument(
diff --git a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/optimizations/PushPredicateIntoTableScan.java b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/optimizations/PushPredicateIntoTableScan.java
index feeda5047eb..b08503c68f1 100644
--- a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/optimizations/PushPredicateIntoTableScan.java
+++ b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/optimizations/PushPredicateIntoTableScan.java
@@ -562,7 +562,8 @@ public class PushPredicateIntoTableScan implements PlanOptimizer {
           ComparisonExpression equality = (ComparisonExpression) conjunct;
 
           boolean alignedComparison =
-              node.getLeftChild().getOutputSymbols().containsAll(extractUnique(equality.getLeft()));
+              new HashSet<>(node.getLeftChild().getOutputSymbols())
+                  .containsAll(extractUnique(equality.getLeft()));
           Expression leftExpression = alignedComparison ? equality.getLeft() : equality.getRight();
           Expression rightExpression = alignedComparison ? equality.getRight() : equality.getLeft();
 
diff --git a/iotdb-core/datanode/src/test/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/JoinTest.java b/iotdb-core/datanode/src/test/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/JoinTest.java
index e11fc5130ed..88c4a58a39d 100644
--- a/iotdb-core/datanode/src/test/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/JoinTest.java
+++ b/iotdb-core/datanode/src/test/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/JoinTest.java
@@ -41,8 +41,6 @@ import java.util.Collections;
 import java.util.List;
 
 import static org.apache.iotdb.db.queryengine.plan.relational.analyzer.AnalyzerTest.analyzeSQL;
-import static org.apache.iotdb.db.queryengine.plan.relational.analyzer.StatementAnalyzer.ONLY_SUPPORT_TIME_COLUMN_EQUI_JOIN;
-import static org.apache.iotdb.db.queryengine.plan.relational.analyzer.StatementAnalyzer.ONLY_SUPPORT_TIME_COLUMN_IN_USING_CLAUSE;
 import static org.apache.iotdb.db.queryengine.plan.relational.analyzer.TestUtils.ALL_DEVICE_ENTRIES;
 import static org.apache.iotdb.db.queryengine.plan.relational.analyzer.TestUtils.BEIJING_A1_DEVICE_ENTRY;
 import static org.apache.iotdb.db.queryengine.plan.relational.analyzer.TestUtils.DEFAULT_WARNING;
@@ -363,50 +361,19 @@ public class JoinTest {
   @Test
   public void innerJoinTest5() {
     // 1. has logical or in subquery filter, outer query filter
-
-    // 2. where t1.value1 > t2.value2
   }
 
   // ========== unsupported test ===============
   @Test
   public void unsupportedJoinTest() {
-    assertAnalyzeSemanticException(
-        "SELECT * FROM table1 t1 INNER JOIN table1 t2 ON t1.time>t2.time",
-        ONLY_SUPPORT_TIME_COLUMN_EQUI_JOIN);
-
-    assertAnalyzeSemanticException(
-        "SELECT * FROM table1 t1 INNER JOIN table1 t2 ON t1.tag1=t2.tag2",
-        ONLY_SUPPORT_TIME_COLUMN_EQUI_JOIN);
-
-    assertAnalyzeSemanticException(
-        "SELECT * FROM table1 t1 INNER JOIN table1 t2 ON t1.time>t2.time AND t1.tag1=t2.tag2",
-        ONLY_SUPPORT_TIME_COLUMN_EQUI_JOIN);
-
-    assertAnalyzeSemanticException(
-        "SELECT * FROM table1 t1 INNER JOIN table1 t2 ON t1.time>t2.time OR t1.tag1=t2.tag2",
-        ONLY_SUPPORT_TIME_COLUMN_EQUI_JOIN);
-
-    assertAnalyzeSemanticException(
-        "SELECT * FROM table1 t1 INNER JOIN table1 t2 USING(tag1)",
-        ONLY_SUPPORT_TIME_COLUMN_IN_USING_CLAUSE);
-
-    assertAnalyzeSemanticException(
-        "SELECT * FROM table1 t1 INNER JOIN table1 t2 USING(tag1, time)",
-        ONLY_SUPPORT_TIME_COLUMN_IN_USING_CLAUSE);
-
-    // LEFT, RIGHT JOIN
+    // LEFT JOIN
     assertAnalyzeSemanticException(
         "SELECT * FROM table1 t1 LEFT JOIN table1 t2 ON t1.time=t2.time",
         "LEFT JOIN is not supported, only support INNER JOIN in current version");
 
+    // RIGHT JOIN
     assertAnalyzeSemanticException(
         "SELECT * FROM table1 t1 RIGHT JOIN table1 t2 ON t1.time=t2.time",
         "RIGHT JOIN is not supported, only support INNER JOIN in current version");
-
-    assertAnalyzeSemanticException(
-        "SELECT * FROM table1 t1 CROSS JOIN table1 t2",
-        "CROSS JOIN is not supported, only support INNER JOIN in current version");
-
-    // TODO(beyyes) has non time equal join criteria;
   }
 }