Calling multi-statement TVF with different parameters in separate CTEs showing wrong results
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..
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.
