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!!!

Improve this question
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... Commented Jan 11, 2017 at 9:27

1 Answer 1

Reset to default
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 ... )
Improve this answer
1
  • Thank you very much! After removing all the dead branches from the WHERE clause, the query is roughly 30% of its former size. ;-) Commented Jan 11, 2017 at 14:52

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.