CREATE TABLE integers(i INTEGER, j TIMESTAMP TIME INDEX); Affected Rows: 0 -- SQLNESS REPLACE (-+) - -- SQLNESS REPLACE (\s\s+) _ -- SQLNESS REPLACE (RoundRobinBatch.*) REDACTED -- SQLNESS REPLACE (Hash.*) REDACTED -- SQLNESS REPLACE (peers.*) REDACTED EXPLAIN SELECT * FROM integers WHERE i IN ((SELECT i FROM integers)) ORDER BY i; +-+-+ | plan_type_| plan_| +-+-+ | logical_plan_| Sort: integers.i ASC NULLS LAST_| |_|_LeftSemi Join: integers.i = __correlated_sq_1.i_| |_|_MergeScan [is_placeholder=false, remote_input=[_| |_| Filter: integers.i IS NOT NULL_| |_|_TableScan: integers, partial_filters=[integers.i IS NOT NULL]_| |_| ]]_| |_|_MergeScan [is_placeholder=false, remote_input=[_| |_| SubqueryAlias: __correlated_sq_1_| |_|_Projection: integers.i_| |_|_Filter: integers.i IS NOT NULL_| |_|_TableScan: integers, partial_filters=[integers.i IS NOT NULL]_| |_| ]]_| | physical_plan | SortPreservingMergeExec: [i@0 ASC NULLS LAST]_| |_|_SortExec: expr=[i@0 ASC NULLS LAST], preserve_partitioning=[true]_| |_|_REDACTED |_|_RepartitionExec: partitioning=REDACTED |_|_MergeScanExec: REDACTED |_|_RepartitionExec: partitioning=REDACTED |_|_MergeScanExec: REDACTED |_|_| +-+-+ -- SQLNESS REPLACE (-+) - -- SQLNESS REPLACE (\s\s+) _ -- SQLNESS REPLACE (RoundRobinBatch.*) REDACTED -- SQLNESS REPLACE (Hash.*) REDACTED -- SQLNESS REPLACE (peers.*) REDACTED EXPLAIN SELECT * FROM integers i1 WHERE EXISTS(SELECT i FROM integers WHERE i=i1.i) ORDER BY i1.i; +-+-+ | plan_type_| plan_| +-+-+ | logical_plan_| Sort: i1.i ASC NULLS LAST_| |_|_LeftSemi Join: i1.i = __correlated_sq_1.i_| |_|_MergeScan [is_placeholder=false, remote_input=[_| |_| SubqueryAlias: i1_| |_|_Filter: integers.i IS NOT NULL_| |_|_TableScan: integers, partial_filters=[integers.i IS NOT NULL]_| |_| ]]_| |_|_MergeScan [is_placeholder=false, remote_input=[_| |_| SubqueryAlias: __correlated_sq_1_| |_|_Projection: integers.i_| |_|_Filter: integers.i IS NOT NULL_| |_|_TableScan: integers, partial_filters=[integers.i IS NOT NULL]_| |_| ]]_| | physical_plan | SortPreservingMergeExec: [i@0 ASC NULLS LAST]_| |_|_SortExec: expr=[i@0 ASC NULLS LAST], preserve_partitioning=[true]_| |_|_REDACTED |_|_RepartitionExec: partitioning=REDACTED |_|_MergeScanExec: REDACTED |_|_RepartitionExec: partitioning=REDACTED |_|_MergeScanExec: REDACTED |_|_| +-+-+ create table other (i INTEGER, j TIMESTAMP TIME INDEX); Affected Rows: 0 -- SQLNESS REPLACE (-+) - -- SQLNESS REPLACE (\s\s+) _ -- SQLNESS REPLACE (RoundRobinBatch.*) REDACTED -- SQLNESS REPLACE (Hash.*) REDACTED -- SQLNESS REPLACE (peers.*) REDACTED explain select t.i from ( select * from integers join other on 1=1 ) t where t.i is not null order by t.i desc; +-+-+ | plan_type_| plan_| +-+-+ | logical_plan_| Sort: t.i DESC NULLS FIRST_| |_|_SubqueryAlias: t_| |_|_Cross Join:_| |_|_Projection: integers.i_| |_|_MergeScan [is_placeholder=false, remote_input=[_| |_| Filter: integers.i IS NOT NULL_| |_|_TableScan: integers, partial_filters=[integers.i IS NOT NULL, Boolean(true)] | |_| ]]_| |_|_Projection:_| |_|_MergeScan [is_placeholder=false, remote_input=[_| |_| TableScan: other_| |_| ]]_| | physical_plan | SortPreservingMergeExec: [i@0 DESC]_| |_|_SortExec: expr=[i@0 DESC], preserve_partitioning=[true]_| |_|_CrossJoinExec_| |_|_ProjectionExec: expr=[i@0 as i]_| |_|_MergeScanExec: REDACTED |_|_RepartitionExec: partitioning=REDACTED |_|_ProjectionExec: expr=[]_| |_|_MergeScanExec: REDACTED |_|_| +-+-+ INSERT INTO other SELECT i, 2 FROM integers WHERE i=(SELECT MAX(i) FROM integers); Affected Rows: 0 -- Explain physical plan for DML is not supported because it looks up the table name in a way that is -- different from normal queries. It also requires the table provider to implement the `insert_into()` method. EXPLAIN INSERT INTO other SELECT i, 2 FROM integers WHERE i=(SELECT MAX(i) FROM integers); +---------------------+-----------------------------------------------------------------------------+ | plan_type | plan | +---------------------+-----------------------------------------------------------------------------+ | logical_plan | Dml: op=[Insert Into] table=[other] | | | Projection: integers.i AS i, TimestampMillisecond(2, None) AS j | | | Filter: integers.i = () | | | Subquery: | | | MergeScan [is_placeholder=false, remote_input=[ | | | Projection: max(integers.i) | | | Aggregate: groupBy=[[]], aggr=[[max(integers.i)]] | | | TableScan: integers | | | ]] | | | Projection: integers.i | | | MergeScan [is_placeholder=false, remote_input=[ | | | TableScan: integers | | | ]] | | physical_plan_error | This feature is not implemented: Insert into not implemented for this table | +---------------------+-----------------------------------------------------------------------------+ drop table other; Affected Rows: 0 drop table integers; Affected Rows: 0 CREATE TABLE integers(i INTEGER, j TIMESTAMP TIME INDEX) PARTITION ON COLUMNS (i) ( i < 1000, i >= 1000 AND i < 2000, i >= 2000 ); Affected Rows: 0 -- SQLNESS REPLACE (-+) - -- SQLNESS REPLACE (\s\s+) _ -- SQLNESS REPLACE (RoundRobinBatch.*) REDACTED -- SQLNESS REPLACE (Hash.*) REDACTED -- SQLNESS REPLACE (peers.*) REDACTED EXPLAIN SELECT * FROM integers i1 WHERE EXISTS(SELECT i FROM integers WHERE i=i1.i) ORDER BY i1.i; +-+-+ | plan_type_| plan_| +-+-+ | logical_plan_| Sort: i1.i ASC NULLS LAST_| |_|_LeftSemi Join: i1.i = __correlated_sq_1.i_| |_|_MergeScan [is_placeholder=false, remote_input=[_| |_| SubqueryAlias: i1_| |_|_Filter: integers.i IS NOT NULL_| |_|_TableScan: integers, partial_filters=[integers.i IS NOT NULL]_| |_| ]]_| |_|_MergeScan [is_placeholder=false, remote_input=[_| |_| SubqueryAlias: __correlated_sq_1_| |_|_Projection: integers.i_| |_|_Filter: integers.i IS NOT NULL_| |_|_TableScan: integers, partial_filters=[integers.i IS NOT NULL]_| |_| ]]_| | physical_plan | SortPreservingMergeExec: [i@0 ASC NULLS LAST]_| |_|_SortExec: expr=[i@0 ASC NULLS LAST], preserve_partitioning=[true]_| |_|_REDACTED |_|_RepartitionExec: partitioning=REDACTED |_|_MergeScanExec: REDACTED |_|_RepartitionExec: partitioning=REDACTED |_|_MergeScanExec: REDACTED |_|_| +-+-+ -- SQLNESS REPLACE (-+) - -- SQLNESS REPLACE (\s\s+) _ -- SQLNESS REPLACE (RoundRobinBatch.*) REDACTED -- SQLNESS REPLACE (Hash.*) REDACTED -- SQLNESS REPLACE (peers.*) REDACTED EXPLAIN SELECT * FROM integers i1 WHERE EXISTS(SELECT count(i) FROM integers WHERE i=i1.i) ORDER BY i1.i; +-+-+ | plan_type_| plan_| +-+-+ | logical_plan_| Sort: i1.i ASC NULLS LAST_| |_|_LeftSemi Join: i1.i = __correlated_sq_1.i_| |_|_MergeScan [is_placeholder=false, remote_input=[_| |_| SubqueryAlias: i1_| |_|_Filter: integers.i IS NOT NULL_| |_|_TableScan: integers, partial_filters=[integers.i IS NOT NULL]_| |_| ]]_| |_|_Projection: __correlated_sq_1.i_| |_|_MergeScan [is_placeholder=false, remote_input=[_| |_| SubqueryAlias: __correlated_sq_1_| |_|_Projection: count(integers.i), integers.i_| |_|_Aggregate: groupBy=[[integers.i]], aggr=[[count(integers.i)]]_| |_|_Filter: integers.i IS NOT NULL_| |_|_TableScan: integers, partial_filters=[integers.i IS NOT NULL]_| |_| ]]_| | physical_plan | SortPreservingMergeExec: [i@0 ASC NULLS LAST]_| |_|_SortExec: expr=[i@0 ASC NULLS LAST], preserve_partitioning=[true]_| |_|_REDACTED |_|_RepartitionExec: partitioning=REDACTED |_|_MergeScanExec: REDACTED |_|_RepartitionExec: partitioning=REDACTED |_|_ProjectionExec: expr=[i@1 as i]_| |_|_MergeScanExec: REDACTED |_|_| +-+-+ DROP TABLE integers; Affected Rows: 0 CREATE TABLE t(ts timestamp time index, a INT, b INT)PARTITION ON COLUMNS (a) ( a < 1000, a >= 1000 AND a < 2000, a >= 2000 ); Affected Rows: 0 CREATE TABLE t1(ts timestamp time index, a INT)PARTITION ON COLUMNS (a) ( a < 1000, a >= 1000 AND a < 2000, a >= 2000 ); Affected Rows: 0 CREATE TABLE t2(ts timestamp time index, a INT)PARTITION ON COLUMNS (a) ( a < 1000, a >= 1000 AND a < 2000, a >= 2000 ); Affected Rows: 0 INSERT INTO t(ts,a,b) VALUES (1,3,30),(2,1,10),(3,2,20); Affected Rows: 3 INSERT INTO t1(ts,a) VALUES (1,1),(2,3); Affected Rows: 2 INSERT INTO t2(ts,a) VALUES (1,2),(2,3); Affected Rows: 2 SELECT x FROM (SELECT a AS x FROM t) sq ORDER BY x; +---+ | x | +---+ | 1 | | 2 | | 3 | +---+ -- expected: 1,2,3 -- SQLNESS REPLACE (-+) - -- SQLNESS REPLACE (\s\s+) _ -- SQLNESS REPLACE (RoundRobinBatch.*) REDACTED -- SQLNESS REPLACE (Hash.*) REDACTED -- SQLNESS REPLACE (peers.*) REDACTED EXPLAIN SELECT x FROM (SELECT a AS x FROM t) sq ORDER BY x; +-+-+ | plan_type_| plan_| +-+-+ | logical_plan_| MergeSort: sq.x ASC NULLS LAST_| |_|_MergeScan [is_placeholder=false, remote_input=[_| |_| Sort: sq.x ASC NULLS LAST_| |_|_Projection: sq.x_| |_|_SubqueryAlias: sq_| |_|_Projection: t.a AS x_| |_|_TableScan: t_| |_| ]]_| | physical_plan | MergeSortExec: [x@0 ASC NULLS LAST]_| |_|_MergeScanExec: REDACTED |_|_| +-+-+ SELECT x, COUNT(*) AS c FROM (SELECT a AS x FROM t) sq GROUP BY x ORDER BY x; +---+---+ | x | c | +---+---+ | 1 | 1 | | 2 | 1 | | 3 | 1 | +---+---+ -- expected: -- x | c -- 1 | 1 -- 2 | 1 -- 3 | 1 -- SQLNESS REPLACE (-+) - -- SQLNESS REPLACE (\s\s+) _ -- SQLNESS REPLACE (RoundRobinBatch.*) REDACTED -- SQLNESS REPLACE (Hash.*) REDACTED -- SQLNESS REPLACE (peers.*) REDACTED EXPLAIN SELECT x, COUNT(*) AS c FROM (SELECT a AS x FROM t) sq GROUP BY x ORDER BY x; +-+-+ | plan_type_| plan_| +-+-+ | logical_plan_| MergeSort: sq.x ASC NULLS LAST_| |_|_MergeScan [is_placeholder=false, remote_input=[_| |_| Sort: sq.x ASC NULLS LAST_| |_|_Projection: sq.x, count(Int64(1)) AS count(*) AS c_| |_|_Aggregate: groupBy=[[sq.x]], aggr=[[count(Int64(1))]]_| |_|_SubqueryAlias: sq_| |_|_Projection: t.a AS x_| |_|_TableScan: t_| |_| ]]_| | physical_plan | MergeSortExec: [x@0 ASC NULLS LAST]_| |_|_MergeScanExec: REDACTED |_|_| +-+-+ SELECT DISTINCT x FROM (SELECT a AS x FROM t) sq ORDER BY x; +---+ | x | +---+ | 1 | | 2 | | 3 | +---+ -- expecetd: 1,2,3 -- SQLNESS REPLACE (-+) - -- SQLNESS REPLACE (\s\s+) _ -- SQLNESS REPLACE (RoundRobinBatch.*) REDACTED -- SQLNESS REPLACE (Hash.*) REDACTED -- SQLNESS REPLACE (peers.*) REDACTED EXPLAIN SELECT DISTINCT x FROM (SELECT a AS x FROM t) sq ORDER BY x; +-+-+ | plan_type_| plan_| +-+-+ | logical_plan_| Sort: sq.x ASC NULLS LAST_| |_|_Aggregate: groupBy=[[sq.x]], aggr=[[]]_| |_|_MergeScan [is_placeholder=false, remote_input=[_| |_| Distinct:_| |_|_Projection: sq.x_| |_|_SubqueryAlias: sq_| |_|_Projection: t.a AS x_| |_|_TableScan: t_| |_| ]]_| | physical_plan | SortPreservingMergeExec: [x@0 ASC NULLS LAST]_| |_|_SortExec: expr=[x@0 ASC NULLS LAST], preserve_partitioning=[true]_| |_|_AggregateExec: mode=FinalPartitioned, gby=[x@0 as x], aggr=[]_| |_|_RepartitionExec: partitioning=REDACTED |_|_AggregateExec: mode=Partial, gby=[x@0 as x], aggr=[]_| |_|_RepartitionExec: partitioning=REDACTED |_|_MergeScanExec: REDACTED |_|_| +-+-+ SELECT sq.x FROM (SELECT a AS x FROM t) sq ORDER BY sq.x; +---+ | x | +---+ | 1 | | 2 | | 3 | +---+ -- expected: 1,2,3 -- SQLNESS REPLACE (-+) - -- SQLNESS REPLACE (\s\s+) _ -- SQLNESS REPLACE (RoundRobinBatch.*) REDACTED -- SQLNESS REPLACE (Hash.*) REDACTED -- SQLNESS REPLACE (peers.*) REDACTED EXPLAIN SELECT sq.x FROM (SELECT a AS x FROM t) sq ORDER BY sq.x; +-+-+ | plan_type_| plan_| +-+-+ | logical_plan_| MergeSort: sq.x ASC NULLS LAST_| |_|_MergeScan [is_placeholder=false, remote_input=[_| |_| Sort: sq.x ASC NULLS LAST_| |_|_Projection: sq.x_| |_|_SubqueryAlias: sq_| |_|_Projection: t.a AS x_| |_|_TableScan: t_| |_| ]]_| | physical_plan | MergeSortExec: [x@0 ASC NULLS LAST]_| |_|_MergeScanExec: REDACTED |_|_| +-+-+ SELECT y FROM (SELECT x AS y FROM (SELECT a AS x FROM t) sq1) sq2 ORDER BY y; +---+ | y | +---+ | 1 | | 2 | | 3 | +---+ -- expected: 1,2,3 -- SQLNESS REPLACE (-+) - -- SQLNESS REPLACE (\s\s+) _ -- SQLNESS REPLACE (RoundRobinBatch.*) REDACTED -- SQLNESS REPLACE (Hash.*) REDACTED -- SQLNESS REPLACE (peers.*) REDACTED EXPLAIN SELECT y FROM (SELECT x AS y FROM (SELECT a AS x FROM t) sq1) sq2 ORDER BY y; +-+-+ | plan_type_| plan_| +-+-+ | logical_plan_| MergeSort: sq2.y ASC NULLS LAST_| |_|_MergeScan [is_placeholder=false, remote_input=[_| |_| Sort: sq2.y ASC NULLS LAST_| |_|_Projection: sq2.y_| |_|_SubqueryAlias: sq2_| |_|_Projection: sq1.x AS y_| |_|_SubqueryAlias: sq1_| |_|_Projection: t.a AS x_| |_|_TableScan: t_| |_| ]]_| | physical_plan | MergeSortExec: [y@0 ASC NULLS LAST]_| |_|_MergeScanExec: REDACTED |_|_| +-+-+ SELECT x, x + 1 AS y FROM (SELECT a AS x FROM t) sq ORDER BY x; +---+---+ | x | y | +---+---+ | 1 | 2 | | 2 | 3 | | 3 | 4 | +---+---+ -- expected: -- (x,y) -- (1,2) -- (2,3) -- (3,4) -- SQLNESS REPLACE (-+) - -- SQLNESS REPLACE (\s\s+) _ -- SQLNESS REPLACE (RoundRobinBatch.*) REDACTED -- SQLNESS REPLACE (Hash.*) REDACTED -- SQLNESS REPLACE (peers.*) REDACTED EXPLAIN SELECT x, x + 1 AS y FROM (SELECT a AS x FROM t) sq ORDER BY x; +-+-+ | plan_type_| plan_| +-+-+ | logical_plan_| MergeSort: sq.x ASC NULLS LAST_| |_|_MergeScan [is_placeholder=false, remote_input=[_| |_| Sort: sq.x ASC NULLS LAST_| |_|_Projection: sq.x, CAST(sq.x AS Int64) + Int64(1) AS y_| |_|_SubqueryAlias: sq_| |_|_Projection: t.a AS x_| |_|_TableScan: t_| |_| ]]_| | physical_plan | MergeSortExec: [x@0 ASC NULLS LAST]_| |_|_MergeScanExec: REDACTED |_|_| +-+-+ SELECT a FROM ((SELECT a FROM t1) UNION ALL (SELECT a FROM t2)) u ORDER BY a; +---+ | a | +---+ | 1 | | 2 | | 3 | | 3 | +---+ -- expected: 1,2,3,3 -- SQLNESS REPLACE (-+) - -- SQLNESS REPLACE (\s\s+) _ -- SQLNESS REPLACE (RoundRobinBatch.*) REDACTED -- SQLNESS REPLACE (Hash.*) REDACTED -- SQLNESS REPLACE (peers.*) REDACTED EXPLAIN SELECT a FROM ((SELECT a FROM t1) UNION ALL (SELECT a FROM t2)) u ORDER BY a; +-+-+ | plan_type_| plan_| +-+-+ | logical_plan_| Sort: u.a ASC NULLS LAST_| |_|_SubqueryAlias: u_| |_|_Union_| |_|_MergeScan [is_placeholder=false, remote_input=[_| |_| Projection: t1.a_| |_|_TableScan: t1_| |_| ]]_| |_|_MergeScan [is_placeholder=false, remote_input=[_| |_| Projection: t2.a_| |_|_TableScan: t2_| |_| ]]_| | physical_plan | SortPreservingMergeExec: [a@0 ASC NULLS LAST]_| |_|_SortExec: expr=[a@0 ASC NULLS LAST], preserve_partitioning=[true]_| |_|_InterleaveExec_| |_|_MergeScanExec: REDACTED |_|_MergeScanExec: REDACTED |_|_| +-+-+ SELECT u1.a FROM (SELECT a FROM t1) u1 JOIN (SELECT a FROM t2) u2 ON u1.a = u2.a ORDER BY u1.a; +---+ | a | +---+ | 3 | +---+ -- expected: 3 -- SQLNESS REPLACE (-+) - -- SQLNESS REPLACE (\s\s+) _ -- SQLNESS REPLACE (RoundRobinBatch.*) REDACTED -- SQLNESS REPLACE (Hash.*) REDACTED -- SQLNESS REPLACE (peers.*) REDACTED EXPLAIN SELECT u1.a FROM (SELECT a FROM t1) u1 JOIN (SELECT a FROM t2) u2 ON u1.a = u2.a ORDER BY u1.a; +-+-+ | plan_type_| plan_| +-+-+ | logical_plan_| Sort: u1.a ASC NULLS LAST_| |_|_Projection: u1.a_| |_|_Inner Join: u1.a = u2.a_| |_|_MergeScan [is_placeholder=false, remote_input=[_| |_| SubqueryAlias: u1_| |_|_Projection: t1.a_| |_|_Filter: t1.a IS NOT NULL_| |_|_TableScan: t1, partial_filters=[t1.a IS NOT NULL]_| |_| ]]_| |_|_MergeScan [is_placeholder=false, remote_input=[_| |_| SubqueryAlias: u2_| |_|_Projection: t2.a_| |_|_Filter: t2.a IS NOT NULL_| |_|_TableScan: t2, partial_filters=[t2.a IS NOT NULL]_| |_| ]]_| | physical_plan | SortPreservingMergeExec: [a@0 ASC NULLS LAST]_| |_|_SortExec: expr=[a@0 ASC NULLS LAST], preserve_partitioning=[true]_| |_|_REDACTED |_|_RepartitionExec: partitioning=REDACTED |_|_MergeScanExec: REDACTED |_|_RepartitionExec: partitioning=REDACTED |_|_MergeScanExec: REDACTED |_|_| +-+-+ SELECT x FROM (VALUES (2),(1)) v(x) ORDER BY x; +---+ | x | +---+ | 1 | | 2 | +---+ -- expected: 1,2 -- SQLNESS REPLACE (-+) - -- SQLNESS REPLACE (\s\s+) _ -- SQLNESS REPLACE (RoundRobinBatch.*) REDACTED -- SQLNESS REPLACE (Hash.*) REDACTED -- SQLNESS REPLACE (peers.*) REDACTED EXPLAIN SELECT x FROM (VALUES (2),(1)) v(x) ORDER BY x; +-+-+ | plan_type_| plan_| +-+-+ | logical_plan_| Sort: v.x ASC NULLS LAST_| |_|_SubqueryAlias: v_| |_|_Projection: column1 AS x_| |_|_Values: (Int64(2)), (Int64(1))_| | physical_plan | ProjectionExec: expr=[column1@0 as x]_| |_|_SortExec: expr=[column1@0 ASC NULLS LAST], preserve_partitioning=[false] | |_|_DataSourceExec: partitions=1, partition_sizes=[1]_| |_|_| +-+-+ SELECT x FROM (SELECT a AS x FROM t) sq ORDER BY x LIMIT 2; +---+ | x | +---+ | 1 | | 2 | +---+ -- expected: 1,2 -- SQLNESS REPLACE (-+) - -- SQLNESS REPLACE (\s\s+) _ -- SQLNESS REPLACE (RoundRobinBatch.*) REDACTED -- SQLNESS REPLACE (Hash.*) REDACTED -- SQLNESS REPLACE (peers.*) REDACTED EXPLAIN SELECT x FROM (SELECT a AS x FROM t) sq ORDER BY x LIMIT 2; +-+-+ | plan_type_| plan_| +-+-+ | logical_plan_| Limit: skip=0, fetch=2_| |_|_MergeSort: sq.x ASC NULLS LAST_| |_|_MergeScan [is_placeholder=false, remote_input=[_| |_| Limit: skip=0, fetch=2_| |_|_Sort: sq.x ASC NULLS LAST_| |_|_Projection: sq.x_| |_|_SubqueryAlias: sq_| |_|_Projection: t.a AS x_| |_|_TableScan: t_| |_| ]]_| | physical_plan | MergeSortExec: [x@0 ASC NULLS LAST], fetch=2_| |_|_SortExec: TopK(fetch=2), expr=[x@0 ASC NULLS LAST], preserve_partitioning=[true]_| |_|_MergeScanExec: REDACTED |_|_| +-+-+ DROP TABLE t; Affected Rows: 0 DROP TABLE t1; Affected Rows: 0 DROP TABLE t2; Affected Rows: 0 -- Regression test for https://github.com/GreptimeTeam/greptimedb/issues/9260: -- nested scalar subqueries must get a MergeScan inside every subquery level. CREATE TABLE nested_scalar (k INT, v BIGINT, ts TIMESTAMP TIME INDEX); Affected Rows: 0 INSERT INTO nested_scalar VALUES (1, 10, '2024-01-01 00:00:00'), (2, NULL, '2024-01-01 00:00:01'); Affected Rows: 2 -- SQLNESS REPLACE (-+) - -- SQLNESS REPLACE (\s\s+) _ -- SQLNESS REPLACE (RoundRobinBatch.*) REDACTED -- SQLNESS REPLACE (Hash.*) REDACTED -- SQLNESS REPLACE (peers.*) REDACTED EXPLAIN SELECT (SELECT (SELECT MAX(v) FROM nested_scalar)) AS m FROM nested_scalar LIMIT 1; +-+-+ | plan_type_| plan_| +-+-+ | logical_plan_| Projection: () AS m_| |_|_Subquery:_| |_|_Projection: ()_| |_|_Subquery:_| |_|_MergeScan [is_placeholder=false, remote_input=[_| |_| Projection: max(nested_scalar.v)_| |_|_Aggregate: groupBy=[[]], aggr=[[max(nested_scalar.v)]]_| |_|_TableScan: nested_scalar_| |_| ]]_| |_|_EmptyRelation: rows=1_| |_|_Projection:_| |_|_Limit: skip=0, fetch=1_| |_|_MergeScan [is_placeholder=false, remote_input=[_| |_| TableScan: nested_scalar_| |_| ]]_| | physical_plan | ScalarSubqueryExec: subqueries=1_| |_|_ProjectionExec: expr=[scalar_subquery() as m]_| |_|_RepartitionExec: partitioning=REDACTED |_|_ProjectionExec: expr=[]_| |_|_GlobalLimitExec: skip=0, fetch=1_| |_|_MergeScanExec: REDACTED |_|_ScalarSubqueryExec: subqueries=1_| |_|_ProjectionExec: expr=[scalar_subquery() as max(nested_scalar.v)] | |_|_PlaceholderRowExec_| |_|_CooperativeExec_| |_|_MergeScanExec: REDACTED |_|_| +-+-+ SELECT (SELECT MAX(v) FROM nested_scalar WHERE v > (SELECT MAX(v) FROM nested_scalar)) AS m FROM nested_scalar LIMIT 1; +---+ | m | +---+ | | +---+ SELECT (SELECT (SELECT MAX(v) FROM nested_scalar)) AS m FROM nested_scalar LIMIT 1; +----+ | m | +----+ | 10 | +----+ DROP TABLE nested_scalar; Affected Rows: 0 -- Same regression as above, but on a range-partitioned table so the inner -- aggregate must merge partial results from multiple regions (the global -- AVG must be computed across regions before the outer SUM filter runs). CREATE TABLE nested_scalar_part (k INT, v BIGINT, ts TIMESTAMP TIME INDEX) PARTITION ON COLUMNS (k) ( k < 20, k >= 20 AND k < 40, k >= 40 ); Affected Rows: 0 INSERT INTO nested_scalar_part VALUES (1, 10, '2024-01-01 00:00:00'), (2, NULL, '2024-01-01 00:00:01'), (21, 20, '2024-01-01 00:00:02'), (25, 30, '2024-01-01 00:00:03'), (41, 40, '2024-01-01 00:00:04'), (50, 50, '2024-01-01 00:00:05'); Affected Rows: 6 -- global AVG(v) = 30, so the outer SUM(v) must be 40 + 50 = 90 SELECT (SELECT SUM(v) FROM nested_scalar_part WHERE v > (SELECT AVG(v) FROM nested_scalar_part)) AS m FROM nested_scalar_part LIMIT 1; +----+ | m | +----+ | 90 | +----+ -- SQLNESS REPLACE (-+) - -- SQLNESS REPLACE (\s\s+) _ -- SQLNESS REPLACE (RoundRobinBatch.*) REDACTED -- SQLNESS REPLACE (Hash.*) REDACTED -- SQLNESS REPLACE (peers.*) REDACTED EXPLAIN SELECT (SELECT SUM(v) FROM nested_scalar_part WHERE v > (SELECT AVG(v) FROM nested_scalar_part)) AS m FROM nested_scalar_part LIMIT 1; +-+-+ | plan_type_| plan_| +-+-+ | logical_plan_| Projection: () AS m_| |_|_Subquery:_| |_|_Aggregate: groupBy=[[]], aggr=[[sum(nested_scalar_part.v)]]_| |_|_Filter: CAST(nested_scalar_part.v AS Float64) > ()_| |_|_Subquery:_| |_|_Aggregate: groupBy=[[]], aggr=[[__avg_merge(__avg_state(nested_scalar_part.v)) AS avg(nested_scalar_part.v)]]_| |_|_MergeScan [is_placeholder=false, remote_input=[_| |_| Aggregate: groupBy=[[]], aggr=[[__avg_state(CAST(nested_scalar_part.v AS Float64))]]_| |_|_TableScan: nested_scalar_part_| |_| ]]_| |_|_Projection: nested_scalar_part.v_| |_|_MergeScan [is_placeholder=false, remote_input=[_| |_| TableScan: nested_scalar_part_| |_| ]]_| |_|_Projection:_| |_|_Limit: skip=0, fetch=1_| |_|_MergeScan [is_placeholder=false, remote_input=[_| |_| TableScan: nested_scalar_part_| |_| ]]_| | physical_plan | ScalarSubqueryExec: subqueries=1_| |_|_ProjectionExec: expr=[scalar_subquery() as m]_| |_|_RepartitionExec: partitioning=REDACTED |_|_CoalescePartitionsExec: fetch=1_| |_|_ProjectionExec: expr=[]_| |_|_LocalLimitExec: fetch=1_| |_|_MergeScanExec: REDACTED |_|_ScalarSubqueryExec: subqueries=1_| |_|_AggregateExec: mode=Final, gby=[], aggr=[sum(nested_scalar_part.v)]_| |_|_CoalescePartitionsExec_| |_|_AggregateExec: mode=Partial, gby=[], aggr=[sum(nested_scalar_part.v)]_| |_|_FilterExec: CAST(v@0 AS Float64) > scalar_subquery()_| |_|_RepartitionExec: partitioning=REDACTED |_|_ProjectionExec: expr=[v@1 as v]_| |_|_MergeScanExec: REDACTED |_|_AggregateExec: mode=Final, gby=[], aggr=[__avg_merge(__avg_state(nested_scalar_part.v)) as avg(nested_scalar_part.v)]_| |_|_CoalescePartitionsExec_| |_|_AggregateExec: mode=Partial, gby=[], aggr=[__avg_merge(__avg_state(nested_scalar_part.v)) as avg(nested_scalar_part.v)] | |_|_RepartitionExec: partitioning=REDACTED |_|_MergeScanExec: REDACTED |_|_| +-+-+ DROP TABLE nested_scalar_part; Affected Rows: 0