SELECT 中的 MSSQL cast([varcharColumn] to int) 在 WHERE 子句过滤掉错误值之前被执行 [英] MSSQL cast( [varcharColumn] to int) in SELECT gets executed before WHERE clause filters out bad values

问题描述

create table dbo.Parent (
    Id bigint NOT NULL,
    TypeId int NOT NULL
)

create table dbo.Child (
    Id bigint NOT NULL,
    ParentId bigint NOT NULL,
    TypeId int NOT NULL,
    varcharColumn varchar(300) NULL
)

select cast(c.varcharColumn as int)
from dbo.Parent p (nolock)
    inner join dbo.Child c (nolock)
        on p.Id = c.ParentId
            and c.TypeId = 2
where p.TypeId = 13

休息时间:

由于无法转换为 int 的值，我们得到了转换中断.在这种情况下:123-1".奇怪的是，被转换的值被从最终结果集中过滤掉了.

The break:

We get a cast break due to a value that cannot be converted to an int. In this case: "123-1". The strange thing is that the value being cast gets filtered out of the final result set.

select c.varcharColumn
from dbo.Parent p (nolock)
    inner join dbo.Child c (nolock)
        on p.Id = c.ParentId
            and c.TypeId = 2
where p.TypeId = 13
    and c.varcharColumn = '123-1'

查询计划最终会查看子表并在 where 子句之前实际应用强制转换函数.

The query plan ends up looking at the Child table and actually applying the cast function before the where clause.

我们能够通过在子表上创建一个新索引来解决这个问题(它正在执行 PK 扫描)

We were able to fix this by creating a new index on the child table (it was doing a PK scan)

create index [NCIDX_dbo_Child__TypeId] on dbo.Child (
    TypeId
)
include (
    ParentId,
    varcharColumn
)

它现在首先过滤父表的 where 子句.

It now filters on the parent table's where clause first.

有没有办法在没有额外索引的情况下解决这个问题?再次重申，请不要提出与修复我们的架构相关的任何建议.在这种情况下，这绝对是正确的解决方法.

Is there any way to fix this without the extra index? And again, please refrain from any suggestions related to fixing our schema. That is definitely the proper fix in this case.

我最感兴趣的是理解为什么它在过滤结果集之前应用了强制转换.

I'm mostly interested in understanding why it applied the cast before it filtered the result set.

谢谢

非常感谢 Aaron 和 Gordon.如果我获得超过 15 的代表点数，我会回来并回复您的两个回复.

Many thanks to both Aaron and Gordon. If I ever get more than 15 rep points, I'll come back and up both of your replies.

我们最终需要 Gordon 的回答，因为我们想在视图中使用这个查询.办公室里的一些人对使用 case 语句持谨慎态度，因为他们更喜欢有更多的控制权来确保我们首先有一个较小的结果集(Aaron 的回答)，但是这一切都归结为查看查询计划并检查您的阅读计数.

We ended up needing Gordon's answer since we wanted to use this query in a view. A few folks at the office were wary of using a case statement because they prefer to have more control over ensuring that we have a smaller result set first (Aaron's answer), however it all boils down to looking at the query plan and checking your read counts.

再次感谢大家的回复！

推荐答案

首先，这不是一个明显的设计问题".SQL 是输出的描述性语言，而不是指定如何进行处理的过程语言.一般来说，不能保证处理的顺序，这是一个优势.我可能会说这是一个设计问题，但它是关于 SQL 语句中异常的一般处理.

First, this is not a "glaring design issue". SQL is a descriptive language of the output, not a procedural language that specifies how prcoessing is being done. There is no guarantee of the order of processing, in general, and this is an advantage. I might say there is a design issue, but it is around the general handling of exceptions in SQL statements.

根据 SQL Server 文档 (http://msdn.microsoft.com/en-us/library/ms181765.aspx)，您可以依赖 CASE 语句的计算顺序对于标量表达式.因此，以下应该有效:

According to SQL Server documentation (http://msdn.microsoft.com/en-us/library/ms181765.aspx), you can depend on the order of evauation for a CASE statement for scalar expressions. So, the following should work:

select (case when isnumeric(c.varcharColumn) = 1 then cast(c.varcharColumn as int) end)

或者，更接近int"表达式:

Or, to get closer to an "int" expression:

select (case when isnumeric(c.varcharColumn) = 1 and c.varcharColumn not like '%.%' and c.varcharColumn not like '%e%'
             then cast(c.varcharColumn as int)
        end)

至少您的代码正在执行显式 CAST.当强制转换是隐式的(并且有数百个列)时，这种情况要糟糕得多.

At least your code is doing an explicit CAST. This situation is much nastier when the casts are implicit (and there are hundreds of columns).

这篇关于SELECT 中的 MSSQL cast([varcharColumn] to int) 在 WHERE 子句过滤掉错误值之前被执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！