4

The function below will fails when I run it the first time with the following error:

8124 (Multiple columns are specified in an aggregated expression containing an outer reference.)

The second time I run it it runs normally.

If I remove *DATEDIFF(D, e.FRADATO , @til) it’s no problem.

ALTER FUNCTION [dbo].[fnc_2019_test] 
    (@m_id INT, @fra DATE, @til DATE)  
RETURNS INT
AS  
BEGIN 
    RETURN
        (SELECT     
             SUM(ISNULL(e.FORBRUK, 0) * DATEDIFF(D, e.FRADATO, @til))
         FROM 
             dbo.mlr_eos_avl e 
         WHERE  
             e.MÅLER_ID = @m_id   
             AND @fra < e.DATO 
             AND DATEADD(D, -1, @til) >= e.FRADATO) 

END

This function works in SQL Server 2008 R2 and SQL Server 2016 without any problem.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
arildh
  • 43
  • 4

1 Answers1

5

SQL Server 2019 is still in CTP and this is a new feature. If you have found a bug in it you should report it to Microsoft so it is fixed before release (done for you here). (Edit this bug has now been marked as fixed, presumably in CU6)

On previous versions if you try and manually inline it you will see the same error (as in the cut down example below). This is because of the limitation discussed here.

WITH T(m_id,fra, til) AS
(
SELECT 1, GETDATE(), GETDATE()
)
SELECT *
FROM T
CROSS APPLY
        (SELECT     
             SUM(DATEDIFF(D, e.FRADATO, til))
         FROM 
             dbo.mlr_eos_avl e 
) CA(result)

Until this inline case is fixed by Microsoft you can use

WITH INLINE = OFF

in the scalar UDF definition to disable inlining of the UDF

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • Thank you. I have voted on your error report. We shall upgrade our 2008R2 server and Scalar UDF inlining is a major reason to go for the 2019 version when it’s ready. Many of our functions is influenced by this problem so I hope they fix it so that we can get the performance gain. – arildh Mar 07 '19 at 07:26
  • @arildh - yep I don't know whether the fix will be to just not inline functions with this issue or to change the behaviour of that group by to allow mixing inner and outer references in an aggregation to give the same semantics as when not inlined. Hopefully the second one but I suspect it may be the first :-( – Martin Smith Mar 07 '19 at 09:57
  • Still there in CTP 2.5. – arildh May 02 '19 at 06:30
  • CU2 for MS SQL Server 2019 solved this issue – Sire Mar 18 '22 at 13:31