T-SQL – What Does (‘*’) = ‘*’ Mean?

sql servert-sql

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

Best Answer

('*') 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 ... )
Related Question