-- pg_orca partition regression tests
-- Ported from Greenplum testrepo/partitioning/BFV/sql/mpp25707.sql
-- MPP-25707: partition selector with compatible types (timestamp vs date casting)
LOAD 'pg_orca';
SET pg_orca.enable_orca = on;
SET client_min_messages = warning;
SET DateStyle = 'ISO';
-- ===================================================================
-- CASE 1: RANGE partitioning START INCLUSIVE, END EXCLUSIVE
-- GPDB: start ('2000-01-01'::timestamp) INCLUSIVE end ('2001-01-01') EXCLUSIVE every ('1 month')
-- PG18: 12 monthly partitions [2000-01-01, 2000-02-01), ..., [2000-12-01, 2001-01-01)
-- ===================================================================
DROP TABLE IF EXISTS TIMESTAMP_MONTH_rangep_STARTINCL;
CREATE TABLE TIMESTAMP_MONTH_rangep_STARTINCL (i1 int, f2 timestamp)
PARTITION BY RANGE (f2);
CREATE TABLE TIMESTAMP_MONTH_rangep_STARTINCL_p01 PARTITION OF TIMESTAMP_MONTH_rangep_STARTINCL FOR VALUES FROM ('2000-01-01') TO ('2000-02-01');
CREATE TABLE TIMESTAMP_MONTH_rangep_STARTINCL_p02 PARTITION OF TIMESTAMP_MONTH_rangep_STARTINCL FOR VALUES FROM ('2000-02-01') TO ('2000-03-01');
CREATE TABLE TIMESTAMP_MONTH_rangep_STARTINCL_p03 PARTITION OF TIMESTAMP_MONTH_rangep_STARTINCL FOR VALUES FROM ('2000-03-01') TO ('2000-04-01');
CREATE TABLE TIMESTAMP_MONTH_rangep_STARTINCL_p04 PARTITION OF TIMESTAMP_MONTH_rangep_STARTINCL FOR VALUES FROM ('2000-04-01') TO ('2000-05-01');
CREATE TABLE TIMESTAMP_MONTH_rangep_STARTINCL_p05 PARTITION OF TIMESTAMP_MONTH_rangep_STARTINCL FOR VALUES FROM ('2000-05-01') TO ('2000-06-01');
CREATE TABLE TIMESTAMP_MONTH_rangep_STARTINCL_p06 PARTITION OF TIMESTAMP_MONTH_rangep_STARTINCL FOR VALUES FROM ('2000-06-01') TO ('2000-07-01');
CREATE TABLE TIMESTAMP_MONTH_rangep_STARTINCL_p07 PARTITION OF TIMESTAMP_MONTH_rangep_STARTINCL FOR VALUES FROM ('2000-07-01') TO ('2000-08-01');
CREATE TABLE TIMESTAMP_MONTH_rangep_STARTINCL_p08 PARTITION OF TIMESTAMP_MONTH_rangep_STARTINCL FOR VALUES FROM ('2000-08-01') TO ('2000-09-01');
CREATE TABLE TIMESTAMP_MONTH_rangep_STARTINCL_p09 PARTITION OF TIMESTAMP_MONTH_rangep_STARTINCL FOR VALUES FROM ('2000-09-01') TO ('2000-10-01');
CREATE TABLE TIMESTAMP_MONTH_rangep_STARTINCL_p10 PARTITION OF TIMESTAMP_MONTH_rangep_STARTINCL FOR VALUES FROM ('2000-10-01') TO ('2000-11-01');
CREATE TABLE TIMESTAMP_MONTH_rangep_STARTINCL_p11 PARTITION OF TIMESTAMP_MONTH_rangep_STARTINCL FOR VALUES FROM ('2000-11-01') TO ('2000-12-01');
CREATE TABLE TIMESTAMP_MONTH_rangep_STARTINCL_p12 PARTITION OF TIMESTAMP_MONTH_rangep_STARTINCL FOR VALUES FROM ('2000-12-01') TO ('2001-01-01');
-- Middle of a middle range
INSERT INTO TIMESTAMP_MONTH_rangep_STARTINCL values (1, '2000-07-16');
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = '2000-07-16';
 i1 |         f2          
----+---------------------
  1 | 2000-07-16 00:00:00
(1 row)

SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = TO_TIMESTAMP('2000-07-16', 'YYYY-MM-DD');
 i1 |         f2          
