Search code examples
sqlsql-serverwindow-functionsdivide-by-zero

Why doesn't this windowed expression result in a divide by zero error?

I came across this answer on Programming Puzzles & Code Golf. In it, the author uses the expression (though the answer has since been edited to use a different solution):

row_number()over(order by 1/0)

I would have expected the 1/0 to result in a divide by zero exception, but it doesn't.

When I asked the author on PPCG, they replied "because 1/0 is not being calculated. where exists(select 1/0) will have the same effect". This leaves me a bit nonplussed, because where exists(select 1) is valid syntax, but row_number()over(order by 1) is not. So why is the expression in the order by not calculated? The result of the expression is an integer, and an integer is not allowed in the order by. How does SQL Server handle the order by in that case? I assume the effect is the same as row_number()over(order by (SELECT NULL)), but if we give an expression, I'd expect that expression to be evaluated.

Coincidentally, if one uses something like:

SELECT  ROW_NUMBER() OVER ( ORDER BY A.x )
FROM    (
            SELECT  *
            ,       1 / 0 x
            FROM    master..spt_values
        ) A

Again, no divide by zero error is reported (when the x column is not selected, naturally). So why is this allowed when an integer is not?

Solution

  • Let's try a few more examples...

    ROW_NUMBER() OVER (ORDER BY 2/1)

    Windowed functions and NEXT VALUE FOR functions do not support integer indices as ORDER BY clause expressions.

    The problem with 2/1 is that it gets constant folded to 2 early in the optimisation process so is treated the same as ROW_NUMBER() OVER (ORDER BY 2) which is not permitted.

    ROW_NUMBER() OVER (ORDER BY LOG(1))

    Windowed functions and NEXT VALUE FOR functions do not support constants as ORDER BY clause expressions.

    Again constant folding kicks in - this time the result is not an integer index but SQL Server doesn't allow constants anyway.

    ROW_NUMBER() OVER (ORDER BY LOG(-1))

    This succeeds on recent versions of SQL Server - on old versions such as SQL Server 2008 you will see An invalid floating point operation occurred.. This specific case is mentioned in the context of CASE here. The compile time constant folding broke the semantics of CASE and this was fixed in more recent versions.

    The suppression of constant folding in these error cases (LOG(-1) and 1/0) is enough for it to bypass the checks that give the error messages above. However SQL Server still recognizes that the expression is in fact a constant and can be optimized out later (so you don't get a sort operation to order the rows by the result of this expression).

    During the simplification phase ROW_NUMBER ORDER BY non_folded_const_expression is simplified to just ROW_NUMBER and non_folded_const_expression removed from the tree as no longer referenced. Thus it causes no problems at runtime as it does not even exist in the final execution plan.