Changing metadata changes query output issue [duplicate]

Question

Recently stumbled across the following problem: changing metadata changes query output.

Here is how to get it.

CREATE TABLE [dbo].[prod]
(
[ID] [tinyint] NOT NULL,
[values] [tinyint] NOT NULL
) 
GO

The following query always returns ‘This is the end’ (error always raised). Also the filter should be some sort of a random generator.

BEGIN TRY
SELECT [id] FROM 
        (
        SELECT [id] as [id]
        FROM dbo.prod
        WHERE ([id]=1/0)
        ) t1
WHERE
-- Random generator 1 always returns false
(FLOOR(RAND()*(1))=1)
-- Random generator 2 does not always return false
/*
SUBSTRING( cast(NEWID() as varchar(max)), 1, 1)
IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0', 'A', 'B', 'C', 'D', 'E', 'F')
*/
-- Random generator 3 does not always return false
/*
SUBSTRING(cast(cast( DATEPART(MILLISECOND,GETDATE()) as char(3)) as varchar(3)), 2, 1)
IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0')
*/
END TRY
BEGIN CATCH
SELECT 'This is the end'
END CATCH;

Now add primary key constraint. Note: no data changes.

ALTER TABLE dbo.prod
ADD CONSTRAINT PK_1 PRIMARY KEY NONCLUSTERED(id);

And now the same query

BEGIN TRY
SELECT [id] FROM 
        (
        SELECT [id] as [id]
        FROM dbo.prod
        WHERE ([id]=1/0)
        ) t1
WHERE
(FLOOR(RAND()*(1))=1)
END TRY
BEGIN CATCH
SELECT 'This is the end'
END CATCH;

never returns an error.

It is understandable that changing metadata may change execution plan but it was unexpected that it changes query’s output. Is this behaviour correct?

Answer 1

Is this behaviour correct?

It is intentional (by design) behaviour; whether it is 'correct' or not is more a matter of opinion.

The general point is: SQL Server does not guarantee the timing or number of evaluations of expressions. This behaviour exists to give the query optimizer the freedom it needs to find good execution plans.

As a consequence, query optimization may change the query's output or runtime behaviour, if there is a dependency on the timing or number of evaluations of an expression (or scalar function).

In the question's examples, the runtime behaviour depends on whether the query optimizer identifies each expression as a runtime constant or not. The execution engine may decide to evaluate (and cache) runtime constant expressions before query execution begins.

If the optimizer identifies the expression 1/0 as a runtime constant, the XML form of show plan output will show it labelled as ConstExprxxxx:

Runtime constants like this can be evaluated once before query execution begins, and the result cached, as mentioned previously. Where this occurs, a divide by zero error is returned during this pre-query constant expression evaluation.

Adding the index to the table happens to take a route through query optimization that does not result in 1/0 being labelled a runtime constant (no ConstExprxxxx below):

In this particular case, the presence of a uniqueness constraint means the optimizer decides there is no benefit in caching the result of an expression that will only be evaluated once. The start-up filter means the faulty expression is never evaluated.

These expression evaluation behaviours are not documented or completely determined by the presence of ConstExpr labels in all cases. The answer is not to write code that depends on the timing or number of evaluations of expressions by the execution engine.

Answer 2

Interesting.

It seems this is due to the addition of the unique constraint, which adds an index on ID, which in turn causes the optimizer to use an index seek rather than a table scan.

Unfortunately I have no idea why the index seek doesn't seem to throw a divide by zero error, as it shows the division in the seek predicate.

DECLARE @X TABLE 
(
ID Int IDENTITY(1,1) NOT Null,
    Value Int NOT Null
)

DECLARE @Y TABLE 
(
    ID Int IDENTITY(1,1) NOT Null UNIQUE,
    Value Int NOT Null
)


SELECT  ID
FROM    @X
WHERE ID = 1/0
AND Rand() = -1

SELECT ID
FROM @Y
WHERE ID = 1/0
AND Rand() = -1

Hmm, even weirder... If you create a table and add the index, but use query hints to 'force' a table scan, the query still succeeds:

CREATE TABLE #X
(
    ID Int IDENTITY(1,1) NOT Null,
    Value Int NOT Null
)

--  Select from table, this errors on divide by zero
SELECT  ID
FROM    #X
WHERE ID = 1/0
AND Rand() = -1

--  Add a unique constraint to the table
ALTER TABLE #X ADD CONSTRAINT TEMP_X_CONSTRAINT UNIQUE (ID)

--  Select from table, this is successful
SELECT  ID
FROM    #X
WHERE ID = 1/0
AND Rand() = -1

--  Select from table forcing table scan, this is successful
SELECT ID
FROM #X WITH (INDEX(0))
WHERE ID = 1/0
AND Rand() = -1

I think SQL Server might be lying to us about it's actual execution plan.