CTE column caused an overflow - Order By only!
SQL Server does not guarantee the timing or number of evaluations for scalar expressions. This means that a query that might throw an error depending on the order of operations in an execution plan might (or might not) do so at runtime.
The script uses CROSS APPLY
where CROSS JOIN
was probably intended, but the point is that the potential number of rows over which the ROW_NUMBER
is calculated depends on the size of the sys.objects
cross product.
For a database with sufficient objects, an overflow for the DATEADD
result is an expected risk. For example, this is reproducible using the AdventureWorks sample database, which has 637 entries in sys.objects
. The size of the cross product is 637 * 637 = 405,769; the following throws an overflow error:
SELECT DATEADD(DAY, 1 - 405769, GETDATE());
One might argue that there is no need to materialize the result of an expression for rows that are not returned (and therefore not throw an error), but that is not the ways things work today.
Consider:
- The highest
ROW_NUMBER
will give the lowest value forDateCode_FK
in theDATEADD(DAY, 1 - ROW_NUMBER()...
expression - The presentation
ORDER BY
isDateCode_FK DESC
- Only the first 10 rows in presentation order are required
If the optimizer contained logic to reason that lower row numbers lead to higher DateCode_FK
values, an explicit Sort would not be needed. Only ten rows would need to flow through the execution plan. The ten lowest row numbers are guaranteed to produce the ten highest DateCode_FK
values.
Regardless, even where a Sort is present, the argument is that SQL Server should not throw an error because none of the ten rows actually returned are associated with an overflow error. As I said above, "that is not the ways things work today".
An alternative formulation that avoids the error (though it is still not guaranteed to do so - see my opening remark), makes the row numbering deterministic, and uses CROSS JOIN
:
WITH cte_Date (DateCode_FK) AS
(
SELECT TOP (10)
DATEADD
(
DAY,
1 - ROW_NUMBER() OVER (
ORDER BY
so1.[object_id],
so2.[object_id]),
GETDATE()
)
FROM sys.objects AS so1
CROSS JOIN sys.objects AS so2
ORDER BY
ROW_NUMBER() OVER (
ORDER BY
so1.[object_id],
so2.[object_id]) ASC
)
SELECT TOP (10) -- Redundant TOP
d.DateCode_FK
FROM cte_Date AS d
ORDER BY
d.DateCode_FK DESC;
Paste the Plan