Treating MIN and MAX as non-scalar

kalo

i'm getting a EI003 warning (Subquery may return more than one row.) for the following code where only one value would be returned; putting TOP 1 {} ORDER BY round it seems like overkill just to remove warning

DECLARE @foo INT = (
SELECT (MAX([value].[val]))
FROM (VALUES(1),(2),(3))AS[value]([val])
)
SELECT @foo

Jessica R

Hi @kalo,

Thanks for your post!

MIN and MAX are actually considered aggregate functions despite returning one value. Can you kindly add a request to our uservoice forum here - https://redgate.uservoice.com/forums/94413-sql-prompt - for EI003 to allow MIN and MAX?

Jessica R

Thanks @kalo!

I've logged a bug for the issue with the SELECT statement with reference SP-6768.

kalo

Thanks Jessica I shall add a request : it seems I get the warning even when I and assign a variable to the result of a created scalar function

Jessica R

Thanks @kalo!

Would it be possible to get a copy of the create script for one of the scalar functions that are still getting the warning?

kalo

Hi Jessica, seems it occurs only if i wrap use a select in the right hand side of the statement assigning the value so I can remove the warning by removing the SELECT keyword.

Example:

IF EXISTS(SELECT (1) FROM sys.objects [o] WHERE (SCHEMA_NAME([o].[schema_id]) = (N'dbo')) AND [o].[name] = (N'fn_myfunc') AND [o].[type]=(N'FN')) DROP FUNCTION [dbo].[fn_myfunc];
GO
CREATE FUNCTION [dbo].[fn_myfunc] ()
RETURNS NVARCHAR(11)
AS BEGIN
RETURN(N'hello world')
END
GO
;DECLARE @HasWarning nvarchar(11) = (SELECT [dbo].[fn_myfunc]())
;PRINT(@HasWarning)
;DECLARE @NoWarning nvarchar(11) = ([dbo].[fn_myfunc]())
;PRINT(@NoWarning)

kalo

Therefore i can remove the warning from MIN/MAX also by not initializing when i declare the variable , but I'd rather keep to the original syntax style

no squiggly line in this example

DECLARE @foo INT

SELECT @foo = (MAX([value].[val]))
FROM (VALUES(1),(2),(3))AS[value]([val])

SELECT @foo