fanfuxiaoran commented on issue #594:
URL: https://github.com/apache/cloudberry/issues/594#issuecomment-2464267391
Have stepped into the query, found :
```
explain (verbose) SELECT t1.c0, t1.c1, t5.c0, t5.c1 FROM t1* FULL OUTER
JOIN t5* ON (('}n()')LIKE((((0.7999568)IS DISTINCT
FROM(0.53532124)))::VARCHAR(100)))
and (NOT (((t1.c1)>=(t1.c1)))) ISNULL;
QUERY PLAN
-----------------------------------------------------------------------------------------------
Merge Full Join (cost=10000000000.00..10027262749.67 rows=46700 width=80)
Output: t1.c0, t1.c1, t5.c0, t5.c1
Join Filter: false
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..812.33
rows=46700 width=40)
Output: t1.c0, t1.c1
-> Seq Scan on public.t1 (cost=0.00..189.67 rows=15567 width=40)
Output: t1.c0, t1.c1
-> Materialize (cost=0.00..929.08 rows=46700 width=40)
Output: t5.c0, t5.c1
-> Gather Motion 3:1 (slice2; segments: 3) (cost=0.00..812.33
rows=46700 width=40)
Output: t5.c0, t5.c1
-> Seq Scan on public.t5 (cost=0.00..189.67 rows=15567
width=40)
Output: t5.c0, t5.c1
Optimizer: Postgres query optimizer
(14 rows)
```
the commit 30cfe889e95dd78c160a0d855dba5d6125ca8bc4 from gpdb only can
handle the query which contains FALSE on join condition .
```
explain (verbose) SELECT t1.c0, t1.c1, t5.c0, t5.c1 FROM t1* FULL OUTER
JOIN t5* ON (('}n()')LIKE((((0.7999568)IS DISTINCT
FROM(0.53532124)))::VARCHAR(100)))
WHERE (NOT (((t1.c1)>=(t1.c1)))) ISNULL;
QUERY PLAN
----------------------------------------------------------------------------------
Result (cost=0.00..0.00 rows=0 width=32)
Output: NULL::double precision, NULL::inet, NULL::double precision,
NULL::inet
One-Time Filter: false
Optimizer: Pivotal Optimizer (GPORCA)
(4 rows)
```
But for the above query, the ISNULL is in where condition, it runs
uncorrectly.
I think `ISNULL` predication should not be pushed down to relation scan when
existing `FULL OUTER JOIN`
Will continue to figure out how to fix this .
--
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]
