StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The reason the Merge iterator appears to output an empty string for the <code>Type</code> column is interesting. The optimizer recognises that <code>Type</code> is a constant value and applies a rewrite that removes that column from the flow, adding it back in later as a Compute Scalar. You can see this in action by adding an <code>OUTPUT</code> clause to the <code>MERGE</code> statement to emit the value of <code>inserted.[Type]</code>. Without the <code>OUTPUT</code> clause, there's no need for the Compute Scalar, so it is optimized away, leaving us with the plan shape seen in the original example.</p> <p>The bug arises when something between the Merge iterator (which can also be a Table Merge rather than a Clustered Index Merge) and the Compute Scalar needs the value of the <code>[Type]</code> column. Since it was removed from the flow (despite showing in the plan as an output from the merge) we end up referencing something that does not exist, and that produces the empty string. In another world, SQL Server would assert instead with something like a null pointer violation, but that's another story.</p> <p>This issue is a bit fixed in SQL Server 2012, but there is still a bug. I say 'a bit' fixed because the rewrite that removes the constant-value column is switched off (so the FK check gets a real value to seek on, not an empty string) but the Compute Scalar that adds the string 'Car' back into the flow still appears if <code>OUTPUT inserted.[Type]</code> is added. In a perfect world, the plan would simply output the value provided by the Merge rather than re-computing the constant. Anyway, that's not all that important (it just shows the implementation is still a bit flaky around the edges) but there is still a bug related to removing the column reference:</p> <p>It only reproduces in SQL 2012 with a table variable (all other types of table are fine) but reproduces on all released versions of SQL Server with any type of table object:</p> <pre><code>DECLARE @Bug TABLE ( id INTEGER PRIMARY KEY, data AS 'X' PERSISTED, CHECK (data = 'A') ) MERGE @Bug AS b USING (VALUES(1)) AS u (id) ON u.id = b.id WHEN NOT MATCHED THEN INSERT (id) VALUES (u.id) OUTPUT INSERTED.data; </code></pre> <p>The point is that the <code>CHECK</code> constraint is skipped - the Assert operator that checks it is added to the plan, but then optimized away when optimizer sees the constant-value column and applies its rewrite. Removing the Assert allows the value 'X' to be added to the table even though it violates the CHECK constraint. As I say, you can reproduce this in 2008 R2 and earlier with real and temporary tables - in 2012 it only bugs out with table variables.</p> <p>Paul</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload