Wrong result with RIGHT/LEFT JOIN and ALL subquery predicate in WHERE condition

Bug #994392 reported by Igor Babaev on 2012-05-04
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MariaDB
Fix Released
High
Oleksandr "Sanja" Byelkin

Bug Description

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 bug #13735712 for mysql code line)

Changed in maria:
status: New → Confirmed
importance: Undecided → High
assignee: nobody → Oleksandr "Sanja" Byelkin (sanja-byelkin)
milestone: none → 5.2
Changed in maria:
status: Confirmed → In Progress

The subquery was never optimized (so and executed).

The bug repeatable also for LEFT join (also table should change order).

Cause of the bug is incorrect not_null_tables of Item_func_not_all (it makes optimizer thinks that the function will reject NULLs).

summary: - Wrong result with RIGHT JOIN and ALL subquery predicate in WHERE
+ Wrong result with RIGHT/LEFT JOIN and ALL subquery predicate in WHERE
condition
Changed in maria:
status: In Progress → Fix Committed
Elena Stepanova (elenst) on 2012-05-10
tags: added: optimizer wrong-result
Changed in maria:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers