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?
Answer :
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;
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: