首页 > 代码库 > 公用表(CTE)表达式引发的改变执行顺序同WHERE条件顺序引发的bug
公用表(CTE)表达式引发的改变执行顺序同WHERE条件顺序引发的bug
以下模拟一下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 Tab Go Create table Tab([Col1] int,[COl2] nvarchar(5)) Insert Tab select 1,N‘a,b,c‘ union all select 2,N‘d,e‘ union all select 3,N‘f‘ Go
方法1:用CTE引发函数出错
if object_id(‘Tempdb..#Tab‘) is not null drop table #Tab select top 100 ID=Identity(int,1,1) into #Tab from syscolumns a,syscolumns b declare @Str varchar(10)=‘a‘ ;with Cte as ( Select a.Col1,COl2=substring(a.Col2,b.ID,charindex(‘,‘,a.Col2+‘,‘,b.ID)-b.ID) from Tab a,#Tab b where 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 #Tab select top 100 ID=Identity(int,1,1) into #Tab from syscolumns a,syscolumns b declare @Str varchar(10)=‘a‘ Select a.Col1 from Tab a,#Tab b where 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 /* Col1 1 */
方法3:把Where条件换一下顺序也出错
if object_id(‘Tempdb..#Tab‘) is not null drop table #Tab select top 100 ID=Identity(int,1,1) into #Tab from syscolumns a,syscolumns b go declare @Str varchar(10)=‘a‘ Select a.Col1 from Tab a,#Tab b where 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 #Tab select top 100 ID=Identity(int,1,1) into #Tab from syscolumns a,syscolumns b go SET SHOWPLAN_ALL ON; go --a.查看方法1执行计划 declare @Str varchar(10)=‘a‘ ;with Cte as ( Select a.Col1,COl2=substring(a.Col2,b.ID,charindex(‘,‘,a.Col2+‘,‘,b.ID)-b.ID) from Tab a,#Tab b where charindex(‘,‘,‘,‘+a.Col2,b.ID)=b.ID --也可用 substring(‘,‘+a.COl2,b.ID,1)=‘,‘ ) select Col1 from Cte where @str=COl2 go --b.查看方法2执行计划 declare @Str varchar(10)=‘a‘ Select a.Col1 from Tab a,#Tab b where 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 go SET SHOWPLAN_ALL off go
方法1、方法2生成的执行计划图
注意看以上第4行的运算,再查第3行执行顺序
方法1:可看到语句先执行条件(@str=COl2),再执行(charindex(‘,‘,‘,‘+a.Col2,b.ID)=b.ID)
方法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一样原因,条件的顺序也会引发执行出错
公用表(CTE)表达式引发的改变执行顺序同WHERE条件顺序引发的bug
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。