SQL Server 2019 Query Issue - Scalar Procedure

Question

Hello community.

I have a weird issue that I am not SQL guru enough to figure out.

I have a stored scalar procedure for a database that does a TOP 95 PERCENT function of bandwidth on an interface. with 4 variables of @InterfaceID, @StartDate, @EndDate, @Percentile

---

BEGIN
DECLARE @ResultVar real

SELECT @ResultVar = MAX(In_Averagebps)
FROM (
SELECT TOP(@Percentile) PERCENT In_Averagebps
FROM dbo.InterfaceTraffic WITH (NOLOCK)
WHERE InterfaceID = @InterfaceId AND DateTime >= @StartDate AND DateTime <= @EndDate
ORDER BY In_Averagebps ASC
) AS AA

RETURN @ResultVar

END

---

This works just fine if I run this selection in a query and PRINT out the result.

If I run a normal query against the database and reference the scalar procedure such as:

---

SELECT
dbo.MyScalarProcedure(@InterfaceID, @StartDate, @EndDate, @Percentile) AS Maxbps_In95 (and actually fill in the variables correctly). The query just selects the TOP 95 records instead of actually doing the 95th percent of all records.

I can see using the Query Execution Plan details that when I call upon the procedure it behaves this way. If I right click and select properties on the "Top" function step of the query in the execution plan view, it says "Is Percent" false and is obvisouly selecting 95 of 95 rows.....

I dont know why it works when I run the procedure itself selectively, but when referencing the procedure from a query....it just totally forgets about the whole 95 PERCENT command in the SELECT statement and just grabs the TOP 95 rows.

Answer 1

I agree it's a bug. Here is a reproduction (I simplified the code somewhat). I'm running SQL 2019 CU15.

Use tempdb
go
-- Create table with 200 rows vith values from 1-200
Create Table dbo.InterfaceTraffic(In_Averagebps real);
go
;With cte As
(Select Cast(1 As real) As r
Union All 
Select r+1. As r
From cte
Where r<200)
Insert dbo.InterfaceTraffic(In_Averagebps)
Select r
From cte
Option (Maxrecursion 300);
go

-- Create Function
Create Function dbo.SampleProc() 
Returns real
As
BEGIN
DECLARE @ResultVar real

SELECT @ResultVar = MAX(In_Averagebps)
FROM (
SELECT TOP 95 PERCENT In_Averagebps
FROM dbo.InterfaceTraffic
ORDER BY In_Averagebps ASC
) AS AA
 RETURN @ResultVar
END
go

-- Call to function returns incorrect result 95, should be 190
Declare @ReturnedResult real;
Select dbo.SampleProc() As BadResult
go

-- Executing same code outside of functions returns correct result 190
DECLARE @ResultVar real
SELECT @ResultVar = MAX(In_Averagebps) 
FROM (
SELECT TOP 95 PERCENT In_Averagebps
FROM dbo.InterfaceTraffic
ORDER BY In_Averagebps ASC
) AS AA
Select @ResultVar As GoodResult
go

--Cleanup
Drop Function dbo.SampleProc;
go
Drop Table dbo.InterfaceTraffic;

Tom

Answer 2

Sounds like a bug to me. What is the output of "SELECT @@version"?

I see that you have NOLOCK in that function. Please beware of that with NOLOCK there are many things that can go wrong. Not only can you read uncommitted dirty data, but you can also fail to read data that has been committed, or you may read the same data twice. This can lead to transient error that are difficult to reproduce.

Answer 3

Hi @Virtual-Dude
What I'm confused about this issue is how you referencing the stored procedure in the select statement.Or what you mean is UDF?
Then I tested on my own sqlsever, and the issue you describe does not appear.
So as the experts answered above, I guess it's a bug.

Best regards,
LiHong