On Wed, 11 Oct 2023 at 22:09, Sergei Glukhov <[email protected]> wrote:
> Thanks for fixing this!
>
> I verified that issues are fixed.
Thanks for having a look.
Unfortunately, I'd not long sent the last email and realised that the
step_lastkeyno parameter is now unused and can just be removed from
both get_steps_using_prefix() and get_steps_using_prefix_recurse().
This requires some comment rewriting so I've attempted to do that too
in the attached updated version.
David
diff --git a/src/backend/partitioning/partprune.c
b/src/backend/partitioning/partprune.c
index 7179b22a05..20b5e01c9e 100644
--- a/src/backend/partitioning/partprune.c
+++ b/src/backend/partitioning/partprune.c
@@ -167,7 +167,6 @@ static List
*get_steps_using_prefix(GeneratePruningStepsContext *context,
bool
step_op_is_ne,
Expr
*step_lastexpr,
Oid
step_lastcmpfn,
- int
step_lastkeyno,
Bitmapset *step_nullkeys,
List
*prefix);
static List *get_steps_using_prefix_recurse(GeneratePruningStepsContext
*context,
@@ -175,7 +174,6 @@ static List
*get_steps_using_prefix_recurse(GeneratePruningStepsContext *context
bool step_op_is_ne,
Expr *step_lastexpr,
Oid step_lastcmpfn,
-
int step_lastkeyno,
Bitmapset *step_nullkeys,
List *prefix,
ListCell *start,
@@ -1531,7 +1529,6 @@ gen_prune_steps_from_opexps(GeneratePruningStepsContext
*context,
pc->op_is_ne,
pc->expr,
pc->cmpfn,
-
0,
NULL,
NIL);
opsteps =
list_concat(opsteps, pc_steps);
@@ -1657,7 +1654,6 @@ gen_prune_steps_from_opexps(GeneratePruningStepsContext
*context,
pc->op_is_ne,
pc->expr,
pc->cmpfn,
-
pc->keyno,
NULL,
prefix);
opsteps =
list_concat(opsteps, pc_steps);
@@ -1731,7 +1727,6 @@ gen_prune_steps_from_opexps(GeneratePruningStepsContext
*context,
false,
pc->expr,
pc->cmpfn,
-
pc->keyno,
nullkeys,
prefix);
opsteps = list_concat(opsteps,
pc_steps);
@@ -2350,25 +2345,27 @@
match_clause_to_partition_key(GeneratePruningStepsContext *context,
/*
* get_steps_using_prefix
- * Generate list of PartitionPruneStepOp steps each consisting of
given
+ * Generate a list of PartitionPruneStepOp steps each consisting
of given
* opstrategy
*
* To generate steps, step_lastexpr and step_lastcmpfn are appended to
* expressions and cmpfns, respectively, extracted from the clauses in
- * 'prefix'. Actually, since 'prefix' may contain multiple clauses for the
- * same partition key column, we must generate steps for various combinations
- * of the clauses of different keys.
+ * 'prefix'. Since 'prefix' may contain multiple clauses for each partition
+ * key, and since each step can only contain a single clause for each
+ * partition key, when there are multiple clauses for any given key, we must
+ * generate steps for all combinations of the clauses.
*
* For list/range partitioning, callers must ensure that step_nullkeys is
* NULL, and that prefix contains at least one clause for each of the
- * partition keys earlier than one specified in step_lastkeyno if it's
- * greater than zero. For hash partitioning, step_nullkeys is allowed to be
- * non-NULL, but they must ensure that prefix contains at least one clause
- * for each of the partition keys other than those specified in step_nullkeys
- * and step_lastkeyno.
- *
- * For both cases, callers must also ensure that clauses in prefix are sorted
- * in ascending order of their partition key numbers.
+ * partition keys prior to the key that 'step_lastexpr' belongs to.
+ *
+ * For hash partitioning, callers must ensure that 'prefix' contains at least
+ * one clause for each of the partition keys apart from the final key. A bit
+ * set in step_nullkeys can substitute clauses in the 'prefix' list for any
+ * given key. Both may not be specified.
+ *
+ * For each of the above cases, callers must ensure that PartClauseInfos in
+ * 'prefix' are sorted in ascending order of keyno.
*/
static List *
get_steps_using_prefix(GeneratePruningStepsContext *context,
@@ -2376,7 +2373,6 @@ get_steps_using_prefix(GeneratePruningStepsContext
*context,
bool step_op_is_ne,
Expr *step_lastexpr,
Oid step_lastcmpfn,
- int step_lastkeyno,
Bitmapset *step_nullkeys,
List *prefix)
{
@@ -2397,13 +2393,12 @@ get_steps_using_prefix(GeneratePruningStepsContext
*context,
return list_make1(step);
}
- /* Recurse to generate steps for various combinations. */
+ /* Recurse to generate steps for every combination of clauses. */
return get_steps_using_prefix_recurse(context,
step_opstrategy,
step_op_is_ne,
step_lastexpr,
step_lastcmpfn,
-
step_lastkeyno,
step_nullkeys,
prefix,
list_head(prefix),
@@ -2413,9 +2408,8 @@ get_steps_using_prefix(GeneratePruningStepsContext
*context,
/*
* get_steps_using_prefix_recurse
* Recursively generate combinations of clauses for different
partition
- * keys and start generating steps upon reaching clauses for the
greatest
- * column that is less than the one for which we're currently
generating
- * steps (that is, step_lastkeyno)
+ * keys and generate PartitionPruneSteps for each combination of
+ * PartClauseInfos in the 'prefix' list.
*
* 'prefix' is the list of PartClauseInfos.
* 'start' is where we should start iterating for the current invocation.
@@ -2428,7 +2422,6 @@
get_steps_using_prefix_recurse(GeneratePruningStepsContext *context,
bool step_op_is_ne,
Expr *step_lastexpr,
Oid step_lastcmpfn,
- int step_lastkeyno,
Bitmapset
*step_nullkeys,
List *prefix,
ListCell *start,
@@ -2438,14 +2431,17 @@
get_steps_using_prefix_recurse(GeneratePruningStepsContext *context,
List *result = NIL;
ListCell *lc;
int cur_keyno;
+ int final_keyno;
/* Actually, recursion would be limited by PARTITION_MAX_KEYS. */
check_stack_depth();
- /* Check if we need to recurse. */
Assert(start != NULL);
cur_keyno = ((PartClauseInfo *) lfirst(start))->keyno;
- if (cur_keyno < step_lastkeyno - 1)
+ final_keyno = ((PartClauseInfo *) llast(prefix))->keyno;
+
+ /* Check if we need to recurse. */
+ if (cur_keyno < final_keyno)
{
PartClauseInfo *pc;
ListCell *next_start;
@@ -2493,7 +2489,6 @@
get_steps_using_prefix_recurse(GeneratePruningStepsContext *context,
step_op_is_ne,
step_lastexpr,
step_lastcmpfn,
-
step_lastkeyno,
step_nullkeys,
prefix,
next_start,
diff --git a/src/test/regress/expected/partition_prune.out
b/src/test/regress/expected/partition_prune.out
index 36791293ee..1bfdf37657 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -4024,20 +4024,327 @@ explain (costs off) select * from rp_prefix_test3
where a >= 1 and b >= 1 and b
Filter: ((a >= 1) AND (b >= 1) AND (d >= 0) AND (b = 2) AND (c = 2))
(2 rows)
+drop table rp_prefix_test1;
+drop table rp_prefix_test2;
+drop table rp_prefix_test3;
create table hp_prefix_test (a int, b int, c int, d int) partition by hash (a
part_test_int4_ops, b part_test_int4_ops, c part_test_int4_ops, d
part_test_int4_ops);
-create table hp_prefix_test_p1 partition of hp_prefix_test for values with
(modulus 2, remainder 0);
-create table hp_prefix_test_p2 partition of hp_prefix_test for values with
(modulus 2, remainder 1);
--- Test that get_steps_using_prefix() handles non-NULL step_nullkeys
-explain (costs off) select * from hp_prefix_test where a = 1 and b is null and
c = 1 and d = 1;
+-- create 16 partitions
+select 'create table hp_prefix_test_p' || x::text || ' partition of
hp_prefix_test for values with (modulus 16, remainder ' || x::text || ');'
+from generate_Series(0,15) x;
+ ?column?
+---------------------------------------------------------------------------------------------------------
+ create table hp_prefix_test_p0 partition of hp_prefix_test for values with
(modulus 16, remainder 0);
+ create table hp_prefix_test_p1 partition of hp_prefix_test for values with
(modulus 16, remainder 1);
+ create table hp_prefix_test_p2 partition of hp_prefix_test for values with
(modulus 16, remainder 2);
+ create table hp_prefix_test_p3 partition of hp_prefix_test for values with
(modulus 16, remainder 3);
+ create table hp_prefix_test_p4 partition of hp_prefix_test for values with
(modulus 16, remainder 4);
+ create table hp_prefix_test_p5 partition of hp_prefix_test for values with
(modulus 16, remainder 5);
+ create table hp_prefix_test_p6 partition of hp_prefix_test for values with
(modulus 16, remainder 6);
+ create table hp_prefix_test_p7 partition of hp_prefix_test for values with
(modulus 16, remainder 7);
+ create table hp_prefix_test_p8 partition of hp_prefix_test for values with
(modulus 16, remainder 8);
+ create table hp_prefix_test_p9 partition of hp_prefix_test for values with
(modulus 16, remainder 9);
+ create table hp_prefix_test_p10 partition of hp_prefix_test for values with
(modulus 16, remainder 10);
+ create table hp_prefix_test_p11 partition of hp_prefix_test for values with
(modulus 16, remainder 11);
+ create table hp_prefix_test_p12 partition of hp_prefix_test for values with
(modulus 16, remainder 12);
+ create table hp_prefix_test_p13 partition of hp_prefix_test for values with
(modulus 16, remainder 13);
+ create table hp_prefix_test_p14 partition of hp_prefix_test for values with
(modulus 16, remainder 14);
+ create table hp_prefix_test_p15 partition of hp_prefix_test for values with
(modulus 16, remainder 15);
+(16 rows)
+
+\gexec
+create table hp_prefix_test_p0 partition of hp_prefix_test for values with
(modulus 16, remainder 0);
+create table hp_prefix_test_p1 partition of hp_prefix_test for values with
(modulus 16, remainder 1);
+create table hp_prefix_test_p2 partition of hp_prefix_test for values with
(modulus 16, remainder 2);
+create table hp_prefix_test_p3 partition of hp_prefix_test for values with
(modulus 16, remainder 3);
+create table hp_prefix_test_p4 partition of hp_prefix_test for values with
(modulus 16, remainder 4);
+create table hp_prefix_test_p5 partition of hp_prefix_test for values with
(modulus 16, remainder 5);
+create table hp_prefix_test_p6 partition of hp_prefix_test for values with
(modulus 16, remainder 6);
+create table hp_prefix_test_p7 partition of hp_prefix_test for values with
(modulus 16, remainder 7);
+create table hp_prefix_test_p8 partition of hp_prefix_test for values with
(modulus 16, remainder 8);
+create table hp_prefix_test_p9 partition of hp_prefix_test for values with
(modulus 16, remainder 9);
+create table hp_prefix_test_p10 partition of hp_prefix_test for values with
(modulus 16, remainder 10);
+create table hp_prefix_test_p11 partition of hp_prefix_test for values with
(modulus 16, remainder 11);
+create table hp_prefix_test_p12 partition of hp_prefix_test for values with
(modulus 16, remainder 12);
+create table hp_prefix_test_p13 partition of hp_prefix_test for values with
(modulus 16, remainder 13);
+create table hp_prefix_test_p14 partition of hp_prefix_test for values with
(modulus 16, remainder 14);
+create table hp_prefix_test_p15 partition of hp_prefix_test for values with
(modulus 16, remainder 15);
+-- insert one row for each test to perform.
+insert into hp_prefix_test
+select
+ case a when 0 then null else 1 end,
+ case b when 0 then null else 2 end,
+ case c when 0 then null else 3 end,
+ case d when 0 then null else 4 end
+from
+ generate_series(0,1) a,
+ generate_series(0,1) b,
+ generate_Series(0,1) c,
+ generate_Series(0,1) d;
+-- Ensure partition pruning works correctly for each combination of IS NULL
+-- and equality quals.
+select
+ 'explain (costs off) select tableoid::regclass,* from hp_prefix_test where '
||
+ string_agg(c.colname || case when g.s & (1 << c.colpos) = 0 then ' is null'
else ' = ' || (colpos+1)::text end, ' and ' order by c.colpos)
+from (values('a',0),('b',1),('c',2),('d',3)) c(colname, colpos),
generate_Series(0,15) g(s)
+group by g.s
+order by g.s;
+ ?column?
+-------------------------------------------------------------------------------------------------------------------------------
+ explain (costs off) select tableoid::regclass,* from hp_prefix_test where a
is null and b is null and c is null and d is null
+ explain (costs off) select tableoid::regclass,* from hp_prefix_test where a =
1 and b is null and c is null and d is null
+ explain (costs off) select tableoid::regclass,* from hp_prefix_test where a
is null and b = 2 and c is null and d is null
+ explain (costs off) select tableoid::regclass,* from hp_prefix_test where a =
1 and b = 2 and c is null and d is null
+ explain (costs off) select tableoid::regclass,* from hp_prefix_test where a
is null and b is null and c = 3 and d is null
+ explain (costs off) select tableoid::regclass,* from hp_prefix_test where a =
1 and b is null and c = 3 and d is null
+ explain (costs off) select tableoid::regclass,* from hp_prefix_test where a
is null and b = 2 and c = 3 and d is null
+ explain (costs off) select tableoid::regclass,* from hp_prefix_test where a =
1 and b = 2 and c = 3 and d is null
+ explain (costs off) select tableoid::regclass,* from hp_prefix_test where a
is null and b is null and c is null and d = 4
+ explain (costs off) select tableoid::regclass,* from hp_prefix_test where a =
1 and b is null and c is null and d = 4
+ explain (costs off) select tableoid::regclass,* from hp_prefix_test where a
is null and b = 2 and c is null and d = 4
+ explain (costs off) select tableoid::regclass,* from hp_prefix_test where a =
1 and b = 2 and c is null and d = 4
+ explain (costs off) select tableoid::regclass,* from hp_prefix_test where a
is null and b is null and c = 3 and d = 4
+ explain (costs off) select tableoid::regclass,* from hp_prefix_test where a =
1 and b is null and c = 3 and d = 4
+ explain (costs off) select tableoid::regclass,* from hp_prefix_test where a
is null and b = 2 and c = 3 and d = 4
+ explain (costs off) select tableoid::regclass,* from hp_prefix_test where a =
1 and b = 2 and c = 3 and d = 4
+(16 rows)
+
+\gexec
+explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is
null and b is null and c is null and d is null
+ QUERY PLAN
+-------------------------------------------------------------------------
+ Seq Scan on hp_prefix_test_p0 hp_prefix_test
+ Filter: ((a IS NULL) AND (b IS NULL) AND (c IS NULL) AND (d IS NULL))
+(2 rows)
+
+explain (costs off) select tableoid::regclass,* from hp_prefix_test where a =
1 and b is null and c is null and d is null
+ QUERY PLAN
+---------------------------------------------------------------------
+ Seq Scan on hp_prefix_test_p1 hp_prefix_test
+ Filter: ((b IS NULL) AND (c IS NULL) AND (d IS NULL) AND (a = 1))
+(2 rows)
+
+explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is
null and b = 2 and c is null and d is null
+ QUERY PLAN
+---------------------------------------------------------------------
+ Seq Scan on hp_prefix_test_p2 hp_prefix_test
+ Filter: ((a IS NULL) AND (c IS NULL) AND (d IS NULL) AND (b = 2))
+(2 rows)
+
+explain (costs off) select tableoid::regclass,* from hp_prefix_test where a =
1 and b = 2 and c is null and d is null
+ QUERY PLAN
+-----------------------------------------------------------------
+ Seq Scan on hp_prefix_test_p12 hp_prefix_test
+ Filter: ((c IS NULL) AND (d IS NULL) AND (a = 1) AND (b = 2))
+(2 rows)
+
+explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is
null and b is null and c = 3 and d is null
+ QUERY PLAN
+---------------------------------------------------------------------
+ Seq Scan on hp_prefix_test_p3 hp_prefix_test
+ Filter: ((a IS NULL) AND (b IS NULL) AND (d IS NULL) AND (c = 3))
+(2 rows)
+
+explain (costs off) select tableoid::regclass,* from hp_prefix_test where a =
1 and b is null and c = 3 and d is null
+ QUERY PLAN
+-----------------------------------------------------------------
+ Seq Scan on hp_prefix_test_p15 hp_prefix_test
+ Filter: ((b IS NULL) AND (d IS NULL) AND (a = 1) AND (c = 3))
+(2 rows)
+
+explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is
null and b = 2 and c = 3 and d is null
+ QUERY PLAN
+-----------------------------------------------------------------
+ Seq Scan on hp_prefix_test_p12 hp_prefix_test
+ Filter: ((a IS NULL) AND (d IS NULL) AND (b = 2) AND (c = 3))
+(2 rows)
+
+explain (costs off) select tableoid::regclass,* from hp_prefix_test where a =
1 and b = 2 and c = 3 and d is null
QUERY PLAN
-------------------------------------------------------------
- Seq Scan on hp_prefix_test_p1 hp_prefix_test
- Filter: ((b IS NULL) AND (a = 1) AND (c = 1) AND (d = 1))
+ Seq Scan on hp_prefix_test_p5 hp_prefix_test
+ Filter: ((d IS NULL) AND (a = 1) AND (b = 2) AND (c = 3))
(2 rows)
-drop table rp_prefix_test1;
-drop table rp_prefix_test2;
-drop table rp_prefix_test3;
+explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is
null and b is null and c is null and d = 4
+ QUERY PLAN
+---------------------------------------------------------------------
+ Seq Scan on hp_prefix_test_p4 hp_prefix_test
+ Filter: ((a IS NULL) AND (b IS NULL) AND (c IS NULL) AND (d = 4))
+(2 rows)
+
+explain (costs off) select tableoid::regclass,* from hp_prefix_test where a =
1 and b is null and c is null and d = 4
+ QUERY PLAN
+-----------------------------------------------------------------
+ Seq Scan on hp_prefix_test_p14 hp_prefix_test
+ Filter: ((b IS NULL) AND (c IS NULL) AND (a = 1) AND (d = 4))
+(2 rows)
+
+explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is
null and b = 2 and c is null and d = 4
+ QUERY PLAN
+-----------------------------------------------------------------
+ Seq Scan on hp_prefix_test_p13 hp_prefix_test
+ Filter: ((a IS NULL) AND (c IS NULL) AND (b = 2) AND (d = 4))
+(2 rows)
+
+explain (costs off) select tableoid::regclass,* from hp_prefix_test where a =
1 and b = 2 and c is null and d = 4
+ QUERY PLAN
+-------------------------------------------------------------
+ Seq Scan on hp_prefix_test_p6 hp_prefix_test
+ Filter: ((c IS NULL) AND (a = 1) AND (b = 2) AND (d = 4))
+(2 rows)
+
+explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is
null and b is null and c = 3 and d = 4
+ QUERY PLAN
+-----------------------------------------------------------------
+ Seq Scan on hp_prefix_test_p12 hp_prefix_test
+ Filter: ((a IS NULL) AND (b IS NULL) AND (c = 3) AND (d = 4))
+(2 rows)
+
+explain (costs off) select tableoid::regclass,* from hp_prefix_test where a =
1 and b is null and c = 3 and d = 4
+ QUERY PLAN
+-------------------------------------------------------------
+ Seq Scan on hp_prefix_test_p5 hp_prefix_test
+ Filter: ((b IS NULL) AND (a = 1) AND (c = 3) AND (d = 4))
+(2 rows)
+
+explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is
null and b = 2 and c = 3 and d = 4
+ QUERY PLAN
+-------------------------------------------------------------
+ Seq Scan on hp_prefix_test_p6 hp_prefix_test
+ Filter: ((a IS NULL) AND (b = 2) AND (c = 3) AND (d = 4))
+(2 rows)
+
+explain (costs off) select tableoid::regclass,* from hp_prefix_test where a =
1 and b = 2 and c = 3 and d = 4
+ QUERY PLAN
+---------------------------------------------------------
+ Seq Scan on hp_prefix_test_p4 hp_prefix_test
+ Filter: ((a = 1) AND (b = 2) AND (c = 3) AND (d = 4))
+(2 rows)
+
+-- And ensure we get exactly 1 row from each.
+select
+ 'select tableoid::regclass,* from hp_prefix_test where ' ||
+ string_agg(c.colname || case when g.s & (1 << c.colpos) = 0 then ' is null'
else ' = ' || (colpos+1)::text end, ' and ' order by c.colpos)
+from (values('a',0),('b',1),('c',2),('d',3)) c(colname, colpos),
generate_Series(0,15) g(s)
+group by g.s
+order by g.s;
+ ?column?
+-----------------------------------------------------------------------------------------------------------
+ select tableoid::regclass,* from hp_prefix_test where a is null and b is null
and c is null and d is null
+ select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and
c is null and d is null
+ select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and
c is null and d is null
+ select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c
is null and d is null
+ select tableoid::regclass,* from hp_prefix_test where a is null and b is null
and c = 3 and d is null
+ select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and
c = 3 and d is null
+ select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and
c = 3 and d is null
+ select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c =
3 and d is null
+ select tableoid::regclass,* from hp_prefix_test where a is null and b is null
and c is null and d = 4
+ select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and
c is null and d = 4
+ select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and
c is null and d = 4
+ select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c
is null and d = 4
+ select tableoid::regclass,* from hp_prefix_test where a is null and b is null
and c = 3 and d = 4
+ select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and
c = 3 and d = 4
+ select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and
c = 3 and d = 4
+ select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c =
3 and d = 4
+(16 rows)
+
+\gexec
+select tableoid::regclass,* from hp_prefix_test where a is null and b is null
and c is null and d is null
+ tableoid | a | b | c | d
+-------------------+---+---+---+---
+ hp_prefix_test_p0 | | | |
+(1 row)
+
+select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and
c is null and d is null
+ tableoid | a | b | c | d
+-------------------+---+---+---+---
+ hp_prefix_test_p1 | 1 | | |
+(1 row)
+
+select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and
c is null and d is null
+ tableoid | a | b | c | d
+-------------------+---+---+---+---
+ hp_prefix_test_p2 | | 2 | |
+(1 row)
+
+select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c is
null and d is null
+ tableoid | a | b | c | d
+--------------------+---+---+---+---
+ hp_prefix_test_p12 | 1 | 2 | |
+(1 row)
+
+select tableoid::regclass,* from hp_prefix_test where a is null and b is null
and c = 3 and d is null
+ tableoid | a | b | c | d
+-------------------+---+---+---+---
+ hp_prefix_test_p3 | | | 3 |
+(1 row)
+
+select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and
c = 3 and d is null
+ tableoid | a | b | c | d
+--------------------+---+---+---+---
+ hp_prefix_test_p15 | 1 | | 3 |
+(1 row)
+
+select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and
c = 3 and d is null
+ tableoid | a | b | c | d
+--------------------+---+---+---+---
+ hp_prefix_test_p12 | | 2 | 3 |
+(1 row)
+
+select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c =
3 and d is null
+ tableoid | a | b | c | d
+-------------------+---+---+---+---
+ hp_prefix_test_p5 | 1 | 2 | 3 |
+(1 row)
+
+select tableoid::regclass,* from hp_prefix_test where a is null and b is null
and c is null and d = 4
+ tableoid | a | b | c | d
+-------------------+---+---+---+---
+ hp_prefix_test_p4 | | | | 4
+(1 row)
+
+select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and
c is null and d = 4
+ tableoid | a | b | c | d
+--------------------+---+---+---+---
+ hp_prefix_test_p14 | 1 | | | 4
+(1 row)
+
+select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and
c is null and d = 4
+ tableoid | a | b | c | d
+--------------------+---+---+---+---
+ hp_prefix_test_p13 | | 2 | | 4
+(1 row)
+
+select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c is
null and d = 4
+ tableoid | a | b | c | d
+-------------------+---+---+---+---
+ hp_prefix_test_p6 | 1 | 2 | | 4
+(1 row)
+
+select tableoid::regclass,* from hp_prefix_test where a is null and b is null
and c = 3 and d = 4
+ tableoid | a | b | c | d
+--------------------+---+---+---+---
+ hp_prefix_test_p12 | | | 3 | 4
+(1 row)
+
+select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and
c = 3 and d = 4
+ tableoid | a | b | c | d
+-------------------+---+---+---+---
+ hp_prefix_test_p5 | 1 | | 3 | 4
+(1 row)
+
+select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and
c = 3 and d = 4
+ tableoid | a | b | c | d
+-------------------+---+---+---+---
+ hp_prefix_test_p6 | | 2 | 3 | 4
+(1 row)
+
+select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c =
3 and d = 4
+ tableoid | a | b | c | d
+-------------------+---+---+---+---
+ hp_prefix_test_p4 | 1 | 2 | 3 | 4
+(1 row)
+
drop table hp_prefix_test;
--
-- Check that gen_partprune_steps() detects self-contradiction from clauses
diff --git a/src/test/regress/sql/partition_prune.sql
b/src/test/regress/sql/partition_prune.sql
index d23133fe43..6b4039179f 100644
--- a/src/test/regress/sql/partition_prune.sql
+++ b/src/test/regress/sql/partition_prune.sql
@@ -1188,16 +1188,49 @@ explain (costs off) select * from rp_prefix_test3 where
a >= 1 and b >= 1 and b
-- that the caller arranges clauses in that prefix in the required order)
explain (costs off) select * from rp_prefix_test3 where a >= 1 and b >= 1 and
b = 2 and c = 2 and d >= 0;
-create table hp_prefix_test (a int, b int, c int, d int) partition by hash (a
part_test_int4_ops, b part_test_int4_ops, c part_test_int4_ops, d
part_test_int4_ops);
-create table hp_prefix_test_p1 partition of hp_prefix_test for values with
(modulus 2, remainder 0);
-create table hp_prefix_test_p2 partition of hp_prefix_test for values with
(modulus 2, remainder 1);
-
--- Test that get_steps_using_prefix() handles non-NULL step_nullkeys
-explain (costs off) select * from hp_prefix_test where a = 1 and b is null and
c = 1 and d = 1;
-
drop table rp_prefix_test1;
drop table rp_prefix_test2;
drop table rp_prefix_test3;
+
+create table hp_prefix_test (a int, b int, c int, d int) partition by hash (a
part_test_int4_ops, b part_test_int4_ops, c part_test_int4_ops, d
part_test_int4_ops);
+
+-- create 16 partitions
+select 'create table hp_prefix_test_p' || x::text || ' partition of
hp_prefix_test for values with (modulus 16, remainder ' || x::text || ');'
+from generate_Series(0,15) x;
+\gexec
+
+-- insert one row for each test to perform.
+insert into hp_prefix_test
+select
+ case a when 0 then null else 1 end,
+ case b when 0 then null else 2 end,
+ case c when 0 then null else 3 end,
+ case d when 0 then null else 4 end
+from
+ generate_series(0,1) a,
+ generate_series(0,1) b,
+ generate_Series(0,1) c,
+ generate_Series(0,1) d;
+
+-- Ensure partition pruning works correctly for each combination of IS NULL
+-- and equality quals.
+select
+ 'explain (costs off) select tableoid::regclass,* from hp_prefix_test where '
||
+ string_agg(c.colname || case when g.s & (1 << c.colpos) = 0 then ' is null'
else ' = ' || (colpos+1)::text end, ' and ' order by c.colpos)
+from (values('a',0),('b',1),('c',2),('d',3)) c(colname, colpos),
generate_Series(0,15) g(s)
+group by g.s
+order by g.s;
+\gexec
+
+-- And ensure we get exactly 1 row from each.
+select
+ 'select tableoid::regclass,* from hp_prefix_test where ' ||
+ string_agg(c.colname || case when g.s & (1 << c.colpos) = 0 then ' is null'
else ' = ' || (colpos+1)::text end, ' and ' order by c.colpos)
+from (values('a',0),('b',1),('c',2),('d',3)) c(colname, colpos),
generate_Series(0,15) g(s)
+group by g.s
+order by g.s;
+\gexec
+
drop table hp_prefix_test;
--
