Bug #120034 Wrong Results with LOG(1, N) <=> NULL: UNIQUE Constraint Causes Incorrect Row Deduplication
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.
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.
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.