首页 > 安全资讯 >

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

15-01-07

以下模拟一下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-b

以下模拟一下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生成的执行计划图



相关文章
最新文章
热点推荐