Hugo Kornelis (3/31/2010)
my gut feeling is that, if the same data is used multiple times in a query, it should use the same value, regardless of concurrent modifications.(And, of course, if the reference is only actually used once in the query and some internal process of SQL Server duplicates it somewhere on the road from query to execution plan, having the result changed between the first and second execution is only worse).
Which is why I submitted a bug report: https://connect.microsoft.com/SQLServer/feedback/details/546437/coalesce-subquery-1-may-return-null. The bug reported is a case I constructed, based on this discussion, where COALESCE(..., 1) may erroneously return NULL (which should of course never happen).
For those who are unwilling to go to the Connect site, here is a copy of the repro steps.
First, set up the required table as follows:
CREATE TABLE Demo
(PrimKey int NOT NULL,
SomeCol int NOT NULL DEFAULT (0),
Nullable int NULL,
Filler char(200) NOT NULL DEFAULT ('Rubbish'),
PRIMARY KEY (PrimKey)
);
go
-- Quick and dirty way to create 10,000 rows of test data
WITH ManyRows AS
(SELECT ROW_NUMBER() OVER (ORDER BY a.object_id, b.object_id) AS rn
FROM sys.objects AS a
CROSS JOIN sys.objects AS b)
INSERT INTO Demo (PrimKey)
SELECT rn
FROM ManyRows
WHERE rn BETWEEN 1 AND 10000;
go
-- Change SomeCol value in one row
UPDATE Demo
SET SomeCol = 1
WHERE PrimKey = 1234;
go
Next, open two connections. In one, paste the code below and run - it will run forever, flipping the Nullable column between 1 and NULL as fast as it can:
WHILE 1 = 1
BEGIN;
UPDATE Demo
SET Nullable = NULLIF(1, Nullable)
WHERE PrimKey = 1234;
END;
In the second connection, paste and execute the code below:
DECLARE @Good int, @Bad int;
SET @Good = 0;
SET @Bad = 0;
WHILE @Good + @Bad < 1000
BEGIN;
IF COALESCE((SELECT Nullable
FROM Demo
WHERE SomeCol = 1), 1) IS NULL
BEGIN;
SET @Bad = @Bad + 1;
END;
ELSE
BEGIN;
SET @Good = @Good + 1;
END;
END;
SELECT @Good AS Good, @Bad AS Bad;
On my system, the Bad count actually exceeded the Good count...
Votes and repro confirmations of the bug are, of course, welcome.