i-one
I like it when I find something strange or unusual in the query plans, because explaining why you see this or that thing in a query plan is a nice exercise for the mind. To find a bug is a kind of special... a mix of enjoyment and frustration. It's interesting to dig into, but you angry at SQL Server for its imperfection.
To prepare this post, an instance of SQL Server 2022 (RTM-CU9) has been used. Demonstrated bug, however, exists down to SQL Server 2008 R2 at least. But before we get to the bug itself, I need to say a few words about its important ingredient.
## **Run-Time Constants**
Consider two variations of a simple query for one and two rows of data
```sql
DECLARE @Mode varchar(10) = 'DISPLAY';
SELECT t.[Name], [NameLen] = LEN(t.[Name])
FROM (VALUES ('A')) t([Name])
WHERE @Mode != 'HIDE';
SELECT t.[Name], [NameLen] = LEN(t.[Name])
FROM (VALUES ('A'), ('AA')) t([Name])
WHERE @Mode != 'HIDE';
```
Here are their execution plans
What is the key distinction between the two you think?
Immediately visible is the slightly different estimated costs balance. This, however, does not amount to be the "key distinction", right? Let me hint to those who are at a loss where to look. This is something in the Filter nodes.
Okay, here are the properties of the Filter nodes
One can see that Output List property is empty for the one-row query and has the `Union1002` column for the two-rows query. Well, yes, this is quite a distinction, but it's beyond the scope of this post and not the one we are interested in here ("not key enough", if I may).
I will not intrigue any longer, the key distinction is in the predicates. They do look identical, as `[@Mode]<>'HIDE'`, when viewed in SSMS, but there is the difference actually, which reveals in the query plan XML
In the one-row query, predicate is the plain comparison of variable and constant. And in the two-rows query it's the same comparison, but wrapped into the expression named `ConstExpr1004`. This is the result of *collapsing run-time constants*.
Optimizer modifies the second query internally to somewhat like
```sql
DECLARE ConstExpr1004 bit = (@Mode != 'HIDE');
SELECT t.[Name], LEN(t.[Name])
FROM (VALUES ('AAAA'), ('BBBB')) t([Name])
WHERE ConstExpr1004;
```
This is invalid T-SQL syntax, and its closest valid representation would be
```sql
DECLARE @ConstExpr1004 bit = IIF(@Mode != 'HIDE', CAST(1 AS bit), CAST(0 AS bit));
SELECT t.[Name], LEN(t.[Name])
FROM (VALUES ('AAAA'), ('BBBB')) t([Name])
WHERE @ConstExpr1004 = CAST(1 AS bit);
```
The idea is to evaluate expression (if it qualifies to be a constant during the query run) just one time, at query start, instead of evaluating it repetitively per row[^1].
This example is really bad demonstration of the *run-time constants* feature itself, because here its benefit is shadowed by the other optimization. Yes, boolean comparison is more efficient than string, if it needs to be performed multiple times, but predicate of the Filter is the *startup predicate*, and so is only evaluated once, regardless of the run-time constant presence.
An example with comparison of a date column, like `[DateColumn] > DATEADD(DAY, -30, SYSDATETIME())`, would be a better run-time constants demonstration probably. What used here, however, is just sufficient for its main purpose --- to raise the question: why is run-time constant introduced for the one query and not the other? As one might guess, cardinality estimation is the influencing element here.
Run-time constants can be added at various compilation stages. Although this is possible during simplification or post-optimization, these are quite rare cases. Most typically, as is the case here, run-time constants are created at the dedicated preliminary stage. Selectivity calculators have not come into play at this point yet, but optimizer is already doing some assumptions about cardinalities. Particularly, it assesses cardinality ranges of the nodes making up the logical tree of the query.
The factor that triggers whether or not run-time constant will be introduced here is the *maximum cardinality* of the filter node. It must be greater than one for the run-time constant to be introduced.
The overall logic behind the run-time constants creation is complex enough and cardinality estimation is just one of its elements. It is possible to get a run-time constant when cardinality criterion is not satisfied, as well as not to get one when it is. For the rest of this post, however, this criterion will be (and it truly is) our "must".
## **The Bug**
Now consider we have two variables: an *int* identifier, and a *bit* flag. The requirement is to return identifier value as a row, but only if it has a non-null value and the flag is unset, like this
```sql
DECLARE @Id int, @SomeFlag bit;
SELECT [Id] = @Id
WHERE @Id IS NOT NULL AND (@SomeFlag IS NULL OR @SomeFlag = CAST(0 AS bit));
```
But let me write it this way
```sql
DECLARE @Id int, @SomeFlag bit;
SELECT t.[Id]
FROM (
SELECT [Id] = @Id
WHERE @Id IS NOT NULL
) t
WHERE @SomeFlag IS NULL OR @SomeFlag = CAST(0 AS bit);
```
This is the execution plan of the query
Predicate of the Filter is the conjunction of two conditions
So far so good. Then, additionally, we have a list of identifiers and need to return the union of the variable and the list, like
```sql
DECLARE @Id int, @SomeFlag bit;
DECLARE @ListOfIds TABLE ([Id] int NOT NULL PRIMARY KEY);
SELECT t.[Id]
FROM (
SELECT [Id] = @Id
WHERE @Id IS NOT NULL
UNION
SELECT [Id]
FROM @ListOfIds
) t
WHERE @SomeFlag IS NULL OR @SomeFlag = CAST(0 AS bit);
```
This is the execution plan
Optimizer pushed the `@SomeFlag` check below the union, but see how it is combined with the `@Id` check in the Filter above the Constant Scan
I will give previous predicate expression together with this one, so that the difference is easier to perceive
```none
([@SomeFlag] IS NULL OR [@SomeFlag]=(0)) AND [@Id] IS NOT NULL
[@SomeFlag] IS NULL OR [@SomeFlag]=(0) AND [@Id] IS NOT NULL
```
Parentheses are missing!
At first glance, it looks that combined predicate is incorrect. However, if we run our query, it does not return any rows
But if query were executed as we see it (with incorrect predicate), it would return one row with `NULL` value
Hey, what's the matter? Is it executing incorrectly in addition?
Let's check predicate definition in the query plan XML. Here it is
As can be seen from this, the logical structure of the predicate is correct --- it is the conjunction of two groups, but the `ScalarString` attribute (that's what we see in SSMS's Properties window) is not. You may also have noticed that one of the groups is the expression wrapped into the `ConstExpr1003` identifier, which is the sign of a run-time constant. It indeed is the run-time constant.
When `ScalarString` attribute is generated, algorithm just does not take into account complexity of the expression behind the run-time constant identifier, and performs concatenation as if it was
```none
ConstExpr1003 AND [@Id] IS NOT NULL
```
So, the only incorrect thing here is the textual representation of the combined predicate. The predicate itself is correct, as is the result returned by the query. And that's probably why this bug exists for so long. Nonetheless, its existence can cause confusion during execution plan analysis.
This could be the ending of the section, but one thing is left. In the previous section we have seen that run-time constant was not added for the one-row query, and this is also the case for the first query in this section. However, here we have run-time constant in the branch where the Constant Scan generates one row only. So why is run-time constant added at all?
The answer is that it was added when `[@SomeFlag] IS NULL OR [@SomeFlag]=(0)` predicate part was still above the union, just like in the query text, and similarly to
Maximum cardinality of the filter above the union is 10001 row here. It is derived from the union. And union, in its turn, derives its maximum cardinality from its children: one row from the Constant Scan branch and 10000 rows from reading the empty `@ListOfIds` table. Yes, ten thousand for the empty table, because formula has a threshold
where _Card_*~act~* is the actual table cardinality.
If we rewrite our union query as
```sql
SELECT [Id] = @Id
WHERE @Id IS NOT NULL AND (@SomeFlag IS NULL OR @SomeFlag = CAST(0 AS bit))
UNION
SELECT [Id]
FROM @ListOfIds
WHERE @SomeFlag IS NULL OR @SomeFlag = CAST(0 AS bit);
```
run-time constant will not be added in the Constant Scan branch and textual representation of the predicate in the Filter above the Constant Scan will be correct.
### More of The Bug
According to [Showplan Schema](https://schemas.microsoft.com/sqlserver/2004/07/showplan/), `ScalarString` is the attribute of `ScalarOperator` elements, which represent scalar expressions of all kinds. And scalar expressions are an element of a wide range of ShowPlanXML node types. So, theoretically, this bug may appear anywhere a run-time constant is combined with some other expression.
Here is a variant of the bug arising from an expression in the `SELECT` list
```sql
DECLARE @A int, @B int;
DECLARE @table TABLE ([C] int NULL);
SELECT (@A + @B) * [C]
FROM @table;
```
This is the execution plan of the query
and following are the Compute Scalar properties
where `(@A + @B) * [C]` became `@A + @B * [C]`. Oops!
[^1]: [Conor vs. Runtime Constant Functions](https://learn.microsoft.com/en-us/archive/blogs/conor_cunningham_msft/conor-vs-runtime-constant-functions) by Conor Cunningham