-- pg_orca static partition pruning regression tests
-- Ported from testrepo/partitioning/partitionselect/sql/
-- Tests ORCA static partition pruning correctness across multiple column types
-- using compound OR predicates on the partition key.
LOAD 'pg_orca';
SET pg_orca.enable_orca = on;
SET client_min_messages = warning;
SET enable_mergejoin TO off;
-- ===================================================================
-- INT4 partition pruning (expected: 2 of 9 partitions selected)
-- ===================================================================
CREATE TABLE INT4_TBL (f1 int4, f2 int4) PARTITION BY RANGE (f1);
CREATE TABLE INT4_TBL_p1 PARTITION OF INT4_TBL FOR VALUES FROM (1) TO (2);
CREATE TABLE INT4_TBL_p2 PARTITION OF INT4_TBL FOR VALUES FROM (2) TO (3);
CREATE TABLE INT4_TBL_p3 PARTITION OF INT4_TBL FOR VALUES FROM (3) TO (4);
CREATE TABLE INT4_TBL_p4 PARTITION OF INT4_TBL FOR VALUES FROM (4) TO (5);
CREATE TABLE INT4_TBL_p5 PARTITION OF INT4_TBL FOR VALUES FROM (5) TO (6);
CREATE TABLE INT4_TBL_p6 PARTITION OF INT4_TBL FOR VALUES FROM (6) TO (7);
CREATE TABLE INT4_TBL_p7 PARTITION OF INT4_TBL FOR VALUES FROM (7) TO (8);
CREATE TABLE INT4_TBL_p8 PARTITION OF INT4_TBL FOR VALUES FROM (8) TO (9);
CREATE TABLE INT4_TBL_p9 PARTITION OF INT4_TBL FOR VALUES FROM (9) TO (10);
-- Expected: 2 partitions selected (p1: [1,2) and p5: [5,6))
EXPLAIN (costs off) SELECT * FROM INT4_TBL
WHERE
(f1>=1 AND f1<2 AND f2<3)
OR
(f1>=5 AND f1<6 AND f2<1);
                                         QUERY PLAN                                         
--------------------------------------------------------------------------------------------
 Custom Scan (DynamicTableScan)
   Filter: (((f1 >= 1) AND (f1 < 2) AND (f2 < 3)) OR ((f1 >= 5) AND (f1 < 6) AND (f2 < 1)))
   Root Table: int4_tbl
   Partitions Selected: 2
 Optimizer: pg_orca
(5 rows)

DROP TABLE INT4_TBL;
-- ===================================================================
-- INT4 out-of-range partition pruning (expected: 0 of 9 partitions selected)
-- ===================================================================
CREATE TABLE INT4_TBL (f1 int4, f2 int4) PARTITION BY RANGE (f1);
CREATE TABLE INT4_TBL_p1 PARTITION OF INT4_TBL FOR VALUES FROM (1) TO (2);
CREATE TABLE INT4_TBL_p2 PARTITION OF INT4_TBL FOR VALUES FROM (2) TO (3);
CREATE TABLE INT4_TBL_p3 PARTITION OF INT4_TBL FOR VALUES FROM (3) TO (4);
CREATE TABLE INT4_TBL_p4 PARTITION OF INT4_TBL FOR VALUES FROM (4) TO (5);
CREATE TABLE INT4_TBL_p5 PARTITION OF INT4_TBL FOR VALUES FROM (5) TO (6);
CREATE TABLE INT4_TBL_p6 PARTITION OF INT4_TBL FOR VALUES FROM (6) TO (7);
CREATE TABLE INT4_TBL_p7 PARTITION OF INT4_TBL FOR VALUES FROM (7) TO (8);
CREATE TABLE INT4_TBL_p8 PARTITION OF INT4_TBL FOR VALUES FROM (8) TO (9);
CREATE TABLE INT4_TBL_p9 PARTITION OF INT4_TBL FOR VALUES FROM (9) TO (10);
-- Expected: 0 partitions selected (predicate entirely outside range [1,10))
EXPLAIN (costs off) SELECT * FROM INT4_TBL
WHERE
(f1>=10 AND f1<14 AND f2<3)
OR
(f1>=15 AND f1<16 AND f2<3);
           QUERY PLAN           
--------------------------------
 Result
   ->  Result
         One-Time Filter: false
 Optimizer: pg_orca
(4 rows)

DROP TABLE INT4_TBL;
-- ===================================================================
-- INT4 within-range partition pruning (expected: 4 of 9 partitions selected)
-- ===================================================================
CREATE TABLE INT4_TBL (f1 int4, f2 int4) PARTITION BY RANGE (f1);
CREATE TABLE INT4_TBL_p1 PARTITION OF INT4_TBL FOR VALUES FROM (1) TO (2);
CREATE TABLE INT4_TBL_p2 PARTITION OF INT4_TBL FOR VALUES FROM (2) TO (3);
CREATE TABLE INT4_TBL_p3 PARTITION OF INT4_TBL FOR VALUES FROM (3) TO (4);
CREATE TABLE INT4_TBL_p4 PARTITION OF INT4_TBL FOR VALUES FROM (4) TO (5);
CREATE TABLE INT4_TBL_p5 PARTITION OF INT4_TBL FOR VALUES FROM (5) TO (6);
CREATE TABLE INT4_TBL_p6 PARTITION OF INT4_TBL FOR VALUES FROM (6) TO (7);
CREATE TABLE INT4_TBL_p7 PARTITION OF INT4_TBL FOR VALUES FROM (7) TO (8);
CREATE TABLE INT4_TBL_p8 PARTITION OF INT4_TBL FOR VALUES FROM (8) TO (9);
CREATE TABLE INT4_TBL_p9 PARTITION OF INT4_TBL FOR VALUES FROM (9) TO (10);
-- Expected: 4 partitions selected (p1,p2,p3: [1,4) and p5: [5,6))
EXPLAIN (costs off) SELECT * FROM INT4_TBL
WHERE
(f1>=1 AND f1<4 AND f2<3)
OR
(f1>=5 AND f1<6 AND f2<3);
                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 Custom Scan (DynamicTableScan)
   Filter: ((f2 < 3) AND (((f1 >= 1) AND (f1 < 4)) OR ((f1 >= 5) AND (f1 < 6))))
   Root Table: int4_tbl
   Partitions Selected: 4
 Optimizer: pg_orca
(5 rows)

DROP TABLE INT4_TBL;
-- ===================================================================
-- FLOAT8 partition pruning (expected: 2 of 9 partitions selected)
-- ===================================================================
CREATE TABLE FLOAT8_TBL (f2 float8, f1 float8) PARTITION BY RANGE (f1);
CREATE TABLE FLOAT8_TBL_p1 PARTITION OF FLOAT8_TBL FOR VALUES FROM (1) TO (2);
CREATE TABLE FLOAT8_TBL_p2 PARTITION OF FLOAT8_TBL FOR VALUES FROM (2) TO (3);
CREATE TABLE FLOAT8_TBL_p3 PARTITION OF FLOAT8_TBL FOR VALUES FROM (3) TO (4);
CREATE TABLE FLOAT8_TBL_p4 PARTITION OF FLOAT8_TBL FOR VALUES FROM (4) TO (5);
CREATE TABLE FLOAT8_TBL_p5 PARTITION OF FLOAT8_TBL FOR VALUES FROM (5) TO (6);
CREATE TABLE FLOAT8_TBL_p6 PARTITION OF FLOAT8_TBL FOR VALUES FROM (6) TO (7);
CREATE TABLE FLOAT8_TBL_p7 PARTITION OF FLOAT8_TBL FOR VALUES FROM (7) TO (8);
CREATE TABLE FLOAT8_TBL_p8 PARTITION OF FLOAT8_TBL FOR VALUES FROM (8) TO (9);
CREATE TABLE FLOAT8_TBL_p9 PARTITION OF FLOAT8_TBL FOR VALUES FROM (9) TO (10);
-- Expected: 2 partitions selected (p1: [1,2) and p5: [5,6))
EXPLAIN (costs off) SELECT * FROM FLOAT8_TBL
WHERE
(f1>=1 AND f1<2 AND f2<3)
OR
(f1>=5 AND f1<6 AND f2<3);
                                                                                     QUERY PLAN                                                                                      
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Custom Scan (DynamicTableScan)
   Filter: ((f2 < '3'::double precision) AND (((f1 >= '1'::double precision) AND (f1 < '2'::double precision)) OR ((f1 >= '5'::double precision) AND (f1 < '6'::double precision))))
   Root Table: float8_tbl
   Partitions Selected: 2
 Optimizer: pg_orca
(5 rows)

