Re: BUG #18568: BUG: Result wrong when do group by on partition table! - Mailing list pgsql-bugs

Hi Amit, 

I have another case that can confirm what I suspect.  I now think in set_baserel_partition_key_exprs(),

we should use partkey->partcollation[cnt] instead of partkey->parttypcoll[cnt].

I find a wrong result when enable partitionwise_join, as below:

postgres=# set enable_partitionwise_join = on;
SET
postgres=# set max_parallel_workers_per_gather = 0;
SET
postgres=# explain select count(*) from pagg_tab t1 join pagg_tab t2 on t1.c = t2.c;
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Aggregate  (cost=17010.00..17010.01 rows=1 width=8)
   ->  Append  (cost=16.50..14760.00 rows=900000 width=0)
         ->  Hash Join  (cost=16.50..2052.00 rows=180000 width=0)
               Hash Cond: (t1_1.c = t2_1.c)
               ->  Seq Scan on pagg_tab_p2 t1_1  (cost=0.00..9.00 rows=600 width=2)
               ->  Hash  (cost=9.00..9.00 rows=600 width=2)
                     ->  Seq Scan on pagg_tab_p2 t2_1  (cost=0.00..9.00 rows=600 width=2)
         ->  Hash Join  (cost=33.00..4104.00 rows=360000 width=0)
               Hash Cond: (t1_2.c = t2_2.c)
               ->  Seq Scan on pagg_tab_p3 t1_2  (cost=0.00..18.00 rows=1200 width=2)
               ->  Hash  (cost=18.00..18.00 rows=1200 width=2)
                     ->  Seq Scan on pagg_tab_p3 t2_2  (cost=0.00..18.00 rows=1200 width=2)
         ->  Hash Join  (cost=33.00..4104.00 rows=360000 width=0)
               Hash Cond: (t1_3.c = t2_3.c)
               ->  Seq Scan on pagg_tab_p1 t1_3  (cost=0.00..18.00 rows=1200 width=2)
               ->  Hash  (cost=18.00..18.00 rows=1200 width=2)
                     ->  Seq Scan on pagg_tab_p1 t2_3  (cost=0.00..18.00 rows=1200 width=2)
(17 rows)

postgres=#  select count(*) from pagg_tab t1 join pagg_tab t2 on t1.c = t2.c;
 count
--------
 900000
(1 row)

postgres=# set enable_partitionwise_join = off;
SET
postgres=# explain select count(*) from pagg_tab t1 join pagg_tab t2 on t1.c = t2.c;
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Aggregate  (cost=1269.02..1269.03 rows=1 width=8)
   ->  Merge Join  (cost=466.52..1156.52 rows=45000 width=0)
         Merge Cond: (t1.c = t2.c)
         ->  Sort  (cost=233.26..240.76 rows=3000 width=2)
               Sort Key: t1.c COLLATE case_insensitive
               ->  Append  (cost=0.00..60.00 rows=3000 width=2)
                     ->  Seq Scan on pagg_tab_p2 t1_1  (cost=0.00..9.00 rows=600 width=2)
                     ->  Seq Scan on pagg_tab_p3 t1_2  (cost=0.00..18.00 rows=1200 width=2)
                     ->  Seq Scan on pagg_tab_p1 t1_3  (cost=0.00..18.00 rows=1200 width=2)
         ->  Sort  (cost=233.26..240.76 rows=3000 width=2)
               Sort Key: t2.c COLLATE case_insensitive
               ->  Append  (cost=0.00..60.00 rows=3000 width=2)
                     ->  Seq Scan on pagg_tab_p2 t2_1  (cost=0.00..9.00 rows=600 width=2)
                     ->  Seq Scan on pagg_tab_p3 t2_2  (cost=0.00..18.00 rows=1200 width=2)
                     ->  Seq Scan on pagg_tab_p1 t2_3  (cost=0.00..18.00 rows=1200 width=2)
(15 rows)

postgres=#  select count(*) from pagg_tab t1 join pagg_tab t2 on t1.c = t2.c;
  count
---------
 1800000
(1 row)

I gave a try that use partkey->partcollation[cnt] then the query result is correct when enable_partitionwise_join is on.

And I found some codes only use equal not check the collation, for example, in match_expr_to_partition_keys():

if (equal(lfirst(lc), expr))

lfirst(lc) is partkeys.

I thinks other codes assume that the collation of partexprs in RelOptInfo is same with pg_partitioned_table. But it is not.

--