Function NULLIF(expr1,expr2) doesn't work correctly in all cases

Detail name	Value
Changelog Number	6119
Type	Bug
Status	Resolved
Affected Versions	EXASOL 6.0.0, Exasol 6.1.0
Fix Versions	Exasol 6.0.16, Exasol 6.1.3
Resolution Date	2019-07-12

Problem Description

For evaluation of NULLIF(expr1,expr2), an equality check is performed. In the case when data types are different, an implicit CAST is performed, not always producing the expected result.

Example

SELECT nullif(TIMESTAMP'2001-01-01 00:00:00' , '2001-01-01') from dual;
-- returns 2001-01-01 00:00:00.000000 instead of NULL

Worarounds

There are two possible workarounds:

1 - CAST explicitly expressions to the same data type:

SELECT nullif(TIMESTAMP'2001-01-01 00:00:00' , CAST('2001-01-01' as TIMESTAMP)) from dual;

2 - Use equivalent CASE expression:

SELECT CASE WHEN expr1=expr2 THEN NULL ELSE expr1 END from dual;