Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
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)
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: Wrong result with RIGHT JOIN and ALL subquery predicate in WHERE condition
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).
Launchpad bug id: 994392
Re: Wrong result with RIGHT JOIN and ALL subquery predicate in WHERE condition
The subquery was never optimized (so and executed).