Index Scan fails for filters on expressions of type Timestamp With Local Time Zone
Details
| Detail name | Value |
|---|---|
| Changelog Number | 5150 |
| Type | Bug |
| Status | Resolved |
| Affected Versions | EXASOL 6.0.0 |
| Fix Versions | EXASOL 6.0.5 |
| Resolution Date | 2017-09-27 |
Problem
Index Scan fails for filters on expressions of type Timestamp With Local Time Zone.
create schema support4130; -- 1. create a table that is large enough for not being replicated create table g as (values 1,2,3,4,5,6,7,8,9,10 as t(x)); create table tt as select trunc(to_timestamp(current_timestamp))+rownum-1 ts from (select 1 a from g,g,g,g,g,g); -- there should be no index on the table tt select * from EXA_USER_INDICES where INDEX_SCHEMA='SUPPORT4130'; -- and this should work: select * from tt where ts = trunc(current_timestamp); -- after performing a join: select * from tt a join tt b on a.ts = b.ts; -- an index has been created: select * from EXA_USER_INDICES where INDEX_SCHEMA='SUPPORT4130'; -- now it crashes select * from tt where ts = trunc(current_timestamp);
Workaround
Use cast to timestamp
select * from tt where ts = to_timestamp(trunc(current_timestamp));