implement the except (distinct | all ) for table model #16760
diff --git a/integration-test/src/test/java/org/apache/iotdb/relational/it/query/recent/IoTDBExceptTableIT.java b/integration-test/src/test/java/org/apache/iotdb/relational/it/query/recent/IoTDBExceptTableIT.java new file mode 100644 index 0000000..6c9c70d --- /dev/null +++ b/integration-test/src/test/java/org/apache/iotdb/relational/it/query/recent/IoTDBExceptTableIT.java
@@ -0,0 +1,159 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +package org.apache.iotdb.relational.it.query.recent; + +import org.apache.iotdb.it.env.EnvFactory; +import org.apache.iotdb.it.framework.IoTDBTestRunner; +import org.apache.iotdb.itbase.category.TableClusterIT; +import org.apache.iotdb.itbase.category.TableLocalStandaloneIT; + +import org.junit.AfterClass; +import org.junit.BeforeClass; +import org.junit.Test; +import org.junit.experimental.categories.Category; +import org.junit.runner.RunWith; + +import static org.apache.iotdb.db.it.utils.TestUtils.prepareTableData; +import static org.apache.iotdb.db.it.utils.TestUtils.tableAssertTestFail; +import static org.apache.iotdb.db.it.utils.TestUtils.tableResultSetEqualTest; + +@RunWith(IoTDBTestRunner.class) +@Category({TableLocalStandaloneIT.class, TableClusterIT.class}) +public class IoTDBExceptTableIT { + protected static final String DATABASE_NAME = "test"; + protected static final String[] createSqls = + new String[] { + "CREATE DATABASE " + DATABASE_NAME, + "USE " + DATABASE_NAME, + // table1: ('d1', 1, 1) * 3, ('d1', 2, 2) * 1 + "create table table1(device STRING TAG, s1 INT32 FIELD, s2 INT32 FIELD)", + "insert into table1 values (1, 'd1', 1, 1)", + "insert into table1 values (2, 'd1', 1, 1)", + "insert into table1 values (3, 'd1', 1, 1)", + "insert into table1 values (4, 'd1', 2, 2)", + // table2: ('d1', 1, 1.0) * 1, ('d1', 3, 3.0) * 1 + "create table table2(device STRING TAG, s1 INT64 FIELD, s2 DOUBLE FIELD)", + "insert into table2 values (1, 'd1', 1, 1.0)", + "insert into table2 values (2, 'd1', 3, 3.0)", + // table3: use for testing alias, mirrors table2 + "create table table3(device STRING TAG, s1_testName INT64 FIELD, s2_testName DOUBLE FIELD)", + "insert into table3 values (1, 'd1', 1, 1.0)", + "insert into table3 values (2, 'd1', 3, 3.0)", + // table4: test type compatible + "create table table4(device STRING TAG, s1 TEXT FIELD, s2 DOUBLE FIELD)" + }; + + @BeforeClass + public static void setUp() throws Exception { + EnvFactory.getEnv().initClusterEnvironment(); + prepareTableData(createSqls); + } + + @AfterClass + public static void tearDown() throws Exception { + EnvFactory.getEnv().cleanClusterEnvironment(); + } + + @Test + public void normalTest() { + String[] expectedHeader = new String[] {"device", "s1", "s2"}; + + // --- EXCEPT (DISTINCT) --- + // table1 (distinct): {('d1', 1, 1.0), ('d1', 2, 2.0)} + // table2 (distinct): {('d1', 1, 1.0), ('d1', 3, 3.0)} + // expected one tuple : ('d1', 2, 2.0) + String[] retArray = + new String[] { + "d1,2,2.0,", + }; + tableResultSetEqualTest( + "select device, s1, s2 from table1 except select device, s1, s2 from table2", + expectedHeader, + retArray, + DATABASE_NAME); + tableResultSetEqualTest( + "select device, s1, s2 from table1 except distinct select device, s1, s2 from table2", + expectedHeader, + retArray, + DATABASE_NAME); + + // --- EXCEPT ALL --- + // Row ('d1', 1, 1.0): table1 has 3, table2 has 1. max(0, 3 - 1) = 2 tuples. + // Row ('d1', 2, 2.0): table1 has 1, table2 has 0. max(0, 1 - 0) = 1 tuple. + // Row ('d1', 3, 3.0): table1 has 0, table2 has 1. max(0, 0 - 1) = 0 tuples. + // expected: 2 * ('d1', 1, 1.0) and 1 * ('d1', 2, 2.0) + retArray = new String[] {"d1,1,1.0,", "d1,1,1.0,", "d1,2,2.0,"}; + tableResultSetEqualTest( + "select device, s1, s2 from table1 except all select device, s1, s2 from table2", + expectedHeader, + retArray, + DATABASE_NAME); + // test table3, the column name is different + tableResultSetEqualTest( + "select device, s1, s2 from table1 except all select device, s1_testName, s2_testName from table3", + expectedHeader, + retArray, + DATABASE_NAME); + } + + @Test + public void mappingTest() { + // table1 (aliased): (s1 as col_a) -> (1), (1), (1), (2) -> { (1.0) * 3, (2.0) * 1 } + // common value: (1.0) + + String[] expectedHeader = new String[] {"col_a"}; + + // --- EXCEPT (DISTINCT) with alias --- + // t1_distinct = {1.0, 2.0} + // t2_distinct = {1.0, 3.0} + // Result: {2.0} + String[] retArray = new String[] {"2.0,"}; + tableResultSetEqualTest( + "select col_a from ((select s1 as col_a, device as col_b from table1) except (select s2, device from table2)) order by col_a", + expectedHeader, + retArray, + DATABASE_NAME); + + // --- EXCEPT ALL with alias --- + // Row (1.0): t1 has 3, t2 has 1. max(0, 3 - 1) = 2. + // Row (2.0): t1 has 1, t2 has 0. max(0, 1 - 0) = 1. + // Result: {1.0, 1.0, 2.0} (query has order by) + retArray = new String[] {"1.0,", "1.0,", "2.0,"}; + tableResultSetEqualTest( + "select col_a from ((select s1 as col_a, device as col_b from table1) except all (select s2, device from table2)) order by col_a", + expectedHeader, + retArray, + DATABASE_NAME); + } + + @Test + public void exceptionTest() { + // type is incompatible (INT32 vs TEXT) + tableAssertTestFail( + "(select * from table1) except all (select * from table4)", + "has incompatible types: INT32, TEXT", + DATABASE_NAME); + + tableAssertTestFail( + "(select * from table1) except all (select time from table4)", + "EXCEPT query has different number of fields: 4, 1", + DATABASE_NAME); + } +}
diff --git a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/planner/plan/node/PlanGraphPrinter.java b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/planner/plan/node/PlanGraphPrinter.java index 82e03d8..bd4dd91 100644 --- a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/planner/plan/node/PlanGraphPrinter.java +++ b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/planner/plan/node/PlanGraphPrinter.java
@@ -71,6 +71,7 @@ import org.apache.iotdb.db.queryengine.plan.relational.planner.node.AssignUniqueId; import org.apache.iotdb.db.queryengine.plan.relational.planner.node.DeviceTableScanNode; import org.apache.iotdb.db.queryengine.plan.relational.planner.node.EnforceSingleRowNode; +import org.apache.iotdb.db.queryengine.plan.relational.planner.node.ExceptNode; import org.apache.iotdb.db.queryengine.plan.relational.planner.node.ExchangeNode; import org.apache.iotdb.db.queryengine.plan.relational.planner.node.ExplainAnalyzeNode; import org.apache.iotdb.db.queryengine.plan.relational.planner.node.GapFillNode; @@ -1116,6 +1117,15 @@ return render(node, boxValue, context); } + @Override + public List<String> visitExcept(ExceptNode node, GraphContext context) { + List<String> boxValue = new ArrayList<>(); + boxValue.add(String.format("Except-%s", node.getPlanNodeId().getId())); + boxValue.add(String.format("OutputSymbols: %s", node.getOutputSymbols())); + boxValue.add(String.format("isDistinct: %s", node.isDistinct())); + return render(node, boxValue, context); + } + private List<String> render(PlanNode node, List<String> nodeBoxString, GraphContext context) { Box box = new Box(nodeBoxString); List<List<String>> children = new ArrayList<>();
diff --git a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/planner/plan/node/PlanNodeType.java b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/planner/plan/node/PlanNodeType.java index 1100793..56ad6d5 100644 --- a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/planner/plan/node/PlanNodeType.java +++ b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/planner/plan/node/PlanNodeType.java
@@ -119,6 +119,7 @@ import org.apache.iotdb.db.queryengine.plan.relational.planner.node.AggregationTreeDeviceViewScanNode; import org.apache.iotdb.db.queryengine.plan.relational.planner.node.AssignUniqueId; import org.apache.iotdb.db.queryengine.plan.relational.planner.node.EnforceSingleRowNode; +import org.apache.iotdb.db.queryengine.plan.relational.planner.node.ExceptNode; import org.apache.iotdb.db.queryengine.plan.relational.planner.node.GapFillNode; import org.apache.iotdb.db.queryengine.plan.relational.planner.node.GroupNode; import org.apache.iotdb.db.queryengine.plan.relational.planner.node.InformationSchemaTableScanNode; @@ -314,6 +315,7 @@ TABLE_INTO_NODE((short) 1033), TABLE_UNION_NODE((short) 1034), TABLE_INTERSECT_NODE((short) 1035), + TABLE_EXCEPT_NODE((short) 1036), RELATIONAL_INSERT_TABLET((short) 2000), RELATIONAL_INSERT_ROW((short) 2001), @@ -705,6 +707,8 @@ return UnionNode.deserialize(buffer); case 1035: return IntersectNode.deserialize(buffer); + case 1036: + return ExceptNode.deserialize(buffer); case 2000: return RelationalInsertTabletNode.deserialize(buffer); case 2001:
diff --git a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/planner/plan/node/PlanVisitor.java b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/planner/plan/node/PlanVisitor.java index 9e8e183..ea66949 100644 --- a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/planner/plan/node/PlanVisitor.java +++ b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/planner/plan/node/PlanVisitor.java
@@ -123,6 +123,7 @@ import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.GroupReference; import org.apache.iotdb.db.queryengine.plan.relational.planner.node.AggregationTreeDeviceViewScanNode; import org.apache.iotdb.db.queryengine.plan.relational.planner.node.DeviceTableScanNode; +import org.apache.iotdb.db.queryengine.plan.relational.planner.node.ExceptNode; import org.apache.iotdb.db.queryengine.plan.relational.planner.node.GapFillNode; import org.apache.iotdb.db.queryengine.plan.relational.planner.node.GroupNode; import org.apache.iotdb.db.queryengine.plan.relational.planner.node.InformationSchemaTableScanNode; @@ -845,4 +846,8 @@ public R visitIntersect(IntersectNode node, C context) { return visitPlan(node, context); } + + public R visitExcept(ExceptNode node, C context) { + return visitPlan(node, context); + } }
diff --git a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/RelationPlanner.java b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/RelationPlanner.java index ce573ce..9732f82 100644 --- a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/RelationPlanner.java +++ b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/RelationPlanner.java
@@ -55,6 +55,7 @@ import org.apache.iotdb.db.queryengine.plan.relational.metadata.TreeDeviceViewSchema; import org.apache.iotdb.db.queryengine.plan.relational.planner.ir.IrUtils; import org.apache.iotdb.db.queryengine.plan.relational.planner.node.DeviceTableScanNode; +import org.apache.iotdb.db.queryengine.plan.relational.planner.node.ExceptNode; import org.apache.iotdb.db.queryengine.plan.relational.planner.node.FilterNode; import org.apache.iotdb.db.queryengine.plan.relational.planner.node.InformationSchemaTableScanNode; import org.apache.iotdb.db.queryengine.plan.relational.planner.node.IntersectNode; @@ -1164,6 +1165,24 @@ intersectNode, analysis.getScope(node), intersectNode.getOutputSymbols(), outerContext); } + @Override + protected RelationPlan visitExcept(Except node, Void context) { + Preconditions.checkArgument( + !node.getRelations().isEmpty(), "No relations specified for except"); + SetOperationPlan setOperationPlan = process(node); + + PlanNode exceptNode = + new ExceptNode( + idAllocator.genPlanNodeId(), + setOperationPlan.getChildren(), + setOperationPlan.getSymbolMapping(), + ImmutableList.copyOf(setOperationPlan.getSymbolMapping().keySet()), + node.isDistinct()); + + return new RelationPlan( + exceptNode, analysis.getScope(node), exceptNode.getOutputSymbols(), outerContext); + } + private SetOperationPlan process(SetOperation node) { RelationType outputFields = analysis.getOutputDescriptor(node); List<Symbol> outputs = @@ -1211,11 +1230,6 @@ } @Override - protected RelationPlan visitExcept(Except node, Void context) { - throw new IllegalStateException("Except is not supported in current version."); - } - - @Override protected RelationPlan visitInsertTablet(InsertTablet node, Void context) { final InsertTabletStatement insertTabletStatement = node.getInnerTreeStatement();
diff --git a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/ImplementExceptAll.java b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/ImplementExceptAll.java new file mode 100644 index 0000000..9cb2de7 --- /dev/null +++ b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/ImplementExceptAll.java
@@ -0,0 +1,112 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +package org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule; + +import org.apache.iotdb.commons.udf.builtin.relational.TableBuiltinScalarFunction; +import org.apache.iotdb.db.queryengine.plan.relational.metadata.Metadata; +import org.apache.iotdb.db.queryengine.plan.relational.planner.Assignments; +import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.Rule; +import org.apache.iotdb.db.queryengine.plan.relational.planner.node.ExceptNode; +import org.apache.iotdb.db.queryengine.plan.relational.planner.node.FilterNode; +import org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns; +import org.apache.iotdb.db.queryengine.plan.relational.planner.node.ProjectNode; +import org.apache.iotdb.db.queryengine.plan.relational.sql.ast.ArithmeticBinaryExpression; +import org.apache.iotdb.db.queryengine.plan.relational.sql.ast.ComparisonExpression; +import org.apache.iotdb.db.queryengine.plan.relational.sql.ast.Expression; +import org.apache.iotdb.db.queryengine.plan.relational.sql.ast.FunctionCall; +import org.apache.iotdb.db.queryengine.plan.relational.sql.ast.GenericLiteral; +import org.apache.iotdb.db.queryengine.plan.relational.sql.ast.QualifiedName; +import org.apache.iotdb.db.queryengine.plan.relational.utils.matching.Captures; +import org.apache.iotdb.db.queryengine.plan.relational.utils.matching.Pattern; + +import com.google.common.collect.ImmutableList; +import org.apache.tsfile.read.common.type.LongType; + +import static com.google.common.base.Preconditions.checkState; +import static java.util.Objects.requireNonNull; +import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns.Except.distinct; +import static org.apache.iotdb.db.queryengine.plan.relational.sql.ast.ArithmeticBinaryExpression.Operator.SUBTRACT; + +public class ImplementExceptAll implements Rule<ExceptNode> { + + private static final Pattern<ExceptNode> PATTERN = + Patterns.except().with(distinct().equalTo(false)); + + private final Metadata metadata; + + public ImplementExceptAll(Metadata metadata) { + this.metadata = requireNonNull(metadata, "metadata is null"); + } + + @Override + public Pattern<ExceptNode> getPattern() { + return PATTERN; + } + + @Override + public Result apply(ExceptNode node, Captures captures, Context context) { + + SetOperationNodeTranslator translator = + new SetOperationNodeTranslator( + metadata, context.getSymbolAllocator(), context.getIdAllocator()); + + // 1. translate the except(all) node to other planNodes + SetOperationNodeTranslator.TranslationResult translationResult = + translator.makeSetContainmentPlanForAll(node); + + checkState( + !translationResult.getCountSymbols().isEmpty(), + "ExceptNode translation result has no count symbols"); + + // 2. add the filter node above the result node from translation process + // filter condition : row_number <= greatest(...greatest((greatest(count1 - count2, 0) - count3, + // 0))....) + Expression minusCount = translationResult.getCountSymbols().get(0).toSymbolReference(); + QualifiedName greatest = + QualifiedName.of(TableBuiltinScalarFunction.GREATEST.getFunctionName()); + for (int i = 1; i < translationResult.getCountSymbols().size(); i++) { + minusCount = + new FunctionCall( + greatest, + ImmutableList.of( + new ArithmeticBinaryExpression( + SUBTRACT, + minusCount, + translationResult.getCountSymbols().get(i).toSymbolReference()), + new GenericLiteral(LongType.INT64.getDisplayName(), "0"))); + } + + FilterNode filterNode = + new FilterNode( + context.getIdAllocator().genPlanNodeId(), + translationResult.getPlanNode(), + new ComparisonExpression( + ComparisonExpression.Operator.LESS_THAN_OR_EQUAL, + translationResult.getRowNumberSymbol().toSymbolReference(), + minusCount)); + + // 3. add the project node to remove the redundant columns + return Result.ofPlanNode( + new ProjectNode( + context.getIdAllocator().genPlanNodeId(), + filterNode, + Assignments.identity(node.getOutputSymbols()))); + } +}
diff --git a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/ImplementExceptDistinctAsUnion.java b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/ImplementExceptDistinctAsUnion.java new file mode 100644 index 0000000..7b3d1c0 --- /dev/null +++ b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/ImplementExceptDistinctAsUnion.java
@@ -0,0 +1,94 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +package org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule; + +import org.apache.iotdb.db.queryengine.plan.relational.metadata.Metadata; +import org.apache.iotdb.db.queryengine.plan.relational.planner.Assignments; +import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.Rule; +import org.apache.iotdb.db.queryengine.plan.relational.planner.node.ExceptNode; +import org.apache.iotdb.db.queryengine.plan.relational.planner.node.FilterNode; +import org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns; +import org.apache.iotdb.db.queryengine.plan.relational.planner.node.ProjectNode; +import org.apache.iotdb.db.queryengine.plan.relational.sql.ast.ComparisonExpression; +import org.apache.iotdb.db.queryengine.plan.relational.sql.ast.Expression; +import org.apache.iotdb.db.queryengine.plan.relational.sql.ast.GenericLiteral; +import org.apache.iotdb.db.queryengine.plan.relational.utils.matching.Captures; +import org.apache.iotdb.db.queryengine.plan.relational.utils.matching.Pattern; + +import com.google.common.collect.ImmutableList; +import org.apache.tsfile.read.common.type.LongType; + +import static java.util.Objects.requireNonNull; +import static org.apache.iotdb.db.queryengine.plan.relational.planner.ir.IrUtils.and; +import static org.apache.iotdb.db.queryengine.plan.relational.sql.ast.ComparisonExpression.Operator.GREATER_THAN_OR_EQUAL; + +public class ImplementExceptDistinctAsUnion implements Rule<ExceptNode> { + + private final Metadata metadata; + private static final Pattern<ExceptNode> PATTERN = + Patterns.except().with(Patterns.Except.distinct().equalTo(true)); + + public ImplementExceptDistinctAsUnion(Metadata metadata) { + this.metadata = requireNonNull(metadata, "metadata is null"); + } + + @Override + public Pattern<ExceptNode> getPattern() { + return PATTERN; + } + + @Override + public Result apply(ExceptNode node, Captures captures, Context context) { + + SetOperationNodeTranslator translator = + new SetOperationNodeTranslator( + metadata, context.getSymbolAllocator(), context.getIdAllocator()); + + SetOperationNodeTranslator.TranslationResult result = + translator.makeSetContainmentPlanForDistinct(node); + + ImmutableList.Builder<Expression> predicatesBuilder = ImmutableList.builder(); + predicatesBuilder.add( + new ComparisonExpression( + GREATER_THAN_OR_EQUAL, + result.getCountSymbols().get(0).toSymbolReference(), + new GenericLiteral(LongType.INT64.getDisplayName(), "1"))); + + for (int i = 1; i < node.getChildren().size(); i++) { + predicatesBuilder.add( + new ComparisonExpression( + ComparisonExpression.Operator.EQUAL, + result.getCountSymbols().get(i).toSymbolReference(), + new GenericLiteral(LongType.INT64.getDisplayName(), "0"))); + } + + FilterNode filterNode = + new FilterNode( + context.getIdAllocator().genPlanNodeId(), + result.getPlanNode(), + and(predicatesBuilder.build())); + + return Result.ofPlanNode( + new ProjectNode( + context.getIdAllocator().genPlanNodeId(), + filterNode, + Assignments.identity(node.getOutputSymbols()))); + } +}
diff --git a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/ImplementIntersectDistinctAsUnion.java b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/ImplementIntersectDistinctAsUnion.java index 0a8f8c4..388631c 100644 --- a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/ImplementIntersectDistinctAsUnion.java +++ b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/ImplementIntersectDistinctAsUnion.java
@@ -33,6 +33,7 @@ import org.apache.iotdb.db.queryengine.plan.relational.utils.matching.Pattern; import com.google.common.collect.ImmutableList; +import org.apache.tsfile.read.common.type.LongType; import static java.util.Objects.requireNonNull; import static org.apache.iotdb.db.queryengine.plan.relational.planner.ir.IrUtils.and; @@ -73,7 +74,7 @@ new ComparisonExpression( GREATER_THAN_OR_EQUAL, symbol.toSymbolReference(), - new GenericLiteral("INT64", "1"))) + new GenericLiteral(LongType.INT64.getDisplayName(), "1"))) .collect(ImmutableList.toImmutableList())); FilterNode filterNode =
diff --git a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/PruneDistinctAggregation.java b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/PruneDistinctAggregation.java index 26fd027..37d604f 100644 --- a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/PruneDistinctAggregation.java +++ b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/PruneDistinctAggregation.java
@@ -24,6 +24,7 @@ import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.Lookup; import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.Rule; import org.apache.iotdb.db.queryengine.plan.relational.planner.node.AggregationNode; +import org.apache.iotdb.db.queryengine.plan.relational.planner.node.ExceptNode; import org.apache.iotdb.db.queryengine.plan.relational.planner.node.IntersectNode; import org.apache.iotdb.db.queryengine.plan.relational.planner.node.UnionNode; import org.apache.iotdb.db.queryengine.plan.relational.utils.matching.Captures; @@ -109,31 +110,15 @@ return visitPlan(node, context); } - /*@Override - public PlanNode visitUnion(UnionNode node, Boolean context) - { + @Override + public PlanNode visitExcept(ExceptNode node, Boolean context) { + if (node.isDistinct()) { return rewriteChildren(node, context); + } + return visitPlan(node, context); } @Override - public PlanNode visitIntersect(IntersectNode node, Boolean context) - { - if (node.isDistinct()) { - return rewriteChildren(node, context); - } - return visitPlan(node, context); - } - - @Override - public PlanNode visitExcept(ExceptNode node, Boolean context) - { - if (node.isDistinct()) { - return rewriteChildren(node, context); - } - return visitPlan(node, context); - }*/ - - @Override public PlanNode visitAggregation(AggregationNode node, Boolean context) { boolean distinct = isDistinctOperator(node);
diff --git a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/SetOperationNodeTranslator.java b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/SetOperationNodeTranslator.java index 3088860..509af16 100644 --- a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/SetOperationNodeTranslator.java +++ b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/SetOperationNodeTranslator.java
@@ -145,8 +145,8 @@ } /** - * only for transforming the intersection (all) node, add the window node and group node above the - * union node + * for transforming the intersectNode (all) and exceptNode(all), add the window node and group + * node above the union node */ private WindowNode appendCounts( UnionNode union,
diff --git a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/node/ExceptNode.java b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/node/ExceptNode.java new file mode 100644 index 0000000..9eb1df4 --- /dev/null +++ b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/node/ExceptNode.java
@@ -0,0 +1,99 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +package org.apache.iotdb.db.queryengine.plan.relational.planner.node; + +import org.apache.iotdb.db.queryengine.plan.planner.plan.node.PlanNode; +import org.apache.iotdb.db.queryengine.plan.planner.plan.node.PlanNodeId; +import org.apache.iotdb.db.queryengine.plan.planner.plan.node.PlanVisitor; +import org.apache.iotdb.db.queryengine.plan.relational.planner.Symbol; + +import com.google.common.collect.ListMultimap; + +import java.io.DataOutputStream; +import java.io.IOException; +import java.nio.ByteBuffer; +import java.util.List; + +public class ExceptNode extends SetOperationNode { + + private final boolean distinct; + + public ExceptNode( + PlanNodeId id, + List<PlanNode> children, + ListMultimap<Symbol, Symbol> outputToInputs, + List<Symbol> outputs, + boolean distinct) { + + super(id, children, outputToInputs, outputs); + this.distinct = distinct; + } + + private ExceptNode( + PlanNodeId id, + ListMultimap<Symbol, Symbol> outputToInputs, + List<Symbol> outputs, + boolean distinct) { + super(id, outputToInputs, outputs); + this.distinct = distinct; + } + + @Override + public <R, C> R accept(PlanVisitor<R, C> visitor, C context) { + return visitor.visitExcept(this, context); + } + + public boolean isDistinct() { + return distinct; + } + + @Override + public PlanNode clone() { + return new ExceptNode(getPlanNodeId(), getSymbolMapping(), getOutputSymbols(), distinct); + } + + @Override + public List<String> getOutputColumnNames() { + throw new UnsupportedOperationException(); + } + + @Override + protected void serializeAttributes(ByteBuffer byteBuffer) { + throw new UnsupportedOperationException( + "ExceptNode should never be serialized in current version"); + } + + @Override + protected void serializeAttributes(DataOutputStream stream) throws IOException { + throw new UnsupportedOperationException( + "ExceptNode should never be serialized in current version"); + } + + public static ExceptNode deserialize(ByteBuffer byteBuffer) { + throw new UnsupportedOperationException( + "ExceptNode should never be deserialized in current version"); + } + + @Override + public PlanNode replaceChildren(List<PlanNode> newChildren) { + return new ExceptNode( + getPlanNodeId(), newChildren, getSymbolMapping(), getOutputSymbols(), isDistinct()); + } +}
diff --git a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/node/Patterns.java b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/node/Patterns.java index d08ac9e..0297add 100644 --- a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/node/Patterns.java +++ b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/node/Patterns.java
@@ -193,6 +193,10 @@ return typeOf(IntersectNode.class); } + public static Pattern<ExceptNode> except() { + return typeOf(ExceptNode.class); + } + /*public static Pattern<TableWriterNode> tableWriterNode() { return typeOf(TableWriterNode.class); @@ -365,6 +369,12 @@ } } + public static final class Except { + public static Property<ExceptNode, Lookup, Boolean> distinct() { + return property("distinct", ExceptNode::isDistinct); + } + } + /*public static final class Sample { public static Property<SampleNode, Lookup, Double> sampleRatio() @@ -421,16 +431,6 @@ } }*/ - /* - - public static final class Except - { - public static Property<ExceptNode, Lookup, Boolean> distinct() - { - return property("distinct", ExceptNode::isDistinct); - } - } - */ public static final class PatternRecognition { public static Property<PatternRecognitionNode, Lookup, RowsPerMatch> rowsPerMatch() { return property("rowsPerMatch", PatternRecognitionNode::getRowsPerMatch);
diff --git a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/optimizations/LogicalOptimizeFactory.java b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/optimizations/LogicalOptimizeFactory.java index 6501571..d7084c3 100644 --- a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/optimizations/LogicalOptimizeFactory.java +++ b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/optimizations/LogicalOptimizeFactory.java
@@ -26,6 +26,8 @@ import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.Rule; import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.RuleStatsRecorder; import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.CanonicalizeExpressions; +import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.ImplementExceptAll; +import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.ImplementExceptDistinctAsUnion; import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.ImplementIntersectAll; import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.ImplementIntersectDistinctAsUnion; import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.ImplementPatternRecognition; @@ -283,10 +285,9 @@ ImmutableSet.<Rule<?>>builder() .add( new ImplementIntersectDistinctAsUnion(metadata), - // new ImplementExceptDistinctAsUnion(metadata) - new ImplementIntersectAll(metadata) - // new ImplementExceptAll(metadata))), - ) + new ImplementExceptDistinctAsUnion(metadata), + new ImplementIntersectAll(metadata), + new ImplementExceptAll(metadata)) .build()), columnPruningOptimizer, inlineProjectionLimitFiltersOptimizer,
diff --git a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/optimizations/UnaliasSymbolReferences.java b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/optimizations/UnaliasSymbolReferences.java index ff3a01b..3fdbec6 100644 --- a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/optimizations/UnaliasSymbolReferences.java +++ b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/optimizations/UnaliasSymbolReferences.java
@@ -36,6 +36,7 @@ import org.apache.iotdb.db.queryengine.plan.relational.planner.node.CorrelatedJoinNode; import org.apache.iotdb.db.queryengine.plan.relational.planner.node.DeviceTableScanNode; import org.apache.iotdb.db.queryengine.plan.relational.planner.node.EnforceSingleRowNode; +import org.apache.iotdb.db.queryengine.plan.relational.planner.node.ExceptNode; import org.apache.iotdb.db.queryengine.plan.relational.planner.node.ExplainAnalyzeNode; import org.apache.iotdb.db.queryengine.plan.relational.planner.node.FilterNode; import org.apache.iotdb.db.queryengine.plan.relational.planner.node.GapFillNode; @@ -1046,6 +1047,36 @@ mapping); } + @Override + public PlanAndMappings visitExcept(ExceptNode node, UnaliasContext context) { + + List<PlanAndMappings> rewrittenSources = + node.getChildren().stream() + .map(source -> source.accept(this, context)) + .collect(toImmutableList()); + + List<SymbolMapper> inputMappers = + rewrittenSources.stream() + .map(source -> symbolMapper(new HashMap<>(source.getMappings()))) + .collect(toImmutableList()); + + Map<Symbol, Symbol> mapping = new HashMap<>(context.getCorrelationMapping()); + SymbolMapper outputMapper = symbolMapper(mapping); + + ListMultimap<Symbol, Symbol> newOutputToInputs = + rewriteOutputToInputsMap(node.getSymbolMapping(), outputMapper, inputMappers); + List<Symbol> newOutputs = outputMapper.mapAndDistinct(node.getOutputSymbols()); + + return new PlanAndMappings( + new ExceptNode( + node.getPlanNodeId(), + rewrittenSources.stream().map(PlanAndMappings::getRoot).collect(toImmutableList()), + newOutputToInputs, + newOutputs, + node.isDistinct()), + mapping); + } + private ListMultimap<Symbol, Symbol> rewriteOutputToInputsMap( ListMultimap<Symbol, Symbol> oldMapping, SymbolMapper outputMapper,
diff --git a/iotdb-core/datanode/src/test/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/ExceptTest.java b/iotdb-core/datanode/src/test/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/ExceptTest.java new file mode 100644 index 0000000..35d3569 --- /dev/null +++ b/iotdb-core/datanode/src/test/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/ExceptTest.java
@@ -0,0 +1,132 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +package org.apache.iotdb.db.queryengine.plan.relational.analyzer; + +import org.apache.iotdb.db.queryengine.plan.planner.plan.LogicalQueryPlan; +import org.apache.iotdb.db.queryengine.plan.relational.planner.PlanTester; + +import org.junit.Test; + +import static org.apache.iotdb.db.queryengine.plan.relational.planner.assertions.PlanAssert.assertPlan; +import static org.apache.iotdb.db.queryengine.plan.relational.planner.assertions.PlanMatchPattern.aggregation; +import static org.apache.iotdb.db.queryengine.plan.relational.planner.assertions.PlanMatchPattern.filter; +import static org.apache.iotdb.db.queryengine.plan.relational.planner.assertions.PlanMatchPattern.output; +import static org.apache.iotdb.db.queryengine.plan.relational.planner.assertions.PlanMatchPattern.project; +import static org.apache.iotdb.db.queryengine.plan.relational.planner.assertions.PlanMatchPattern.sort; +import static org.apache.iotdb.db.queryengine.plan.relational.planner.assertions.PlanMatchPattern.tableScan; +import static org.apache.iotdb.db.queryengine.plan.relational.planner.assertions.PlanMatchPattern.union; +import static org.apache.iotdb.db.queryengine.plan.relational.planner.assertions.PlanMatchPattern.window; + +/** tests for except (distinct) and except all */ +public class ExceptTest { + + @Test + public void exceptTest() { + + PlanTester planTester = new PlanTester(); + LogicalQueryPlan actualLogicalQueryPlan = + planTester.createPlan("select tag1 from t1 except select tag1 from t2"); + // Verify Logical plan: `Output - project - filter - aggregation - union - 2*(project - + // tableScan)` + // This matches the ImplementExceptDistinctAsUnion logic + assertPlan( + actualLogicalQueryPlan, + output( + project( + filter( + aggregation( + union( + project(tableScan("testdb.t1")), project(tableScan("testdb.t2")))))))); + } + + @Test + public void exceptAllTest() { + + PlanTester planTester = new PlanTester(); + LogicalQueryPlan actualLogicalQueryPlan = + planTester.createPlan("select tag1 from t1 except all select tag1 from t2"); + // Verify Logical plan: `Output - project - filter - project - window - sort - union - + // 2*(project - tableScan)` + // This matches the ImplementExceptAll logic + assertPlan( + actualLogicalQueryPlan, + output( + project( + filter( + project( + window( + sort( + union( + project(tableScan("testdb.t1")), + project(tableScan("testdb.t2")))))))))); + } + + @Test + public void typeCompatibleTest() { + // use CAST if types of according columns is not compatible + // s1 is INT64, s3 is DOUBLE + + PlanTester planTester = new PlanTester(); + LogicalQueryPlan actualLogicalQueryPlan = + planTester.createPlan("select s1, s3 from table2 except all select s1, s1 from table3 "); + + // The plan structure should be the same as exceptAllTest + assertPlan( + actualLogicalQueryPlan, + output( + project( + filter( + project( + window( + sort( + union( + project(tableScan("testdb.table2")), + project(tableScan("testdb.table3")))))))))); + } + + /** + * The priority of INTERSECT is higher than that of EXCEPT. This query is parsed as: t1 EXCEPT (t2 + * INTERSECT t3) + */ + @Test + public void setOperationPriority() { + + PlanTester planTester = new PlanTester(); + LogicalQueryPlan actualLogicalQueryPlan = + planTester.createPlan( + "select tag1 from t1 except select tag1 from t2 intersect select tag1 from t3"); + + assertPlan( + actualLogicalQueryPlan, + output( + project( + filter( + aggregation( + union( + project(tableScan("testdb.t1")), + project( + project( + filter( + aggregation( + union( + project(tableScan("testdb.t2")), + project(tableScan("testdb.t3"))))))))))))); + } +}
diff --git a/iotdb-core/datanode/src/test/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/TestMetadata.java b/iotdb-core/datanode/src/test/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/TestMetadata.java index 6182def..aa9fcdf 100644 --- a/iotdb-core/datanode/src/test/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/TestMetadata.java +++ b/iotdb-core/datanode/src/test/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/TestMetadata.java
@@ -40,6 +40,7 @@ import org.apache.iotdb.db.queryengine.plan.planner.plan.parameter.model.ModelInferenceDescriptor; import org.apache.iotdb.db.queryengine.plan.relational.function.OperatorType; import org.apache.iotdb.db.queryengine.plan.relational.function.TableBuiltinTableFunction; +import org.apache.iotdb.db.queryengine.plan.relational.function.arithmetic.SubtractionResolver; import org.apache.iotdb.db.queryengine.plan.relational.metadata.AlignedDeviceEntry; import org.apache.iotdb.db.queryengine.plan.relational.metadata.ColumnMetadata; import org.apache.iotdb.db.queryengine.plan.relational.metadata.ColumnSchema; @@ -244,8 +245,16 @@ throws OperatorNotFoundException { switch (operatorType) { - case ADD: case SUBTRACT: + Optional<Type> resolvedType = SubtractionResolver.checkConditions(argumentTypes); + return resolvedType.orElseThrow( + () -> + new OperatorNotFoundException( + operatorType, + argumentTypes, + new IllegalArgumentException( + "The combination of argument types is not supported for this operator."))); + case ADD: case MULTIPLY: case DIVIDE: case MODULUS: