13

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?

  • Yeah, this is a bug. Tested it on SQL 2017 CTP 2.1 and it behaves the same as it does on SQL 2016. – Dean Savović Jun 19 '17 at 19:53
12

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.

Connect item: Incorrect behaviour with memory-optimized tables by Dmitry Savchenko


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.