Alena0704 commented on code in PR #1933:
URL: https://github.com/apache/cloudberry/pull/1933#discussion_r3932828105
##########
src/test/regress/expected/eagerfree.out:
##########
@@ -1379,21 +1379,20 @@ where i < (select count(*) from smallt where smallt.i =
smallt2.i) order by 1,2,
explain select smallt2.* from smallt2
where i < (select count(*) from smallt where smallt.i = smallt2.i);
- QUERY PLAN
----------------------------------------------------------------------------------------
- Gather Motion 3:1 (slice1; segments: 3) (cost=5.10..8.08 rows=17 width=15)
- -> Hash Join (cost=5.10..8.08 rows=6 width=15)
- Hash Cond: smallt2.i = "Expr_SUBQUERY".csq_c0
- Join Filter: smallt2.i < "Expr_SUBQUERY".csq_c1
- -> Seq Scan on smallt2 (cost=0.00..2.50 rows=17 width=15)
- -> Hash (cost=4.97..4.97 rows=4 width=12)
- -> Subquery Scan on "Expr_SUBQUERY" (cost=4.75..4.97 rows=4
width=12)
- -> HashAggregate (cost=4.75..4.88 rows=4 width=12)
- Filter: smallt.i < count(*)
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=1.59..3.97 rows=17 width=15)
+ -> Hash Left Join (cost=1.59..2.86 rows=6 width=15)
+ Hash Cond: (smallt2.i = "Expr_SUBQUERY".csq_c0)
+ Filter: (smallt2.i < CASE WHEN "Expr_SUBQUERY".csq_c1 THEN
"Expr_SUBQUERY".csq_c2 ELSE '0'::bigint END)
+ -> Seq Scan on smallt2 (cost=0.00..1.17 rows=17 width=15)
+ -> Hash (cost=1.54..1.54 rows=3 width=13)
+ -> Subquery Scan on "Expr_SUBQUERY" (cost=1.50..1.54 rows=3
width=13)
+ -> HashAggregate (cost=1.50..1.53 rows=3 width=13)
Group Key: smallt.i
- -> Seq Scan on smallt (cost=0.00..4.00 rows=34
width=4)
+ -> Seq Scan on smallt (cost=0.00..1.33 rows=33
width=4)
Optimizer: Postgres query optimizer
-(12 rows)
+(11 rows)
Review Comment:
About the WindowAgg bug: without the pull-up the subquery has a plain
aggregate, so it returns exactly one row and the window runs over that single
row; the pulled-up subquery is grouped by the correlation columns, so the same
window runs over all groups at once.
I found no safe way to keep the pull-up here, so safe_to_convert_EXPR() now
bails out on subselect->hasWindowFuncs and the sublink stays a SubPlan.
I have found another bug - division by zero at plan time. Unlike the one
above, this one is introduced by this patch: on main the query below runs fine
and returns no rows. Reproduction:
```
set optimizer = off;
create table e1(a int, d int);
create table k1(a int); insert into k1 values (1), (2);
select * from e1 where e1.a > (select 1/count(*) from k1 where k1.a =
e1.d);
```
e1 is empty, so the correct answer is no rows and no error. Before the fix:
` ERROR: division by zero`
It failed in EXPLAIN too — nothing was ever executed. For a row with no
match the patch replaces the aggregate with the value it returns over empty
input — 0 for count(*), NULL for the others — and puts that expression into the
outer WHERE. So 1/count(*) becomes 1/0.
Nothing variable is left in it, and the planner evaluates it right away
instead of at run time. Only the 0 can do this: an expression built on NULL is
simplified to NULL without evaluating anything.
replace_agg_with_empty_default_mutator() now records whether it invented a
non-NULL value (only COUNT does), and convert_EXPR_to_join() refuses the
pull-up when it did and the default is not a plain Const. The sublink stays a
SubPlan:
```
Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on e1
Filter: (a > (SubPlan 1))
SubPlan 1
-> Aggregate
-> Result
Filter: (k1.a = e1.d)
-> Materialize
-> Broadcast Motion 3:3 (slice2;
segments: 3)
-> Seq Scan on k1
a | d
---+---
(0 rows)
```
--
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
