sql - 为什么这个sql会导致类型转换错误？

Question

WITH tb_testl AS （
    SELECT 1 AS id ,'hehe' AS value 
    UNION ALL 
    SELECT 1 AS id, '1' AS value 
    UNION ALL 
    SELECT 2 AS id, '2' AS value 
    UNION ALL 
    SELECT 2 AS id, '2' AS value
), tb_test2 AS (
    SELECT CONVERT(INT , value) AS value FROM tb_testl WHERE id = 2 
)
SELECT * FROM tb_test2 WHERE value = 2;

这个sql会导致错误

Conversion failed when converting the varchar value 'hehe' to data type int.

但表 tb_test2没有在 anthor 表中的值 'hehe' tb_test1 .而且我发现如果我不附加语句 WHERE value = 2;，这个 sql 会运行得很好。 .我试过 ISNUMBERIC功能，但它没有工作。

版本:mssql2008 R2

Answer 1

关于发生这种情况的原因:

有一个Logical Processing Order ，它描述了评估子句的顺序。顺序是:

FROM
ON
JOIN
WHERE
GROUP BY
WITH CUBE or WITH ROLLUP
HAVING
SELECT
DISTINCT
ORDER BY
TOP

您也可以在 SET SHOWPLAN_ALL ON时查看处理顺序.对于这个查询，处理如下:

持续扫描 - 这是 FROM子句，由硬编码值组成，因此是常量。

过滤器 - 这是 WHERE条款。虽然看起来有两个 where 子句( WHERE id = 2 和 WHERE value = 2 )。 SQL Server 对此有不同看法，它考虑单个 WHERE条款:WHERE CONVERT(INT , value) = 2 AND id = 2 .

计算缩放器。这是CONVERT功能在选择。

因为两者 WHERE子句同时执行， hehe值未从 CONVERT 中过滤掉范围。

实际上，查询被简化为类似:

SELECT  CONVERT(INT, tb_testl.value) AS Cvalue
FROM    (
            SELECT  1 AS id
            ,       'hehe' AS value
            UNION ALL
            SELECT  1 AS id
            ,       '1' AS value
            UNION ALL
            SELECT  2 AS id
            ,       '2' AS value
            UNION ALL
            SELECT  2 AS id
            ,       '2' AS value
        ) tb_testl
WHERE   CONVERT(INT, tb_testl.value) = 2
    AND tb_testl.id = 2

这应该澄清为什么会发生错误。

使用 SQL，您无法像 C 等命令式语言那样以相同的方式阅读代码。SQL 代码行不一定(实际上，大多数情况下根本没有)按照写入的顺序执行。在这种情况下，这是一个错误认为内部 where 在外部 where 之前执行。