SQL Server 2014 - Standard Edition (64-bit)
Version 12.0.4100.1
OS: Windows NT 6.3 (9600)  --> Windows Server 2012 R2

I'm in the process of evaluating moving from SS 2008 to SS 2014 and found a problem.

I've created a small bit of code that replicates the problem.

Create the following function. Then change the name to my_splitter_2 and create the function again.

This just takes a CSV string and turns the values into a table.

CREATE FUNCTION dbo.my_splitter_1
(
	@csv_list VARCHAR(MAX)  
)  
RETURNS @ret TABLE (csv_item VARCHAR(100))  
AS  
BEGIN  
  DECLARE @delimiter_position	INT  
  DECLARE @working_csv_list		VARCHAR(MAX)  
  
  SET @delimiter_position	= 0  
  SET @working_csv_list		= @csv_list  
  SET @delimiter_position	= CHARINDEX(',', @working_csv_list)  
  
  WHILE (@delimiter_position <> 0)  
  BEGIN  
    INSERT @ret (csv_item)  
    SELECT LTRIM(RTRIM(LEFT(@working_csv_list, @delimiter_position - 1)))  
  
    SET @working_csv_list = 
		SUBSTRING
		(
			@working_csv_list, 
			@delimiter_position + 1,  
			LEN(@working_csv_list) - @delimiter_position
		)  
  
    SET @delimiter_position = CHARINDEX(',', @working_csv_list)  
  END  
  
  if (@working_csv_list IS NOT NULL)  
  BEGIN  
    INSERT INTO @ret (csv_item)  
	SELECT LTRIM(RTRIM(@working_csv_list))  
  END  
  
  RETURN  
END  

Now run the following

DECLARE @ListValue	VARCHAR(MAX)
DECLARE @value		VARCHAR(MAX)

SET @value		= '2'
SET @ListValue	= '1,2,3,A'

DECLARE @cnt INT

-- Join my_splitter_1 to my_splitter_1
SELECT @cnt = 
(
	SELECT COUNT(*) 
	FROM dbo.my_splitter_1(@value) L1
	  INNER JOIN dbo.my_splitter_1(@ListValue) L2
		ON L1.csv_item = L2.csv_item
)

SELECT @cnt

-- Join my_splitter_1 to my_splitter_2
SELECT @cnt = 
(
	SELECT COUNT(*) 
	FROM dbo.my_splitter_1(@value) L1
	  INNER JOIN dbo.my_splitter_2(@ListValue) L2
		ON L1.csv_item = L2.csv_item
)

SELECT @cnt

-- Join my_splitter_2 to my_splitter_2
SELECT @cnt = 
(
	SELECT COUNT(*) 
	FROM dbo.my_splitter_2(@value) L1
	  INNER JOIN dbo.my_splitter_2(@ListValue) L2
		ON L1.csv_item = L2.csv_item
)

SELECT @cnt

The results are 4 then 1 then 4.

So when the same function is used in the same operation, the wrong result is returned.

Any thoughts on what is going on?

PLEASE don't suggest re-writing code.
I have 10's of thousands of lines of code and I can't go through them all by hand looking to find potential problems.

I got 1,1,1 all 3 times and 1 is correct answer. Make sure to apply SP1 for your SQL Server 2014.

I ran your code with your functions and it returns 1,1,1.

Strange, because I can duplicate the problem.  I'm running Microsoft SQL Server 2014 SP1 - 12.0.4213.0 (X64) Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) [Windows 7].

I get the same result as the OP running the OP's code.  I can also duplicate it with the following simpler example:

use tempdb
go
CREATE FUNCTION dbo.my_splitter_1
(
	@csv_list VARCHAR(MAX)  
)  
RETURNS @ret TABLE (csv_item VARCHAR(100))  
AS  
BEGIN
  IF @csv_list = '2'
    Insert @ret(csv_item) Values ('2')
  ELSE  
    Insert @ret(csv_item) Values ('1'),('2'),('3'),('A')
  RETURN  
END  
go
	SELECT COUNT(*) 
	FROM dbo.my_splitter_1('2') L1
	  INNER JOIN dbo.my_splitter_1('') L2
		ON L1.csv_item = L2.csv_item
