Bizarre SQL Server Errror

Captain

Hey all,

I'm running into a really really weird error with SQL Server. I can provide details about the SQL server if necessary but I'd rather jump right into the description of the problem.

So, I have a stored procedure that is the code-behind for a report. A user reported that the report didn't work, and so I ran the report in Visual Studio with the same parameters, and I got 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.

OK, that's a shitty error. It doesn't even tell me a line number (at least, not a relevant line number).

So I checked every single subselect in the stored procedure and couldn't find one that produced more than one value.

OK.

The point of the stored procedure is to do a select at the end, so I basically copied all of the code out of the sproc and commented out all the fields and tested and tested until it threw the error again.

It finally did, which told me that a specific function was doing the error.

OK.

So I verified that this function was throwing the error by calling

select MyFunction( @MyId, @Etc )

and it threw the error.

OK.

So then I went to the function definition and copied THAT code out. I ran that query and WTF it's fucking working. It's giving me a single row.

OK WTF.

To summarize, I have a function dbo.MyFunction that throws an error when I call it but whose underlying query does not.

Has anybody run into this before? Any idea how to solve it?

Captain

Lightly anonymized function definition:

CREATE FUNCTION dbo.fnFormattedAddressLine (@GroupCode int, @PrimacyCode int, @AddressGroupId int, @AsOfDate datetime)
  RETURNS varchar(100)
AS
  BEGIN
    RETURN
	  ( select trim( concat( dbo.fnNormalizeBlankOrEmptyString( a.AD_STRT_NBR )
                           , case
                               when dbo.fnNormalizeBlankOrEmptyString(a.AD_STRT_NBR) = '' then ''
						       else ' '
                             end
                     	   , Anon.fnNormalizeBlankOrEmptyString(a.AD_STRT_NME)
                           , case
                               when dbo.fnNormalizeBlankOrEmptyString(a.AD_STRT_NME) = '' then ''
						       else ' '
                             end 
					       , case
                               when dbo.fnNormalizeBlankOrEmptyString(a.AD_APT) = '' then ''
                               else ' Apt ' + dbo.fnNormalizeBlankOrEmptyString(a.AD_APT)
                             end
	                       )
                   ) as AddressLine

          from dbo.ADDRESS a with (nolock) inner join ( select max([address].DT_EFCT_START) as DT_EFCT_START
                                                             , [address].ID_ADRSS_GRP
                                                             , [address].CD_GRP
                                                             , [address].CD_GRP_TYPE
                                                          from [address] with (nolock)
                                                         where DT_EFCT_START <= @AsOfDate
		                                                   and ( @AsOfDate <= DT_EFCT_END or DT_EFCT_END is null )
                                                         group by [address].ID_ADRSS_GRP, [address].CD_GRP, [address].CD_GRP_TYPE
                                                      ) as LatestAddress on LatestAddress.ID_ADRSS_GRP = a.ID_ADRSS_GRP
                                                                        and LatestAddress.DT_EFCT_START = a.DT_EFCT_START 	     
                                                                        and LatestAddress.CD_GRP = a.CD_GRP
                                                                        and LatestAddress.CD_GRP_TYPE = a.CD_GRP_TYPE

         where a.CD_GRP = @GroupCode
           and a.CD_GRP_TYPE = @PrimacyCode
		   and a.ID_ADRSS_GRP = @AddressGroupId
		   and a.DT_EFCT_START <= @AsOfDate
		   and ( @AsOfDate <= a.DT_EFCT_END or a.DT_EFCT_END is null )
      )
  END

izzion

@Captain said in Bizarre SQL Server Errror:

Lightly anonymized function definition:

CREATE FUNCTION dbo.fnFormattedAddressLine (@GroupCode int, @PrimacyCode int, @AddressGroupId int, @AsOfDate datetime)
  RETURNS varchar(100)
