Bug #120340 Inconsistent evaluation of NULLIF(ROUND(VAR_SAMP(...)), const) <= value between MySQL and PostgreSQL/DuckDB causes diffe

• Submitted: 24 Apr 9:13
• Reporter: Jasper Andrew
• Status: Open
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 8.0.43 (MySQL Community Server - GPL)
• OS: Any
• CPU Architecture: Any
• Tags: Logic bug, NULLIF, round, VAR_SAMP

[24 Apr 9:13] Jasper Andrew

Description:
In MySQL, the following query returns 5 rows, while the same query returns 0 rows in PostgreSQL and DuckDB:
```SQL
SELECT COUNT(*)
FROM users AS ref_0
WHERE NULLIF(
        ROUND((SELECT VAR_SAMP(id) FROM posts)),
        86.82
      ) <= 2;
```
Expected behavior

The query should behave consistently across database engines. Given the semantics of:

- VAR_SAMP(id) (sample variance aggregate over posts)
- ROUND(...)
- NULLIF(..., 86.82)
- comparison with <= 2

The expression inside the WHERE clause should evaluate to either:

- NULL → predicate becomes unknown/false, or
- a numeric value → consistently comparable across engines

In both PostgreSQL and DuckDB, the condition evaluates in a way that results in no rows matching, which is the expected and standard SQL behavior.

Actual behavior (MySQL)

MySQL returns 5 rows, indicating that the predicate:
```SQL
NULLIF(ROUND((SELECT VAR_SAMP(id) FROM posts)), 86.82) <= 2
```
is evaluating differently, leading to rows being incorrectly included.

Observations
The discrepancy appears to stem from how MySQL evaluates:
scalar subquery aggregation (SELECT VAR_SAMP(id) FROM posts)
ROUND() applied to aggregate results
NULLIF() interaction with comparison operators
PostgreSQL and DuckDB both consistently return 0 rows, suggesting stricter adherence to SQL semantics for NULL propagation or comparison with NULL.

How to repeat:
```SQL
-- SCHEMA

CREATE TABLE users (
    id           INT,
    username     VARCHAR(100),
    email        VARCHAR(255),
    age          INT,
    status       VARCHAR(20),
    created_at   TIMESTAMP NULL,
    score        DOUBLE
);

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

INSERT INTO users VALUES
(1, 'alice', '[e-mail]', 20, 'active',  '2022-01-01 10:00:00', 88.5),
(2, 'bob',   '[e-mail]',   30, 'active',  '2022-01-02 11:00:00', 92.3),
(3, 'carol', NULL,             NULL, 'banned','2022-01-03 12:00:00', NULL),
(4, 'dave',  '[e-mail]',  45, 'active',  '2022-01-04 13:00:00', 65.2),
(5, NULL,    '[e-mail]',  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 NULLIF(
        ROUND((SELECT VAR_SAMP(id) FROM posts)),
        86.82
      ) <= 2;

-- MySQL returns 5 rows, While the expected result is 0.

```