[MDEV-2780] LP:994392 - Wrong result with RIGHT/LEFT JOIN and ALL subquery predicate in WHERE condition Created: 2012-05-04  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Igor Babaev Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug994392.xml    

 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)

 Comments   
Comment by Oleksandr Byelkin [ 2012-05-10 ]

Re: Wrong result with RIGHT JOIN and ALL subquery predicate in WHERE condition
The subquery was never optimized (so and executed).

Comment by Oleksandr Byelkin [ 2012-05-10 ]

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).

Comment by Rasmus Johansson (Inactive) [ 2012-05-11 ]

Launchpad bug id: 994392

Generated at Thu Feb 08 06:44:10 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.