sql服务器不正确地重写我的查询？

Question

输入中有一个脏数据。我们正在尝试清理数据集，然后对已清除的数据进行一些计算。

declare @t table (str varchar(10))
insert into @t select '12345' union all select 'ABCDE' union all select '111aa'

;with prep as
(
select *, cast(substring(str, 1, 3) as int) as str_int
from @t
where isnumeric(substring(str, 1, 3)) = 1
)

select * 
from prep
where 1=1
and case when str_int > 0 then 'Y' else 'N' end = 'Y'
--and str_int > 0

最后两行也在做同样的事情。第一个可以工作，但是如果取消第二个注释，它将与Conversion failed when converting the varchar value 'ABC' to data type int.崩溃。

显然，Server正在重写将所有条件混合在一起的查询。我猜它认为'case‘是havy操作，并将其作为最后一步执行。这就是为什么用案例解决问题的原因。

这种行为有任何记录吗？还是一只虫子？

Answer 1

这是Server的一个已知问题，微软并不认为它是一个bug，尽管用户是这样认为的。这两个查询之间的区别是执行路径。一种是在过滤之前进行转换，另一种是在过滤之后进行转换。

Server保留重新排序处理的权限.文档确实将子句的逻辑处理指定为：

1. 从…
2. 在……上面
3. 加入
4. 哪里
5. 按组
6. 用立方体或卷起
7. 有
8. 选择
9. 分异
10. 命令由
11. 顶部

首先对CTE进行逻辑处理(可能是但这里没有明确记录)。逻辑处理意味着什么？这并不意味着运行时错误就会被捕获。它确实决定了编译阶段标识符的范围。

当Server从数据源读取时，它可以在其中添加新变量。这是一个方便的时间来做这件事，因为一切都在记忆中。但是，这可能发生在筛选之前，这就是在发生错误时导致错误的原因。

解决此问题的方法是使用case语句。因此，以下CTE通常会起作用：

with prep as (
      select *, (case when isnumeric(substring(str, 1, 3)) = 1 and str not like '%.%'
                      then cast(substring(str, 1, 3) as int)
                 end) as str_int
      from @t
      where isnumeric(substring(str, 1, 3)) = 1
     )

看起来怪怪的。我觉得雷德蒙也这么认为。Server 2012引入了try_convert() (参见这里)，它在转换失败时返回NULL。

如果您可以指示SQL Server实现CTE，也会有所帮助。在这种情况下，这也可以解决问题。您可以投票决定在Server 这里中添加这样的选项。