-- The above returns 1
-- but the following returns 4
SELECT
	(SELECT COUNT(*)
	FROM dbo.my_splitter_1('2') L1
	  INNER JOIN dbo.my_splitter_1('') L2
		ON L1.csv_item = L2.csv_item)

go

drop function my_splitter_1

The problem seems to only occur on my server when you use the subquery.  As above if you directly select the COUNT it works.  And the problem seems to be only with COUNT(*).  I don't see the problem if I use MAX(L2.csv_item), or MIN(L2.csv_item), or COUNT(L2.csv_item).

I also don't see the problem on Microsoft SQL Server 2012 SP2 - 11.0.5343.0 (X64).

Tom

With your new code I also got incorrect results here. I think I tested on another laptop originally. In any case, it sounds like a SQL Server bug. I didn't re-produce in SQL Server 2012. Also using Windows 7 here (the other laptop is using Wi

Strange, because I can duplicate the problem.  I'm running Microsoft SQL Server 2014 SP1 - 12.0.4213.0 (X64) Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) [Windows 7].

I get the same result as the OP running the OP's code.  I can also duplicate it with the following simpler example:

use tempdb
go
CREATE FUNCTION dbo.my_splitter_1
(
	@csv_list VARCHAR(MAX)  
)  
RETURNS @ret TABLE (csv_item VARCHAR(100))  
AS  
BEGIN
  IF @csv_list = '2'
    Insert @ret(csv_item) Values ('2')
  ELSE  
    Insert @ret(csv_item) Values ('1'),('2'),('3'),('A')
  RETURN  
END  
go
	SELECT COUNT(*) 
	FROM dbo.my_splitter_1('2') L1
	  INNER JOIN dbo.my_splitter_1('') L2
		ON L1.csv_item = L2.csv_item
-- The above returns 1
-- but the following returns 4
SELECT
	(SELECT COUNT(*)
	FROM dbo.my_splitter_1('2') L1
	  INNER JOIN dbo.my_splitter_1('') L2
		ON L1.csv_item = L2.csv_item)

go

drop function my_splitter_1

The problem seems to only occur on my server when you use the subquery.  As above if you directly select the COUNT it works.  And the problem seems to be only with COUNT(*).  I don't see the problem if I use MAX(L2.csv_item), or MIN(L2.csv_item), or COUNT(L2.csv_item).

I also don't see the problem on Microsoft SQL Server 2012 SP2 - 11.0.5343.0 (X64).

Tom

• Proposed as answer by Naomi NModerator Wednesday, September 09, 2015 4:34 PM

Strange, because I can duplicate the problem.  I'm running Microsoft SQL Server 2014 SP1 - 12.0.4213.0 (X64) Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) [Windows 7].

I get the same result as the OP running the OP's code.  I can also duplicate it with the following simpler example:

use tempdb
go
CREATE FUNCTION dbo.my_splitter_1
(
	@csv_list VARCHAR(MAX)  
)  
RETURNS @ret TABLE (csv_item VARCHAR(100))  
AS  
BEGIN
  IF @csv_list = '2'
    Insert @ret(csv_item) Values ('2')
  ELSE  
    Insert @ret(csv_item) Values ('1'),('2'),('3'),('A')
  RETURN  
END  
go
	SELECT COUNT(*) 
	FROM dbo.my_splitter_1('2') L1
	  INNER JOIN dbo.my_splitter_1('') L2
		ON L1.csv_item = L2.csv_item
-- The above returns 1
-- but the following returns 4
SELECT
	(SELECT COUNT(*)
	FROM dbo.my_splitter_1('2') L1
	  INNER JOIN dbo.my_splitter_1('') L2
		ON L1.csv_item = L2.csv_item)

go

drop function my_splitter_1

The problem seems to only occur on my server when you use the subquery.  As above if you directly select the COUNT it works.  And the problem seems to be only with COUNT(*).  I don't see the problem if I use MAX(L2.csv_item), or MIN(L2.csv_item), or COUNT(L2.csv_item).

I also don't see the problem on Microsoft SQL Server 2012 SP2 - 11.0.5343.0 (X64).

Tom

• Proposed as answer by Naomi NModerator Wednesday, September 09, 2015 4:34 PM

Hello Dave,

I can reproduce that problem using Tom's code in a Microsoft SQL Server 2014 Enterprise.

