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

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.

HERE IS THE QUERY

;WITH Usernames
as
(
select
SUBSTRING(Email, 1, CHARINDEX('@', Email)-1) as Username
from
(
select 'nancy.hotte@cspo.qc.ca' union all
select 'nancy.hotte@hotmail.com' union all
select 'admin'
) tb (email)
where
Email <> 'nancy.hotte@cspo.qc.ca'
AND (CHARINDEX('@', Email) <> 0)
)
select *
from Usernames U
where U.Username = N'nancy.hotte'