Looks like some kind of query rewrite bug that only kicks in when you add the where clause. If I skip the where clause and make it into a table:SQL> alter session set nls_date_format = 'dd/mm/yyyy';Session altered.SQL> create table bob2 aswith sample_data(dt) as(select '00/02/2015' from dual union allselect '10/02/2015' from dual union allselect '02/2015' from dual union allselect '13/02/2015' from dual union allselect '29/02/2015' from dual union allselect '29/02/2016' from dual union allselect '13/06/2015' from dual union allselect '13/06/2005' from dual union allselect '01//2015' from dual union allselect null from dual)select *from(select dt from sample_data where dt in (select to_char(to_date('01/01/2013')+rownum-1,'dd/mm/yyyy') from dual connect by level<=2000) ); 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21Table created.
We can then desc the table and see that oracle thinks dt is a varchar, which it should:SQL> desc bob2; Name Null? Type ----------------------------------------- -------- ---------------------------- DT VARCHAR2(10)
But if we then try and run the query with the where clause:SQL> with sample_data(dt) as(select '00/02/2015' from dual union allselect '10/02/2015' from dual union allselect '02/2015' from dual union allselect '13/02/2015' from dual union allselect '29/02/2015' from dual union allselect '29/02/2016' from dual union allselect '13/06/2015' from dual union allselect '13/06/2005' from dual union allselect '01//2015' from dual union allselect null from dual)select *from(select dt from sample_data where dt in (select to_char(to_date('01/01/2013')+rownum-1,'dd/mm/yyyy') from dual connect by level<=2000) )where to_date(dt,'dd/mm/yyyy') between to_date('01/01/2000', 'dd/mm/yyyy') and to_date('01/01/2020', 'dd/mm/yyyy'); 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21(select '00/02/2015' from dual union all *ERROR at line 2:ORA-01847: day of month must be between 1 and last day of monthSQL>
It's trying to to_Date the strings in the WITH clause. It really shouldn't.
I would have thought you could fix this with a hint, but I tried the obvious NO_QUERY_TRANSFORMATION and that didn't help. Maybe someone else has an idea.