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 Usernamesas (select SUBSTRING(Email, 1, CHARINDEX('@', Email)-1) as Usernamefrom ( select 'firstname.lastname@example.org' union all select 'email@example.com' union all select 'admin' ) tb (email)where Email <> 'firstname.lastname@example.org' AND (CHARINDEX('@', Email) <> 0) )select *from Usernames Uwhere U.Username = N'nancy.hotte'
Operating System Language
Steps to Reproduce