I knew something was wrong when a couple different developers on the project asked me if I was good at SQL. We have full time DBAs. We have consultants from Oracle. But I still got the call.Apparently a script was not working correctly in development. It worked fine in Production. But a developer needed to do some tests in development and things were going weird. There was a column IDENTIFIER in a table defined as VARCHAR2(5). And they tried to run a query like this:
SELECT COUNT(*)
FROM table
WHERE identifier='00350';
The problem was that the count was returning 0. However there were many records that had the specific identified the WHERE clause was comparing. This was just blowing the mind of the developer. Surprisingly when he removed the single quotes from the WHERE clause, the count came back correctly.
We tried a number of experiments to determine the source of the problem. For example, we inserted a new row with the same identified. Then the count returned 1 (not 51 as we expected). However updating all 51 records setting the identifier to the quoted value did not change the query results. Somebody thought maybe this was due to a bad index on the column. But this column was not a part of any index. We also tried creating another database table with a similarly defined column, but we could not replicate the problem with this other table.
My conclusion was that something went wrong when these 50 records were put into the table. I could not explain why. We loaded the data doing a sql*load like we always do. For now to get the fix out the developer changed the source code to not use quotes. This is just a hack until we can get a grip on this perplexing issue. Any thoughts?
