SQL & PL/SQL

Issue seems to be caused by the fact that the result is a DOUBLE_PRECISION value.

The following condition works:

FLOOR(CAST(LOG(2,4) AS INT))

You can verify that the double precision result log(2,4) does not exactly match the number 2 with the following query:

select count(1) from dual where log(2,4) = 2

it returns a count of 0

Redo the SQLs so we see the full floating point:

select TO_CHAR( floor(2),'TM' ) result, dump(floor(2),1016) from dual;

select TO_CHAR( log (2,4),'TM' ) result, dump(log (2,4),1016) from dual;

select TO_CHAR( floor(log (2,4)),'TM' ) result, dump(floor(log (2,4)),1016) from dual;

You are using SQL*Plus that has its own rounding controlled by certain SQL*Plus settings. Look what happens when we increase fractional digits in NUMFORMAT:

SQL> set numformat 999.999999999999999999999999999999999999999999

SQL> select log (2,4) result from dual;

                                         RESULT

-----------------------------------------------

   1.999999999999999999999999999999999999980000

SQL>

Same happens if I set NUMWIDTH:

SQL> set numformat "" -- set numformat back to original value

SQL> set numwidth 49

SQL> select log (2,4) result from dual;

                                           RESULT

-------------------------------------------------

         1.99999999999999999999999999999999999998

SQL>

Or when I would set column format:

SQL> set numwidth 10 -- set numwidth back to original value

SQL> column result_with_many_fractional_digits format 999.999999999999999999999999999999999999999999

SQL> select  log (2,4) result,

  2          log (2,4) result_with_many_fractional_digits

  3    from  dual

  4  /

    RESULT              RESULT_WITH_MANY_FRACTIONAL_DIGITS

---------- -----------------------------------------------

         2    1.999999999999999999999999999999999999980000

SQL>

SY.

Of course,   FLOOR(CAST(LOG(2,4) AS INT))   makes little sense; once you cast something as an INT, taking the FLOOR of the result is meaningless (you will get back the same integer in all cases). This is the same as CAST(LOG(2,4) AS INT),  but writing it that way is still misleading; what is happening is simply  ROUND(LOG(2,4)) (because that is how Oracle chose to define the CAST to INT).

This is, then, no solution at all. The OP really wants the FLOOR of a number, not the ROUND. And your proposed solution is equivalent to ROUND. The simple answer is that the OP can't get what he wants, not this way anyway.

You are not doing anything wrong. Computing has limitations, given its finite nature.

I don't know how Oracle implements LOG, but the issue is that, whatever the implementation, it gives an approximate result. Oracle (and C, if Oracle simply uses the C functions in the math library) can't do formal algebra.

For example, imagine that LOG(x,y) is always computed as LN(y)/LN(x), no matter what x and y. And there is a table of values for LN for a very large number of arguments, and we use linear interpolation between those arguments. Then LOG(2,4) may end up looking like 1.9999999999999999999999938208  due to rounding.

This is one issue you must always be aware of whenever you work with computers. For example - do you realize that, without rounding, 1/3 * 3 can never be equal to 1 in a computer? 1/3 does not have an exact, finite representation, either in base 10 or in base 2, so there is no way 1/3 can be represented exactly in memory.

Why do you need the exact value of LOG(2, 4) - and why did you need to take its FLOOR? Such precision, and such operations, are often needed in abstract math, or in its offshoots (such as abstract physics) - not in engineering, finance, etc. For those applications (number theory for example) you would need to use specialized routines that can do LOG(2, 4) = 2 without using any kind of approximations.