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?
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 (
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
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.
Why doesn't the Leaning Tower of Pisa fall over?, If playback doesn't begin shortly, try restarting your device. Your browser does not currently Duration: 5:06 Posted: Dec 3, 2019 PHILADELPHIA — A bubble can’t contain Major League Baseball. That’s the reasonable contention that Rob Manfred made Monday to the MLB Network as concerns grew about the league’s viability
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.
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.
Why doesn't Trump try harder to solve the coronavirus crisis?, The president has failed at the one task that could help him achieve all of his goals — confronting the pandemic with a clear strategy and� It's no surprise that President Donald Trump says he won't follow CDC recommendations to wear a mask to stop the spread of coronavirus, writes Michael D'Antonio. Ever the defiant and self-involved
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
Why doesn't Python have a main function? | by Pawan Jain, if __name__ == '__main__' is not function. And the author doesn't recommend it to write this either. Want to know the reasons? but how to write� Why China’s Growth Sometimes Doesn’t Add Up Unless Chinese consumers start spending again and businesses start investing, 3.2% growth might be hard to sustain
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))
exists, I use
IT Doesn't Matter, As information technology's power and ubiquity have grown, its strategic importance has diminished. The way you approach IT investment and management will� Robert De Niro on Tuesday trashed Donald Trump’s floundering handling of the coronavirus pandemic, slamming the president as a “lunatic” who “doesn’t even care how many people die” so long as he wins the 2020 election.
Why Doesn't This HR Department Get Any Respect?, And within a year, two of the five remaining senior managers had resigned as well. “It doesn't matter why those two left,” Robinson told Rose between bites of� Why More Testing Doesn't Explain the Rise in COVID-19 in Several New U.S. Hotspots. By Chris Wilson and Tara Law June 19, 2020 4:03 PM EDT A
If you connect your device to your computer with a USB cable and your computer doesn't recognize your iPhone, iPad, or iPod, get help.
Why doesn’t the U.S. use the metric system? Phil Lewis/shutterstock As of today, the entire world has adopted the metric system, with the exception of the United States, Myanmar, and Liberia.
- I suspected this was the case. I wholeheartedly agree with your last sentence!
- @Damien_The_Unbeliever . . . I don't think this answer is quite right. Expressions on integers do NOT work, so
1 + 0results in an error.
- Yes, but it's an absolute ban on integer literals. You're certainly allowed some expressions on integers (simplest case, a column of type
int). Now we're trying to pick which things can/will fool the optimizer.
- Interesting, your query indeed doesn't return an error, but when I add
order by 1/0to a query that returns multiple rows it does error.
- @HoneyBadger even if you select from an empty table and order by 1/0, it will throw a division by 0 error
- @t-clausen.dk I guess the optimizer is even clever enough to skip the order by when it knows only one row will be returned.
- @HoneyBadger that is not the whole truth, order by 1/0 will fail when one row is selected from a table
- @t-clausen.dk, yeah, I meant when there is no
FROMclause only one row can possibly be returned. This is known at compile time. When you select from a table, the optimizer can assume only one row will be returned, but it cannot know.