SQL Server 2019, a function and the error “Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.”

I have encountered additional errors in SQL Server 2019.
Consider a function with following code:

CREATE OR ALTER FUNCTION dbo.fn_Test()

RETURNS INT          

AS 

BEGIN          

    

    DECLARE @Result INT = 0       

    SELECT  @Result = 0

    FROM    (

                SELECT 1 as abc

                UNION ALL

                SELECT 0 as abc

            ) as a

    RETURN @Result        

            

END

GO

SELECT dbo.fn_Test()

In SQL Server 2019 with compatibility level 150
You will get the error below:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

To fix it you can do one of the following:

• Add TOP(1)  :  SELECT  TOP(1) @Result = 0
• Add cast as INT : SELECT  @Result = CAST(0 AS INT)
• Switch to compatibility level 140

Another bug is also a function related,
Consider the following code,

CREATE OR ALTER FUNCTION dbo.fn_Test2()  

RETURNS BIT

AS 

BEGIN      

    DECLARE @Result BIT = 1

    SELECT @Result = 0

    WHERE 1 = 0

             

    RETURN @Result

            

END

GO

SELECT dbo.fn_Test2()

What we except is that the @Result value will be 1
But it is returned as NULL in SQL Server 2019.

To fix it you can do one of the following :

• Add cast as BIT: SELECT  @Result = CAST(0 AS BIT)
• Switch to compatibility level 140