Auto Parameterization and Implicit Conversion
When SQL Server processes a query that can be auto parameterized, the results can be a bit, shall we say, unexpected. Auto parameterization makes an implicit conversion to numeric(10,0) when there is a divisor present in the expression being evaluated. This conversion to numeric(10,0) can result in an unexpected loss of precision, and query results that differ compared to a non-autoparameterized query.
David Garrick as Richard III, by William Hogarth, circua 1745.
Take the following minimal, complete, and verifiable example. First, we’ll create a couple of tables:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
DROP TABLE IF EXISTS dbo.t2; DROP TABLE IF EXISTS dbo.t1; GO CREATE TABLE dbo.t1 ( t1_id decimal(20,18) NOT NULL CONSTRAINT t1_pk PRIMARY KEY CLUSTERED ); CREATE TABLE dbo.t2 ( t2_id decimal(20,18) NOT NULL CONSTRAINT t2_pk PRIMARY KEY CLUSTERED , t1_id decimal(20,18) NOT NULL CONSTRAINT t2_t1_id_fk FOREIGN KEY REFERENCES dbo.t1(t1_id) ); |
Next, we’ll add a couple of rows:
|
1 2 3 4 5 6 7 |
DECLARE @val decimal(20,18) = 7 / 113.2623792124923; INSERT INTO dbo.t1 (t1_id) VALUES (@val); INSERT INTO dbo.t2 (t2_id, t1_id) VALUES (42, @val); |
This shows the rows present in each table:
|
1 2 3 4 5 |
SELECT * FROM dbo.t1; SELECT t2.t1_id, t2.t2_id FROM dbo.t2; |
The output:
╔══════════════════════╗ ║ t1_id ║ ╠══════════════════════╣ ║ 0.061803398874989670 ║ ╚══════════════════════╝ ╔══════════════════════╦═══════════════════════╗ ║ t1_id ║ t2_id ║ ╠══════════════════════╬═══════════════════════╣ ║ 0.061803398874989670 ║ 42.000000000000000000 ║ ╚══════════════════════╩═══════════════════════╝
Now, let’s run an auto-parameterized query:
|
1 2 3 |
SELECT * FROM dbo.t1 WHERE t1.t1_id = 7 / 113.2623792124923; |
The output contains zero rows!
The actual execution plan:
Plan showing auto-parameterization with implicit conversion to numeric(10,0)
Notice the WHERE clause has WHERE [t1].[t1_id]=@1/@2 – the @1 and @2 indicate the query has been auto-parameterized. If you look at the query plan XML, you’ll see StatementOptmLevel="TRIVIAL" and the <ParameterList> section contains the following:
|
1 2 3 4 |
<ParameterList> <ColumnReference Column="@2" ParameterDataType="numeric(16,13)" ParameterCompiledValue="(113.2623792124923)" ParameterRuntimeValue="(113.2623792124923)" /> <ColumnReference Column="@1" ParameterDataType="int" ParameterCompiledValue="(7)" ParameterRuntimeValue="(7)" /> </ParameterList> |
The plan clearly shows the auto-parameterized values have an implicit conversion to numeric(10,0), as seen in the highlighted section of the plan above. This implicit conversion is converting the value 0.061803398874989670 to 0.061803398874989, which returns no matching results.
However, if we explicitly parameterize the query, we get the expected results:
|
1 2 3 4 5 |
DECLARE @val decimal(20,18) = 7 / 113.2623792124923; SELECT * FROM dbo.t1 WHERE t1.t1_id = @val; |
╔══════════════════════╗ ║ t1_id ║ ╠══════════════════════╣ ║ 0.061803398874989670 ║ ╚══════════════════════╝
Similarly, if auto-parameterization is not available, for instance when we force a non-trivial query plan to be generated, the expected results are returned:
|
1 2 3 4 |
SELECT * FROM dbo.t1 INNER JOIN dbo.t2 ON t1.t1_id = t2.t1_id WHERE t1.t1_id = 7 / 113.2623792124923; |
╔══════════════════════╦═══════════════════════╦══════════════════════╗ ║ t1_id ║ t2_id ║ t1_id ║ ╠══════════════════════╬═══════════════════════╬══════════════════════╣ ║ 0.061803398874989670 ║ 42.000000000000000000 ║ 0.061803398874989670 ║ ╚══════════════════════╩═══════════════════════╩══════════════════════╝
The plan for the above query looks like:
Non-auto-parameterized query plan
As you can see above, the plan contains no implicit conversion, and returns the desired results.
This is admittedly a bit of an edge-case, but still worth knowing about. Microsoft has documented this behavior at their Docs page covering the int data type.
Thanks for reading this post, and please check out the rest of our posts on T-SQL!