2

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

James
  • 373
  • 4
  • 14
  • Hi James, have you upgraded your SQL Server 2019 instance to the latest CU? – TT. Jun 24 '20 at 07:24
  • Interesting bug. I ran on my local SQL2019, same error. 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 – Jerry Hung Jun 24 '20 at 07:25
  • 2
    It is the label `USCITA:` that messes things up. Why is that there anyway? But this looks like a fixed bug with scalar UDF inlining. Probably related to [UDFs referencing labels without an associated GOTO command return incorrect results (added in Microsoft SQL Server 2019 CU2)](https://support.microsoft.com/en-gb/help/4538581/fix-scalar-udf-inlining-issues-in-sql-server-2019) – Martin Smith Jun 24 '20 at 07:47
  • @MartinSmith it was the label USCITA, if you put it as an answer I will tick it as correct – James Jun 24 '20 at 09:26
  • 2
    I suspect a scalar function inlining issue. It is probably fixed in a CU since I cannot repro. Without applying the latest CU, you could either add `INLINE=OFF` to the function (i.e. `...RETURNS INT WITH INLINE=OFF AS...` or turn off scalar function inlining at the database level with `USE YourDatabase;ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;`. – Dan Guzman Jun 24 '20 at 10:33

2 Answers2

1

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
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
0

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

Jerry Hung
  • 150
  • 5
  • unfortunately I have that function all over the place and it would be too much to change it evereywhere and i run it on multiple records sometimes and I would have to create a cursor which I would prefer not to – James Jun 24 '20 at 09:08