Not Every “Not Equals” is Equal

Yesterday I voted on the poll by @SQL Daily about using different forms for “not equals” conditions and that brought back bad (and funny) memories. Over 25 years I have always used “!=”. “Does it matter”, you might ask? Actually, it shouldn’t. But here is my story and the reason, why I was considering to change my habits and use “<>”.

At one of my customers, I had to re-implement the ETL process for some huge dimensions where the final loading SQL statement was generated dynamically. And I was using Oracle’s quoted string notation. Here I have another habit of using an exclamation mark as a quotation delimiter like this: q'! This is a long SQL-Statement !' So I had something like the following pseudo-code and what’s very important, there was i crucial “not equals” condition in this SQL:

BEGIN
  v_sql := q'! SELECT...
               FROM ... 
               WHERE cnt != 2 !';
  ...
END;
/

That should work just fine and it did! The one special thing about this process was, even though it was carefully tested on development and test systems, it was deliberately planned to deploy it manually, bypassing the DevOps processes. This was the first factor for the disaster.

After the first deployment in a production system, we noticed only after some time, that the loading logic was totally wrong. This meant days of reloading, patching and so on. And of course of searching for the cause. How astonished I was when I saw that in the production code the condition was changed to “equals”! The exclamation mark was not there! Of course I blamed myself first for providing a wrong code somehow, because I just couldn’t believe, there can be another reason.

Well, after double-checking everything, I gave the correct package code with “!=” condition to the colleague, who should deploy that again. Guess what? You’re right! The “!” was removed from production code again! It turned out that the colleague has used a GUI tool (don’t want to name it here), which for some reason thought it would be a good idea to convert all the Q-notations in the code to the “normal” string literals in the background just before sending the code to the database. And made the fatal mistake of removing one exclamation mark too much! What was actually sent to and compiled by the database, was:

BEGIN
  v_sql := ' SELECT...
           FROM ... 
           WHERE cnt = 2 ';
  ...
END;
/

It was absolutely working code, compiled without any errors or warnings, just doing completely wrong thing and we discovered it purely by accident!

The moral of the story: it is not as important what “not equals” condition or quotation marks you are using; more important is to have an established and clean deployment process without manual steps and odd tools in between and to use it every time without any shortcuts.

Maybe it’s worth thinking about applying and comparing some hash functions digests for the code before and after deploying or something like this? If you would ask a security officer at your company, you would probably learn, that you have to ensure the immutability of critical production code anyway. What do you think?

Leave a Reply

Your email address will not be published. Required fields are marked *

*