一个“神奇”的查询出错

Question

换CTE试试，根据我了解，好像是因为优化器改写了你的查询，导致先读取了那些你以为已经过滤掉的数据

Answer 1

 
不妨运行下面的语句，检查一下看是否存在
   select * from (SELECT RIGHT(f1,4)  AS f FROM Table1 WHERE a=b) a where charindex(')',f)>0

Answer 2

SELECT RIGHT(f1,4)  AS f FROM Table1
这句能返回 '90)' 这样的记录吗？？？

Answer 3

这个情况经常会遇到，据我分析应该是分两步
1，这个语句会变成SELECT CAST(RIGHT(f1,4) AS INT) AS f  FROM Table1 WHERE a=b and CAST(RIGHT(f1,4) AS INT)>0
2，SQLServer是同时计算where条件中的各判断的，所以就会出现这样的错误其他的例子比如：
with t as(
select 1 as col1,0 as col2 union all
select 3 as col1,4 as col2)
select * from (select * from t where col2<>0)t1 where col1/col2<1这种情况的排除方法是用case语句：
with t as(
select 1 as col1,0 as col2 union all
select 3 as col1,4 as col2)
select * from t where case when col2<>0 then 1 else 0 end=1 and col1/col2<1你这个情况这样：
SELECT * FROM table1 where case when a=b then 1 else 0 end=1 and f>0

Answer 4

上面最后一句写错了：
SELECT CAST(RIGHT(f1,4) AS INT) FROM table1 where case when a=b then 1 else 0 end=1 and f>0见《Microsoft SQL Server 2008技术内幕：T-SQL语言》62页的2.5同时操作

Answer 5

如果表中没有包含 '90)' 这样的记录，那就是很“神奇”了。我之前有遇到过这个问题，很少见的。通常认为是先执行Where，再进行数据类型转换。但就是有的时候，会先转换，这样就报错了。原因不明，后来只有改SQL。

Answer 6

优化器会改写你的逻辑查询
如果表中没有包含 '90)' 这样的记录，那就是很“神奇”了。我之前有遇到过这个问题，很少见的。通常认为是先执行Where，再进行数据类型转换。但就是有的时候，会先转换，这样就报错了。原因不明，后来只有改SQL。

Answer 7

谢谢你的意见，不过试验下来好像还是一样的啊
不会啊，按你的：
with table1 as(
select ')90' as f1,1 as a,2 as b,3 as f union all
select '90' as f1,1 as a,1 as b,3 as f)
SELECT * FROM (SELECT CAST(RIGHT(f1,4) AS INT) AS f FROM Table1 WHERE a=b) t WHERE f>0
这一段会出错
但是改成这样：
with table1 as(
select ')90' as f1,1 as a,2 as b,3 as f union all
select '90' as f1,1 as a,1 as b,3 as f)
SELECT CAST(RIGHT(f1,4) AS INT) FROM table1 where case when a=b then 1 else 0 end=1 and f>0
就可以选出90啊，你给个不行的例子看看

Answer 8

谢谢你的意见，不过试验下来好像还是一样的啊
不好意思写错，这样的
with table1 as(
select ')90' as f1,1 as a,2 as b union all
select '90' as f1,1 as a,1 as b)
select CAST(RIGHT(f1,4) AS INT) FROM table1 where case when a=b then 1 else 0 end=1 and CAST(RIGHT(f1,4) AS INT)>0

Answer 9

如果用子查询的方法：
with table1 as(
select ')90' as f1,1 as a,2 as b union all
select '90' as f1,1 as a,1 as b)
SELECT * from 
(select CAST(RIGHT(f1,4) AS INT) as f
FROM table1 
where case when a=b then 1 else 0 end=1) t
where f>0;
也是不行的，是因为经过SQLServer改写后的谓词逻辑变成了：
where CAST(RIGHT(f1,4) AS INT)>0 and case....
只有把case语句显式的写在最前面才可以避免这个问题：
where case when a=b then 1 else 0 end=1 and CAST(RIGHT(f1,4) AS INT)>0