You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@iotdb.apache.org by la...@apache.org on 2025/01/17 08:56:46 UTC

(iotdb) branch support_exists_and_correlate created (now 38a9f223e02)

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

lancelly pushed a change to branch support_exists_and_correlate
in repository https://gitbox.apache.org/repos/asf/iotdb.git


      at 38a9f223e02 Merge branch 'support_uncorrelated_quantified_comparison' into support_exists_and_correlate

This branch includes the following new commits:

     new fb6c5e51a63 add AssignUniqueId
     new 45f99a79449 add leftjoin operator
     new 143689af8c4 correct check in pushpredicate
     new dc4cd7bbb21 add correlated agg related rules
     new 8beeb0c3b49 add rules and join check
     new 87b485ef8c4 add tryNormalizeToOuterToInnerJoin
     new 56aa492d379 Merge branch 'support_uncorrelated_quantified_comparison' into support_exists_and_correlate
     new 38a9f223e02 Merge branch 'support_uncorrelated_quantified_comparison' into support_exists_and_correlate

The 8 revisions listed above as "new" are entirely new to this
repository and will be described in separate emails.  The revisions
listed as "add" were already present in the repository and have only
been added to this reference.



(iotdb) 02/08: add leftjoin operator

Posted by la...@apache.org.
This is an automated email from the ASF dual-hosted git repository.

lancelly pushed a commit to branch support_exists_and_correlate
in repository https://gitbox.apache.org/repos/asf/iotdb.git

commit 45f99a79449f43feadcf58a120928ab2d6c8d2fc
Author: lancelly <14...@qq.com>
AuthorDate: Fri Jan 10 21:26:26 2025 +0800

    add leftjoin operator
---
 .../relational/MergeSortLeftJoinOperator.java      | 163 +++++++++++++++++++++
 .../plan/planner/TableOperatorGenerator.java       |  19 +++
 2 files changed, 182 insertions(+)

diff --git a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/execution/operator/source/relational/MergeSortLeftJoinOperator.java b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/execution/operator/source/relational/MergeSortLeftJoinOperator.java
new file mode 100644
index 00000000000..be6cb2f3a51
--- /dev/null
+++ b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/execution/operator/source/relational/MergeSortLeftJoinOperator.java
@@ -0,0 +1,163 @@
+/*
+ * 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.execution.operator.source.relational;
+
+import org.apache.iotdb.db.queryengine.execution.MemoryEstimationHelper;
+import org.apache.iotdb.db.queryengine.execution.operator.Operator;
+import org.apache.iotdb.db.queryengine.execution.operator.OperatorContext;
+import org.apache.iotdb.db.queryengine.execution.operator.process.join.merge.comparator.JoinKeyComparator;
+
+import org.apache.tsfile.enums.TSDataType;
+import org.apache.tsfile.utils.RamUsageEstimator;
+
+import java.util.List;
+
+public class MergeSortLeftJoinOperator extends AbstractMergeSortJoinOperator {
+  private static final long INSTANCE_SIZE =
+      RamUsageEstimator.shallowSizeOfInstance(MergeSortLeftJoinOperator.class);
+
+  public MergeSortLeftJoinOperator(
+      OperatorContext operatorContext,
+      Operator leftChild,
+      int[] leftJoinKeyPositions,
+      int[] leftOutputSymbolIdx,
+      Operator rightChild,
+      int[] rightJoinKeyPositions,
+      int[] rightOutputSymbolIdx,
+      List<JoinKeyComparator> joinKeyComparators,
+      List<TSDataType> dataTypes) {
+    super(
+        operatorContext,
+        leftChild,
+        leftJoinKeyPositions,
+        leftOutputSymbolIdx,
+        rightChild,
+        rightJoinKeyPositions,
+        rightOutputSymbolIdx,
+        joinKeyComparators,
+        dataTypes);
+  }
+
+  @Override
+  public boolean hasNext() throws Exception {
+    if (retainedTsBlock != null) {
+      return true;
+    }
+
+    return !leftFinished;
+  }
+
+  @Override
+  protected boolean prepareInput() throws Exception {
+    gotCandidateBlocks();
+    if (rightFinished) {
+      return leftBlockNotEmpty();
+    }
+    return leftBlockNotEmpty() && rightBlockNotEmpty() && gotNextRightBlock();
+  }
+
+  @Override
+  protected boolean processFinished() {
+    if (rightFinished) {
+      appendLeftWithEmptyRight();
+      return true;
+    }
+
+    // skip all NULL values in right, because NULL value will not match the left value
+    while (currentRightHasNullValue()) {
+      if (rightFinishedWithIncIndex()) {
+        return true;
+      }
+    }
+
+    // all the join keys in rightTsBlock are less than leftTsBlock, just skip right
+    if (allRightLessThanLeft()) {
+      resetRightBlockList();
+      return true;
+    }
+
+    // all the join Keys in leftTsBlock are less than rightTsBlock, just append the left value
+    if (allLeftLessThanRight()) {
+      appendLeftWithEmptyRight();
+      resetLeftBlock();
+      return true;
+    }
+
+    // continue right < left, until right >= left
+    while (lessThan(
+        rightBlockList.get(rightBlockListIdx),
+        rightJoinKeyPositions,
+        rightIndex,
+        leftBlock,
+        leftJoinKeyPositions,
+        leftIndex)) {
+      if (rightFinishedWithIncIndex()) {
+        return true;
+      }
+    }
+    if (currentRoundNeedStop()) {
+      return true;
+    }
+
+    // if current left is null, append null to result
+    while (currentLeftHasNullValue()) {
+      appendOneLeftRowWithEmptyRight();
+      if (leftFinishedWithIncIndex()) {
+        return true;
+      }
+    }
+
+    // continue left < right, until left >= right
+    while (lessThan(
+        leftBlock,
+        leftJoinKeyPositions,
+        leftIndex,
+        rightBlockList.get(rightBlockListIdx),
+        rightJoinKeyPositions,
+        rightIndex)) {
+      appendOneLeftRowWithEmptyRight();
+      if (leftFinishedWithIncIndex()) {
+        return true;
+      }
+    }
+    if (currentRoundNeedStop()) {
+      return true;
+    }
+
+    // has right value equals to current left, append to join result, inc leftIndex
+    return hasMatchedRightValueToProbeLeft() && leftFinishedWithIncIndex();
+  }
+
+  @Override
+  protected void recordsWhenDataMatches() {
+    // do nothing
+  }
+
+  @Override
+  public long ramBytesUsed() {
+    return INSTANCE_SIZE
+        + MemoryEstimationHelper.getEstimatedSizeOfAccountableObject(leftChild)
+        + MemoryEstimationHelper.getEstimatedSizeOfAccountableObject(rightChild)
+        + RamUsageEstimator.sizeOf(leftOutputSymbolIdx)
+        + RamUsageEstimator.sizeOf(rightOutputSymbolIdx)
+        + MemoryEstimationHelper.getEstimatedSizeOfAccountableObject(operatorContext)
+        + resultBuilder.getRetainedSizeInBytes();
+  }
+}
diff --git a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/planner/TableOperatorGenerator.java b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/planner/TableOperatorGenerator.java
index 1d99562e5ed..efd8323af07 100644
--- a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/planner/TableOperatorGenerator.java
+++ b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/planner/TableOperatorGenerator.java
@@ -84,6 +84,7 @@ import org.apache.iotdb.db.queryengine.execution.operator.source.relational.Info
 import org.apache.iotdb.db.queryengine.execution.operator.source.relational.LastQueryAggTableScanOperator;
 import org.apache.iotdb.db.queryengine.execution.operator.source.relational.MergeSortFullOuterJoinOperator;
 import org.apache.iotdb.db.queryengine.execution.operator.source.relational.MergeSortInnerJoinOperator;
+import org.apache.iotdb.db.queryengine.execution.operator.source.relational.MergeSortLeftJoinOperator;
 import org.apache.iotdb.db.queryengine.execution.operator.source.relational.MergeSortSemiJoinOperator;
 import org.apache.iotdb.db.queryengine.execution.operator.source.relational.TableScanOperator;
 import org.apache.iotdb.db.queryengine.execution.operator.source.relational.TreeAlignedDeviceViewAggregationScanOperator;
@@ -1467,6 +1468,24 @@ public class TableOperatorGenerator extends PlanVisitor<Operator, LocalExecution
           JoinKeyComparatorFactory.getComparators(joinKeyTypes, true),
           dataTypes,
           joinKeyTypes.stream().map(this::buildUpdateLastRowFunction).collect(Collectors.toList()));
+    } else if (requireNonNull(node.getJoinType()) == JoinNode.JoinType.LEFT) {
+      OperatorContext operatorContext =
+          context
+              .getDriverContext()
+              .addOperatorContext(
+                  context.getNextOperatorId(),
+                  node.getPlanNodeId(),
+                  MergeSortLeftJoinOperator.class.getSimpleName());
+      return new MergeSortLeftJoinOperator(
+          operatorContext,
+          leftChild,
+          leftJoinKeyPositions,
+          leftOutputSymbolIdx,
+          rightChild,
+          rightJoinKeyPositions,
+          rightOutputSymbolIdx,
+          JoinKeyComparatorFactory.getComparators(joinKeyTypes, true),
+          dataTypes);
     }
 
     throw new IllegalStateException("Unsupported join type: " + node.getJoinType());


(iotdb) 03/08: correct check in pushpredicate

Posted by la...@apache.org.
This is an automated email from the ASF dual-hosted git repository.

lancelly pushed a commit to branch support_exists_and_correlate
in repository https://gitbox.apache.org/repos/asf/iotdb.git

commit 143689af8c47315cb5326f81ead6a67c171c848c
Author: lancelly <14...@qq.com>
AuthorDate: Fri Jan 10 21:31:54 2025 +0800

    correct check in pushpredicate
---
 .../it/db/it/IoTDBMultiTAGsWithAttributesTableIT.java        | 12 ++++++------
 .../plan/relational/planner/optimizations/JoinUtils.java     |  4 ++--
 .../planner/optimizations/PushPredicateIntoTableScan.java    |  7 +++----
 3 files changed, 11 insertions(+), 12 deletions(-)

diff --git a/integration-test/src/test/java/org/apache/iotdb/relational/it/db/it/IoTDBMultiTAGsWithAttributesTableIT.java b/integration-test/src/test/java/org/apache/iotdb/relational/it/db/it/IoTDBMultiTAGsWithAttributesTableIT.java
index 7090b2b6ea3..d9650267e4e 100644
--- a/integration-test/src/test/java/org/apache/iotdb/relational/it/db/it/IoTDBMultiTAGsWithAttributesTableIT.java
+++ b/integration-test/src/test/java/org/apache/iotdb/relational/it/db/it/IoTDBMultiTAGsWithAttributesTableIT.java
@@ -36,7 +36,7 @@ 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.apache.iotdb.db.queryengine.plan.relational.planner.optimizations.JoinUtils.FULL_JOIN_ONLY_SUPPORT_EQUI_JOIN;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.optimizations.JoinUtils.ONLY_SUPPORT_EQUI_JOIN;
 import static org.junit.Assert.fail;
 
 /** In this IT, table has more than one TAGs and Attributes. */