AS
  BEGIN
    RETURN
	  ( select trim( concat( dbo.fnNormalizeBlankOrEmptyString( a.AD_STRT_NBR )
                           , case
                               when dbo.fnNormalizeBlankOrEmptyString(a.AD_STRT_NBR) = '' then ''
						       else ' '
                             end
                     	   , Anon.fnNormalizeBlankOrEmptyString(a.AD_STRT_NME)
                           , case
                               when dbo.fnNormalizeBlankOrEmptyString(a.AD_STRT_NME) = '' then ''
						       else ' '
                             end 
					       , case
                               when dbo.fnNormalizeBlankOrEmptyString(a.AD_APT) = '' then ''
                               else ' Apt ' + dbo.fnNormalizeBlankOrEmptyString(a.AD_APT)
                             end
	                       )
                   ) as AddressLine

          from dbo.ADDRESS a with (nolock) inner join ( select max([address].DT_EFCT_START) as DT_EFCT_START
                                                             , [address].ID_ADRSS_GRP
                                                             , [address].CD_GRP
                                                             , [address].CD_GRP_TYPE
                                                          from [address] with (nolock)
                                                         where DT_EFCT_START <= @AsOfDate
		                                                   and ( @AsOfDate <= DT_EFCT_END or DT_EFCT_END is null )
                                                         group by [address].ID_ADRSS_GRP, [address].CD_GRP, [address].CD_GRP_TYPE
                                                      ) as LatestAddress on LatestAddress.ID_ADRSS_GRP = a.ID_ADRSS_GRP
                                                                        and LatestAddress.DT_EFCT_START = a.DT_EFCT_START 	     
                                                                        and LatestAddress.CD_GRP = a.CD_GRP
                                                                        and LatestAddress.CD_GRP_TYPE = a.CD_GRP_TYPE

         where a.CD_GRP = @GroupCode
           and a.CD_GRP_TYPE = @PrimacyCode
		   and a.ID_ADRSS_GRP = @AddressGroupId
		   and a.DT_EFCT_START <= @AsOfDate
		   and ( @AsOfDate <= a.DT_EFCT_END or a.DT_EFCT_END is null )
      )
  END

Are you sure the AsOfDate you tested with is the one that's actually getting passed into the function for all possible rows that can be calling the subquery? Since it looks like that's the only parameter that can be causing the fan out.

I don't think that inner join & subquery is really necessary and might not be helping prevent the row fan out. You might be able to do something like:

SELECT TOP 1
	TRIM(
		CONCAT(
			  dbo.fnNormalizeBlankOrEmptyString(a.AD_STRT_NBR)
			, ' ' -- technically the case statement isn't necessary here because it'll trim out if the first one was an empty string
			, dbo.fnNormalizeBlankOrEmptyString(a.AD_STRT_NME)
			, CASE 
				WHEN dbo.fnNormalizeBlankOrEmptyString(a.AD_STRT_NME) = '' 
				THEN '' 
				ELSE ' '
			  END
			, CASE 
				WHEN dbo.fnNormalizeBlankOrEmptyString(a.AD_APT) = ''
				THEN ''
				ELSE ' Apt ' + dbo.fnNormalizeBlankOrEmptyString(a.AD_APT)
		)
	) as AddressLine
FROM dbo.ADDRESS AS a
WHERE a.CD_CRP = @GroupCode
  AND a.CD_GRP_TYPE = @PrimacyCode
  AND a.ID_ADRSS_GRP = @AddressGroupId
  AND a.DT_EFCT_START <= @AsOfDate
  AND ( @AsOfDate <= a.DT_EFCT_END OR a.DT_EFCT_END IS NULL )
ORDER BY a.DT_EFCT_START DESC

Depending on how fugly the Normalize function call is, there may not be any real performance "optimization" in dropping that case between street number & street name but it was a free opportunity to prematurely optimize so there

Tsaukpaetra

@izzion said in Bizarre SQL Server Errror:

Are you sure the AsOfDate you tested with is the one that's actually getting passed into the function for all possible rows that can be calling the subquery?

This. And to help figure that out you might unroll the rows feeding into it into distinct individual selects on that function so you can find which value set is causing it...

boomzilla

@izzion said in Bizarre SQL Server Errror:

Are you sure the AsOfDate you tested with is the one that's actually getting passed into the function for all possible rows that can be calling the subquery? Since it looks like that's the only parameter that can be causing the fan out.

I don't follow that logic. It might be fine for one address but not for another. I'd look for duplicate DT_EFCT_STARTs by:

[address].ID_ADRSS_GRP
[address].CD_GRP
[address].CD_GRP_TYPE

Captain

So one thing I do know is that there were overlapping dates in the address data.

@izzion said in Bizarre SQL Server Errror:

Are you sure the AsOfDate you tested with is the one that's actually getting passed into the function for all possible rows that can be calling the subquery?

Yes. It's actually a variable in the outer sproc so I threw it into the outer sproc's select when I started dissecting the function.

Depending on how fugly the Normalize function call is

I did a good job on writing it, but it handles a lot more cases than your example (there can be "any" number (usually 5) of blank spaces in the field because of craisins).

@boomzilla said:

I'd look for duplicate DT_EFCT_STARTs by:

Yes, that is my thinking too. But then I don't understand why the function version of it breaks and the raw select just returns one row with one field with a properly formatted address line.

I can't exactly normalize all the address data -- the fix "has to" happen in the stored procedure or function.

I am aware that there "were" overlapping dates in this data that's throwing the error, but at the same time, setting an end date resolved the stupid error. (I still need to fix the function -- there are other addresses out there with overlapping dates and this needs to work).

The top 1 idea makes a lot of sense. Or maybe trying the subselect with another join field.

I'll mess around with this a while this morning and probably have more bitching to do later. Thanks everybody.

Oh yeah, of course stackoverflow closed my question.

Captain

OK let the bitching commence.

All the goddamn DT_EFCT_STARTs in the data that are triggering this BS are unique.

izzion

@Captain said in Bizarre SQL Server Errror:

OK let the bitching commence.

All the goddamn DT_EFCT_STARTs in the data that are triggering this BS are unique.

Ultimately, I’d question why this is a scalar function at all. Functions for code reuse / organization in T-SQL are usually somewhere between “oh that’s nice I guess” and bad ideas that become ticking time bombs when you upgrade the server (I suppose I should add the caveat that my specific knowledge is in MS-SQL land, but I’ve definitely been bitten by this type of function design absolutely destroying query performance after a server upgrade even without changing the compatibility level of the database — some query optimizer behaviors are just server-global regardless of database compat level).

I’d be much more inclined to rewrite this into a CTE and put it “inline” with the queries that actually use it, and use the TOP 1 pattern there to ensure no fanout.

Captain

@izzion I'm kind of stuck with the function because of cranberries like my team lead really really wanting it. And it kind of makes sense, I have to do this stupid formatting thing in a bunch of data sets in this report and others.

I suppose I could change the parameters to the actual address parts I want to concatenate instead of doing a lookup by the table's composite key and then doing the concat.

But it really does seem like this isn't the code's fault, right? Like there's some kind of environment issue breaking the code's semantics.

Maybe the reparametrization idea would get around whatever is breaking the semantics. (I.e., if I find the latest address in the outer sproc and pass the address parts into the function)

izzion

@Captain said in Bizarre SQL Server Errror:

@izzion I'm kind of stuck with the function because of cranberries like my team lead really really wanting it. And it kind of makes sense, I have to do this stupid formatting thing in a bunch of data sets in this report and others.

I suppose I could change the parameters to the actual address parts I want to concatenate instead of doing a lookup by the table's composite key and then doing the concat.

But it really does seem like this isn't the code's fault, right? Like there's some kind of environment issue breaking the code's semantics.

Maybe the reparametrization idea would get around whatever is breaking the semantics. (I.e., if I find the latest address in the outer sproc and pass the address parts into the function)

I would put 100% of the blame for this on the code. The code is trying to get the latest effective address as of the specified date, but it's not actually ensuring it gets exactly zero or one address (like the SORT & TOP 1 approach would guarantee). And as a result of the poor design for trying to figure out which address is the right one, it's doing an unnecessary self join which is destroying the query optimizer's ability to figure out how to get the data (as evidenced by the double NOLOCK in the query, which is the SQL equivalent of "ON ERROR CONTINUE NEXT" in terms of rank code smell).

Sure, it might work as long as the data is immaculate. But immaculate data only exists in local development.

Captain

@izzion said in Bizarre SQL Server Errror:

@Captain said in Bizarre SQL Server Errror:

@izzion I'm kind of stuck with the function because of cranberries like my team lead really really wanting it. And it kind of makes sense, I have to do this stupid formatting thing in a bunch of data sets in this report and others.

I suppose I could change the parameters to the actual address parts I want to concatenate instead of doing a lookup by the table's composite key and then doing the concat.

But it really does seem like this isn't the code's fault, right? Like there's some kind of environment issue breaking the code's semantics.

Maybe the reparametrization idea would get around whatever is breaking the semantics. (I.e., if I find the latest address in the outer sproc and pass the address parts into the function)

I would put 100% of the blame for this on the code. The code is trying to get the latest effective address as of the specified date, but it's not actually ensuring it gets exactly zero or one address (like the SORT & TOP 1 approach would guarantee). And as a result of the poor design for trying to figure out which address is the right one, it's doing an unnecessary self join which is destroying the query optimizer's ability to figure out how to get the data (as evidenced by the double NOLOCK in the query, which is the SQL equivalent of "ON ERROR CONTINUE NEXT" in terms of rank code smell).

Sure, it might work as long as the data is immaculate. But immaculate data only exists in local development.

The bosses want the nolocks. (on every table in every lookup...)

And they made me switch from using top 1 to the self-join on max value thing to avoid a really costly sort.

This query is really really simple to be destroying the optimizer...

izzion

@Captain said in Bizarre SQL Server Errror:

@izzion said in Bizarre SQL Server Errror:

@Captain said in Bizarre SQL Server Errror:

@izzion I'm kind of stuck with the function because of cranberries like my team lead really really wanting it. And it kind of makes sense, I have to do this stupid formatting thing in a bunch of data sets in this report and others.

I suppose I could change the parameters to the actual address parts I want to concatenate instead of doing a lookup by the table's composite key and then doing the concat.

But it really does seem like this isn't the code's fault, right? Like there's some kind of environment issue breaking the code's semantics.

Maybe the reparametrization idea would get around whatever is breaking the semantics. (I.e., if I find the latest address in the outer sproc and pass the address parts into the function)

I would put 100% of the blame for this on the code. The code is trying to get the latest effective address as of the specified date, but it's not actually ensuring it gets exactly zero or one address (like the SORT & TOP 1 approach would guarantee). And as a result of the poor design for trying to figure out which address is the right one, it's doing an unnecessary self join which is destroying the query optimizer's ability to figure out how to get the data (as evidenced by the double NOLOCK in the query, which is the SQL equivalent of "ON ERROR CONTINUE NEXT" in terms of rank code smell).

Sure, it might work as long as the data is immaculate. But immaculate data only exists in local development.

The bosses want the nolocks.

And they made me switch from using top 1 to the self-join on max value thing to avoid a really costly sort.

This query is really really simple to be destroying the optimizer...

Self joins are usually instant query plan explosions (especially on SQL 2016+). And that risk is much higher with the two overlapping WHERE clauses in the two pieces of this query. I'd expect to see a function like this causing 1-2 full scans of the address table per row in the original query and then result in the NOLOCK becoming necessary in order to prevent the query from causing massive blocking on the Address table.

Obviously, my psychic troubleshooting might be wrong. But in my experience, the self join is a massive code smell, and the NOLOCK is a much worse code smell. And if the sort is causing a performance problem, the better solution would be to add an index on DT_EFCT_START,DT_EFCT_END

