Incorrect uniqueidentifier works without a Primary Key but fails with one

Question

So we were recently running some logic tests on an insert into a table with a uniqueidentifier as a primary key. Here's my test query;

IF OBJECT_ID('tempdb..#DatabaseTable') IS NOT NULL DROP TABLE #DatabaseTable
CREATE TABLE #DatabaseTable
        ([ID] [uniqueidentifier] NOT NULL PRIMARY KEY,
        [LastUpdate] [datetime] NOT NULL,
        [Locked] [tinyint] NOT NULL)

IF OBJECT_ID('tempdb..#temptable') IS NOT NULL  DROP TABLE #temptable
CREATE TABLE #temptable (ID varchar(36), Data nvarchar(max))

INSERT INTO #temptable (ID, Data) 
VALUES ('g078f19e-e150-4bb9-b5f4-b20b3fc64016', 'text goes here')

SELECT tmp.ID ,tmp.Data
FROM #temptable tmp 
LEFT JOIN #DatabaseTable db
    ON tmp.ID = db.ID
WHERE db.ID is null

This fails because the uniqueidentifier is not valid as it contains an incorrect value of 'g' that sits outside the acceptable range for this data type. This happened as a developer had altered it manually for testing as he knew that it wouldn't exist in the destination table but wasn't aware of the valid character list.

This is the error that it throws;

(1 row(s) affected)
Msg 8169, Level 16, State 2, Line 13
Conversion failed when converting from a character string to uniqueidentifier.

This is the behaviour that I would expect. However, when messing about in testing I removed the primary key on #DatabaseTable and it runs totally fine. Now I examined the execution plan expecting it to convert the uniqueidentifier to match the varchar(36), however I see this compute scalar;

CONVERT_IMPLICIT(uniqueidentifier,[tempdb].[dbo].[#temptable].[ID] as [tmp].[ID],0)

I can't seem to find any documentation from Microsoft on the actual syntax of CONVERT_IMPLICIT so I am assuming that the final zero is what it is replaced with if the convert fails, would this be a correct assumption (same as ISNULL/COALESCE)? Also, why does this work fine without the PK but fails with it?

Answer 1

I was able to repro this.

Both plans had the row from #temptable driving a nested loops join.

When #DatabaseTable has a primary key there is an index to seek into so the inside of the join has a seek. It is necessary to cast the varchar to uniqueidentifier to get the value to seek at which point the attempt to convert the invalid value fails.

When there is no primary key the plan has a scan on the inside of the nested loops instead. it can defer the evaluation until it has a row to compare, there is no row output from the scan on #DatabaseTable (as it is empty) so the CONVERT_IMPLICIT is never required to be evaluated.

The position of the compute scalar may lead you to believe otherwise but see Compute Scalars, Expressions and Execution Plan Performance for more about that.