Bug #120034 Wrong Results with LOG(1, N) <=> NULL: UNIQUE Constraint Causes Incorrect Row Deduplication

Submitted: 11 Mar 15:13 Modified: 11 Mar 21:10
Reporter: b a Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.4.8 OS:Any
Assigned to: CPU Architecture:Any

[11 Mar 15:13] b a
Description:
Two tables containing identical data produce different query results depending on whether a `UNIQUE` constraint is defined on column `c0`.

In both cases, column `c0` contains only `NULL` values. The query compares `LOG(1, N)` (where N is any positive number) with `c0` using the NULL-safe equality operator `<=>`.

`LOG(1, N)` evaluates to `NULL` for any valid N, because logarithm base 1 is mathematically undefined (1 raised to any power always equals 1). Therefore, the predicate `LOG(1, N) <=> c0` should evaluate to `TRUE` for all rows where `c0 IS NULL`, since `<=>` returns `TRUE` when both operands are `NULL`.

When the `UNIQUE` constraint is present, the query incorrectly returns only one row.  
When the constraint is removed, the query correctly returns two rows.

The issue can be reproduced with various `LOG(1, N)` expressions, including `LOG(1, 1)` and `LOG(1, 2)`.

How to repeat:
Case 1: Table with `UNIQUE` constraint

```sql
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;

CREATE TABLE t0(
  c0 DOUBLE UNIQUE,
  c1 VARCHAR(500)
);

INSERT INTO t0(c1) VALUES("1");
INSERT INTO t0(c1) VALUES("2");

SELECT 1 AS c30
FROM t0 AS tom24
WHERE LOG(1,2) <=> tom24.c0
LIMIT 42;
```

Result:

```
+-----+
| c30 |
+-----+
|   1 |
+-----+
1 row in set, 3 warnings
```

Case 2: Table without `UNIQUE` constraint (same data)

```sql
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;

CREATE TABLE t0(
  c0 DOUBLE,
  c1 VARCHAR(500)
);

INSERT INTO t0(c1) VALUES("1");
INSERT INTO t0(c1) VALUES("2");

SELECT 1 AS c30
FROM t0 AS tom24
WHERE LOG(1,2) <=> tom24.c0
LIMIT 42;
```

Result:

```
+-----+
| c30 |
+-----+
|   1 |
|   1 |
+-----+
2 rows in set, 1 warning
```

The two tables contain identical data, so the query result should return the same number of rows.
[11 Mar 21:10] Roy Lyseng
Thank you for the bug report.
Verified as described.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.037 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.