Edit: There might be some potential for an indexed view in terms of maintaining some level of "separate function" organization while being more of a set-based data structure that SQL will perform better with. But if the underlying dataset has cases where a given [address].ID_ADRSS_GRP, [address].CD_GRP, [address].CD_GRP_TYPE doesn't have any active addresses (the latest address has a DT_EFCT_END and no superseding address exists), then it gets pretty clunky to write the view -- and even if that case doesn't exist, you'd still wind up "moving" the AsOfDate logic up into the original query/queries.

Captain

@izzion said in Bizarre SQL Server Errror:

@Captain said in Bizarre SQL Server Errror:

@izzion said in Bizarre SQL Server Errror:

@Captain said in Bizarre SQL Server Errror:

@izzion I'm kind of stuck with the function because of cranberries like my team lead really really wanting it. And it kind of makes sense, I have to do this stupid formatting thing in a bunch of data sets in this report and others.

I suppose I could change the parameters to the actual address parts I want to concatenate instead of doing a lookup by the table's composite key and then doing the concat.

But it really does seem like this isn't the code's fault, right? Like there's some kind of environment issue breaking the code's semantics.

Maybe the reparametrization idea would get around whatever is breaking the semantics. (I.e., if I find the latest address in the outer sproc and pass the address parts into the function)

I would put 100% of the blame for this on the code. The code is trying to get the latest effective address as of the specified date, but it's not actually ensuring it gets exactly zero or one address (like the SORT & TOP 1 approach would guarantee). And as a result of the poor design for trying to figure out which address is the right one, it's doing an unnecessary self join which is destroying the query optimizer's ability to figure out how to get the data (as evidenced by the double NOLOCK in the query, which is the SQL equivalent of "ON ERROR CONTINUE NEXT" in terms of rank code smell).

Sure, it might work as long as the data is immaculate. But immaculate data only exists in local development.

The bosses want the nolocks.

And they made me switch from using top 1 to the self-join on max value thing to avoid a really costly sort.

This query is really really simple to be destroying the optimizer...

Self joins are usually instant query plan explosions (especially on SQL 2016+). And that risk is much higher with the two overlapping WHERE clauses in the two pieces of this query. I'd expect to see a function like this causing 1-2 full scans of the address table per row in the original query and then result in the NOLOCK becoming necessary in order to prevent the query from causing massive blocking on the Address table.

Obviously, my psychic troubleshooting might be wrong. But in my experience, the self join is a massive code smell, and the NOLOCK is a much worse code smell. And if the sort is causing a performance problem, the better solution would be to add an index on DT_EFCT_START,DT_EFCT_END

Sorry, I'm not even trying to argue with you. I really appreciate your help.

I just find it so confusing and frustrating that the "same" query throws an error in one context and not another. (And that is way more interesting to me than actually fixing this POS )

And I don't see why a slow and shitty query plan would do that unless it was actually triggering buggy behavior in the server. If the dt_efct_starts are unique then the runtime should be able to pick a single maximum one and then the self-join on that composite PK should just do its thing, slow as it may be.

I got rid of the nolocks and the function worked correctly, with no error.

I agree changing to a more robust approach is reasonable.

izzion

@Captain said in Bizarre SQL Server Errror:

@izzion said in Bizarre SQL Server Errror:

@Captain said in Bizarre SQL Server Errror:

@izzion said in Bizarre SQL Server Errror:

@Captain said in Bizarre SQL Server Errror:

@izzion I'm kind of stuck with the function because of cranberries like my team lead really really wanting it. And it kind of makes sense, I have to do this stupid formatting thing in a bunch of data sets in this report and others.

I suppose I could change the parameters to the actual address parts I want to concatenate instead of doing a lookup by the table's composite key and then doing the concat.

But it really does seem like this isn't the code's fault, right? Like there's some kind of environment issue breaking the code's semantics.

Maybe the reparametrization idea would get around whatever is breaking the semantics. (I.e., if I find the latest address in the outer sproc and pass the address parts into the function)

