The puzzles are interesting and in most cases, if we try to play around a little bit more, many more interesting extensions can be added. A couple of weeks back, I wrote a round – SELECT One by Two – Why Does SELECT 1/2 Returns 0 – Interview Question of the Week #067. It brought some good interactions as comments over the blog and I was happy we were all excited about something as simple as this.
There were tons of learnings that cannot be denied. A lot of you were giving me options of converting the values or cast the values and many more options have been explained in the comments section of that blog. I would like to refrain from rehashing the same. But keep those comments flowing – we get to learn more from each other.
Now I went ahead to play a small extension to the same query. See the 3 queries written below:
-- Query 1 SELECT 1.*1/2*1. "Query 1" GO -- Query 2 SELECT 1.*1/2 "Query 2" GO -- Query 1 SELECT 1/2*1. "Query 3" GO
Do you think there is anything different on these 3 queries? Shouldn’t they return the same results? My first thought was to get the same results. But there will always be surprises to learn from.
Trust me, I have not done any manipulation here and the query results have been done as-is from my SQL Server Management Studio window. I have made the problem statement simple. Why am I getting a different output? What have I don’t differently? Why is SQL Server behaving like this? Is this the expected output?
Note: Part of the solution is very much available in the first post. Do make sure to read the post and the comments carefully and do share your understanding / reason for the above output. Let us learn together again.
Reference: Pinal Dave (https://blog.sqlauthority.com)
9 Comments. Leave new
As their were no brackets it moves from left to right in each case.
lets consider your example:
1) 1.*1/2*1. => 1./2*1. => .5*1 => .5
2) 1.*1/2 => 1. / 2 => .5
3) 1/2*1 => 0 *1 => 0
Would someone really ask this in an interview? I wouldn’t – too abstract, maybe if I gave them a computer, gave them the query, and output then asked them to explain why its different.
Multiplication and Division are on the same level, so, the order of the factors don’t alter the product.
The only difference here is that the top 2 are not integer, and the last is integer.
1./2 = 0.5
1/2 = 0 (integer)
you can see it better here
11/5 = 2 (integer)
11./5 = 2.2
11/5. = 2.2
I have found it helpful to include “.” for each number, which then keeps float values true.
SELECT 1.0/2.0*1. “Query 3”
GO
or
SELECT 1./2.*1. “Query 4”
GO
or
SELECT cast(1 as float)/cast(2 as float)*cast(1 as float) “Query 5”
Good Morning,
SELECT 1.*1/2*1. “Query 1” –OUTPUT 0.5000
GO
SELECT 1.*1/2 “Query 2” –OUTPUT 0.5000
GO
SELECT 1/2*1. “Query 3” –OUTPUT 0
GO
SELECT 1/2.*1 “Query 4” –OUTPUT 0.5000
GO
SELECT 1/2*1.0 “Query 5” –OUTPUT 0.0
GO
My answer is see the “QUERY 3 and QUERY 5′
Consider Query 3 is at end of the values should be 1. So their is no values for after the decimal. For Query 3 IN 1. is INT values Because the no values for after decimal,at the same time Query 5 has at the end of the value for 1.0 in .0 as one of the value(decimal) so it take as decimal .Output is 0.0
select 1/(2*1.0) = 0.5
but
select 1/2*1.0 = 0.0
while
select 2*1.0 = 2.0
why so ?? ??
1 / 2 * 1.0 = 0.0
Because:
1 is an integer and 2 is an integer. And the *integer* result of 1 / 2 = 0. When reduced, the expression becomes 0 * 1.0 which obvious = 0.
To fix this, you need to explicitly state that you want to use floating point instead of integer. You do this simply by adding a dot at the end of the number.
1. / 2. * 1. will work exactly as you expect, because we have specified floating point instead of integer, which means 1. / 2. = .5 as expected, which results in .5 * 1.0 = .5
nice explanation .. thanx
This is due how a parser works
In the first two queries we have 1.*1/2 an expression tree is generated as follows
DivNode(TimesNode(1.0 , 1),2)
Since the TimesNode receives a Float number and an Integer, TimesNode must return a value that can contain the result of operating both number. So in this case it returns 1.0 as a float number.
The same applies for DivNode(1.0,2) which returns 0.5
In the third query you have 1/2*1 the expression tree generated is
TimesNode(DivNode (1,2),1.0)
So in this scenario DivNode receives two Integers, this tells the parser the operation needed is an Integer division that truncates the floating part. Then DivNode(1,2) return 0 as an Integer.
And at the end TimesNode(0,1.0) returns 0 probably as a Float Number.