KB4039776 - FIX: Incorrect behavior when you use memory-optimized tables with "where exists" statement in SQL Server 2016 or 2017

Symptoms

Assume that you are using Microsoft SQL Server 2016 or 2017. When you process memory-optimized data transactions by using memory-optimized table variables with the where exists statement, you may get a wrong result.

For example:

Step1: Create a memory-optimized database and table.

Create DATABASE demo

ALTER DATABASE demo ADD FILEGROUP demo_mod CONTAINS MEMORY_OPTIMIZED_DATA 

ALTER DATABASE demo ADD FILE (name='demo_mod1', filename='C:\DATA\demo_mod1') TO FILEGROUP demo_mod 

Use Demo

Go

CREATE TYPE dbo.IN_MEMORY_TABLE_TYPE AS TABLE

( source_col INT NULL,

target_col INT not NULL

INDEX ix_InMemoryTable NONCLUSTERED (target_col)

) WITH (MEMORY_OPTIMIZED = ON)

GO

Step2: Insert data and update data.

DECLARE @t dbo.IN_MEMORY_TABLE_TYPE

INSERT @t ( source_col, target_col ) VALUES (10, 0),(0, 0)

select * from @t

UPDATE r1 SET target_col = -1 FROM @t r1

WHERE EXISTS ( SELECT * FROM @t r2 WHERE r2.source_col > 0 )

SELECT * FROM @t

GO

Step3: Check results.

Actual Results: Not all rows of table variable @t get updated.

source_col | target_col

----------------------

10 | -1

0 | 0

The expected results: All rows should get updated to have target_col = -1.

source_col | target_col

----------------------

10 | -1

0 | -1.

Resolution

This issue is fixed in the following cumulative updates for SQL Server:

      Cumulative Update 1 for SQL Server 2017 

      Cumulative Update 5 for SQL Server 2016 SP1

Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. Check out the latest cumulative updates for SQL Server:

Latest cumulative update for SQL Server 2017

Latest cumulative updates for SQL Server 2016

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

References

Learn about the terminology that Microsoft uses to describe software updates.

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×