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

Question

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?

Answer 1

You're not allowed integer literals in this ORDER BY but you are allowed expressions returning integers - otherwise you wouldn't be able to use e.g. CASE expressions.

So that's why you're not allowed OVER (ORDER BY 1) but are apparently allowed OVER (ORDER BY 1/0). I don't think it's a deliberate feature to allow you to write this. You're certainly not allowed a divide by zero if your expression is actually dependent on any columns from the rows - this generates an error:

select name,object_id,ROW_NUMBER() OVER (ORDER BY 1/(object_id-3))
from sys.objects

So, I'd put it down to "the optimizer is smart enough to realise that this expression doesn't depend on any row values, therefore it's the same value for all rows so we do not need to compute it". In a sane language, this would generate a warning.

Answer 2

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.

Answer 3

SQL Server convert order by expression value to constant rather than actual evaluating the expression.

So in case of integer constant it may be either a positive or negative value. Hence it doesn't give error.

You can also check this.

Select 'a' as [Test] order by 1/0

Answer 4

This is informed speculation.

SQL Server optimizes constant expressions during the compile phase of the query. Under some circumstances, errors in the expressions -- such as divide-by-zero -- are ignored, because the expressions may not be needed in the final result. For whatever reason, the error is not tagged as a "constant".

Divide-by-zero errors generated during the execution phase are not ignored.

Personally, I would never use a non-sensical expression like this in any real code (only demonstration code). This returns a divide-by-zero in most other databases.

I would write the first query as:

row_number() over (order by (select null))

For exists, I use select 1.