@@ -2098,27 +2098,27 @@ public class IoTDBMultiTAGsWithAttributesTableIT {
   public void exceptionTest() {
     tableAssertTestFail(
         "select * from table0 t0 full join table1 t1 on t0.num>t1.num",
-        FULL_JOIN_ONLY_SUPPORT_EQUI_JOIN,
+            ONLY_SUPPORT_EQUI_JOIN,
         DATABASE_NAME);
 
     tableAssertTestFail(
         "select * from table0 t0 full join table1 t1 on t0.num!=t1.num",
-        FULL_JOIN_ONLY_SUPPORT_EQUI_JOIN,
+            ONLY_SUPPORT_EQUI_JOIN,
         DATABASE_NAME);
 
     tableAssertTestFail(
         "select * from table0 t0 full join table1 t1 on t0.device=t1.device AND t0.num>t1.num",
-        FULL_JOIN_ONLY_SUPPORT_EQUI_JOIN,
+            ONLY_SUPPORT_EQUI_JOIN,
         DATABASE_NAME);
 
     tableAssertTestFail(
         "select * from table0 t0 full join table1 t1 on t0.device=t1.device OR t0.num>t1.num",
-        FULL_JOIN_ONLY_SUPPORT_EQUI_JOIN,
+            ONLY_SUPPORT_EQUI_JOIN,
         DATABASE_NAME);
 
     tableAssertTestFail(
         "select * from table0 t0 full join table1 t1 on t0.device=t1.device OR t0.time=t1.time",
-        FULL_JOIN_ONLY_SUPPORT_EQUI_JOIN,
+            ONLY_SUPPORT_EQUI_JOIN,
         DATABASE_NAME);
   }
 
diff --git a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/optimizations/JoinUtils.java b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/optimizations/JoinUtils.java
index 83ac24ff9b6..8d317cc09d8 100644
--- a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/optimizations/JoinUtils.java
+++ b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/optimizations/JoinUtils.java
@@ -48,8 +48,8 @@ import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.JoinN
 import static org.apache.iotdb.db.queryengine.plan.relational.sql.ast.BooleanLiteral.TRUE_LITERAL;
 
 public class JoinUtils {
-  public static final String FULL_JOIN_ONLY_SUPPORT_EQUI_JOIN =
-      "Full outer join only support equiJoinClauses";
+  public static final String ONLY_SUPPORT_EQUI_JOIN =
+      "For now, FullOuterJoin and LeftJoin only support EquiJoinClauses";
 
   private JoinUtils() {}
 
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 ba9487c2ba9..286c53d91b7 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
@@ -100,9 +100,8 @@ import static org.apache.iotdb.db.queryengine.plan.relational.planner.ir.GlobalT
 import static org.apache.iotdb.db.queryengine.plan.relational.planner.ir.IrUtils.combineConjuncts;
 import static org.apache.iotdb.db.queryengine.plan.relational.planner.ir.IrUtils.extractConjuncts;
 import static org.apache.iotdb.db.queryengine.plan.relational.planner.ir.IrUtils.filterDeterministicConjuncts;
-import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.JoinNode.JoinType.FULL;
 import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.JoinNode.JoinType.INNER;
-import static org.apache.iotdb.db.queryengine.plan.relational.planner.optimizations.JoinUtils.FULL_JOIN_ONLY_SUPPORT_EQUI_JOIN;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.optimizations.JoinUtils.ONLY_SUPPORT_EQUI_JOIN;
 import static org.apache.iotdb.db.queryengine.plan.relational.planner.optimizations.JoinUtils.extractJoinPredicate;
 import static org.apache.iotdb.db.queryengine.plan.relational.planner.optimizations.JoinUtils.joinEqualityExpression;
 import static org.apache.iotdb.db.queryengine.plan.relational.planner.optimizations.JoinUtils.processInnerJoin;
@@ -628,8 +627,8 @@ public class PushPredicateIntoTableScan implements PlanOptimizer {
 
           equiJoinClauses.add(new JoinNode.EquiJoinClause(leftSymbol, rightSymbol));
         } else {
-          if (node.getJoinType() == FULL) {
-            throw new UnsupportedOperationException(FULL_JOIN_ONLY_SUPPORT_EQUI_JOIN);
+          if (node.getJoinType() != INNER) {
+            throw new UnsupportedOperationException(ONLY_SUPPORT_EQUI_JOIN);
           }
           joinFilterBuilder.add(conjunct);
         }


(iotdb) 06/08: add tryNormalizeToOuterToInnerJoin

Posted by la...@apache.org.
This is an automated email from the ASF dual-hosted git repository.

lancelly pushed a commit to branch support_exists_and_correlate
in repository https://gitbox.apache.org/repos/asf/iotdb.git

commit 87b485ef8c4bc675e9d9c8240d4a2810643e08b2
Author: lancelly <14...@qq.com>
AuthorDate: Mon Jan 13 10:14:18 2025 +0800

    add tryNormalizeToOuterToInnerJoin
---
 .../planner/optimizations/JoinUtils.java           |  88 --------------
 .../optimizations/LogicalOptimizeFactory.java      |   2 +-
 .../optimizations/PushPredicateIntoTableScan.java  | 128 ++++++++++++++++++++-
 3 files changed, 126 insertions(+), 92 deletions(-)

diff --git a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/optimizations/JoinUtils.java b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/optimizations/JoinUtils.java
index 80e1b586995..0575d9a6f41 100644
--- a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/optimizations/JoinUtils.java
+++ b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/optimizations/JoinUtils.java
@@ -30,8 +30,6 @@ import com.google.common.collect.ImmutableList;
 import com.google.common.collect.ImmutableSet;
 
 import java.util.Collection;
-import java.util.EnumSet;
-import java.util.List;
 import java.util.Objects;
 import java.util.Set;
 
@@ -41,10 +39,6 @@ import static org.apache.iotdb.db.queryengine.plan.relational.planner.ir.Determi
 import static org.apache.iotdb.db.queryengine.plan.relational.planner.ir.IrUtils.combineConjuncts;
 import static org.apache.iotdb.db.queryengine.plan.relational.planner.ir.IrUtils.extractConjuncts;
 import static org.apache.iotdb.db.queryengine.plan.relational.planner.ir.IrUtils.filterDeterministicConjuncts;
-import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.JoinNode.JoinType.FULL;
-import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.JoinNode.JoinType.INNER;
-import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.JoinNode.JoinType.LEFT;
-import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.JoinNode.JoinType.RIGHT;
 import static org.apache.iotdb.db.queryengine.plan.relational.sql.ast.BooleanLiteral.TRUE_LITERAL;
 
 public class JoinUtils {
@@ -98,88 +92,6 @@ public class JoinUtils {
     return false;
   }
 
-  static JoinNode tryNormalizeToOuterToInnerJoin(JoinNode node, Expression inheritedPredicate) {
-    checkArgument(
-        EnumSet.of(INNER, RIGHT, LEFT, FULL).contains(node.getJoinType()),
-        "Unsupported join type: %s",
-        node.getJoinType());
-
-    if (node.getJoinType() == JoinNode.JoinType.INNER) {
-      return node;
-    }
-
-    if (node.getJoinType() == JoinNode.JoinType.FULL) {
-      boolean canConvertToLeftJoin =
-          canConvertOuterToInner(node.getLeftChild().getOutputSymbols(), inheritedPredicate);
-      boolean canConvertToRightJoin =
-          canConvertOuterToInner(node.getRightChild().getOutputSymbols(), inheritedPredicate);
-      if (!canConvertToLeftJoin && !canConvertToRightJoin) {
-        return node;
-      }
-      if (canConvertToLeftJoin && canConvertToRightJoin) {
-        return new JoinNode(
-            node.getPlanNodeId(),
-            INNER,
-            node.getLeftChild(),
-            node.getRightChild(),
-            node.getCriteria(),
-            node.getLeftOutputSymbols(),
-            node.getRightOutputSymbols(),
-            node.getFilter(),
-            node.isSpillable());
-      }
-      return new JoinNode(
-          node.getPlanNodeId(),
-          canConvertToLeftJoin ? LEFT : RIGHT,
-          node.getLeftChild(),
-          node.getRightChild(),
-          node.getCriteria(),
-          node.getLeftOutputSymbols(),
-          node.getRightOutputSymbols(),
-          node.getFilter(),
-          node.isSpillable());
-    }
-
-    if (node.getJoinType() == JoinNode.JoinType.LEFT
-            && !canConvertOuterToInner(node.getRightChild().getOutputSymbols(), inheritedPredicate)
-        || node.getJoinType() == JoinNode.JoinType.RIGHT
-            && !canConvertOuterToInner(
-                node.getLeftChild().getOutputSymbols(), inheritedPredicate)) {
-      return node;
-    }
-    return new JoinNode(
-        node.getPlanNodeId(),
-        JoinNode.JoinType.INNER,
-        node.getLeftChild(),
-        node.getRightChild(),
-        node.getCriteria(),
-        node.getLeftOutputSymbols(),
-        node.getRightOutputSymbols(),
-        node.getFilter(),
-        node.isSpillable());
-  }
-
-  static boolean canConvertOuterToInner(
-      List<Symbol> innerSymbolsForOuterJoin, Expression inheritedPredicate) {
-    Set<Symbol> innerSymbols = ImmutableSet.copyOf(innerSymbolsForOuterJoin);
-    for (Expression conjunct : extractConjuncts(inheritedPredicate)) {
-      if (isDeterministic(conjunct)) {
-        return true;
-        // Ignore a conjunct for this test if we cannot deterministically get responses from it
-        // Object response = nullInputEvaluator(innerSymbols, conjunct);
-        // if (response == null || response instanceof NullLiteral ||
-        // Boolean.FALSE.equals(response)) {
-        // If there is a single conjunct that returns FALSE or NULL given all NULL inputs for the
-        // inner side symbols of an outer join
-        // then this conjunct removes all effects of the outer join, and effectively turns this
-        // into an equivalent of an inner join.
-        // So, let's just rewrite this join as an INNER join
-        // }
-      }
-    }
-    return false;
-  }
-
   static InnerJoinPushDownResult processInnerJoin(
       Metadata metadata,
       Expression inheritedPredicate,
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 834fa51d281..0cdd9127d15 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
@@ -256,7 +256,7 @@ public class LogicalOptimizeFactory {
         new IterativeOptimizer(
             plannerContext, ruleStats, ImmutableSet.of(new PruneDistinctAggregation())),
         simplifyOptimizer,
-        new PushPredicateIntoTableScan(),
+        new PushPredicateIntoTableScan(plannerContext, typeAnalyzer),
         // Currently, Distinct is not supported, so we cant use this rule for now.
         //        new IterativeOptimizer(
         //            plannerContext,
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 629dfb59fd0..ecc32fa4766 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
@@ -32,6 +32,7 @@ import org.apache.iotdb.db.queryengine.plan.planner.plan.node.PlanVisitor;
 import org.apache.iotdb.db.queryengine.plan.planner.plan.node.write.InsertTabletNode;
 import org.apache.iotdb.db.queryengine.plan.planner.plan.node.write.RelationalInsertTabletNode;
 import org.apache.iotdb.db.queryengine.plan.relational.analyzer.Analysis;
+import org.apache.iotdb.db.queryengine.plan.relational.analyzer.NodeRef;
 import org.apache.iotdb.db.queryengine.plan.relational.analyzer.predicate.ConvertPredicateToTimeFilterVisitor;
 import org.apache.iotdb.db.queryengine.plan.relational.analyzer.predicate.PredicateCombineIntoTableScanChecker;
 import org.apache.iotdb.db.queryengine.plan.relational.analyzer.predicate.PredicatePushIntoMetadataChecker;
@@ -41,7 +42,10 @@ import org.apache.iotdb.db.queryengine.plan.relational.metadata.Metadata;
 import org.apache.iotdb.db.queryengine.plan.relational.metadata.NonAlignedAlignedDeviceEntry;
 import org.apache.iotdb.db.queryengine.plan.relational.planner.Assignments;
 import org.apache.iotdb.db.queryengine.plan.relational.planner.EqualityInference;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.IrExpressionInterpreter;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.IrTypeAnalyzer;
 import org.apache.iotdb.db.queryengine.plan.relational.planner.OrderingScheme;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.PlannerContext;
 import org.apache.iotdb.db.queryengine.plan.relational.planner.SortOrder;
 import org.apache.iotdb.db.queryengine.plan.relational.planner.Symbol;
 import org.apache.iotdb.db.queryengine.plan.relational.planner.SymbolAllocator;
@@ -60,18 +64,22 @@ 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.LogicalExpression;
 import org.apache.iotdb.db.queryengine.plan.relational.sql.ast.Node;
+import org.apache.iotdb.db.queryengine.plan.relational.sql.ast.NullLiteral;
 import org.apache.iotdb.db.queryengine.plan.relational.sql.ast.SymbolReference;
 
 import com.google.common.collect.ImmutableList;
 import com.google.common.collect.ImmutableMap;
 import com.google.common.collect.ImmutableSet;
+import org.apache.tsfile.read.common.type.Type;
 import org.apache.tsfile.read.filter.basic.Filter;
 import org.apache.tsfile.utils.Pair;
 
 import javax.annotation.Nullable;
 
 import java.util.ArrayList;
+import java.util.Collection;
 import java.util.Collections;
+import java.util.EnumSet;
 import java.util.HashMap;
 import java.util.HashSet;
 import java.util.List;
@@ -100,7 +108,10 @@ import static org.apache.iotdb.db.queryengine.plan.relational.planner.ir.GlobalT
 import static org.apache.iotdb.db.queryengine.plan.relational.planner.ir.IrUtils.combineConjuncts;
 import static org.apache.iotdb.db.queryengine.plan.relational.planner.ir.IrUtils.extractConjuncts;
 import static org.apache.iotdb.db.queryengine.plan.relational.planner.ir.IrUtils.filterDeterministicConjuncts;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.JoinNode.JoinType.FULL;
 import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.JoinNode.JoinType.INNER;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.JoinNode.JoinType.LEFT;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.JoinNode.JoinType.RIGHT;
 import static org.apache.iotdb.db.queryengine.plan.relational.planner.optimizations.JoinUtils.ONLY_SUPPORT_EQUI_JOIN;
 import static org.apache.iotdb.db.queryengine.plan.relational.planner.optimizations.JoinUtils.extractJoinPredicate;
 import static org.apache.iotdb.db.queryengine.plan.relational.planner.optimizations.JoinUtils.joinEqualityExpression;
@@ -138,6 +149,15 @@ public class PushPredicateIntoTableScan implements PlanOptimizer {
 
   private static final IoTDBConfig CONFIG = IoTDBDescriptor.getInstance().getConfig();
 
+  private final PlannerContext plannerContext;
+
+  private final IrTypeAnalyzer typeAnalyzer;
+
+  public PushPredicateIntoTableScan(PlannerContext plannerContext, IrTypeAnalyzer typeAnalyzer) {
+    this.plannerContext = plannerContext;
+    this.typeAnalyzer = typeAnalyzer;
+  }
+
   @Override
   public PlanNode optimize(PlanNode plan, Context context) {
     return plan.accept(
@@ -145,7 +165,9 @@ public class PushPredicateIntoTableScan implements PlanOptimizer {
             context.getQueryContext(),
             context.getAnalysis(),
             context.getMetadata(),
-            context.getSymbolAllocator()),
+            context.getSymbolAllocator(),
+            plannerContext,
+            typeAnalyzer),
         new RewriteContext(TRUE_LITERAL));
   }
 
@@ -155,17 +177,24 @@ public class PushPredicateIntoTableScan implements PlanOptimizer {
     private final Metadata metadata;
     private final SymbolAllocator symbolAllocator;
     private final QueryId queryId;
+    private final PlannerContext plannerContext;
+
+    private final IrTypeAnalyzer typeAnalyzer;
 
     Rewriter(
         MPPQueryContext queryContext,
         Analysis analysis,
         Metadata metadata,
-        SymbolAllocator symbolAllocator) {
+        SymbolAllocator symbolAllocator,
+        PlannerContext plannerContext,
+        IrTypeAnalyzer typeAnalyzer) {
       this.queryContext = queryContext;
       this.analysis = analysis;
       this.metadata = metadata;
       this.symbolAllocator = symbolAllocator;
       this.queryId = queryContext.getQueryId();
+      this.plannerContext = plannerContext;
+      this.typeAnalyzer = typeAnalyzer;
     }
 
     @Override
@@ -550,7 +579,7 @@ public class PushPredicateIntoTableScan implements PlanOptimizer {
           context.inheritedPredicate != null ? context.inheritedPredicate : TRUE_LITERAL;
 
       // See if we can rewrite outer joins in terms of a plain inner join
-      // node = tryNormalizeToOuterToInnerJoin(node, inheritedPredicate);
+      node = tryNormalizeToOuterToInnerJoin(node, inheritedPredicate);
 
       Expression leftEffectivePredicate = TRUE_LITERAL;
       // effectivePredicateExtractor.extract(session, node.getLeftChild(), types, typeAnalyzer);
@@ -1055,6 +1084,99 @@ public class PushPredicateIntoTableScan implements PlanOptimizer {
         return metadata.getDataPartition(database, dataPartitionQueryParams);
       }
     }
+
+    private JoinNode tryNormalizeToOuterToInnerJoin(JoinNode node, Expression inheritedPredicate) {
+      checkArgument(
+          EnumSet.of(INNER, RIGHT, LEFT, FULL).contains(node.getJoinType()),
+          "Unsupported join type: %s",
+          node.getJoinType());
+
+      if (node.getJoinType() == JoinNode.JoinType.INNER) {
+        return node;
+      }
+
+      if (node.getJoinType() == JoinNode.JoinType.FULL) {
+        boolean canConvertToLeftJoin =
+            canConvertOuterToInner(node.getLeftChild().getOutputSymbols(), inheritedPredicate);
+        boolean canConvertToRightJoin =
+            canConvertOuterToInner(node.getRightChild().getOutputSymbols(), inheritedPredicate);
+        if (!canConvertToLeftJoin && !canConvertToRightJoin) {
+          return node;
+        }
+        if (canConvertToLeftJoin && canConvertToRightJoin) {
+          return new JoinNode(
+              node.getPlanNodeId(),
+              INNER,
+              node.getLeftChild(),
+              node.getRightChild(),
+              node.getCriteria(),
+              node.getLeftOutputSymbols(),
+              node.getRightOutputSymbols(),
+              node.getFilter(),
+              node.isSpillable());
+        }
+        return new JoinNode(
+            node.getPlanNodeId(),
+            canConvertToLeftJoin ? LEFT : RIGHT,
+            node.getLeftChild(),
+            node.getRightChild(),
+            node.getCriteria(),
+            node.getLeftOutputSymbols(),
+            node.getRightOutputSymbols(),
+            node.getFilter(),
+            node.isSpillable());
+      }
+
+      if (node.getJoinType() == JoinNode.JoinType.LEFT
+              && !canConvertOuterToInner(
+                  node.getRightChild().getOutputSymbols(), inheritedPredicate)
+          || node.getJoinType() == JoinNode.JoinType.RIGHT
+              && !canConvertOuterToInner(
+                  node.getLeftChild().getOutputSymbols(), inheritedPredicate)) {
+        return node;
+      }
+      return new JoinNode(
+          node.getPlanNodeId(),
+          JoinNode.JoinType.INNER,
+          node.getLeftChild(),
+          node.getRightChild(),
+          node.getCriteria(),
+          node.getLeftOutputSymbols(),
+          node.getRightOutputSymbols(),
+          node.getFilter(),
+          node.isSpillable());
+    }
+
+    private boolean canConvertOuterToInner(
+        List<Symbol> innerSymbolsForOuterJoin, Expression inheritedPredicate) {
+      Set<Symbol> innerSymbols = ImmutableSet.copyOf(innerSymbolsForOuterJoin);
+      for (Expression conjunct : extractConjuncts(inheritedPredicate)) {
+        if (isDeterministic(conjunct)) {
+          // Ignore a conjunct for this test if we cannot deterministically get responses from it
+          Object response = nullInputEvaluator(innerSymbols, conjunct);
+          if (response == null
+              || response instanceof NullLiteral
+              || Boolean.FALSE.equals(response)) {
+            // If there is a single conjunct that returns FALSE or NULL given all NULL inputs for
+            // the inner side symbols of an outer join
+            // then this conjunct removes all effects of the outer join, and effectively turns this
+            // into an equivalent of an inner join.
+            // So, let's just rewrite this join as an INNER join
+            return true;
+          }
+        }
+      }
+      return false;
+    }
+
+    /** Evaluates an expression's response to binding the specified input symbols to NULL */
+    private Object nullInputEvaluator(Collection<Symbol> nullSymbols, Expression expression) {
+      Map<NodeRef<Expression>, Type> expressionTypes =
+          typeAnalyzer.getTypes(queryContext.getSession(), symbolAllocator.getTypes(), expression);
+      return new IrExpressionInterpreter(
+              expression, plannerContext, queryContext.getSession(), expressionTypes)
+          .optimize(symbol -> nullSymbols.contains(symbol) ? null : symbol.toSymbolReference());
+    }
   }
 
   public static boolean containsDiffFunction(Expression expression) {


(iotdb) 05/08: add rules and join check

Posted by la...@apache.org.
This is an automated email from the ASF dual-hosted git repository.

lancelly pushed a commit to branch support_exists_and_correlate
in repository https://gitbox.apache.org/repos/asf/iotdb.git

commit 8beeb0c3b49d5bd958067502f4fdf8abb72aa832
Author: lancelly <14...@qq.com>
AuthorDate: Sun Jan 12 14:56:18 2025 +0800

    add rules and join check
---
 .../plan/planner/TableOperatorGenerator.java       |  18 +++
 .../iterative/rule/RemoveRedundantExists.java      | 104 ++++++++++++++
 .../planner/optimizations/JoinUtils.java           | 159 +++++++++++++++++++++
 .../optimizations/LogicalOptimizeFactory.java      |   5 +-
 .../optimizations/PushPredicateIntoTableScan.java  |  20 ++-
 5 files changed, 302 insertions(+), 4 deletions(-)

diff --git a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/planner/TableOperatorGenerator.java b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/planner/TableOperatorGenerator.java
index efd8323af07..c50fc0aca1f 100644
--- a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/planner/TableOperatorGenerator.java
+++ b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/planner/TableOperatorGenerator.java
@@ -239,6 +239,7 @@ import static org.apache.iotdb.db.queryengine.plan.planner.OperatorTreeGenerator
 import static org.apache.iotdb.db.queryengine.plan.planner.plan.parameter.SeriesScanOptions.updateFilterUsingTTL;
 import static org.apache.iotdb.db.queryengine.plan.relational.planner.SortOrder.ASC_NULLS_LAST;
 import static org.apache.iotdb.db.queryengine.plan.relational.planner.ir.GlobalTimePredicateExtractVisitor.isTimeColumn;
+import static org.apache.iotdb.db.queryengine.plan.relational.sql.ast.BooleanLiteral.TRUE_LITERAL;
 import static org.apache.iotdb.db.queryengine.plan.relational.type.InternalTypeManager.getTSDataType;
 import static org.apache.iotdb.db.utils.constant.SqlConstant.AVG;
 import static org.apache.iotdb.db.utils.constant.SqlConstant.COUNT;
@@ -1404,6 +1405,8 @@ public class TableOperatorGenerator extends PlanVisitor<Operator, LocalExecution
           dataTypes);
     }
 
+    semanticCheckForJoin(node);
+
     int size = node.getCriteria().size();
     int[] leftJoinKeyPositions = new int[size];
     for (int i = 0; i < size; i++) {
@@ -1491,6 +1494,21 @@ public class TableOperatorGenerator extends PlanVisitor<Operator, LocalExecution
     throw new IllegalStateException("Unsupported join type: " + node.getJoinType());
   }
 
+  private void semanticCheckForJoin(JoinNode node) {
+    try {
+      checkArgument(
+          !node.getFilter().isPresent() || node.getFilter().get().equals(TRUE_LITERAL),
+          String.format(
+              "Filter is not supported in %s. Filter is %s.",
+              node.getJoinType(), node.getFilter().get()));
+      checkArgument(
+          !node.getCriteria().isEmpty(),
+          String.format("%s must have join keys.", node.getJoinType()));
+    } catch (IllegalArgumentException e) {
+      throw new SemanticException(e.getMessage());
+    }
+  }
+
   private BiFunction<Column, Integer, Column> buildUpdateLastRowFunction(Type joinKeyType) {
     switch (joinKeyType.getTypeEnum()) {
       case INT32:
diff --git a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/RemoveRedundantExists.java b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/RemoveRedundantExists.java
new file mode 100644
index 00000000000..8af7e07af92
--- /dev/null
+++ b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/RemoveRedundantExists.java
@@ -0,0 +1,104 @@
+/*
+ * 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.planner.Assignments;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.Symbol;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.Rule;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.node.ApplyNode;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.node.ProjectNode;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.optimizations.Cardinality;
+import org.apache.iotdb.db.queryengine.plan.relational.sql.ast.Expression;
+import org.apache.iotdb.db.queryengine.plan.relational.utils.matching.Captures;
+import org.apache.iotdb.db.queryengine.plan.relational.utils.matching.Pattern;
+
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns.applyNode;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.optimizations.QueryCardinalityUtil.extractCardinality;
+import static org.apache.iotdb.db.queryengine.plan.relational.sql.ast.BooleanLiteral.FALSE_LITERAL;
+import static org.apache.iotdb.db.queryengine.plan.relational.sql.ast.BooleanLiteral.TRUE_LITERAL;
+
+/**
+ * Given:
+ *
+ * <pre>
+ * - Apply [X.*, e = EXISTS (true)]
+ *   - X
+ *   - S with cardinality >= 1
+ * </pre>
+ *
+ * <p>Produces:
+ *
+ * <pre>
+ * - Project [X.*, e = true]
+ *   - X
+ * </pre>
+ *
+ * <p>Given:
+ *
+ * <pre>
+ * - Apply [X.*, e = EXISTS (true)]
+ *   - X
+ *   - S with cardinality = 0
+ * </pre>
+ *
+ * <p>Produces:
+ *
+ * <pre>
+ * - Project [X.*, e = false]
+ *   - X
+ * </pre>
+ */
+public class RemoveRedundantExists implements Rule<ApplyNode> {
+  private static final Pattern<ApplyNode> PATTERN =
+      applyNode()
+          .matching(
+              node ->
+                  node.getSubqueryAssignments().values().stream()
+                      .allMatch(ApplyNode.Exists.class::isInstance));
+
+  @Override
+  public Pattern<ApplyNode> getPattern() {
+    return PATTERN;
+  }
+
+  @Override
+  public Result apply(ApplyNode node, Captures captures, Context context) {
+    Assignments.Builder assignments = Assignments.builder();
+    assignments.putIdentities(node.getInput().getOutputSymbols());
+
+    Cardinality subqueryCardinality = extractCardinality(node.getSubquery(), context.getLookup());
+    Expression result;
+    if (subqueryCardinality.isEmpty()) {
+      result = FALSE_LITERAL;
+    } else if (subqueryCardinality.isAtLeastScalar()) {
+      result = TRUE_LITERAL;
+    } else {
+      return Result.empty();
+    }
+
+    for (Symbol output : node.getSubqueryAssignments().keySet()) {
+      assignments.put(output, result);
+    }
+
+    return Result.ofPlanNode(
+        new ProjectNode(
+            context.getIdAllocator().genPlanNodeId(), node.getInput(), assignments.build()));
+  }
+}
diff --git a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/optimizations/JoinUtils.java b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/optimizations/JoinUtils.java
index 8d317cc09d8..80e1b586995 100644
--- a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/optimizations/JoinUtils.java
+++ b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/optimizations/JoinUtils.java
@@ -335,4 +335,163 @@ public class JoinUtils {
       return postJoinPredicate;
     }
   }
+
+  static OuterJoinPushDownResult processLimitedOuterJoin(
+      Metadata metadata,
+      Expression inheritedPredicate,
+      Expression outerEffectivePredicate,
+      Expression innerEffectivePredicate,
+      Expression joinPredicate,
+      Collection<Symbol> outerSymbols,
+      Collection<Symbol> innerSymbols) {
+    checkArgument(
+        outerSymbols.containsAll(extractUnique(outerEffectivePredicate)),
+        "outerEffectivePredicate must only contain symbols from outerSymbols");
+    checkArgument(
+        innerSymbols.containsAll(extractUnique(innerEffectivePredicate)),
+        "innerEffectivePredicate must only contain symbols from innerSymbols");
+
+    ImmutableList.Builder<Expression> outerPushdownConjuncts = ImmutableList.builder();
+    ImmutableList.Builder<Expression> innerPushdownConjuncts = ImmutableList.builder();
+    ImmutableList.Builder<Expression> postJoinConjuncts = ImmutableList.builder();
+    ImmutableList.Builder<Expression> joinConjuncts = ImmutableList.builder();
+
+    // Strip out non-deterministic conjuncts
+    extractConjuncts(inheritedPredicate).stream()
+        .filter(expression -> !isDeterministic(expression))
+        .forEach(postJoinConjuncts::add);
+    inheritedPredicate = filterDeterministicConjuncts(inheritedPredicate);
+
+    outerEffectivePredicate = filterDeterministicConjuncts(outerEffectivePredicate);
+    innerEffectivePredicate = filterDeterministicConjuncts(innerEffectivePredicate);
+    extractConjuncts(joinPredicate).stream()
+        .filter(expression -> !isDeterministic(expression))
+        .forEach(joinConjuncts::add);
+    joinPredicate = filterDeterministicConjuncts(joinPredicate);
+
+    // Generate equality inferences
+    EqualityInference inheritedInference = new EqualityInference(metadata, inheritedPredicate);
+    EqualityInference outerInference =
+        new EqualityInference(metadata, inheritedPredicate, outerEffectivePredicate);
+
+    Set<Symbol> innerScope = ImmutableSet.copyOf(innerSymbols);
+    Set<Symbol> outerScope = ImmutableSet.copyOf(outerSymbols);
+
+    EqualityInference.EqualityPartition equalityPartition =
+        inheritedInference.generateEqualitiesPartitionedBy(outerScope);
+    Expression outerOnlyInheritedEqualities =
+        combineConjuncts(equalityPartition.getScopeEqualities());
+    EqualityInference potentialNullSymbolInference =
+        new EqualityInference(
+            metadata,
+            outerOnlyInheritedEqualities,
+            outerEffectivePredicate,
+            innerEffectivePredicate,
+            joinPredicate);
+
+    // Push outer and join equalities into the inner side. For example:
+    // SELECT * FROM nation LEFT OUTER JOIN region ON nation.regionkey = region.regionkey and
+    // nation.name = region.name WHERE nation.name = 'blah'
+
+    EqualityInference potentialNullSymbolInferenceWithoutInnerInferred =
+        new EqualityInference(
+            metadata, outerOnlyInheritedEqualities, outerEffectivePredicate, joinPredicate);
+    innerPushdownConjuncts.addAll(
+        potentialNullSymbolInferenceWithoutInnerInferred
+            .generateEqualitiesPartitionedBy(innerScope)
+            .getScopeEqualities());
+
+    // TODO: we can further improve simplifying the equalities by considering other relationships
+    // from the outer side
+    EqualityInference.EqualityPartition joinEqualityPartition =
+        new EqualityInference(metadata, joinPredicate).generateEqualitiesPartitionedBy(innerScope);
+    innerPushdownConjuncts.addAll(joinEqualityPartition.getScopeEqualities());
+    joinConjuncts
+        .addAll(joinEqualityPartition.getScopeComplementEqualities())
+        .addAll(joinEqualityPartition.getScopeStraddlingEqualities());
+
+    // Add the equalities from the inferences back in
+    outerPushdownConjuncts.addAll(equalityPartition.getScopeEqualities());
+    postJoinConjuncts.addAll(equalityPartition.getScopeComplementEqualities());
+    postJoinConjuncts.addAll(equalityPartition.getScopeStraddlingEqualities());
+
+    // See if we can push inherited predicates down
+    EqualityInference.nonInferrableConjuncts(metadata, inheritedPredicate)
+        .forEach(
+            conjunct -> {
+              Expression outerRewritten = outerInference.rewrite(conjunct, outerScope);
+              if (outerRewritten != null) {
+                outerPushdownConjuncts.add(outerRewritten);
+
+                // A conjunct can only be pushed down into an inner side if it can be rewritten in
+                // terms of the outer side
+                Expression innerRewritten =
+                    potentialNullSymbolInference.rewrite(outerRewritten, innerScope);
+                if (innerRewritten != null) {
+                  innerPushdownConjuncts.add(innerRewritten);
+                }
+              } else {
+                postJoinConjuncts.add(conjunct);
+              }
+            });
+
+    // See if we can push down any outer effective predicates to the inner side
+    EqualityInference.nonInferrableConjuncts(metadata, outerEffectivePredicate)
+        .map(conjunct -> potentialNullSymbolInference.rewrite(conjunct, innerScope))
+        .filter(Objects::nonNull)
+        .forEach(innerPushdownConjuncts::add);
+
+    // See if we can push down join predicates to the inner side
+    EqualityInference.nonInferrableConjuncts(metadata, joinPredicate)
+        .forEach(
+            conjunct -> {
+              Expression innerRewritten =
+                  potentialNullSymbolInference.rewrite(conjunct, innerScope);
+              if (innerRewritten != null) {
+                innerPushdownConjuncts.add(innerRewritten);
+              } else {
+                joinConjuncts.add(conjunct);
+              }
+            });
+
+    return new OuterJoinPushDownResult(
+        combineConjuncts(outerPushdownConjuncts.build()),
+        combineConjuncts(innerPushdownConjuncts.build()),
+        combineConjuncts(joinConjuncts.build()),
+        combineConjuncts(postJoinConjuncts.build()));
+  }
+
+  static class OuterJoinPushDownResult {
+    private final Expression outerJoinPredicate;
+    private final Expression innerJoinPredicate;
+    private final Expression joinPredicate;
+    private final Expression postJoinPredicate;
+
+    private OuterJoinPushDownResult(
+        Expression outerJoinPredicate,
+        Expression innerJoinPredicate,
+        Expression joinPredicate,
+        Expression postJoinPredicate) {
+      this.outerJoinPredicate = outerJoinPredicate;
+      this.innerJoinPredicate = innerJoinPredicate;
+      this.joinPredicate = joinPredicate;
+      this.postJoinPredicate = postJoinPredicate;
+    }
+
+    public Expression getOuterJoinPredicate() {
+      return outerJoinPredicate;
+    }
+
+    public Expression getInnerJoinPredicate() {
+      return innerJoinPredicate;
+    }
+
+    public Expression getJoinPredicate() {
+      return joinPredicate;
+    }
+
+    public Expression getPostJoinPredicate() {
+      return postJoinPredicate;
+    }
+  }
 }
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 ca27877079d..834fa51d281 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
@@ -56,6 +56,7 @@ import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.Pu
 import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.PushLimitThroughProject;
 import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.RemoveDuplicateConditions;
 import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.RemoveRedundantEnforceSingleRowNode;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.RemoveRedundantExists;
 import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.RemoveRedundantIdentityProjections;
 import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.RemoveTrivialFilters;
 import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.RemoveUnreferencedScalarApplyNodes;
@@ -183,8 +184,8 @@ public class LogicalOptimizeFactory {
                         //                        new ReplaceRedundantJoinWithSource(),
                         //                        new RemoveRedundantJoin(),
                         //                        new ReplaceRedundantJoinWithProject(),
-                        new RemoveRedundantEnforceSingleRowNode()
-                        //                        new RemoveRedundantExists(),
+                        new RemoveRedundantEnforceSingleRowNode(),
+                        new RemoveRedundantExists()
                         //                        new RemoveRedundantWindow(),
                         //                        new SingleDistinctAggregationToGroupBy(),
                         //                        new MergeLimitWithDistinct(),
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 286c53d91b7..629dfb59fd0 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
@@ -105,6 +105,7 @@ import static org.apache.iotdb.db.queryengine.plan.relational.planner.optimizati
 import static org.apache.iotdb.db.queryengine.plan.relational.planner.optimizations.JoinUtils.extractJoinPredicate;
 import static org.apache.iotdb.db.queryengine.plan.relational.planner.optimizations.JoinUtils.joinEqualityExpression;
 import static org.apache.iotdb.db.queryengine.plan.relational.planner.optimizations.JoinUtils.processInnerJoin;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.optimizations.JoinUtils.processLimitedOuterJoin;
 import static org.apache.iotdb.db.queryengine.plan.relational.planner.optimizations.QueryCardinalityUtil.extractCardinality;
 import static org.apache.iotdb.db.queryengine.plan.relational.sql.ast.BooleanLiteral.TRUE_LITERAL;
 import static org.apache.iotdb.db.queryengine.plan.relational.sql.ast.ComparisonExpression.Operator.EQUAL;
@@ -578,6 +579,21 @@ public class PushPredicateIntoTableScan implements PlanOptimizer {
           postJoinPredicate = innerJoinPushDownResult.getPostJoinPredicate();
           newJoinPredicate = innerJoinPushDownResult.getJoinPredicate();
           break;
+        case LEFT:
+          JoinUtils.OuterJoinPushDownResult leftOuterJoinPushDownResult =
+              processLimitedOuterJoin(
+                  metadata,
+                  inheritedPredicate,
+                  leftEffectivePredicate,
+                  rightEffectivePredicate,
+                  joinPredicate,
+                  node.getLeftChild().getOutputSymbols(),
+                  node.getRightChild().getOutputSymbols());
+          leftPredicate = leftOuterJoinPushDownResult.getOuterJoinPredicate();
+          rightPredicate = leftOuterJoinPushDownResult.getInnerJoinPredicate();
+          postJoinPredicate = leftOuterJoinPushDownResult.getPostJoinPredicate();
+          newJoinPredicate = leftOuterJoinPushDownResult.getJoinPredicate();
+          break;
         case FULL:
           leftPredicate = TRUE_LITERAL;
           rightPredicate = TRUE_LITERAL;
@@ -585,8 +601,8 @@ public class PushPredicateIntoTableScan implements PlanOptimizer {
           newJoinPredicate = joinPredicate;
           break;
         default:
-          throw new IllegalStateException(
-              "Only support INNER JOIN and FULL OUTER JOIN in current version");
+          throw new IllegalArgumentException(
+              "Unsupported join type in predicate push down: " + node.getJoinType().name());
       }
 
       // newJoinPredicate = simplifyExpression(newJoinPredicate);


(iotdb) 04/08: add correlated agg related rules

Posted by la...@apache.org.
This is an automated email from the ASF dual-hosted git repository.

lancelly pushed a commit to branch support_exists_and_correlate
in repository https://gitbox.apache.org/repos/asf/iotdb.git

commit dc4cd7bbb21700d28fb1a2da448b9263276f1d04
Author: lancelly <14...@qq.com>
AuthorDate: Fri Jan 10 22:24:52 2025 +0800

    add correlated agg related rules
---
 .../iterative/rule/AggregationDecorrelation.java   |  83 ++++++
 ...orrelatedDistinctAggregationWithProjection.java | 186 ++++++++++++
 ...elatedDistinctAggregationWithoutProjection.java | 167 +++++++++++
 ...mCorrelatedGlobalAggregationWithProjection.java | 318 +++++++++++++++++++++
 ...rrelatedGlobalAggregationWithoutProjection.java | 304 ++++++++++++++++++++
 ...CorrelatedGroupedAggregationWithProjection.java | 252 ++++++++++++++++
 ...relatedGroupedAggregationWithoutProjection.java | 233 +++++++++++++++
 .../rule/TransformCorrelatedJoinToJoin.java        |  99 +++++++
 .../plan/relational/planner/node/Patterns.java     |  19 +-
 .../optimizations/LogicalOptimizeFactory.java      |  30 +-
 10 files changed, 1678 insertions(+), 13 deletions(-)

diff --git a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/AggregationDecorrelation.java b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/AggregationDecorrelation.java
new file mode 100644
index 00000000000..e61bf6f7be2
--- /dev/null
+++ b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/AggregationDecorrelation.java
@@ -0,0 +1,83 @@
+/*
+ * 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.planner.plan.node.PlanNode;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.Symbol;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.node.AggregationNode;
+
+import com.google.common.collect.ImmutableList;
+import com.google.common.collect.ImmutableMap;
+
+import java.util.List;
+import java.util.Map;
+import java.util.Optional;
+
+import static com.google.common.base.Preconditions.checkArgument;
+
+class AggregationDecorrelation {
+  private AggregationDecorrelation() {}
+
+  public static boolean isDistinctOperator(PlanNode node) {
+    return node instanceof AggregationNode
+        && ((AggregationNode) node).getAggregations().isEmpty()
+        && ((AggregationNode) node).getGroupingSetCount() == 1
+        && ((AggregationNode) node).hasNonEmptyGroupingSet();
+  }
+
+  public static Map<Symbol, AggregationNode.Aggregation> rewriteWithMasks(
+      Map<Symbol, AggregationNode.Aggregation> aggregations, Map<Symbol, Symbol> masks) {
+    ImmutableMap.Builder<Symbol, AggregationNode.Aggregation> rewritten = ImmutableMap.builder();
+    for (Map.Entry<Symbol, AggregationNode.Aggregation> entry : aggregations.entrySet()) {
+      Symbol symbol = entry.getKey();
+      AggregationNode.Aggregation aggregation = entry.getValue();
+      rewritten.put(
+          symbol,
+          new AggregationNode.Aggregation(
+              aggregation.getResolvedFunction(),
+              aggregation.getArguments(),
+              aggregation.isDistinct(),
+              aggregation.getFilter(),
+              aggregation.getOrderingScheme(),
+              Optional.of(masks.get(symbol))));
+    }
+
+    return rewritten.buildOrThrow();
+  }
+
+  /**
+   * Creates distinct aggregation node based on existing distinct aggregation node.
+   *
+   * @see #isDistinctOperator(PlanNode)
+   */
+  public static AggregationNode restoreDistinctAggregation(
+      AggregationNode distinct, PlanNode source, List<Symbol> groupingKeys) {
+    checkArgument(isDistinctOperator(distinct));
+    return new AggregationNode(
+        distinct.getPlanNodeId(),
+        source,
+        ImmutableMap.of(),
+        AggregationNode.singleGroupingSet(groupingKeys),
+        ImmutableList.of(),
+        distinct.getStep(),
+        Optional.empty(),
+        Optional.empty());
+  }
+}
diff --git a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/TransformCorrelatedDistinctAggregationWithProjection.java b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/TransformCorrelatedDistinctAggregationWithProjection.java
new file mode 100644
index 00000000000..9e9423fea4e
--- /dev/null
+++ b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/TransformCorrelatedDistinctAggregationWithProjection.java
@@ -0,0 +1,186 @@
+/*
+ * 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.planner.plan.node.PlanNode;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.Assignments;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.PlannerContext;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.Symbol;
+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.AssignUniqueId;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.node.CorrelatedJoinNode;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.node.JoinNode;
+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.planner.optimizations.PlanNodeDecorrelator;
+import org.apache.iotdb.db.queryengine.plan.relational.utils.matching.Capture;
+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 java.util.HashSet;
+import java.util.List;
+import java.util.Optional;
+import java.util.Set;
+
+import static com.google.common.collect.ImmutableList.toImmutableList;
+import static java.util.Objects.requireNonNull;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.AggregationNode.singleGroupingSet;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.JoinNode.JoinType.LEFT;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns.CorrelatedJoin.filter;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns.CorrelatedJoin.subquery;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns.CorrelatedJoin.type;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns.aggregation;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns.correlatedJoin;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns.project;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns.source;
+import static org.apache.iotdb.db.queryengine.plan.relational.sql.ast.BooleanLiteral.TRUE_LITERAL;
+import static org.apache.iotdb.db.queryengine.plan.relational.utils.matching.Capture.newCapture;
+import static org.apache.iotdb.db.queryengine.plan.relational.utils.matching.Pattern.nonEmpty;
+
+/**
+ * This rule decorrelates a correlated subquery of LEFT correlated join with distinct operator
+ * (grouped aggregation with no aggregation assignments)
+ *
+ * <p>Transforms:
+ *
+ * <pre>
+ * - CorrelatedJoin LEFT (correlation: [c], filter: true, output: a, x)
+ *      - Input (a, c)
+ *      - Project (x <- b + 100)
+ *           - Aggregation "distinct operator" group by [b]
+ *                - Source (b) with correlated filter (b > c)
+ * </pre>
+ *
+ * Into:
+ *
+ * <pre>
+ * - Project (a <- a, x <- b + 100)
+ *      - Aggregation "distinct operator" group by [a, c, unique, b]
+ *           - LEFT join (filter: b > c)
+ *                - UniqueId (unique)
+ *                     - Input (a, c)
+ *                - Source (b) decorrelated
+ * </pre>
+ */
+public class TransformCorrelatedDistinctAggregationWithProjection
+    implements Rule<CorrelatedJoinNode> {
+  private static final Capture<ProjectNode> PROJECTION = newCapture();
+  private static final Capture<AggregationNode> AGGREGATION = newCapture();
+
+  private static final Pattern<CorrelatedJoinNode> PATTERN =
+      correlatedJoin()
+          .with(type().equalTo(LEFT))
+          .with(nonEmpty(Patterns.CorrelatedJoin.correlation()))
+          .with(filter().equalTo(TRUE_LITERAL))
+          .with(
+              subquery()
+                  .matching(
+                      project()
+                          .capturedAs(PROJECTION)
+                          .with(
+                              source()
+                                  .matching(
+                                      aggregation()
+                                          .matching(AggregationDecorrelation::isDistinctOperator)
+                                          .capturedAs(AGGREGATION)))));
+
+  private final PlannerContext plannerContext;
+
+  public TransformCorrelatedDistinctAggregationWithProjection(PlannerContext plannerContext) {
+    this.plannerContext = requireNonNull(plannerContext, "plannerContext is null");
+  }
+
+  @Override
+  public Pattern<CorrelatedJoinNode> getPattern() {
+    return PATTERN;
+  }
+
+  @Override
+  public Result apply(CorrelatedJoinNode correlatedJoinNode, Captures captures, Context context) {
+    // decorrelate nested plan
+    PlanNodeDecorrelator decorrelator =
+        new PlanNodeDecorrelator(plannerContext, context.getSymbolAllocator(), context.getLookup());
+    Optional<PlanNodeDecorrelator.DecorrelatedNode> decorrelatedSource =
+        decorrelator.decorrelateFilters(
+            captures.get(AGGREGATION).getChild(), correlatedJoinNode.getCorrelation());
+    if (!decorrelatedSource.isPresent()) {
+      return Result.empty();
+    }
+
+    PlanNode source = decorrelatedSource.get().getNode();
+
+    // assign unique id on correlated join's input. It will be used to distinguish between original
+    // input rows after join
+    PlanNode inputWithUniqueId =
+        new AssignUniqueId(
+            context.getIdAllocator().genPlanNodeId(),
+            correlatedJoinNode.getInput(),
+            context.getSymbolAllocator().newSymbol("unique", LongType.getInstance()));
+
+    JoinNode join =
+        new JoinNode(
+            context.getIdAllocator().genPlanNodeId(),
+            JoinNode.JoinType.LEFT,
+            inputWithUniqueId,
+            source,
+            ImmutableList.of(),
+            inputWithUniqueId.getOutputSymbols(),
+            source.getOutputSymbols(),
+            decorrelatedSource.get().getCorrelatedPredicates(),
+            Optional.empty());
+
+    // restore aggregation
+    AggregationNode aggregation = captures.get(AGGREGATION);
+    aggregation =
+        new AggregationNode(
+            aggregation.getPlanNodeId(),
+            join,
+            aggregation.getAggregations(),
+            singleGroupingSet(
+                ImmutableList.<Symbol>builder()
+                    .addAll(join.getLeftOutputSymbols())
+                    .addAll(aggregation.getGroupingKeys())
+                    .build()),
+            ImmutableList.of(),
+            aggregation.getStep(),
+            Optional.empty(),
+            Optional.empty());
+
+    // restrict outputs and apply projection
+    Set<Symbol> outputSymbols = new HashSet<>(correlatedJoinNode.getOutputSymbols());
+    List<Symbol> expectedAggregationOutputs =
+        aggregation.getOutputSymbols().stream()
+            .filter(outputSymbols::contains)
+            .collect(toImmutableList());
+
+    Assignments assignments =
+        Assignments.builder()
+            .putIdentities(expectedAggregationOutputs)
+            .putAll(captures.get(PROJECTION).getAssignments())
+            .build();
+
+    return Result.ofPlanNode(
+        new ProjectNode(context.getIdAllocator().genPlanNodeId(), aggregation, assignments));
+  }
+}
diff --git a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/TransformCorrelatedDistinctAggregationWithoutProjection.java b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/TransformCorrelatedDistinctAggregationWithoutProjection.java
new file mode 100644
index 00000000000..9625736a8f3
--- /dev/null
+++ b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/TransformCorrelatedDistinctAggregationWithoutProjection.java
@@ -0,0 +1,167 @@
+/*
+ * 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.planner.plan.node.PlanNode;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.PlannerContext;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.Symbol;
+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.AssignUniqueId;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.node.CorrelatedJoinNode;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.node.JoinNode;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.optimizations.PlanNodeDecorrelator;
+import org.apache.iotdb.db.queryengine.plan.relational.utils.matching.Capture;
+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 com.google.common.collect.ImmutableSet;
+import org.apache.tsfile.read.common.type.BooleanType;
+
+import java.util.Optional;
+
+import static java.util.Objects.requireNonNull;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.Util.restrictOutputs;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.AggregationNode.singleGroupingSet;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.JoinNode.JoinType.LEFT;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns.CorrelatedJoin.filter;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns.CorrelatedJoin.subquery;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns.CorrelatedJoin.type;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns.aggregation;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns.correlatedJoin;
+import static org.apache.iotdb.db.queryengine.plan.relational.sql.ast.BooleanLiteral.TRUE_LITERAL;
+import static org.apache.iotdb.db.queryengine.plan.relational.utils.matching.Capture.newCapture;
+import static org.apache.iotdb.db.queryengine.plan.relational.utils.matching.Pattern.nonEmpty;
+
+/**
+ * This rule decorrelates a correlated subquery of LEFT correlated join with distinct operator
+ * (grouped aggregation with no aggregation assignments) It is similar to
+ * TransformCorrelatedDistinctAggregationWithProjection rule, but does not support projection over
+ * aggregation in the subquery
+ *
+ * <p>Transforms:
+ *
+ * <pre>
+ * - CorrelatedJoin LEFT (correlation: [c], filter: true, output: a, b)
+ *      - Input (a, c)
+ *      - Aggregation "distinct operator" group by [b]
+ *           - Source (b) with correlated filter (b > c)
+ * </pre>
+ *
+ * Into:
+ *
+ * <pre>
+ * - Project (a <- a, b <- b)
+ *      - Aggregation "distinct operator" group by [a, c, unique, b]
+ *           - LEFT join (filter: b > c)
+ *                - UniqueId (unique)
+ *                     - Input (a, c)
+ *                - Source (b) decorrelated
+ * </pre>
+ */
+public class TransformCorrelatedDistinctAggregationWithoutProjection
+    implements Rule<CorrelatedJoinNode> {
+  private static final Capture<AggregationNode> AGGREGATION = newCapture();
+
+  private static final Pattern<CorrelatedJoinNode> PATTERN =
+      correlatedJoin()
+          .with(type().equalTo(LEFT))
+          .with(nonEmpty(Patterns.CorrelatedJoin.correlation()))
+          .with(filter().equalTo(TRUE_LITERAL))
+          .with(
+              subquery()
+                  .matching(
+                      aggregation()
+                          .matching(AggregationDecorrelation::isDistinctOperator)
+                          .capturedAs(AGGREGATION)));
+
+  private final PlannerContext plannerContext;
+
+  public TransformCorrelatedDistinctAggregationWithoutProjection(PlannerContext plannerContext) {
+    this.plannerContext = requireNonNull(plannerContext, "plannerContext is null");
+  }
+
+  @Override
+  public Pattern<CorrelatedJoinNode> getPattern() {
+    return PATTERN;
+  }
+
+  @Override
+  public Result apply(CorrelatedJoinNode correlatedJoinNode, Captures captures, Context context) {
+    // decorrelate nested plan
+    PlanNodeDecorrelator decorrelator =
+        new PlanNodeDecorrelator(plannerContext, context.getSymbolAllocator(), context.getLookup());
+    Optional<PlanNodeDecorrelator.DecorrelatedNode> decorrelatedSource =
+        decorrelator.decorrelateFilters(
+            captures.get(AGGREGATION).getChild(), correlatedJoinNode.getCorrelation());
+    if (!decorrelatedSource.isPresent()) {
+      return Result.empty();
+    }
+
+    PlanNode source = decorrelatedSource.get().getNode();
+
+    // assign unique id on correlated join's input. It will be used to distinguish between original
+    // input rows after join
+    PlanNode inputWithUniqueId =
+        new AssignUniqueId(
+            context.getIdAllocator().genPlanNodeId(),
+            correlatedJoinNode.getInput(),
+            context.getSymbolAllocator().newSymbol("unique", BooleanType.getInstance()));
+
+    JoinNode join =
+        new JoinNode(
+            context.getIdAllocator().genPlanNodeId(),
+            JoinNode.JoinType.LEFT,
+            inputWithUniqueId,
+            source,
+            ImmutableList.of(),
+            inputWithUniqueId.getOutputSymbols(),
+            source.getOutputSymbols(),
+            decorrelatedSource.get().getCorrelatedPredicates(),
+            Optional.empty());
+
+    // restore aggregation
+    AggregationNode aggregation = captures.get(AGGREGATION);
+    aggregation =
+        AggregationNode.builderFrom(aggregation)
+            .setSource(join)
+            .setGroupingSets(
+                singleGroupingSet(
+                    ImmutableList.<Symbol>builder()
+                        .addAll(join.getLeftOutputSymbols())
+                        .addAll(aggregation.getGroupingKeys())
+                        .build()))
+            .setPreGroupedSymbols(ImmutableList.of())
+            .setHashSymbol(Optional.empty())
+            .setGroupIdSymbol(Optional.empty())
+            .build();
+
+    // restrict outputs
+    Optional<PlanNode> project =
+        restrictOutputs(
+            context.getIdAllocator(),
+            aggregation,
+            ImmutableSet.copyOf(correlatedJoinNode.getOutputSymbols()));
+
+    return Result.ofPlanNode(project.orElse(aggregation));
+  }
+}
diff --git a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/TransformCorrelatedGlobalAggregationWithProjection.java b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/TransformCorrelatedGlobalAggregationWithProjection.java
new file mode 100644
index 00000000000..021de9fe9bd
--- /dev/null
+++ b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/TransformCorrelatedGlobalAggregationWithProjection.java
@@ -0,0 +1,318 @@
+/*
+ * 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.planner.plan.node.PlanNode;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.Assignments;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.PlannerContext;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.Symbol;
+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.AssignUniqueId;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.node.CorrelatedJoinNode;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.node.JoinNode;
+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.planner.optimizations.PlanNodeDecorrelator;
+import org.apache.iotdb.db.queryengine.plan.relational.sql.ast.Expression;
+import org.apache.iotdb.db.queryengine.plan.relational.utils.matching.Capture;
+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 com.google.common.collect.ImmutableMap;
+import org.apache.tsfile.read.common.type.BooleanType;
+import org.apache.tsfile.read.common.type.LongType;
+
+import java.util.HashSet;
+import java.util.List;
+import java.util.Map;
+import java.util.Optional;
+import java.util.Set;
+
+import static com.google.common.base.Preconditions.checkArgument;
+import static com.google.common.collect.ImmutableList.toImmutableList;
+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.planner.iterative.rule.AggregationDecorrelation.isDistinctOperator;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.AggregationDecorrelation.restoreDistinctAggregation;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.AggregationDecorrelation.rewriteWithMasks;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.AggregationNode.singleGroupingSet;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.JoinNode.JoinType.INNER;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.JoinNode.JoinType.LEFT;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns.Aggregation.groupingColumns;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns.CorrelatedJoin.filter;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns.CorrelatedJoin.subquery;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns.aggregation;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns.correlatedJoin;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns.project;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns.source;
+import static org.apache.iotdb.db.queryengine.plan.relational.sql.ast.BooleanLiteral.TRUE_LITERAL;
+import static org.apache.iotdb.db.queryengine.plan.relational.utils.matching.Capture.newCapture;
+import static org.apache.iotdb.db.queryengine.plan.relational.utils.matching.Pattern.empty;
+import static org.apache.iotdb.db.queryengine.plan.relational.utils.matching.Pattern.nonEmpty;
+
+/**
+ * This rule decorrelates a correlated subquery of LEFT or INNER correlated join with: - single
+ * global aggregation, or - global aggregation over distinct operator (grouped aggregation with no
+ * aggregation assignments), in case when the distinct operator cannot be de-correlated by
+ * PlanNodeDecorrelator
+ *
+ * <p>In the case of single aggregation, it transforms:
+ *
+ * <pre>
+ * - CorrelatedJoin LEFT or INNER (correlation: [c], filter: true, output: a, x, y)
+ *      - Input (a, c)
+ *      - Project (x <- f(count), y <- f'(agg))
+ *           - Aggregation global
+ *             count <- count(*)
+ *             agg <- agg(b)
+ *                - Source (b) with correlated filter (b > c)
+ * </pre>
+ *
+ * Into:
+ *
+ * <pre>
+ * - Project (a <- a, x <- f(count), y <- f'(agg))
+ *      - Aggregation (group by [a, c, unique])
+ *        count <- count(*) mask(non_null)
+ *        agg <- agg(b) mask(non_null)
+ *           - LEFT join (filter: b > c)
+ *                - UniqueId (unique)
+ *                     - Input (a, c)
+ *                - Project (non_null <- TRUE)
+ *                     - Source (b) decorrelated
+ * </pre>
+ *
+ * <p>In the case of global aggregation over distinct operator, it transforms:
+ *
+ * <pre>
+ * - CorrelatedJoin LEFT or INNER (correlation: [c], filter: true, output: a, x, y)
+ *      - Input (a, c)
+ *      - Project (x <- f(count), y <- f'(agg))
+ *           - Aggregation global
+ *             count <- count(*)
+ *             agg <- agg(b)
+ *                - Aggregation "distinct operator" group by [b]
+ *                     - Source (b) with correlated filter (b > c)
+ * </pre>
+ *
+ * Into:
+ *
+ * <pre>
+ * - Project (a <- a, x <- f(count), y <- f'(agg))
+ *      - Aggregation (group by [a, c, unique])
+ *        count <- count(*) mask(non_null)
+ *        agg <- agg(b) mask(non_null)
+ *           - Aggregation "distinct operator" group by [a, c, unique, non_null, b]
+ *                - LEFT join (filter: b > c)
+ *                     - UniqueId (unique)
+ *                          - Input (a, c)
+ *                     - Project (non_null <- TRUE)
+ *                          - Source (b) decorrelated
+ * </pre>
+ */
+public class TransformCorrelatedGlobalAggregationWithProjection
+    implements Rule<CorrelatedJoinNode> {
+  private static final Capture<ProjectNode> PROJECTION = newCapture();
+  private static final Capture<AggregationNode> AGGREGATION = newCapture();
+  private static final Capture<PlanNode> SOURCE = newCapture();
+
+  private static final Pattern<CorrelatedJoinNode> PATTERN =
+      correlatedJoin()
+          .with(nonEmpty(Patterns.CorrelatedJoin.correlation()))
+          .with(filter().equalTo(TRUE_LITERAL))
+          .with(
+              subquery()
+                  .matching(
+                      project()
+                          .capturedAs(PROJECTION)
+                          .with(
+                              source()
+                                  .matching(
+                                      aggregation()
+                                          .with(empty(groupingColumns()))
+                                          .with(source().capturedAs(SOURCE))
+                                          .capturedAs(AGGREGATION)))));
+
+  private final PlannerContext plannerContext;
+
+  public TransformCorrelatedGlobalAggregationWithProjection(PlannerContext plannerContext) {
+    this.plannerContext = requireNonNull(plannerContext, "plannerContext is null");
+  }
+
+  @Override
+  public Pattern<CorrelatedJoinNode> getPattern() {
+    return PATTERN;
+  }
+
+  @Override
+  public Result apply(CorrelatedJoinNode correlatedJoinNode, Captures captures, Context context) {
+    checkArgument(
+        correlatedJoinNode.getJoinType() == INNER || correlatedJoinNode.getJoinType() == LEFT,
+        "unexpected correlated join type: %s",
+        correlatedJoinNode.getJoinType());
+
+    // if there is another aggregation below the AggregationNode, handle both
+    PlanNode source = captures.get(SOURCE);
+
+    // if we fail to decorrelate the nested plan, and it contains a distinct operator, we can
+    // extract and special-handle the distinct operator
+    AggregationNode distinct = null;
+
+    // decorrelate nested plan
+    PlanNodeDecorrelator decorrelator =
+        new PlanNodeDecorrelator(plannerContext, context.getSymbolAllocator(), context.getLookup());
+    Optional<PlanNodeDecorrelator.DecorrelatedNode> decorrelatedSource =
+        decorrelator.decorrelateFilters(source, correlatedJoinNode.getCorrelation());
+    if (!decorrelatedSource.isPresent()) {
+      // we failed to decorrelate the nested plan, so check if we can extract a distinct operator
+      // from the nested plan
+      if (isDistinctOperator(source)) {
+        distinct = (AggregationNode) source;
+        source = distinct.getChild();
+        decorrelatedSource =
+            decorrelator.decorrelateFilters(source, correlatedJoinNode.getCorrelation());
+      }
+      if (!decorrelatedSource.isPresent()) {
+        return Result.empty();
+      }
+    }
+
+    source = decorrelatedSource.get().getNode();
+
+    // append non-null symbol on nested plan. It will be used to restore semantics of null-sensitive
+    // aggregations after LEFT join
+    Symbol nonNull = context.getSymbolAllocator().newSymbol("non_null", BooleanType.getInstance());
+    source =
+        new ProjectNode(
+            context.getIdAllocator().genPlanNodeId(),
+            source,
+            Assignments.builder()
+                .putIdentities(source.getOutputSymbols())
+                .put(nonNull, TRUE_LITERAL)
+                .build());
+
+    // assign unique id on correlated join's input. It will be used to distinguish between original
+    // input rows after join
+    PlanNode inputWithUniqueId =
+        new AssignUniqueId(
+            context.getIdAllocator().genPlanNodeId(),
+            correlatedJoinNode.getInput(),
+            context.getSymbolAllocator().newSymbol("unique", LongType.getInstance()));
+
+    JoinNode join =
+        new JoinNode(
+            context.getIdAllocator().genPlanNodeId(),
+            JoinNode.JoinType.LEFT,
+            inputWithUniqueId,
+            source,
+            ImmutableList.of(),
+            inputWithUniqueId.getOutputSymbols(),
+            source.getOutputSymbols(),
+            decorrelatedSource.get().getCorrelatedPredicates(),
+            Optional.empty());
+
+    PlanNode root = join;
+
+    // restore distinct aggregation
+    if (distinct != null) {
+      root =
+          restoreDistinctAggregation(
+              distinct,
+              join,
+              ImmutableList.<Symbol>builder()
+                  .addAll(join.getLeftOutputSymbols())
+                  .add(nonNull)
+                  .addAll(distinct.getGroupingKeys())
+                  .build());
+    }
+
+    // prepare mask symbols for aggregations
+    // Every original aggregation agg() will be rewritten to agg() mask(non_null). If the
+    // aggregation
+    // already has a mask, it will be replaced with conjunction of the existing mask and non_null.
+    // This is necessary to restore the original aggregation result in case when:
+    // - the nested lateral subquery returned empty result for some input row,
+    // - aggregation is null-sensitive, which means that its result over a single null row is
+    // different
+    //   than result for empty input (with global grouping)
+    // It applies to the following aggregate functions: count(*), checksum(), array_agg().
+    AggregationNode globalAggregation = captures.get(AGGREGATION);
+    ImmutableMap.Builder<Symbol, Symbol> masks = ImmutableMap.builder();
+    Assignments.Builder assignmentsBuilder = Assignments.builder();
+    for (Map.Entry<Symbol, AggregationNode.Aggregation> entry :
+        globalAggregation.getAggregations().entrySet()) {
+      AggregationNode.Aggregation aggregation = entry.getValue();
+      if (aggregation.getMask().isPresent()) {
+        Symbol newMask = context.getSymbolAllocator().newSymbol("mask", BooleanType.getInstance());
+        Expression expression =
+            and(aggregation.getMask().get().toSymbolReference(), nonNull.toSymbolReference());
+        assignmentsBuilder.put(newMask, expression);
+        masks.put(entry.getKey(), newMask);
+      } else {
+        masks.put(entry.getKey(), nonNull);
+      }
+    }
+    Assignments maskAssignments = assignmentsBuilder.build();
+    if (!maskAssignments.isEmpty()) {
+      root =
+          new ProjectNode(
+              context.getIdAllocator().genPlanNodeId(),
+              root,
+              Assignments.builder()
+                  .putIdentities(root.getOutputSymbols())
+                  .putAll(maskAssignments)
+                  .build());
+    }
+
+    // restore global aggregation
+    globalAggregation =
+        new AggregationNode(
+            globalAggregation.getPlanNodeId(),
+            root,
+            rewriteWithMasks(globalAggregation.getAggregations(), masks.buildOrThrow()),
+            singleGroupingSet(
+                ImmutableList.<Symbol>builder()
+                    .addAll(join.getLeftOutputSymbols())
+                    .addAll(globalAggregation.getGroupingKeys())
+                    .build()),
+            ImmutableList.of(),
+            globalAggregation.getStep(),
+            Optional.empty(),
+            Optional.empty());
+
+    // restrict outputs and apply projection
+    Set<Symbol> outputSymbols = new HashSet<>(correlatedJoinNode.getOutputSymbols());
+    List<Symbol> expectedAggregationOutputs =
+        globalAggregation.getOutputSymbols().stream()
+            .filter(outputSymbols::contains)
+            .collect(toImmutableList());
+
+    Assignments assignments =
+        Assignments.builder()
+            .putIdentities(expectedAggregationOutputs)
+            .putAll(captures.get(PROJECTION).getAssignments())
+            .build();
+
+    return Result.ofPlanNode(
+        new ProjectNode(context.getIdAllocator().genPlanNodeId(), globalAggregation, assignments));
+  }
+}
diff --git a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/TransformCorrelatedGlobalAggregationWithoutProjection.java b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/TransformCorrelatedGlobalAggregationWithoutProjection.java
new file mode 100644
index 00000000000..ab7979f9eda
--- /dev/null
+++ b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/TransformCorrelatedGlobalAggregationWithoutProjection.java
@@ -0,0 +1,304 @@
+/*
+ * 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.planner.plan.node.PlanNode;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.Assignments;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.PlannerContext;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.Symbol;
+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.AssignUniqueId;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.node.CorrelatedJoinNode;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.node.JoinNode;
+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.planner.optimizations.PlanNodeDecorrelator;
+import org.apache.iotdb.db.queryengine.plan.relational.sql.ast.Expression;
+import org.apache.iotdb.db.queryengine.plan.relational.utils.matching.Capture;
+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 com.google.common.collect.ImmutableMap;
+import com.google.common.collect.ImmutableSet;
+import org.apache.tsfile.read.common.type.BooleanType;
+import org.apache.tsfile.read.common.type.LongType;
+
+import java.util.Map;
+import java.util.Optional;
+
+import static com.google.common.base.Preconditions.checkArgument;
+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.planner.iterative.rule.AggregationDecorrelation.isDistinctOperator;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.AggregationDecorrelation.restoreDistinctAggregation;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.AggregationDecorrelation.rewriteWithMasks;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.Util.restrictOutputs;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.AggregationNode.singleGroupingSet;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.JoinNode.JoinType.INNER;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.JoinNode.JoinType.LEFT;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns.Aggregation.groupingColumns;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns.CorrelatedJoin.filter;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns.CorrelatedJoin.subquery;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns.aggregation;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns.correlatedJoin;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns.source;
+import static org.apache.iotdb.db.queryengine.plan.relational.sql.ast.BooleanLiteral.TRUE_LITERAL;
+import static org.apache.iotdb.db.queryengine.plan.relational.utils.matching.Capture.newCapture;
+import static org.apache.iotdb.db.queryengine.plan.relational.utils.matching.Pattern.empty;
+import static org.apache.iotdb.db.queryengine.plan.relational.utils.matching.Pattern.nonEmpty;
+
+/**
+ * This rule decorrelates a correlated subquery with: - single global aggregation, or - global
+ * aggregation over distinct operator (grouped aggregation with no aggregation assignments), in case
+ * when the distinct operator cannot be de-correlated by PlanNodeDecorrelator It is similar to
+ * TransformCorrelatedGlobalAggregationWithProjection rule, but does not support projection over
+ * aggregation in the subquery
+ *
+ * <p>In the case of single aggregation, it transforms:
+ *
+ * <pre>
+ * - CorrelatedJoin LEFT or INNER (correlation: [c], filter: true, output: a, count, agg)
+ *      - Input (a, c)
+ *      - Aggregation global
+ *        count <- count(*)
+ *        agg <- agg(b)
+ *           - Source (b) with correlated filter (b > c)
+ * </pre>
+ *
+ * Into:
+ *
+ * <pre>
+ * - Project (a <- a, count <- count, agg <- agg)
+ *      - Aggregation (group by [a, c, unique])
+ *        count <- count(*) mask(non_null)
+ *        agg <- agg(b) mask(non_null)
+ *           - LEFT join (filter: b > c)
+ *                - UniqueId (unique)
+ *                     - Input (a, c)
+ *                - Project (non_null <- TRUE)
+ *                     - Source (b) decorrelated
+ * </pre>
+ *
+ * <p>In the case of global aggregation over distinct operator, it transforms:
+ *
+ * <pre>
+ * - CorrelatedJoin LEFT or INNER (correlation: [c], filter: true, output: a, count, agg)
+ *      - Input (a, c)
+ *      - Aggregation global
+ *        count <- count(*)
+ *        agg <- agg(b)
+ *           - Aggregation "distinct operator" group by [b]
+ *                - Source (b) with correlated filter (b > c)
+ * </pre>
+ *
+ * Into:
+ *
+ * <pre>
+ * - Project (a <- a, count <- count, agg <- agg)
+ *      - Aggregation (group by [a, c, unique])
+ *        count <- count(*) mask(non_null)
+ *        agg <- agg(b) mask(non_null)
+ *           - Aggregation "distinct operator" group by [a, c, unique, non_null, b]
+ *                - LEFT join (filter: b > c)
+ *                     - UniqueId (unique)
+ *                          - Input (a, c)
+ *                     - Project (non_null <- TRUE)
+ *                          - Source (b) decorrelated
+ * </pre>
+ */
+public class TransformCorrelatedGlobalAggregationWithoutProjection
+    implements Rule<CorrelatedJoinNode> {
+  private static final Capture<AggregationNode> AGGREGATION = newCapture();
+  private static final Capture<PlanNode> SOURCE = newCapture();
+
+  private static final Pattern<CorrelatedJoinNode> PATTERN =
+      correlatedJoin()
+          .with(nonEmpty(Patterns.CorrelatedJoin.correlation()))
+          .with(
+              filter()
+                  .equalTo(
+                      TRUE_LITERAL)) // todo non-trivial join filter: adding filter/project on top
+          // of aggregation
+          .with(
+              subquery()
+                  .matching(
+                      aggregation()
+                          .with(empty(groupingColumns()))
+                          .with(source().capturedAs(SOURCE))
+                          .capturedAs(AGGREGATION)));
+
+  private final PlannerContext plannerContext;
+
+  public TransformCorrelatedGlobalAggregationWithoutProjection(PlannerContext plannerContext) {
+    this.plannerContext = requireNonNull(plannerContext, "plannerContext is null");
+  }
+
+  @Override
+  public Pattern<CorrelatedJoinNode> getPattern() {
+    return PATTERN;
+  }
+
+  @Override
+  public Result apply(CorrelatedJoinNode correlatedJoinNode, Captures captures, Context context) {
+    checkArgument(
+        correlatedJoinNode.getJoinType() == INNER || correlatedJoinNode.getJoinType() == LEFT,
+        "unexpected correlated join type: %s",
+        correlatedJoinNode.getJoinType());
+
+    PlanNode source = captures.get(SOURCE);
+
+    // if we fail to decorrelate the nested plan, and it contains a distinct operator, we can
+    // extract and special-handle the distinct operator
+    AggregationNode distinct = null;
+
+    // decorrelate nested plan
+    PlanNodeDecorrelator decorrelator =
+        new PlanNodeDecorrelator(plannerContext, context.getSymbolAllocator(), context.getLookup());
+    Optional<PlanNodeDecorrelator.DecorrelatedNode> decorrelatedSource =
+        decorrelator.decorrelateFilters(source, correlatedJoinNode.getCorrelation());
+    if (!decorrelatedSource.isPresent()) {
+      // we failed to decorrelate the nested plan, so check if we can extract a distinct operator
+      // from the nested plan
+      if (isDistinctOperator(source)) {
+        distinct = (AggregationNode) source;
+        source = distinct.getChild();
+        decorrelatedSource =
+            decorrelator.decorrelateFilters(source, correlatedJoinNode.getCorrelation());
+      }
+      if (!decorrelatedSource.isPresent()) {
+        return Result.empty();
+      }
+    }
+
+    source = decorrelatedSource.get().getNode();
+
+    // append non-null symbol on nested plan. It will be used to restore semantics of null-sensitive
+    // aggregations after LEFT join
+    Symbol nonNull = context.getSymbolAllocator().newSymbol("non_null", BooleanType.getInstance());
+    source =
+        new ProjectNode(
+            context.getIdAllocator().genPlanNodeId(),
+            source,
+            Assignments.builder()
+                .putIdentities(source.getOutputSymbols())
+                .put(nonNull, TRUE_LITERAL)
+                .build());
+
+    // assign unique id on correlated join's input. It will be used to distinguish between original
+    // input rows after join
+    PlanNode inputWithUniqueId =
+        new AssignUniqueId(
+            context.getIdAllocator().genPlanNodeId(),
+            correlatedJoinNode.getInput(),
+            context.getSymbolAllocator().newSymbol("unique", LongType.getInstance()));
+
+    JoinNode join =
+        new JoinNode(
+            context.getIdAllocator().genPlanNodeId(),
+            JoinNode.JoinType.LEFT,
+            inputWithUniqueId,
+            source,
+            ImmutableList.of(),
+            inputWithUniqueId.getOutputSymbols(),
+            source.getOutputSymbols(),
+            decorrelatedSource.get().getCorrelatedPredicates(),
+            Optional.empty());
+
+    PlanNode root = join;
+
+    // restore distinct aggregation
+    if (distinct != null) {
+      root =
+          restoreDistinctAggregation(
+              distinct,
+              join,
+              ImmutableList.<Symbol>builder()
+                  .addAll(join.getLeftOutputSymbols())
+                  .add(nonNull)
+                  .addAll(distinct.getGroupingKeys())
+                  .build());
+    }
+
+    // prepare mask symbols for aggregations
+    // Every original aggregation agg() will be rewritten to agg() mask(non_null). If the
+    // aggregation
+    // already has a mask, it will be replaced with conjunction of the existing mask and non_null.
+    // This is necessary to restore the original aggregation result in case when:
+    // - the nested lateral subquery returned empty result for some input row,
+    // - aggregation is null-sensitive, which means that its result over a single null row is
+    // different
+    //   than result for empty input (with global grouping)
+    // It applies to the following aggregate functions: count(*), checksum(), array_agg().
+    AggregationNode globalAggregation = captures.get(AGGREGATION);
+    ImmutableMap.Builder<Symbol, Symbol> masks = ImmutableMap.builder();
+    Assignments.Builder assignmentsBuilder = Assignments.builder();
+    for (Map.Entry<Symbol, AggregationNode.Aggregation> entry :
+        globalAggregation.getAggregations().entrySet()) {
+      AggregationNode.Aggregation aggregation = entry.getValue();
+      if (aggregation.getMask().isPresent()) {
+        Symbol newMask = context.getSymbolAllocator().newSymbol("mask", BooleanType.getInstance());
+        Expression expression =
+            and(aggregation.getMask().get().toSymbolReference(), nonNull.toSymbolReference());
+        assignmentsBuilder.put(newMask, expression);
+        masks.put(entry.getKey(), newMask);
+      } else {
+        masks.put(entry.getKey(), nonNull);
+      }
+    }
+    Assignments maskAssignments = assignmentsBuilder.build();
+    if (!maskAssignments.isEmpty()) {
+      root =
+          new ProjectNode(
+              context.getIdAllocator().genPlanNodeId(),
+              root,
+              Assignments.builder()
+                  .putIdentities(root.getOutputSymbols())
+                  .putAll(maskAssignments)
+                  .build());
+    }
+
+    // restore global aggregation
+    globalAggregation =
+        new AggregationNode(
+            globalAggregation.getPlanNodeId(),
+            root,
+            rewriteWithMasks(globalAggregation.getAggregations(), masks.buildOrThrow()),
+            singleGroupingSet(
+                ImmutableList.<Symbol>builder()
+                    .addAll(join.getLeftOutputSymbols())
+                    .addAll(globalAggregation.getGroupingKeys())
+                    .build()),
+            ImmutableList.of(),
+            globalAggregation.getStep(),
+            Optional.empty(),
+            Optional.empty());
+
+    // restrict outputs
+    Optional<PlanNode> project =
+        restrictOutputs(
+            context.getIdAllocator(),
+            globalAggregation,
+            ImmutableSet.copyOf(correlatedJoinNode.getOutputSymbols()));
+
+    return Result.ofPlanNode(project.orElse(globalAggregation));
+  }
+}
diff --git a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/TransformCorrelatedGroupedAggregationWithProjection.java b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/TransformCorrelatedGroupedAggregationWithProjection.java
new file mode 100644
index 00000000000..70a1a4d7bc2
--- /dev/null
+++ b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/TransformCorrelatedGroupedAggregationWithProjection.java
@@ -0,0 +1,252 @@
+/*
+ * 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.planner.plan.node.PlanNode;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.Assignments;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.PlannerContext;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.Symbol;
+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.AssignUniqueId;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.node.CorrelatedJoinNode;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.node.JoinNode;
+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.planner.optimizations.PlanNodeDecorrelator;
+import org.apache.iotdb.db.queryengine.plan.relational.utils.matching.Capture;
+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.BooleanType;
+
+import java.util.HashSet;
+import java.util.List;
+import java.util.Optional;
+import java.util.Set;
+
+import static com.google.common.collect.ImmutableList.toImmutableList;
+import static java.util.Objects.requireNonNull;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.AggregationDecorrelation.isDistinctOperator;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.AggregationDecorrelation.restoreDistinctAggregation;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.AggregationNode.singleGroupingSet;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.JoinNode.JoinType.INNER;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns.Aggregation.groupingColumns;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns.CorrelatedJoin.filter;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns.CorrelatedJoin.subquery;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns.CorrelatedJoin.type;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns.aggregation;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns.correlatedJoin;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns.project;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns.source;
+import static org.apache.iotdb.db.queryengine.plan.relational.sql.ast.BooleanLiteral.TRUE_LITERAL;
+import static org.apache.iotdb.db.queryengine.plan.relational.utils.matching.Capture.newCapture;
+import static org.apache.iotdb.db.queryengine.plan.relational.utils.matching.Pattern.nonEmpty;
+
+/**
+ * This rule decorrelates a correlated subquery of INNER correlated join with: - single grouped
+ * aggregation, or - grouped aggregation over distinct operator (grouped aggregation with no
+ * aggregation assignments), in case when the distinct operator cannot be de-correlated by
+ * PlanNodeDecorrelator
+ *
+ * <p>In the case of single aggregation, it transforms:
+ *
+ * <pre>
+ * - CorrelatedJoin INNER (correlation: [c], filter: true, output: a, count, agg)
+ *      - Input (a, c)
+ *      - Project (x <- f(count), y <- f'(agg))
+ *           - Aggregation (group by b)
+ *             count <- count(*)
+ *             agg <- agg(d)
+ *                - Source (b, d) with correlated filter (b > c)
+ * </pre>
+ *
+ * Into:
+ *
+ * <pre>
+ * - Project (a <- a, x <- f(count), y <- f'(agg))
+ *      - Aggregation (group by [a, c, unique, b])
+ *        count <- count(*)
+ *        agg <- agg(d)
+ *           - INNER join (filter: b > c)
+ *                - UniqueId (unique)
+ *                     - Input (a, c)
+ *                - Source (b, d) decorrelated
+ * </pre>
+ *
+ * <p>In the case of grouped aggregation over distinct operator, it transforms:
+ *
+ * <pre>
+ * - CorrelatedJoin INNER (correlation: [c], filter: true, output: a, count, agg)
+ *      - Input (a, c)
+ *      - Project (x <- f(count), y <- f'(agg))
+ *           - Aggregation (group by b)
+ *             count <- count(*)
+ *             agg <- agg(b)
+ *                - Aggregation "distinct operator" group by [b]
+ *                     - Source (b) with correlated filter (b > c)
+ * </pre>
+ *
+ * Into:
+ *
+ * <pre>
+ * - Project (a <- a, x <- f(count), y <- f'(agg))
+ *      - Aggregation (group by [a, c, unique, b])
+ *        count <- count(*)
+ *        agg <- agg(b)
+ *           - Aggregation "distinct operator" group by [a, c, unique, b]
+ *                - INNER join (filter: b > c)
+ *                     - UniqueId (unique)
+ *                          - Input (a, c)
+ *                     - Source (b) decorrelated
+ * </pre>
+ */
+public class TransformCorrelatedGroupedAggregationWithProjection
+    implements Rule<CorrelatedJoinNode> {
+  private static final Capture<ProjectNode> PROJECTION = newCapture();
+  private static final Capture<AggregationNode> AGGREGATION = newCapture();
+  private static final Capture<PlanNode> SOURCE = newCapture();
+
+  private static final Pattern<CorrelatedJoinNode> PATTERN =
+      correlatedJoin()
+          .with(type().equalTo(INNER))
+          .with(nonEmpty(Patterns.CorrelatedJoin.correlation()))
+          .with(filter().equalTo(TRUE_LITERAL))
+          .with(
+              subquery()
+                  .matching(
+                      project()
+                          .capturedAs(PROJECTION)
+                          .with(
+                              source()
+                                  .matching(
+                                      aggregation()
+                                          .with(nonEmpty(groupingColumns()))
+                                          .matching(
+                                              aggregation -> aggregation.getGroupingSetCount() == 1)
+                                          .with(source().capturedAs(SOURCE))
+                                          .capturedAs(AGGREGATION)))));
+
+  private final PlannerContext plannerContext;
+
+  public TransformCorrelatedGroupedAggregationWithProjection(PlannerContext plannerContext) {
+    this.plannerContext = requireNonNull(plannerContext, "plannerContext is null");
+  }
+
+  @Override
+  public Pattern<CorrelatedJoinNode> getPattern() {
+    return PATTERN;
+  }
+
+  @Override
+  public Result apply(CorrelatedJoinNode correlatedJoinNode, Captures captures, Context context) {
+    PlanNode source = captures.get(SOURCE);
+
+    // if we fail to decorrelate the nested plan, and it contains a distinct operator, we can
+    // extract and special-handle the distinct operator
+    AggregationNode distinct = null;
+
+    // decorrelate nested plan
+    PlanNodeDecorrelator decorrelator =
+        new PlanNodeDecorrelator(plannerContext, context.getSymbolAllocator(), context.getLookup());
+    Optional<PlanNodeDecorrelator.DecorrelatedNode> decorrelatedSource =
+        decorrelator.decorrelateFilters(source, correlatedJoinNode.getCorrelation());
+    if (!decorrelatedSource.isPresent()) {
+      // we failed to decorrelate the nested plan, so check if we can extract a distinct operator
+      // from the nested plan
+      if (isDistinctOperator(source)) {
+        distinct = (AggregationNode) source;
+        source = distinct.getChild();
+        decorrelatedSource =
+            decorrelator.decorrelateFilters(source, correlatedJoinNode.getCorrelation());
+      }
+      if (!decorrelatedSource.isPresent()) {
+        return Result.empty();
+      }
+    }
+
+    source = decorrelatedSource.get().getNode();
+
+    // assign unique id on correlated join's input. It will be used to distinguish between original
+    // input rows after join
+    PlanNode inputWithUniqueId =
+        new AssignUniqueId(
+            context.getIdAllocator().genPlanNodeId(),
+            correlatedJoinNode.getInput(),
+            context.getSymbolAllocator().newSymbol("unique", BooleanType.getInstance()));
+
+    JoinNode join =
+        new JoinNode(
+            context.getIdAllocator().genPlanNodeId(),
+            JoinNode.JoinType.INNER,
+            inputWithUniqueId,
+            source,
+            ImmutableList.of(),
+            inputWithUniqueId.getOutputSymbols(),
+            source.getOutputSymbols(),
+            decorrelatedSource.get().getCorrelatedPredicates(),
+            Optional.empty());
+
+    // restore distinct aggregation
+    if (distinct != null) {
+      distinct =
+          restoreDistinctAggregation(
+              distinct,
+              join,
+              ImmutableList.<Symbol>builder()
+                  .addAll(join.getLeftOutputSymbols())
+                  .addAll(distinct.getGroupingKeys())
+                  .build());
+    }
+
+    // restore grouped aggregation
+    AggregationNode groupedAggregation = captures.get(AGGREGATION);
+    groupedAggregation =
+        AggregationNode.builderFrom(groupedAggregation)
+            .setSource(distinct != null ? distinct : join)
+            .setGroupingSets(
+                singleGroupingSet(
+                    ImmutableList.<Symbol>builder()
+                        .addAll(join.getLeftOutputSymbols())
+                        .addAll(groupedAggregation.getGroupingKeys())
+                        .build()))
+            .setPreGroupedSymbols(ImmutableList.of())
+            .setHashSymbol(Optional.empty())
+            .setGroupIdSymbol(Optional.empty())
+            .build();
+
+    // restrict outputs and apply projection
+    Set<Symbol> outputSymbols = new HashSet<>(correlatedJoinNode.getOutputSymbols());
+    List<Symbol> expectedAggregationOutputs =
+        groupedAggregation.getOutputSymbols().stream()
+            .filter(outputSymbols::contains)
+            .collect(toImmutableList());
+
+    Assignments assignments =
+        Assignments.builder()
+            .putIdentities(expectedAggregationOutputs)
+            .putAll(captures.get(PROJECTION).getAssignments())
+            .build();
+
+    return Result.ofPlanNode(
+        new ProjectNode(context.getIdAllocator().genPlanNodeId(), groupedAggregation, assignments));
+  }
+}
diff --git a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/TransformCorrelatedGroupedAggregationWithoutProjection.java b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/TransformCorrelatedGroupedAggregationWithoutProjection.java
new file mode 100644
index 00000000000..2674632a619
--- /dev/null
+++ b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/TransformCorrelatedGroupedAggregationWithoutProjection.java
@@ -0,0 +1,233 @@
+/*
+ * 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.planner.plan.node.PlanNode;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.PlannerContext;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.Symbol;
+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.AssignUniqueId;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.node.CorrelatedJoinNode;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.node.JoinNode;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.optimizations.PlanNodeDecorrelator;
+import org.apache.iotdb.db.queryengine.plan.relational.utils.matching.Capture;
+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 com.google.common.collect.ImmutableSet;
+import org.apache.tsfile.read.common.type.BooleanType;
+
+import java.util.Optional;
+
+import static java.util.Objects.requireNonNull;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.AggregationDecorrelation.isDistinctOperator;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.AggregationDecorrelation.restoreDistinctAggregation;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.Util.restrictOutputs;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.AggregationNode.singleGroupingSet;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.JoinNode.JoinType.INNER;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns.Aggregation.groupingColumns;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns.CorrelatedJoin.filter;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns.CorrelatedJoin.subquery;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns.CorrelatedJoin.type;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns.aggregation;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns.correlatedJoin;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns.source;
+import static org.apache.iotdb.db.queryengine.plan.relational.sql.ast.BooleanLiteral.TRUE_LITERAL;
+import static org.apache.iotdb.db.queryengine.plan.relational.utils.matching.Capture.newCapture;
+import static org.apache.iotdb.db.queryengine.plan.relational.utils.matching.Pattern.nonEmpty;
+
+/**
+ * This rule decorrelates a correlated subquery of INNER correlated join with: - single grouped
+ * aggregation, or - grouped aggregation over distinct operator (grouped aggregation with no
+ * aggregation assignments), in case when the distinct operator cannot be de-correlated by
+ * PlanNodeDecorrelator It is similar to TransformCorrelatedGroupedAggregationWithProjection rule,
+ * but does not support projection over aggregation in the subquery
+ *
+ * <p>In the case of single aggregation, it transforms:
+ *
+ * <pre>
+ * - CorrelatedJoin INNER (correlation: [c], filter: true, output: a, count, agg)
+ *      - Input (a, c)
+ *      - Aggregation (group by b)
+ *        count <- count(*)
+ *        agg <- agg(d)
+ *           - Source (b, d) with correlated filter (b > c)
+ * </pre>
+ *
+ * Into:
+ *
+ * <pre>
+ * - Project (a <- a, count <- count, agg <- agg)
+ *      - Aggregation (group by [a, c, unique, b])
+ *        count <- count(*)
+ *        agg <- agg(d)
+ *           - INNER join (filter: b > c)
+ *                - UniqueId (unique)
+ *                     - Input (a, c)
+ *                - Source (b, d) decorrelated
+ * </pre>
+ *
+ * <p>In the case of grouped aggregation over distinct operator, it transforms:
+ *
+ * <pre>
+ * - CorrelatedJoin INNER (correlation: [c], filter: true, output: a, count, agg)
+ *      - Input (a, c)
+ *      - Aggregation (group by b)
+ *        count <- count(*)
+ *        agg <- agg(b)
+ *           - Aggregation "distinct operator" group by [b]
+ *                - Source (b) with correlated filter (b > c)
+ * </pre>
+ *
+ * Into:
+ *
+ * <pre>
+ * - Project (a <- a, count <- count, agg <- agg)
+ *      - Aggregation (group by [a, c, unique, b])
+ *        count <- count(*)
+ *        agg <- agg(b)
+ *           - Aggregation "distinct operator" group by [a, c, unique, b]
+ *                - INNER join (filter: b > c)
+ *                     - UniqueId (unique)
+ *                          - Input (a, c)
+ *                     - Source (b) decorrelated
+ * </pre>
+ */
+public class TransformCorrelatedGroupedAggregationWithoutProjection
+    implements Rule<CorrelatedJoinNode> {
+  private static final Capture<AggregationNode> AGGREGATION = newCapture();
+  private static final Capture<PlanNode> SOURCE = newCapture();
+
+  private static final Pattern<CorrelatedJoinNode> PATTERN =
+      correlatedJoin()
+          .with(type().equalTo(INNER))
+          .with(nonEmpty(Patterns.CorrelatedJoin.correlation()))
+          .with(filter().equalTo(TRUE_LITERAL))
+          .with(
+              subquery()
+                  .matching(
+                      aggregation()
+                          .with(nonEmpty(groupingColumns()))
+                          .matching(aggregation -> aggregation.getGroupingSetCount() == 1)
+                          .with(source().capturedAs(SOURCE))
+                          .capturedAs(AGGREGATION)));
+
+  private final PlannerContext plannerContext;
+
+  public TransformCorrelatedGroupedAggregationWithoutProjection(PlannerContext plannerContext) {
+    this.plannerContext = requireNonNull(plannerContext, "plannerContext is null");
+  }
+
+  @Override
+  public Pattern<CorrelatedJoinNode> getPattern() {
+    return PATTERN;
+  }
+
+  @Override
+  public Result apply(CorrelatedJoinNode correlatedJoinNode, Captures captures, Context context) {
+    PlanNode source = captures.get(SOURCE);
+
+    // if we fail to decorrelate the nested plan, and it contains a distinct operator, we can
+    // extract and special-handle the distinct operator
+    AggregationNode distinct = null;
+
+    // decorrelate nested plan
+    PlanNodeDecorrelator decorrelator =
+        new PlanNodeDecorrelator(plannerContext, context.getSymbolAllocator(), context.getLookup());
+    Optional<PlanNodeDecorrelator.DecorrelatedNode> decorrelatedSource =
+        decorrelator.decorrelateFilters(source, correlatedJoinNode.getCorrelation());
+    if (!decorrelatedSource.isPresent()) {
+      // we failed to decorrelate the nested plan, so check if we can extract a distinct operator
+      // from the nested plan
+      if (isDistinctOperator(source)) {
+        distinct = (AggregationNode) source;
+        source = distinct.getChild();
+        decorrelatedSource =
+            decorrelator.decorrelateFilters(source, correlatedJoinNode.getCorrelation());
+      }
+      if (!decorrelatedSource.isPresent()) {
+        return Result.empty();
+      }
+    }
+
+    source = decorrelatedSource.get().getNode();
+
+    // assign unique id on correlated join's input. It will be used to distinguish between original
+    // input rows after join
+    PlanNode inputWithUniqueId =
+        new AssignUniqueId(
+            context.getIdAllocator().genPlanNodeId(),
+            correlatedJoinNode.getInput(),
+            context.getSymbolAllocator().newSymbol("unique", BooleanType.getInstance()));
+
+    JoinNode join =
+        new JoinNode(
+            context.getIdAllocator().genPlanNodeId(),
+            JoinNode.JoinType.INNER,
+            inputWithUniqueId,
+            source,
+            ImmutableList.of(),
+            inputWithUniqueId.getOutputSymbols(),
+            source.getOutputSymbols(),
+            decorrelatedSource.get().getCorrelatedPredicates(),
+            Optional.empty());
+
+    // restore distinct aggregation
+    if (distinct != null) {
+      distinct =
+          restoreDistinctAggregation(
+              distinct,
+              join,
+              ImmutableList.<Symbol>builder()
+                  .addAll(join.getLeftOutputSymbols())
+                  .addAll(distinct.getGroupingKeys())
+                  .build());
+    }
+
+    // restore grouped aggregation
+    AggregationNode groupedAggregation = captures.get(AGGREGATION);
+    groupedAggregation =
+        AggregationNode.builderFrom(groupedAggregation)
+            .setSource(distinct != null ? distinct : join)
+            .setAggregations(groupedAggregation.getAggregations())
+            .setGroupingSets(
+                singleGroupingSet(
+                    ImmutableList.<Symbol>builder()
+                        .addAll(join.getLeftOutputSymbols())
+                        .addAll(groupedAggregation.getGroupingKeys())
+                        .build()))
+            .setPreGroupedSymbols(ImmutableList.of())
+            .setHashSymbol(Optional.empty())
+            .setGroupIdSymbol(Optional.empty())
+            .build();
+
+    // restrict outputs
+    Optional<PlanNode> project =
+        restrictOutputs(
+            context.getIdAllocator(),
+            groupedAggregation,
+            ImmutableSet.copyOf(correlatedJoinNode.getOutputSymbols()));
+
+    return Result.ofPlanNode(project.orElse(groupedAggregation));
+  }
+}
diff --git a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/TransformCorrelatedJoinToJoin.java b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/TransformCorrelatedJoinToJoin.java
new file mode 100644
index 00000000000..cfd22fe4f12
--- /dev/null
+++ b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/TransformCorrelatedJoinToJoin.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.iterative.rule;
+
+import org.apache.iotdb.db.queryengine.plan.planner.plan.node.PlanNode;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.PlannerContext;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.Rule;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.node.CorrelatedJoinNode;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.node.JoinNode;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.optimizations.PlanNodeDecorrelator;
+import org.apache.iotdb.db.queryengine.plan.relational.sql.ast.Expression;
+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 java.util.Optional;
+
+import static com.google.common.base.Preconditions.checkArgument;
+import static java.util.Objects.requireNonNull;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.ir.IrUtils.combineConjuncts;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.JoinNode.JoinType.INNER;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.JoinNode.JoinType.LEFT;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns.CorrelatedJoin.correlation;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns.correlatedJoin;
+import static org.apache.iotdb.db.queryengine.plan.relational.sql.ast.BooleanLiteral.TRUE_LITERAL;
+import static org.apache.iotdb.db.queryengine.plan.relational.utils.matching.Pattern.nonEmpty;
+
+/**
+ * Tries to decorrelate subquery and rewrite it using normal join. Decorrelated predicates are part
+ * of join condition.
+ */
+public class TransformCorrelatedJoinToJoin implements Rule<CorrelatedJoinNode> {
+  private static final Pattern<CorrelatedJoinNode> PATTERN =
+      correlatedJoin().with(nonEmpty(correlation()));
+
+  private final PlannerContext plannerContext;
+
+  public TransformCorrelatedJoinToJoin(PlannerContext plannerContext) {
+    this.plannerContext = requireNonNull(plannerContext, "plannerContext is null");
+  }
+
+  @Override
+  public Pattern<CorrelatedJoinNode> getPattern() {
+    return PATTERN;
+  }
+
+  @Override
+  public Result apply(CorrelatedJoinNode correlatedJoinNode, Captures captures, Context context) {
+    checkArgument(
+        correlatedJoinNode.getJoinType() == INNER || correlatedJoinNode.getJoinType() == LEFT,
+        "correlation in %s JOIN",
+        correlatedJoinNode.getJoinType().name());
+    PlanNode subquery = correlatedJoinNode.getSubquery();
+
+    PlanNodeDecorrelator planNodeDecorrelator =
+        new PlanNodeDecorrelator(plannerContext, context.getSymbolAllocator(), context.getLookup());
+    Optional<PlanNodeDecorrelator.DecorrelatedNode> decorrelatedNodeOptional =
+        planNodeDecorrelator.decorrelateFilters(subquery, correlatedJoinNode.getCorrelation());
+    if (!decorrelatedNodeOptional.isPresent()) {
+      return Result.empty();
+    }
+    PlanNodeDecorrelator.DecorrelatedNode decorrelatedSubquery = decorrelatedNodeOptional.get();
+
+    Expression filter =
+        combineConjuncts(
+            decorrelatedSubquery.getCorrelatedPredicates().orElse(TRUE_LITERAL),
+            correlatedJoinNode.getFilter());
+
+    return Result.ofPlanNode(
+        new JoinNode(
+            correlatedJoinNode.getPlanNodeId(),
+            correlatedJoinNode.getJoinType(),
+            correlatedJoinNode.getInput(),
+            decorrelatedSubquery.getNode(),
+            ImmutableList.of(),
+            correlatedJoinNode.getInput().getOutputSymbols(),
+            correlatedJoinNode.getSubquery().getOutputSymbols(),
+            filter.equals(TRUE_LITERAL) ? Optional.empty() : Optional.of(filter),
+            Optional.empty()));
+  }
+}
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 f676fcd0ca5..a64eea771f6 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
@@ -272,18 +272,15 @@ public final class Patterns {
             node.getChildren().stream().map(lookup::resolve).collect(toImmutableList()));
   }
 
-  /*public static final class Aggregation
-  {
-      public static Property<AggregationNode, Lookup, List<Symbol>> groupingColumns()
-      {
-          return property("groupingKeys", AggregationNode::getGroupingKeys);
-      }
+  public static final class Aggregation {
+    public static Property<AggregationNode, Lookup, List<Symbol>> groupingColumns() {
+      return property("groupingKeys", AggregationNode::getGroupingKeys);
+    }
 
-      public static Property<AggregationNode, Lookup, AggregationNode.Step> step()
-      {
-          return property("step", AggregationNode::getStep);
-      }
-  }*/
+    public static Property<AggregationNode, Lookup, AggregationNode.Step> step() {
+      return property("step", AggregationNode::getStep);
+    }
+  }
 
   public static final class Apply {
     public static Property<ApplyNode, Lookup, List<Symbol>> correlation() {
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 de2b245bfd2..ca27877079d 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
@@ -58,8 +58,16 @@ import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.Re
 import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.RemoveRedundantEnforceSingleRowNode;
 import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.RemoveRedundantIdentityProjections;
 import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.RemoveTrivialFilters;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.RemoveUnreferencedScalarApplyNodes;
 import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.RemoveUnreferencedScalarSubqueries;
 import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.SimplifyExpressions;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.TransformCorrelatedDistinctAggregationWithProjection;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.TransformCorrelatedDistinctAggregationWithoutProjection;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.TransformCorrelatedGlobalAggregationWithProjection;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.TransformCorrelatedGlobalAggregationWithoutProjection;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.TransformCorrelatedGroupedAggregationWithProjection;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.TransformCorrelatedGroupedAggregationWithoutProjection;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.TransformCorrelatedJoinToJoin;
 import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.TransformUncorrelatedInPredicateSubqueryToSemiJoin;
 import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.TransformUncorrelatedSubqueryToJoin;
 
@@ -215,9 +223,27 @@ public class LogicalOptimizeFactory {
             plannerContext,
             ruleStats,
             ImmutableSet.of(
-                new RemoveRedundantEnforceSingleRowNode(), new RemoveUnreferencedScalarSubqueries(),
+                new RemoveRedundantEnforceSingleRowNode(),
+                new RemoveUnreferencedScalarSubqueries(),
                 new TransformUncorrelatedSubqueryToJoin(),
-                    new TransformUncorrelatedInPredicateSubqueryToSemiJoin())),
+                new TransformUncorrelatedInPredicateSubqueryToSemiJoin(),
+                new TransformCorrelatedJoinToJoin(plannerContext),
+                new TransformCorrelatedGlobalAggregationWithProjection(plannerContext),
+                new TransformCorrelatedGlobalAggregationWithoutProjection(plannerContext),
+                new TransformCorrelatedDistinctAggregationWithProjection(plannerContext),
+                new TransformCorrelatedDistinctAggregationWithoutProjection(plannerContext),
+                new TransformCorrelatedGroupedAggregationWithProjection(plannerContext),
+                new TransformCorrelatedGroupedAggregationWithoutProjection(plannerContext))),
+        new IterativeOptimizer(
+            plannerContext,
+            ruleStats,
+            ImmutableSet.of(
+                new RemoveUnreferencedScalarApplyNodes(),
+                //                            new TransformCorrelatedInPredicateToJoin(metadata), //
+                // must be run after columnPruningOptimizer
+                //                            new TransformCorrelatedScalarSubquery(metadata), //
+                // must be run after TransformCorrelatedAggregation rules
+                new TransformCorrelatedJoinToJoin(plannerContext))),
         new IterativeOptimizer(
             plannerContext,
             ruleStats,


(iotdb) 08/08: Merge branch 'support_uncorrelated_quantified_comparison' into support_exists_and_correlate

Posted by la...@apache.org.
This is an automated email from the ASF dual-hosted git repository.

lancelly pushed a commit to branch support_exists_and_correlate
in repository https://gitbox.apache.org/repos/asf/iotdb.git

commit 38a9f223e0219d6ec925ff289c14bfa4137a9715
Merge: 56aa492d379 55123de96e5
Author: lancelly <14...@qq.com>
AuthorDate: Fri Jan 17 16:44:40 2025 +0800

    Merge branch 'support_uncorrelated_quantified_comparison' into support_exists_and_correlate

 .../operator/source/relational/aggregation/CountAllAccumulator.java | 6 +++---
 1 file changed, 3 insertions(+), 3 deletions(-)


(iotdb) 01/08: add AssignUniqueId

Posted by la...@apache.org.
This is an automated email from the ASF dual-hosted git repository.

lancelly pushed a commit to branch support_exists_and_correlate
in repository https://gitbox.apache.org/repos/asf/iotdb.git

commit fb6c5e51a63add52421457cff4843d83264f7c27
Author: lancelly <14...@qq.com>
AuthorDate: Fri Jan 10 16:42:24 2025 +0800

    add AssignUniqueId
---
 .../operator/process/AssignUniqueIdOperator.java   | 142 +++++
 .../plan/planner/TableOperatorGenerator.java       |  15 +
 .../plan/planner/plan/node/PlanGraphPrinter.java   |  10 +
 .../plan/planner/plan/node/PlanNodeType.java       |   1 +
 .../plan/planner/plan/node/PlanVisitor.java        |   5 +
 .../iterative/rule/PruneAssignUniqueIdColumns.java |  47 ++
 .../rule/TransformExistsApplyToCorrelatedJoin.java | 215 +++++++
 .../relational/planner/node/AssignUniqueId.java    |  97 +++
 .../plan/relational/planner/node/Patterns.java     |   8 +-
 .../optimizations/LogicalOptimizeFactory.java      |   2 +
 .../optimizations/PlanNodeDecorrelator.java        | 699 +++++++++++++++++++++
 .../optimizations/PushPredicateIntoTableScan.java  |  15 +
 ...mQuantifiedComparisonApplyToCorrelatedJoin.java |  27 +-
 .../optimizations/UnaliasSymbolReferences.java     |  13 +
 .../relational/planner/optimizations/Util.java     |  19 +
 .../planner/assertions/AssignUniqueIdMatcher.java  |  49 ++
 .../planner/assertions/PlanMatchPattern.java       |   6 +
 17 files changed, 1344 insertions(+), 26 deletions(-)

diff --git a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/execution/operator/process/AssignUniqueIdOperator.java b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/execution/operator/process/AssignUniqueIdOperator.java
new file mode 100644
index 00000000000..97a64293805
--- /dev/null
+++ b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/execution/operator/process/AssignUniqueIdOperator.java
@@ -0,0 +1,142 @@
+/*
+ * 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.execution.operator.process;
+
+import org.apache.iotdb.db.queryengine.execution.MemoryEstimationHelper;
+import org.apache.iotdb.db.queryengine.execution.operator.Operator;
+import org.apache.iotdb.db.queryengine.execution.operator.OperatorContext;
+import org.apache.iotdb.db.queryengine.execution.schedule.task.DriverTaskId;
+
+import com.google.common.util.concurrent.ListenableFuture;
+import org.apache.tsfile.block.column.Column;
+import org.apache.tsfile.common.conf.TSFileDescriptor;
+import org.apache.tsfile.read.common.block.TsBlock;
+import org.apache.tsfile.read.common.block.column.LongColumnBuilder;
+import org.apache.tsfile.utils.RamUsageEstimator;
+
+import java.util.concurrent.atomic.AtomicLong;
+
+import static com.google.common.base.Preconditions.checkState;
+import static com.google.common.base.Verify.verify;
+
+public class AssignUniqueIdOperator implements ProcessOperator {
+  private static final long INSTANCE_SIZE =
+      RamUsageEstimator.shallowSizeOfInstance(AssignUniqueIdOperator.class);
+
+  private static final long ROW_IDS_PER_REQUEST = 1L << 20L;
+  private static final long MAX_ROW_ID = 1L << 40L;
+
+  private final OperatorContext operatorContext;
+  private final Operator child;
+
+  private final AtomicLong rowIdPool = new AtomicLong();
+  private final long uniqueValueMask;
+
+  private long rowIdCounter;
+  private long maxRowIdCounterValue;
+
+  public AssignUniqueIdOperator(OperatorContext operatorContext, Operator child) {
+    this.operatorContext = operatorContext;
+    this.child = child;
+
+    DriverTaskId id = operatorContext.getDriverContext().getDriverTaskID();
+    this.uniqueValueMask =
+        (((long) id.getFragmentId().getId()) << 54) | (((long) id.getPipelineId()) << 40);
+    requestValues();
+  }
+
+  @Override
+  public ListenableFuture<?> isBlocked() {
+    return child.isBlocked();
+  }
+
+  @Override
+  public TsBlock next() throws Exception {
+    TsBlock tsBlock = child.next();
+    if (tsBlock == null || tsBlock.isEmpty()) {
+      return null;
+    }
+    return tsBlock.appendValueColumns(new Column[] {generateIdColumn(tsBlock.getPositionCount())});
+  }
+
+  @Override
+  public boolean hasNext() throws Exception {
+    return child.hasNext();
+  }
+
+  @Override
+  public void close() throws Exception {
+    if (child != null) {
+      child.close();
+    }
+  }
+
+  @Override
+  public boolean isFinished() throws Exception {
+    return child.isFinished();
+  }
+
+  @Override
+  public long calculateMaxPeekMemory() {
+    return child.calculateMaxPeekMemory();
+  }
+
+  @Override
+  public long calculateMaxReturnSize() {
+    return child.calculateMaxReturnSize()
+        + (long) Long.SIZE * TSFileDescriptor.getInstance().getConfig().getMaxTsBlockLineNumber();
+  }
+
+  @Override
+  public long calculateRetainedSizeAfterCallingNext() {
+    return child.calculateRetainedSizeAfterCallingNext();
+  }
+
+  @Override
+  public long ramBytesUsed() {
+    return INSTANCE_SIZE
+        + MemoryEstimationHelper.getEstimatedSizeOfAccountableObject(child)
+        + MemoryEstimationHelper.getEstimatedSizeOfAccountableObject(operatorContext);
+  }
+
+  @Override
+  public OperatorContext getOperatorContext() {
+    return operatorContext;
+  }
+
+  private Column generateIdColumn(int positionCount) {
+    LongColumnBuilder columnBuilder = new LongColumnBuilder(null, positionCount);
+    for (int currentPosition = 0; currentPosition < positionCount; currentPosition++) {
+      if (rowIdCounter >= maxRowIdCounterValue) {
+        requestValues();
+      }
+      long rowId = rowIdCounter++;
+      verify((rowId & uniqueValueMask) == 0, "RowId and uniqueValue mask overlaps");
+      columnBuilder.writeLong(uniqueValueMask | rowId);
+    }
+    return columnBuilder.build();
+  }
+
+  private void requestValues() {
+    rowIdCounter = rowIdPool.getAndAdd(ROW_IDS_PER_REQUEST);
+    maxRowIdCounterValue = Math.min(rowIdCounter + ROW_IDS_PER_REQUEST, MAX_ROW_ID);
+    checkState(rowIdCounter < MAX_ROW_ID, "Unique row id exceeds a limit: %s", MAX_ROW_ID);
+  }
+}
diff --git a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/planner/TableOperatorGenerator.java b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/planner/TableOperatorGenerator.java
index 9ca7ddda112..1d99562e5ed 100644
--- a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/planner/TableOperatorGenerator.java
+++ b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/planner/TableOperatorGenerator.java
@@ -129,6 +129,7 @@ import org.apache.iotdb.db.queryengine.plan.relational.planner.Symbol;
 import org.apache.iotdb.db.queryengine.plan.relational.planner.node.AggregationNode;
 import org.apache.iotdb.db.queryengine.plan.relational.planner.node.AggregationTableScanNode;
 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.CollectNode;
 import org.apache.iotdb.db.queryengine.plan.relational.planner.node.DeviceTableScanNode;
 import org.apache.iotdb.db.queryengine.plan.relational.planner.node.EnforceSingleRowNode;
@@ -1580,6 +1581,20 @@ public class TableOperatorGenerator extends PlanVisitor<Operator, LocalExecution
     return new EnforceSingleRowOperator(operatorContext, child);
   }
 
+  @Override
+  public Operator visitAssignUniqueId(AssignUniqueId node, LocalExecutionPlanContext context) {
+    Operator child = node.getChild().accept(this, context);
+    OperatorContext operatorContext =
+        context
+            .getDriverContext()
+            .addOperatorContext(
+                context.getNextOperatorId(),
+                node.getPlanNodeId(),
+                EnforceSingleRowOperator.class.getSimpleName());
+
+    return new EnforceSingleRowOperator(operatorContext, child);
+  }
+
   @Override
   public Operator visitCountMerge(
       final CountSchemaMergeNode node, final LocalExecutionPlanContext context) {
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 442d59f8be3..c61ab511168 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
@@ -66,6 +66,7 @@ import org.apache.iotdb.db.queryengine.plan.planner.plan.parameter.CrossSeriesAg
 import org.apache.iotdb.db.queryengine.plan.planner.plan.parameter.DeviceViewIntoPathDescriptor;
 import org.apache.iotdb.db.queryengine.plan.planner.plan.parameter.IntoPathDescriptor;
 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.DeviceTableScanNode;
 import org.apache.iotdb.db.queryengine.plan.relational.planner.node.EnforceSingleRowNode;
 import org.apache.iotdb.db.queryengine.plan.relational.planner.node.ExchangeNode;
@@ -947,6 +948,15 @@ public class PlanGraphPrinter extends PlanVisitor<List<String>, PlanGraphPrinter
     return render(node, boxValue, context);
   }
 
+  @Override
+  public List<String> visitAssignUniqueId(AssignUniqueId node, GraphContext context) {
+    List<String> boxValue = new ArrayList<>();
+    boxValue.add(String.format("AssignUniqueId-%s", node.getPlanNodeId().getId()));
+    boxValue.add(String.format("OutputSymbols: %s", node.getOutputSymbols()));
+    boxValue.add(String.format("IdColumnSymbol: %s", node.getIdColumn()));
+    return render(node, boxValue, context);
+  }
+
   private String printRegion(TRegionReplicaSet regionReplicaSet) {
     return String.format(
         "Partition: %s",
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 3e86d189e1e..0dd5788970f 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
@@ -290,6 +290,7 @@ public enum PlanNodeType {
   TREE_ALIGNED_DEVICE_VIEW_SCAN_NODE((short) 1023),
   TREE_NONALIGNED_DEVICE_VIEW_SCAN_NODE((short) 1024),
   TABLE_SEMI_JOIN_NODE((short) 1025),
+  TABLE_ASSIGN_UNIQUE_ID((short) 1026),
 
   RELATIONAL_INSERT_TABLET((short) 2000),
   RELATIONAL_INSERT_ROW((short) 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 d04c57e77fb..bf0099d17fd 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
@@ -654,6 +654,11 @@ public abstract class PlanVisitor<R, C> {
     return visitTwoChildProcess(node, context);
   }
 
+  public R visitAssignUniqueId(
+      org.apache.iotdb.db.queryengine.plan.relational.planner.node.AssignUniqueId node, C context) {
+    return visitSingleChildProcess(node, context);
+  }
+
   public R visitEnforceSingleRow(
       org.apache.iotdb.db.queryengine.plan.relational.planner.node.EnforceSingleRowNode node,
       C context) {
diff --git a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/PruneAssignUniqueIdColumns.java b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/PruneAssignUniqueIdColumns.java
new file mode 100644
index 00000000000..a5c675b5fc8
--- /dev/null
+++ b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/PruneAssignUniqueIdColumns.java
@@ -0,0 +1,47 @@
+/*
+ * 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.planner.plan.node.PlanNode;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.Symbol;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.node.AssignUniqueId;
+
+import java.util.Optional;
+import java.util.Set;
+
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.Util.restrictChildOutputs;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns.assignUniqueId;
+
+public class PruneAssignUniqueIdColumns extends ProjectOffPushDownRule<AssignUniqueId> {
+  public PruneAssignUniqueIdColumns() {
+    super(assignUniqueId());
+  }
+
+  @Override
+  protected Optional<PlanNode> pushDownProjectOff(
+      Context context, AssignUniqueId assignUniqueId, Set<Symbol> referencedOutputs) {
+    // remove unused AssignUniqueId node
+    if (!referencedOutputs.contains(assignUniqueId.getIdColumn())) {
+      return Optional.of(assignUniqueId.getChild());
+    }
+
+    return restrictChildOutputs(context.getIdAllocator(), assignUniqueId, referencedOutputs);
+  }
+}
diff --git a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/TransformExistsApplyToCorrelatedJoin.java b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/TransformExistsApplyToCorrelatedJoin.java
new file mode 100644
index 00000000000..86f1ec96a69
--- /dev/null
+++ b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/iterative/rule/TransformExistsApplyToCorrelatedJoin.java
@@ -0,0 +1,215 @@
+/*
+ * 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.planner.plan.node.PlanNode;
+import org.apache.iotdb.db.queryengine.plan.relational.metadata.ResolvedFunction;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.Assignments;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.PlannerContext;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.Symbol;
+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.ApplyNode;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.node.CorrelatedJoinNode;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.node.LimitNode;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.node.ProjectNode;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.optimizations.PlanNodeDecorrelator;
+import org.apache.iotdb.db.queryengine.plan.relational.sql.ast.BooleanLiteral;
+import org.apache.iotdb.db.queryengine.plan.relational.sql.ast.Cast;
+import org.apache.iotdb.db.queryengine.plan.relational.sql.ast.CoalesceExpression;
+import org.apache.iotdb.db.queryengine.plan.relational.sql.ast.ComparisonExpression;
+import org.apache.iotdb.db.queryengine.plan.relational.sql.ast.LongLiteral;
+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 com.google.common.collect.ImmutableMap;
+import org.apache.tsfile.read.common.type.LongType;
+
+import java.util.Optional;
+
+import static com.google.common.base.Preconditions.checkState;
+import static com.google.common.collect.Iterables.getOnlyElement;
+import static java.util.Objects.requireNonNull;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.AggregationNode.globalAggregation;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.AggregationNode.singleAggregation;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.JoinNode.JoinType.INNER;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.JoinNode.JoinType.LEFT;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.Patterns.applyNode;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.optimizations.Util.getResolvedBuiltInAggregateFunction;
+import static org.apache.iotdb.db.queryengine.plan.relational.sql.ast.BooleanLiteral.TRUE_LITERAL;
+import static org.apache.iotdb.db.queryengine.plan.relational.sql.ast.ComparisonExpression.Operator.GREATER_THAN;
+import static org.apache.iotdb.db.queryengine.plan.relational.type.TypeSignatureTranslator.toSqlType;
+import static org.apache.tsfile.read.common.type.BooleanType.BOOLEAN;
+
+/**
+ * EXISTS is modeled as (if correlated predicates are equality comparisons):
+ *
+ * <pre>
+ *     - Project(exists := COALESCE(subqueryTrue, false))
+ *       - CorrelatedJoin(LEFT)
+ *         - input
+ *         - Project(subqueryTrue := true)
+ *           - Limit(count=1)
+ *             - subquery
+ * </pre>
+ *
+ * or:
+ *
+ * <pre>
+ *     - CorrelatedJoin(LEFT)
+ *       - input
+ *       - Project($0 > 0)
+ *         - Aggregation(COUNT(*))
+ *           - subquery
+ * </pre>
+ *
+ * otherwise
+ */
+public class TransformExistsApplyToCorrelatedJoin implements Rule<ApplyNode> {
+  private static final Pattern<ApplyNode> PATTERN = applyNode();
+
+  private final PlannerContext plannerContext;
+
+  public TransformExistsApplyToCorrelatedJoin(PlannerContext plannerContext) {
+    this.plannerContext = requireNonNull(plannerContext, "plannerContext is null");
+  }
+
+  @Override
+  public Pattern<ApplyNode> getPattern() {
+    return PATTERN;
+  }
+
+  @Override
+  public Result apply(ApplyNode parent, Captures captures, Context context) {
+    if (parent.getSubqueryAssignments().size() != 1) {
+      return Result.empty();
+    }
+
+    ApplyNode.SetExpression expression = getOnlyElement(parent.getSubqueryAssignments().values());
+    if (!(expression instanceof ApplyNode.Exists)) {
+      return Result.empty();
+    }
+
+    /*
+    Empty correlation list indicates that the subquery contains no correlation symbols from the
+    immediate outer scope. The subquery might be either not correlated at all, or correlated with
+    symbols from further outer scope.
+    Currently, the two cases are indistinguishable.
+    To support the latter case, the ApplyNode with empty correlation list is rewritten to default
+    aggregation, which is inefficient in the rare case of uncorrelated EXISTS subquery,
+    but currently allows to successfully decorrelate a correlated EXISTS subquery.
+
+    TODO: remove this condition when exploratory optimizer is implemented or support for decorrelating joins is implemented in PlanNodeDecorrelator
+    */
+    if (parent.getCorrelation().isEmpty()) {
+      return Result.ofPlanNode(rewriteToDefaultAggregation(parent, context));
+    }
+
+    Optional<PlanNode> nonDefaultAggregation = rewriteToNonDefaultAggregation(parent, context);
+    return nonDefaultAggregation
+        .map(Result::ofPlanNode)
+        .orElseGet(() -> Result.ofPlanNode(rewriteToDefaultAggregation(parent, context)));
+  }
+
+  private Optional<PlanNode> rewriteToNonDefaultAggregation(ApplyNode applyNode, Context context) {
+    checkState(
+        applyNode.getSubquery().getOutputSymbols().isEmpty(),
+        "Expected subquery output symbols to be pruned");
+
+    Symbol subqueryTrue = context.getSymbolAllocator().newSymbol("subqueryTrue", BOOLEAN);
+
+    PlanNode subquery =
+        new ProjectNode(
+            context.getIdAllocator().genPlanNodeId(),
+            new LimitNode(
+                context.getIdAllocator().genPlanNodeId(),
+                applyNode.getSubquery(),
+                1L,
+                Optional.empty()),
+            Assignments.of(subqueryTrue, TRUE_LITERAL));
+
+    PlanNodeDecorrelator decorrelator =
+        new PlanNodeDecorrelator(plannerContext, context.getSymbolAllocator(), context.getLookup());
+    if (!decorrelator.decorrelateFilters(subquery, applyNode.getCorrelation()).isPresent()) {
+      return Optional.empty();
+    }
+
+    Symbol exists = getOnlyElement(applyNode.getSubqueryAssignments().keySet());
+    Assignments.Builder assignments =
+        Assignments.builder()
+            .putIdentities(applyNode.getInput().getOutputSymbols())
+            .put(
+                exists,
+                new CoalesceExpression(
+                    ImmutableList.of(
+                        subqueryTrue.toSymbolReference(), BooleanLiteral.FALSE_LITERAL)));
+
+    return Optional.of(
+        new ProjectNode(
+            context.getIdAllocator().genPlanNodeId(),
+            new CorrelatedJoinNode(
+                applyNode.getPlanNodeId(),
+                applyNode.getInput(),
+                subquery,
+                applyNode.getCorrelation(),
+                LEFT,
+                TRUE_LITERAL,
+                applyNode.getOriginSubquery()),
+            assignments.build()));
+  }
+
+  private PlanNode rewriteToDefaultAggregation(ApplyNode applyNode, Context context) {
+    ResolvedFunction countFunction =
+        getResolvedBuiltInAggregateFunction(
+            plannerContext.getMetadata(), "count", ImmutableList.of());
+    Symbol count = context.getSymbolAllocator().newSymbol("count", LongType.getInstance());
+    Symbol exists = getOnlyElement(applyNode.getSubqueryAssignments().keySet());
+
+    return new CorrelatedJoinNode(
+        applyNode.getPlanNodeId(),
+        applyNode.getInput(),
+        new ProjectNode(
+            context.getIdAllocator().genPlanNodeId(),
+            singleAggregation(
+                context.getIdAllocator().genPlanNodeId(),
+                applyNode.getSubquery(),
+                ImmutableMap.of(
+                    count,
+                    new AggregationNode.Aggregation(
+                        countFunction,
+                        ImmutableList.of(),
+                        false,
+                        Optional.empty(),
+                        Optional.empty(),
+                        Optional.empty())),
+                globalAggregation()),
+            Assignments.of(
+                exists,
+                new ComparisonExpression(
+                    GREATER_THAN,
+                    count.toSymbolReference(),
+                    new Cast(new LongLiteral("0"), toSqlType(LongType.getInstance()))))),
+        applyNode.getCorrelation(),
+        INNER,
+        TRUE_LITERAL,
+        applyNode.getOriginSubquery());
+  }
+}
diff --git a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/node/AssignUniqueId.java b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/node/AssignUniqueId.java
new file mode 100644
index 00000000000..3528d8d3649
--- /dev/null
+++ b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/node/AssignUniqueId.java
@@ -0,0 +1,97 @@
+/*
+ * 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.PlanNodeType;
+import org.apache.iotdb.db.queryengine.plan.planner.plan.node.PlanVisitor;
+import org.apache.iotdb.db.queryengine.plan.planner.plan.node.process.SingleChildProcessNode;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.Symbol;
+
+import com.google.common.collect.ImmutableList;
+import com.google.common.collect.Iterables;
+
+import java.io.DataOutputStream;
+import java.io.IOException;
+import java.nio.ByteBuffer;
+import java.util.List;
+
+import static com.google.common.base.Preconditions.checkArgument;
+import static java.util.Objects.requireNonNull;
+
+public class AssignUniqueId extends SingleChildProcessNode {
+  private final Symbol idColumn;
+
+  public AssignUniqueId(PlanNodeId id, PlanNode child, Symbol idColumn) {
+    super(id);
+    this.child = child;
+    this.idColumn = requireNonNull(idColumn, "idColumn is null");
+  }
+
+  @Override
+  public <R, C> R accept(PlanVisitor<R, C> visitor, C context) {
+    return visitor.visitAssignUniqueId(this, context);
+  }
+
+  @Override
+  public List<Symbol> getOutputSymbols() {
+    return ImmutableList.<Symbol>builder().addAll(child.getOutputSymbols()).add(idColumn).build();
+  }
+
+  @Override
+  public PlanNode clone() {
+    // clone without child
+    return new AssignUniqueId(id, null, idColumn);
+  }
+
+  @Override
+  public PlanNode replaceChildren(List<PlanNode> newChildren) {
+    checkArgument(newChildren.size() == 1, "expected newChildren to contain 1 node");
+    return new AssignUniqueId(getPlanNodeId(), Iterables.getOnlyElement(newChildren), idColumn);
+  }
+
+  @Override
+  protected void serializeAttributes(ByteBuffer byteBuffer) {
+    PlanNodeType.TABLE_ASSIGN_UNIQUE_ID.serialize(byteBuffer);
+    Symbol.serialize(idColumn, byteBuffer);
+  }
+
+  @Override
+  protected void serializeAttributes(DataOutputStream stream) throws IOException {
+    PlanNodeType.TABLE_ASSIGN_UNIQUE_ID.serialize(stream);
+    Symbol.serialize(idColumn, stream);
+  }
+
+  public static AssignUniqueId deserialize(ByteBuffer byteBuffer) {
+    Symbol idColumn = Symbol.deserialize(byteBuffer);
+    PlanNodeId planNodeId = PlanNodeId.deserialize(byteBuffer);
+    return new AssignUniqueId(planNodeId, null, idColumn);
+  }
+
+  @Override
+  public List<String> getOutputColumnNames() {
+    throw new UnsupportedOperationException();
+  }
+
+  public Symbol getIdColumn() {
+    return idColumn;
+  }
+}
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 604e2b06a3a..f676fcd0ca5 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
@@ -42,10 +42,10 @@ public final class Patterns {
     return typeOf(AggregationNode.class);
   }
 
-  //  public static Pattern<AssignUniqueId> assignUniqueId()
-  //  {
-  //      return typeOf(AssignUniqueId.class);
-  //  }
+  public static Pattern<AssignUniqueId> assignUniqueId() {
+    return typeOf(AssignUniqueId.class);
+  }
+
   //
   //  public static Pattern<GroupIdNode> groupId()
   //  {
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 b3766383c18..de2b245bfd2 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
@@ -35,6 +35,7 @@ import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.Pr
 import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.PruneApplyColumns;
 import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.PruneApplyCorrelation;
 import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.PruneApplySourceColumns;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.PruneAssignUniqueIdColumns;
 import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.PruneCorrelatedJoinColumns;
 import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.PruneCorrelatedJoinCorrelation;
 import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.rule.PruneDistinctAggregation;
@@ -86,6 +87,7 @@ public class LogicalOptimizeFactory {
             new PruneApplyColumns(),
             new PruneApplyCorrelation(),
             new PruneApplySourceColumns(),
+            new PruneAssignUniqueIdColumns(),
             new PruneCorrelatedJoinColumns(),
             new PruneCorrelatedJoinCorrelation(),
             new PruneEnforceSingleRowColumns(),
diff --git a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/optimizations/PlanNodeDecorrelator.java b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/optimizations/PlanNodeDecorrelator.java
new file mode 100644
index 00000000000..8527f74191c
--- /dev/null
+++ b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/optimizations/PlanNodeDecorrelator.java
@@ -0,0 +1,699 @@
+/*
+ * 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.optimizations;
+
+import org.apache.iotdb.db.exception.sql.SemanticException;
+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.Assignments;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.PlannerContext;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.Symbol;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.SymbolAllocator;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.SymbolsExtractor;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.GroupReference;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.Lookup;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.node.AggregationNode;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.node.FilterNode;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.node.LimitNode;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.node.ProjectNode;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.node.TopKNode;
+import org.apache.iotdb.db.queryengine.plan.relational.sql.ast.Cast;
+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.SymbolReference;
+import org.apache.iotdb.db.queryengine.plan.relational.type.TypeCoercion;
+import org.apache.iotdb.db.queryengine.plan.relational.type.TypeManager;
+
+import com.google.common.collect.ImmutableList;
+import com.google.common.collect.ImmutableMap;
+import com.google.common.collect.ImmutableMultimap;
+import com.google.common.collect.ImmutableSet;
+import com.google.common.collect.Iterables;
+import com.google.common.collect.Multimap;
+import com.google.common.collect.Sets;
+
+import java.util.List;
+import java.util.Map;
+import java.util.Optional;
+import java.util.Set;
+import java.util.stream.Collectors;
+
+import static com.google.common.base.Preconditions.checkState;
+import static com.google.common.collect.ImmutableList.toImmutableList;
+import static com.google.common.collect.ImmutableMap.toImmutableMap;
+import static java.util.Objects.requireNonNull;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.ir.DeterminismEvaluator.isDeterministic;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.ir.IrUtils.and;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.ir.IrUtils.combineConjuncts;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.ir.IrUtils.extractConjuncts;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.AggregationNode.singleAggregation;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.AggregationNode.singleGroupingSet;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.optimizations.SymbolMapper.symbolMapper;
+import static org.apache.iotdb.db.queryengine.plan.relational.sql.ast.ComparisonExpression.Operator.EQUAL;
+
+public class PlanNodeDecorrelator {
+  private final PlannerContext plannerContext;
+  private final SymbolAllocator symbolAllocator;
+  private final Lookup lookup;
+  private final TypeCoercion typeCoercion;
+
+  public PlanNodeDecorrelator(
+      PlannerContext plannerContext, SymbolAllocator symbolAllocator, Lookup lookup) {
+    this.plannerContext = requireNonNull(plannerContext, "plannerContext is null");
+    this.symbolAllocator = requireNonNull(symbolAllocator, "symbolAllocator is null");
+    this.lookup = requireNonNull(lookup, "lookup is null");
+    this.typeCoercion = new TypeCoercion(plannerContext.getTypeManager()::getType);
+  }
+
+  public Optional<DecorrelatedNode> decorrelateFilters(PlanNode node, List<Symbol> correlation) {
+    if (correlation.isEmpty()) {
+      return Optional.of(new DecorrelatedNode(ImmutableList.of(), node));
+    }
+
+    Optional<DecorrelationResult> decorrelationResultOptional =
+        node.accept(new DecorrelatingVisitor(plannerContext.getTypeManager(), correlation), null);
+    return decorrelationResultOptional.flatMap(
+        decorrelationResult ->
+            decorrelatedNode(
+                decorrelationResult.correlatedPredicates, decorrelationResult.node, correlation));
+  }
+
+  private class DecorrelatingVisitor extends PlanVisitor<Optional<DecorrelationResult>, Void> {
+    private final TypeManager typeManager;
+    private final List<Symbol> correlation;
+
+    DecorrelatingVisitor(TypeManager typeManager, List<Symbol> correlation) {
+      this.typeManager = requireNonNull(typeManager, "typeManager is null");
+      this.correlation = requireNonNull(correlation, "correlation is null");
+    }
+
+    @Override
+    public Optional<DecorrelationResult> visitPlan(PlanNode node, Void context) {
+      if (containsCorrelation(node, correlation)) {
+        return Optional.empty();
+      }
+      return Optional.of(
+          new DecorrelationResult(
+              node,
+              ImmutableSet.of(),
+              ImmutableList.of(),
+              ImmutableMultimap.of(),
+              ImmutableSet.of(),
+              false));
+    }
+
+    @Override
+    public Optional<DecorrelationResult> visitGroupReference(GroupReference node, Void context) {
+      return lookup.resolve(node).accept(this, null);
+    }
+
+    @Override
+    public Optional<DecorrelationResult> visitFilter(FilterNode node, Void context) {
+      Optional<DecorrelationResult> childDecorrelationResultOptional =
+          Optional.of(
+              new DecorrelationResult(
+                  node.getChild(),
+                  ImmutableSet.of(),
+                  ImmutableList.of(),
+                  ImmutableMultimap.of(),
+                  ImmutableSet.of(),
+                  false));
+
+      // try to decorrelate filters down the tree
+      if (containsCorrelation(node.getChild(), correlation)) {
+        childDecorrelationResultOptional = node.getChild().accept(this, null);
+      }
+
+      if (!childDecorrelationResultOptional.isPresent()) {
+        return Optional.empty();
+      }
+
+      Expression predicate = node.getPredicate();
+      Map<Boolean, List<Expression>> predicates =
+          extractConjuncts(predicate).stream()
+              .collect(Collectors.partitioningBy(DecorrelatingVisitor.this::isCorrelated));
+      List<Expression> correlatedPredicates = ImmutableList.copyOf(predicates.get(true));
+      List<Expression> uncorrelatedPredicates = ImmutableList.copyOf(predicates.get(false));
+
+      DecorrelationResult childDecorrelationResult = childDecorrelationResultOptional.get();
+      FilterNode newFilterNode =
+          new FilterNode(
+              node.getPlanNodeId(),
+              childDecorrelationResult.node,
+              combineConjuncts(uncorrelatedPredicates));
+
+      Set<Symbol> symbolsToPropagate =
+          Sets.difference(
+              SymbolsExtractor.extractUnique(correlatedPredicates),
+              ImmutableSet.copyOf(correlation));
+      return Optional.of(
+          new DecorrelationResult(
+              newFilterNode,
+              Sets.union(childDecorrelationResult.symbolsToPropagate, symbolsToPropagate),
+              ImmutableList.<Expression>builder()
+                  .addAll(childDecorrelationResult.correlatedPredicates)
+                  .addAll(correlatedPredicates)
+                  .build(),
+              ImmutableMultimap.<Symbol, Symbol>builder()
+                  .putAll(childDecorrelationResult.correlatedSymbolsMapping)
+                  .putAll(extractCorrelatedSymbolsMapping(correlatedPredicates))
+                  .build(),
+              ImmutableSet.<Symbol>builder()
+                  .addAll(childDecorrelationResult.constantSymbols)
+                  .addAll(extractConstantSymbols(correlatedPredicates))
+                  .build(),
+              childDecorrelationResult.atMostSingleRow));
+    }
+
+    @Override
+    public Optional<DecorrelationResult> visitLimit(LimitNode node, Void context) {
+      if (node.getCount() == 0 || node.isWithTies()) {
+        return Optional.empty();
+      }
+
+      Optional<DecorrelationResult> childDecorrelationResultOptional =
+          node.getChild().accept(this, null);
+      if (!childDecorrelationResultOptional.isPresent()) {
+        return Optional.empty();
+      }
+
+      DecorrelationResult childDecorrelationResult = childDecorrelationResultOptional.get();
+      if (childDecorrelationResult.atMostSingleRow) {
+        return childDecorrelationResultOptional;
+      }
+
+      if (node.getCount() == 1) {
+        return rewriteLimitWithRowCountOne(childDecorrelationResult, node.getPlanNodeId());
+      }
+      throw new SemanticException(
+          "Decorrelation for LIMIT with row count greater than 1 is not supported yet");
+      // return rewriteLimitWithRowCountGreaterThanOne(childDecorrelationResult, node);
+    }
+
+    // TODO Limit (1) could be decorrelated by the method rewriteLimitWithRowCountGreaterThanOne()
+    // as well.
+    // The current decorrelation method for Limit (1) cannot deal with subqueries outputting other
+    // symbols
+    // than constants.
+    //
+    // An example query that is currently not supported:
+    // SELECT (
+    //      SELECT a+b
+    //      FROM (VALUES (1, 2), (1, 2)) inner_relation(a, b)
+    //      WHERE a=x
+    //      LIMIT 1)
+    // FROM (VALUES (1)) outer_relation(x)
+    //
+    // Switching the decorrelation method would change the way that queries with EXISTS are
+    // executed,
+    // and thus it needs benchmarking.
+    private Optional<DecorrelationResult> rewriteLimitWithRowCountOne(
+        DecorrelationResult childDecorrelationResult, PlanNodeId nodeId) {
+      PlanNode decorrelatedChildNode = childDecorrelationResult.node;
+      Set<Symbol> constantSymbols = childDecorrelationResult.getConstantSymbols();
+
+      if (constantSymbols.isEmpty()
+          || !constantSymbols.containsAll(decorrelatedChildNode.getOutputSymbols())) {
+        return Optional.empty();
+      }
+
+      // rewrite Limit to aggregation on constant symbols
+      AggregationNode aggregationNode =
+          singleAggregation(
+              nodeId,
+              decorrelatedChildNode,
+              ImmutableMap.of(),
+              singleGroupingSet(decorrelatedChildNode.getOutputSymbols()));
+
+      return Optional.of(
+          new DecorrelationResult(
+              aggregationNode,
+              childDecorrelationResult.symbolsToPropagate,
+              childDecorrelationResult.correlatedPredicates,
+              childDecorrelationResult.correlatedSymbolsMapping,
+              childDecorrelationResult.constantSymbols,
+              true));
+    }
+
+    /*
+    private Optional<DecorrelationResult> rewriteLimitWithRowCountGreaterThanOne(
+        DecorrelationResult childDecorrelationResult, LimitNode node) {
+      PlanNode decorrelatedChildNode = childDecorrelationResult.node;
+
+      // no rewrite needed (no symbols to partition by)
+      if (childDecorrelationResult.symbolsToPropagate.isEmpty()) {
+        return Optional.of(
+            new DecorrelationResult(
+                node.replaceChildren(ImmutableList.of(decorrelatedChildNode)),
+                childDecorrelationResult.symbolsToPropagate,
+                childDecorrelationResult.correlatedPredicates,
+                childDecorrelationResult.correlatedSymbolsMapping,
+                childDecorrelationResult.constantSymbols,
+                false));
+      }
+
+      Set<Symbol> constantSymbols = childDecorrelationResult.getConstantSymbols();
+      if (!constantSymbols.containsAll(childDecorrelationResult.symbolsToPropagate)) {
+        return Optional.empty();
+      }
+
+      // rewrite Limit to RowNumberNode partitioned by constant symbols
+      RowNumberNode rowNumberNode =
+          new RowNumberNode(
+              node.getPlanNodeId(),
+              decorrelatedChildNode,
+              ImmutableList.copyOf(childDecorrelationResult.symbolsToPropagate),
+              false,
+              symbolAllocator.newSymbol("row_number", BIGINT),
+              Optional.of(toIntExact(node.getCount())),
+              Optional.empty());
+
+      return Optional.of(
+          new DecorrelationResult(
+              rowNumberNode,
+              childDecorrelationResult.symbolsToPropagate,
+              childDecorrelationResult.correlatedPredicates,
+              childDecorrelationResult.correlatedSymbolsMapping,
+              childDecorrelationResult.constantSymbols,
+              false));
+    }*/
+
+    @Override
+    public Optional<DecorrelationResult> visitTopK(TopKNode node, Void context) {
+      throw new SemanticException("TopK is not supported in correlated subquery for now");
+    }
+
+    /*@Override
+    public Optional<DecorrelationResult> visitTopK(TopKNode node, Void context)
+    {
+        if (node.getCount() == 0) {
+            return Optional.empty();
+        }
+
+        checkState(node.getChildren().size() == 1, "Expected TopKNode to have a single child");
+        Optional<DecorrelationResult> childDecorrelationResultOptional = node.getChildren().get(0).accept(this, null);
+        if (!childDecorrelationResultOptional.isPresent()) {
+            return Optional.empty();
+        }
+
+        DecorrelationResult childDecorrelationResult = childDecorrelationResultOptional.get();
+        if (childDecorrelationResult.atMostSingleRow) {
+            return childDecorrelationResultOptional;
+        }
+
+        PlanNode decorrelatedChildNode = childDecorrelationResult.node;
+        Set<Symbol> constantSymbols = childDecorrelationResult.getConstantSymbols();
+        Optional<OrderingScheme> decorrelatedOrderingScheme = decorrelateOrderingScheme(node.getOrderingScheme(), constantSymbols);
+
+        // no partitioning needed (no symbols to partition by)
+        if (childDecorrelationResult.symbolsToPropagate.isEmpty()) {
+            return decorrelatedOrderingScheme
+                    .map(orderingScheme -> new DecorrelationResult(
+                            // ordering symbols are present - return decorrelated TopNNode
+                            new TopKNode(node.getPlanNodeId(), decorrelatedChildNode, node.getCount(), orderingScheme, node.getStep()),
+                            childDecorrelationResult.symbolsToPropagate,
+                            childDecorrelationResult.correlatedPredicates,
+                            childDecorrelationResult.correlatedSymbolsMapping,
+                            childDecorrelationResult.constantSymbols,
+                            node.getCount() == 1))
+                    .or(() -> Optional.of(new DecorrelationResult(
+                            // no ordering symbols are left - convert to LimitNode
+                            new LimitNode(node.getPlanNodeId(), decorrelatedChildNode, node.getCount(), Optional.empty()),
+                            childDecorrelationResult.symbolsToPropagate,
+                            childDecorrelationResult.correlatedPredicates,
+                            childDecorrelationResult.correlatedSymbolsMapping,
+                            childDecorrelationResult.constantSymbols,
+                            node.getCount() == 1)));
+        }
+
+        if (!constantSymbols.containsAll(childDecorrelationResult.symbolsToPropagate)) {
+            return Optional.empty();
+        }
+
+        return decorrelatedOrderingScheme
+                .map(orderingScheme -> {
+                    // ordering symbols are present - rewrite TopN to TopNRankingNode partitioned by constant symbols
+                    TopNRankingNode topNRankingNode = new TopNRankingNode(
+                            node.getId(),
+                            decorrelatedChildNode,
+                            new DataOrganizationSpecification(
+                                    ImmutableList.copyOf(childDecorrelationResult.symbolsToPropagate),
+                                    Optional.of(orderingScheme)),
+                            ROW_NUMBER,
+                            symbolAllocator.newSymbol("ranking", BIGINT),
+                            toIntExact(node.getCount()),
+                            false,
+                            Optional.empty());
+
+                    return Optional.of(new DecorrelationResult(
+                            topNRankingNode,
+                            childDecorrelationResult.symbolsToPropagate,
+                            childDecorrelationResult.correlatedPredicates,
+                            childDecorrelationResult.correlatedSymbolsMapping,
+                            childDecorrelationResult.constantSymbols,
+                            node.getCount() == 1));
+                })
+                .orElseGet(() -> {
+                    // no ordering symbols are left - rewrite TopN to RowNumberNode partitioned by constant symbols
+                    RowNumberNode rowNumberNode = new RowNumberNode(
+                            node.getId(),
+                            decorrelatedChildNode,
+                            ImmutableList.copyOf(childDecorrelationResult.symbolsToPropagate),
+                            false,
+                            symbolAllocator.newSymbol("row_number", BIGINT),
+                            Optional.of(toIntExact(node.getCount())),
+                            Optional.empty());
+
+                    return Optional.of(new DecorrelationResult(
+                            rowNumberNode,
+                            childDecorrelationResult.symbolsToPropagate,
+                            childDecorrelationResult.correlatedPredicates,
+                            childDecorrelationResult.correlatedSymbolsMapping,
+                            childDecorrelationResult.constantSymbols,
+                            node.getCount() == 1));
+                });
+    }
+
+    private Optional<OrderingScheme> decorrelateOrderingScheme(
+        OrderingScheme orderingScheme, Set<Symbol> constantSymbols) {
+      // remove local and remote constant sort symbols from the OrderingScheme
+      ImmutableList.Builder<Symbol> nonConstantOrderBy = ImmutableList.builder();
+      ImmutableMap.Builder<Symbol, SortOrder> nonConstantOrderings = ImmutableMap.builder();
+      for (Symbol symbol : orderingScheme.getOrderBy()) {
+        if (!constantSymbols.contains(symbol) && !correlation.contains(symbol)) {
+          nonConstantOrderBy.add(symbol);
+          nonConstantOrderings.put(symbol, orderingScheme.getOrdering(symbol));
+        }
+      }
+      if (nonConstantOrderBy.build().isEmpty()) {
+        return Optional.empty();
+      }
+      return Optional.of(
+          new OrderingScheme(nonConstantOrderBy.build(), nonConstantOrderings.buildOrThrow()));
+    }*/
+
+    @Override
+    public Optional<DecorrelationResult> visitAggregation(AggregationNode node, Void context) {
+      // Aggregation with global grouping cannot be converted to aggregation grouped on constants.
+      // Theoretically, if there are no constants to group on, the aggregation could be successfully
+      // decorrelated.
+      // However, it can only happen when Aggregation's source plan is not correlated.
+      // Then:
+      // - either we should not reach here because uncorrelated subplans of correlated filters are
+      // not explored,
+      // - or the aggregation contains correlation which is unresolvable. This is indicated by
+      // returning Optional.empty().
+      if (node.hasEmptyGroupingSet()) {
+        return Optional.empty();
+      }
+
+      if (node.getGroupingSetCount() != 1) {
+        return Optional.empty();
+      }
+
+      Optional<DecorrelationResult> childDecorrelationResultOptional =
+          node.getChild().accept(this, null);
+      if (!childDecorrelationResultOptional.isPresent()) {
+        return Optional.empty();
+      }
+
+      DecorrelationResult childDecorrelationResult = childDecorrelationResultOptional.get();
+      Set<Symbol> constantSymbols = childDecorrelationResult.getConstantSymbols();
+
+      AggregationNode decorrelatedAggregation =
+          childDecorrelationResult
+              .getCorrelatedSymbolMapper()
+              .map(node, childDecorrelationResult.node);
+
+      Set<Symbol> groupingKeys = ImmutableSet.copyOf(node.getGroupingKeys());
+      checkState(
+          ImmutableSet.copyOf(decorrelatedAggregation.getGroupingKeys()).equals(groupingKeys),
+          "grouping keys were correlated");
+      List<Symbol> symbolsToAdd =
+          childDecorrelationResult.symbolsToPropagate.stream()
+              .filter(symbol -> !groupingKeys.contains(symbol))
+              .collect(toImmutableList());
+
+      if (!constantSymbols.containsAll(symbolsToAdd)) {
+        return Optional.empty();
+      }
+
+      AggregationNode newAggregation =
+          AggregationNode.builderFrom(decorrelatedAggregation)
+              .setGroupingSets(
+                  singleGroupingSet(
+                      ImmutableList.<Symbol>builder()
+                          .addAll(node.getGroupingKeys())
+                          .addAll(symbolsToAdd)
+                          .build()))
+              .setPreGroupedSymbols(ImmutableList.of())
+              .build();
+
+      return Optional.of(
+          new DecorrelationResult(
+              newAggregation,
+              childDecorrelationResult.symbolsToPropagate,
+              childDecorrelationResult.correlatedPredicates,
+              childDecorrelationResult.correlatedSymbolsMapping,
+              childDecorrelationResult.constantSymbols,
+              constantSymbols.containsAll(newAggregation.getGroupingKeys())));
+    }
+
+    @Override
+    public Optional<DecorrelationResult> visitProject(ProjectNode node, Void context) {
+      Optional<DecorrelationResult> childDecorrelationResultOptional =
+          node.getChild().accept(this, null);
+      if (!childDecorrelationResultOptional.isPresent()) {
+        return Optional.empty();
+      }
+
+      DecorrelationResult childDecorrelationResult = childDecorrelationResultOptional.get();
+      Set<Symbol> nodeOutputSymbols = ImmutableSet.copyOf(node.getOutputSymbols());
+      List<Symbol> symbolsToAdd =
+          childDecorrelationResult.symbolsToPropagate.stream()
+              .filter(symbol -> !nodeOutputSymbols.contains(symbol))
+              .collect(toImmutableList());
+
+      Assignments assignments =
+          Assignments.builder().putAll(node.getAssignments()).putIdentities(symbolsToAdd).build();
+
+      return Optional.of(
+          new DecorrelationResult(
+              new ProjectNode(node.getPlanNodeId(), childDecorrelationResult.node, assignments),
+              childDecorrelationResult.symbolsToPropagate,
+              childDecorrelationResult.correlatedPredicates,
+              childDecorrelationResult.correlatedSymbolsMapping,
+              childDecorrelationResult.constantSymbols,
+              childDecorrelationResult.atMostSingleRow));
+    }
+
+    private Multimap<Symbol, Symbol> extractCorrelatedSymbolsMapping(
+        List<Expression> correlatedConjuncts) {
+      ImmutableMultimap.Builder<Symbol, Symbol> mapping = ImmutableMultimap.builder();
+      for (Expression conjunct : correlatedConjuncts) {
+        if (!(conjunct instanceof ComparisonExpression)) {
+          continue;
+        }
+
+        ComparisonExpression comparison = (ComparisonExpression) conjunct;
+
+        if (!(comparison.getLeft() instanceof SymbolReference
+            && comparison.getRight() instanceof SymbolReference
+            && comparison.getOperator() == EQUAL)) {
+          continue;
+        }
+
+        Symbol left = Symbol.from(comparison.getLeft());
+        Symbol right = Symbol.from(comparison.getRight());
+
+        if (correlation.contains(left) && !correlation.contains(right)) {
+          mapping.put(left, right);
+        }
+
+        if (correlation.contains(right) && !correlation.contains(left)) {
+          mapping.put(right, left);
+        }
+      }
+
+      return mapping.build();
+    }
+
+    private Set<Symbol> extractConstantSymbols(List<Expression> correlatedConjuncts) {
+      ImmutableSet.Builder<Symbol> constants = ImmutableSet.builder();
+
+      correlatedConjuncts.stream()
+          .filter(ComparisonExpression.class::isInstance)
+          .map(ComparisonExpression.class::cast)
+          .filter(comparison -> comparison.getOperator() == EQUAL)
+          .forEach(
+              comparison -> {
+                Expression left = comparison.getLeft();
+                Expression right = comparison.getRight();
+
+                if (!isCorrelated(left)
+                    && (left instanceof SymbolReference || isSimpleInjectiveCast(left))
+                    && isConstant(right)) {
+                  constants.add(getSymbol(left));
+                }
+
+                if (!isCorrelated(right)
+                    && (right instanceof SymbolReference || isSimpleInjectiveCast(right))
+                    && isConstant(left)) {
+                  constants.add(getSymbol(right));
+                }
+              });
+
+      return constants.build();
+    }
+
+    // checks whether the expression is a deterministic combination of correlation symbols
+    private boolean isConstant(Expression expression) {
+      return isDeterministic(expression)
+          && ImmutableSet.copyOf(correlation)
+              .containsAll(SymbolsExtractor.extractUnique(expression));
+    }
+
+    // checks whether the expression is an injective cast over a symbol
+    private boolean isSimpleInjectiveCast(Expression expression) {
+      if (!(expression instanceof Cast)) {
+        return false;
+      }
+      Cast cast = (Cast) expression;
+      if (!(cast.getExpression() instanceof SymbolReference)) {
+        return false;
+      }
+      // simply return true for now
+      return true;
+      /*Symbol sourceSymbol = Symbol.from(cast.getExpression());
+
+      Type sourceType = symbolAllocator.getTypes().getTableModelType(sourceSymbol);
+      Type targetType = typeManager.getType(toTypeSignature(((Cast) expression).getType()));
+
+      return typeCoercion.isInjectiveCoercion(sourceType, targetType);*/
+    }
+
+    private Symbol getSymbol(Expression expression) {
+      if (expression instanceof SymbolReference) {
+        return Symbol.from(expression);
+      }
+      return Symbol.from(((Cast) expression).getExpression());
+    }
+
+    private boolean isCorrelated(Expression expression) {
+      return correlation.stream().anyMatch(SymbolsExtractor.extractUnique(expression)::contains);
+    }
+  }
+
+  private static class DecorrelationResult {
+    final PlanNode node;
+    final Set<Symbol> symbolsToPropagate;
+    final List<Expression> correlatedPredicates;
+
+    // mapping from correlated symbols to their uncorrelated equivalence
+    final Multimap<Symbol, Symbol> correlatedSymbolsMapping;
+
+    // local (uncorrelated) symbols known to be constant based on their dependency on correlation
+    // symbols
+    // they are derived from the filter predicate, e.g.
+    // a = corr --> a is constant
+    // b = f(corr_1, corr_2, ...) --> b is constant provided that f is deterministic
+    // cast(c AS ...) = corr --> c is constant provided that cast is injective
+    final Set<Symbol> constantSymbols;
+
+    // If a subquery has at most single row for any correlation values?
+    final boolean atMostSingleRow;
+
+    DecorrelationResult(
+        PlanNode node,
+        Set<Symbol> symbolsToPropagate,
+        List<Expression> correlatedPredicates,
+        Multimap<Symbol, Symbol> correlatedSymbolsMapping,
+        Set<Symbol> constantSymbols,
+        boolean atMostSingleRow) {
+      this.node = node;
+      this.symbolsToPropagate = symbolsToPropagate;
+      this.correlatedPredicates = correlatedPredicates;
+      this.atMostSingleRow = atMostSingleRow;
+      this.correlatedSymbolsMapping = correlatedSymbolsMapping;
+      this.constantSymbols = constantSymbols;
+      checkState(
+          constantSymbols.containsAll(correlatedSymbolsMapping.values()),
+          "Expected constant symbols to contain all correlated symbols local equivalents");
+      checkState(
+          symbolsToPropagate.containsAll(constantSymbols),
+          "Expected symbols to propagate to contain all constant symbols");
+    }
+
+    SymbolMapper getCorrelatedSymbolMapper() {
+      return symbolMapper(
+          correlatedSymbolsMapping.asMap().entrySet().stream()
+              .collect(
+                  toImmutableMap(
+                      Map.Entry::getKey, symbols -> Iterables.getLast(symbols.getValue()))));
+    }
+
+    /**
+     * @return constant symbols from a perspective of a subquery
+     */
+    Set<Symbol> getConstantSymbols() {
+      return constantSymbols;
+    }
+  }
+
+  private Optional<DecorrelatedNode> decorrelatedNode(
+      List<Expression> correlatedPredicates, PlanNode node, List<Symbol> correlation) {
+    if (containsCorrelation(node, correlation)) {
+      // node is still correlated ; /
+      return Optional.empty();
+    }
+    return Optional.of(new DecorrelatedNode(correlatedPredicates, node));
+  }
+
+  private boolean containsCorrelation(PlanNode node, List<Symbol> correlation) {
+    return Sets.union(
+            SymbolsExtractor.extractUnique(node, lookup),
+            SymbolsExtractor.extractOutputSymbols(node, lookup))
+        .stream()
+        .anyMatch(correlation::contains);
+  }
+
+  public static class DecorrelatedNode {
+    private final List<Expression> correlatedPredicates;
+    private final PlanNode node;
+
+    public DecorrelatedNode(List<Expression> correlatedPredicates, PlanNode node) {
+      requireNonNull(correlatedPredicates, "correlatedPredicates is null");
+      this.correlatedPredicates = ImmutableList.copyOf(correlatedPredicates);
+      this.node = requireNonNull(node, "node is null");
+    }
+
+    public Optional<Expression> getCorrelatedPredicates() {
+      if (correlatedPredicates.isEmpty()) {
+        return Optional.empty();
+      }
+      return Optional.of(and(correlatedPredicates));
+    }
+
+    public PlanNode getNode() {
+      return node;
+    }
+  }
+}
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 f039c09a701..ba9487c2ba9 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
@@ -47,6 +47,7 @@ import org.apache.iotdb.db.queryengine.plan.relational.planner.Symbol;
 import org.apache.iotdb.db.queryengine.plan.relational.planner.SymbolAllocator;
 import org.apache.iotdb.db.queryengine.plan.relational.planner.ir.ReplaceSymbolInExpression;
 import org.apache.iotdb.db.queryengine.plan.relational.planner.node.AggregationNode;
+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.FilterNode;
 import org.apache.iotdb.db.queryengine.plan.relational.planner.node.JoinNode;
@@ -81,6 +82,7 @@ import java.util.Set;
 import java.util.stream.Collectors;
 
 import static com.google.common.base.Preconditions.checkArgument;
+import static com.google.common.base.Preconditions.checkState;
 import static com.google.common.collect.ImmutableMap.toImmutableMap;
 import static java.util.Objects.requireNonNull;
 import static org.apache.iotdb.commons.schema.table.column.TsTableColumnCategory.ATTRIBUTE;
@@ -984,6 +986,19 @@ public class PushPredicateIntoTableScan implements PlanOptimizer {
       return output;
     }
 
+    @Override
+    public PlanNode visitAssignUniqueId(AssignUniqueId node, RewriteContext context) {
+      Expression inheritedPredicate =
+          context.inheritedPredicate != null ? context.inheritedPredicate : TRUE_LITERAL;
+      Set<Symbol> predicateSymbols = extractUnique(inheritedPredicate);
+      checkState(
+          !predicateSymbols.contains(node.getIdColumn()),
+          "UniqueId in predicate is not yet supported");
+      PlanNode rewrittenChild = node.getChild().accept(this, context);
+      node.replaceChildren(ImmutableList.of(rewrittenChild));
+      return node;
+    }
+
     @Override
     public PlanNode visitInsertTablet(InsertTabletNode node, RewriteContext context) {
       return node;
diff --git a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/optimizations/TransformQuantifiedComparisonApplyToCorrelatedJoin.java b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/optimizations/TransformQuantifiedComparisonApplyToCorrelatedJoin.java
index 00fecc1a69a..a867faaa6e5 100644
--- a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/optimizations/TransformQuantifiedComparisonApplyToCorrelatedJoin.java
+++ b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/optimizations/TransformQuantifiedComparisonApplyToCorrelatedJoin.java
@@ -21,12 +21,7 @@ package org.apache.iotdb.db.queryengine.plan.relational.planner.optimizations;
 
 import org.apache.iotdb.db.queryengine.common.QueryId;
 import org.apache.iotdb.db.queryengine.plan.planner.plan.node.PlanNode;
-import org.apache.iotdb.db.queryengine.plan.relational.function.BoundSignature;
-import org.apache.iotdb.db.queryengine.plan.relational.function.FunctionId;
-import org.apache.iotdb.db.queryengine.plan.relational.function.FunctionKind;
-import org.apache.iotdb.db.queryengine.plan.relational.metadata.FunctionNullability;
 import org.apache.iotdb.db.queryengine.plan.relational.metadata.Metadata;
-import org.apache.iotdb.db.queryengine.plan.relational.metadata.ResolvedFunction;
 import org.apache.iotdb.db.queryengine.plan.relational.planner.Assignments;
 import org.apache.iotdb.db.queryengine.plan.relational.planner.SimplePlanRewriter;
 import org.apache.iotdb.db.queryengine.plan.relational.planner.Symbol;
@@ -54,7 +49,6 @@ import org.apache.tsfile.read.common.type.Type;
 
 import java.util.EnumSet;
 import java.util.List;
-import java.util.Locale;
 import java.util.Optional;
 import java.util.function.Function;
 
@@ -66,6 +60,7 @@ import static org.apache.iotdb.db.queryengine.plan.relational.planner.ir.IrUtils
 import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.AggregationNode.globalAggregation;
 import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.AggregationNode.singleAggregation;
 import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.ApplyNode.Quantifier.ALL;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.optimizations.Util.getResolvedBuiltInAggregateFunction;
 import static org.apache.iotdb.db.queryengine.plan.relational.sql.ast.BooleanLiteral.FALSE_LITERAL;
 import static org.apache.iotdb.db.queryengine.plan.relational.sql.ast.BooleanLiteral.TRUE_LITERAL;
 import static org.apache.iotdb.db.queryengine.plan.relational.sql.ast.ComparisonExpression.Operator.EQUAL;
@@ -142,7 +137,7 @@ public class TransformQuantifiedComparisonApplyToCorrelatedJoin implements PlanO
                   minValue,
                       new AggregationNode.Aggregation(
                           getResolvedBuiltInAggregateFunction(
-                              "min", ImmutableList.of(outputColumnType)),
+                              metadata, "min", ImmutableList.of(outputColumnType)),
                           outputColumnReferences,
                           false,
                           Optional.empty(),
@@ -151,7 +146,7 @@ public class TransformQuantifiedComparisonApplyToCorrelatedJoin implements PlanO
                   maxValue,
                       new AggregationNode.Aggregation(
                           getResolvedBuiltInAggregateFunction(
-                              "max", ImmutableList.of(outputColumnType)),
+                              metadata, "max", ImmutableList.of(outputColumnType)),
                           outputColumnReferences,
                           false,
                           Optional.empty(),
@@ -160,7 +155,7 @@ public class TransformQuantifiedComparisonApplyToCorrelatedJoin implements PlanO
                   countAllValue,
                       new AggregationNode.Aggregation(
                           getResolvedBuiltInAggregateFunction(
-                              "count_all", ImmutableList.of(outputColumnType)),
+                              metadata, "count_all", ImmutableList.of(outputColumnType)),
                           outputColumnReferences,
                           false,
                           Optional.empty(),
@@ -169,7 +164,7 @@ public class TransformQuantifiedComparisonApplyToCorrelatedJoin implements PlanO
                   countNonNullValue,
                       new AggregationNode.Aggregation(
                           getResolvedBuiltInAggregateFunction(
-                              "count", ImmutableList.of(outputColumnType)),
+                              metadata, "count", ImmutableList.of(outputColumnType)),
                           outputColumnReferences,
                           false,
                           Optional.empty(),
@@ -197,18 +192,6 @@ public class TransformQuantifiedComparisonApplyToCorrelatedJoin implements PlanO
           join, Assignments.of(quantifiedComparisonSymbol, valueComparedToSubquery));
     }
 
-    private ResolvedFunction getResolvedBuiltInAggregateFunction(
-        String functionName, List<Type> argumentTypes) {
-      // The same as the code in ExpressionAnalyzer
-      Type type = metadata.getFunctionReturnType(functionName, argumentTypes);
-      return new ResolvedFunction(
-          new BoundSignature(functionName.toLowerCase(Locale.ENGLISH), type, argumentTypes),
-          new FunctionId("noop"),
-          FunctionKind.AGGREGATE,
-          true,
-          FunctionNullability.getAggregationFunctionNullability(argumentTypes.size()));
-    }
-
     public Expression rewriteUsingBounds(
         ApplyNode.QuantifiedComparison quantifiedComparison,
         Symbol minValue,
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 2b414438983..2e32cc13acd 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
@@ -31,6 +31,7 @@ import org.apache.iotdb.db.queryengine.plan.relational.planner.SymbolAllocator;
 import org.apache.iotdb.db.queryengine.plan.relational.planner.ir.DeterminismEvaluator;
 import org.apache.iotdb.db.queryengine.plan.relational.planner.node.AggregationNode;
 import org.apache.iotdb.db.queryengine.plan.relational.planner.node.ApplyNode;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.node.AssignUniqueId;
 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;
@@ -760,6 +761,18 @@ public class UnaliasSymbolReferences implements PlanOptimizer {
               newSemiJoinOutput),
           outputMapping);
     }
+
+    @Override
+    public PlanAndMappings visitAssignUniqueId(AssignUniqueId node, UnaliasContext context) {
+      PlanAndMappings rewrittenSource = node.getChild().accept(this, context);
+      Map<Symbol, Symbol> mapping = new HashMap<>(rewrittenSource.getMappings());
+      SymbolMapper mapper = symbolMapper(mapping);
+
+      Symbol newUnique = mapper.map(node.getIdColumn());
+
+      return new PlanAndMappings(
+          new AssignUniqueId(node.getPlanNodeId(), rewrittenSource.getRoot(), newUnique), mapping);
+    }
   }
 
   private static class UnaliasContext {
diff --git a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/optimizations/Util.java b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/optimizations/Util.java
index 83798c451bc..523dd09f9f9 100644
--- a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/optimizations/Util.java
+++ b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/optimizations/Util.java
@@ -21,6 +21,11 @@ package org.apache.iotdb.db.queryengine.plan.relational.planner.optimizations;
 
 import org.apache.iotdb.commons.udf.builtin.relational.TableBuiltinAggregationFunction;
 import org.apache.iotdb.db.queryengine.common.QueryId;
+import org.apache.iotdb.db.queryengine.plan.relational.function.BoundSignature;
+import org.apache.iotdb.db.queryengine.plan.relational.function.FunctionId;
+import org.apache.iotdb.db.queryengine.plan.relational.function.FunctionKind;
+import org.apache.iotdb.db.queryengine.plan.relational.metadata.FunctionNullability;
+import org.apache.iotdb.db.queryengine.plan.relational.metadata.Metadata;
 import org.apache.iotdb.db.queryengine.plan.relational.metadata.ResolvedFunction;
 import org.apache.iotdb.db.queryengine.plan.relational.planner.Symbol;
 import org.apache.iotdb.db.queryengine.plan.relational.planner.SymbolAllocator;
@@ -33,6 +38,8 @@ import org.apache.tsfile.read.common.type.Type;
 import org.apache.tsfile.utils.Pair;
 
 import java.util.LinkedHashMap;
+import java.util.List;
+import java.util.Locale;
 import java.util.Map;
 import java.util.Optional;
 
@@ -217,4 +224,16 @@ public class Util {
             node.getGroupIdSymbol()),
         rightResult);
   }
