SQL 2019 Bug : "SELECT @local_variable"

Question

Post reply

SQL 2019 Bug : "SELECT @local_variable"

FHoornaert

SSC Enthusiast

Points: 181
More actions
February 4, 2020 at 11:47 am

#3722879
Hi,
It seems that the statement "SELECT @local_variable (Transact-SQL)" no longer works as it is documented by SQL Docs an now returns incorrect data.
According to SQL Docs : https://docs.microsoft.com/en-us/sql/t-sql/language-elements/select-local-variable-transact-sql?view=sql-server-ver15
SELECT @local_variable is typically used to return a single value into the variable. However, when expression is the name of a column, it can return multiple values. If the SELECT statement returns more than one value, the variable is assigned the last value that is returned.
Below code sample show the difference between SQL 2016 result & SQL 2019, just by changing the database compatibility level.
It now seems to return the first value instead of the last value.
Is this a known bug, and is there any fix for this issue ?
Code sample - tested on Microsoft SQL Server 2019 (RTM-CU1) (KB4527376) - 15.0.4003.23 (X64) Dec 6 2019 14:53:33
```
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

use master
go

if object_id('dbo.fnTest') is not null
  exec ('drop function [dbo].[fnTest]');
go

CREATE FUNCTION [dbo].[fnTest]()
RETURNS int
AS
BEGIN
       declare @myNumber INT;
       select  @myNumber = Column1 
       from (select 1 union select 2 union select 3  union select 4) as testTable(Column1)
       order by column1;
       RETURN ISNULL(@myNumber, 0);
END
GO

ALTER DATABASE [master] SET COMPATIBILITY_LEVEL = 130
GO
SELECT dbo.fnTest() -- returns 4 : expected value in SQL 2016
GO

ALTER DATABASE [master] SET COMPATIBILITY_LEVEL = 150
GO
SELECT dbo.fnTest() -- returns 1 in SQL 2019 -> incorrect, should be 4
GO
```
3 DataBase Admins walked into a NoSQL bar...
A little while later, they walked out because they couldn't find a table.

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 1

looks like it's the function

if you just run

       declare @myNumber INT;
       select  @myNumber = Column1 
       from (select 1 union select 2 union select 3  union select 4) as testTable(Column1)
       order by column1;
   SELECT @myNumber

then you get 4

but I've got to say... if you put an order by any query then you need to specify ASC or DESC otherwise you are letting the optimiser choose for you

must be a change in the way functions generate plans in 2019 mode

It is however an odd bug

MVDBA

FHoornaert SSC Enthusiast Points: 181 More actions · Answer 2

Thx Mike,

You're right, when I execute below on SQL 2016 & SQL 2019, both return the same value.

So it's something with the function, and not the SQL body.

--CREATE FUNCTION [dbo].[fnTest]()
--RETURNS int
--AS
--BEGIN
       declare @myNumber INT;
       select  @myNumber = Column1 
       from (select 1 union select 2 union select 3  union select 4) as testTable(Column1)
       order by column1 ASC;
       SELECT ISNULL(@myNumber, 0);
       --return ISNULL(@myNumber, 0);
--END
--GO

Regarding the sort order, it should be default ASC when it's not specified.

SELECT - ORDER BY Clause (Transact-SQL) - https://docs.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-ver15

ASC | DESC
Specifies that the values in the specified column should be sorted in ascending or descending order. ASC sorts from the lowest value to highest value. DESC sorts from highest value to lowest value. ASC is the default sort order. Null values are treated as the lowest possible values.

It would be silly if the optimizer chooses the sort order ...

Another observation I just discovered while playing with the query :

1. Adding the sort order ASC has no effect. Same result : 4 on SQL 2016, 1 on SQL 2019

select  @myNumber = Column1 
from (select 1 union select 2 union select 3  union select 4) as testTable(Column1)
order by column1 ASC;

2. Adding the sort order DESC has another effect. 1 on SQL 2016, 1 on SQL 2019

select  @myNumber = Column1 
from (select 1 union select 2 union select 3  union select 4) as testTable(Column1)
order by column1 DESC;

So it seems that SQL 2019 only takes the first record of the resultset, and no longer iterates over all records.

3 DataBase Admins walked into a NoSQL bar...
A little while later, they walked out because they couldn't find a table.

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 3

but why only in functions ?

i'm starting to worry quite a bit of code now - given that we are upgrading in the next 3 weeks

MVDBA

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 4

Can you try putting in TOP (1) to insure you only get a single value? Also, can you provide the execution plans for both ASC and DESC versions - it would be interesting to see if there is a difference.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 5

This isn't what I'd call a change in behavior. It's always been stated, for decades now, that you can't rely on the order of rows without an ORDER BY statement. Sure, it "worked" one way and now it does something different, but it was never proper functionality.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 6

Grant Fritchey wrote:

This isn't what I'd call a change in behavior. It's always been stated, for decades now, that you can't rely on the order of rows without an ORDER BY statement. Sure, it "worked" one way and now it does something different, but it was never proper functionality.

there used to be a myth that your records were returned in clustered index order - never seen it happen … ever and as soon as you put a join into the equation everything flies out of the window

SQL is a declarative language - you tell it what you want and it figures out how to give it you.. fail to specify the details of "what you want" (ignore query hints.. that's telling sql how to do it's own job) then you get a bad data set

for example - your wife asks what you want for dinner, you reply "pie" , so she goes and gets alligator pie rather than the zebra pie that you really wanted.

old school sql from back in 2000 - ALWAYS put asc or desc in your query if you have an order by

MVDBA

FHoornaert SSC Enthusiast Points: 181 More actions · Answer 7

Hi Grant,

That's the point, the statement does include an ORDER BY.

See my first post.

CREATE FUNCTION [dbo].[fnTest]()
RETURNS int
AS
BEGIN
       declare @myNumber INT;
       select  @myNumber = Column1 
       from (select 1 union select 2 union select 3  union select 4) as testTable(Column1)
       order by column1;
       RETURN ISNULL(@myNumber, 0);
END

I know it does not include ASC or DESC, but it should be ASC by default when no sort order is specified.

The outcome of the function changes when you change your compatibility level ...

3 DataBase Admins walked into a NoSQL bar...
A little while later, they walked out because they couldn't find a table.

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 8

FHoornaert wrote:

Hi Grant,
That's the point, the statement does include an ORDER BY.
See my first post.
CREATE FUNCTION [dbo].[fnTest]()
RETURNS int
AS
BEGIN
       declare @myNumber INT;
       select  @myNumber = Column1 
       from (select 1 union select 2 union select 3  union select 4) as testTable(Column1)
       order by column1;
       RETURN ISNULL(@myNumber, 0);
END
I know it does not include ASC or DESC, but it should be ASC by default when no sort order is specified.
The outcome of the function changes when you change your compatibility level ...

does it do the same when you select from a real table? is it possible that your union "virtual table" is the issue??? just thinking out loud

MVDBA

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 9

Oops. Sorry.

Still don't see an issue with this. If you don't specify exactly what you want, you get whatever the optimizer and the query engine gives you. I wouldn't call this a bug. It's just unexpected behavior from an incomplete query statement.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

FHoornaert SSC Enthusiast Points: 181 More actions · Answer 10

Jeffrey,

Yes, using a TOP 1 fixes the problem. And that's the way i usually do it, but when you inherit a program with 7000 SP's and 1500 Scalar UDF, you don't have control over the existing code ...

Below code works as expected in both SQL 2016 & 2019.

CREATE FUNCTION [dbo].[fnTest]()
RETURNS int
AS
BEGIN
       declare @myNumber INT;
       SELECT @myNumber = 
   (
SELECT TOP 1 Column1 
from (select 1 union select 2 union select 3  union select 4) as testTable(Column1)
order by column1 DESC
   );       
   RETURN ISNULL(@myNumber, 0);
END
GO

Attached you'll find the execution plans for both 2016 & 2019. I see no difference between the sort ASC & DESC.

ExecutionPlan

3 DataBase Admins walked into a NoSQL bar...
A little while later, they walked out because they couldn't find a table.

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 11

scrap that - I tried it and it's still an issue

looks like the assertion to the return variable is stopping at the first row within a scalar function (ask Microsoft) - and it is definatly ignoring order by

move towards using MAX and MIN in that situation

MVDBA

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 12

big difference in the plans - it explains quite a lot

MVDBA

FHoornaert SSC Enthusiast Points: 181 More actions · Answer 13

Grant,

I don't see why the statement is incomplete ?

BOL indicates that ORDER BY <column> without a ASC or DESC is ASC by default.

And even when I add "order by column1 ASC" explicitly, it doesn't make any difference in the results.

3 DataBase Admins walked into a NoSQL bar...
A little while later, they walked out because they couldn't find a table.

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 14

looks like it's tried to apply "parallelism" (not quite the correct word, but it will do) to the function - this is possibly a result of the new scalar value function inlining that is new in sql 2019

MVDBA