Microsoft SQL Server Violates the Commutative Law of Basic Math

I love SQL Server.  I’ve been a SQL Server expert for decades, but just a few days ago I found a bug in SQL Server when performing simple math calculations.

These two queries yield different results:

• select 1.0 * 3/2
• select 3/2 * 1.0

The first query returns 1.5, and the second query returns 1.0.  Different results violates the Commutative rule of math that tells us the order of operands should not matter.

I understand why this is happening.  SQL Server looks at the data type of the first argument and rounds the results of calculations to that data type.  So in the first query, 1.0 is a float (aka double); but in the second query 3 is an int (aka integer) with no decimal places so 3/2 rounded to fit in an integer is 1, not 1.5.  But just because we can explain it does not mean that it is acceptable.

This problem affects real code.  The problem occurs not just with values, but when doing similar calculations with columns in tables.

• create table CommTest (int1 int, int2 int, dbl1 float)
  insert into CommTest values(3,2,1.0)
  select dbl1 * int1/int2 from CommTest
  select int1/int2 * dbl1 from CommTest

The first select in this query will return 1.5, and the second select will return 1.

The fix is to cast the first variable as a float “select cast(int1 as float)/int2 * dbl1…”, but that is challenging to remember and difficult to apply if you have code generating SQL for you.  The better solution is probably to do all math in the program calling the database; at least if SQL Server is your DBMS.  Though this is not easy when you just have a reporting tool like SSRS for your front end.

Until Microsoft fixes this, which I doubt they ever will, developers just need to be extra careful.

This problem does not exist in other database management systems.  At least I know it does not exist in Microsoft Access nor Oracle.  For reference, I tested this on SQL Server 2014.