ORA-01840: input value not long enough for date format

I was assigned a case today where a nightly batch job crashed with error
ORA-01840 input value not long enough for date format, it started three days ago.
The job has been running fine for a long time and is still working in test and dev databases.

The sql executed is this:

SQL> SELECT Count("SALES_FACT"."VEH_KEY") "VEH_KEY"
FROM "SALES"."SALES_FACT" "SALES_FACT",
 "SALES"."ORDER_DIM" "ORDER_DIM",
 "SALES"."VEH_DIM" "VEH_DIM"
WHE 5 RE SUBSTR("ORDER_DIM"."ORDER_NBR", 1, 1) <> 'A' AND
 SUBSTR("ORDER_DIM"."ORDER_NBR", 1, 1) <> 'C' AND
 SUBSTR("ORDER_DIM"."ORDER_NBR", 1, 1) <> 'Z' AND
 "ORDER_DIM"."ORDER_ENTRY_DATE_KEY" <> 0 AND
 TO_DATE(TO_CHAR("ORDER_DIM"."ORDER_ENTRY_DATE_KEY"), 'YYYYMMDD') >= TO_DATE(' 2015-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
 TO_DATE(TO_CHAR("ORDER_DIM"."ORDER_ENTRY_DATE_KEY"), 'YYYYMMDD') <= TO_DATE(' 2015-01-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
 "SALES_FACT"."RECORD_TYPE" = 'ORDER' AND
 "SALES_FACT"."VEH_KEY" = "VEH_DIM"."VEH_KEY" AND
 "SALES_FACT"."ORDER_KEY" = "ORDER_DIM"."ORDER_KEY" AND
 TO_DATE(' 2015-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') <= TO_DATE(' 2015-01-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss') ;
 TO_DATE(TO_CHAR("ORDER_DIM"."ORDER_ENTRY_DATE_KEY"), 'YYYYMMDD') >= TO_DATE(' 2015-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
 *
ERROR at line 9:
ORA-01840: input value not long enough for date format

My first steps was to compare different statistics like …
– Plans (differs)
– Indexes
– Column definition
– Data
– Statistics
– Parameters
– Query rewrite

Plans are different
Looking at the plans they differ, we have for ..
Test that works fine

----------------------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT |                                            |
| 1 |  SORT AGGREGATE |                                             |
| 2 |   NESTED LOOPS |                                              |
| 3 |    NESTED LOOPS |                                             |
| 4 |     VIEW | index$_join$_002                                   |
|*5 |      HASH JOIN |                                              |
|*6 |       HASH JOIN |                                             |
| 7 |        BITMAP CONVERSION TO ROWIDS |                          |
|*8 |         BITMAP INDEX FULL SCAN | ORDER_DIM_ORD_DT_BINDX       |
| 9 |        BITMAP CONVERSION TO ROWIDS |                          |
|*10|         BITMAP INDEX FULL SCAN | ORDER_DIM_ORD_NBR_BINDX      |
| 11|       INDEX FAST FULL SCAN | ORDER_DIM_PK_INDX01              |
| 12|     PARTITION HASH ALL |                                      |
|*13|      TABLE ACCESS BY LOCAL INDEX ROWID| SALES_FACT            |
| 14|       BITMAP CONVERSION TO ROWIDS |                           |
|*15|        BITMAP INDEX SINGLE VALUE | SALES_FACT_ORDER_BINDX     |
|*16|   INDEX UNIQUE SCAN | VEHICLE_DIM_PK_INDX01                   |
---------------------------------------------------------------------

As you see we use a bitmap index and hash joins.

The same plan for production is…

---------------------------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------------------------
| 0| SELECT STATEMENT |                                             |
| 1|  SORT AGGREGATE |                                              |
| 2|   NESTED LOOPS |                                               |
| 3|    NESTED LOOPS |                                              |
|*4|     TABLE ACCESS FULL | ORDER_DIM                              |
| 5|     PARTITION HASH ALL |                                       |
|*6|      TABLE ACCESS BY LOCAL INDEX ROWID| SALES_FACT             |
| 7|       BITMAP CONVERSION TO ROWIDS |                            |
|*8|        BITMAP INDEX SINGLE VALUE | SALES_FACT_ORDER_BINDX      |
|*9|    INDEX UNIQUE SCAN | VEHICLE_DIM_PK_INDX01                   |
---------------------------------------------------------------------

Indexes
So different plans, do we have the same indexes in both databases ?
Test:

Index Name              Pos# Sort Column Name
----------------------- ---  ---- ---------------------
order_dim_ord_dt_bindx     1  ASC order_entry_date_key
order_dim_ord_nbr_bindx    1  ASC order_nbr
order_dim_pk_indx01        1  ASC order_key

Prod:

Index Name              Pos# Sort Column Name
----------------------- ---  ---- ---------------------
order_dim_ord_dt_bindx     1  ASC order_entry_date_key
order_dim_ord_nbr_bindx    1  ASC order_nbr
order_dim_pk_indx01        1  ASC order_key

So the indexes are identical when it comes to name and columns.

Column definition
The column has this definition in both databases

Name                 Null?    Type
-------------------- --------- --------
ORDER_ENTRY_DATE_KEY NOT NULL NUMBER(10)

The definition is a bit odd since we store dates in the column, a date column would be preferred but as it seems (see Data below) it has a purpose to create it like this.

Data
We store dates in the column in the format YYYYMMDD, and we store 0 for unset dates. I understand the logic but you will see that this is part of the problem. My idea at first was that we had some issues in the data stored, that one row had a fault (short) dateformat. So I wanted to check that.

> select length(order_entry_date_key),count(1) 
from sales.order_dim 
group by length(order_entry_date_key) 
order by 2;
LENGTH(ORDER_ENTRY_DATE_KEY) COUNT(1)
---------------------------- --------
                           1       13
                           8   186684

If my assumption was correct, we would have see one or more rows
with length between 1 and 8. But we have no such rows, remember that the error said “input value not long enough”. Our sql had the predicate ORDER_ENTRY_DATE_KEY<> 0, what if we have a value that has length 1 but is not the value 0, it would be returned. So I checked that as well.

> select order_entry_date_key 
  from sales.order_dim 
  where length(order_entry_date_key)=1 
   and order_entry_date_key<>0;

no rows selected

No, no errors there.

Statistics
Looking at the statistics it differs some, not a lot but clearly enough to have production to use another plan. But statistics in itself should not cause error ORA-1840.

Parameters
Parameter where almost the same (I used a 10053 trace to check that ), production had _fix_control_key set which was not set in test. But no changes like that was done for the past days so the parameter was there when it performed well.

Query Rewrite
I used the same 10053 trace to verify if the query was rewritten differently in the two databases, but they were identical.

So apart from the statistics and the plan I couldn’t see any differences explaining the issue. So I turned to study the execution plan again. It seems using index ORDER_DIM_ORD_DT_BINDX it works but if we perform a full table scan of order_dim we got the error. What if I force a full table scan on my test database ?

> SELECT /*+ FULL(ORDER_DIM) */ Count (SALES_FACT.VEH_KEY) AS VEH_KEY
  FROM ( SALES.SALES_FACT SALES_FACT INNER JOIN 
  SALES.ORDER_DIM ORDER_DIM 
ON (SALES_FACT.ORDER_KEY = ORDER_DIM.ORDER_KEY))
 INNER JOIN SALES.VEH_DIM VEH_DIM 
ON (SALES_FACT.VEH_KEY = VEH_DIM.VEH_KEY)
 WHERE (SUBSTR(ORDER_DIM.ORDER_NBR, 1, 1) NOT IN ('A', 'C', 'Z')) 
AND
 ((ORDER_DIM.ORDER_ENTRY_DATE_KEY != 0) 
AND (TO_DATE(ORDER_DIM.ORDER_ENTRY_DATE_KEY, 'YYYYMMDD')
BETWEEN TO_DATE('01/01/2015', 'MM/DD/YYYY')
AND TO_DATE('01/02/2015', 'MM/DD/YYYY'))) 
AND SALES_FACT.RECORD_TYPE = 'ORDER';
 (TO_DATE(ORDER_DIM.ORDER_ENTRY_DATE_KEY, 'YYYYMMDD')
 *
ERROR at line 7:
ORA-01840: input value not long enough for date format

BINGO !! I got the error in my test database if I forced a full table scan of order_dim. What in a full table scan can cause the error ?

I searched My Oracle Support
I found Note: Correct Query Generates ORA-1841 or ORA-1842 or ORA-1840 or ORA-1843 or ORA-1844 (Doc ID 422068.1)
Under cause it says: The date column has NULL values, in itself allowed a to_date operation on this column is also allowed but can generate error dependant on if where predicate ‘date field’ is not null is evaluated first or later

But looking at my columns definition we have not null constraints, so no nulls in our column. Statistics also confirms no nulls. Looking at bugs I came across a very old (2001) Bug 1781677 : ORA-1840 ON SELECT WITH DATE COLUMN AGAINST NOT NULL, BAD EXPLAIN PLAN CREATED, it was closed as “Not a bug” It had this info which made me read the bug more carefully.

If we use full table scan of PER_PERSON_ANALYSES . 
we cannot predict the order in which predicates 1) 
and 2) are evaluated.

They had the similar scenario as we have, two predicates on the table where one is a date predicate. And it is related to full table scan. This is how our predicate looks like

ORDER_DIM.ORDER_ENTRY_DATE_KEY != 0 AND
(TO_DATE(ORDER_DIM.ORDER_ENTRY_DATE_KEY, ‘YYYYMMDD’) BETWEEN TO_DATE(’01/01/2015′, ‘MM/DD/YYYY’) AND TO_DATE(’01/02/2015′, ‘MM/DD/YYYY’))

So when we use the index, we are filtering the ORDER_ENTRY_DATE_KEY != 0 before we filter the to_date clause. We can see this in the predicate information the plan. For test it looks like this..

 8 - filter(("ORDER_DIM"."ORDER_ENTRY_DATE_KEY"<>0 AND 
     TO_DATE(TO_CHAR("ORDER_DIM"."ORDER_ENTRY_DATE_KEY"),'YYYYMMDD')
     <=TO_DATE(' 2015-01-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss')   
 AND TO_DATE(TO_CHAR("ORDER_DIM"."ORDER_ENTRY_DATE_KEY"),'YYYYMMDD')
     >=TO_DATE(' 2015-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

And for Prod:

4 - filter((SUBSTR("ORDER_DIM"."ORDER_NBR",1,1)<>'A' 
    AND SUBSTR("ORDER_DIM"."ORDER_NBR",1,1)<>'C' 
    AND SUBSTR("ORDER_DIM"."ORDER_NBR",1,1)<>'Z' 
AND TO_DATE(TO_CHAR("ORDER_DIM"."ORDER_ENTRY_DATE_KEY"),'YYYYMMDD')
    >=TO_DATE(' 2015-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') 
AND TO_DATE(TO_CHAR("ORDER_DIM"."ORDER_ENTRY_DATE_KEY"),'YYYYMMDD')
    <=TO_DATE(' 2015-01-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss') 
    AND "ORDER_DIM"."ORDER_ENTRY_DATE_KEY"<>0))

As you see in test ORDER_ENTRY_DATE_KEY<>0 is evaluated first while in prod it is evaluated last, and that is why we get the error when we use the full table scan. We can not predict in which order the two predicates is evaluated using full table scan.

How to solve this
I talked to the customer and my idea to solve this is to use Sql Plan Management, with SPM we can lock a sql_id to a specific plan. Now in our example we use literals. The dates will change for every night it runs. We do control the sql and I have suggested that we changes the date values to bind variables. With bind variables for the dates the sql_id should not change night after night which is a must to make SPM work.
When the sql is changed I will update this case with the details how we solved it.

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