SQL worked fine on pre 12c but failed with “ORA-01843: not a valid month” after upgrade.

Recently on OTN, OP posted below scenario.

When he ran below SQL, it failed with “ORA-01843” in oracle 12c version.

select FISCAL_SYSTEM_PERIOD_CAL_YR_EQ, calendrical_equivalent
from fiscal_system_periods
where calendrical_equivalent > 0 and
to_date(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ || ', ' || 2014,'Month, YYYY') >= to_date('November, 2014','Month, YYYY')

SQL comprise of two different condition on same table within “AND”,

condition 1 : calendrical_equivalent > 0
condition 2 : to_date(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ || ‘, ‘ || 2014,’Month, YYYY’) >= to_date(‘November, 2014′,’Month, YYYY’)

OP mentioned same, used to work fine in pre oracle version(11g)

Lets check the table Data.
Table data:

Create Table fiscal_system_periods
As
with
FISCAL_SYSTEM_PERIOD_CAL_YR_EQs as
(Select 'January' FISCAL_SYSTEM_PERIOD_CAL_YR_EQ, 1 calendrical_equivalent From Dual
union all Select 'February' FISCAL_SYSTEM_PERIOD_CAL_YR_EQ , 2 calendrical_equivalent From Dual
union all Select 'March' FISCAL_SYSTEM_PERIOD_CAL_YR_EQ , 3 calendrical_equivalent From Dual
union all Select 'April' FISCAL_SYSTEM_PERIOD_CAL_YR_EQ , 4 calendrical_equivalent From Dual
union all Select 'May' FISCAL_SYSTEM_PERIOD_CAL_YR_EQ, 5 calendrical_equivalent From Dual
union all Select 'June' FISCAL_SYSTEM_PERIOD_CAL_YR_EQ, 6 calendrical_equivalent From Dual
union all Select 'July' FISCAL_SYSTEM_PERIOD_CAL_YR_EQ, 7 calendrical_equivalent From Dual
union all Select 'August' FISCAL_SYSTEM_PERIOD_CAL_YR_EQ, 8 calendrical_equivalent From Dual
union all Select 'September' FISCAL_SYSTEM_PERIOD_CAL_YR_EQ, 9 calendrical_equivalent From Dual
union all Select 'October' FISCAL_SYSTEM_PERIOD_CAL_YR_EQ, 10 calendrical_equivalent From Dual
union all Select 'November' FISCAL_SYSTEM_PERIOD_CAL_YR_EQ, 11 calendrical_equivalent From Dual
Union All Select 'December' FISCAL_SYSTEM_PERIOD_CAL_YR_EQ, 12 calendrical_equivalent From Dual
Union All Select '13 Series' FISCAL_SYSTEM_PERIOD_CAL_YR_EQ, Null calendrical_equivalent From Dual)
Select FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,Cal
from fiscal_system_periods ;

Check at last records, “FISCAL_SYSTEM_PERIOD_CAL_YR_EQ” is set as ’13 Series’.Any date function on such data will surely fail.

i.e. for sql, condition 2 for last rows will be as below.
to_date(’13 Series’ || ‘, ‘ || 2014,’Month, YYYY’) >= to_date(‘November, 2014′,’Month, YYYY’)

Surely , it will failed with ORA-01843 error.
select to_date('13 Series' || ', ' || 2014,'Month, YYYY') from dual;

ORA-01843: not a valid month
01843. 00000 - "not a valid month"

To avoid last rows, OP has already applied condition “calendrical_equivalent > 0” but still error.!

Myself also posted my observation on error at OTN.

Condition 1: calendrical_equivalent > 0
Condition 2: to_date(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ || ‘, ‘ || 2014,’Month, YYYY’) >= to_date(‘November, 2014′,’Month, YYYY’)

If only condition 1 is processed first, then no exception!
If combined, condition 1 + condition 2 is processed by oracle then Exception is raised.

But was not aware of actual root cause.Later folks posted about condition order of processing can cause exception.

“Oracle may choose to evaluate any of the two predicates first when you AND them”

If condition 1 is processed first then row with “FISCAL_SYSTEM_PERIOD_CAL_YR_EQ = 13 Series” is eliminated and condition 2 on filtered rows will not give any error.
But If condition 2 is processed first, then oracle is bound to give exceptions as last rows will also get processed.

Point of interest is how Oracle decide which condition to processed first, how optimizer internally work for such conditions?
As if oracle decide to processed condition 1 first, SQL will result no error.but vice versa will caused an error.

Ideally speaking for such cases, developer them self should enforce a condition to processed first.
instead of relying on oracle for exact order of processing to avoid any exception at runtime.

Some thing like below,

WITH ABC AS
(select /*+ materialize*/ Period, Cal,to_date(Period || ', ' || 2014,'Month, YYYY') col1 ,to_date('November, 2014','Month, YYYY') col2
FROM Table T
Where condition1 > 0 ) -- materialize forcing condition 1 to processed first.
SELECT * FROM ABC WHERE COL1 >= COL2;

For OTN Discussion on same, please check here.

For actual root cause and some internals on such behavious.Best is, Jonathan lewis post on same.Please Click here for actual reasons which is not due to upgrade, but related to stats! and experts comments on same.

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Oracle Internal and tagged , , . Bookmark the permalink.

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