Обсуждение: BUG #19465: Inconsistency in EXP(LN(x)) equivalence leading to different COUNT result in PostgreSQL

The following bug has been logged on the website:

Bug reference:      19465
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.
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)));
```
Notes
The expression EXP(LN(x)) is mathematically equivalent to x, so the
comparison should evaluate to false when both subqueries return the same
value.
Other database systems seem to treat this equivalence as exact (or
sufficiently precise), resulting in COUNT(*) = 0.
PostgreSQL appears to produce a different result, possibly due to
differences in:
- floating-point precision
- numeric type handling
- implementation of VAR_SAMP, LN, or EXP

Is this behavior expected due to floating-point precision differences, or
does it indicate a potential bug in PostgreSQL?

Steps to reproduce
```SQL
-- SCHEMA
CREATE TABLE users (
    id           INT,
    username     VARCHAR(100),
    email        VARCHAR(255),
    age          INT,
    status       VARCHAR(20),
    created_at   TIMESTAMP,
    score        DOUBLE PRECISION
);

CREATE TABLE posts (
    id          INT,
    user_id     INT,
    title       VARCHAR(255),
    content     VARCHAR(1000),
    views       INT,
    likes       INT,
    created_at  TIMESTAMP,
    rating      DOUBLE PRECISION
);

INSERT INTO users VALUES
(1, 'alice', 'alice@test.com', 20, 'active',  '2022-01-01 10:00:00', 88.5),
(2, 'bob',   'bob@test.com',   30, 'active',  '2022-01-02 11:00:00', 92.3),
(3, 'carol', NULL,             NULL, 'banned','2022-01-03 12:00:00', NULL),
(4, 'dave',  'dave@test.com',  45, 'active',  '2022-01-04 13:00:00', 65.2),
(5, NULL,    'null@test.com',  18, 'inactive','2022-01-05 14:00:00', 70.0);

INSERT INTO posts VALUES
(1, 1, 'Hello World', 'First post', 100, 10, '2022-01-10 10:00:00', 4.5),
(2, 1, 'Another Post', NULL,        150, 20, '2022-01-11 11:00:00', 3.0),
(3, 2, 'Bob Post',     'Content',   NULL,  5, '2022-01-12 12:00:00', NULL),
(4, 3, NULL,           'Empty',     50,   2, '2022-01-13 13:00:00', 5.0),
(5, 4, 'Last Post',    'Last',      300,  30,'2022-01-14 14:00:00', 4.9);

-- TRIGGER SQL

SELECT COUNT(*)
FROM users AS ref_0
WHERE (SELECT VAR_SAMP(id) FROM posts)
      <> EXP(LN((SELECT VAR_SAMP(id) FROM posts)));

-- RESULT: {5} Wrong!
```

On Sat, 25 Apr 2026 at 00:37, PG Bug reporting form
<noreply@postgresql.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