On Tue, Oct 31, 2017 at 2:45 PM, Robert Haas <robertmh...@gmail.com> wrote:
>> OK, committed. This is a good example of how having good code
> coverage doesn't necessarily mean you've found all the bugs. :-)
>
As of now partition_join.sql is not having test cases covering cases
where partition table have default partition, attaching a small test
case patch to cover those.
Thanks & Regards,
Rajkumar Raghuwanshi
QMG, EnterpriseDB Corporation
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index 27ab852..045b4c2 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -1337,6 +1337,160 @@ SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM pht1 t1, ph
574.0000000000000000 | 574.5000000000000000 | 1148.0000000000000000 | 0011 | 0011 | A0011
(12 rows)
+-- test default partition behavior for range
+ALTER TABLE prt1 DETACH PARTITION prt1_p3;
+ALTER TABLE prt2 DETACH PARTITION prt2_p3;
+ALTER TABLE prt1 ATTACH PARTITION prt1_p3 DEFAULT;
+ALTER TABLE prt2 ATTACH PARTITION prt2_p3 DEFAULT;
+-- Join with pruned partitions from joining relations
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.a < 450 AND t2.b > 250 AND t1.b = 0 ORDER BY t1.a, t2.b;
+ QUERY PLAN
+---------------------------------------------------------------
+ Sort
+ Sort Key: t1.a
+ -> Append
+ -> Hash Join
+ Hash Cond: (t2.b = t1.a)
+ -> Seq Scan on prt2_p2 t2
+ Filter: (b > 250)
+ -> Hash
+ -> Seq Scan on prt1_p2 t1
+ Filter: ((a < 450) AND (b = 0))
+ -> Nested Loop
+ -> Seq Scan on prt1_p3 t1_1
+ Filter: ((a < 450) AND (b = 0))
+ -> Index Scan using iprt2_p3_b on prt2_p3 t2_1
+ Index Cond: ((b = t1_1.a) AND (b > 250))
+(15 rows)
+
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.a < 450 AND t2.b > 250 AND t1.b = 0 ORDER BY t1.a, t2.b;
+ a | c | b | c
+-----+------+-----+------
+ 300 | 0300 | 300 | 0300
+(1 row)
+
+ALTER TABLE prt1_e DETACH PARTITION prt1_e_p3;
+ALTER TABLE prt1_e ATTACH PARTITION prt1_e_p3 DEFAULT;
+-- N-way join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b) RIGHT JOIN prt1_e t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t3.c = 0 ORDER BY t1.a, t2.b, t3.a + t3.b;
+ QUERY PLAN
+-------------------------------------------------------------------------
+ Sort
+ Sort Key: t1.a, t2.b, ((t3.a + t3.b))
+ -> Result
+ -> Append
+ -> Nested Loop Left Join
+ -> Hash Right Join
+ Hash Cond: (t1.a = ((t3.a + t3.b) / 2))
+ -> Seq Scan on prt1_p1 t1
+ -> Hash
+ -> Seq Scan on prt1_e_p1 t3
+ Filter: (c = 0)
+ -> Index Scan using iprt2_p1_b on prt2_p1 t2
+ Index Cond: (t1.a = b)
+ -> Nested Loop Left Join
+ -> Hash Right Join
+ Hash Cond: (t1_1.a = ((t3_1.a + t3_1.b) / 2))
+ -> Seq Scan on prt1_p2 t1_1
+ -> Hash
+ -> Seq Scan on prt1_e_p2 t3_1
+ Filter: (c = 0)
+ -> Index Scan using iprt2_p2_b on prt2_p2 t2_1
+ Index Cond: (t1_1.a = b)
+ -> Nested Loop Left Join
+ -> Hash Right Join
+ Hash Cond: (t1_2.a = ((t3_2.a + t3_2.b) / 2))
+ -> Seq Scan on prt1_p3 t1_2
+ -> Hash
+ -> Seq Scan on prt1_e_p3 t3_2
+ Filter: (c = 0)
+ -> Index Scan using iprt2_p3_b on prt2_p3 t2_2
+ Index Cond: (t1_2.a = b)
+(31 rows)
+
+SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b) RIGHT JOIN prt1_e t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t3.c = 0 ORDER BY t1.a, t2.b, t3.a + t3.b;
+ a | c | b | c | ?column? | c
+-----+------+-----+------+----------+---
+ 0 | 0000 | 0 | 0000 | 0 | 0
+ 50 | 0050 | | | 100 | 0
+ 100 | 0100 | | | 200 | 0
+ 150 | 0150 | 150 | 0150 | 300 | 0
+ 200 | 0200 | | | 400 | 0
+ 250 | 0250 | | | 500 | 0
+ 300 | 0300 | 300 | 0300 | 600 | 0
+ 350 | 0350 | | | 700 | 0
+ 400 | 0400 | | | 800 | 0
+ 450 | 0450 | 450 | 0450 | 900 | 0
+ 500 | 0500 | | | 1000 | 0
+ 550 | 0550 | | | 1100 | 0
+(12 rows)
+
+-- test default partition behavior for list
+ALTER TABLE plt1 DETACH PARTITION plt1_p3;
+ALTER TABLE plt2 DETACH PARTITION plt2_p3;
+ALTER TABLE plt1 ATTACH PARTITION plt1_p3 DEFAULT;
+ALTER TABLE plt2 ATTACH PARTITION plt2_p3 DEFAULT;
+ALTER TABLE plt1_e DETACH PARTITION plt1_e_p3;
+ALTER TABLE plt1_e ATTACH PARTITION plt1_e_p3 DEFAULT;
+-- N-way join
+EXPLAIN (COSTS OFF)
+SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM plt1 t1, plt2 t2, plt1_e t3 WHERE t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c;
+ QUERY PLAN
+--------------------------------------------------------------------------------------
+ Sort
+ Sort Key: t1.c, t3.c
+ -> HashAggregate
+ Group Key: t1.c, t2.c, t3.c
+ -> Result
+ -> Append
+ -> Hash Join
+ Hash Cond: (t1.c = t2.c)
+ -> Seq Scan on plt1_p1 t1
+ -> Hash
+ -> Hash Join
+ Hash Cond: (t2.c = ltrim(t3.c, 'A'::text))
+ -> Seq Scan on plt2_p1 t2
+ -> Hash
+ -> Seq Scan on plt1_e_p1 t3
+ -> Hash Join
+ Hash Cond: (t1_1.c = t2_1.c)
+ -> Seq Scan on plt1_p2 t1_1
+ -> Hash
+ -> Hash Join
+ Hash Cond: (t2_1.c = ltrim(t3_1.c, 'A'::text))
+ -> Seq Scan on plt2_p2 t2_1
+ -> Hash
+ -> Seq Scan on plt1_e_p2 t3_1
+ -> Hash Join
+ Hash Cond: (t1_2.c = t2_2.c)
+ -> Seq Scan on plt1_p3 t1_2
+ -> Hash
+ -> Hash Join
+ Hash Cond: (t2_2.c = ltrim(t3_2.c, 'A'::text))
+ -> Seq Scan on plt2_p3 t2_2
+ -> Hash
+ -> Seq Scan on plt1_e_p3 t3_2
+(33 rows)
+
+SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM plt1 t1, plt2 t2, plt1_e t3 WHERE t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c;
+ avg | avg | avg | c | c | c
+----------------------+----------------------+-----------------------+------+------+-------
+ 24.0000000000000000 | 24.0000000000000000 | 48.0000000000000000 | 0000 | 0000 | A0000
+ 74.0000000000000000 | 75.0000000000000000 | 148.0000000000000000 | 0001 | 0001 | A0001
+ 124.0000000000000000 | 124.5000000000000000 | 248.0000000000000000 | 0002 | 0002 | A0002
+ 174.0000000000000000 | 174.0000000000000000 | 348.0000000000000000 | 0003 | 0003 | A0003
+ 224.0000000000000000 | 225.0000000000000000 | 448.0000000000000000 | 0004 | 0004 | A0004
+ 274.0000000000000000 | 274.5000000000000000 | 548.0000000000000000 | 0005 | 0005 | A0005
+ 324.0000000000000000 | 324.0000000000000000 | 648.0000000000000000 | 0006 | 0006 | A0006
+ 374.0000000000000000 | 375.0000000000000000 | 748.0000000000000000 | 0007 | 0007 | A0007
+ 424.0000000000000000 | 424.5000000000000000 | 848.0000000000000000 | 0008 | 0008 | A0008
+ 474.0000000000000000 | 474.0000000000000000 | 948.0000000000000000 | 0009 | 0009 | A0009
+ 524.0000000000000000 | 525.0000000000000000 | 1048.0000000000000000 | 0010 | 0010 | A0010
+ 574.0000000000000000 | 574.5000000000000000 | 1148.0000000000000000 | 0011 | 0011 | A0011
+(12 rows)
+
--
-- multiple levels of partitioning
--
@@ -1663,6 +1817,73 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1_l WHERE a = 1 AND a = 2)
One-Time Filter: false
(11 rows)
+-- test default partition behavior for multiple levels of partitoning
+ALTER TABLE prt1_l DETACH PARTITION prt1_l_p2;
+ALTER TABLE prt1_l ATTACH PARTITION prt1_l_p2 DEFAULT;
+ALTER TABLE prt1_l_p2 DETACH PARTITION prt1_l_p2_p2;
+ALTER TABLE prt1_l_p2 ATTACH PARTITION prt1_l_p2_p2 DEFAULT;
+ALTER TABLE prt1_l_p3 DETACH PARTITION prt1_l_p3_p1;
+ALTER TABLE prt1_l_p3 ATTACH PARTITION prt1_l_p3_p1 DEFAULT;
+ALTER TABLE prt2_l DETACH PARTITION prt2_l_p2;
+ALTER TABLE prt2_l ATTACH PARTITION prt2_l_p2 DEFAULT;
+ALTER TABLE prt2_l_p2 DETACH PARTITION prt2_l_p2_p2;
+ALTER TABLE prt2_l_p2 ATTACH PARTITION prt2_l_p2_p2 DEFAULT;
+ALTER TABLE prt2_l_p3 DETACH PARTITION prt2_l_p3_p1;
+ALTER TABLE prt2_l_p3 ATTACH PARTITION prt2_l_p3_p1 DEFAULT;
+-- left join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1 LEFT JOIN prt2_l t2 ON t1.a = t2.b AND t1.c = t2.c WHERE t1.b = 0 ORDER BY t1.a, t2.b;
+ QUERY PLAN
+------------------------------------------------------------------------------------
+ Sort
+ Sort Key: t1.a, t2.b
+ -> Append
+ -> Hash Right Join
+ Hash Cond: ((t2.b = t1.a) AND ((t2.c)::text = (t1.c)::text))
+ -> Seq Scan on prt2_l_p1 t2
+ -> Hash
+ -> Seq Scan on prt1_l_p1 t1
+ Filter: (b = 0)
+ -> Hash Right Join
+ Hash Cond: ((t2_1.b = t1_1.a) AND ((t2_1.c)::text = (t1_1.c)::text))
+ -> Append
+ -> Seq Scan on prt2_l_p3_p2 t2_1
+ -> Seq Scan on prt2_l_p3_p1 t2_2
+ -> Hash
+ -> Append
+ -> Seq Scan on prt1_l_p3_p1 t1_1
+ Filter: (b = 0)
+ -> Hash Right Join
+ Hash Cond: ((t2_3.b = t1_2.a) AND ((t2_3.c)::text = (t1_2.c)::text))
+ -> Seq Scan on prt2_l_p2_p1 t2_3
+ -> Hash
+ -> Seq Scan on prt1_l_p2_p1 t1_2
+ Filter: (b = 0)
+ -> Hash Right Join
+ Hash Cond: ((t2_4.b = t1_3.a) AND ((t2_4.c)::text = (t1_3.c)::text))
+ -> Seq Scan on prt2_l_p2_p2 t2_4
+ -> Hash
+ -> Seq Scan on prt1_l_p2_p2 t1_3
+ Filter: (b = 0)
+(30 rows)
+
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1 LEFT JOIN prt2_l t2 ON t1.a = t2.b AND t1.c = t2.c WHERE t1.b = 0 ORDER BY t1.a, t2.b;
+ a | c | b | c
+-----+------+-----+------
+ 0 | 0000 | 0 | 0000
+ 50 | 0002 | |
+ 100 | 0000 | |
+ 150 | 0002 | 150 | 0002
+ 200 | 0000 | |
+ 250 | 0002 | |
+ 300 | 0000 | 300 | 0000
+ 350 | 0002 | |
+ 400 | 0000 | |
+ 450 | 0002 | 450 | 0002
+ 500 | 0000 | |
+ 550 | 0002 | |
+(12 rows)
+
--
-- negative testcases
--
@@ -1868,3 +2089,24 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_n t1 FULL JOIN prt1 t2 ON (t1.c = t2.c);
-> Seq Scan on prt1_n_p2 t1_1
(10 rows)
+-- partition-wise join can not be applied if the different partition have
+-- default partition
+ALTER TABLE prt4_n DETACH PARTITION prt4_n_p1;
+ALTER TABLE prt4_n ATTACH PARTITION prt4_n_p1 DEFAULT;
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt4_n t2 WHERE t1.a = t2.a;
+ QUERY PLAN
+----------------------------------------------
+ Hash Join
+ Hash Cond: (t1.a = t2.a)
+ -> Append
+ -> Seq Scan on prt1_p1 t1
+ -> Seq Scan on prt1_p2 t1_1
+ -> Seq Scan on prt1_p3 t1_2
+ -> Hash
+ -> Append
+ -> Seq Scan on prt4_n_p2 t2
+ -> Seq Scan on prt4_n_p3 t2_1
+ -> Seq Scan on prt4_n_p1 t2_2
+(11 rows)
+
diff --git a/src/test/regress/sql/partition_join.sql b/src/test/regress/sql/partition_join.sql
index 6efdf3c..e6eb64a 100644
--- a/src/test/regress/sql/partition_join.sql
+++ b/src/test/regress/sql/partition_join.sql
@@ -261,6 +261,39 @@ EXPLAIN (COSTS OFF)
SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM pht1 t1, pht2 t2, pht1_e t3 WHERE t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c;
SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM pht1 t1, pht2 t2, pht1_e t3 WHERE t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c;
+-- test default partition behavior for range
+ALTER TABLE prt1 DETACH PARTITION prt1_p3;
+ALTER TABLE prt2 DETACH PARTITION prt2_p3;
+ALTER TABLE prt1 ATTACH PARTITION prt1_p3 DEFAULT;
+ALTER TABLE prt2 ATTACH PARTITION prt2_p3 DEFAULT;
+
+-- Join with pruned partitions from joining relations
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.a < 450 AND t2.b > 250 AND t1.b = 0 ORDER BY t1.a, t2.b;
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.a < 450 AND t2.b > 250 AND t1.b = 0 ORDER BY t1.a, t2.b;
+
+ALTER TABLE prt1_e DETACH PARTITION prt1_e_p3;
+ALTER TABLE prt1_e ATTACH PARTITION prt1_e_p3 DEFAULT;
+
+-- N-way join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b) RIGHT JOIN prt1_e t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t3.c = 0 ORDER BY t1.a, t2.b, t3.a + t3.b;
+SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b) RIGHT JOIN prt1_e t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t3.c = 0 ORDER BY t1.a, t2.b, t3.a + t3.b;
+
+-- test default partition behavior for list
+ALTER TABLE plt1 DETACH PARTITION plt1_p3;
+ALTER TABLE plt2 DETACH PARTITION plt2_p3;
+ALTER TABLE plt1 ATTACH PARTITION plt1_p3 DEFAULT;
+ALTER TABLE plt2 ATTACH PARTITION plt2_p3 DEFAULT;
+
+ALTER TABLE plt1_e DETACH PARTITION plt1_e_p3;
+ALTER TABLE plt1_e ATTACH PARTITION plt1_e_p3 DEFAULT;
+
+-- N-way join
+EXPLAIN (COSTS OFF)
+SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM plt1 t1, plt2 t2, plt1_e t3 WHERE t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c;
+SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM plt1 t1, plt2 t2, plt1_e t3 WHERE t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c;
+
--
-- multiple levels of partitioning
--
@@ -319,6 +352,26 @@ SELECT * FROM prt1_l t1 LEFT JOIN LATERAL
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1_l WHERE a = 1 AND a = 2) t1 RIGHT JOIN prt2_l t2 ON t1.a = t2.b AND t1.b = t2.a AND t1.c = t2.c;
+-- test default partition behavior for multiple levels of partitoning
+ALTER TABLE prt1_l DETACH PARTITION prt1_l_p2;
+ALTER TABLE prt1_l ATTACH PARTITION prt1_l_p2 DEFAULT;
+ALTER TABLE prt1_l_p2 DETACH PARTITION prt1_l_p2_p2;
+ALTER TABLE prt1_l_p2 ATTACH PARTITION prt1_l_p2_p2 DEFAULT;
+ALTER TABLE prt1_l_p3 DETACH PARTITION prt1_l_p3_p1;
+ALTER TABLE prt1_l_p3 ATTACH PARTITION prt1_l_p3_p1 DEFAULT;
+
+ALTER TABLE prt2_l DETACH PARTITION prt2_l_p2;
+ALTER TABLE prt2_l ATTACH PARTITION prt2_l_p2 DEFAULT;
+ALTER TABLE prt2_l_p2 DETACH PARTITION prt2_l_p2_p2;
+ALTER TABLE prt2_l_p2 ATTACH PARTITION prt2_l_p2_p2 DEFAULT;
+ALTER TABLE prt2_l_p3 DETACH PARTITION prt2_l_p3_p1;
+ALTER TABLE prt2_l_p3 ATTACH PARTITION prt2_l_p3_p1 DEFAULT;
+
+-- left join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1 LEFT JOIN prt2_l t2 ON t1.a = t2.b AND t1.c = t2.c WHERE t1.b = 0 ORDER BY t1.a, t2.b;
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1 LEFT JOIN prt2_l t2 ON t1.a = t2.b AND t1.c = t2.c WHERE t1.b = 0 ORDER BY t1.a, t2.b;
+
--
-- negative testcases
--
@@ -384,3 +437,10 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_n t1 JOIN prt2_n t2 ON (t1.c = t2.c) JOI
-- partitioned table
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_n t1 FULL JOIN prt1 t2 ON (t1.c = t2.c);
+
+-- partition-wise join can not be applied if the different partition have
+-- default partition
+ALTER TABLE prt4_n DETACH PARTITION prt4_n_p1;
+ALTER TABLE prt4_n ATTACH PARTITION prt4_n_p1 DEFAULT;
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt4_n t2 WHERE t1.a = t2.a;
