SQL scalar function msg 107 after upgrade to 2019

user_1696822 0
Viewed 351

I have a really simple scalar function with the following code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[GetNDate_YYYYMM] 
(
        @InputDate DATETIME
)
RETURNS INT
AS
BEGIN
    DECLARE @RIS AS INT
    SET @RIS=NULL
    IF (@InputDate IS NOT NULL) SET @RIS=(YEAR(@InputDate)*100)+(MONTH(@InputDate))
USCITA:
    RETURN @RIS
END

This function has worked for years in SQL 2012 but now I have migrated the function to SQL 2019 I get the following message:

Msg 107, Level 15, State 1, Procedure GetNDate_YYYYMM, Line 1 [Batch Start Line 0]
The column prefix 'DT0' does not match with a table name or alias name used in the query.

In reality if I run a select on this function from the SQL management studio (and not during a stored procedure, where I first noticed the problem) I get this message only on the first run and then it doesn't appear until I reconnect to the DB.

Thanks for the help, James

sql-server-2019 sql-server-2012 tsql
2 Answers

This looks like a scalar UDF inlining bug in 2019 RTM that has been fixed already in some cumulative update (quite likely CU2 as that fixed the below issue and you have such a label)

UDFs referencing labels without an associated GOTO command return incorrect results (added in Microsoft SQL Server 2019 CU2)

It looks like some internal error that is mistakenly returned to the client but not actually treated as an error.

For the following SQL

BEGIN TRY
SELECT [dbo].[GetNDate_YYYYMM]('1900-01-01') AS FunctionResult
OPTION (RECOMPILE)

SELECT 'After UDF' AS Message

END TRY
BEGIN CATCH
SELECT 'In Catch' AS Message
END CATCH

The output with "Results to Text" selected is

Msg 107, Level 15, State 1, Procedure GetNDate_YYYYMM, Line 1 [Batch Start Line 21]
The column prefix 'DT0' does not match with a table name or alias name used in the query.
Msg 107, Level 15, State 1, Procedure GetNDate_YYYYMM, Line 1 [Batch Start Line 21]
The column prefix 'DT0' does not match with a table name or alias name used in the query.
FunctionResult
--------------
190001

Message
---------
After UDF

So the function result is returned successfully after the error message and execution continues without the CATCH block being reached.

Some possible resolutions to this

  • remove the problem label (USCITA:)
  • add INLINE=OFF to the function to disable inlining
  • upgrade to the latest CU to get the latest bug fixes
user_732260

You could also avoid using a function :P

This returns the same thing :)

DECLARE @InputDate DATETIME = GETDATE()
SELECT FORMAT(@InputDate,'yyyyMM')
--202006

Interesting bug. I ran on my local SQL2019, same error.

Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 (Build 15063: )

The column prefix 'DT0' does not match with a table name or alias name used in the query.

but db<>fiddle works fine https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=100c7cb025acfdeaa3fbf1093e91102b

user_81332280
Related