Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
5.2.12
-
None
-
None
Description
fix https://bugs.launchpad.net/maria/+bug/994392
The following sequence of commands gives us a wrong result set in mariadb-5.2:
CREATE TABLE t1(a INT); |
INSERT INTO t1 VALUES(9); |
CREATE TABLE t2(b INT); |
INSERT INTO t2 VALUES(8); |
CREATE TABLE t3(c INT); |
INSERT INTO t3 VALUES(3); |
SELECT * FROM t2 RIGHT JOIN t3 ON(c = b) WHERE b < ALL(SELECT a FROM t1 WHERE a <= 7); |
MariaDB [test]> SELECT * FROM t2 RIGHT JOIN t3 ON(c = b) WHERE b < ALL(SELECT a FROM t1 WHERE a <= 7);
|
Empty set (0.00 sec)
|
|
|
MariaDB [test]> EXPLAIN EXTENDED SELECT * FROM t2 RIGHT JOIN t3 ON(c = b) WHERE b < ALL(SELECT a FROM t1 WHERE a <= 7);
|
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
|
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
|
| 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
|
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
|
2 rows in set, 1 warning (0.00 sec)
|
MariaDB [test]> show warnings;
|
+-------+------+------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+------------------------------------------------------------------------+
|
| Note | 1003 | select '8' AS `b`,'3' AS `c` from `test`.`t3` join `test`.`t2` where 0 |
|
+-------+------+------------------------------------------------------------------------+
|
The bug is not reproducible in mariadb-5.3:
MariaDB [test]> select version();
|
+---------------------+
|
| version() |
|
+---------------------+
|
| 5.3.6-MariaDB-debug |
|
+---------------------+
|
|
|
MariaDB [test]> SELECT * FROM t2 RIGHT JOIN t3 ON(c = b) WHERE b < ALL(SELECT a FROM t1 WHERE a <= 7);
|
+------+------+
|
| b | c |
|
+------+------+
|
| NULL | 3 |
|
+------+------+
|
|
|
MariaDB [test]> EXPLAIN EXTENDED SELECT * FROM t2 RIGHT JOIN t3 ON(c = b) WHERE b < ALL(SELECT a FROM t1 WHERE a <= 7);
|
+----+-------------+-------+--------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+----+-------------+-------+--------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
|
| 1 | PRIMARY | t3 | system | NULL | NULL | NULL | NULL | 1 | 100.00 | |
|
| 1 | PRIMARY | t2 | system | NULL | NULL | NULL | NULL | 1 | 100.00 | |
|
| 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
|
+----+-------------+-------+--------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
|
|
|
MariaDB [test]> show warnings;
|
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Note | 1003 | select NULL AS `b`,3 AS `c` from `test`.`t3` left join `test`.`t2` on(0) where <not>(<in_optimizer>(NULL,(<min>(select 9 from `test`.`t1` where (9 <= 7)) <= <cache>(NULL)))) |
|
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
(See also lp:13735712 for mysql code line)