CRYPT_GEN_RANDOM erroneously treated as constant

CRYPT_GEN_RANDOM erroneously treated as constant by Martin Smith

Closed

as Fixed

Type:	Bug
ID:	654809
Opened:	3/30/2011 9:25:06 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

When using CRYPT_GEN_RANDOM to populate a column constrained by a Foreign Key I found that false (and seemingy impossible) constraint violation errors were raised.

Upon further investigation I found that this only happened when there is a merge join leading into the assert operator. When the plan has a nested loops no such errors are raised.

There is no Sort operator between the clustered index insert and the Merge Join. The only circumstances in which this would be valid would be if all the newly inserted rows had the same value returned by CRYPT_GEN_RANDOM however this is not the case.

So CRYPT_GEN_RANDOM seems to be treated in the plan as though it behaved in the same manner as rand() and is evaluated once per query. However this is not the actual behaviour of the function.

Details (expand)

English

SQL Server 2008 - Developer Edition

SQL Engine

Windows 7 Home Premium

English

CREATE TABLE table_a
(
a_id INT PRIMARY KEY
)

INSERT INTO table_a VALUES (1),(2),(3)

CREATE TABLE table_b
(
c_id INT IDENTITY(1,1) PRIMARY KEY,
a_id INT NOT NULL REFERENCES dbo.table_a(a_id)
)

/*This works*/
INSERT INTO table_b (a_id)
SELECT TOP (38) 1 + CRYPT_GEN_RANDOM(1)%3
FROM master..spt_values

/*This doesn't*/
INSERT INTO table_b (a_id)
SELECT TOP (39) 1 + CRYPT_GEN_RANDOM(1)%3
FROM master..spt_values

The version with "TOP (38)" works fine and inserts the records. The version with "TOP (39)" always fails.

NB: This was the cut off point between it choosing a nested loops and a merge join plan on my machine.

SELECT 1 + CRYPT_GEN_RANDOM(1)%3

always returns a value between 1 and 3. These numbers are present in table A so I would expect the Foreign Key Violation to never be raised.

X64

0 attachments

The problem is with the way the optimizer treats the CRYPT_GEN_RANDOM function: We assume that it will output the same value for every row, since its input is constant. That means that when we use a merge join to verify the foreign key constraint, we think we don't need to sort the input -- if it's constant, it's already sorted! The first time the merge join sees an out-of-order value, it can't go backwards to find the matching primary key value, so it fails the insert.

Here are a few potential workarounds:

- Use the NEWID function instead of CRYPT_GEN_RANDOM. The optimizer knows that NEWID is non-deterministic.

- Wrap the CRYPT_GEN_RANDOM function in a user-defined function. The optimizer assumes that user-defined functions are non-deterministic.

- Insert the CRYPT_GEN_RANDOM values into a temp table and then insert from the temp table into the foreign key table.

- Add an OPTION(LOOP JOIN) hint to the insert statement.

Thank you for informing us of this bug. I have fixed it for the next major release. The decision to include the fix in the next PCU is pending.

Andrew Richardson
Developer, SQL Server Query Optimizer.