However when changing the database compalibility to 110(2012), the query returns correct 1.

ALTER DATABASE testdb
SET COMPATIBILITY_LEVEL = 110

The execution plan in compatibility 110 and 120 are very different, The culprit should be the new redesigned Cardinality Estimator.

If alter database compatibility_level is not a option, you can rely on traceflag 9481, see this link.

SELECT
	(SELECT COUNT(*)
	FROM dbo.my_splitter_1('2') L1
	  INNER JOIN dbo.my_splitter_1('') L2
		ON L1.csv_item = L2.csv_item)
		OPTION (QUERYTRACEON 9481)

Further more, this issue is not fixed in the latest SQL Server 2016, you can play with 2016 by clicking here(an online lab).

You can submit a feedback to the Microsoft Connect at this link https://connect.microsoft.com/SQLServer/Feedback . Your feedback enables Microsoft to offer the best software and deliver superior services

Dave has created the Connect item, you can find it at https://connect.microsoft.com/SQLServer/Feedback/Details/1769932 if you wish to vote for it.

I have added Eric's two workarounds.

Tom

Thanks for letting us know, I voted.

Nothing to do with the new cardinality estimator.

This bug has existed since at least SQL Server 2008. 

The issue is that when a multistatement TVF is referenced multiple times in the same query with different paramers it can incorrectly be spooled for one call and then the contents of the spool reused for the other call.

This is not the same as https://connect.microsoft.com/SQLServer/feedback/details/781422/unexpected-results-in-sql.  That problem has existed since at least SQL 2008R2. 

This is a new problem and is a regression in SQL 2014.  The code given in my earlier reply works correctly in SQL 2012 and SQL 2008R2, but fails in SQL 2014.

Tom

It's the same issue. The details about the exact execution plan you end up with for a query are cost based and can vary for many reasons including version, trace flags in use, and even hardware characteristics such as amount of RAM. So it may well be the case that in different versions you see one with a spool and the other without.

Using the different cardinality estimator may happen to tip the balance in a cost based decision. In fact the new higher estimated row count from multistatement TVFs may make this plan substantially more likely.

But the actual bug is that it is never valid to store the results for one function call in a spool and then replay it for a different function call that takes different parameter values. This is the thing that needs f

Not the fault of the new cardinality estimator.

This bug has existed since at least SQL Server 2008. Though the new cardinality estimator may make it more likely that you get a plan that hits the bug due to different estimated row counts from TVFs making a spool look more worthwhile.

The issue is that when a multistatement TVF is referenced multiple times in the same query with different paramers it can incorrectly be spooled for one call and then the contents of the spool reused for the other call.

Regarding Tom's simpler example the reason for the difference in behaviour is that the query that works correctly just enters the TP stage - search(0). Wrapping in an outer SELECT seems to prevent this and it goes straight in to the QuickPlan stage - search(1).  This allows other transformation rules to be considered such as GenGbApplySimple.

For demonstration purposes only this disables that rule and shows correct results.

SELECT
    (SELECT COUNT(*)
    FROM dbo.my_splitter_1('2') L1
     INNER JOIN dbo.my_splitter_1('') L2
        ON L1.csv_item = L2.csv_item)
OPTION (QUERYRULEOFF GenGbApplySimple)

Martin Smith: Stack Overflow dba.stackexchange.com 

• Proposed as answer by Naomi NModerator Friday, September 11, 2015 9:11 PM
• Edited by Martin Smith 8 hours 12 minutes ago

It's the same issue. The details about the exact execution plan you end up with for a query are cost based and can vary for many reasons including version, trace flags in use, and even hardware characteristics such as amount of RAM. So it may well be the case that in different versions you see one with a spool and the other without.

Using the different cardinality estimator may happen to tip the balance in a cost based decision. In fact the new higher estimated row count from multistatement TVFs may make this plan substantially more likely.

But the actual bug is that it is never valid to store the results for one function call in a spool and then replay it for a different function call that takes different parameter values. This is the thing that needs fixing.

It is possible to get the same incorrect results in SQL Server 2012 as per SQL Server 2014 by using the "USE PLAN" hint and transplanting the plan from 2014 over to 2012 (and making minor adjustments to remove new attributes) - this shows the bug still exists there. Just the different cost estimations make the plan less likely to be c