cloudberry
cloudberry copied to clipboard
Bugs of hash join for is not distinct null
gpadmin=# explain SELECT * FROM distinct_1, distinct_2 WHERE distinct_1.a IS not DISTINCT FROM distinct_2.a;
QUERY PLAN
------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=10000000000.00..10139293190.55 rows=9273690 width=8)
-> Nested Loop (cost=10000000000.00..10139107716.75 rows=9273690 width=8)
Join Filter: (NOT (distinct_1.a IS DISTINCT FROM distinct_2.a))
-> Seq Scan on distinct_1 (cost=0.00..1063.00 rows=96300 width=4)
-> Materialize (cost=0.00..1544.50 rows=96300 width=4)
-> Seq Scan on distinct_2 (cost=0.00..1063.00 rows=96300 width=4)
Optimizer: Postgres query optimizer
(7 rows)
gpadmin=# SELECT * FROM distinct_1, distinct_2 WHERE distinct_1.a IS not DISTINCT FROM distinct_2.a;
a | a
---+---
1 | 1
|
(2 rows)
gpadmin=# explain SELECT * FROM distinct_1, distinct_2 WHERE distinct_1.a IS not DISTINCT FROM distinct_2.a;
QUERY PLAN
------------------------------------------------------------------------------------------
Hash Join (cost=0.00..862.00 rows=1 width=8)
Hash Cond: (NOT (distinct_1.a IS DISTINCT FROM distinct_2.a))
-> Gather Motion 1:1 (slice1; segments: 1) (cost=0.00..431.00 rows=1 width=4)
-> Seq Scan on distinct_1 (cost=0.00..431.00 rows=1 width=4)
-> Hash (cost=431.00..431.00 rows=1 width=4)
-> Gather Motion 1:1 (slice2; segments: 1) (cost=0.00..431.00 rows=1 width=4)
-> Seq Scan on distinct_2 (cost=0.00..431.00 rows=1 width=4)
Optimizer: Pivotal Optimizer (GPORCA)
(8 rows)
gpadmin=# SELECT * FROM distinct_1, distinct_2 WHERE distinct_1.a IS not DISTINCT FROM distinct_2.a;
a | a
---+---
1 | 1
(1 row)