----+---------------------
  1 | 2000-07-16 00:00:00
(1 row)

SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = TO_DATE('2000-07-16', 'YYYY-MM-DD');
 i1 |         f2          
----+---------------------
  1 | 2000-07-16 00:00:00
(1 row)

-- Beginning of the first range (inclusive start)
INSERT INTO TIMESTAMP_MONTH_rangep_STARTINCL values (2, '2000-01-01');
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = '2000-01-01';
 i1 |         f2          
----+---------------------
  2 | 2000-01-01 00:00:00
(1 row)

SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = TO_TIMESTAMP('2000-01-01', 'YYYY-MM-DD');
 i1 |         f2          
----+---------------------
  2 | 2000-01-01 00:00:00
(1 row)

SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = TO_DATE('2000-01-01', 'YYYY-MM-DD');
 i1 |         f2          
----+---------------------
  2 | 2000-01-01 00:00:00
(1 row)

INSERT INTO TIMESTAMP_MONTH_rangep_STARTINCL values (3, '2000-01-02');
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = '2000-01-02';
 i1 |         f2          
----+---------------------
  3 | 2000-01-02 00:00:00
(1 row)

SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = TO_TIMESTAMP('2000-01-02', 'YYYY-MM-DD');
 i1 |         f2          
----+---------------------
  3 | 2000-01-02 00:00:00
(1 row)

SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = TO_DATE('2000-01-02', 'YYYY-MM-DD');
 i1 |         f2          
----+---------------------
  3 | 2000-01-02 00:00:00
(1 row)

-- End of the last range (last partition ends at 2001-01-01 exclusive)
INSERT INTO TIMESTAMP_MONTH_rangep_STARTINCL values (4, '2000-12-31');
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = '2000-12-31';
 i1 |         f2          
----+---------------------
  4 | 2000-12-31 00:00:00
(1 row)

SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = TO_TIMESTAMP('2000-12-31', 'YYYY-MM-DD');
 i1 |         f2          
----+---------------------
  4 | 2000-12-31 00:00:00
(1 row)

SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = TO_DATE('2000-12-31', 'YYYY-MM-DD');
 i1 |         f2          
----+---------------------
  4 | 2000-12-31 00:00:00
(1 row)

-- 2001-01-01 is beyond the last partition (end exclusive) -- should fail
INSERT INTO TIMESTAMP_MONTH_rangep_STARTINCL values (5, '2001-01-01');
ERROR:  no partition of relation "timestamp_month_rangep_startincl" found for row
DETAIL:  Partition key of the failing row contains (f2) = (2001-01-01 00:00:00).
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = '2001-01-01';
 i1 | f2 
----+----
(0 rows)

SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = TO_TIMESTAMP('2001-01-01', 'YYYY-MM-DD');
 i1 | f2 
----+----
(0 rows)

SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = TO_DATE('2001-01-01', 'YYYY-MM-DD');
 i1 | f2 
----+----
(0 rows)

