Why do query results change when using coalesce on a char field in a where clause? Why do query results change when using coalesce on a char field in a where clause? oracle oracle

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