Recursive Subquery Factoring Date Bug in 11gR2

Since Oracle 11gR2, Oracle has supported Recursive Subquery Factoring – this is a more flexible way of doing recursive queries than Oracle’s Connect By. Although 11g is quite old now, it’s still being used in earnest.  Whilst writing some code recently, I noticed there appears to be a problem with them regarding dates. I found this in version 11.2.0.2.0. Let’s take a look at what I found…

with t1(a_date) as (
  select date '2018-12-26'
  from   dual
  union all
  select a_date + 1 -- Problem here
  from   t1
  where a_date + 1 < date '2018-12-30'
)
select * from t1

Error at line 5
ORA-01790: expression must have same datatype as corresponding expression

Note : date ‘2018-12-26’ is already a date. This is ISO 8601 compliant short form for defining dates in Oracle.

Mmm ok, let’s cast the expression a_date + 1 to a date.

with t1(a_date) as (
  select date '2018-12-26'
  from   dual
  union all
  select cast(a_date as date) + 1 
  from   t1
  where a_date + 1 < date '2018-12-30'
)
select * from t1

Error at line 5
ORA-01790: expression must have same datatype as corresponding expression

Still a problem?!!  OK, let’s go overboard and cast all the dates to a date, to see if that fixes it!

with t1(a_date) as (
  select cast(date '2018-12-26' as date)
  from   dual
  union all
  select cast(a_date as date) + 1 
  from   t1
  where cast(a_date as date) + 1 < date '2018-12-30'
)
select * from t1

A_DATE   
---------
26-DEC-18
1 row selected.

OK, it works, but the result isn’t correct???!!!! The recursion should continue until the date reaches 30th December 2018!

Well maybe the execution plan give us some clues…

SQL_ID  3b81h2mu2agbh, child number 0
-------------------------------------
with t1(a_date) as (   select cast(date '2018-12-26' as date)   from   
dual   union all   select cast(a_date as date) + 1    from   t1   where 
cast(a_date as date) + 1 < cast(date '2018-12-30' as date) ) select * 
from t1
 
Plan hash value: 1492144221
 
---------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |      |        |       |     4 (100)|          |
|   1 |  VIEW                                     |      |      2 |    18 |     4   (0)| 00:00:01 |
|   2 |   UNION ALL (RECURSIVE WITH) BREADTH FIRST|      |        |       |            |          |
|   3 |    FAST DUAL                              |      |      1 |       |     2   (0)| 00:00:01 |
|   4 |    RECURSIVE WITH PUMP                    |      |        |       |            |          |
---------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SET$1 / T1@SEL$3
   2 - SET$1
   3 - SEL$1 / DUAL@SEL$1
   4 - SEL$2
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
      DB_VERSION('11.2.0.2')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"SET$1")
      OUTLINE_LEAF(@"SEL$3")
      NO_ACCESS(@"SEL$3" "T1"@"SEL$3")
      FULL(@"SEL$2" "T1"@"SEL$2")
      END_OUTLINE_DATA
  */
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "T1"."A_DATE"[DATE,7]
   2 - STRDEF[7]
   4 - SYSVARCOL[4], "A_DATE"[DATE,8]
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

Pay particular attention to the Column Projection Information.

Column1 = Result = DATE (7 byte) = Internal SQL date type
Column4 = Recursion = DATE (8 byte) = PL/SQL date type, usually caused by a to_date(..)

See this article for how are dates stored in Oracle..

Weird huh?!

If the date is from a table you get some leniency from having to cast everything (except the where clause), avoiding some errors, but is still gives wrong results.

create table dummy_date as 
select to_date('2018-12-26', 'yyyy-mm-dd') a_date from dual;

with t1(a_date) as (  
  select a_date
  from   dummy_date
  union all
  select a_date + 1 
  from   t1
  where cast(a_date as date) + 1 < date '2018-12-30'
)
select * from t1

A_DATE   
---------
26-DEC-18
1 row selected.

Well, before you ask, I tried all sorts of variations of to_date(….) cast(… as date) even timestamps. You name it, I tried it, it seems a nasty bug.

What about Depth First searches (Breadth First is the default).

with t1(a_date) as (
 select cast(date '2018-12-26' as date)
 from dual
 union all
 select cast(a_date as date) + 1 
 from t1
 where cast(a_date as date) + 1 < date '2018-12-30'
)
search depth first by a_date set order_by
select a_date from t1

A_DATE   
---------
26-DEC-18
1 row selected.

Nope, still incorrect.  Let’s see if other types are affected in 11gR2…

with t1(a_string) as (
  select cast('a' as varchar2(10))
  from   dual
  union all
  select a_string||'a'
  from   t1
  where length(a_string||'a') < 6
)
select * from t1;

A_STRING    
------------
a           
aa          
aaa         
aaaa        
aaaaa       

5 rows selected.

with t1(a_number) as (
  select 1
  from   dual
  union all
  select a_number + 1
  from   t1
  where a_number + 1 < 6
)
select * from t1;

  A_NUMBER
----------
         1
         2
         3
         4
         5

5 rows selected.

Nope, they work fine, it’s only dates!

Let’s see how the original query works in 12.2.0.1.0c

with t1(a_date) as (
  select date '2018-12-26'
  from   dual
  union all
  select a_date + 1 
  from   t1
  where a_date + 1 < date '2018-12-30'
)
select * from t1;

A_DATE
26-DEC-18
27-DEC-18
28-DEC-18
29-DEC-18

4 rows selected.

Surprise surprise, it’s fine!

Anyway, if you are on 11g and using Recursive Subquery Factoring with dates you have been warned….

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s