Calling multi-statement TVF with different parameters in separate CTEs showing wrong results

Question

I'm trying to call a TVF twice with different parameters in the same query, but for some reason, when I join the results together, one of the results sort of masks the other. I've reduced my problem down to this small example:

Take this inline 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
)

and an equivalent multiline function:

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

If I run this query:

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;

the results are as expected:

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

but when I use the multiline version of the function:

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;

the results are incorrect - cte2 shows the same values as cte1:

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

Additionally, I only see this behavior when the GROUP BY is present. Without it, the results are fine.

Strangely, if I add another column to the second CTE, it changes the results:

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;

yields

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

It appears the extra column needs to reference a column in the TVF table - a constant value there doesn't change the results.

What's going on here? Are you not supposed to call a multiline TVF more than once per query?

I've tested this on SQL Server 2008 R2 and 2012

Answer 1

This is a known bug in SQL Server where it can incorrectly spool the results for one instance of the TVF and replay them for the other (despite the fact that the other has different parameters and returns different results).

The bug has existed for some time but recent changes to the cardinality estimator mean that in 2014+ it is even more likely to hit this issue.

See connect items..

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

NB: The execution plan looks as below.

It uses a Common Subexpression Spool All three highlighted spools are in fact the same object, in the yellow operator the rows are inserted and then they are replayed in the green operators.

Adding

OPTION (QUERYRULEOFF  GenGbApplySimple, QUERYRULEOFF BuildGbApply)

avoids the issue and gives a different plan with correct results but this is not something I would use in production.