This is an automated email from the ASF dual-hosted git repository. lancelly pushed a commit to branch support_correlated_scalar_subquery in repository https://gitbox.apache.org/repos/asf/iotdb.git
commit 0bce073b1fd6ffd7bcc593d796ada2d135e9e58e Author: lancelly <[email protected]> AuthorDate: Tue Feb 18 15:49:07 2025 +0800 add IT and fix assignUniqueId --- .../IoTDBCorrelatedExistsSubqueryIT.java | 2 +- .../IoTDBCorrelatedScalarSubqueryIT.java | 283 ++++++++++++++++++++- .../optimizations/PushPredicateIntoTableScan.java | 3 +- 3 files changed, 277 insertions(+), 11 deletions(-) diff --git a/integration-test/src/test/java/org/apache/iotdb/relational/it/query/recent/subquery/correlated/IoTDBCorrelatedExistsSubqueryIT.java b/integration-test/src/test/java/org/apache/iotdb/relational/it/query/recent/subquery/correlated/IoTDBCorrelatedExistsSubqueryIT.java index 1359777653d..587e6002835 100644 --- a/integration-test/src/test/java/org/apache/iotdb/relational/it/query/recent/subquery/correlated/IoTDBCorrelatedExistsSubqueryIT.java +++ b/integration-test/src/test/java/org/apache/iotdb/relational/it/query/recent/subquery/correlated/IoTDBCorrelatedExistsSubqueryIT.java @@ -356,7 +356,7 @@ public class IoTDBCorrelatedExistsSubqueryIT { } @Test - public void testUnCorrelatedExistsSubqueryInSelectClause() { + public void testCorrelatedExistsSubqueryInSelectClause() { String sql; String[] expectedHeader; String[] retArray; diff --git a/integration-test/src/test/java/org/apache/iotdb/relational/it/query/recent/subquery/correlated/IoTDBCorrelatedScalarSubqueryIT.java b/integration-test/src/test/java/org/apache/iotdb/relational/it/query/recent/subquery/correlated/IoTDBCorrelatedScalarSubqueryIT.java index 7457a0d9f50..b8d2dfe24b1 100644 --- a/integration-test/src/test/java/org/apache/iotdb/relational/it/query/recent/subquery/correlated/IoTDBCorrelatedScalarSubqueryIT.java +++ b/integration-test/src/test/java/org/apache/iotdb/relational/it/query/recent/subquery/correlated/IoTDBCorrelatedScalarSubqueryIT.java @@ -94,10 +94,281 @@ public class IoTDBCorrelatedScalarSubqueryIT { retArray, DATABASE_NAME); } + + // Test case: limit 1 in scalar subquery + sql = + "SELECT cast(%s AS INT32) as %s FROM table1 t1 WHERE device_id = 'd01' and %s >= (SELECT %s from table3 t3 where t1.%s = t3.%s and %s > 30 limit 1)"; + retArray = new String[] {"40,"}; + for (String measurement : NUMERIC_MEASUREMENTS) { + expectedHeader = new String[] {measurement}; + tableResultSetEqualTest( + String.format( + sql, + measurement, + measurement, + measurement, + measurement, + measurement, + measurement, + measurement), + expectedHeader, + retArray, + DATABASE_NAME); + } + } + + @Test + public void testNestedCorrelatedScalarSubquery() { + String sql; + String[] expectedHeader; + String[] retArray; + + // Test case: Nested exists + sql = + "select distinct s1 from table1 t1 where s1 >= (select max(s1) from table3 t3 where t1.s1 = t3.s1 and s1 = (select max(s1) from table1 t1_2 where t1_2.s1 = t3.s1))"; + retArray = new String[] {"30,", "40,"}; + expectedHeader = new String[] {"s1"}; + tableResultSetEqualTest(sql, expectedHeader, retArray, DATABASE_NAME); } @Test - public void testNonComparisonFilterInCorrelatedScalarSubquery() { + public void testMultipleScalarSubquery() { + String sql; + String[] expectedHeader; + String[] retArray; + + // Test case: multiple scalar subquery + sql = + "select distinct s1 from table1 t1 where s1 = (select max(s1) from table3 t3 where t1.s1 = t3.s1) and s1 = (select min(s1) from table3 t3 where t1.s1 = t3.s1)"; + retArray = new String[] {"30,", "40,"}; + expectedHeader = new String[] {"s1"}; + tableResultSetEqualTest(sql, expectedHeader, retArray, DATABASE_NAME); + } + + @Test + public void testCorrelatedScalarSubqueryInWhereClauseWithOtherCorrelatedSubquery() { + String sql; + String[] expectedHeader; + String[] retArray; + // Test case: with Exists + sql = + "SELECT distinct cast(%s AS INT32) as %s FROM table1 t1 WHERE %s = (SELECT max(%s) from table3 t3 WHERE device_id = 'd01' and t1.%s = t3.%s) and exists(select s1 from table3 t3 where t1.s1 = t3.s1)"; + retArray = new String[] {"30,", "40,"}; + for (String measurement : NUMERIC_MEASUREMENTS) { + expectedHeader = new String[] {measurement}; + tableResultSetEqualTest( + String.format( + sql, + measurement, + measurement, + measurement, + measurement, + measurement, + measurement, + measurement, + measurement), + expectedHeader, + retArray, + DATABASE_NAME); + } + } + + @Test + public void testCorrelatedScalarSubqueryInWhereClauseWithOtherUncorrelatedSubquery() { + String sql; + String[] expectedHeader; + String[] retArray; + + // Test case: with InPredicate + sql = + "SELECT distinct cast(%s AS INT32) as %s FROM table1 t1 WHERE %s = (SELECT max(%s) from table3 t3 WHERE device_id = 'd01' and t1.%s = t3.%s) and %s in (select %s from table3)"; + retArray = new String[] {"30,", "40,"}; + for (String measurement : NUMERIC_MEASUREMENTS) { + expectedHeader = new String[] {measurement}; + tableResultSetEqualTest( + String.format( + sql, + measurement, + measurement, + measurement, + measurement, + measurement, + measurement, + measurement, + measurement), + expectedHeader, + retArray, + DATABASE_NAME); + } + + // Test case: with InPredicate in scalar subquery + sql = + "SELECT distinct cast(%s AS INT32) as %s FROM table1 t1 WHERE device_id = 'd01' and %s = (SELECT max(%s) from table3 t3 WHERE device_id = 'd01' and t1.%s = t3.%s and %s not in (select %s from table2 where %s is not null))"; + retArray = new String[] {"30,", "40,"}; + for (String measurement : NUMERIC_MEASUREMENTS) { + expectedHeader = new String[] {measurement}; + tableResultSetEqualTest( + String.format( + sql, + measurement, + measurement, + measurement, + measurement, + measurement, + measurement, + measurement, + measurement, + measurement), + expectedHeader, + retArray, + DATABASE_NAME); + } + + // Test case: with Scalar Subquery + sql = + "SELECT cast(%s AS INT32) as %s FROM table1 t1 WHERE device_id = 'd01' and %s = (SELECT max(%s) from table3 t3 WHERE device_id = 'd01' and t1.%s = t3.%s) and s1 > (select min(%s) from table3)"; + retArray = new String[] {"40,"}; + for (String measurement : NUMERIC_MEASUREMENTS) { + expectedHeader = new String[] {measurement}; + tableResultSetEqualTest( + String.format( + sql, + measurement, + measurement, + measurement, + measurement, + measurement, + measurement, + measurement), + expectedHeader, + retArray, + DATABASE_NAME); + } + + // Test case: with nested Scalar Subquery + sql = + "SELECT distinct cast(%s AS INT32) as %s FROM table1 t1 WHERE device_id = 'd01' and %s = (SELECT (%s) from table3 t3 WHERE device_id = 'd01' and t1.%s = t3.%s and s1 = (select min(%s) from table3))"; + retArray = new String[] {"30,"}; + for (String measurement : NUMERIC_MEASUREMENTS) { + expectedHeader = new String[] {measurement}; + tableResultSetEqualTest( + String.format( + sql, + measurement, + measurement, + measurement, + measurement, + measurement, + measurement, + measurement), + expectedHeader, + retArray, + DATABASE_NAME); + } + + // Test case: with Quantified Comparison + sql = + "SELECT distinct cast(%s AS INT32) as %s FROM table1 t1 WHERE device_id = 'd01' and %s = (SELECT max(%s) from table3 t3 WHERE device_id = 'd01' and t1.%s = t3.%s) and s1 != any(select %s from table2)"; + retArray = new String[] {"30,", "40,"}; + for (String measurement : NUMERIC_MEASUREMENTS) { + expectedHeader = new String[] {measurement}; + tableResultSetEqualTest( + String.format( + sql, + measurement, + measurement, + measurement, + measurement, + measurement, + measurement, + measurement), + expectedHeader, + retArray, + DATABASE_NAME); + } + + // Test case: with Quantified Comparison in scalar subquery + sql = + "SELECT distinct cast(%s AS INT32) as %s FROM table1 t1 WHERE device_id = 'd01' and %s = (SELECT max(%s) from table3 t3 WHERE device_id = 'd01' and t1.%s = t3.%s and %s = any (select %s from table3))"; + retArray = new String[] {"30,", "40,"}; + for (String measurement : NUMERIC_MEASUREMENTS) { + expectedHeader = new String[] {measurement}; + tableResultSetEqualTest( + String.format( + sql, + measurement, + measurement, + measurement, + measurement, + measurement, + measurement, + measurement, + measurement), + expectedHeader, + retArray, + DATABASE_NAME); + } + } + + @Test + public void testCorrelatedScalarSubqueryWithMultipleCorrelation() { + String sql; + String[] expectedHeader; + String[] retArray; + + // Test case: Multiple correlation in exists + sql = + "SELECT cast(%s AS INT32) as %s FROM table1 t1 WHERE device_id = 'd01' and %s = (SELECT max(%s) from table3 t3 WHERE t1.%s = t3.%s and t1.s1 = t3.s1 and t1.s2 = t3.s2)"; + retArray = new String[] {"30,", "40,"}; + for (String measurement : NUMERIC_MEASUREMENTS) { + expectedHeader = new String[] {measurement}; + tableResultSetEqualTest( + String.format( + sql, measurement, measurement, measurement, measurement, measurement, measurement), + expectedHeader, + retArray, + DATABASE_NAME); + } + } + + @Test + public void testCorrelatedScalarSubqueryInHavingClause() { + String sql; + String[] expectedHeader; + String[] retArray; + + // Test case: scalar subquery in having + sql = + "SELECT device_id, count(*) from table1 t1 group by device_id having count(*) + 35 = (SELECT max(s1) from table3 t3 where t3.device_id = t1.device_id)"; + expectedHeader = new String[] {"device_id", "_col1"}; + retArray = new String[] {"d01,5,"}; + for (String measurement : NUMERIC_MEASUREMENTS) { + tableResultSetEqualTest( + String.format(sql, measurement), expectedHeader, retArray, DATABASE_NAME); + } + } + + @Test + public void testCorrelatedScalarSubqueryInSelectClause() { + String sql; + String[] expectedHeader; + String[] retArray; + + // Test case: exists in Select clause + sql = + "select s1 = (select max(s1) from table1 t1 where t1.s1 = t3.s1) from table3 t3 where exists(select s1 from table2 t2 where t2.s1 = t3.s1 - 25)"; + retArray = new String[] {"true,", "true,"}; + expectedHeader = new String[] {"_col0"}; + tableResultSetEqualTest(sql, expectedHeader, retArray, DATABASE_NAME); + + sql = "select (select max(s1) from table1 t1 where t1.s1 = t3.s1) from table3 t3"; + retArray = new String[] {"30,", "30,", "40,", "null,"}; + expectedHeader = new String[] {"_col0"}; + tableResultSetEqualTest(sql, expectedHeader, retArray, DATABASE_NAME); + } + + @Test + public void testNonEqualityComparisonFilterInCorrelatedScalarSubquery() { // Legality check: Correlated subquery with Non-equality comparison is not support for now. tableAssertTestFail( "select s1 from table1 t1 where s1 > (select max(s1) from table3 t3 where t1.s1 > t3.s1)", @@ -126,22 +397,18 @@ public class IoTDBCorrelatedScalarSubqueryIT { // Legality check: Correlated subqueries can only access columns from the immediately outer // scope and cannot access columns from the further outer queries. tableAssertTestFail( - "select s1 from table1 t1 where s1 > (select s1 from table3 t3 where t1.s1 = t3.s1 and s1 > (select s1 from table2 t2 where t2.s1 = t1.s1 limit 1) limit 1)", + "select s1 from table1 t1 where s1 > (select s1 from table3 t3 where t1.s1 = t3.s1 and s1 = (select s1 from table2 t2 where t2.s1 = t1.s1 limit 1) limit 1)", "701: Given correlated subquery is not supported", DATABASE_NAME); // Legality check: Correlated subqueries with limit clause and limit count greater than 1 is not - // supported for now + // supported for now. tableAssertTestFail( "select s1 from table3 t3 where 30 = t3.s1 and s1 > (select max(s1) from table2 t2 where t2.s1 = t3.s1 limit 2)", "701: Given correlated subquery is not supported", DATABASE_NAME); - } - // todo: find out why this fails occasionally - @Test - public void testMultipleRowsReturnedByScalarSubquery() { - // Legality check: Scalar subquery should only return one row + // Legality check: Scalar subquery should return only one row. tableAssertTestFail( "select s1 from table1 t1 where s1 >= (select s1 from table3 t3 where t3.s1 = t1.s1)", "701: Scalar sub-query has returned multiple rows", 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 4f7a4d2b97b..1a663f47724 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 @@ -1105,8 +1105,7 @@ public class PushPredicateIntoTableScan implements PlanOptimizer { !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; + return node.replaceChildren(ImmutableList.of(rewrittenChild)); } @Override
