Strange issue on selecting against a date column partition key
Hello,
We're experiencing a weird issue regarding a simple query selecting data from a partitioned table using the partition key with a literal value. Here is the query:
SELECT * FROM MOSAIC_DATA WHERE CDATE = TO_DATE('16/01/2016', 'DD/MM/YYYY');
This query returns no rows, while the partition for 16/01/2016 does exist.
CDATE is of DATE type, and the table is range-partitioned on that column (it's also subpartitioned). Basically there's a partition a day. So there are as many partitions as date calculated so far. I'm not at work so I can't give you exact details, but it starts somewhere years ago until few days in the future. So there's a partition for 01/01/2015, another for 02/01/2015, and so on until 18/01/2016 or so. There's a local index on CDATE, which has also got the partition for that date.