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?
