If you use MERGE
, indexed views and foreign keys, your queries might return incorrect results. Microsoft have released a fix for incorrect results returned when querying an indexed view. The problem applies to:
- SQL Server 2012
- SQL Server 2008 R2
- SQL Server 2008
The Knowledge Base article does not go into detail, or provide a reproduction script, but this blog post does.
The KB says that reproducing the bug requires these features:
- An indexed view on two tables that have a foreign key relationship
- An update performed against the base tables
- A query executed against the indexed view using a
NOEXPAND
hint
There are two important details I would like to add:
- The
NOEXPAND
hint is not required to reproduce the bug on Enterprise Edition or equivalent - The update must be performed by a
MERGE
statement
The fix is available starting from the following cumulative updates:
- SQL Server 2012 SP1 CU2 build 11.0.3339
- SQL Server 2012 RTM CU5 build 11.0.2395
- SQL Server 2008 R2 SP2 CU4 build 10.50.4270
- SQL Server 2008 R2 SP1 CU10 build 10.50.2868
- SQL Server 2008 SP3 CU8 build 10.00.5828
Steps to Reproduce
We will need two tables:
CREATE TABLE dbo.Parent
(
parent_id integer IDENTITY NOT NULL,
[value] varchar(20) NOT NULL,
CONSTRAINT PK_Parent_id
PRIMARY KEY CLUSTERED (parent_id)
);
CREATE TABLE dbo.Child
(
child_id integer IDENTITY NOT NULL,
parent_id integer NOT NULL,
CONSTRAINT PK_Child_id
PRIMARY KEY CLUSTERED (child_id)
);
…and a few rows of data:
INSERT dbo.Child
(parent_id)
SELECT
New.parent_id
FROM
(
INSERT dbo.Parent
OUTPUT inserted.parent_id
VALUES
('Apple'),
('Banana'),
('Cherry')
) AS New;
The tables now look like this (parent first):
We can now add the required FOREIGN KEY
relationship:
ALTER TABLE dbo.Child
ADD CONSTRAINT FK_Child_Parent
FOREIGN KEY (parent_id)
REFERENCES dbo.Parent (parent_id);
Next, we add an indexed view that joins the two tables. The view could contain more complex features like aggregates, but it is not necessary:
CREATE VIEW dbo.ParentsAndChildren
WITH SCHEMABINDING
AS
SELECT
P.parent_id,
P.[value],
C.child_id
FROM dbo.Parent AS P
JOIN dbo.Child AS C
ON C.parent_id = P.parent_id;
GO
CREATE UNIQUE CLUSTERED INDEX cuq
ON dbo.ParentsAndChildren (child_id);
The final step is to use a MERGE
statement to make some changes to the Parent
table:
DECLARE @ParentMerge AS TABLE
(
parent_id integer PRIMARY KEY,
[value] varchar(20) NOT NULL
);
INSERT @ParentMerge
(parent_id, [value])
VALUES
(1, 'Kiwi Fruit'),
(4, 'Dragon Fruit');
MERGE dbo.Parent AS P
USING @ParentMerge AS S
ON S.parent_id = P.parent_id
WHEN MATCHED THEN
UPDATE
SET [value] = S.[value]
WHEN NOT MATCHED THEN
INSERT ([value])
VALUES (S.[value])
OUTPUT
$action,
inserted.parent_id,
deleted.[value] AS old_value,
inserted.[value] AS new_value;
This MERGE
performs two actions:
- Updates the
value
column of parent row 1 from ‘Apple’ to ‘Kiwi Fruit’ - Adds a new parent row 4 for ‘Dragon Fruit’
The statement includes an OUTPUT
clause to show the changes it makes (this is not required for the repro):
This confirms that the changes have been made as we requested: parent row 1 has changed; and row 4 has been added. The changes are reflected in the base tables:
SELECT P.* FROM dbo.Parent AS P;
SELECT C.* FROM dbo.Child AS C;
As highlighted, row 1 has changed from Apple to Kiwi Fruit and row 4 has been added.
We do not expect to see row 4 in the indexed view because there are no child records for that row (the indexed view uses an inner join).
Checking the indexed view using the NOEXPAND
table hint (required in non-Enterprise SKUs to use indexes on a view):
SELECT PAC.*
FROM dbo.ParentsAndChildren AS PAC
WITH (NOEXPAND);
These results are incorrect. They show the old value of the data for parent row 1 (apple instead of kiwi fruit).
Now we try using the EXPAND VIEWS
query hint to force SQL Server to access base tables rather than view indexes:
SELECT PAC.*
FROM dbo.ParentsAndChildren AS PAC
OPTION (EXPAND VIEWS);
This query produces correct results.
On SQL Server Enterprise Edition and equivalents, the optimizer chooses whether to access the indexed view or the base tables. For following query, without any hints, the optimizer chooses not to expand the view. It reads the view index and produces incorrect results:
-- Enterprise Edition ONLY
SELECT PAC.*
FROM dbo.ParentsAndChildren AS PAC;
Perhaps adding a child row to match the new parent row 4 will somehow fix things up?
INSERT dbo.Child
(parent_id)
VALUES
(4);
GO
SELECT PAC.*
FROM dbo.ParentsAndChildren AS PAC
WITH (NOEXPAND);
SELECT PAC.*
FROM dbo.ParentsAndChildren AS PAC
OPTION (EXPAND VIEWS);
No. The query plan that accesses the view index still returns an incorrect value for row 1. It seems MERGE
has corrupted our indexed view.
Analysis using DBCC CHECKTABLE
Checking the view with DBCC CHECKTABLE
returns no errors:
…unless we use the EXTENDED_LOGICAL_CHECKS
option:
DBCC CHECKTABLE (N'dbo.ParentsAndChildren')
WITH EXTENDED_LOGICAL_CHECKS;
The damage is repairable:
ALTER DATABASE Sandpit
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
DBCC CHECKTABLE (N'dbo.ParentsAndChildren', REPAIR_REBUILD)
WITH EXTENDED_LOGICAL_CHECKS;
GO
DBCC CHECKTABLE (N'dbo.ParentsAndChildren')
WITH EXTENDED_LOGICAL_CHECKS;
GO
ALTER DATABASE Sandpit
SET MULTI_USER;
Now you probably do not want to set your database to SINGLE_USER
mode and run a DBCC
repair after every MERGE
statement. (We could also rebuild the clustered index on the indexed view to effect a repair).
Cause
For the MERGE
statement above, the query optimizer builds a plan that does not update the indexed view:
In a version of SQL Server with the fix applied, the same MERGE
statement produces a plan that does maintain the indexed view:
The plan operators used to keep the view index in step with the base tables are highlighted. Without these operators, the changes to the base table are not correctly written to any indexes defined on the view.
The root cause of the problem is related to the same simplification that allows the optimizer to remove the reference to the Parent
table in this query:
SELECT
COUNT_BIG(*)
FROM dbo.Parent AS P
JOIN dbo.Child AS C
ON C.parent_id = P.parent_id;
The FOREIGN KEY
relationship and NOT NULL
constraints on the referencing column together mean that the join to Parent
cannot affect the result of the query, so the join can be simplified away.
In SQL Server 2012 onward, we can see when this simplification is performed because the following message appears when undocumented trace flag 8619 (with 3604) is enabled during compilation:
Full Join removed for table TBL: dbo.Parent TableID=xxx
The same message is emitted when a MERGE
statement contains a WHEN MATCHED THEN UPDATE
clause and either a WHEN NOT MATCHED THEN INSERT
or WHEN MATCHED THEN DELETE
clause.
These conditions combine such that the optimizer incorrectly concludes that a table reference can be removed, when in fact it is needed later on when the update side of the plan is built.
Other details of the query and database can affect whether the simplification can be misapplied. For example, if the FOREIGN KEY
constraint contains an ON DELETE CASCADE
clause, and the MERGE
contains a DELETE
clause, the simplification is not performed, the TF 8619 message does not appear, and the bug does not manifest.
The key to determining whether a particular query is vulnerable to this bug (TF 8619 aside) is to check whether the query plan includes operators to maintain the indexed view.
At a minimum, you should see a update operator for the view:
Sentry One Plan Explorer identifies the operator as applying to a view explicitly. In SSMS you need to click on the graphical operator and inspect the Properties window to see the same information.
Summary
The updated conditions for incorrect results are:
- An indexed view that joins tables
- Two tables have a single-column
FOREIGN KEY
constraint - A
MERGE
statement contains anUPDATE
action that affects one of the tables - The
MERGE
statement also contains anINSERT
orDELETE
action (or both) - The optimizer applies a simplification that removes a table reference based on the foreign key relationship
- As a result, the
MERGE
execution plan does not contain the operators necessary to correctly maintain the indexed view - A subsequent query plan accesses an index on the view, either explicitly or via indexed-view matching (Enterprise Edition)
Note:
- The simplification is not applied in tempdb
- The simplification is not applied to multi-column foreign key constraints
Under these conditions, view indexes do not reflect the state of the base tables and incorrect results are returned. Once the fix is applied, the optimizer does not misapply the simplification, and the correct indexed view maintenance features are built into execution plans.
© Paul White
email: SQLkiwi@gmail.com
twitter: @SQL_Kiwi
No comments:
Post a Comment
All comments are reviewed before publication.