How can FLOOR(3) equal 2?

Question

I'm trying to find a reliable, efficient expression to calculate how many decimal digits it takes to write a positive integer.

Mathematically, the number of decimal digits in an integer n is 1 + floor(log(n)), where log is the common logarithm (base 10).

There are several ways to construct an equivalent expression using built-in functions, but some of them give incorrect results. Can someone explain why?

Here is an example.

How to calculate the log?

The simplest way to calculate the common logarithm is to use the LOG10 function.

If you prefer one function for all logarithms you can use the LOG function and specify base 10 with the second parameter.

Prior to 2012, SQL Server's LOG function would calculate only the natural log (base e=2.71828...). You can calculate the log to an arbitrary base of a number by dividing the natural logarithm of the number by the natural logarithm of the base.

The following query calculates all three expressions for some example values:

SELECT
  Number,
  LOG(Number, 10) AS LogAB,
  LOG10(Number) AS LogTen,
  LOG(Number) / LOG(10) AS LogOverLog
FROM (
  VALUES (999), (1000), (1001)
) AS Tally (Number);

Output:

Number      LogAB                  LogTen                 LogOverLog
----------- ---------------------- ---------------------- ----------------------
999         2.99956548822598       2.99956548822598       2.99956548822598
1000        3                      3                      3
1001        3.00043407747932       3.00043407747932       3.00043407747932

I have chosen the values 999, 1000, and 1001 because 1000 is a point where the number of digits steps up. 999 has 3 digits, 1000 has 4.

The value of all three expressions is visibly the same, and looks correct.

Let's move on to the floor step.

How to calculate the floor?

You can take the floor of each log in the previous example using a query like this:

SELECT
  Number,
  FLOOR(LOG(Number, 10)) AS FloorLogAB,
  FLOOR(LOG10(Number)) AS FloorLogTen,
  FLOOR(LOG(Number) / LOG(10)) AS FloorLogOverLog
FROM (
  VALUES (999), (1000), (1001)
) AS Tally (Number);

Output:

Number      FloorLogAB             FloorLogTen            FloorLogOverLog
----------- ---------------------- ---------------------- ----------------------
999         2                      2                      2
1000        2                      3                      2
1001        3                      3                      3

The values of each expression for 999 and 1001 are equal and correct. If we added 1 to each value, we would have a count of 3 digits in 999 and a count of 4 digits in 1001.

The values for 1000 are not the same! If we added 1 to each value, we would have a count of 4 digits in 1000 if we used the LOG10 function, and a count of 3 digits if we used the LOG function in either form.

There is an inconsistency here!

How can FLOOR(3) equal 2?

The implication is clear: using the LOG function would give me an incorrect count for some values, so I should use the LOG10 function.

But the value of each log expression itself is identical and correct. Why does the floor function produce different values from their input?

Answer 1

SELECT
  Number,
  CAST(LOG(Number, 10) AS VARBINARY) AS LogAB,
  CAST(LOG10(Number) AS VARBINARY) AS LogTen,
  CAST(LOG(Number) / LOG(10) AS VARBINARY) AS LogOverLog
FROM (
  VALUES (1000)
) AS Tally (Number);

Returns

Number      LogAB                   LogTen                  LogOverLog
----------- ----------------------- ----------------------- ----------------------
1000        0x4007FFFFFFFFFFFF      0x4008000000000000      0x4007FFFFFFFFFFFF

0x4008000000000000 is exactly 3.

0x4007FFFFFFFFFFFF is 2.99999999999999955591079014994.

If you are looking for an efficient expression maybe a CASE expression with the 10 different cases would actually work out less CPU intensive than calculating logarithms (or possibly you could have nested case expressions to do a trinary search)

Answer 2

I would call it a rounding problem ...

declare @a table(Number int)
insert into @a Values (999),(1000),(1001);

SELECT
  Number ,
  LOG10(Number) AS LogTen,
  LOG(Number) / LOG(10) AS LogOverLog
  ,LOG10(Number) - (LOG(Number) / LOG(10)) as Diff
FROM @a

Output

999 2,99956548822598    2,99956548822598    0
1000    3   3   4,44089209850063E-16
1001    3,00043407747932    3,00043407747932    4,44089209850063E-16

Displaying the limit

SELECT
  Number,

  FLOOR(LOG10(Number)) AS FloorLogTen,
  FLOOR(LOG(Number) / LOG(10) ) AS FloorLogOverLog,
  FLOOR(LOG(Number) / LOG(10) + 2.22044E-16) AS LessCorrection,
  FLOOR(LOG(Number) / LOG(10) + 2.22045E-16) AS Overcorrection
FROM @a