This is an automated email from the ASF dual-hosted git repository.
JackieTien97 pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/iotdb.git
The following commit(s) were added to refs/heads/master by this push:
new db22016e160 Fix order in subquery when meets diff function (#17629)
db22016e160 is described below
commit db22016e160108b66e3d1351386c06e82c13e578
Author: Weihao Li <[email protected]>
AuthorDate: Mon May 11 14:38:11 2026 +0800
Fix order in subquery when meets diff function (#17629)
---
.../scalar/IoTDBDiffFunctionTableIT.java | 80 ++++++++++++++++++++++
.../relational/analyzer/StatementAnalyzer.java | 52 +++++++++++++-
.../distribute/TableDistributedPlanGenerator.java | 34 +++++++--
.../optimizations/PushPredicateIntoTableScan.java | 2 +-
.../plan/relational/analyzer/AnalyzerTest.java | 50 ++++++++++++++
5 files changed, 210 insertions(+), 8 deletions(-)
diff --git
a/integration-test/src/test/java/org/apache/iotdb/relational/it/query/old/builtinfunction/scalar/IoTDBDiffFunctionTableIT.java
b/integration-test/src/test/java/org/apache/iotdb/relational/it/query/old/builtinfunction/scalar/IoTDBDiffFunctionTableIT.java
index 6bf1ef8db3e..2eaa96e5b85 100644
---
a/integration-test/src/test/java/org/apache/iotdb/relational/it/query/old/builtinfunction/scalar/IoTDBDiffFunctionTableIT.java
+++
b/integration-test/src/test/java/org/apache/iotdb/relational/it/query/old/builtinfunction/scalar/IoTDBDiffFunctionTableIT.java
@@ -146,4 +146,84 @@ public class IoTDBDiffFunctionTableIT {
retArray,
DATABASE_NAME);
}
+
+ @Test
+ public void testDiffWithOrderBySubquery() {
+ String[] expectedHeader = new String[] {"time", "device_id", "s1",
"_col3"};
+ String[] retArray =
+ new String[] {
+ "1970-02-27T20:53:20.001Z,d1,8,3.0,",
+ "1970-02-27T20:53:20.000Z,d1,null,null,",
+ "1970-01-01T00:00:00.006Z,d1,null,null,",
+ "1970-01-01T00:00:00.005Z,d1,5,1.0,",
+ "1970-01-01T00:00:00.004Z,d1,4,2.0,",
+ "1970-01-01T00:00:00.003Z,d1,null,null,",
+ "1970-01-01T00:00:00.002Z,d1,2,1.0,",
+ "1970-01-01T00:00:00.001Z,d1,1,null,"
+ };
+ tableResultSetEqualTest(
+ "SELECT time, device_id, s1, diff(s1) FROM ("
+ + "select * "
+ + "from table1 where device_id='d1' ORDER by time"
+ + ") "
+ + "ORDER by time DESC",
+ expectedHeader,
+ retArray,
+ DATABASE_NAME);
+ }
+
+ @Test
+ public void testDiffInOuterWhereHavingOrderBy() {
+ String[] expectedHeader = new String[] {"time", "device_id", "s1"};
+ String[] expectedRowsFilteredByDiff =
+ new String[] {
+ "1970-02-27T20:53:20.001Z,d1,8,",
+ "1970-01-01T00:00:00.005Z,d1,5,",
+ "1970-01-01T00:00:00.004Z,d1,4,",
+ "1970-01-01T00:00:00.002Z,d1,2,"
+ };
+ tableResultSetEqualTest(
+ "SELECT time, device_id, s1 FROM ("
+ + "select * "
+ + "from table1 where device_id='d1' ORDER by time"
+ + ") "
+ + "WHERE diff(s1) IS NOT NULL "
+ + "ORDER by time DESC",
+ expectedHeader,
+ expectedRowsFilteredByDiff,
+ DATABASE_NAME);
+
+ tableResultSetEqualTest(
+ "SELECT time, device_id, s1 FROM ("
+ + "select * "
+ + "from table1 where device_id='d1' ORDER by time"
+ + ") "
+ + "GROUP BY time, device_id, s1 "
+ + "HAVING diff(s1) IS NOT NULL "
+ + "ORDER by time DESC",
+ expectedHeader,
+ expectedRowsFilteredByDiff,
+ DATABASE_NAME);
+
+ String[] expectedRowsOrderedByDiff =
+ new String[] {
+ "1970-02-27T20:53:20.001Z,d1,8,",
+ "1970-01-01T00:00:00.004Z,d1,4,",
+ "1970-01-01T00:00:00.005Z,d1,5,",
+ "1970-01-01T00:00:00.002Z,d1,2,",
+ "1970-02-27T20:53:20.000Z,d1,null,",
+ "1970-01-01T00:00:00.006Z,d1,null,",
+ "1970-01-01T00:00:00.003Z,d1,null,",
+ "1970-01-01T00:00:00.001Z,d1,1,"
+ };
+ tableResultSetEqualTest(
+ "SELECT time, device_id, s1 FROM ("
+ + "select * "
+ + "from table1 where device_id='d1' ORDER by time"
+ + ") "
+ + "ORDER BY coalesce(diff(s1), -1000.0) DESC, time DESC",
+ expectedHeader,
+ expectedRowsOrderedByDiff,
+ DATABASE_NAME);
+ }
}
diff --git
a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/StatementAnalyzer.java
b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/StatementAnalyzer.java
index 6d3326dd402..12656f0ce5e 100644
---
a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/StatementAnalyzer.java
+++
b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/StatementAnalyzer.java
@@ -292,6 +292,7 @@ import static
org.apache.iotdb.db.queryengine.plan.relational.analyzer.Expressio
import static
org.apache.iotdb.db.queryengine.plan.relational.analyzer.Scope.BasisType.TABLE;
import static
org.apache.iotdb.db.queryengine.plan.relational.metadata.MetadataUtil.createQualifiedObjectName;
import static
org.apache.iotdb.db.queryengine.plan.relational.planner.IrExpressionInterpreter.evaluateConstantExpression;
+import static
org.apache.iotdb.db.queryengine.plan.relational.planner.optimizations.PushPredicateIntoTableScan.containsDiffFunction;
import static
org.apache.iotdb.db.queryengine.plan.relational.sql.util.AstUtil.preOrder;
import static
org.apache.iotdb.db.queryengine.plan.relational.utils.NodeUtils.getSortItemsFromOrderBy;
import static org.apache.tsfile.read.common.type.BooleanType.BOOLEAN;
@@ -303,6 +304,7 @@ public class StatementAnalyzer {
private final Analysis analysis;
private boolean hasFillInParentScope = false;
+ private boolean hasDiffFunctionInParentScope = false;
private final MPPQueryContext queryContext;
private final AccessControl accessControl;
@@ -870,9 +872,13 @@ public class StatementAnalyzer {
@Override
public Scope visitQuery(Query node, Optional<Scope> context) {
+ boolean originalHasFillInParentScope = hasFillInParentScope;
+ boolean originalHasDiffFunctionInParentScope =
hasDiffFunctionInParentScope;
analysis.setQuery(true);
Scope withScope = analyzeWith(node, context);
hasFillInParentScope = node.getFill().isPresent() ||
hasFillInParentScope;
+ hasDiffFunctionInParentScope =
+ containsDiffFunctionInQuery(node) || hasDiffFunctionInParentScope;
Scope queryBodyScope = process(node.getQueryBody(), withScope);
if (node.getFill().isPresent()) {
@@ -887,7 +893,8 @@ public class StatementAnalyzer {
if ((queryBodyScope.getOuterQueryParent().isPresent() || !isTopLevel)
&& !node.getLimit().isPresent()
&& !node.getOffset().isPresent()
- && !hasFillInParentScope) {
+ && !hasFillInParentScope
+ && !hasDiffFunctionInParentScope) {
// not the root scope and ORDER BY is ineffective
analysis.markRedundantOrderBy(node.getOrderBy().get());
warningCollector.add(
@@ -922,6 +929,8 @@ public class StatementAnalyzer {
.build();
analysis.setScope(node, queryScope);
+ hasFillInParentScope = originalHasFillInParentScope;
+ hasDiffFunctionInParentScope = originalHasDiffFunctionInParentScope;
return queryScope;
}
@@ -1144,6 +1153,7 @@ public class StatementAnalyzer {
statementAnalyzerFactory.createStatementAnalyzer(
analysis, queryContext, sessionContext, warningCollector,
CorrelationSupport.ALLOWED);
analyzer.hasFillInParentScope = hasFillInParentScope;
+ analyzer.hasDiffFunctionInParentScope = hasDiffFunctionInParentScope;
Scope queryScope =
analyzer.analyze(
node.getQuery(),
@@ -1153,9 +1163,13 @@ public class StatementAnalyzer {
@Override
public Scope visitQuerySpecification(QuerySpecification node,
Optional<Scope> scope) {
+ boolean originalHasFillInParentScope = hasFillInParentScope;
+ boolean originalHasDiffFunctionInParentScope =
hasDiffFunctionInParentScope;
// TODO: extract candidate names from SELECT, WHERE, HAVING, GROUP BY
and ORDER BY expressions
// to pass down to analyzeFrom
hasFillInParentScope = node.getFill().isPresent() ||
hasFillInParentScope;
+ hasDiffFunctionInParentScope =
+ containsDiffFunctionInQuerySpecification(node) ||
hasDiffFunctionInParentScope;
Scope sourceScope = analyzeFrom(node, scope);
analyzeWindowDefinitions(node, sourceScope);
@@ -1188,7 +1202,8 @@ public class StatementAnalyzer {
if ((sourceScope.getOuterQueryParent().isPresent() || !isTopLevel)
&& !node.getLimit().isPresent()
&& !node.getOffset().isPresent()
- && !hasFillInParentScope) {
+ && !hasFillInParentScope
+ && !hasDiffFunctionInParentScope) {
// not the root scope and ORDER BY is ineffective
analysis.markRedundantOrderBy(orderBy);
warningCollector.add(
@@ -1249,6 +1264,8 @@ public class StatementAnalyzer {
orderByScope.orElseThrow(() -> new NoSuchElementException("No
value present")));
}
+ hasFillInParentScope = originalHasFillInParentScope;
+ hasDiffFunctionInParentScope = originalHasDiffFunctionInParentScope;
return outputScope;
}
@@ -1301,6 +1318,37 @@ public class StatementAnalyzer {
return windowFunctions;
}
+ // cover case: (query1) UNION (query2) order by ...
+ private boolean containsDiffFunctionInQuery(Query node) {
+ return getSortItemsFromOrderBy(node.getOrderBy()).stream()
+ .map(SortItem::getSortKey)
+ .anyMatch(expression -> containsDiffFunction(expression));
+ }
+
+ private boolean
containsDiffFunctionInQuerySpecification(QuerySpecification node) {
+ for (SelectItem selectItem : node.getSelect().getSelectItems()) {
+ if (selectItem instanceof AllColumns) {
+ Optional<Expression> target = ((AllColumns) selectItem).getTarget();
+ if (target.isPresent() && containsDiffFunction(target.get())) {
+ return true;
+ }
+ } else if (selectItem instanceof SingleColumn
+ && containsDiffFunction(((SingleColumn)
selectItem).getExpression())) {
+ return true;
+ }
+ }
+
+ if (node.getWhere().isPresent() &&
containsDiffFunction(node.getWhere().get())) {
+ return true;
+ }
+ if (node.getHaving().isPresent() &&
containsDiffFunction(node.getHaving().get())) {
+ return true;
+ }
+ return getSortItemsFromOrderBy(node.getOrderBy()).stream()
+ .map(SortItem::getSortKey)
+ .anyMatch(expression -> containsDiffFunction(expression));
+ }
+
private void resolveFunctionCallAndMeasureWindows(QuerySpecification
querySpecification) {
ImmutableList.Builder<Expression> expressions = ImmutableList.builder();
diff --git
a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/distribute/TableDistributedPlanGenerator.java
b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/distribute/TableDistributedPlanGenerator.java
index eb967534e17..fbd1d4cc24d 100644
---
a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/distribute/TableDistributedPlanGenerator.java
+++
b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/distribute/TableDistributedPlanGenerator.java
@@ -107,7 +107,6 @@ import
org.apache.iotdb.db.queryengine.plan.relational.planner.node.schema.Table
import
org.apache.iotdb.db.queryengine.plan.relational.planner.node.schema.TableDeviceQueryCountNode;
import
org.apache.iotdb.db.queryengine.plan.relational.planner.node.schema.TableDeviceQueryScanNode;
import
org.apache.iotdb.db.queryengine.plan.relational.planner.optimizations.DataNodeLocationSupplierFactory;
-import
org.apache.iotdb.db.queryengine.plan.relational.planner.optimizations.PushPredicateIntoTableScan;
import org.apache.iotdb.db.queryengine.plan.relational.sql.ast.Insert;
import org.apache.iotdb.db.queryengine.plan.statement.component.Ordering;
import org.apache.iotdb.db.schemaengine.table.DataNodeTableCache;
@@ -321,7 +320,22 @@ public class TableDistributedPlanGenerator
@Override
public List<PlanNode> visitProject(ProjectNode node, PlanContext context) {
+ boolean containsDiff =
+ node.getAssignments().getMap().values().stream()
+ .anyMatch(expression -> containsDiffFunction(expression));
+ OrderingScheme originalExpectedOrdering = context.expectedOrderingScheme;
+ boolean originalHasSortProperty = context.hasSortProperty;
+ if (containsDiff) {
+ context.clearExpectedOrderingScheme();
+ }
List<PlanNode> childrenNodes = node.getChild().accept(this, context);
+ if (containsDiff) {
+ if (originalHasSortProperty) {
+ context.setExpectedOrderingScheme(originalExpectedOrdering);
+ } else {
+ context.clearExpectedOrderingScheme();
+ }
+ }
OrderingScheme childOrdering =
nodeOrderingMap.get(childrenNodes.get(0).getPlanNodeId());
boolean containAllSortItem = false;
if (childOrdering != null) {
@@ -378,9 +392,6 @@ public class TableDistributedPlanGenerator
return Collections.singletonList(node);
}
- boolean containsDiff =
- node.getAssignments().getMap().values().stream()
- .anyMatch(PushPredicateIntoTableScan::containsDiffFunction);
if (containsDiff) {
if (containAllSortItem) {
nodeOrderingMap.put(node.getPlanNodeId(), childOrdering);
@@ -583,7 +594,20 @@ public class TableDistributedPlanGenerator
@Override
public List<PlanNode> visitFilter(FilterNode node, PlanContext context) {
+ boolean containsDiff = containsDiffFunction(node.getPredicate());
+ OrderingScheme originalExpectedOrdering = context.expectedOrderingScheme;
+ boolean originalHasSortProperty = context.hasSortProperty;
+ if (containsDiff) {
+ context.clearExpectedOrderingScheme();
+ }
List<PlanNode> childrenNodes = node.getChild().accept(this, context);
+ if (containsDiff) {
+ if (originalHasSortProperty) {
+ context.setExpectedOrderingScheme(originalExpectedOrdering);
+ } else {
+ context.clearExpectedOrderingScheme();
+ }
+ }
OrderingScheme childOrdering =
nodeOrderingMap.get(childrenNodes.get(0).getPlanNodeId());
if (childOrdering != null) {
nodeOrderingMap.put(node.getPlanNodeId(), childOrdering);
@@ -594,7 +618,7 @@ public class TableDistributedPlanGenerator
return Collections.singletonList(node);
}
- if (containsDiffFunction(node.getPredicate())) {
+ if (containsDiff) {
node.setChild(mergeChildrenViaCollectOrMergeSort(childOrdering,
childrenNodes));
return Collections.singletonList(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 5827993a062..61245da7a34 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
@@ -1453,7 +1453,7 @@ public class PushPredicateIntoTableScan implements
PlanOptimizer {
if (!expression.getChildren().isEmpty()) {
for (Node node : expression.getChildren()) {
- if (containsDiffFunction((Expression) node)) {
+ if (node instanceof Expression && containsDiffFunction((Expression)
node)) {
return true;
}
}
diff --git
a/iotdb-core/datanode/src/test/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/AnalyzerTest.java
b/iotdb-core/datanode/src/test/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/AnalyzerTest.java
index e8626735374..45599c8a981 100644
---
a/iotdb-core/datanode/src/test/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/AnalyzerTest.java
+++
b/iotdb-core/datanode/src/test/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/AnalyzerTest.java
@@ -65,6 +65,7 @@ import
org.apache.iotdb.db.queryengine.plan.relational.metadata.Metadata;
import
org.apache.iotdb.db.queryengine.plan.relational.metadata.OperatorNotFoundException;
import
org.apache.iotdb.db.queryengine.plan.relational.metadata.QualifiedObjectName;
import
org.apache.iotdb.db.queryengine.plan.relational.metadata.fetcher.TableHeaderSchemaValidator;
+import org.apache.iotdb.db.queryengine.plan.relational.planner.PlanTester;
import org.apache.iotdb.db.queryengine.plan.relational.planner.SymbolAllocator;
import
org.apache.iotdb.db.queryengine.plan.relational.planner.TableLogicalPlanner;
import
org.apache.iotdb.db.queryengine.plan.relational.planner.distribute.TableDistributedPlanner;
@@ -108,6 +109,15 @@ import static
org.apache.iotdb.db.queryengine.plan.relational.analyzer.TestUtils
import static
org.apache.iotdb.db.queryengine.plan.relational.analyzer.TestUtils.TEST_MATADATA;
import static
org.apache.iotdb.db.queryengine.plan.relational.analyzer.TestUtils.assertTableScanWithoutEntryOrder;
import static
org.apache.iotdb.db.queryengine.plan.relational.analyzer.TestUtils.getChildrenNode;
+import static
org.apache.iotdb.db.queryengine.plan.relational.planner.assertions.PlanAssert.assertPlan;
+import static
org.apache.iotdb.db.queryengine.plan.relational.planner.assertions.PlanMatchPattern.aggregation;
+import static
org.apache.iotdb.db.queryengine.plan.relational.planner.assertions.PlanMatchPattern.exchange;
+import static
org.apache.iotdb.db.queryengine.plan.relational.planner.assertions.PlanMatchPattern.filter;
+import static
org.apache.iotdb.db.queryengine.plan.relational.planner.assertions.PlanMatchPattern.mergeSort;
+import static
org.apache.iotdb.db.queryengine.plan.relational.planner.assertions.PlanMatchPattern.output;
+import static
org.apache.iotdb.db.queryengine.plan.relational.planner.assertions.PlanMatchPattern.project;
+import static
org.apache.iotdb.db.queryengine.plan.relational.planner.assertions.PlanMatchPattern.sort;
+import static
org.apache.iotdb.db.queryengine.plan.relational.planner.assertions.PlanMatchPattern.tableScan;
import static
org.apache.iotdb.db.queryengine.plan.statement.component.Ordering.ASC;
import static org.apache.tsfile.read.common.type.BooleanType.BOOLEAN;
import static org.apache.tsfile.read.common.type.DoubleType.DOUBLE;
@@ -854,6 +864,46 @@ public class AnalyzerTest {
filterNode.getPredicate().toString());
}
+ @Test
+ public void diffWithSubqueryOrderByTest() {
+ PlanTester planTester = new PlanTester();
+ String sqlWithOuterWhere =
+ "SELECT time, tag1, s1 FROM ("
+ + "select * from table1 ORDER by time"
+ + ") WHERE diff(s1) IS NOT NULL ORDER by time DESC";
+ planTester.createPlan(sqlWithOuterWhere);
+ assertPlan(
+ planTester.getFragmentPlan(0),
+ output(sort(filter(mergeSort(exchange(), exchange(), exchange())))));
+ assertPlan(planTester.getFragmentPlan(1),
sort(tableScan("testdb.table1")));
+ assertPlan(planTester.getFragmentPlan(2),
sort(tableScan("testdb.table1")));
+ assertPlan(planTester.getFragmentPlan(3),
sort(tableScan("testdb.table1")));
+
+ String sqlWithOuterHaving =
+ "SELECT time, tag1, s1 FROM ("
+ + "select * from table1 ORDER by time"
+ + ") GROUP BY time, tag1, s1 HAVING diff(s1) IS NOT NULL ORDER by
time DESC";
+ planTester.createPlan(sqlWithOuterHaving);
+ assertPlan(
+ planTester.getFragmentPlan(0),
+ output(sort(filter(aggregation(mergeSort(exchange(), exchange(),
exchange()))))));
+ assertPlan(planTester.getFragmentPlan(1),
sort(tableScan("testdb.table1")));
+ assertPlan(planTester.getFragmentPlan(2),
sort(tableScan("testdb.table1")));
+ assertPlan(planTester.getFragmentPlan(3),
sort(tableScan("testdb.table1")));
+
+ String sqlWithOuterOrderBy =
+ "SELECT time, tag1, s1 FROM ("
+ + "select * from table1 ORDER by time"
+ + ") ORDER by coalesce(diff(s1), -1000.0) DESC, time DESC";
+ planTester.createPlan(sqlWithOuterOrderBy);
+ assertPlan(
+ planTester.getFragmentPlan(0),
+ output(project(sort(project(mergeSort(exchange(), exchange(),
exchange()))))));
+ assertPlan(planTester.getFragmentPlan(1),
sort(tableScan("testdb.table1")));
+ assertPlan(planTester.getFragmentPlan(2),
sort(tableScan("testdb.table1")));
+ assertPlan(planTester.getFragmentPlan(3),
sort(tableScan("testdb.table1")));
+ }
+
@Test
public void predicatePushDownTest() {
sql =
