Wrong comparison between DATE and a constant of type TIMESTAMP WITH LOCAL TIME ZONE

Detail name	Value
Changelog Number	10157
Type	Bug
Status	Resolved
Affected Versions	EXASOL 6.0.0, Exasol 6.1.0, Exasol 6.2.0, Exasol 7.0.alpha2
Fix Versions	Exasol 7.0.0, Exasol 6.2.7
Resolution Date	2020-06-08

Description

When comparing a DATE and a TIMESTAMP, the DATE value is implicitly cast to a TIMESTAMP so that the comparison occurs using the type TIMESTAMP  and the timestamp values are compared. 

However, when a DATE is compared to a constant of type TIMESTAMP WITH LOCAL TIME ZONE (such as CURRENT_TIMESTAMP), the TIMESTAMP value is implicitly cast to a DATE and the comparison happens using the type DATE instead - which gives a wrong result when comparing data from the same day.

An important constant of the type TIMESTAMP WITH LOCAL TIME ZONE is the CURRENT_TIMESTAMP literal.

If the values are stored in tables, this bug will not occur because no constant is used.

Example

-- wrong comparison for constants of type TIMESTAMP WITH LOCAL TIME ZONE
SELECT DATE '2020-05-05' < CAST(TIMESTAMP '2020-05-05 12:00:00' as TIMESTAMP WITH LOCAL TIME ZONE);
--> returns FALSE because the values are compared using the DATE datatype

-- this also affects CURRENT_TIMESTAMP
SELECT CURRENT_DATE < CURRENT_TIMESTAMP;
--> returns FALSE because the values are compared using the DATE datatype

-- correct comparison for constants of type TIMESTAMP
SELECT DATE '2020-05-05' < TIMESTAMP '2020-05-05 12:00:00';
--> returns TRUE because the values are compared using the TIMESTAMP datatype

-- correct comparison for tables
CREATE TABLE T(tl TIMESTAMP WITH LOCAL TIME ZONE);
INSERT INTO T VALUES CAST(TIMESTAMP '2020-05-05 12:00:00' as TIMESTAMP WITH LOCAL TIME ZONE);
SELECT date '2020-05-05' < tl FROM T;
--> returns TRUE because the values are compared using the TIMESTAMP datatype

Workaround

As a workaround, the DATE-Value can be cast to the compared TIMESTAMP type explicitly:

SELECT CAST(DATE '2020-05-05' as TIMESTAMP WITH LOCAL TIME ZONE) < CAST(TIMESTAMP '2020-05-05 12:00:00' as TIMESTAMP WITH LOCAL TIME ZONE);
--> TRUE

Fix

The comparison of a DATE and TIMESTAMP WITH LOCAL TIMEZONE will be performed using the TIMESTAMP data type. The following query should return true after the fix:

SELECT DATE '2020-05-05' < CAST(TIMESTAMP '2020-05-05 12:00:00' as TIMESTAMP WITH LOCAL TIME ZONE);
-- > Should return true

Changed behavior