| Empty-String Comparisons in SQL | KEVIN RESCHENBERG
09-13-2006 |
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...)
|
