Why do query results change when using coalesce on a char field in a where clause?
Your problem is in the data type of some_char
. When a column of type CHAR
is compared to a string, Oracle blank pads the string to the length of the column (see the docs). In the tests you are doing, the values match in length ('1'
vs '1'
) or are completely different ('1'
vs 'abc1'
) so everything works fine. However when you use COALESCE
on a CHAR
field, the output of COALESCE
is the fully blank padded column value returned as a VARCHAR
(see the docs for NVL) and so the comparison string is not blank padded, and you are then comparing '1 '
vs '1'
, which fails. There are a couple of ways to work around this. You can TRIM
the output of COALESCE
i.e.
TRIM(COALESCE(some_char, 'wasNull'))
or change the data type of some_char
to VARCHAR(8)
instead.
I've made a demo of all of this on dbfiddle