过滤条件顺序导致不一致问题

背景：派生表中的条件已经过滤掉非法的数据，但是最终查询的时候还是出现报错的问题。
原因：sqlserver在决定生产最终的执行计划的时候会在不改变最终查询结果的前提下会做条件的合并（派生表中的条件和最外层条件处于相同优先级），然后按照cost最优的原则选择条件的执行顺序。

if(object_id('testa','U') is not null) drop table testa;
create table testa (
id int not null identity(1,1) primary key,
val varchar(200) not null );

--Query
set statistics profile off;
set nocount on;
declare @i int=0;
while (@i<100000)
begin
insert into testa(val) 
select newid() II union all 
select newid() II union all 
select newid() II union all 
select newid() II union all 
select newid() II union all 
select newid() II union all 
select newid() II union all 
select newid() II union all 
select newid() II union all 
select newid() ;
set @i+=10;
end

insert into testa(val)
output inserted.id
select 'A' val
union all 
select 'N'
--100001,100002

set nocount off;
set statistics profile on;

--生成副本数据
if(object_id('testb','U') is not null) drop table testb;
select id,val into testb from testa;

--set statistics profile on
select top 10 substring(a.val,1,len(a.val)-34) cut,a.* from testa a join
testb b on a.val=b.val
where b.id > 13254 
and b.id not in (100001,100002)
and substring(a.val,1,len(a.val)-34) ='AA';
--Invalid length parameter passed to the LEFT or SUBSTRING function.
--去掉substring(a.val,1,len(a.val)-34) ='AA' 依然报错

--调整cost
select top 10 substring(a.val,1,len(a.val)-34) cut,a.* from testa a join
testb b on a.val=b.val
where b.id > 99900 
and b.id not in (100001,100002)
and substring(a.val,1,len(a.val)-34) ='AA';
/*
id  val
99909   AA9CBC29-D5A9-4D6D-8DAA-3EF0F1BB5330
*/
--Invalid length parameter passed to the LEFT or SUBSTRING function.
--去掉substring(a.val,1,len(a.val)-34) ='AA' 不报错


select top 10 substring(a.val,1,len(a.val)-34) cut,a.* from testa a join
testb b on a.val=b.val
where b.id > 99900 
and b.id not in (100001,100002)
and substring(b.val,1,len(b.val)-34) ='AA';
--OK

--派生表
select substring(a.val,1,len(a.val)-34) cut,A.*
from (
select a.* from testa a join
testb b on a.val=b.val
where b.id > 99900 
and b.id not in (100001,100002)
) AS A
where substring(a.val,1,len(a.val)-34) ='AA';
--ERROP Invalid length parameter passed to the LEFT or SUBSTRING function.

--CTE
;with A as
(
select a.* from testa a join
testb b on a.val=b.val
where b.id > 99900 
and b.id not in (100001,100002)
) 
select substring(a.val,1,len(a.val)-34) cut,A.*
from  A
where substring(a.val,1,len(a.val)-34) ='AA';
--Invalid length parameter passed to the LEFT or SUBSTRING function.

--解决方法：
--way1 在报错条件前加判断条件预处理
;with A as
(
select a.* from testa a join
testb b on a.val=b.val
where b.id > 99900 
and b.id not in (100001,100002)
) 
select substring(a.val,1,len(a.val)-34) cut,A.*
from  A 
where (case when len(a.val)-34<0 then NULL else substring(a.val,1,len(a.val)-34) end) ='AA';
--way2 增加索引，使条件优先执行
create index IDX_testa on testa(val);
select top 10 substring(a.val,1,len(a.val)-34) cut,a.* from testa a join
testb b on a.val=b.val
where b.id > 99900 
and b.id not in (100001,100002)
and substring(a.val,1,len(a.val)-34) ='AA';
--OK
--索引/执行计划也可能会影响导数据的执行结果
drop index IDX_testa on testa;
--IF create index IDX_testb on testb(val); --ERROR

