Changing metadata changes query output issue 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?
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.