1

I have inherited maintenance of an in-house application along with a couple of views in our ERP system's database.

I have begun to suspect that my predecessor was drunk as a skunk when writing these views, or was making the queries deliberately complicated as a form job protection (if so, the joke is on me, because the guy quit).

One of the things that I find highly confusing about the views is that the WHERE-clause contains a sub-clause that looks a little bit like this:

SELECT ...
WHERE ...
OR ( (('*') IS NULL)
     AND Project.Invalid = 0
     AND ... )
OR ...

From my understanding, that ('*') IS NULL part should basically be a no-op. Yet, when I comment that line out, the number of rows returned by the query jumps from 460 to ~350,000.

I am fairly confused by this, I would have expected no change at all in the number of results. In the same query, another branch of the WHERE-clause contained a similar sub-clause, ((-255) IS NULL), and when I commented that one out, the number of results stayed the same.

What is going on here? Is there something funky going on, or am I missing something obvious?

Thank you very, very much for any input!!!

2
  • ('*') IS NULL evaluates to false. Of course if you comment it out, the result changes. Try commenting out the whole OR part (that is also evaluated to false): OR ( (('*') IS NULL) AND Project.Invalid = 0 AND ... ) Commented Jan 11, 2017 at 9:25
  • 1
    Thank you very much! In retrospect, it is so obvious! ;-) I am going to facepalm myself for a while...
    – krylon
    Commented Jan 11, 2017 at 9:27

1 Answer 1

4

('*') IS NULL evaluates to false. As a result the OR part also evaluates to false. We can use the simple rule:

(FALSE AND Something AND Something_Else AND ... ) 
= FALSE

Of course as a result, if you comment out (('*') IS NULL) the result changes.

Try commenting out the whole OR part:

SELECT ...
WHERE ...
-- OR ( (('*') IS NULL)
--      AND Project.Invalid = 0
--      AND ... )
OR ...

This should have no effect, due to the other rule:

(FALSE OR Something OR Something_Else OR ... ) 
= 
(Something OR Something_Else OR ... )
1
  • Thank you very much! After removing all the dead branches from the WHERE clause, the query is roughly 30% of its former size. ;-)
    – krylon
    Commented Jan 11, 2017 at 14:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.