Why doesn't this query fail, when converting to_date

Question

This query runs and returns no rows.

SELECT DISTINCT TO_CHAR(START_DATE,'MM/DD/YYYY'),                             
                TO_CHAR(END_DATE,  'MM/DD/YYYY')                              
FROM                                                                          
(                                   
    SELECT START_DATE, END_DATE, END_DATE - START_DATE
    FROM
    (
        SELECT TO_DATE(SUBSTR(B,  1, 10), 'MM/DD/YYYY') START_DATE,               
               TO_DATE(SUBSTR(B, 14, 10), 'MM/DD/YYYY') END_DATE                  
        FROM (SELECT 'test date' b from dual)
    )
    WHERE END_DATE - START_DATE != 6
)

This small piece fails to run, due to the conversion error. [1]: ORA-01858: a non-numeric character was found where a numeric was expected

SELECT TO_DATE(SUBSTR(B,  1, 10), 'MM/DD/YYYY') START_DATE,               
       TO_DATE(SUBSTR(B, 14, 10), 'MM/DD/YYYY') END_DATE                  
FROM (SELECT 'test date' b from dual)

My expectation here was that the conversion error would cause an Oracle exception causing the program to fail out. There is something I don't know, or am not thinking about correctly.

Can someone point my nose in the correct direction on this one?

Thanks. Evil.

EDIT - NULL is handled differently with to_date. Oracle: Avoiding NULL value in to_date

EDIT - Plan

3 SELECT STATEMENT  ALL_ROWS
Cost: 3  Bytes: 0  Cardinality: 1  
Partition #: 0          
    2 FILTER  
    Cost: 0  Bytes: 0  Cardinality: 0  
    Partition #: 0      
        1 FAST DUAL  
        Cost: 2  Bytes: 0  Cardinality: 1  
        Partition #: 0

EDIT - I am running 10g.

Yes. That is why I put it there. Expecting the full query to fail. — EvilTeach, Apr 02 '14 at 20:13
That is interesting. Must be something to do with the optimizer. — OldProgrammer, Apr 02 '14 at 20:15
This is why I always tell people "check the plan for the *actual query*, including bind variables - don't just check it with literal values". — Jeffrey Kemp, Apr 03 '14 at 04:42
Yep. The select from dual is there to substitute for the actual table where the data comes from. — EvilTeach, Apr 03 '14 at 12:47

score 5 · Accepted Answer · answered Apr 02 '14 at 20:24

OK, I figured this out because it was driving me crazy. If you simplify the query to:

SELECT START_DATE, END_DATE, END_DATE - START_DATE
FROM
(
    SELECT TO_DATE(SUBSTR(B,  1, 10), 'MM/DD/YYYY') START_DATE,               
           TO_DATE(SUBSTR(B, 14, 10), 'MM/DD/YYYY') END_DATE                  
    FROM (SELECT 'test date' b from dual)
)
WHERE END_DATE - START_DATE != 6

The resulting execution plan (on 11gR2) looks like this:

Execution Plan
----------------------------------------------------------
Plan hash value: 4034615273

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|*  1 |  FILTER          |      |       |            |          |
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

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

   1 - filter(TO_DATE(NULL)-TO_DATE('test date','MM/DD/YYYY')<>6)

So, the CBO has determined that the substring for END_DATE is null, and hence, the to_date will result in a null value also. The optimizer then does not evaluate the START_DATE expression, and the invalid data formatting error is not encountered. You can see the same behavior if you execute:

SELECT TO_DATE (NULL) - TO_DATE ('test date', 'MM/DD/YYYY') FROM DUAL

It will not fail.

nice, +1. btw, if put in a string value longer than 14, it will fail. — tbone, Apr 02 '14 at 20:34

Why doesn't this query fail, when converting to_date

1 Answers1