Possible data exception for BETWEEN on date/timestamp columns

Detail name	Value
Changelog Number	10513
Type	Bug
Status	Resolved
Affected Versions	EXASOL 6.0.0, Exasol 6.1.0, Exasol 6.2.0, Exasol 7.0.0
Fix Versions	Exasol 7.0.1
Resolution Date	2020-09-23

Description

The following conditions will lead to an unexpected data exception in a query:

• The query contains a BETWEEN filter on a DATE or TIMESTAMP column
• The lower bound of this filter is one of the SYSDATE, CURRENT_DATE, SYSTIMESTAMP, CURRENT_TIMESTAMP and LOCALTIMESTAMP literals
• The table containing the filter column is chosen as root/scan table by the optimizer
• An index exists on that column
• The NLS format for the data type in question has been set to non-standard

Example

The below test case demonstrates this error:

create table h (i int);

insert into h values 1,2,3,4,5,6,7,8,9,10;

create or replace table DATE_SMALL (d date,  te timestamp);

insert into DATE_SMALL select
  add_days(current_date, -1*rownum+1), 
  add_hours(add_days(current_timestamp, 2) , -1*rownum+1)
 from (select * from h,h,h, h);

enforce local index on DATE_SMALL(d);
enforce local index on DATE_SMALL(te);

alter session set nls_date_format = 'MM.DD.YYYY';
select count(*) from DATE_SMALL where D between sysdate and sysdate+4;
-- [Code: 0, SQL State: 22018]  data exception - invalid value for MM format token; Value: '1993-03-26' Format: 'MM.DD.YYYY' (Session: 1674625188365843335)

Workaround

Replace the lower bound of BETWEEN with an equivalent expression:

-- where D between sysdate+0 and sysdate+4;
-- where D between add_days(sysdate,0) and sysdate+4;
-- ...