公用表表达式(CTE)引发的改变执行顺序同WHERE条件顺序引发的bug

来源：互联网发布：flv播放器 mac 编辑：程序博客网时间：2024/06/07 03:53

以下模拟一下CTE出错

/*测试环境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) */

生成表Tab数据：

--> --> (Roy)生成測試數據  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

方法1:用CTE引发函数出错

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     a.Col1,COl2=substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-b.ID) from     Tab a,#Tab bwhere    charindex(',',','+a.Col2,b.ID)=b.ID --也可用 substring(','+a.COl2,b.ID,1)=',')select Col1 from Cte where @str=COl2/*消息 537，级别 16，状态 3，第 8 行传递给 LEFT 或 SUBSTRING 函数的长度参数无效。*/

方法2：直接用语句时不会报错：

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     a.Col1from     Tab a,#Tab bwhere    charindex(',',','+a.Col2,b.ID)=b.ID --也可用 substring(','+a.COl2,b.ID,1)=','and substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-b.ID) =@Str/*Col11*/

方法3:把Where条件换一下顺序也出错

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     a.Col1from     Tab a,#Tab bwhere    substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-b.ID) =@Str and charindex(',',','+a.Col2,b.ID)=b.ID --也可用 substring(','+a.COl2,b.ID,1)=','

查原因从执行计划来找

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--a.查看方法1执行计划declare @Str varchar(10)='a';with Cteas(Select     a.Col1,COl2=substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-b.ID) from     Tab a,#Tab bwhere    charindex(',',','+a.Col2,b.ID)=b.ID --也可用 substring(','+a.COl2,b.ID,1)=',')select Col1 from Cte where @str=COl2go--b.查看方法2执行计划declare @Str varchar(10)='a'Select     a.Col1from     Tab a,#Tab bwhere    charindex(',',','+a.Col2,b.ID)=b.ID --也可用 substring(','+a.COl2,b.ID,1)=','and substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-b.ID) =@StrgoSET SHOWPLAN_ALL offgo

方法1、方法2生成的执行计划图

注意看以上第4行的运算，再查第3行执行顺序
方法1:可看到语句先执行条件(@str=COl2),再执行(charindex(',',','+a.Col2,b.ID)=b.ID)

方法2:可看到语句先执行条件(charindex(',',','+a.Col2,b.ID)=b.ID),再执行(substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-b.ID) =@Str)

方法3同方法1一样原因，条件的顺序也会引发执行出错

3 0