Joins and filter conditions do not always raise an exception for implicit casts to invalid timestamps or dates

Detail name	Value
Changelog Number	10899
Type	Bug
Status	Resolved
Affected Versions	Exasol 7.0.0
Fix Versions	Exasol 7.0.4
Resolution Date	2020-11-26

Description

A string can only be converted to a timestamp or date if the string literal actually contains a valid timestamp or date. Otherwise, a data exception will be thrown.
Filtering a timestamp or date column with an invalid string literal only throws an exception if there is no local index on the column. If a local index exists, the filter is (incorrectly) evaluated. For example:

create or replace table t(c timestamp);
insert into t values '2020-02-10';
select * from t where c = 'invalid timestamp'; -- data exception
enforce local index on t(c);
select * from t where c = 'invalid timestamp'; -- no exception, returns no results

Furthermore, joining a timestamp or date column with a string column that contains invalid values does not raise an exception. For all other data types, there are exceptions in such cases.

create or replace table t2(a varchar(200));
insert into t2 values ('invalid timestamp');
select * from t inner join t2 on t.c = t2.a; -- no exception

Workaround

You can use an explicit type conversion to receive the expected data exception. For example:

select * from t where c = to_date('invalid timestamp');

Fix

Exasol now throws exceptions in these cases.

Changed behavior

Filtering or joining a timestamp or date column with strings that contain invalid values now always throws an exception.