SqlException "Divide by Zero" in order by clause

Question

I'm currently trying to write a SQL query, that get's the top 100 rows with the biggest percent increase over two values. My current solution is to write my query like this:

//NULL is also not accepted in where clause, shortened version
SELECT TOP 100 id, name, currentValue, pastDayValue FROM comparables
WHERE NOT pastDayValue = currentValue AND NOT pastDayValue = 0 AND NOT currentValue = 0
ORDER BY (currentValue - pastDayValue) / (currentValue / 100) desc;

The only issue is: it can occur that the values are zero or the same, and altough I do want to filter these out (see where clause), I still get a System.Data.SqlException 'Divide by zero' which has to come from the order by clause.

I've done some research, and as far as I've found, the where clause should be executed before the order by, therefore filtering any zero values out (the tables can also have NULL values which I do escape in the where clause aswell, just didn't include it into the query above to make it shorter). The only ways I saw to prevent this, are either NULLIF or a CASE clause, but I'm not sure how to work those into my order by statement.

I'd appreciate any ideas on how to solve this! (Note: I'm working on SQL-Server 2019)

Looks like a case of integer division error. Is maybe currentValue < 100 ?? The use (currentValue / 100f) or some DB floating point type..! Or, maybe even better reorder the math terms suitably, maybe like so: `(currentValue - pastDayValue) * 100 / currentValue` — TaW, Dec 29 '21 at 11:22
Just in case , try a case statement to know the problem (the -1 need to be fixed, just to get some weird info to find the guilty row) ORDER BY (currentValue - pastDayValue) / CASE WHEN currentValue = 0 THEN -1 ELSE (currentValue / 100) END desc — J.Salas, Dec 29 '21 at 11:27

score 1 · Accepted Answer · answered Dec 29 '21 at 11:31

1

This is possibly caused by dividing an Integer-value (currentValue) by an Integer-value. If an Integer dividend is divided by an Integer divisor, the decimals will be truncated.

See: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/divide-transact-sql?view=sql-server-ver15

A possible solution would be to change the Integer 100 to a Decimal number like this:

ORDER BY (currentValue - pastDayValue) / (currentValue / 100.0) desc;

answered Dec 29 '21 at 11:31

Dieselfahrverbot

111
4

You can also skip the divide by 100 part completely. It doesn't change result ordering. – jarlh Dec 29 '21 at 11:34
Yes thank you! That actually was it, good to know that that can happen – TheBest_Kappa Dec 29 '21 at 11:35

score 0 · Answer 2 · answered Dec 29 '21 at 12:51

0

Your mistake is:

"the where clause should be executed before the order by, therefore filtering any zero values out"

This is absolutely not true. It refers to the logical execution of WHERE, but the compiler will often rearrange a query to take advantage of indexes etc. The one thing it does not factor in is possible exceptions, which means that the calculation of the ORDER BY expression may happen before the WHERE and cause an exception. So you must null out zeroes by using NULLIF or CASE

SELECT TOP (100)
  id,
  name,
  currentValue,
  pastDayValue
FROM comparables
WHERE pastDayValue <> currentValue
  AND pastDayValue <> 0 
  AND currentValue <> 0
ORDER BY (currentValue - pastDayValue) / NULLIF(currentValue, 0) DESC;
--alternatively
ORDER BY pastDayValue / NULLIF(currentValue, 0) ASC;

answered Dec 29 '21 at 12:51

Charlieface

52,284
6
19
43

I'd expect the opposite. The logical execution decides the possible errors. E.g. a dbms is not supposed to raise an error for `select 1 / n from t where n <> 0`, even if there are n = 0 values in the table. – jarlh Dec 29 '21 at 16:51
@jarlh You're right that that is what happens in *most* cases (after all sorting a fewer number of rows is normally cheaper), but there is no guarantee anywhere that it will be so. The possibility of an exception being raised is not taken into account by the optimizer. – Charlieface Dec 29 '21 at 17:00
That's what I call a bug. Luckily I've never run into it. – jarlh Dec 29 '21 at 17:02
I think you need to look at it this way: the optimizer assumes that exceptions cannot happen, because it considers that if they were then the results returned probably wouldn't be correct anyway. Ergo, you need to code defensively and take account that any possible exception could happen. Perhaps it would be wiser if it didn't, but that may cause it to create worse plans in many situations. Here are more examples of a similar issue involving `SUBSTRING` https://dba.stackexchange.com/a/301650/220697 and https://stackoverflow.com/a/67259538/14868997, this can also happen with `CONVERT` – Charlieface Dec 29 '21 at 17:11
Do you have a link to an official (more or less) Microsoft source describing this problem? (I couldn't find any.) – jarlh Dec 29 '21 at 17:51
@jarlh That's the point: it simply doesn't take it into account. You'd expect a mention of it here https://learn.microsoft.com/en-us/sql/relational-databases/query-processing-architecture-guide?view=sql-server-ver15#optimizing-select-statements but there is just no mention of it. – Charlieface Dec 29 '21 at 20:57
Think also about what you ask of the engine: to be able to use the same plans as it had, and still throw an exception if a value made it to the final `SELECT`, it would have to have an extra flag for every value that has a division, marking it as excepted. You couldn't use `NULL` as that is against ANSI rules, and would also be confused with a normal `NULL`. An extra flag like that could cause a further slowdown in all divisions in every piece of code. Whereas this way, the standard null logic is used, and the incorrect rows are just filtered out later. – Charlieface Dec 29 '21 at 21:01

score -1 · Answer 3 · answered Dec 29 '21 at 11:37

ORDER BY (currentValue - pastDayValue) / (currentValue / 100.0) desc;

eq

ORDER BY (currentValue - pastDayValue) / (currentValue) desc;

eq

ORDER BY (currentValue) / (currentValue) - (pastDayValue) / (currentValue) desc;

eq

ORDER BY 1 - pastDayValue / currentValue desc;

eq

ORDER BY pastDayValue / currentValue asc;

SqlException "Divide by Zero" in order by clause

3 Answers3