Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Subject: BUG #19465: Inconsistency in EXP(LN(x)) equivalence leading to
 different COUNT result in PostgreSQL
To: pgsql-bugs@lists.postgresql.org
From: PG Bug reporting form <noreply@postgresql.org>
Cc: fmusqlgen@163.com
Reply-To: fmusqlgen@163.com, pgsql-bugs@lists.postgresql.org
Date: Fri, 24 Apr 2026 11:56:38 +0000
Message-ID: <19465-0e3f9ab09f98be28@postgresql.org>
Auto-Submitted: auto-generated
Archived-At: 
 <https://www.postgresql.org/message-id/19465-0e3f9ab09f98be28%40postgresql.org>
Precedence: bulk

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:       =20

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(*) =3D 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!
```