Re: inconsistent results querying table partitioned by date - Mailing list pgsql-bugs

I wrote:
> So, the changes in gen_prune_steps_from_opexps seem okay, but
> the change in perform_pruning_base_step is just wrong,

I went ahead and pushed the non-controversial part of that patch,
just to avoid carrying it around longer.

I propose that we use the attached test cases once we have a fix
for the remaining problem.  The results shown are the wrong answers
that HEAD produces.  Amit's patch fixes the first case but not the
second, since substituting equality semantics is the right thing
to do in only the first case.

            regards, tom lane

diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index 723fc70..88701d8 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -3149,6 +3149,43 @@ select * from mc3p where a < 3 and abs(b) = 1;
          Filter: ((a < 3) AND (abs(b) = 1))
 (7 rows)

+--
+-- Check that pruning with composite range partitioning works correctly when
+-- a combination of runtime parameters is specified, not all of whose values
+-- are available at the same time
+--
+set plan_cache_mode = force_generic_plan;
+prepare ps1 as
+  select * from mc3p where a = $1 and abs(b) < (select 3);
+explain (analyze, costs off, summary off, timing off)
+execute ps1(1);
+                  QUERY PLAN
+----------------------------------------------
+ Append (actual rows=0 loops=1)
+   InitPlan 1 (returns $0)
+     ->  Result (never executed)
+   Subplans Removed: 2
+   ->  Seq Scan on mc3p0 (never executed)
+         Filter: ((a = $1) AND (abs(b) < $0))
+(6 rows)
+
+deallocate ps1;
+prepare ps2 as
+  select * from mc3p where a <= $1 and abs(b) < (select 3);
+explain (analyze, costs off, summary off, timing off)
+execute ps2(1);
+                   QUERY PLAN
+-------------------------------------------------
+ Append (actual rows=1 loops=1)
+   InitPlan 1 (returns $0)
+     ->  Result (actual rows=1 loops=1)
+   Subplans Removed: 2
+   ->  Seq Scan on mc3p0 (actual rows=1 loops=1)
+         Filter: ((a <= $1) AND (abs(b) < $0))
+(6 rows)
+
+deallocate ps2;
+reset plan_cache_mode;
 drop table mc3p;
 -- Ensure runtime pruning works with initplans params with boolean types
 create table boolvalues (value bool not null);
diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql
index 2373bd8..071e28d 100644
--- a/src/test/regress/sql/partition_prune.sql
+++ b/src/test/regress/sql/partition_prune.sql
@@ -809,6 +809,24 @@ insert into mc3p values (0, 1, 1), (1, 1, 1), (2, 1, 1);
 explain (analyze, costs off, summary off, timing off)
 select * from mc3p where a < 3 and abs(b) = 1;

+--
+-- Check that pruning with composite range partitioning works correctly when
+-- a combination of runtime parameters is specified, not all of whose values
+-- are available at the same time
+--
+set plan_cache_mode = force_generic_plan;
+prepare ps1 as
+  select * from mc3p where a = $1 and abs(b) < (select 3);
+explain (analyze, costs off, summary off, timing off)
+execute ps1(1);
+deallocate ps1;
+prepare ps2 as
+  select * from mc3p where a <= $1 and abs(b) < (select 3);
+explain (analyze, costs off, summary off, timing off)
+execute ps2(1);
+deallocate ps2;
+reset plan_cache_mode;
+
 drop table mc3p;

 -- Ensure runtime pruning works with initplans params with boolean types