+
+  public static ResolvedFunction getResolvedBuiltInAggregateFunction(
+      Metadata metadata, String functionName, List<Type> argumentTypes) {
+    // The same as the code in ExpressionAnalyzer
+    Type type = metadata.getFunctionReturnType(functionName, argumentTypes);
+    return new ResolvedFunction(
+        new BoundSignature(functionName.toLowerCase(Locale.ENGLISH), type, argumentTypes),
+        new FunctionId("noop"),
+        FunctionKind.AGGREGATE,
+        true,
+        FunctionNullability.getAggregationFunctionNullability(argumentTypes.size()));
+  }
 }
diff --git a/iotdb-core/datanode/src/test/java/org/apache/iotdb/db/queryengine/plan/relational/planner/assertions/AssignUniqueIdMatcher.java b/iotdb-core/datanode/src/test/java/org/apache/iotdb/db/queryengine/plan/relational/planner/assertions/AssignUniqueIdMatcher.java
new file mode 100644
index 00000000000..6ff4a6b9e4c
--- /dev/null
+++ b/iotdb-core/datanode/src/test/java/org/apache/iotdb/db/queryengine/plan/relational/planner/assertions/AssignUniqueIdMatcher.java
@@ -0,0 +1,49 @@
+/*
+ * 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.assertions;
+
+import org.apache.iotdb.db.queryengine.common.SessionInfo;
+import org.apache.iotdb.db.queryengine.plan.planner.plan.node.PlanNode;
+import org.apache.iotdb.db.queryengine.plan.relational.metadata.Metadata;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.Symbol;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.node.AssignUniqueId;
+
+import java.util.Optional;
+
+import static com.google.common.base.MoreObjects.toStringHelper;
+
+public class AssignUniqueIdMatcher implements RvalueMatcher {
+  @Override
+  public Optional<Symbol> getAssignedSymbol(
+      PlanNode node, SessionInfo sessionInfo, Metadata metadata, SymbolAliases symbolAliases) {
+    if (!(node instanceof AssignUniqueId)) {
+      return Optional.empty();
+    }
+
+    AssignUniqueId assignUniqueIdNode = (AssignUniqueId) node;
+
+    return Optional.of(assignUniqueIdNode.getIdColumn());
+  }
+
+  @Override
+  public String toString() {
+    return toStringHelper(this).toString();
+  }
+}
diff --git a/iotdb-core/datanode/src/test/java/org/apache/iotdb/db/queryengine/plan/relational/planner/assertions/PlanMatchPattern.java b/iotdb-core/datanode/src/test/java/org/apache/iotdb/db/queryengine/plan/relational/planner/assertions/PlanMatchPattern.java
index 5c2ce8e2f80..6d8b3e98628 100644
--- a/iotdb-core/datanode/src/test/java/org/apache/iotdb/db/queryengine/plan/relational/planner/assertions/PlanMatchPattern.java
+++ b/iotdb-core/datanode/src/test/java/org/apache/iotdb/db/queryengine/plan/relational/planner/assertions/PlanMatchPattern.java
@@ -28,6 +28,7 @@ import org.apache.iotdb.db.queryengine.plan.relational.planner.iterative.GroupRe
 import org.apache.iotdb.db.queryengine.plan.relational.planner.node.AggregationNode;
 import org.apache.iotdb.db.queryengine.plan.relational.planner.node.AggregationTableScanNode;
 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.CollectNode;
 import org.apache.iotdb.db.queryengine.plan.relational.planner.node.DeviceTableScanNode;
 import org.apache.iotdb.db.queryengine.plan.relational.planner.node.EnforceSingleRowNode;
@@ -472,6 +473,11 @@ public final class PlanMatchPattern {
         .with(new SemiJoinMatcher(sourceSymbolAlias, filteringSymbolAlias, outputAlias));
   }
 
+  public static PlanMatchPattern assignUniqueId(String uniqueSymbolAlias, PlanMatchPattern source) {
+    return node(AssignUniqueId.class, source)
+        .withAlias(uniqueSymbolAlias, new AssignUniqueIdMatcher());
+  }
+
   public static PlanMatchPattern streamSort(PlanMatchPattern source) {
     return node(StreamSortNode.class, source);
   }


(iotdb) 07/08: Merge branch 'support_uncorrelated_quantified_comparison' into support_exists_and_correlate

Posted by la...@apache.org.
This is an automated email from the ASF dual-hosted git repository.

lancelly pushed a commit to branch support_exists_and_correlate
in repository https://gitbox.apache.org/repos/asf/iotdb.git

commit 56aa492d379bc00c7ba15d4ed3daaba3edc0dea0
Merge: 87b485ef8c4 231a4f3da68
Author: lancelly <14...@qq.com>
AuthorDate: Fri Jan 17 16:33:09 2025 +0800

    Merge branch 'support_uncorrelated_quantified_comparison' into support_exists_and_correlate

 .github/workflows/cluster-it-1c1d.yml              |   2 +-
 .github/workflows/cluster-it-1c1d1a.yml            |   2 +-
 .github/workflows/cluster-it-1c3d.yml              |   2 +-
 .github/workflows/compile-check.yml                |   2 +-
 .github/workflows/daily-it.yml                     |   2 +-
 .github/workflows/daily-ut.yml                     |   2 +-
 .github/workflows/dependency-check.yml             |   2 +-
 .github/workflows/pipe-it-2cluster.yml             |   2 +-
 .github/workflows/sonar-codecov.yml                |   2 +-
 .github/workflows/table-cluster-it-1c1d.yml        |   2 +-
 .github/workflows/table-cluster-it-1c3d.yml        |   2 +-
 .github/workflows/unit-test.yml                    |   2 +-
 .github/workflows/vulnerability-check.yml          |   2 +-
 .mvn/develocity.xml                                |   3 +-
 .mvn/extensions.xml                                |   4 +-
 .../java/org/apache/iotdb/CountPointProcessor.java |   2 +
 .../iotdb/AlignedTimeseriesSessionExample.java     |  67 +--
 .../org/apache/iotdb/SessionConcurrentExample.java |  23 -
 .../main/java/org/apache/iotdb/SessionExample.java | 107 +---
 .../org/apache/iotdb/trigger/LoggerTrigger.java    |  12 +-
 .../iotdb/trigger/StatisticsUpdaterTrigger.java    |   4 +-
 .../java/org/apache/iotdb/it/env/EnvFactory.java   |   5 +
 .../iotdb/it/env/cluster/env/AbstractEnv.java      |   9 +
 .../iotdb/it/env/cluster/node/AINodeWrapper.java   |   5 +
 .../it/env/cluster/node/AbstractNodeWrapper.java   |  12 +
 .../it/env/cluster/node/ConfigNodeWrapper.java     |   5 +
 .../iotdb/it/env/cluster/node/DataNodeWrapper.java |   5 +
 .../iotdb/it/env/remote/env/RemoteServerEnv.java   |   5 +
 .../org/apache/iotdb/it/utils/TsFileGenerator.java |  87 +--
 .../iotdb/it/utils/TsFileTableGenerator.java       |  78 +--
 .../java/org/apache/iotdb/itbase/env/BaseEnv.java  |   3 +
 ...ionMigrateDataNodeCrashITFrameworkForIoTV1.java |   2 +-
 ...ionMigrateDataNodeCrashITFrameworkForIoTV2.java |   2 +-
 ...oTDBRegionOperationReliabilityITFramework.java} | 174 +++---
 .../IoTDBRegionGroupExpandAndShrinkForIoTV1IT.java | 157 +++++
 .../commit/IoTDBRegionReconstructForIoTV1IT.java   | 141 +++++
 .../IoTDBRegionMigrateNormalITForIoTV2Batch.java   |   4 +-
 .../IoTDBRegionMigrateOtherITForIoTV2Batch.java    |   4 +-
 .../IoTDBRegionMigrateNormalITForIoTV2Stream.java  |   4 +-
 .../IoTDBRegionMigrateOtherITForIoTV2Stream.java   |   4 +-
 .../IoTDBRegionMigrateDataNodeCrashForIoTV1IT.java |   4 +-
 ...TDBRegionMigrateDataNodeCrashForIoTV2Batch.java |   4 +-
 ...DBRegionMigrateDataNodeCrashForIoTV2Stream.java |   4 +-
 .../IoTDBRegionMigrateClusterCrashIoTV1IT.java     |   4 +-
 .../IoTDBRegionMigrateConfigNodeCrashIoTV1IT.java  |   4 +-
 .../IoTDBRegionMigrateClusterCrashIoTV2Batch.java  |   4 +-
 ...oTDBRegionMigrateConfigNodeCrashIoTV2Batch.java |   4 +-
 .../IoTDBRegionMigrateClusterCrashIoTV2Stream.java |   4 +-
 ...TDBRegionMigrateConfigNodeCrashIoTV2Stream.java |   4 +-
 .../IoTDBRemoveDataNodeITFramework.java            |  22 +-
 .../org/apache/iotdb/db/it/IoTDBLoadTsFileIT.java  |   2 +-
 .../iotdb/db/it/IoTDBLoadTsFileWithModIT.java      | 172 ++++++
 .../it/IoTDBSyntaxConventionStringLiteralIT.java   |   3 +-
 .../org/apache/iotdb/db/it/utils/TestUtils.java    | 176 +++++-
 .../pipe/it/autocreate/IoTDBPipeAutoDropIT.java    |  10 +-
 .../pipe/it/autocreate/IoTDBPipeIdempotentIT.java  |  93 +++
 .../pipe/it/autocreate/IoTDBPipeNullValueIT.java   |  28 +-
 .../pipe/it/manual/IoTDBPipeReqAutoSliceIT.java    |   8 +-
 .../pipe/it/manual/IoTDBPipeTableManualIT.java     | 293 ++++++++++
 .../manual/IoTDBPipeTypeConversionISessionIT.java  |  10 +-
 .../pipe/it/tablemodel/IoTDBPipeAutoDropIT.java    |  10 +-
 .../pipe/it/tablemodel/IoTDBPipeDataSinkIT.java    |  41 +-
 .../pipe/it/tablemodel/IoTDBPipeProtocolIT.java    |   1 -
 .../pipe/it/tablemodel/IoTDBPipeSyntaxIT.java      |   2 +-
 .../IoTDBPipeTypeConversionISessionIT.java         |  97 +++-
 .../iotdb/pipe/it/tablemodel/TableModelUtils.java  |  38 +-
 .../relational/it/db/it/IoTDBDeletionTableIT.java  |   2 +-
 .../relational/it/db/it/IoTDBInsertTableIT.java    |   2 +-
 .../udf/IoTDBUserDefinedAggregateFunctionIT.java   |  53 +-
 .../IoTDBAlignByDeviceWithTemplateTableIT.java     |   4 +-
 .../scalar/IoTDBFormatFunctionTableIT.java         |  21 +-
 ...oTDBNoSelectExpressionAfterAnalyzedTableIT.java |   6 +-
 .../query/old/query/IoTDBNullValueFillTableIT.java | 327 -----------
 .../it/query/old/query/IoTDBResultSetTableIT.java  |   2 +-
 .../query/old/query/IoTDBSelectSchemaTableIT.java  |  22 +-
 .../it/query/recent/IoTDBTableAggregationIT.java   | 644 ++++++++++++++++++++-
 .../it/rest/it/IoTDBRestServiceCaseWhenThenIT.java |   7 +-
 .../it/rest/it/IoTDBRestServiceFlushQueryIT.java   |   6 +-
 .../relational/it/rest/it/IoTDBRestServiceIT.java  |   3 +-
 ...IT.java => IoTDBRestServiceInsertValuesIT.java} |  20 +-
 .../iotdb/relational/it/schema/IoTDBDeviceIT.java  |  47 +-
 .../iotdb/relational/it/schema/IoTDBTableIT.java   | 122 ++--
 .../it/session/IoTDBSessionRelationalIT.java       |  29 +-
 .../iotdb/session/it/IoTDBSessionComplexIT.java    |   8 +-
 .../iotdb/session/it/IoTDBSessionSimpleIT.java     |  78 ++-
 .../session/it/IoTDBSessionSyntaxConventionIT.java |  18 +-
 .../org/apache/iotdb/session/it/SessionIT.java     |  32 +-
 .../it/local/IoTDBSubscriptionDataTypeIT.java      |   8 +-
 .../java/org/apache/iotdb/util/MagicUtils.java     |  61 ++
 .../java/org/apache/iotdb/pipe/api/PipePlugin.java |  37 ++
 .../iotdb/pipe/api/annotation/TableModel.java      |  23 +-
 .../iotdb/pipe/api/annotation/TreeModel.java       |  23 +-
 .../parameter/PipeParameterValidator.java          |  42 +-
 .../org/apache/iotdb/tool/tsfile/ExportTsFile.java |   3 -
 .../java/org/apache/iotdb/rpc/TSStatusCode.java    |   3 +
 .../java/org/apache/iotdb/session/Session.java     |  27 +-
 .../payload/SubscriptionSessionDataSet.java        |  12 +-
 .../apache/iotdb/session/util/SessionUtils.java    |  25 +-
 .../java/org/apache/iotdb/session/TabletTest.java  |  28 +-
 .../iotdb/session/util/SessionUtilsTest.java       |  16 +-
 .../org/apache/iotdb/db/qp/sql/IoTDBSqlParser.g4   |  16 +-
 .../antlr4/org/apache/iotdb/db/qp/sql/SqlLexer.g4  |   8 +
 .../consensus/request/ConfigPhysicalPlan.java      |  10 +-
 .../consensus/request/ConfigPhysicalPlanType.java  |   2 +
 .../request/ConfigPhysicalPlanVisitor.java         | 110 +++-
 .../write/pipe/payload/PipeCreateTablePlan.java}   |  18 +-
 .../pipe/payload/PipeDeactivateTemplatePlan.java   |  22 +-
 .../write/pipe/payload/PipeDeleteDevicesPlan.java  | 130 +++++
 .../request/write/table/PreCreateTablePlan.java    |  11 +-
 .../response/pipe/plugin/PipePluginTableResp.java  |  27 +-
 .../response/pipe/task/PipeTableResp.java          |   2 +-
 .../iotdb/confignode/manager/ClusterManager.java   |   2 +-
 .../iotdb/confignode/manager/ConfigManager.java    |  38 +-
 .../apache/iotdb/confignode/manager/IManager.java  |  13 +
 .../iotdb/confignode/manager/ProcedureManager.java | 491 ++++++++++++----
 .../payload/PipeTransferConfigSnapshotSealReq.java |  32 +-
 .../protocol/IoTDBConfigRegionAirGapConnector.java |   8 +
 .../protocol/IoTDBConfigRegionConnector.java       |  10 +-
 .../coordinator/plugin/PipePluginCoordinator.java  |  29 +
 .../pipe/event/PipeConfigRegionSnapshotEvent.java  |   3 +-
 .../extractor/ConfigRegionListeningFilter.java     |  89 +--
 .../pipe/extractor/ConfigRegionListeningQueue.java |  23 +-
 .../pipe/extractor/IoTDBConfigRegionExtractor.java |  74 ++-
 ...ConfigPhysicalPlanTablePatternParseVisitor.java | 135 +++++
 ...ConfigPhysicalPlanTreePatternParseVisitor.java} |   6 +-
 .../receiver/protocol/IoTDBConfigNodeReceiver.java | 170 +++++-
 .../PipeConfigPhysicalPlanTSStatusVisitor.java     |  76 +++
 .../manager/schema/ClusterSchemaManager.java       | 126 ++--
 .../persistence/executor/ConfigPlanExecutor.java   |   1 +
 .../confignode/persistence/pipe/PipeInfo.java      |   2 +-
 .../persistence/pipe/PipePluginInfo.java           |  39 +-
 .../confignode/persistence/pipe/PipeTaskInfo.java  |   7 +-
 .../schema/CNPhysicalPlanGenerator.java            |  77 ++-
 .../confignode/persistence/schema/ConfigMTree.java |  13 +-
 .../schema/ConfignodeSnapshotParser.java           |  30 +-
 .../procedure/env/ConfigNodeProcedureEnv.java      |  23 +-
 .../procedure/env/RegionMaintainHandler.java       |  13 +-
 .../impl/pipe/task/CreatePipeProcedureV2.java      |  42 +-
 .../impl/region/AddRegionPeerProcedure.java        |  84 +--
 .../impl/region/ReconstructRegionProcedure.java    | 209 +++++++
 .../impl/region/RegionMigrateProcedure.java        |  39 +-
 .../impl/region/RegionOperationProcedure.java      |  39 +-
 .../impl/region/RemoveRegionPeerProcedure.java     |  52 +-
 .../impl/schema/DeleteDatabaseProcedure.java       |   2 +-
 .../impl/schema/DeleteTimeSeriesProcedure.java     |   4 +-
 .../table/AbstractAlterOrDropTableProcedure.java   |  10 +-
 .../impl/schema/table/AddTableColumnProcedure.java |  16 +-
 .../impl/schema/table/CreateTableProcedure.java    |  20 +-
 .../impl/schema/table/DeleteDevicesProcedure.java  |  43 +-
 .../schema/table/DropTableColumnProcedure.java     |  21 +-
 .../impl/schema/table/DropTableProcedure.java      |  24 +-
 .../schema/table/RenameTableColumnProcedure.java   |  18 +-
 .../schema/table/SetTablePropertiesProcedure.java  |  18 +-
 .../impl/trigger/CreateTriggerProcedure.java       |   9 +-
 .../procedure/state/ReconstructRegionState.java    |  10 +-
 .../procedure/store/ProcedureFactory.java          |  40 +-
 .../confignode/procedure/store/ProcedureType.java  |  12 +-
 .../thrift/ConfigNodeRPCServiceProcessor.java      |  24 +
 .../request/ConfigPhysicalPlanSerDeTest.java       |  40 ++
 .../response/pipe/PipePluginTableRespTest.java     |   4 +-
 .../connector/PipeConfigNodeThriftRequestTest.java |  12 +-
 ...igPhysicalPlanTablePatternParseVisitorTest.java | 133 +++++
 ...igPhysicalPlanTreePatternParseVisitorTest.java} |  44 +-
 .../pipe/annotation/PipePluginAnnotationTest.java} |  23 +-
 .../pipe/receiver/PipeEnrichedProcedureTest.java   | 194 +++++++
 .../schema/table/AddTableColumnProcedureTest.java  |   8 +-
 .../schema/table/CreateTableProcedureTest.java     |   5 +-
 .../schema/table/DeleteDevicesProcedureTest.java   |   5 +-
 .../schema/table/DropTableColumnProcedureTest.java |   4 +-
 .../impl/schema/table/DropTableProcedureTest.java  |   4 +-
 .../table/RenameTableColumnProcedureTest.java      |   4 +-
 .../table/SetTablePropertiesProcedureTest.java     |   6 +-
 .../apache/iotdb/consensus/pipe/PipeConsensus.java |  57 +-
 .../apache/iotdb/consensus/ratis/RatisClient.java  |   6 +-
 .../apache/iotdb/consensus/ratis/utils/Utils.java  |   1 +
 .../dataregion/DataExecutionVisitor.java           |   9 +-
 .../schemaregion/SchemaRegionStateMachine.java     |  15 +-
 .../pipe/agent/plugin/PipeDataNodePluginAgent.java |  20 +-
 .../dataregion/PipeDataRegionPluginAgent.java      |  49 ++
 .../agent/task/connection/PipeEventCollector.java  |  35 +-
 .../PipeTransferDataNodeHandshakeV1Req.java        |  10 +-
 .../evolvable/request/PipeTransferPlanNodeReq.java |  18 +-
 .../request/PipeTransferSchemaSnapshotSealReq.java |  94 ++-
 .../request/PipeTransferTabletInsertNodeReqV2.java |   2 +-
 .../request/PipeTransferTsFileSealWithModReq.java  |  42 +-
 .../airgap/IoTDBDataNodeAirGapConnector.java       |   4 +
 .../airgap/IoTDBDataRegionAirGapConnector.java     |   4 +
 .../airgap/IoTDBSchemaRegionAirGapConnector.java   |  19 +-
 .../protocol/legacy/IoTDBLegacyPipeConnector.java  |   2 +
 .../connector/protocol/opcua/OpcUaConnector.java   |   4 +
 .../connector/protocol/opcua/OpcUaNameSpace.java   |  36 +-
 .../pipeconsensus/PipeConsensusAsyncConnector.java |   4 +
 .../pipeconsensus/PipeConsensusSyncConnector.java  |   4 +
 .../async/IoTDBDataRegionAsyncConnector.java       |   4 +
 .../thrift/sync/IoTDBDataNodeSyncConnector.java    |   4 +
 .../thrift/sync/IoTDBDataRegionSyncConnector.java  |   4 +
 .../thrift/sync/IoTDBSchemaRegionConnector.java    |  26 +-
 .../protocol/websocket/WebSocketConnector.java     |   2 +
 .../protocol/writeback/WriteBackConnector.java     | 197 ++++++-
 .../util/builder/PipeTableModeTsFileBuilder.java   |   6 +-
 .../util/builder/PipeTreeModelTsFileBuilder.java   |   4 +-
 .../sorter/PipeTableModelTabletEventSorter.java    |  54 +-
 .../sorter/PipeTreeModelTabletEventSorter.java     |  24 +-
 .../pipe/consensus/deletion/DeletionResource.java  |   2 +-
 .../db/pipe/event/common/PipeInsertionEvent.java   |   5 +-
 .../common/deletion/PipeDeleteDataNodeEvent.java   |  24 +-
 .../db/pipe/event/common/row/PipeRowCollector.java |   2 +-
 .../schema/PipeSchemaRegionSnapshotEvent.java      |  75 ++-
 .../schema/PipeSchemaSerializableEventType.java    |  15 +-
 .../common/tablet/PipeRawTabletInsertionEvent.java |   2 +-
 .../tablet/parser/TabletInsertionEventParser.java  |   8 +-
 .../TabletInsertionEventTreePatternParser.java     |  13 +-
 .../scan/TsFileInsertionEventScanParser.java       |  42 +-
 .../table/TsFileInsertionEventTableParser.java     |   2 +-
 .../dataregion/DataRegionListeningFilter.java      |  21 +-
 .../dataregion/IoTDBDataRegionExtractor.java       | 136 +++--
 ...oricalDataRegionTsFileAndDeletionExtractor.java |   4 +-
 .../listener/PipeInsertionDataNodeListener.java    |  27 +-
 .../schemaregion/IoTDBSchemaRegionExtractor.java   |  28 +-
 .../PipePlanTablePatternParseVisitor.java          |  77 +++
 ...r.java => PipePlanTreePatternParseVisitor.java} |   7 +-
 .../schemaregion/SchemaRegionListeningFilter.java  |  87 +--
 .../schemaregion/SchemaRegionListeningQueue.java   |   8 +-
 .../pipe/metric/PipeDataNodeReceiverMetrics.java   |  48 +-
 .../processor/aggregate/AggregateProcessor.java    |   2 +
 .../StandardStatisticsOperatorProcessor.java       |   2 +
 .../processor/TumblingWindowingProcessor.java      |   2 +
 .../changing/ChangingValueSamplingProcessor.java   |   2 +
 .../sdt/SwingingDoorTrendingSamplingProcessor.java |   2 +
 .../tumbling/TumblingTimeSamplingProcessor.java    |   2 +
 .../pipeconsensus/PipeConsensusProcessor.java      |   4 +
 .../schemachange/RenameDatabaseProcessor.java      |   2 +
 .../twostage/plugin/TwoStageCountProcessor.java    |  37 +-
 .../pipeconsensus/PipeConsensusReceiver.java       |   2 +
 .../protocol/thrift/IoTDBDataNodeReceiver.java     | 212 +++++--
 .../statement/PipeConvertedInsertRowStatement.java |  30 +-
 .../PipeConvertedInsertTabletStatement.java        |  48 +-
 .../visitor/PipePlanToStatementVisitor.java        |  54 +-
 .../PipeStatementTablePatternParseVisitor.java     |  33 +-
 ...a => PipeStatementTreePatternParseVisitor.java} |   6 +-
 ...leStatementDataTypeConvertExecutionVisitor.java |   3 +-
 ...eeStatementDataTypeConvertExecutionVisitor.java |   3 +-
 ...r.java => PipeTreeStatementToBatchVisitor.java} |   2 +-
 .../pipe/resource/memory/PipeMemoryWeightUtil.java |  21 +-
 .../resource/tsfile/PipeTsFileResourceManager.java |   5 +-
 .../iotdb/db/protocol/client/ConfigNodeClient.java |  29 +
 .../impl/DataNodeInternalRPCServiceImpl.java       |   2 +
 .../execution/executor/RegionWriteExecutor.java    |  55 +-
 .../schema/source/TableDeviceQuerySource.java      |   4 +-
 .../relational/aggregation/AccumulatorFactory.java | 246 +++++++-
 .../relational/aggregation/AggregationMask.java    | 204 +++++++
 .../relational/aggregation/AvgAccumulator.java     | 122 ++--
 .../relational/aggregation/CountAccumulator.java   |  22 +-
 .../relational/aggregation/CountIfAccumulator.java |  22 +-
 .../relational/aggregation/ExtremeAccumulator.java |  94 ++-
 .../relational/aggregation/FirstAccumulator.java   | 158 +++--
 .../relational/aggregation/FirstByAccumulator.java | 164 ++++--
 .../aggregation/FirstByDescAccumulator.java        | 132 ++++-
 .../aggregation/FirstDescAccumulator.java          | 126 +++-
 .../relational/aggregation/LastAccumulator.java    | 140 ++++-
 .../relational/aggregation/LastByAccumulator.java  | 146 ++++-
 .../aggregation/LastByDescAccumulator.java         | 150 ++++-
 .../aggregation/LastDescAccumulator.java           | 144 ++++-
 ...dAccumulator.java => MaskedRecordIterator.java} |  40 +-
 .../relational/aggregation/MaxAccumulator.java     | 140 ++++-
 .../relational/aggregation/MinAccumulator.java     | 140 ++++-
 .../relational/aggregation/SumAccumulator.java     | 114 +++-
 .../relational/aggregation/TableAccumulator.java   |   2 +-
 .../relational/aggregation/TableAggregator.java    |   5 +-
 .../aggregation/TableMaxMinByBaseAccumulator.java  | 146 +++--
 .../aggregation/TableModeAccumulator.java          | 192 ++++--
 .../aggregation/TableVarianceAccumulator.java      | 158 +++--
 .../UserDefinedAggregateFunctionAccumulator.java   |   8 +-
 .../aggregation/grouped/GroupedAccumulator.java    |   4 +-
 .../aggregation/grouped/GroupedAggregator.java     |   6 +-
 .../aggregation/grouped/GroupedAvgAccumulator.java | 119 +++-
 .../grouped/GroupedCountAccumulator.java           |  22 +-
 .../grouped/GroupedCountIfAccumulator.java         |  22 +-
 .../grouped/GroupedExtremeAccumulator.java         |  95 ++-
 .../grouped/GroupedFirstAccumulator.java           | 153 ++++-
 .../grouped/GroupedFirstByAccumulator.java         | 191 ++++--
 .../grouped/GroupedLastAccumulator.java            | 133 ++++-
 .../grouped/GroupedLastByAccumulator.java          | 150 ++++-
 .../aggregation/grouped/GroupedMaxAccumulator.java | 141 ++++-
 .../grouped/GroupedMaxMinByBaseAccumulator.java    | 147 ++++-
 .../aggregation/grouped/GroupedMinAccumulator.java | 141 ++++-
 .../grouped/GroupedModeAccumulator.java            | 246 ++++++--
 .../aggregation/grouped/GroupedSumAccumulator.java | 111 +++-
 .../GroupedUserDefinedAggregateAccumulator.java    |  14 +-
 .../grouped/GroupedVarianceAccumulator.java        | 159 +++--
 .../aggregation/grouped/hash/GroupByHash.java      |   2 +
 .../aggregation/grouped/hash/MarkDistinctHash.java |  90 +++
 .../iotdb/db/queryengine/plan/Coordinator.java     |   4 +
 .../db/queryengine/plan/analyze/AnalyzeUtils.java  |   9 +-
 .../queryengine/plan/analyze/AnalyzeVisitor.java   |   4 +-
 .../analyze/load/LoadTsFileTableSchemaCache.java   |   3 +-
 .../load/LoadTsFileToTableModelAnalyzer.java       |   7 +-
 .../load/LoadTsFileToTreeModelAnalyzer.java        |   5 +-
 .../analyze/load/LoadTsFileTreeSchemaCache.java    |   3 +-
 .../execution/config/TableConfigTaskVisitor.java   | 141 ++++-
 .../execution/config/TreeConfigTaskVisitor.java    |  30 +-
 .../config/executor/ClusterConfigTaskExecutor.java | 139 ++++-
 .../config/executor/IConfigTaskExecutor.java       |  19 +-
 .../config/metadata/DropPipePluginTask.java        |  13 +-
 .../config/metadata/ShowPipePluginsTask.java       |  20 +-
 .../ExtendRegionTask.java}                         |  19 +-
 .../metadata/{ => region}/MigrateRegionTask.java   |   4 +-
 .../ReconstructRegionTask.java}                    |  19 +-
 .../RemoveRegionTask.java}                         |  19 +-
 .../metadata/relational/ShowTablesDetailsTask.java |  47 +-
 .../config/metadata/relational/ShowTablesTask.java |  35 +-
 .../db/queryengine/plan/parser/ASTVisitor.java     |  29 +-
 .../plan/planner/LogicalPlanVisitor.java           |   7 +-
 .../plan/planner/TableOperatorGenerator.java       |  11 +-
 .../node/metadata/write/CreateTimeSeriesNode.java  |  74 +--
 .../plan/node/pipe/PipeEnrichedDeleteDataNode.java |  66 ++-
 .../plan/node/pipe/PipeEnrichedInsertNode.java     |  44 +-
 .../plan/node/pipe/PipeEnrichedWritePlanNode.java  |  23 +-
 .../relational/analyzer/ExpressionAnalyzer.java    |   6 +-
 .../plan/relational/analyzer/Scope.java            |   8 +-
 .../relational/analyzer/StatementAnalyzer.java     | 106 ++--
 .../relational/metadata/TableMetadataImpl.java     |  19 +-
 .../metadata/fetcher/TableDeviceSchemaFetcher.java |   4 +-
 .../fetcher/TableHeaderSchemaValidator.java        |   3 +-
 .../plan/relational/planner/RelationPlanner.java   |  91 +--
 .../plan/relational/planner/SubqueryPlanner.java   |  51 +-
 .../relational/planner/TableLogicalPlanner.java    |  15 +-
 .../distribute/TableDistributedPlanGenerator.java  |   9 +
 .../node/schema/CreateOrUpdateTableDeviceNode.java |  14 +-
 .../PushAggregationIntoTableScan.java              |   4 +
 .../relational/sql/ast/AbstractTraverseDevice.java |   8 +
 .../plan/relational/sql/ast/AstVisitor.java        |  10 +-
 .../relational/sql/ast/CreateOrUpdateDevice.java   |  31 +-
 .../plan/relational/sql/ast/CreatePipe.java        |   4 +-
 .../plan/relational/sql/ast/Delete.java            |  28 +-
 .../plan/relational/sql/ast/DeleteDevice.java      |   2 -
 .../plan/relational/sql/ast/DropColumn.java        |   3 -
 .../plan/relational/sql/ast/PipeEnriched.java      |   8 +-
 .../ast/{Statement.java => StartRepairData.java}   |  12 +-
 .../plan/relational/sql/ast/Statement.java         |   4 +-
 .../ast/{Statement.java => StopRepairData.java}    |  12 +-
 .../plan/relational/sql/ast/Update.java            |   6 +-
 .../plan/relational/sql/parser/AstBuilder.java     |  26 +-
 .../plan/statement/StatementVisitor.java           |  18 +-
 .../plan/statement/crud/InsertBaseStatement.java   |  22 +-
 .../plan/statement/crud/InsertRowStatement.java    |  18 +-
 .../metadata/pipe/DropPipePluginStatement.java     |  17 +-
 .../metadata/pipe/ShowPipePluginsStatement.java    |  23 +-
 .../ExtendRegionStatement.java}                    |  32 +-
 .../{ => region}/MigrateRegionStatement.java       |   2 +-
 .../ReconstructRegionStatement.java}               |  42 +-
 .../RemoveRegionStatement.java}                    |  32 +-
 .../plan/statement/pipe/PipeEnrichedStatement.java |  10 +-
 .../attribute/DeviceAttributeStore.java            |  10 +-
 .../attribute/IDeviceAttributeStore.java           |   6 +-
 .../schemaregion/impl/SchemaRegionMemoryImpl.java  | 136 ++---
 .../schemaregion/mtree/impl/mem/MemMTreeStore.java |  52 +-
 .../mtree/impl/mem/mnode/basic/BasicMNode.java     |  22 +-
 .../impl/mem/snapshot/MemMTreeSnapshotUtil.java    |  37 +-
 .../schemaengine/table/InformationSchemaUtils.java |   2 +-
 .../db/service/metrics/DataNodeMetricsHelper.java  |   4 +-
 .../metrics/memory/ConsensusMemoryMetrics.java     |  73 +++
 .../metrics/memory/GlobalMemoryMetrics.java        | 132 +++++
 .../metrics/memory/QueryEngineMemoryMetrics.java   | 179 ++++++
 .../metrics/memory/SchemaEngineMemoryMetrics.java  | 124 ++++
 .../metrics/memory/StorageEngineMemoryMetrics.java | 210 +++++++
 .../metrics/memory/StreamEngineMemoryMetrics.java  |  73 +++
 .../metrics/memory/ThresholdMemoryMetrics.java     | 194 -------
 .../db/storageengine/dataregion/DataRegion.java    |   2 +
 .../task/InsertionCrossSpaceCompactionTask.java    |   3 -
 .../utils/CompactionTableSchemaCollector.java      |   2 +-
 .../dataregion/memtable/TsFileProcessor.java       |   4 +
 .../dataregion/modification/ModificationFile.java  |  89 ++-
 .../modification/PartitionLevelModFileManager.java |   5 +-
 .../dataregion/snapshot/SnapshotTaker.java         |   2 +-
 .../dataregion/tsfile/TsFileResource.java          |  10 +-
 .../tsfile/timeindex/ArrayDeviceTimeIndex.java     |  20 +
 .../db/storageengine/load/LoadTsFileManager.java   |   2 +-
 .../load/splitter/TsFileSplitter.java              |   5 +-
 .../broker/SubscriptionPrefetchingQueue.java       |   2 +
 .../db/subscription/event/SubscriptionEvent.java   |  21 +-
 .../batch/SubscriptionPipeTabletEventBatch.java    |  24 +-
 .../db/tools/schema/SRStatementGenerator.java      | 203 +++++--
 .../tools/schema/SchemaRegionSnapshotParser.java   |  32 +-
 .../iotdb/db/utils/datastructure/BinaryTVList.java |   6 +-
 .../db/utils/datastructure/BooleanTVList.java      |   6 +-
 .../iotdb/db/utils/datastructure/DoubleTVList.java |   6 +-
 .../iotdb/db/utils/datastructure/FloatTVList.java  |   6 +-
 .../iotdb/db/utils/datastructure/IntTVList.java    |   6 +-
 .../iotdb/db/utils/datastructure/LongTVList.java   |   6 +-
 .../connector/PipeDataNodeThriftRequestTest.java   |  22 +-
 .../PipeStatementTablePatternParseVisitorTest.java |  57 ++
 ... PipeStatementTreePatternParseVisitorTest.java} |  20 +-
 .../pipe/connector/PipeTabletEventSorterTest.java  |  61 +-
 .../db/pipe/consensus/DeletionResourceTest.java    |  39 +-
 .../pipe/event/PipeTabletInsertionEventTest.java   |  25 +-
 .../PipePlanTablePatternParseVisitorTest.java      | 129 +++++
 ...va => PipePlanTreePatternParseVisitorTest.java} |  32 +-
 .../annotation/PipePluginAnnotationTest.java}      |  23 +-
 .../resource/PipeTsFileResourceManagerTest.java    |   2 +-
 .../plan/relational/analyzer/DistinctTest.java     |  91 +++
 .../planner/assertions/PlanMatchPattern.java       |   6 +
 .../storageengine/buffer/BloomFilterCacheTest.java |   5 +-
 .../storageengine/dataregion/DataRegionTest.java   |  24 +-
 .../compaction/CompactionValidationTest.java       |   7 +-
 .../inner/InnerSpaceCompactionSelectorTest.java    |   2 +-
 .../TableModelFastCompactionPerformerTest.java     |  13 +-
 ...TableModelReadChunkCompactionPerformerTest.java |  13 +-
 ...TableModelReadPointCompactionPerformerTest.java |  13 +-
 .../modification/ModificationFileTest.java         |  90 ++-
 .../file/UnsealedTsFileRecoverPerformerTest.java   |   4 +-
 .../iotdb/db/tools/TsFileAndModSettleToolTest.java |   2 +-
 .../iotdb/db/tools/TsFileSelfCheckToolTest.java    |   6 +-
 .../iotdb/db/tools/TsFileSketchToolTest.java       |  10 +-
 .../db/utils/SchemaRegionSnapshotParserTest.java   | 200 +++++--
 iotdb-core/node-commons/pom.xml                    |   4 +
 .../exception}/table/ColumnNotExistsException.java |   2 +-
 .../table/TableAlreadyExistsException.java         |   2 +-
 .../exception}/table/TableNotExistsException.java  |   2 +-
 .../commons/pipe/agent/plugin/PipePluginAgent.java |  17 +-
 .../builtin/connector/PlaceholderConnector.java    |   4 +
 .../connector/donothing/DoNothingConnector.java    |   4 +
 .../iotdb/airgap/IoTDBAirGapConnector.java         |   4 +
 .../consensus/PipeConsensusAsyncConnector.java     |   4 +
 .../iotdb/thrift/IoTDBLegacyPipeConnector.java     |   2 +
 .../iotdb/thrift/IoTDBThriftAsyncConnector.java    |   5 +
 .../iotdb/thrift/IoTDBThriftConnector.java         |   4 +
 .../iotdb/thrift/IoTDBThriftSslConnector.java      |   4 +
 .../iotdb/thrift/IoTDBThriftSyncConnector.java     |   5 +
 .../builtin/connector/opcua/OpcUaConnector.java    |   4 +
 .../connector/websocket/WebSocketConnector.java    |   2 +
 .../connector/writeback/WriteBackConnector.java    |   4 +
 .../extractor/donothing/DoNothingExtractor.java    |   4 +
 .../builtin/extractor/iotdb/IoTDBExtractor.java    |   4 +
 .../processor/donothing/DoNothingProcessor.java    |   4 +
 .../throwing/ThrowingExceptionProcessor.java       |   4 +
 .../agent/plugin/meta/PipePluginMetaKeeper.java    |  47 +-
 .../commons/pipe/agent/task/PipeTaskAgent.java     |   2 +-
 .../commons/pipe/agent/task/meta/PipeMeta.java     |  40 --
 .../pipe/agent/task/meta/PipeMetaKeeper.java       |   6 +-
 .../pipe/agent/task/meta/PipeStaticMeta.java       |  14 +
 .../payload/thrift/request/PipeRequestType.java    |   4 +-
 .../thrift/request/PipeTransferFileSealReqV2.java  |  13 +-
 .../connector/protocol/IoTDBAirGapConnector.java   |   4 +
 .../pipe/connector/protocol/IoTDBConnector.java    |  10 +
 .../connector/protocol/IoTDBSslSyncConnector.java  |   4 +
 .../options/PipeInclusionOptions.java              |  43 +-
 .../pipe/datastructure/pattern/TablePattern.java   |  36 +-
 .../pipe/datastructure/pattern/TreePattern.java    |  33 +-
 .../{ => resource}/PersistentResource.java         |   2 +-
 .../commons/pipe/datastructure/result/Result.java} |  37 +-
 .../pipe/datastructure/visibility/Visibility.java  |   9 +-
 .../visibility/VisibilityTestUtils.java            | 135 +++++
 .../datastructure/visibility/VisibilityUtils.java  | 133 +++++
 .../commons/pipe/extractor/IoTDBExtractor.java     |  38 +-
 .../extractor/IoTDBNonDataRegionExtractor.java     |  24 +-
 .../commons/pipe/receiver/IoTDBFileReceiver.java   |  32 +-
 .../apache/iotdb/commons/schema/node/IMNode.java   |  18 +-
 .../commons/schema/node/visitor/MNodeVisitor.java  |   6 +-
 .../iotdb/commons/service/metric/enums/Metric.java |   3 +-
 .../iotdb/commons/udf/access/RecordIterator.java   |   9 +-
 .../annotation/PipePluginAnnotationTest.java}      |  23 +-
 .../db/relational/grammar/sql/RelationalSql.g4     |  11 +-
 .../src/main/thrift/confignode.thrift              |  31 +
 pom.xml                                            |   6 +-
 465 files changed, 13805 insertions(+), 4746 deletions(-)