EXP and LOG: Read Approximate Inverse Calculations

EXP and LOG are mathematical inverses, but their SQL results use approximate numbers. I distinguish a useful rounded round trip from a promise of unrestricted exact equality.

Keep the logarithm base clear

LOG without a base argument computes the natural logarithm. EXP raises the constant e to the supplied power. Their relationship lets an exponential reverse a natural logarithm. This example uses that relationship rather than changing the logarithm base.

Both functions return float. Numeric arguments that convert implicitly to float can be supplied. A decimal source therefore doesn’t make their internal calculations exact decimal arithmetic. The native representation remains approximate even when an output looks like an integer.

I’d name the logarithm convention before comparing calculations from different systems. A base-ten logarithm isn’t the same transformation. Its inverse isn’t the same EXP expression. Matching function names without matching their mathematical definitions can produce a persuasive but incorrect check.

Read both round trips beside the input

The script supplies positive values one, two, ten and twenty. A fifth input is NULL. It displays the natural logarithm with six fractional digits. Two additional columns apply EXP after LOG and LOG after EXP.

All displayed outputs are explicitly cast to decimal(12,6). The original input uses decimal(6,2). Those choices make this small result contract readable. They do not change either function’s native float return type.

The logarithm of one displays zero. The other positive logarithms display fractional values. Both round-trip columns are expected to recover the selected inputs at this display scale. That expectation is narrower than comparing every underlying float bit for every possible number.

WITH Inputs AS
(
    SELECT Id, CAST(InputValue AS decimal(6,2)) AS InputValue
    FROM (VALUES (1, 1), (2, 2), (3, 10), (4, 20), (5, NULL))
        AS v(Id, InputValue)
)
SELECT Id, InputValue, CAST(LOG(InputValue) AS decimal(12,6)) AS NaturalLog,
       CAST(EXP(LOG(InputValue)) AS decimal(12,6)) AS ExpAfterLog,
       CAST(LOG(EXP(InputValue)) AS decimal(12,6)) AS LogAfterExp
FROM Inputs
ORDER BY Id;

Restrict inputs before generalizing

A real natural logarithm needs a positive input. The script doesn’t execute zero or negative logarithms. NULL is retained as a missing input. It produces missing outputs rather than a substituted positive value.

EXP also has finite representation limits. A large exponent can exceed the supported result range even when its input is a valid number. This demonstration uses small values with safe intermediate magnitudes. It establishes no unlimited exponential range.

I can justify using a logarithmic transformation for a measurement calculation. That decision requires the original unit and valid domain too. A round trip doesn’t establish that a transformation suits the real data. It checks one bounded numerical relationship.

Set the comparison precision deliberately

Approximate arithmetic can introduce small differences that a chosen display scale removes. That isn’t automatically an error. The consumer must specify the permitted difference or output scale. A generic equality predicate can demand a stronger contract than the calculation supplies.

The complete script is a CTE over literal inputs followed by a SELECT. It creates no objects or connection settings. ORDER BY fixes the five-row sequence. Every original value remains beside the logarithm and both round-trip results.

Compare each displayed decimal and SQL type across the full input set. Keep the NULL row and both inverse directions. One apparently recovered value doesn’t prove a broader approximation policy. Retain the original value when judging the selected displayed result.

Small differences are normal with float, so say how close is close enough.

A rounded round trip is not exact equality, it is a check against a chosen scale.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.