Geek asks and answers

Extra parenthesis changing formula result in SQL Server 2008

In all other languages ​​(in general, in arithmetic machines), adding an additional set of parentheses around operators with the same priority does not affect the results. But recently in a test project, I noticed that the MS SQL server changes the results in these cases. Please review the query below and let me know if you have any ideas (or settings in SQL Server Administration) or any links to an 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

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

+5
source share
4 answers

, 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;


calc_no_parens  calc_parens no_paren_round  paren_round
239.675          239.675    239.68           239.68

: "" !

+4

, , , .

SQL SQL ( ).

287.61/0.66 435.7727272727272727272727272... SQL , ( , ).

: ? ?

+2

Habib , , . , (Transact-SQL)

, , . , .

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;


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

, , . , .

0

SQL, :

{

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')) + ')'

}

PrecisionError NotPrecisionError

239.67499985 239.6750000000000

TYPE value

239.67499985 numeric (14.8)

TYPE value

239.6750000000000 numeric (38.13)

0
source

More articles:


All Articles