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
SUBSTRING(Email, 1, CHARINDEX('@', Email)-1) as Username
select 'email@example.com' union all
select 'firstname.lastname@example.org' union all
) tb (email)
Email <> 'email@example.com'
AND (CHARINDEX('@', Email) <> 0)
from Usernames U
where U.Username = N'nancy.hotte'