Implicit conversions will find you

-- two thirds minus one half
SELECT 2/3 - 1/2 
     , 2/3 - 1.0/2 
     , 2.0/3 - 1/2 
     , 2.0/3 - 1.0/2 
     , ISNULL(2/3, 1.0) - .5 
     , ISNULL(2.0/3, 1) - .5 
     , COALESCE(2/3, 1.0) - .5 
     , COALESCE(2/3.0, 1.0) - 1/2 
     , COALESCE(2/3, 1.0) - 1.0/2; 
0           
 -0.500000                               
 0.666666                                
 0.166666                                
 -0.5                                    
 0.166666                                
 -0.5                                    
 0.666666                                
 -0.500000

Need I say more?

It can get worse you know.

SELECT 1 + 0X1 + 1.0 
SELECT 1 + 0X1  
SELECT 1.0 + 0X1
---------------------------------------
3.0

(1 row(s) affected)

-----------
2

(1 row(s) affected)

---------------------------------------
Msg 8114, Level 16, State 5, Line 2
Error converting data type varbinary to numeric.

The decimal point induced implicit conversions in the first example all converted an INT to a REAL (whole number to imaginary number implicit conversion) yet in this last example the decimal point induces an error message about a VARBINARY and DECIMAL data type even though the result is an INT or REAL.  (eh? There is a risk to convert a hex value to real number – if you try it in the wrong order – but not to introduce floating point precision problems that actually do change the calculations?).

…and they use this stuff to make important decisions?…

The best hope for flushing out unintended implicit conversion math errors is a good code review practice.

The title is a reference to the Steve Goodman song, “Don’t Go Looking for Trouble”.

Advertisements
This entry was posted in Code Review, Testing. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s