DROP TABLE TIMESTAMP_MONTH_rangep_STARTINCL;
-- ===================================================================
-- CASE 2: RANGE partitioning START EXCLUSIVE, END INCLUSIVE
-- GPDB: start ('2000-01-01') EXCLUSIVE end ('2001-01-01') INCLUSIVE every ('1 month')
-- PG18: 12 monthly partitions (2000-01-01, 2000-02-01], ..., (2000-12-01, 2001-01-01]
-- PG18 RANGE is always [from, to), so EXCLUSIVE start means from ('2000-01-01 00:00:00.000001')
-- and INCLUSIVE end means to ('2001-01-01 00:00:00.000001')
-- ===================================================================
DROP TABLE IF EXISTS TIMESTAMP_MONTH_rangep_STARTEXCL;
CREATE TABLE TIMESTAMP_MONTH_rangep_STARTEXCL (i1 int, f2 timestamp)
PARTITION BY RANGE (f2);
CREATE TABLE TIMESTAMP_MONTH_rangep_STARTEXCL_p01 PARTITION OF TIMESTAMP_MONTH_rangep_STARTEXCL FOR VALUES FROM ('2000-01-01 00:00:00.000001') TO ('2000-02-01 00:00:00.000001');
CREATE TABLE TIMESTAMP_MONTH_rangep_STARTEXCL_p02 PARTITION OF TIMESTAMP_MONTH_rangep_STARTEXCL FOR VALUES FROM ('2000-02-01 00:00:00.000001') TO ('2000-03-01 00:00:00.000001');
CREATE TABLE TIMESTAMP_MONTH_rangep_STARTEXCL_p03 PARTITION OF TIMESTAMP_MONTH_rangep_STARTEXCL FOR VALUES FROM ('2000-03-01 00:00:00.000001') TO ('2000-04-01 00:00:00.000001');
CREATE TABLE TIMESTAMP_MONTH_rangep_STARTEXCL_p04 PARTITION OF TIMESTAMP_MONTH_rangep_STARTEXCL FOR VALUES FROM ('2000-04-01 00:00:00.000001') TO ('2000-05-01 00:00:00.000001');
CREATE TABLE TIMESTAMP_MONTH_rangep_STARTEXCL_p05 PARTITION OF TIMESTAMP_MONTH_rangep_STARTEXCL FOR VALUES FROM ('2000-05-01 00:00:00.000001') TO ('2000-06-01 00:00:00.000001');
CREATE TABLE TIMESTAMP_MONTH_rangep_STARTEXCL_p06 PARTITION OF TIMESTAMP_MONTH_rangep_STARTEXCL FOR VALUES FROM ('2000-06-01 00:00:00.000001') TO ('2000-07-01 00:00:00.000001');
CREATE TABLE TIMESTAMP_MONTH_rangep_STARTEXCL_p07 PARTITION OF TIMESTAMP_MONTH_rangep_STARTEXCL FOR VALUES FROM ('2000-07-01 00:00:00.000001') TO ('2000-08-01 00:00:00.000001');
CREATE TABLE TIMESTAMP_MONTH_rangep_STARTEXCL_p08 PARTITION OF TIMESTAMP_MONTH_rangep_STARTEXCL FOR VALUES FROM ('2000-08-01 00:00:00.000001') TO ('2000-09-01 00:00:00.000001');
CREATE TABLE TIMESTAMP_MONTH_rangep_STARTEXCL_p09 PARTITION OF TIMESTAMP_MONTH_rangep_STARTEXCL FOR VALUES FROM ('2000-09-01 00:00:00.000001') TO ('2000-10-01 00:00:00.000001');
CREATE TABLE TIMESTAMP_MONTH_rangep_STARTEXCL_p10 PARTITION OF TIMESTAMP_MONTH_rangep_STARTEXCL FOR VALUES FROM ('2000-10-01 00:00:00.000001') TO ('2000-11-01 00:00:00.000001');
CREATE TABLE TIMESTAMP_MONTH_rangep_STARTEXCL_p11 PARTITION OF TIMESTAMP_MONTH_rangep_STARTEXCL FOR VALUES FROM ('2000-11-01 00:00:00.000001') TO ('2000-12-01 00:00:00.000001');
CREATE TABLE TIMESTAMP_MONTH_rangep_STARTEXCL_p12 PARTITION OF TIMESTAMP_MONTH_rangep_STARTEXCL FOR VALUES FROM ('2000-12-01 00:00:00.000001') TO ('2001-01-01 00:00:00.000001');
-- Middle of a middle range
INSERT INTO TIMESTAMP_MONTH_rangep_STARTEXCL values (1, '2000-07-16');
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = '2000-07-16';
 i1 |         f2          
----+---------------------
  1 | 2000-07-16 00:00:00
(1 row)

SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = TO_TIMESTAMP('2000-07-16', 'YYYY-MM-DD');
 i1 |         f2          
----+---------------------
  1 | 2000-07-16 00:00:00
(1 row)

SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = TO_DATE('2000-07-16', 'YYYY-MM-DD');
 i1 |         f2          
----+---------------------
  1 | 2000-07-16 00:00:00
(1 row)

-- 2000-01-01 is excluded from start -- should fail
INSERT INTO TIMESTAMP_MONTH_rangep_STARTEXCL values (2, '2000-01-01');
ERROR:  no partition of relation "timestamp_month_rangep_startexcl" found for row
DETAIL:  Partition key of the failing row contains (f2) = (2000-01-01 00:00:00).
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = '2000-01-01';
 i1 | f2 
----+----
(0 rows)

SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = TO_TIMESTAMP('2000-01-01', 'YYYY-MM-DD');
 i1 | f2 
----+----
(0 rows)

SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = TO_DATE('2000-01-01', 'YYYY-MM-DD');
 i1 | f2 
----+----
(0 rows)

INSERT INTO TIMESTAMP_MONTH_rangep_STARTEXCL values (3, '2000-01-02');
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = '2000-01-02';
 i1 |         f2          
----+---------------------
  3 | 2000-01-02 00:00:00
(1 row)

SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = TO_TIMESTAMP('2000-01-02', 'YYYY-MM-DD');
 i1 |         f2          
----+---------------------
  3 | 2000-01-02 00:00:00
(1 row)

SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = TO_DATE('2000-01-02', 'YYYY-MM-DD');
 i1 |         f2          
----+---------------------
  3 | 2000-01-02 00:00:00
(1 row)

-- End of the last range (end inclusive: 2001-01-01 is valid)
INSERT INTO TIMESTAMP_MONTH_rangep_STARTEXCL values (4, '2000-12-31');
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = '2000-12-31';
 i1 |         f2          
----+---------------------
  4 | 2000-12-31 00:00:00
(1 row)

SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = TO_TIMESTAMP('2000-12-31', 'YYYY-MM-DD');
 i1 |         f2          
----+---------------------
  4 | 2000-12-31 00:00:00
(1 row)

SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = TO_DATE('2000-12-31', 'YYYY-MM-DD');
 i1 |         f2          
----+---------------------
  4 | 2000-12-31 00:00:00
(1 row)

-- 2001-01-01 should be within range (end inclusive) -- should succeed
INSERT INTO TIMESTAMP_MONTH_rangep_STARTEXCL values (5, '2001-01-01');
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = '2001-01-01';
 i1 |         f2          
----+---------------------
  5 | 2001-01-01 00:00:00
(1 row)

SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = TO_TIMESTAMP('2001-01-01', 'YYYY-MM-DD');
 i1 |         f2          
----+---------------------
  5 | 2001-01-01 00:00:00
(1 row)

SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = TO_DATE('2001-01-01', 'YYYY-MM-DD');
 i1 |         f2          
----+---------------------
  5 | 2001-01-01 00:00:00
(1 row)

DROP TABLE TIMESTAMP_MONTH_rangep_STARTEXCL;
-- ===================================================================
-- CASE 3: LIST partitioning on timestamp
-- ===================================================================
DROP TABLE IF EXISTS TIMESTAMP_MONTH_listp;
CREATE TABLE TIMESTAMP_MONTH_listp (i1 int, f2 timestamp)
PARTITION BY LIST (f2);
CREATE TABLE TIMESTAMP_MONTH_listp_jan1 PARTITION OF TIMESTAMP_MONTH_listp FOR VALUES IN ('2000-01-01'::timestamp);
CREATE TABLE TIMESTAMP_MONTH_listp_jan2 PARTITION OF TIMESTAMP_MONTH_listp FOR VALUES IN ('2000-01-02'::timestamp);
CREATE TABLE TIMESTAMP_MONTH_listp_jan3 PARTITION OF TIMESTAMP_MONTH_listp FOR VALUES IN ('2000-01-03'::timestamp);
CREATE TABLE TIMESTAMP_MONTH_listp_jan4 PARTITION OF TIMESTAMP_MONTH_listp FOR VALUES IN ('2000-01-04'::timestamp);
CREATE TABLE TIMESTAMP_MONTH_listp_jan5 PARTITION OF TIMESTAMP_MONTH_listp FOR VALUES IN ('2000-01-05'::timestamp);
INSERT INTO TIMESTAMP_MONTH_listp values (1, '2000-01-03');
SELECT * FROM TIMESTAMP_MONTH_listp WHERE f2 = '2000-01-03';
 i1 |         f2          
----+---------------------
  1 | 2000-01-03 00:00:00
(1 row)

SELECT * FROM TIMESTAMP_MONTH_listp WHERE f2 = TO_TIMESTAMP('2000-01-03', 'YYYY-MM-DD');
 i1 |         f2          
----+---------------------
  1 | 2000-01-03 00:00:00
(1 row)

SELECT * FROM TIMESTAMP_MONTH_listp WHERE f2 = TO_DATE('2000-01-03', 'YYYY-MM-DD');
 i1 |         f2          
----+---------------------
  1 | 2000-01-03 00:00:00
(1 row)

DROP TABLE TIMESTAMP_MONTH_listp;