I am trying to use COALESCE function to handle default value in WHERE condition like below.
select * from table1 where created_date >= coalesce(null, trunc(now(), 'day'));
But it throws NullPointerException
Caused by: java.lang.NullPointerException
... 47 more
I was able to reproduce the same error with following query
select coalesce(null, now()) as date;
Here are some other variant of same issue
1. select coalesce(now(), now()) as date; // returns 2019-05-14
2. select coalesce(now(), null) as date; // returns empty
3. select coalesce(null, now()) as date; // throws exception
I have tried the same for INT and VARCHAR, same outcome
Am I doing something wrong here or is coalesce suppose to return a non null value ?
thanks and regards,