The bug that the change execution sequence caused by the common table expression (CTE) is the same as that caused by the WHERE condition order, ctewhere

The bug that the change execution sequence caused by the common table expression (CTE) is the same as that caused by the WHERE condition order, ctewhere
The following simulated CTE Error

/* Test environment Microsoft SQL Server 2008 R2 (RTM)-10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1 )*/

Generate table Tab data:

--> (Roy) generate Partition Number if not object_id ('tab ') is null drop table TabGoCreate table Tab ([Col1] int, [COl2] nvarchar (5) Insert Tabselect 1, n' a, B, C' union allselect 2, n' d, e' union allselect 3, n' F' Go

Method 1: Use CTE to cause a function Error

If object_id ('tempdb .. # Tab ') is not null drop table # Tabselect top 100 ID = Identity (int, 1, 1) into # Tab from syscolumns a, syscolumns bdeclare @ Str varchar (10) = 'a'; with Cteas (Select. col1, COl2 = substring (. col2, B. ID, charindex (',',. col2 + ',', B. ID)-B. ID) from Tab a, # Tab bwhere charindex (',' +. col2, B. ID) = B. ID -- substring (',' +. COl2, B. ID, 1) = ',') select Col1 from Cte where @ str = COl2/* message 537, level 16, status 3. The length parameter passed by row 8th to the LEFT or SUBSTRING function is invalid. */

Method 2: no error is reported when you use a statement directly:

If object_id ('tempdb .. # Tab ') is not null drop table # Tabselect top 100 ID = Identity (int, 1, 1) into # Tab from syscolumns a, syscolumns bdeclare @ Str varchar (10) = 'A' Select. col1from Tab a, # Tab bwhere charindex (',' +. col2, B. ID) = B. ID -- substring (',' +. COl2, B. ID, 1) = ',' and substring (. col2, B. ID, charindex (',',. col2 + ',', B. ID)-B. ID) = @ Str/* Col11 */

Method 3: An error occurred while changing the Where condition order.

If object_id ('tempdb .. # Tab ') is not null drop table # Tabselect top 100 ID = Identity (int, 1, 1) into # Tab from syscolumns a, syscolumns bgodeclare @ Str varchar (10) = 'A' Select. col1from Tab a, # Tab bwhere substring (. col2, B. ID, charindex (',',. col2 + ',', B. ID)-B. ID) = @ Str and charindex (',' +. col2, B. ID) = B. ID -- substring (',' +. COl2, B. ID, 1) = ','

Find the cause from the execution plan

If object_id ('tempdb .. # Tab ') is not null drop table # Tabselect top 100 ID = Identity (int, 1, 1) into # Tab from syscolumns a, syscolumns bgoSET SHOWPLAN_ALL ON; go --. view method 1 execution plan declare @ Str varchar (10) = 'a'; with Cteas (Select. col1, COl2 = substring (. col2, B. ID, charindex (',',. col2 + ',', B. ID)-B. ID) from Tab a, # Tab bwhere charindex (',' +. col2, B. ID) = B. ID -- substring (',' +. COl2, B. ID, 1) = ',') select Col1 from Cte where @ str = COl2go -- B. view method 2 execution plan declare @ Str varchar (10) = 'A' Select. col1from Tab a, # Tab bwhere charindex (',' +. col2, B. ID) = B. ID -- substring (',' +. COl2, B. ID, 1) = ',' and substring (. col2, B. ID, charindex (',',. col2 + ',', B. ID)-B. ID) = @ StrgoSET SHOWPLAN_ALL offgo

Method 1 and method 2 generate an execution plan

Check the operation of the preceding 4th rows and check the execution sequence of the second row.
Method 1: You can see that the statement first executes the condition (@ str = COl2) and then runs (charindex (',' +. col2, B. ID) = B. ID) Method 2: You can see that the statement first executes the condition (charindex (',' +. col2, B. ID) = B. ID), and then execute (substring (. col2, B. ID, charindex (',',. col2 + ',', B. ID)-B. ID) = @ Str)
Method 3 is the same as method 1, and the order of conditions also triggers execution errors.