How will the following expression be evaluated to in Oracle?
WHERE nvl(MODIFIEDDATE, TO_DATE('2030/01/01', 'YYYY/MM/DD HH24:MI:SS'))
> TO_DATE('', 'YYYY/MM/DD HH24:MI:SS')
Can anyone explain what this would do? I am not Oracle person.
Let's walk through this in detail:
NVL(MODIFIEDDATE, TO_DATE('2030/01/01', 'YYYY/MM/DD HH24:MI:SS'))
This will return MODIFIEDDATE if it is not NULL. If MODIFIEDDATE is NULL then it will return a DATE value of 2030/01/01 00:00:00 (the time component will default to midnight because it's not specified in the time literal of '2030/01/01'). So this will always return a non-NULL DATE value.
TO_DATE('', 'YYYY/MM/DD HH24:MI:SS')
This will always return NULL. A zero-length string, in Oracle, means NULL. This is by design (it goes back to the very earliest days of Oracle), and is IMO one of the worst design decisions in the Oracle product. But it's what it is - a zero-length string in Oracle is NULL, always and forever. And NULLs are poison, so TO_DATE(NULL, 'YYYY/MM/DD HH24:MI:SS') will return NULL.
So now we've got
SOME_VALID_DATE_VALUE > NULL
This will always return NULL, because any comparison with NULL returns NULL (remember - NULLs are poison). So the WHERE clause becomes
WHERE NULL...
And thus the query which uses this WHERE clause will return no rows.