Fix wrong results from UNION elimination with filter on TIMESTAMP WITH LOCAL TIME ZONE column

Detail name	Value
Changelog Number	21578
Type	Bug
Status	Resolved
Affected Versions	Exasol 7.1.0, Exasol 8.0.0
Fix Versions	Exasol 8.31.0, Exasol 8.29.2, Exasol 7.1.30
Resolution Date	2024-08-29

Description

Exasol has an optimization that eliminates UNION ALL branches where it knows from the column statistics that the table has no rows satisfying a predicate in the query.

For TIMESTAMP WITH LOCAL TIME ZONE columns, the check for the elimination does not apply the local time zone change. If the range of timestamps in a union branch table falls between the timestamp with and without the local time zone change then it can eliminate the table when it should not, leading to wrong results.

Example

-- Setup
alter session set time_zone = '1';

create schema S;
create table T1 (C1 timestamp with local time zone);
insert into T1 values ('1111-11-11 11:11:11');
create table T2 (C1 timestamp with local time zone);
insert into T2 values ('1234-05-06 07:07:09');

-- Expected: 2 rows, values (1234-05-06 07:07:09), (1111-11-11 11:11:11)
-- Observed: 1 row, values (1234-05-06 07:07:09)
with W as (table T1 union all table T2)
select * from W
where C1 > cast('1111-11-11 11:11:00' as timestamp with local time zone);

Workaround

Cast both sides of the predicate to TIMESTAMP:

-- Expected/Observed: 2 rows, values (1234-05-06 07:07:09), (1111-11-11 11:11:11)
with W as (table T1 union all table T2)
select * from W
where cast(C1 as timestamp) > cast(cast('1111-11-11 11:11:00' as timestamp with local time zone) as timestamp);

Fix

The bug is fixed.