Crash in partition-wise join involving dummy partitioned relation - Mailing list pgsql-hackers

Hi,
I noticed a crash in partition-wise involving dummy partitioned
tables. Here's simple testcase

CREATE TABLE prt1 (a int, b int, c varchar) PARTITION BY RANGE(a);
CREATE TABLE prt1_p1 PARTITION OF prt1 FOR VALUES FROM (0) TO (250);
CREATE TABLE prt1_p3 PARTITION OF prt1 FOR VALUES FROM (500) TO (600);
CREATE TABLE prt1_p2 PARTITION OF prt1 FOR VALUES FROM (250) TO (500);
INSERT INTO prt1 SELECT i, i % 25, to_char(i, 'FM0000') FROM
generate_series(0, 599) i WHERE i % 2 = 0;
ANALYZE prt1;

CREATE TABLE prt2 (a int, b int, c varchar) PARTITION BY RANGE(b);
CREATE TABLE prt2_p1 PARTITION OF prt2 FOR VALUES FROM (0) TO (250);
CREATE TABLE prt2_p2 PARTITION OF prt2 FOR VALUES FROM (250) TO (500);
CREATE TABLE prt2_p3 PARTITION OF prt2 FOR VALUES FROM (500) TO (600);
INSERT INTO prt2 SELECT i % 25, i, to_char(i, 'FM0000') FROM
generate_series(0, 599) i WHERE i % 3 = 0;
ANALYZE prt2;

SET enable_partition_wise_join TO true;

EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a = 1 AND
a = 2) t1 RIGHT JOIN prt2 t2 ON t1.a = t2.b, prt1 t3 WHERE t2.b =
t3.a;

t1 is an empty partitioned relation, with partition scheme matching
that of t2. Thus build_joinrel_partition_info() deems t1 RIGHT JOIN t2
as partitioned and sets part_scheme, nparts and other partition
properties except part_rels. Later in try_partition_wise_join(), the
function bails out since t1 is dummy because of following code

    /*
     * set_rel_pathlist() may not create paths in children of an empty
     * partitioned table and so we can not add paths to child-joins. So, deem
     * such a join as unpartitioned. When a partitioned relation is deemed
     * empty because all its children are empty, dummy path will be set in
     * each of the children.  In such a case we could still consider the join
     * as partitioned, but it might not help much.
     */
    if (IS_DUMMY_REL(rel1) || IS_DUMMY_REL(rel2))
        return;

So, part_rels is never set for relation t1 LEFT JOIN t2. When
build_joinrel_partition_info() processes (t1 LEFT JOIN t2, t3), it
expects part_rels to be set for (t1 LEFT JOIN t2) since it's deemed to
be partitioned and following assertion fails

    Assert(REL_HAS_ALL_PART_PROPS(outer_rel) &&
           REL_HAS_ALL_PART_PROPS(inner_rel));

When I wrote this code, I thought that some join order of an N-way
join involving a dummy relation would have both the joining relations
partitioned with part_rels set i.e. child-join created. But that was a
wrong assumption. Any two-way join involving a dummy relation can not
have child-joins and hence can not be deemed as partitioned. For a 3
way join involving dummy relation, every two-way join involving that
dummy relation won't have child-joins and hence the 3 way join can not
have child-join. Similarly we can use induction to prove that any
N-way join involving a dummy relation will not have child-joins and
hence won't be partitioned. We can detect this case during
build_joinrel_partition_info(). One of the joining relations presented
to that function will involve the dummy relation and would have been
deemed as unpartitioned when it was processed. We don't need any dummy
relation handling in try_partition_wise_join().

Here's patch taking that approach.

-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company