Example of SQL problem

Posted on by John

Here’s an excerpt from an email someone sent me in response to my post previous post Why SQL failed.

…

Your SQL blog article got my attention since I recently had to fix some SQL code that someone else wrote that has been in production for the last 4 years, running on a dataset containing about 3 million records. It performed some compound operations like the following.

SELECT *
FROM TESTTB2
WHERE
(COL1 IN (‘A', ‘B', ‘F')
AND
COL1 NOT IN (‘X','Y','Z')
OR
COL1 <> ‘R')

If the column COL1 contained null(s), then the NOT IN tests did not work as expected.

In the SQL code I was fixing, the SELECT missed 49,000 records that the programmer thought were being selected! The take-away message is that SQL requires attention to detail that often is not realized by the hobbyist programmer.

3 thoughts on “Example of SQL problem”

  1. Daniel Lemire

    Ok. But that’s actually pretty universal. Not specific to SQL.

    That is why we actually, you know, run tests… to make sure our code does what it is supposed to do.

  2. Sarah

    Daniel beat me to it. My immediate thought: Well, doh! Anytime you’re programming something, whether with SQL or any other language – you need to test your code! Devise common scenarios (and null fields are clearly common) and test your code on them before putting it onto production!

  3. Carsten

    You have to know your language…

    well no matter what you just have to know the basics – problems like this are in every programming language.
    For example: in most languages I know an expression like
    if X = true AND Y = true then …
    is processed like:
    1.) first check if X is true – if not fine jump over the rest of the expresseion
    2.) if X is true check if Y is also true – if yes fire the clause after the then if not fine jump over it.

    This allows you to make some nice looking lines like
    if (X != null) && (X.whatever == “abc”) { … }

    So now look at VB – VB checks both X=true and Y = true before deciding what to do – so if you translate the example above to VB you will be in trouble if indeed X is null.
    Solution: VB provides alternative operators like “AndAlso” or “OrElse” that behaves just like the C/C++/C#, … parts – but you have to KNOW both the behavior and the existence of those operators (and in this case most VB-programmers I know don’t both)

    So don’t blame those languages – blame the people using it.

Comments are closed.