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!
```
