cloudberry icon indicating copy to clipboard operation
cloudberry copied to clipboard

Bugs of hash join for is not distinct null

Open my-ship-it opened this issue 2 years ago • 0 comments

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)

my-ship-it avatar Aug 11 '23 09:08 my-ship-it