In the world of SQL Server, we are often taught that COALESCE is just a fancier,
standard-compliant version of ISNULL.
Official Syntax
According to the official Microsoft documentation, the definition is straightforward. It evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.
COALESCE ( expression [ ,...n ] )
Under the hood, however, SQL Server translates this simple function into a more complex CASE
expression:
CASE WHEN (expression1 IS NOT NULL) THEN expression1 WHEN (expression2 IS NOT NULL) THEN expression2 ... ELSE expressionN END
This definition hides a dangerous implementation detail. If you run the query below enough times in SQL
Server, you will eventually get a NULL result, bypassing your hardcoded
safety net of 2.
SELECT COALESCE(CASE WHEN RAND() <= 0.5 THEN 1 END, 2);
The Smoking Gun: SSMS Execution Results
If you execute this query repeatedly in SQL Server Management Studio, you would expect to see only
1 or 2. However, because of the "Double Evaluation" bug, here is what happens
after a few runs:
| (No column name) | |
|---|---|
| 1 | 1 |
| (No column name) | |
|---|---|
| 1 | 2 |
| (No column name) | |
|---|---|
| 1 | NULL |
The "Double Dip" Execution
The issue arises because the expression A appears twice in the expanded
logic: once in the WHEN check, and again in the THEN return.
If expression A is deterministic (like a column value or a static number),
this doesn't matter. But in our query, A is non-deterministic: Every time
you touch it, RAND() re-rolls the dice.
Visualizing the Failure
Here is the logic flow that leads to the impossible NULL:
The "Volatility Trap" Logic Flow: One check, one return, two evaluations.
How to Fix It
To prevent this "Volatility Trap," you must force SQL Server to evaluate the random logic only once.
Option 1: Use ISNULL (T-SQL Specific)
ISNULL guarantees the expression is evaluated only once.
SELECT ISNULL(CASE WHEN RAND() <= 0.5 THEN 1 END, 2);
Option 2: Subqueries (The Standard Way)
Materialize the random value in a derived table or Common Table Expression (CTE) first, then coalesce the result.
SELECT COALESCE(Val, 2) FROM (SELECT CASE WHEN RAND() <= 0.5 THEN 1 END AS Val) AS T;
Reference & Further Learning
For a detailed breakdown of the performance and behavior differences, I highly recommend this video by Erik Darling:
Discussion
Leave a Reply
Your email address will not be published.