oracle daysbetween,ORA-01847: day of month must be between 1 and last day of month

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.