Non-constant format specification might lead to a crash when using TO_TIMESTAMP to cast DATE type

Detail name	Value
Changelog Number	5451
Type	Bug
Status	Resolved
Affected Versions	EXASOL 6.0.0, EXASolution 5.0.0
Fix Versions	Exasol 6.1.0, EXASOL 6.0.7, Exasol 6.2.0, Exasol 7.0.0
Resolution Date	2020-09-11

Problem

The function TO_TIMESTAMP accepts a format specification. When casting from DATE to TIMESTAMP this specification is not used because the format of the DATE type is already well-defined. If a specification is provided and it is not a constant then this might lead to a crash (internal server error).

Since the format specification is not used in the described case an exception should be thrown instead.

Example
The query

SELECT
	TO_TIMESTAMP(
		TO_DATE(TO_CHAR(20180101), 'YYYYMMDD'),
		COALESCE('YYYYMMDD', 'YYYYMMDD')
	);

leads to an internal server error.

Workaround

Use constant (or no) format specifications for DATE types.

The above query without the unnecessary format specification produces the expected results:

SELECT
	TO_TIMESTAMP(
		TO_DATE(TO_CHAR(20180101), 'YYYYMMDD')
	);

Changed Behavior

With this fix, a format string is no longer accepted for TO_TIMESTAMP(<date>) conversions. The exception "Format is not needed"  is thrown in this case.

Changed behavior

The new exception "Format is not needed" was introduced and is thrown when the function TO_TIMESTAMP is used for converting DATE types. Before this exception was introduced, constant format specifications were tacitly ignored. Non-constant format specifications lead to a crash. With the exception, the specification is not accepted in both cases.