主页 / dba / 问题 / 300125
Accepted
J.D.
Asked: 2021-09-25 08:36:54 +0800 CST

为什么添加 WHERE 子句会破坏我的工作查询，并出现错误“查询处理器无法生成查询计划，因为定义了提示......”？

  • 772

我有以下（愚蠢地简化）查询，它利用两个 CTE 引用同一个表并将它们相互连接：

WITH CTE1 AS
(
    SELECT dbo.RemoveNonNumericCharacters(PhoneNumber) AS PhoneNumberCleaned
    FROM PhoneNumbersTable
    GROUP BY dbo.RemoveNonNumericCharacters(PhoneNumber)
),
CTE2 AS
(
    SELECT CTE1.PhoneNumberCleaned
    FROM CTE1
    INNER HASH JOIN PhoneNumbersTable
        ON CTE1.PhoneNumbersCleaned = dbo.RemoveNonNumericCharacters(PhoneNumbersTable.PhoneNumber)
    WHERE PhoneNumbersTable.AreaCode IN (718, 212)
)

SELECT PhoneNumberCleaned
FROM CTE2

注意HASH JOIN里面发生的事情CTE2。到目前为止，这一切都运作良好。

如果我将以下WHERE子句添加到最终SELECT查询中，那么我的整个查询现在变为：

WITH CTE1 AS
(
    SELECT dbo.RemoveNonNumericCharacters(PhoneNumber) AS PhoneNumberCleaned
    FROM PhoneNumbersTable
    GROUP BY dbo.RemoveNonNumericCharacters(PhoneNumber)
),
CTE2 AS
(
    SELECT CTE1.PhoneNumberCleaned
    FROM CTE1
    INNER HASH JOIN PhoneNumbersTable
        ON CTE1.PhoneNumbersCleaned = dbo.RemoveNonNumericCharacters(PhoneNumbersTable.PhoneNumber)
    WHERE PhoneNumbersTable.AreaCode IN (718, 212)
)

SELECT PhoneNumberCleaned
FROM CTE2
WHERE PhoneNumberCleaned = 'SomePhoneNumberInTheResultSet' -- E.g. 7183998888

然后我得到经典错误：

消息 8622，级别 16，状态 1，第 50 行 由于此查询中定义的提示，查询处理器无法生成查询计划。在不指定任何提示且不使用 SET FORCEPLAN 的情况下重新提交查询。

仅当我在WHERE子句中使用的值实际存在于结果集中时，才会发生这种情况。如果我选择任何不存在的值，那么我不会收到上述错误。

现在显然我的例子对于正在发生的事情有点愚蠢，我可以用几种不同的方式重写它来修复它，但我更好奇为什么会发生这种情况？如果 SQL Server 引擎能够生成返回所有记录的查询计划，为什么它无法在该查询计划的末尾为我在WHERE子句中过滤的标量值添加额外的过滤运算符？

这是我的函数的黑盒代码dbo.RemoveNonNumericCharacters（注意我没有写这个）：

CREATE FUNCTION [dbo].[RemoveNonNumericCharacters] (@strText VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
    WHILE PATINDEX('%[^0-9]%', @strText) > 0
    BEGIN
        SET @strText = STUFF(@strText, PATINDEX('%[^0-9]%', @strText), 1, '')
    END
    RETURN @strText
END

另请注意， 中的列PhoneNumber是PhoneNumbersTable类型VARCHAR(20)。

sql-server sql-server-2016

1 个回答

  1. Best Answer

    该问题不包含复制脚本，但由于隐含谓词使连接谓词变得多余，因此经常出现此错误。

    换句话说，查询规范中的逻辑含义将内部联接变成了逻辑叉积（简化后）。这不一定是一件坏事（因为人们倾向于假设交叉产品是）它只是意味着可以通过这种方式简化查询规范。

    哈希连接需要一个相等谓词。在优化器考虑连接实现时，如果查询树中没有合适的谓词，编译会失败并出现错误。在理想情况下，优化器可能不会简化满足提示所需的连接谓词。

    这是我根据问题的文本内容做出的有根据的猜测。如果需要更详细的解释，请提供完整的repro并指定环境。

    作为一个附带问题，除了 Erik Darling 的建议之外，这里是一个适用于 SQL Server 2016的确定性纯数字标量函数：

    CREATE FUNCTION dbo.RemoveNonNumericCharacters
    (@string nvarchar(4000))
RETURNS 
    nvarchar(4000)
WITH 
    SCHEMABINDING, 
    RETURNS NULL ON NULL INPUT
AS
BEGIN
RETURN
    ISNULL(
        CONVERT(nvarchar(4000),
        (
            SELECT This.ch AS [text()]
            FROM OPENJSON(N'[1' + REPLICATE(N',1', LEN(@string) - 1) + N']') AS J
            CROSS APPLY (SELECT 1 + CONVERT(integer, J.[Key])) AS V (v)
            OUTER APPLY (SELECT SUBSTRING(@string COLLATE Latin1_General_100_BIN2, V.v, 1)) AS This (ch)
            WHERE This.ch COLLATE Latin1_General_100_BIN2 LIKE N'[0123456789]'
            ORDER BY V.v
            FOR XML PATH (N'')
        )), 
        N'');
END;

    这可能不是效率的最后一句话，但它很有趣。您可以使用CHARINDEX.

    对于 SQL Server 2017 及更高版本：

    CREATE FUNCTION dbo.RemoveNonNumericCharacters
    (@string nvarchar(4000))
RETURNS 
    nvarchar(4000)
WITH 
    SCHEMABINDING, 
    --INLINE = ON, /* for 2019 */
    RETURNS NULL ON NULL INPUT
AS
BEGIN
    RETURN
        REPLACE(
            TRANSLATE(@string COLLATE Latin1_General_100_BIN2, 
                TRANSLATE(@string COLLATE Latin1_General_100_BIN2, 
                    N'0123456789',
                    N'XXXXXXXXXX'),
                REPLICATE(N'X', LEN(@string))), 
            N'X', N'');
END;

    两者都可以简单地转换为内联表值函数。

    • 6

相关问题