CODEWITHSUNDEEP
oracle-databaseoracle12c
Andres Castro
Andres Castro

Reputation: 25

Oracle 12c PARTITION BY RANGE INTERVAL MONTH ORA-01841

When working with some partitioning proof of concepts for huge tables (2b+ rows) we came up with some strange behavior of the select statements:

create table date_test (
JOURNAL_ID VARCHAR2(10 BYTE) NOT NULL ENABLE, 
JOURNAL_DATE DATE NOT NULL ENABLE
)
PARTITION BY RANGE (JOURNAL_DATE) INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) 
(
PARTITION P_20010101  VALUES LESS THAN (TO_DATE('20010101', 'YYYYMMDD'))
);

And when running this query:

select * from date_test
 WHERE (
 JOURNAL_DATE < TO_DATE('20061201','YYYYMMDD')
 OR 
 JOURNAL_DATE > TO_DATE('20061231','YYYYMMDD')
 ) ;

I get the following error:

ORA-01841: (full) year must be between -4713 and +9999, and not be 0
01841. 00000 - "(full) year must be between -4713 and +9999, and not be 0
*Cause: Illegal year entered
*Action: Input year in the specified range

The "interesting" thing is that if you remove one of the clauses on either side of the OR, the query seems to fire up OK. Of course the query works correctly in a non partitioned table.

Any help or pointers are greatly appreciated.

Upvotes: 0

Views: 1841

Answers (2)

Alex Poole
Alex Poole

Reputation: 191275

This looks like bug, possibly 20961659 or one of its relatives. You may be able to work around it with by changing the optimizer_features_enable setting, which you can do as part of your query if you don't want it applied more widely:

select /*+ optimizer_features_enable('12.1.0.1') */ * from date_test
 WHERE (
 JOURNAL_DATE < TO_DATE('20061201','YYYYMMDD')
 OR 
 JOURNAL_DATE > TO_DATE('20061231','YYYYMMDD')
 ) ;

In 12.1.0.2.170117 I see the same error with your original query; but with that hint it works as expected.

Beware of side effects of course, and if in doubt you can always raise a service request with Oracle Support.

Read more in the documentation, and also explore the entries related to this error in My Oracle Support.

In this case it appears you can also get what you want by reversing the logic to use an inclusive date range with and, and negating that with not; but you need to stop the optimizer changing it back - this may be overkill again but a no_query_transformation hint seems to do the trick:

select /*+ no_query_transformation */ * from date_test
 WHERE NOT (
 JOURNAL_DATE >= TO_DATE('20061201','YYYYMMDD')
 AND 
 JOURNAL_DATE <= TO_DATE('20061231','YYYYMMDD')
 ) ;

The no_expand hint doesn't seem to help here; though as @WernfriedDomscheit suggested, the undocumented no_expand_table hint does seem to help.

Upvotes: 1

waleedazam
waleedazam

Reputation: 367

try to use >=

select * from date_test WHERE TO_DATE('20061201','YYYYMMDD') OR JOURNAL_DATE >= TO_DATE('20061231','YYYYMMDD') ) ;( JOURNAL_DATE <

or use between

where JOURNAL_DATE between to_date('20061201','YYYYMMDD') and TO_DATE('20061231','YYYYMMDD')

Upvotes: 0

Related Questions