Incorrect query plan result in run-time error when not equal is used. - by Maurice Pelchat

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 778678 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 2/6/2013 9:37:38 AM
Access Restriction Public


When the query below is executed a bug occurs in the substring expression as if the charindex('@', email) <> 0 would fail to avoid those rows for which it evaluates to zero.  The problem occurs in the manner the predicate is built.  The query plan puts into the same evaluation a test for the substring and a test for the charindex splitted in two tests: one for a result <0 and one for a result > 0 joined by an OR.  The substring is being evaluated at the same time in the where and before the charindex test.  So the charindex result <> 0 is not evaluated at the proper time, which lead to the run-time error in the substring.  This occurs only with a CTE.  The bug appears on SQL2008r2 and SQL2012.  I didn't test other platforms.  This query can easily be rewritten to achieve the same result, but it could uncover a bug in query plan builder that may affect many other queries.  


;WITH Usernames
  SUBSTRING(Email, 1, CHARINDEX('@', Email)-1) as Username
  select '' union all
  select '' union all
  select 'admin'
  ) tb (email)
  Email <> ''
  AND (CHARINDEX('@', Email) <> 0) 
select *
from Usernames U
where U.Username = N'nancy.hotte'
Sign in to post a comment.
Posted by Microsoft on 4/25/2013 at 2:30 PM

Thank you for submitting this feedback. After carefully evaluating all of the bugs in our pipeline, we are closing bugs that are by design. The reason is because SQL Server does not guarantee any particular evaluation order between conditions in the WHERE clause, or between conditions in the WHERE clause and expressions in the SELECT clause. If you need to enforce a particular order, you can use a CASE expression.

Thanks again for reporting the product issue and continued support in improving our product.
Gus Apostol, SQL Server Program Manager
Posted by Martin Smith on 2/6/2013 at 12:27 PM
Duplicate of

You need to use CASE

;WITH Usernames
                 WHEN CHARINDEX('@', Email) > 0
                 THEN SUBSTRING(Email, 1, CHARINDEX('@', Email) - 1)
                END AS Username
                 SELECT '' UNION ALL
                 SELECT 'admin') tb (Email)
         WHERE Email <> ''
                AND ( CHARINDEX('@', Email) <> 0 ))
FROM Usernames U
WHERE U.Username = N'nancy.hotte'