Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.7.2, 10.5, 10.6, 10.11, 11.4, 11.8
-
None
-
None
-
docker
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)
|
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| 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) |
These two equivalent queries produce contradictory results:
{noformat} 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) {noformat} |
| Affects Version/s | 10.5 [ 23123 ] | |
| Affects Version/s | 10.6 [ 24028 ] | |
| Affects Version/s | 10.11 [ 27614 ] | |
| Affects Version/s | 11.4 [ 29301 ] | |
| Affects Version/s | 11.8 [ 29921 ] |
| Priority | Critical [ 2 ] | Major [ 3 ] |
| Fix Version/s | 10.11 [ 27614 ] | |
| Fix Version/s | 11.4 [ 29301 ] | |
| Fix Version/s | 11.8 [ 29921 ] |
| Assignee | Sergei Petrunia [ psergey ] |
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
| Description |
These two equivalent queries produce contradictory results:
{noformat} 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) {noformat} |
These two equivalent queries produce contradictory results:
{noformat} 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) {noformat} |
Thanks! I repeated as described on 10.5-11.8
MariaDB [test]> explain extended SELECT * FROM t1 LEFT JOIN t0 ON (t0.c0 IS NULL);+------+-------------+-------+------+---------------+------+---------+-------+------+----------+--------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+------+-------------+-------+------+---------------+------+---------+-------+------+----------+--------------------------+| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | || 1 | SIMPLE | t0 | ref | i0 | i0 | 5 | const | 2 | 100.00 | Using where; Using index |+------+-------------+-------+------+---------------+------+---------+-------+------+----------+--------------------------+2 rows in set, 1 warning (0,003 sec)Note (Code 1003): select `test`.`t1`.`c1` AS `c1`,`test`.`t0`.`c0` AS `c0` from `test`.`t1` left join `test`.`t0` on(`test`.`t0`.`c0` is null) where 1MariaDB [test]> explain extended SELECT t1.c1 FROM t1 LEFT JOIN t0 ON (t0.c0 IS NULL);+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | |+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+1 row in set, 1 warning (0,002 sec)Note (Code 1003): select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where 1MariaDB [test]> alter table t0 drop index i0;Query OK, 0 rows affected (0,036 sec)Records: 0 Duplicates: 0 Warnings: 0MariaDB [test]> SELECT t1.c1 FROM t1 LEFT JOIN t0 ON (t0.c0 IS NULL);+------+| c1 |+------+| 1 || 1 |+------+2 rows in set (0,005 sec)