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.