Alena0704 commented on PR #1960:
URL: https://github.com/apache/cloudberry/pull/1960#issuecomment-5540388135
When a correlated aggregate subquery is pulled up into a join,
`SubqueryToJoinWalker()` records only two kinds of quals: non-correlated
branches found *inside* an AND `BoolExpr`, and correlated equality `OpExpr`s
that become the grouping key. A single non-correlated qual matches neither
branch — it falls through to the final `return` and is recorded
nowhere. `RemoveInnerJoinQuals()` then clears `je->quals`, and
`subselect->jointree->quals` is overwritten with what the walker collected, so
the predicate is gone from the rewritten query.
A one-predicate `JOIN ... ON` clause is exactly such a qual, so its
condition is silently dropped and the aggregate is computed over an
unrestricted join:
```sql
set optimizer = off;
create table o (a int, d int); insert into o values (2, 1);
create table i1(a int); insert into i1 values (1);
create table i2(a int); insert into i2 values (1), (2);
select * from o where o.a > (select max(i2.a) from i1 join i2 on i2.a = i1.a
where i1.a = o.d);
```
```
postgres=# create table o (a int, d int); insert into o values (2, 1);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named
'a' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make
sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
INSERT 0 1
postgres=# create table i1(a int); insert into i1 values (1);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named
'a' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make
sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
INSERT 0 1
postgres=# create table i2(a int); insert into i2 values (1), (2);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named
'a' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make
sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
INSERT 0 2
postgres=#
postgres=# -- returns (2,1) as a SubPlan, returns nothing when pulled up
postgres=# select * from o where o.a > (select max(i2.a) from i1 join i2 on
i2.a = i1.a where i1.a = o.d);
a | d
---+---
2 | 1
(1 row)
postgres=# set optimizer = off;
SET
postgres=# select * from o where o.a > (select max(i2.a) from i1 join i2 on
i2.a = i1.a where i1.a = o.d);
a | d
---+---
(0 rows)
```
`i1.a = 1`, so `ON` leaves only `i2.a = 1` and the subquery is `1`; `2 > 1`
holds and the row must be returned. Instead the query returns nothing, because
a `Nested Loop` with no `Join Cond` producing 2 rows instead of 1, which makes
`max` come out as 2.
The fix adds an `else` branch that keeps such quals where they were. Only
the Postgres planner is affected (`optimizer=off`, or an ORCA fallback); ORCA
does its own decorrelation and preserves the `ON` condition.
--
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]
