sql - 在单独的 CTE 中调用具有不同参数的多语句 TVF 显示错误结果

Question

我试图在同一查询中使用不同的参数调用 TVF 两次，但由于某种原因，当我将结果连接在一起时，其中一个结果会掩盖另一个结果。我已将问题简化为这个小例子:

采用此内联 TVF:

CREATE FUNCTION dbo.fnTestErrorInline(@Test INT)
RETURNS TABLE 
AS
RETURN 
(
    SELECT ID, Val
    FROM (VALUES 
        (1, 1, 10), 
        (1, 2, 20), 
        (1, 3, 30), 
        (1, 4, 40), 
        (2, 1, 50), 
        (2, 2, 60), 
        (2, 3, 70), 
        (2, 4, 80)
    ) t(Test, ID, Val)
    WHERE t.Test=@Test
)

以及等效的多行函数:

CREATE FUNCTION dbo.fnTestErrorMultiline(@Test INT)
RETURNS @tbl TABLE (
    ID INT NOT NULL, 
    Val INT NOT NULL
)
AS
BEGIN
    IF @Test=1
    INSERT INTO @tbl (ID, Val) VALUES 
    (1, 10),
    (2, 20),
    (3, 30),
    (4, 40);

    IF @Test=2
    INSERT INTO @tbl (ID, Val) VALUES 
    (1, 50),
    (2, 60),
    (3, 70), 
    (4, 80);

    RETURN 
END

如果我运行此查询:

WITH cte1 AS (
    SELECT ID, SUM(Val) AS Total
    FROM dbo.fnTestErrorInline(1)
    GROUP BY ID
), cte2 AS (
    SELECT ID, SUM(Val) AS Total
    FROM dbo.fnTestErrorInline(2)
    GROUP BY ID
)
SELECT *
FROM cte1 c1
INNER JOIN cte2 c2 ON c1.ID=c2.ID;

结果符合预期:

ID  Total   ID  Total
1   10  1   50
2   20  2   60
3   30  3   70
4   40  4   80

但是当我使用该函数的多行版本时:

WITH cte1 AS (
    SELECT ID, SUM(Val) AS Total
    FROM dbo.fnTestErrorMultiline(1)
    GROUP BY ID
), cte2 AS (
    SELECT ID, SUM(Val) AS Total
    FROM dbo.fnTestErrorMultiline(2)
    GROUP BY ID
)
SELECT *
FROM cte1 c1
INNER JOIN cte2 c2 ON c1.ID=c2.ID;

结果不正确 - cte2 显示与 cte1 相同的值:

ID  Total   ID  Total
1   10  1   10
2   20  2   20
3   30  3   30
4   40  4   40

此外，我仅在存在 GROUP BY 时才会看到此行为。没有它，结果很好。

奇怪的是，如果我向第二个 CTE 添加另一列，它会更改结果:

WITH cte1 AS (
    SELECT ID, SUM(Val) AS Total
    FROM dbo.fnTestErrorMultiline(1)
    GROUP BY ID
), cte2 AS (
    SELECT ID, SUM(Val) AS Total, SUM(Val+0) AS why
    FROM dbo.fnTestErrorMultiline(2)
    GROUP BY ID
)
SELECT *
FROM cte1 c1
INNER JOIN cte2 c2 ON c1.ID=c2.ID;

产量

ID  Total   ID  Total   why
1   50  1   50  50
2   60  2   60  60
3   70  3   70  70
4   80  4   80  80

看来额外的列需要引用 TVF 表中的列 - 那里的常量值不会改变结果。

这是怎么回事？您是否不应该在每个查询中多次调用多线 TVF？

我已经在 SQL Server 2008 R2 和 2012 上对此进行了测试

Answer 1

这是 SQL Server 中的一个已知错误，它可能会错误地假脱机一个 TVF 实例的结果并为另一个实例重播它们(尽管另一个实例具有不同的参数并返回不同的结果)。

该错误已经存在了一段时间，但最近对基数估计器的更改意味着在 2014 年以后更有可能遇到此问题。

查看连接项目..

• Unexpected results in SQL
• Count wrong when function_1 inner joined to function_1 .

注意:执行计划如下所示。

它使用Common Subexpression Spool所有三个突出显示的线轴实际上都是同一个对象，在黄色操作符中插入行，然后在绿色操作符中重播它们。

添加

OPTION (QUERYRULEOFF  GenGbApplySimple, QUERYRULEOFF BuildGbApply)

避免了这个问题，并给出了具有正确结果的不同计划，但这不是我在生产中使用的东西。