CRYPT_GEN_RANDOM erroneously treated as constant - by Martin Smith

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 654809 Comments
Status Closed Workarounds
Type Bug Repros 1
Opened 3/30/2011 9:25:06 AM
Access Restriction Public


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.
Sign in to post a comment.
Posted by Microsoft on 6/8/2011 at 8:48 AM
This issue will be fixed in the next PCU (or service pack, if you prefer the old terminology) of SQL Server 2008.

Andrew Richardson
Developer, SQL Server Query Optimizer.
Posted by Microsoft on 4/7/2011 at 6:42 PM
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.
Posted by Microsoft on 4/7/2011 at 3:58 PM

Thanks for the feedback. We're looking into fixing this in the next PCU of SQL Server 2008.

Best regards,
Eric Hanson
Program Manager
SQL Server Query Processing