SQLSTATE[23000]: [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot insert duplicate key row in object 'dbo.simpletest431540file_managed' with unique index 'uri_unique'.

The same tests do no fail on MySQL. My bets are on collation differences. To be investigated before release.

CommentFileSizeAuthor
#1 1050800-computed-varbinary-max.patch1.74 KBdamien tournoud

Comments

damien tournoud’s picture

damien tournoud commented
Status: Active » Needs review
StatusFileSize
new 1050800-computed-varbinary-max.patch1.74 KB

This is actually a regression introduced by our removal of filtered indexes.

It seems that this computed column:

[__unique_uri]  AS ( CONVERT([varbinary](16), hashbytes('MD4', CONVERT([varbinary], [uri], 0)), 0) ),

Results in the [uri] column being *first* truncated to 16 characters, and then hashed. In other words, it's equivalent to:

[__unique_uri]  AS ( CONVERT([varbinary](16), hashbytes('MD4', CONVERT([varbinary](16), [uri], 0)), 0) ),

I don't know the implicit cast rules of SQL Server, but it really feels like a bug to me that the final type is carried into hashbytes() arguments, as this is a very destructive function. (@Ashay, if you want to look into this :p)

Anyway, casting explicitly to varbinary(max) prevents this behavior. Patch attached for review.

damien tournoud’s picture

damien tournoud commented
Status: Needs review » Fixed

Committed to HEAD.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

henneman’s picture

henneman commented

Subscribe