May 28, 2015 at 9:49 am
DataTherapist (5/28/2015)
Say WHAT?!?Apparently I have NOT been around the block enough times, because this was new to me.
I would have not suspected that 1/2 is NOT .5....!
No small wonder that folks get bitten on this. It is just not what you expect.
If I'm dealing with a variable that I've declared, then I think I might have a chance of being more aware of how the datatype would affect the math...
I just had to do some testing.
SQL2008R2:
select CAST(100 AS Decimal)/CAST(200 AS Decimal) --> 0.5000000000000000000
select CAST(100 AS Decimal)/200 --> .5000000
select 100/CAST(200 AS Decimal) --> 0.5000000000000000000
select CAST(100 AS float)/CAST(200 AS Decimal) --> 0.5 interesting...
select 100.0/200 --> .5000000
select 100/200 --> 0
select 108/100 --> 1
hmmmm....
Your findings have to do with rules of data type precedence: https://msdn.microsoft.com/en-us/library/ms190309.aspx. When mixing data types as you do in many of the examples above, one gets converted to the other before the operator is processed.
The last two examples also are related to how SQL Server infers data types from constants. I have no link at hand, but "100.0/200" is evaluated as a decimal divided by an intiger, so the integer is converted to decimal and then the decimals are divided. The other two examples have only integers in the constants, so no conversion is needed and integer division applies.
May 28, 2015 at 10:08 am
Hugo Kornelis (5/28/2015)
DataTherapist (5/28/2015)
Say WHAT?!?Apparently I have NOT been around the block enough times, because this was new to me.
I would have not suspected that 1/2 is NOT .5....!
No small wonder that folks get bitten on this. It is just not what you expect.
If I'm dealing with a variable that I've declared, then I think I might have a chance of being more aware of how the datatype would affect the math...
I just had to do some testing.
SQL2008R2:
select CAST(100 AS Decimal)/CAST(200 AS Decimal) --> 0.5000000000000000000
select CAST(100 AS Decimal)/200 --> .5000000
select 100/CAST(200 AS Decimal) --> 0.5000000000000000000
select CAST(100 AS float)/CAST(200 AS Decimal) --> 0.5 interesting...
select 100.0/200 --> .5000000
select 100/200 --> 0
select 108/100 --> 1
hmmmm....
Your findings have to do with rules of data type precedence: https://msdn.microsoft.com/en-us/library/ms190309.aspx. When mixing data types as you do in many of the examples above, one gets converted to the other before the operator is processed.
The last two examples also are related to how SQL Server infers data types from constants. I have no link at hand, but "100.0/200" is evaluated as a decimal divided by an intiger, so the integer is converted to decimal and then the decimals are divided. The other two examples have only integers in the constants, so no conversion is needed and integer division applies.
Thanks Hugo!
I would like to think that I'm always aware of the data type precedence in back of my mind...
The problem for me is that I see this "SELECT 1/2" as normal math, just like "SELECT 1+2"... but I need to shift my thinking to see these numbers as a sort of self declared variable - which come with a corresponding data type.
So behind the scenes:
SELECT 1/2 becomes:
DECLARE a INT=1, b INT=2
SELECT a / b
... and then the phrase "When both operand expressions have the same data type, the result of the operation has that data type." from the data type precendence kicks in.
I think I'm learning more from these QODs than hours of book learning.
May 29, 2015 at 2:47 am
Just think of it as a 'bug' in SQLs logic that you have to learn to live with
May 29, 2015 at 7:00 am
Revenant (5/27/2015)
Really easy - I am (almost) ashamed that I got a full point...
Points are countable, not continuous, so if you had got a less than full point it would have been rounded down to no points at all. 😛
Tom
May 29, 2015 at 8:42 am
Koen Verbeeck (5/27/2015)
Ed Wagner (5/27/2015)
Koen Verbeeck (5/27/2015)
Easy one. Been bitten by integer division many, many times.Agreed on both points. Integer division can also be used to your advantage, but that article isn't published yet.
It's quite useful when you don't need the remainder of the division, indeed.
In all other cases, it's just extremely frustrating 😀
The thing I've found frustrating over the years is people (usually database people) declaring (whether in a column definition in a DDL statement or in a variable declaration statement) that some data is integer data and then being surprised that it behaves as integer data.
Neither mathematicians nor logicians fail to understand that the integer domain is closed under integer operations (in the absence of operations that break the rules (zero divide) and also, in computing systems with finite storage, operations that fail because the implementation of the type doesn't permit all integer values to be represented). Too many DBAs do fail to understand that, but have no problem at all with the idea that the decimal(10,0) type, just like the integer type, doesn't permit non-integers to be represented.
Maybe these DBAs would like the integer type to allow operations to produce non-integer types, just as operations on decimal(10,0) can produce results of type decimal(21,11) instead of type decimal(10,0)? But I suspect that a lot of people would then complain about having to write (A-A%C)/C when they wanted genuine integer arithmetic, and also that it would cause a lot of existing code to stop working - so perhaps those who currently moan about int/int delivering int should stop moaning and learn to write "(cast A as decimal(10,0))/C" instead of "A/C" if they want a non-integer result - or even write "1.0*A/C", which has the same effect as writing "(cast A as decimal (13,1))/C" (so that the result of the division will have type decimal (24,12) instead of decimal (21,11)). (Warning: this needs a different precision in the cast if the integers are bigint rther than int; scale 0 is still OK)
Tom
May 29, 2015 at 11:54 am
Declare @a int
Set @a = 1.0/2.0
Select @a
-- returns 0
Should probably be expected by everybody.
Declare @b-2 float
Set @b-2 = 1/2
Select @b-2
-- returns 0
I have sympathy that this is unexpected. The language has to interpret this someway, and people can learn how each language defines this, but there are different possible interpretations. People being surprised means they don't know the specifics for their environment, yet -- perhaps they haven't fully considered the options and haven't encountered a specific example.
I referenced, above, that Python has a different interpretation of integer division involving negative numbers (also modulo).
foo = -8//3 #force integer division
print(foo)
# yields -3
Declare @C int
Set @C = -8/3
Select @C
-- returns -2
I found this surprising, even being aware of integer division in languages such as C and T-SQL.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply