public inbox for [email protected]  

help / color / mirror / Atom feed

From: PG Bug reporting form <[email protected]>
To: [email protected]
Cc: [email protected]
Subject: BUG #19465: Inconsistency in EXP(LN(x)) equivalence leading to different COUNT result in PostgreSQL
Date: Fri, 24 Apr 2026 11:56:38 +0000
Message-ID: <[email protected]> (raw)

The following bug has been logged on the website:

Bug reference:      19465
Logged by:          Jasper Andrew
Email address:      [email protected]
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', '[email protected]', 20, 'active',  '2022-01-01 10:00:00', 88.5),
(2, 'bob',   '[email protected]',   30, 'active',  '2022-01-02 11:00:00', 92.3),
(3, 'carol', NULL,             NULL, 'banned','2022-01-03 12:00:00', NULL),
(4, 'dave',  '[email protected]',  45, 'active',  '2022-01-04 13:00:00', 65.2),
(5, NULL,    '[email protected]',  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!
```

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: <[email protected]>

* 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