--SQL改写
--1.派生表转换关联条件（主键走索引）
select top 10 substring(a.val,1,len(a.val)-34) cut,a.* from testa a join
(select a.id  from testa a join testb b on a.val=b.val where b.id > 99900 and b.id not in (100001,100002)) b 
on a.id=b.id
where substring(a.val,1,len(a.val)-34) ='AA';
--OK
--2.派生表转换关联条件（非主键）
select top 10 substring(a.val,1,len(a.val)-34) cut,a.* from testa a join
(select val from testb b where b.id > 99900 and b.id not in (100001,100002)) b 
on a.val=b.val
where substring(a.val,1,len(a.val)-34) ='AA';
--ERROR
--3.hints（hash join 有一定概率使执行计划改变）
select top 10 substring(a.val,1,len(a.val)-34) cut,a.* from testa a left hash join
(select val from testb b where b.id > 99900 and b.id not in (100001,100002)) b 
on a.val=b.val
where substring(a.val,1,len(a.val)-34) ='AA';
--ERROR
--4.cross apply(此处和left join等价)
select top 10 substring(a.val,1,len(a.val)-34) cut,a.* from testa a cross apply
(select val from testb b where b.id > 99900 and b.id not in (100001,100002) and a.val=b.val) b 
where substring(a.val,1,len(a.val)-34) ='AA';
--5.cross apply(交换顺序)
select top 10 substring(a.val,1,len(a.val)-34) cut,a.* 
from  
(select * from testb b where b.id > 99900 and b.id not in (100001,100002) ) AS B 
cross apply (select a.* from testa a where a.val=b.val) as A
where substring(a.val,1,len(a.val)-34) ='AA';
--ERROR
--6.left join 
select top 10 substring(a.val,1,len(a.val)-34) cut,a.* from testa a left join
(select b.* from testb b where b.id > 99900 and b.id not in (100001,100002)) b on a.val=b.val
where substring(a.val,1,len(a.val)-34) ='AA'
and b.id is not NULL;
--ERROR
--7.left join(交换顺序) 
select top 10 substring(a.val,1,len(a.val)-34) cut,a.* 
from  
(select * from testb b where b.id > 99900 and b.id not in (100001,100002) ) AS B 
left join (select a.* from testa a ) as A on a.val=b.val
where substring(a.val,1,len(a.val)-34) ='AA'
and a.id is not null;
--ERROR
--8.distinct
select top 10 substring(a.val,1,len(a.val)-34) cut,a.* from testa a join
(select distinct val from testb b where b.id > 99900 and b.id not in (100001,100002)) b 
on a.val=b.val
where substring(a.val,1,len(a.val)-34) ='AA';
--ERROR
--9.top order
select top 10 substring(a.val,1,len(a.val)-34) cut,a.* from testa a  join
(select top 100 percent val from testb b where b.id > 99900 and b.id not in (100001,100002) order by b.id) b 
on a.val=b.val
where substring(a.val,1,len(a.val)-34) ='AA';
--ERROR
--10.in/exists
select top 10 substring(a.val,1,len(a.val)-34) cut,a.* from testa a
where exists
(select 1 from testb b where b.id > 99900 and b.id not in (100001,100002) and a.val=b.val)
and substring(a.val,1,len(a.val)-34) ='AA';
--Error
--11.集合
select top 10 a.val,substring(a.val,1,len(a.val)-34) cut from (
select val from testa
except
select val from testb where id <= 99900 or id in (100001,100002) --注意条件取反
) AS A
 where substring(a.val,1,len(a.val)-34) ='AA';
 --Error
 --12.case when改写
select top 10 substring(a.val,1,len(a.val)-34) cut,a.* from testa a join
testb b on a.val=b.val
where case when b.id > 99900 and b.id not in (100001,100002) then substring(a.val,1,len(a.val)-34) 
else NULL end='AA';
--OK


--其他思路：
--1.把需要优先执行的SQL中间结果存放到临时表
--2.重写substring()函数

drop table testa;
drop table testb;