“Fun” with CHAR fields

I always enjoy learning new things.. especially when it is something that comes as a complete surprise. A developer was updating some code from using literals to instead use bind variables. So far, fantastic! However, during testing, the code no longer worked as expected. Before, when using literals, it returned rows. Now that it used bind variables, no rows were returned. I checked the code and everything looked right. So I was quite puzzled. After a bit of digging, it turns out that one of the columns in the WHERE clause was of type CHAR(10). However, the data that was in it was not always 10 characters, so some of the rows would contain trailing spaces. So now I understood why the query was not returning rows now. However, I was still confused how this could have been working before. I came up with a simple test to see if I could reproduce this issue:

Make a table with 2 columns, one VARCHAR2(10), one CHAR(10):

SQL> create table x_cm2
(c_varchar varchar2(10), c_char char(10))
Table created.

Add some test data. This is a query I like to use when I need a row for each month of the year:

SQL> insert into x_cm2(c_varchar, c_char)
select str, str
from (
    select to_char(add_months(trunc(sysdate, 'YY'), rownum-1), 'Mon') str 
    from dual
    connect by level <= 12
)
12 rows created.

Now let's run some tests. First we will test with the bind variables:

SQL> var mon varchar2(10)
SQL> exec :mon := 'Dec'
PL/SQL procedure successfully completed.
SQL> select *
from x_cm2
where c_varchar = :mon

C_VARCHAR  C_CHAR    
---------- ----------
Dec        Dec       
1 row selected.

SQL> select *
from x_cm2
where c_char = :mon
no rows selected.

So this is behaving like the new code... exactly how I would expect. The query returns rows when filtering against the VARCHAR column, but not the CHAR column. This is because the CHAR column would have 7 trailing spaces at the end. So far, so good.

Now lets try with a literal:

SQL> select *
from x_cm2
where c_varchar = 'Dec'

C_VARCHAR  C_CHAR    
---------- ----------
Dec        Dec       
1 row selected.

SQL> select *
from x_cm2
where c_char = 'Dec'

C_VARCHAR  C_CHAR    
---------- ----------
Dec        Dec       
1 row selected.

So with a literal, both queries return a row. What is going on? As is usually the case, the documentation had the answer (emphasis mine):

With blank-padded semantics, if the two values have different lengths, then Oracle first adds blanks to the end of the shorter one so their lengths are equal. Oracle then compares the values character by character up to the first character that differs. The value with the greater character in the first differing position is considered greater. If two values have no differing characters, then they are considered equal. This rule means that two values are equal if they differ only in the number of trailing blanks. Oracle uses blank-padded comparison semantics only when both values in the comparison are either expressions of data type CHAR, NCHAR, text literals, or values returned by the USER function.

So it turns out this is expected behavior... just not the behavior I would have expected.