September 13, 2006

Empty-String Comparisons in SQL

Last week I wrote a little about how to evaluate nulls and empty strings in SQR. Coincidentally, at about the same time, I was troubleshooting a delivered App Engine program and found it contained a related, serious bug. A SQL statement was using an empty string in a comparison, like this:

   WHERE FIELD = ''

In Oracle (and all of this discussion is specifically for Oracle), that's a valid clause but it won't select anything, regardless of what data is in the table. Of course, then, we might expect the following to select all of the rows in the table:

   WHERE FIELD <> ''

But that doesn't select any rows either!

Recall that PeopleSoft automatically adds a NOT NULL constraint to character fields when it creates a table. If you try to insert an empty string into a table, Oracle will say that it can't set the field to NULL. But I had a temporary table created outside of App Designer and was able to insert an empty string into a field. It could then be selected by searching for NULL. That's fine, but even in this case, searching for an empty string—exactly what had been inserted—failed to return any rows.

After finding this bug, I became curious and did a search through the entire system (8.9 HRMS/HCM) and found over 150 occurrences of comparisons with empty strings. (I wrote a quick SQR that selected SQLTEXT from PSSQLTEXTDEFN and used INSTR() to find the suspicious patterns.) In most cases the fix would be to change all empty strings to strings containing a space:

   WHERE FIELD = ''   -- Probably wrong
   WHERE FIELD = ' '  -- Probably correct

However, it is possible that some of these SQL statements are working correctly. Sometimes they are coded like this:

   WHERE FIELD = '' OR FIELD = ' '

That works to find all blank fields (although the first part contributes nothing). It may also be that the system works correctly when no rows are returned, and that if rows suddenly were returned from the SQL, unexpected execution paths could be taken. It is possible that two bugs working together can produce correct results! And, of course, most of the 150 occurrences probably are unused—in modules we don't use, or in code that is obsolete or never executed.

How could code like this slip through the testing cycle? Is it possible that the code was tested using a database other than Oracle? More evidence of this came from the fact that the App Engine program tried to copy long character fields using a SQL INSERT INTO/SELECT FROM—something else Oracle won't do. (The irony of it all...)

Until next time...