Search code examples
oracle-databaseoracle10goracle-sqldeveloperoracle-xe

Different matches when using prepared statements on CHAR(3) column

I had to make a CHAR(1 CHAR) column wider and I forgot to change the column type to VARCHAR2:

DUPLICADO CHAR(3 CHAR)

I noticed the error when my PHP app would no longer find exact matches, e.g.:

SELECT *
FROM NUMEROS
WHERE DUPLICADO = :foo

... with :foo being #4 didn't find the 3-char padded #4 value. However, I initially hit a red herring while debugging the query in SQL Developer because injecting raw values into the query would find matches!

SELECT *
FROM NUMEROS
WHERE DUPLICADO = '#4'

Why do I get matches with the second query? Why do prepared statements make a difference?

Solution

  • To expand a little on my comments, I found a bit in the documentation that explains difference between blankpadded and nonpadded comparison:

    http://docs.oracle.com/database/121/SQLRF/sql_elements002.htm#BABJBDGB

    If both values in your comparison (the two sides of the equal sign) have datatype CHAR or NCHAR or are literal strings, then Oracle chooses blankpadded comparison. That means that if the lengths are different, then it pads the short one with blanks until they are the same length.

    With the column DUPLICADO being a CHAR(3), the value '#4' is stored in the column as three characters '#4 ' (note the blank as third character.) When you do DUPLICADO = '#4' the rule states Oracle will use blankpadded comparison and therefore blankpad the literal '#4' until it has the same length as the column. So it actually becomes DUPLICADO = '#4 '.

    But when you do DUPLICADO = :foo, it will depend on the datatype of the bind variable. If the datatype is CHAR, it will also perform blankpadded comparison. But if the datatype is VARCHAR2, then Oracle will use non-padded comparison and then it will be up to you to ensure to do blankpadding where necessary.

    Depending on client or client language you may be able to specify the datatype of the bind variable and thereby get blankpadded or nonpadded comparison as needed.

    SQL Developer may be a special case that might not allow you to specify datatype - it just possibly might default to bind variables always being datatype VARCHAR2. I don't know sufficient about SQL Developer to be certain about that ;-)