Incorrect behaviour of SQL Server 2016 with memory-optimized tables

Question

Please have a look at the following SQL query:

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
DECLARE
 @t dbo.IN_MEMORY_TABLE_TYPE
INSERT @t
(
 source_col,
 target_col
)
VALUES
 (10, 0),
 (0, 0)
UPDATE r1
SET
 target_col = -1
FROM @t r1
WHERE EXISTS
 (
 SELECT *
 FROM @t r2
 WHERE r2.source_col > 0
 )
SELECT *
FROM @t
GO
DROP TYPE dbo.IN_MEMORY_TABLE_TYPE

When executing it on SQL Server 2014 (12.0.4100.1 X64) the UPDATE in the query performs as expected and the following valid result is returned:

source_col | target_col
----------------------
10 | -1
0 | -1

However, when executing on SQL Server 2016 (13.0.4001.0 X64) not all the rows get updated and the following is returned:

source_col | target_col
----------------------
10 | -1
0 | 0

This looks like a bug to me, does it look so to you?

• Dean Savović: Yeah, this is a bug. Tested it on SQL 2017 CTP 2.1 and it behaves the same as it does on SQL 2016.

Answer 1

Yes it is a bug, which seems to only affect table variables, with a bw-tree index access method, and an uncorrelated self-join.

Simplified repro using DELETE:

CREATE TYPE dbo.IN_MEMORY_TABLE_TYPE AS TABLE
(
 col integer NOT NULL INDEX i NONCLUSTERED (col)
)
WITH (MEMORY_OPTIMIZED = ON);
GO
DECLARE @T AS dbo.IN_MEMORY_TABLE_TYPE;
INSERT @T (col)
VALUES (1), (2), (3), (4), (5);
DELETE T
FROM @T AS T
WHERE EXISTS 
(
 SELECT 1
 FROM @T AS T2
 WHERE T2.col = 1 -- Vary this number 1-5
);
SELECT T.col FROM @T AS T;
GO
DROP TYPE dbo.IN_MEMORY_TABLE_TYPE;

Faulty plan

Note in the above plan the search for rows to delete terminates earlier than expected (only two rows are read from the scan). Halloween Protection is generally correctly handled for In-Memory OLTP, there just seems to be a specific issue with the combination of factors mentioned above.

---

This bug is fixed in SQL Server 2016 SP1 CU5 and SQL Server 2017 CU1:

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