Oracle internal date conversion : ORA-00932 and weird stuff

Question

Here many of our developpers are using Oracle hierarchical sub-queries to build lists of days, like this for exemple :

WITH mydays AS (
  SELECT CAST( TO_DATE('20161201', 'YYYYMMDD') + ROWNUM AS DATE) AS d
  FROM DUAL CONNECT BY TO_DATE('20161201', 'YYYYMMDD') + ROWNUM <= to_date('20161215', 'YYYYMMDD')
) 

For me it looked like the CAST ... AS DATE should be useless because we are converting a DATE to a DATE, right ? Actually it is not. If I create and populate a table like this (I need a join for my demonstration...) :

create table t(d date, i number);
insert into t(d, i) values (to_date('20161204', 'YYYYMMDD'), 1);
commit;

and try a query with a join like that, without the CAST, I get the following explain plan :

WITH mydays AS (
  SELECT  TO_DATE('20161201', 'YYYYMMDD') + ROWNUM  AS d
  FROM DUAL CONNECT BY TO_DATE('20161215', 'YYYYMMDD') + ROWNUM <= to_date('20161201', 'YYYYMMDD')
) 
select mydays.d from mydays join t on t.d = mydays.d;

.

----------------------------------------------------------------------------------------
| Id  | Operation                       | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |      |     1 |    15 |     6  (17)| 00:00:01 |
|*  1 |  HASH JOIN                      |      |     1 |    15 |     6  (17)| 00:00:01 |
|   2 |   VIEW                          |      |     1 |     6 |     2   (0)| 00:00:01 |
|   3 |    COUNT                        |      |       |       |            |          |
|*  4 |     CONNECT BY WITHOUT FILTERING|      |       |       |            |          |
|   5 |      FAST DUAL                  |      |     1 |       |     2   (0)| 00:00:01 |
|   6 |   TABLE ACCESS FULL             | T    |     1 |     9 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("T"."D"=INTERNAL_FUNCTION("MYDAYS"."D"))
4 - filter(TO_DATE(' 2016-12-15 00:00:00', 'syyyy-mm-dd 
          hh24:mi:ss')+ROWNUM<=TO_DATE(' 2016-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

with the CAST, the INTERNAL_FUNCTION() is gone :

4 - filter(TO_DATE(' 2016-12-01 00:00:00', 'syyyy-mm-dd 
          hh24:mi:ss')+ROWNUM<=TO_DATE(' 2016-12-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

I googled a little and found that the conversion should be between two internal date format, handled internally by Oracle with id 12 and 13, as we can find a call to DUMP(). Without the CAST :

Typ=13 Len=8: 7,224,12,4,0,0,0,0

With the CAST :

Typ=12 Len=7: 120,116,12,4,1,1,1

Which explains that we have less conversion with the CAST than without (!!!).

But it get worse when I put bind variables in play. If I want to get the explain plan of this SQL :

WITH mydays AS (
  SELECT  TO_DATE(:beginning, 'YYYYMMDD') + ROWNUM  AS d
  FROM DUAL CONNECT BY TO_DATE(:end, 'YYYYMMDD') + ROWNUM <= to_date(:beginning, 'YYYYMMDD')
) 
select mydays.d from mydays join t on t.d = mydays.d

I get an error : ORA-00932: inconsistent datatypes: expected DATE got NUMBER. If I add the CAST magic the error is fixed. I know that when I generate a plan all bind variables are considered as VARCHAR2. But with the TO_DATE(), we should only be working with DATE, right ? Where does this NUMBER come from ?

Thanks if you can give me any explaination, as my brain is beginning to get very messy with all that weird stuff.

Answer 1

Pretty interest query for getting calendar. Easier to read:

SELECT to_date('20160101','yyyymmdd') + LEVEL 
  FROM DUAL 
CONNECT BY LEVEL <= 90;

You are looking for solution or just investigating local issue ?