Re: BUG #19465: Inconsistency in EXP(LN(x)) equivalence leading to different COUNT result in PostgreSQL

On Sat, 25 Apr 2026 at 00:37, PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:
> The following query produces inconsistent results across different database
> systems.
> When executed on MySQL, DuckDB, and MonetDB, the result is 0.
> However, on PostgreSQL, the same query returns 5.
> ```SQL
> SELECT COUNT(*)
> FROM users AS ref_0
> WHERE (SELECT VAR_SAMP(id) FROM posts)
> <> EXP(LN((SELECT VAR_SAMP(id) FROM posts)));

This relates to the precision of NUMERIC vs FLOAT8. Consider:

postgres=# select '2.5000000000000001'::float8 = '2.5000000000000000'::float8;
?column?
----------
t
(1 row)

postgres=# select '2.5000000000000001'::numeric = '2.5000000000000000'::numeric;
?column?
----------
f
(1 row)

You can see here that the numeric VAR_SAMP function is selected due to
the integer type of posts.id:

postgres=# \dfS var_samp
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+----------+------------------+---------------------+------
pg_catalog | var_samp | numeric | bigint | agg
pg_catalog | var_samp | double precision | double precision | agg
pg_catalog | var_samp | numeric | integer | agg
<-- returns numeric
pg_catalog | var_samp | numeric | numeric | agg
pg_catalog | var_samp | double precision | real | agg
pg_catalog | var_samp | numeric | smallint | agg
(6 rows)

Therefore, the numeric versions of LN and EXP are also selected, which
means you get the equivalent of '2.5000000000000001'::numeric <>
'2.5000000000000000'::numeric, which is true, so all rows match.

If you need the less precise version, you could cast to float8.
Something like: SELECT COUNT(*) FROM users AS ref_0 WHERE (SELECT
VAR_SAMP(id) FROM posts)::float8 <> EXP(LN((SELECT VAR_SAMP(id) FROM
posts)))::float8;

David