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

Question

I'm seeing some unexpected behavior when querying with a where clause on a coalesced char field in an Oracle database.

It seems that the results of

CASE WHEN COALESCE(char_field, 'some_val') = 'someOtherVal'

are different than the results of

CASE WHEN char_field = 'someOtherVal'

The specific comparisons in which I've noticed this weird output are 'between', 'in', and 'equals'. These are the weird outputs I'm seeing:

1. Between seems to be non-inclusive on the upper end
2. In and equals return false for every comparison

Here's some sql to replicate the weirdness:

CREATE TABLE delete_me( some_char CHAR(8) );

INSERT ALL   
  INTO delete_me (some_char) VALUES ('1')
  INTO delete_me (some_char) VALUES ('2')
  INTO delete_me (some_char) VALUES ('4')
  INTO delete_me (some_char) VALUES ('5')
  INTO delete_me (some_char) VALUES ('abc1')
  INTO delete_me (some_char) VALUES (null)
SELECT 1 FROM DUAL;

SELECT some_char,
  COALESCE(some_char, 'wasNull') AS coalesce_some_char,
  CASE
    WHEN (some_char BETWEEN '1' AND '5')
    THEN 'true'
    ELSE 'false'
  END AS between_1_5,
  CASE
    WHEN (COALESCE(some_char, 'wasNull') BETWEEN '1' AND '5')
    THEN 'true'
    ELSE 'false'
  END AS coalesce_between_1_5,
  CASE
    WHEN (some_char IN ('1', '5'))
    THEN 'true'
    ELSE 'false'
  END AS in_1_5,
  CASE
    WHEN (COALESCE(some_char, 'wasNull') IN ('1', '5'))
    THEN 'true'
    ELSE 'false'
  END AS coalesce_in_1_5,
  CASE
    WHEN (some_char = 'abc1')
    THEN 'true'
    ELSE 'false'
  END AS equals_abc1,
  CASE
    WHEN (COALESCE(some_char, 'wasNull') = 'abc1')
    THEN 'true'
    ELSE 'false'
  END AS coalesce_equals_abc1
FROM delete_me;

I would have expected the output of the comparisons on coalesced fields to match those on non-coalesced fields for all operators except IS NULL.

Does anyone have any idea why these results wouldn't match up?

Answer 1

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