irenjj commented on code in PR #16186:
URL: https://github.com/apache/datafusion/pull/16186#discussion_r2106333691
##########
datafusion/sqllogictest/test_files/subquery.slt:
##########
@@ -1482,3 +1482,85 @@ logical_plan
statement count 0
drop table person;
+
+#
correlated_recursive_scalar_subquery_with_level_3_scalar_subquery_referencing_level1_relation
+query TT
+explain select c_custkey from customer
+where c_acctbal < (
+ select sum(o_totalprice) from orders
+ where o_custkey = c_custkey
+ and o_totalprice < (
+ select sum(l_extendedprice) as price from lineitem where l_orderkey =
o_orderkey
+ and l_extendedprice < c_acctbal
+ )
+) order by c_custkey;
+----
+logical_plan
+01)Sort: customer.c_custkey ASC NULLS LAST
+02)--Projection: customer.c_custkey
+03)----Inner Join: customer.c_custkey = __scalar_sq_1.o_custkey Filter:
CAST(customer.c_acctbal AS Decimal128(25, 2)) <
__scalar_sq_1.sum(orders.o_totalprice)
+04)------TableScan: customer projection=[c_custkey, c_acctbal]
+05)------SubqueryAlias: __scalar_sq_1
+06)--------Projection: sum(orders.o_totalprice), orders.o_custkey
+07)----------Aggregate: groupBy=[[orders.o_custkey]],
aggr=[[sum(orders.o_totalprice)]]
+08)------------Projection: orders.o_custkey, orders.o_totalprice
+09)--------------Filter: CAST(orders.o_totalprice AS Decimal128(25, 2)) <
(<subquery>)
+10)----------------Subquery:
+11)------------------Projection: sum(lineitem.l_extendedprice) AS price
+12)--------------------Aggregate: groupBy=[[]],
aggr=[[sum(lineitem.l_extendedprice)]]
+13)----------------------Filter: lineitem.l_orderkey =
outer_ref(orders.o_orderkey) AND lineitem.l_extendedprice <
outer_ref(customer.c_acctbal)
+14)------------------------TableScan: lineitem,
partial_filters=[lineitem.l_orderkey = outer_ref(orders.o_orderkey),
lineitem.l_extendedprice < outer_ref(customer.c_acctbal)]
+15)----------------TableScan: orders projection=[o_orderkey, o_custkey,
o_totalprice]
+
+#
correlated_recursive_scalar_subquery_with_level_3_exists_subquery_referencing_level1_relation
+query TT
+explain select c_custkey from customer
+where c_acctbal < (
+ select sum(o_totalprice) from orders
+ where o_custkey = c_custkey
+ and exists (
+ select * from lineitem where l_orderkey = o_orderkey
+ and l_extendedprice < c_acctbal
+ )
+) order by c_custkey;
+----
+logical_plan
+01)Sort: customer.c_custkey ASC NULLS LAST
+02)--Projection: customer.c_custkey
+03)----Inner Join: customer.c_custkey = __scalar_sq_2.o_custkey Filter:
CAST(customer.c_acctbal AS Decimal128(25, 2)) <
__scalar_sq_2.sum(orders.o_totalprice)
+04)------TableScan: customer projection=[c_custkey, c_acctbal]
+05)------SubqueryAlias: __scalar_sq_2
+06)--------Projection: sum(orders.o_totalprice), orders.o_custkey
+07)----------Aggregate: groupBy=[[orders.o_custkey]],
aggr=[[sum(orders.o_totalprice)]]
+08)------------Projection: orders.o_custkey, orders.o_totalprice
+09)--------------LeftSemi Join: orders.o_orderkey =
__correlated_sq_1.l_orderkey Filter: __correlated_sq_1.l_extendedprice <
customer.c_acctbal
+10)----------------TableScan: orders projection=[o_orderkey, o_custkey,
o_totalprice]
+11)----------------SubqueryAlias: __correlated_sq_1
+12)------------------TableScan: lineitem projection=[l_orderkey,
l_extendedprice]
+
+#
correlated_recursive_scalar_subquery_with_level_3_in_subquery_referencing_level1_relation
+query TT
+explain select c_custkey from customer
+where c_acctbal < (
+ select sum(o_totalprice) from orders
+ where o_custkey = c_custkey
+ and o_totalprice in (
+ select l_extendedprice as price from lineitem where l_orderkey =
o_orderkey
+ and l_extendedprice < c_acctbal
+ )
+) order by c_custkey;
Review Comment:
Still failed in datafusion-cli, but in sqllogictest it runs well:
```
> explain select c_custkey from customer
where c_acctbal < (
select sum(o_totalprice) from orders
where o_custkey = c_custkey
and o_totalprice in (
select l_extendedprice as price from lineitem where l_orderkey =
o_orderkey
and l_extendedprice < c_acctbal
)
) order by c_custkey;
Schema error: No field named customer.c_acctbal. Valid fields are
__correlated_sq_1.l_extendedprice.
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org
For additional commands, e-mail: github-h...@datafusion.apache.org
