Q106133 - BUG: IF Statement with Two EXISTS Separated by OR Clause

BUG: IF Statement with Two EXISTS Separated by OR Clause

Q106133

The information in this article applies to:

Microsoft SQL Server version 4.2x


BUG# NT:  544 (4.2)

SYMPTOMS

If one of the SELECT statements is against an empty tableA query that issues an IF EXISTS statement with an OR clause can process incorrectly. The basic logic looks like:


   If exists (SELECT f1 FROM table1 WHERE f1 = "z") OR
      exists (SELECT f1 FROM table2 WHERE f1 = "a")
         select "TRUE"
   else
         select "FALSE"

If table1 is empty and table2 has a row that matches the select, the statement should return TRUE; however, it returns FALSE. If a row is added to table1, even though it does not meet the search criteria, the IF EXISTS statement will process correctly.

CAUSE

SQL Server is not correctly evaluating the IF statement when one or more of the tables has no rows.

WORKAROUND

This problem can be avoided by making sure that both tables contain at least one row. An alternate method is to restructure the IF statement to evaluate one of the EXISTS at a time. In the example given above, it would look like the following:


   If exists (SELECT f1 FROM table1 WHERE f1 = "z")
      select "TRUE"
   else if exists (SELECT f1 FROM table2 WHERE f1 = "a")
      select "TRUE"
   else
      select "FALSE"

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 4.2.

Additional query words: transact-SQL Windows NT

Keywords : kbprogramming kbSQLServ
Issue type :
Technology : kbSQLServSearch kbAudDeveloper kbSQLServ420OS2