Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
11.7.2, 10.6, 10.11, 11.4, 11.8, 10.5(EOL)
-
docker
-
Unexpected results
-
Added more conditions to not do table elimination when UNIQUE indexes over nullable columns may have duplicate NULL values.
-
Q3/2025 Maintenance
Description
These two equivalent queries produce contradictory results:
SELECT t1.c1 FROM t1 LEFT JOIN t0 ON (t0.c0 IS NULL);
|
SELECT * FROM t1 LEFT JOIN t0 ON (t0.c0 IS NULL);
|
|
sudo docker run -it -p 3307:3306 -e MARIADB_ROOT_PASSWORD=root mariadb:latest
|
|
CREATE TABLE t0(c0 FLOAT);
|
CREATE TABLE t1(c1 FLOAT);
|
INSERT INTO t0 VALUES (NULL), (NULL);
|
INSERT INTO t1 VALUES (1.0);
|
CREATE UNIQUE INDEX i0 USING BTREE ON t0(c0);
|
|
SELECT t1.c1 FROM t1 LEFT JOIN t0 ON (t0.c0 IS NULL);
|
+------+
|
| c1 |
|
+------+
|
| 1 |
|
+------+
|
1 row in set (0.001 sec)
|
|
SELECT * FROM t1 LEFT JOIN t0 ON (t0.c0 IS NULL);
|
+------+------+
|
| c1 | c0 |
|
+------+------+
|
| 1 | NULL |
|
| 1 | NULL |
|
+------+------+
|
2 rows in set (0.001 sec)
|