pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19467: Inconsistency in MOD() result involving POWER() and floating-point precision in PostgreSQL
3+ messages / 3 participants
[nested] [flat]
* BUG #19467: Inconsistency in MOD() result involving POWER() and floating-point precision in PostgreSQL
@ 2026-04-27 06:44 PG Bug reporting form <noreply@postgresql.org>
2026-04-27 12:20 ` Re: BUG #19467: Inconsistency in MOD() result involving POWER() and floating-point precision in PostgreSQL John Naylor <johncnaylorls@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: PG Bug reporting form @ 2026-04-27 06:44 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: fmusqlgen@163.com
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?
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #19467: Inconsistency in MOD() result involving POWER() and floating-point precision in PostgreSQL
2026-04-27 06:44 BUG #19467: Inconsistency in MOD() result involving POWER() and floating-point precision in PostgreSQL PG Bug reporting form <noreply@postgresql.org>
@ 2026-04-27 12:20 ` John Naylor <johncnaylorls@gmail.com>
2026-04-27 14:08 ` Re: BUG #19467: Inconsistency in MOD() result involving POWER() and floating-point precision in PostgreSQL Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: John Naylor @ 2026-04-27 12:20 UTC (permalink / raw)
To: fmusqlgen@163.com; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #19467: Inconsistency in MOD() result involving POWER() and floating-point precision in PostgreSQL
2026-04-27 06:44 BUG #19467: Inconsistency in MOD() result involving POWER() and floating-point precision in PostgreSQL PG Bug reporting form <noreply@postgresql.org>
2026-04-27 12:20 ` Re: BUG #19467: Inconsistency in MOD() result involving POWER() and floating-point precision in PostgreSQL John Naylor <johncnaylorls@gmail.com>
@ 2026-04-27 14:08 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2026-04-27 14:08 UTC (permalink / raw)
To: John Naylor <johncnaylorls@gmail.com>; +Cc: fmusqlgen@163.com; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-04-27 14:08 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-04-27 06:44 BUG #19467: Inconsistency in MOD() result involving POWER() and floating-point precision in PostgreSQL PG Bug reporting form <noreply@postgresql.org>
2026-04-27 12:20 ` John Naylor <johncnaylorls@gmail.com>
2026-04-27 14:08 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox