5

In all other languages (arithmetic engines in general) putting an extra set of parenthesis around operators of same priority does not impact results. But recently in a testing project I noticed that MS SQL server changes the results in those cases. Please take a look at the query below, and let me know if you have any idea (or a setting in SQL Server administration) or any links to MSDN article explaining the behavior.

select (0.55 * 287.61 / 0.66) calc_no_parens
,(0.55 * (287.61 / 0.66)) calc_parens
,round(0.55 * 287.61 / 0.66,2) no_paren_round
,round(0.55 * (287.61 / 0.66),2) paren_round;

Results

Column  Record 1
calc_no_parens  239.6750000
calc_parens     239.67499985
no_paren_round  239.6800000
paren_round     239.67000000

To me, first two of them should return 239.675, and round should give 239.68.

Adarsha
  • 2,267
  • 22
  • 29
  • How different are the answers? Could this be just a floating-point rounding/precision issue? – lc. Sep 17 '12 at 04:03
  • They differ by a fraction (see the update in my question). But this is not a problem in any other programming platforms. But exists in IBM DB2 and MS SQL Server. I don't have Oracle server handy to try out – Adarsha Sep 17 '12 at 04:19

4 Answers4

4

You will get the desired result if you declare each value as Float.

DECLARE @Float1 float, @Float2 float, @Float3 float;
SET @Float1 = 0.55;
SET @Float2 = 287.61;
SET @Float3 = 0.66;

select (@Float1 * @Float2 / @Float3) calc_no_parens
,(@Float1* (@Float2/ @Float3)) calc_parens
,round(@Float1 * @Float2/ @Float3,2) no_paren_round
,round(@Float1* (@Float2/ @Float3),2) paren_round;

Output

calc_no_parens  calc_parens no_paren_round  paren_round
239.675          239.675    239.68           239.68

You may want to see this article: So-called "exact" numerics are not at all exact!

Habib
  • 219,104
  • 29
  • 407
  • 436
  • Actually I can't do that since I am selecting few decimal columns and doing the math in the query, and comparing it to an Informatica ETL job results. But you are right about the Float, that led me to an article in MSDN about decimal precision. Please see my answer below – Adarsha Sep 17 '12 at 15:38
3

I can see what is happening, but I don't think there is a fix.

SQL calculates and stores each part of the function as a SQL data type (in this case it's a floating point number).

287.61/0.66 produces 435.7727272727272727272727272... which SQL will store as a floating point number to some degree of accuracy, however it isn't exact (after all, it's a floating point number).

For more info on floating point numbers: How is floating point stored? When does it matter?

Community
  • 1
  • 1
Greg
  • 3,442
  • 3
  • 29
  • 50
0

Habib's answer made me thinking this has to be with decimal data types my columns are using. After a bit of research, I found this Precision, Scale, and Length (Transact-SQL)

As you can see in that article, division operation significantly changes the both scale and precision of resulting decimal. Then I tried an variation of my query, this time adding extra parenthesis around Multiplication operation. 

select distinct (0.55 * 287.61 / 0.66) calc_no_parens
,(0.55 * (287.61 / 0.66)) calc_parens_div
,((0.55 * 287.61) / 0.66) calc_parens_mult
,round(0.55 * 287.61 / 0.66,2) no_paren_round
,round(0.55 * (287.61 / 0.66),2) paren_round
,round((0.55 * 287.61) / 0.66,2) paren_round2;

Results

Column              Record 1
calc_no_parens      239.6750000
calc_parens_div     239.67499985
calc_parens_mult    239.6750000
no_paren_round      239.6800000
paren_round         239.67000000
paren_round2        239.6800000

So as long as division is the last operator in the formula we get correct answers. Its not a fix to the problem, but a learning to self in any future testing projects.

Adarsha
  • 2,267
  • 22
  • 29
  • The Parenthesis around the multiplication is going to make SQL do the exact same thing as not having any parenthesis because SQL will work from left to right for multiplication and division. If I remember my high school math correctly, your calc_no_parens and calc_parens_mult are the exact same equation – Greg Sep 17 '12 at 20:23
  • I know that Greg, I think think my point was to highlight order of same priority operations do matter when it comes to decimal Math. my What your high school math must have taught you is a * b / c = a / c * b. It should be since both * and / operators have same priority, and math should not change by order of operation. But in reality, it does. If you divide first then multiply you are going to lose precision, and your answers are going to less precise. – Adarsha Sep 18 '12 at 20:50
  • Sorry, wasn't trying to offend. Completely agree with you – Greg Sep 19 '12 at 00:17
0

When you use numbers SQL try to convert them dynamically:

{

SELECT 
0.55*(287.61 / 0.66) PrecisionError, 
0.55* (CONVERT(NUMERIC(24,12), 287.61) / CONVERT(NUMERIC(24,12), 0.66)) NotPrecisionError

DECLARE @V SQL_VARIANT
SET @V = 0.55*(287.61 / 0.66)

SELECT 
Value = @V
,[TYPE] = CONVERT(SYSNAME, sql_variant_property(@V, 'BaseType')) + '(' + 
          CONVERT(VARCHAR(10), sql_variant_property(@V, 'Precision')) + ',' + 
          CONVERT(VARCHAR(10), sql_variant_property(@V, 'Scale')) + ')'  

SET @V = 0.55 * (CONVERT(NUMERIC(24,14), 287.61) / CONVERT(NUMERIC(24,14), 0.66))
SELECT 
Value = @V
,[TYPE] = CONVERT(SYSNAME, sql_variant_property(@V, 'BaseType')) + '(' + 
          CONVERT(VARCHAR(10), sql_variant_property(@V, 'Precision')) + ',' + 
          CONVERT(VARCHAR(10), sql_variant_property(@V, 'Scale')) + ')'

}

RESULTS

PrecisionError NotPrecisionError

239.67499985 239.6750000000000

Value TYPE

239.67499985 numeric(14,8)

Value TYPE

239.6750000000000 numeric(38,13)

prampe
  • 1