DROP TABLE FLOAT8_TBL;
-- ===================================================================
-- NUMERIC partition pruning (expected: 2 of 9 partitions selected)
-- ===================================================================
CREATE TABLE num_data_big_rangep (f2 int4, f1 numeric(1000,800)) PARTITION BY RANGE (f1);
CREATE TABLE num_data_big_rangep_p1 PARTITION OF num_data_big_rangep FOR VALUES FROM (1) TO (2);
CREATE TABLE num_data_big_rangep_p2 PARTITION OF num_data_big_rangep FOR VALUES FROM (2) TO (3);
CREATE TABLE num_data_big_rangep_p3 PARTITION OF num_data_big_rangep FOR VALUES FROM (3) TO (4);
CREATE TABLE num_data_big_rangep_p4 PARTITION OF num_data_big_rangep FOR VALUES FROM (4) TO (5);
CREATE TABLE num_data_big_rangep_p5 PARTITION OF num_data_big_rangep FOR VALUES FROM (5) TO (6);
CREATE TABLE num_data_big_rangep_p6 PARTITION OF num_data_big_rangep FOR VALUES FROM (6) TO (7);
CREATE TABLE num_data_big_rangep_p7 PARTITION OF num_data_big_rangep FOR VALUES FROM (7) TO (8);
CREATE TABLE num_data_big_rangep_p8 PARTITION OF num_data_big_rangep FOR VALUES FROM (8) TO (9);
CREATE TABLE num_data_big_rangep_p9 PARTITION OF num_data_big_rangep FOR VALUES FROM (9) TO (10);
-- Expected: 2 partitions selected (p1: [1,2) and p5: [5,6))
EXPLAIN (costs off) SELECT * FROM num_data_big_rangep
WHERE
(f1>=1 AND f1<2 AND f2<3)
OR
(f1>=5 AND f1<6 AND f2<3);
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Custom Scan (DynamicTableScan)
   Filter: ((f2 < 3) AND (((f1 >= '1'::numeric) AND (f1 < '2'::numeric)) OR ((f1 >= '5'::numeric) AND (f1 < '6'::numeric))))
   Root Table: num_data_big_rangep
   Partitions Selected: 2
 Optimizer: pg_orca
(5 rows)

DROP TABLE num_data_big_rangep;
-- ===================================================================
-- DATE partition pruning (expected: 2 of 12 monthly partitions selected)
-- ===================================================================
CREATE TABLE DATE_TBL_MONTH_rangep (f1 date, f2 date) PARTITION BY RANGE (f1);
CREATE TABLE DATE_TBL_MONTH_rangep_p01 PARTITION OF DATE_TBL_MONTH_rangep FOR VALUES FROM ('2000-01-01') TO ('2000-02-01');
CREATE TABLE DATE_TBL_MONTH_rangep_p02 PARTITION OF DATE_TBL_MONTH_rangep FOR VALUES FROM ('2000-02-01') TO ('2000-03-01');
CREATE TABLE DATE_TBL_MONTH_rangep_p03 PARTITION OF DATE_TBL_MONTH_rangep FOR VALUES FROM ('2000-03-01') TO ('2000-04-01');
CREATE TABLE DATE_TBL_MONTH_rangep_p04 PARTITION OF DATE_TBL_MONTH_rangep FOR VALUES FROM ('2000-04-01') TO ('2000-05-01');
CREATE TABLE DATE_TBL_MONTH_rangep_p05 PARTITION OF DATE_TBL_MONTH_rangep FOR VALUES FROM ('2000-05-01') TO ('2000-06-01');
CREATE TABLE DATE_TBL_MONTH_rangep_p06 PARTITION OF DATE_TBL_MONTH_rangep FOR VALUES FROM ('2000-06-01') TO ('2000-07-01');
CREATE TABLE DATE_TBL_MONTH_rangep_p07 PARTITION OF DATE_TBL_MONTH_rangep FOR VALUES FROM ('2000-07-01') TO ('2000-08-01');
CREATE TABLE DATE_TBL_MONTH_rangep_p08 PARTITION OF DATE_TBL_MONTH_rangep FOR VALUES FROM ('2000-08-01') TO ('2000-09-01');
CREATE TABLE DATE_TBL_MONTH_rangep_p09 PARTITION OF DATE_TBL_MONTH_rangep FOR VALUES FROM ('2000-09-01') TO ('2000-10-01');
CREATE TABLE DATE_TBL_MONTH_rangep_p10 PARTITION OF DATE_TBL_MONTH_rangep FOR VALUES FROM ('2000-10-01') TO ('2000-11-01');
CREATE TABLE DATE_TBL_MONTH_rangep_p11 PARTITION OF DATE_TBL_MONTH_rangep FOR VALUES FROM ('2000-11-01') TO ('2000-12-01');
CREATE TABLE DATE_TBL_MONTH_rangep_p12 PARTITION OF DATE_TBL_MONTH_rangep FOR VALUES FROM ('2000-12-01') TO ('2001-01-01');
-- Expected: 2 partitions selected (p01: Jan 2000 and p03: Mar 2000)
EXPLAIN (costs off) SELECT * FROM DATE_TBL_MONTH_rangep
WHERE
(f1>='2000-01-01' AND f1<'2000-02-01' AND f2<'2000-02-01')
OR
(f1>='2000-03-01' AND f1<'2000-04-01' AND f2>='2000-01-01');
                                                                                            QUERY PLAN                                                                                             
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Custom Scan (DynamicTableScan)
   Filter: (((f1 >= '01-01-2000'::date) AND (f1 < '02-01-2000'::date) AND (f2 < '02-01-2000'::date)) OR ((f1 >= '03-01-2000'::date) AND (f1 < '04-01-2000'::date) AND (f2 >= '01-01-2000'::date)))
   Root Table: date_tbl_month_rangep
   Partitions Selected: 2
 Optimizer: pg_orca
(5 rows)

DROP TABLE DATE_TBL_MONTH_rangep;
-- ===================================================================
-- TIMESTAMP partition pruning (expected: 2 of 12 monthly partitions selected)
-- ===================================================================
CREATE TABLE TIMESTAMP_TBL_MONTH_rangep (f1 timestamp, f2 timestamp) PARTITION BY RANGE (f1);
CREATE TABLE TIMESTAMP_TBL_MONTH_rangep_p01 PARTITION OF TIMESTAMP_TBL_MONTH_rangep FOR VALUES FROM ('2000-01-01') TO ('2000-02-01');
CREATE TABLE TIMESTAMP_TBL_MONTH_rangep_p02 PARTITION OF TIMESTAMP_TBL_MONTH_rangep FOR VALUES FROM ('2000-02-01') TO ('2000-03-01');
CREATE TABLE TIMESTAMP_TBL_MONTH_rangep_p03 PARTITION OF TIMESTAMP_TBL_MONTH_rangep FOR VALUES FROM ('2000-03-01') TO ('2000-04-01');
CREATE TABLE TIMESTAMP_TBL_MONTH_rangep_p04 PARTITION OF TIMESTAMP_TBL_MONTH_rangep FOR VALUES FROM ('2000-04-01') TO ('2000-05-01');
CREATE TABLE TIMESTAMP_TBL_MONTH_rangep_p05 PARTITION OF TIMESTAMP_TBL_MONTH_rangep FOR VALUES FROM ('2000-05-01') TO ('2000-06-01');
CREATE TABLE TIMESTAMP_TBL_MONTH_rangep_p06 PARTITION OF TIMESTAMP_TBL_MONTH_rangep FOR VALUES FROM ('2000-06-01') TO ('2000-07-01');
CREATE TABLE TIMESTAMP_TBL_MONTH_rangep_p07 PARTITION OF TIMESTAMP_TBL_MONTH_rangep FOR VALUES FROM ('2000-07-01') TO ('2000-08-01');
CREATE TABLE TIMESTAMP_TBL_MONTH_rangep_p08 PARTITION OF TIMESTAMP_TBL_MONTH_rangep FOR VALUES FROM ('2000-08-01') TO ('2000-09-01');
CREATE TABLE TIMESTAMP_TBL_MONTH_rangep_p09 PARTITION OF TIMESTAMP_TBL_MONTH_rangep FOR VALUES FROM ('2000-09-01') TO ('2000-10-01');
CREATE TABLE TIMESTAMP_TBL_MONTH_rangep_p10 PARTITION OF TIMESTAMP_TBL_MONTH_rangep FOR VALUES FROM ('2000-10-01') TO ('2000-11-01');
CREATE TABLE TIMESTAMP_TBL_MONTH_rangep_p11 PARTITION OF TIMESTAMP_TBL_MONTH_rangep FOR VALUES FROM ('2000-11-01') TO ('2000-12-01');
CREATE TABLE TIMESTAMP_TBL_MONTH_rangep_p12 PARTITION OF TIMESTAMP_TBL_MONTH_rangep FOR VALUES FROM ('2000-12-01') TO ('2001-01-01');
-- Expected: 2 partitions selected (p01: Jan 2000 and p03: Mar 2000)
EXPLAIN (costs off) SELECT * FROM TIMESTAMP_TBL_MONTH_rangep
WHERE
(f1>='2000-01-01' AND f1<'2000-02-01' AND f2<'2000-02-01')
OR
(f1>='2000-03-01' AND f1<'2000-04-01' AND f2>='2000-01-01');
                                                                                                                                                                                                           QUERY PLAN                                                                                                                                                                                                            
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Custom Scan (DynamicTableScan)
   Filter: (((f1 >= 'Sat Jan 01 00:00:00 2000'::timestamp without time zone) AND (f1 < 'Tue Feb 01 00:00:00 2000'::timestamp without time zone) AND (f2 < 'Tue Feb 01 00:00:00 2000'::timestamp without time zone)) OR ((f1 >= 'Wed Mar 01 00:00:00 2000'::timestamp without time zone) AND (f1 < 'Sat Apr 01 00:00:00 2000'::timestamp without time zone) AND (f2 >= 'Sat Jan 01 00:00:00 2000'::timestamp without time zone)))
   Root Table: timestamp_tbl_month_rangep
   Partitions Selected: 2
 Optimizer: pg_orca
(5 rows)

DROP TABLE TIMESTAMP_TBL_MONTH_rangep;