public inbox for [email protected]
help / color / mirror / Atom feedFrom: David Rowley <[email protected]>
To: [email protected]
To: [email protected]
Subject: Re: BUG #19465: Inconsistency in EXP(LN(x)) equivalence leading to different COUNT result in PostgreSQL
Date: Sat, 25 Apr 2026 01:11:57 +1200
Message-ID: <CAApHDvocULBC4thD3uBHx_G5DcsT4Zhq5bPc95n7EQ1yMbARCA@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
On Sat, 25 Apr 2026 at 00:37, PG Bug reporting form
<[email protected]> 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
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: BUG #19465: Inconsistency in EXP(LN(x)) equivalence leading to different COUNT result in PostgreSQL
In-Reply-To: <CAApHDvocULBC4thD3uBHx_G5DcsT4Zhq5bPc95n7EQ1yMbARCA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox