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

Applies to: Microsoft SQL Server 2016 Service Pack 1SQL Server 2016 Enterprise CoreSQL Server 2016 Enterprise More

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

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.