[MDEV-264] lp:994392: Wrong result with RIGHT JOIN and ALL subquery predicate in WHERE condition - JIRA

Details

Type: Bug
Status: Closed
Priority: Critical
Resolution: Fixed
Affects Version/s: 5.2.12
Fix Version/s: 5.2.13
Component/s: None
Labels:
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)))) |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

lp:994392: Wrong result with RIGHT JOIN and ALL subquery predicate in WHERE condition

Details

Description

Gliffy Diagrams

Attachments

Activity

People

Dates

Agile