Custom NLS setting for DATE, TIMESTAMP data type cause data exception, if range index is candidate to be used for index scan.
Details
| Detail name | Value |
|---|---|
| Changelog Number | 9926 |
| Type | Bug |
| Status | Resolved |
| Affected Versions | Exasol 6.2.0, Exasol 7.0.alpha1 |
| Fix Versions | Exasol 6.2.6, Exasol 7.0.alpha2 |
| Resolution Date | 2020-04-16 |
Background
Statements using BETWEEN filters may benefit from using an index to evaluate this filter. To decide if usage of such an index is suitable, some metrics are automatically considered, especially column statistics (providing minimal/maximal value, global number of rows, etc.).
Problem
If custom NLS settings differ from default NLS, minimal/maximal value for TIMESTAMP and DATE data type are not evaluated correctly. This causes a data exception ("invalid character value for cast").
Workaround:
Switch NLS settings back to default.
In case of timestamp data type:
alter session set NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS.FF6';
In case of date data type:
alter session set NLS_DATE_FORMAT='YYYY-MM-DD';
