查询优化不会先执行内部查询

Question

运行此查询时：

create table #test (field varchar(100))

insert into #test (field) values ('this is not a number')
insert into #test (field) values ('1234567')

select  *
from    (   select  field
            from    #test
            where   ISNUMERIC(field) = 1
        ) as subquery
where   CAST(field as int) > 100

drop table #test

我期望返回1234567，但实际上却得到了如下结果：

Msg 245, Level 16, State 1, Line 7
Conversion failed when converting the varchar value 'this is not a number' to data type int.

这就像内部查询没有首先执行，而查询优化器对我尝试做什么进行了（不正确的）假设。我不得不使用临时表来解决这个问题：

create table #test (field varchar(100))

insert into #test (field) values ('this is not a number')
insert into #test (field) values ('1234567')

select  field
into    #subquery
from    #test
where   ISNUMERIC(field) = 1

select  *
from    #subquery
where   CAST(field as int) > 100

drop table #subquery
drop table #test

这是SQL查询优化器的一个bug吗？有人能给我解释一下吗？

编辑：针对这个问题是重复的回答...我没有使用'IN'语句。我写的逻辑意味着我需要在应用CAST条件之前返回一个经过过滤的子查询。任何理智的人都会期望我的查询以这种方式运行。优化器版本的查询显然不是逻辑上相同的。

- Jack

---

可能是SQL引擎按照什么顺序执行查询和子查询？的重复问题。 - devlin carnate

顺便提一下：IsNumeric()在使用上是出了名的有问题。 - HABO

Answer 1

SQL Server优化器将命令（T-SQL）作为输入，查看可用资源（索引、统计数据等），并为查询提供最佳执行计划，包括先执行哪个查询/查询部分，将哪些谓词推入查询等。因此，如果您查看此查询的执行计划，谓词被推入子查询，因此int转换失败了。执行计划中只有一个运算符，即表扫描，两个谓词都传递给内部查询。如果您考虑一下，这是有道理的，为什么要从子查询返回更多行，然后再通过外部查询的where子句进行过滤。我认为这不是一个bug，而是优化执行计划的聪明方式 :)

- M.Ali

---

如果您不同意SQL Server的工作方式，可以打开连接项提出建议。但这就是它的工作方式，在大多数情况下运行良好 :) - M.Ali

@jack 我试过了，它运行良好... http://sqlfiddle.com/#!18/d9805/2 ... 但是，就像我说的，当你运行它时，SQL Server会提供最佳可能的计划..所以也许你的实例有一些我们不知道的更多信息。 - Nenad Zivkovic

我已经在SQL 2008R2和2016上尝试了select field from #test where ISNUMERIC(field) = 1 and CAST(field as int) > 100，两者都可以正常执行。有人能解释一下吗？谓词是isnumeric([tempdb].[dbo].[#test].[field])=(1) AND CONVERT(int,[tempdb].[dbo].[#test].[field],0) > (100)。看起来WHERE条件的顺序确实很重要。 - Squirrel

只要在WHERE语句中首先调用ISNUMERIC函数，它就会首先运行并且有效。我已经在SQL 2008、2012和2016上尝试过这种方法。 - Owain Esau

我尝试使用WITH子句执行相同的查询，但仍然无法将无效的数字数据转换为有效数据。 - Anil Soman