I would put 100% of the blame for this on the code. The code is trying to get the latest effective address as of the specified date, but it's not actually ensuring it gets exactly zero or one address (like the SORT & TOP 1 approach would guarantee). And as a result of the poor design for trying to figure out which address is the right one, it's doing an unnecessary self join which is destroying the query optimizer's ability to figure out how to get the data (as evidenced by the double NOLOCK in the query, which is the SQL equivalent of "ON ERROR CONTINUE NEXT" in terms of rank code smell).

Sure, it might work as long as the data is immaculate. But immaculate data only exists in local development.

The bosses want the nolocks.

And they made me switch from using top 1 to the self-join on max value thing to avoid a really costly sort.

This query is really really simple to be destroying the optimizer...

Self joins are usually instant query plan explosions (especially on SQL 2016+). And that risk is much higher with the two overlapping WHERE clauses in the two pieces of this query. I'd expect to see a function like this causing 1-2 full scans of the address table per row in the original query and then result in the NOLOCK becoming necessary in order to prevent the query from causing massive blocking on the Address table.

Obviously, my psychic troubleshooting might be wrong. But in my experience, the self join is a massive code smell, and the NOLOCK is a much worse code smell. And if the sort is causing a performance problem, the better solution would be to add an index on DT_EFCT_START,DT_EFCT_END

Sorry, I'm not even trying to argue with you. I really appreciate your help.

I just find it so confusing and frustrating that the "same" query throws an error in one context and not another. (And that is way more interesting to me than actually fixing this POS )

And I don't see why a slow and shitty query plan would do that unless it was actually triggering buggy behavior in the server. If the dt_efct_starts are unique then the runtime should be able to pick a single maximum one and then the self-join on that composite PK should just do its thing, slow as it may be.

Yeah, to get the row fanout, there has to be a DT_EFCT_START,ID_ADRSS_GRP,CD_GRP,CD_GRP_TYPE combination that is duplicated. My naïve assumption is that the row(s) you tried this with were not the actual row causing the problem, but scalar sub-functions like this are a bitch to debug no matter what (and hence my hatred of them )

izzion

@Captain said in Bizarre SQL Server Errror:

I got rid of the nolocks and the function worked correctly, with no error.

Ah, then you may have run into this: https://www.brentozar.com/archive/2021/11/nolock-is-bad-and-you-probably-shouldnt-use-it/

boomzilla

@izzion said in Bizarre SQL Server Errror:

scalar sub-functions like this

I guess you could return it as a table, right? And just join on it in your query?

izzion

@boomzilla said in Bizarre SQL Server Errror:

@izzion said in Bizarre SQL Server Errror:

scalar sub-functions like this

I guess you could return it as a table, right? And just join on it in your query?

I wouldn't expect returning it as a table to change any of the resulting query plan, due to the way the AsOfDate parameter is functioning and trying to guard against overlapping DT_EFCT date ranges or all addresses being past their DT_EFCT_END date on the AsOfDate. But I'm not 100% sure on that (and I guess me arguing for "make it a CTE" would kind of point to it being able to be a joinable table query some way or another, so I guess revise my opinion to definitely maybe).

boomzilla

@izzion said in Bizarre SQL Server Errror:

@boomzilla said in Bizarre SQL Server Errror:

@izzion said in Bizarre SQL Server Errror:

scalar sub-functions like this

I guess you could return it as a table, right? And just join on it in your query?

I wouldn't expect returning it as a table to change any of the resulting query plan,

No, it wasn't meant to address that, just get past the multiple results from a subquery problem.

due to the way the AsOfDate parameter is functioning and trying to guard against overlapping DT_EFCT date ranges or all addresses being past their DT_EFCT_END date on the AsOfDate. But I'm not 100% sure on that (and I guess me arguing for "make it a CTE" would kind of point to it being able to be a joinable table query some way or another, so I guess revise my opinion to definitely maybe).

Oh, I totally agree with you about using a CTE for performance reasons.