Обсуждение: BUG #19467: Inconsistency in MOD() result involving POWER() and floating-point precision in PostgreSQL

The following bug has been logged on the website:

Bug reference:      19467
Logged by:          Jasper Andrew
Email address:      fmusqlgen@163.com
PostgreSQL version: 18.1
Operating system:   Ubuntu 24.04 LTS x86_64
Description:

The following query produces inconsistent results across different database
systems:

```SQL
select mod(coalesce(pow(3.00,70.31),93.23),ceiling(sign(58.81)))
from comments as ref_0;
```

# Observed Behavior
- On MySQL, DuckDB, and MonetDB, the result is consistently:

```text
 mod
------
 0.0
 0.0
 0.0
 0.0
(4 rows)
```

- On PostgreSQL, the same query returns:

```text
 mod
------
 0.41
 0.41
 0.41
 0.41
(4 rows)
```
# Expected Behavior
Given that:
- sign(58.81) evaluates to 1
- ceiling(1) evaluates to 1

the expression simplifies to:

- mod(pow(3.00, 70.31), 1)

Mathematically, this corresponds to the fractional part of 3^70.31,  which
should be deterministic for a given evaluation strategy.

However, different systems produce significantly different results:

some return 0
only PostgreSQL returns 0.41

# Question

Is this discrepancy expected due to differences in floating-point evaluation
and implementation of functions such as:

- POWER() / pow()
- MOD()
- implicit type handling (e.g., double precision vs numeric)

Or could this indicate a potential inconsistency in how PostgreSQL evaluates
floating-point expressions compared to other systems?

On Mon, Apr 27, 2026 at 6:33 PM PG Bug reporting form
<noreply@postgresql.org> wrote:
> Mathematically, this corresponds to the fractional part of 3^70.31,  which
> should be deterministic for a given evaluation strategy.
>
> However, different systems produce significantly different results:

These two statements don't contradict eachother.

Trying the expression on WolframAlpha shows 0.41 is close to the
expected value, so I don't see a bug here.

--
John Naylor
Amazon Web Services

John Naylor <johncnaylorls@gmail.com> writes:
> On Mon, Apr 27, 2026 at 6:33 PM PG Bug reporting form
> <noreply@postgresql.org> wrote:
>> Mathematically, this corresponds to the fractional part of 3^70.31,  which
>> should be deterministic for a given evaluation strategy.
>> 
>> However, different systems produce significantly different results:

> These two statements don't contradict eachother.

> Trying the expression on WolframAlpha shows 0.41 is close to the
> expected value, so I don't see a bug here.

Those other systems are probably using float8 arithmetic, which has
nowhere near enough precision to give a nonzero answer.

In Postgres, constants like "3.00" are type numeric not type float8,
so:

regression=# select pow(3.00, 70.31);
                  pow                  
---------------------------------------
 3518806773889710662003177340498520.41
(1 row)

regression=# select mod(pow(3.00, 70.31), 1);
 mod  
------
 0.41
(1 row)

You can duplicate the lower-precision answer if you want:

regression=# select pow(3.00::float8, 70.31::float8);
          pow           
------------------------
 3.5188067738897196e+33
(1 row)

regression=# select pow(3.00::float8, 70.31::float8)::numeric;
                pow                 
------------------------------------
 3518806773889720000000000000000000
(1 row)

regression=# select mod(pow(3.00::float8, 70.31::float8)::numeric, 1);
 mod 
-----
   0
(1 row)


            regards, tom lane