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;
}
}