Earlier I asked a puzzle SQL SERVER – Puzzle – Why Decimal is Rounded Up? and it was very well received by all of you. You can read various comments posted in the blog post and they are indeed very enlightening. After posting that blog post, I received quite a lots of emails asking for more puzzle similar to that. Based on your request, here is another puzzle which is very similar to the earlier puzzle but have a very different approach.
The question is why following T-SQL gives a famous divide by zero error.
SELECT 10000/(17/17/17) AS Result2;
When we execute above query it gives following error:
Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered.
Here is the screenshot of the error.
Hint: Read the comments in this blog post.
I will be announcing the winner of this puzzle in my newsletter. There will be a surprise gift of USD 29. Leave your answers in the comment.
Reference: Pinal Dave (https://blog.sqlauthority.com)
36 Comments. Leave new
Hi Pinal,
Denominator will be converted into 0.05882… by compiler so
17/17=1
1/17=0.05882… (decimal value.)
when we divide this decimal value it gets cast as an INT and rounds to 0.
So ultimately
10000/0 will give us ‘divide by zero’ error.
Thanks,
Vivek Chandel
Hi Pinal,
(17/17/17) = 1/17 = 0 >> It’s an integer division (rounded up)
So 10000/0 is not permitted division, why the error message
It is “SELECT 10000/(1/17) AS Result2;” which execute as “SELECT 10000/0 AS Result2;”
That is why giving divide by zero error envountered.
(17/17/17)
17/17=1 => It starts executing from left to right
1/17=0 => Result with in the bracket becomes zero
10000/(17/17/17)
10000/0 = ERROR !!
Actually 17/17/17 return Zero so it is returning error.
17/17=1
1/17=0 (when 1/17 it gives reminder 1 and result 0)
1%17=1 (reminder 1)
this is the reasion it is returing 0
Actually 17/17/17 return Zero so it is returning error.
17/17=1
1/17=0
1%17=1
because actuality SELECT 10000/(0) AS Result2; so this error is coming
Hello,
I will go step-by-step.
Firstly, the result of the SELECT (17/17/17) is – 0.
Why?
Arithmetically, it will perform this operation like below (internally) –
select (17/17/17/1)
Output: 0
Reason: We are performing the integer division.
If we run the below statement with decimal point values, here’s the result. (To contrast)
select 1.0/17.0
Output: 0.058823
Hope this is clear enough. Let me know if you need more explanation on this.
Secondly, we are performing,
SELECT 10000/(17/17/17) AS Result2
In short, it is an integer division, and we are dividing 10000/0 (as explained above). So, it will throw the below message.
“Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.”
We can avoid this using below statement. (There are more ways, but this is just an example)
Example 1:
SELECT 10000.0/(17.0/17.0/17.0) AS Result2
Output: 170000.0000340000000068
Example 2:
SELECT 10000/17/(17/17) AS Result2
Output: 588
Thanks,
Rohit Kalmankar
Simple and sober answer:
BODMAS: Order of Operation
B-Brackets first
O-Orders (ie. Powers and Square Roots, etc.)
DM-Division and Multiplication (left-to-right)
AS-Addition and Subtraction (left-to-right)
Now no need to explain further.
Thank you.
you added () in denominator. so it will take presidency first and perform operation 17/17/17 which is 1/17 equal 0.058 but its integer only so it result 0. so now equation become like 1000/0 will hit divide by zero error.
Below expression will not give an error.
select 1000/17/17/17/17
OR
select 1000/(17/17/17.0) –> 1000/0.058823 –> it will work without error.
Thanks you for sharing puzzle. keep sharing
Thanks
Rikhil
Hi,
As per operator preedence, SQL will process brackets info first from left to right.
In bracket we have (17/17/17), it will process like (17/17)/17= (1)/17 = 0.
If I break expression , it will be like 10000/((17/17)/17)
As it will convert Select (17/17/17) to integer which will come as 0.so the error is coming.
if we simply use (17/17/17.0), it will not give erro because now the sql server will converting the values in decimal.
Due to the datatype of 17 being an int, causing loss of data when dividing.
Declare @Decimal Decimal(18,4) = 17
Declare @Int Int = 17
SELECT 10000/(@Decimal/@Decimal/@Decimal) –Works Fine
SELECT 10000/(@Int/@Int/@Int) –Devide by zero
17/17/17 = 0.0588235294117647
The ‘int’ data type has a precision of 10, scale of 0 and length of 4. If an arithmetic operator has two expressions of same type, the result has the same data type with the precision and scale defined for that concerned data type.
Hence, applying BODMAS for an expression 1000 / (17/17/17), it becomes 1000 / (1/17).
Now, 1/17 has a result of 0.0588235; Since both numerator and denominator are of type ‘int’, having the scale of 0. Hence, the result becomes 0.
Finally, the expression becomes 1000 / 0 resulting division by zero error.
Thanks && Regards,
Balasubramanian Kumaran
By applying BODMAS, the expression 1000 / (17/17/17) becomes 1000 / (1/17).
The int data type has a precision of 10, a length of 4, and a scale of 0. If an arithmetic operator has two expressions of the same type, the result has the same data type with the precision and scale defined for that type.
Hence, 1/17 returns 0 though it actually results 0.05. It is because of integer type has a scale of 0.
Now, the expression becomes 1000 / 0 which results in division by zero error.
Thanks && Regards,
Balasubramanian Kumaran
Guys, i discovered something that looks like sql bug, it gives division by zero where it should not be possible. I checked tables for integrity and no errors was found. Here is the smallest query i could get, if i remove any of the components it starts working, it is enough to remove order by and it works. Here is the query generating division by 0:
SELECT CONVERT(VARCHAR(1), startTime), COUNT(DISTINCT vi), 1 / SUM(1)
FROM Archive AS V
INNER JOIN ctCategory AS C ON C.ID = V.categoryID
GROUP BY CONVERT(VARCHAR(1), startTime)
UNION
SELECT CONVERT(VARCHAR(1), startTime), COUNT(DISTINCT vi), 1 / SUM(1)
FROM Archive
GROUP BY CONVERT(VARCHAR(1), startTime)
ORDER BY 1
Query makes little sense as i removed as many things as possible, now it is at the state where you can remove any of the fields or conditions or order or union and it will start working without error. Thing is 1/ SUM(1) should never give division by zero. Just wondering what do you think about it?
SELECT (10000)/(17.0/17/17) AS Result2;
need to do
SELECT 10000/convert(numeric(18,2),@i)/convert(numeric(18,2),@j)/convert(numeric(18,2),@k) AS Result2;