Bug #63868 | Left outer join with user defined function on NULL column gives wrong results | ||
---|---|---|---|
Submitted: | 28 Dec 2011 19:34 | Modified: | 29 Dec 2011 4:24 |
Reporter: | Pim VAN DER WAL | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 5.1.60, 5.5.20 | OS: | Linux (Centos 5 x64) |
Assigned to: | CPU Architecture: | Any | |
Tags: | FUNCTION, LEFT OUTER JOIN, udf |
[28 Dec 2011 19:34]
Pim VAN DER WAL
[29 Dec 2011 4:24]
Valeriy Kravchuk
Thank you for the problem report. Verified with 5.5.20 also: macbook-pro:5.5 openxs$ bin/mysql -uroot test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 196 Server version: 5.5.20-debug Source distribution Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CREATE TABLE tbl_1 (a INT) ENGINE=INNODB; Query OK, 0 rows affected (0.31 sec) mysql> CREATE TABLE tbl_2 (b INT) ENGINE=INNODB; Query OK, 0 rows affected (0.20 sec) mysql> INSERT INTO tbl_1 (a) VALUES (1); Query OK, 1 row affected (0.14 sec) mysql> DELIMITER $$ mysql> DROP FUNCTION IF EXISTS fn_1 $$ Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> CREATE FUNCTION fn_1 (p_1 INT) RETURNS INT -> BEGIN -> RETURN 1; -> END $$ Query OK, 0 rows affected (0.15 sec) mysql> DELIMITER ; mysql> SELECT * -> FROM tbl_1 -> LEFT JOIN (SELECT b FROM tbl_2) AS tmp ON tbl_1.a = tmp.b -> WHERE fn_1(tmp.b) = 1; Empty set (0.06 sec) mysql> SELECT * FROM tbl_1 LEFT JOIN tbl_2 AS tmp ON tbl_1.a = tmp.b WHERE fn_1(tmp.b) = 1; Empty set (0.04 sec) mysql> SELECT * FROM tbl_1 LEFT JOIN tbl_2 AS tmp ON tbl_1.a = tmp.b WHERE fn_1(tmp.b) <=> 1; +------+------+ | a | b | +------+------+ | 1 | NULL | +------+------+ 1 row in set (0.00 sec) Here is what happens internally: mysql> explain extended SELECT * FROM tbl_1 LEFT JOIN tbl_2 AS tmp ON tbl_1.a = tmp.b WHERE fn_1(tmp.b) = 1; +----+-------------+-------+------+---------------+------+---------+------+------+----------+--------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+----------+--------------------------------+ | 1 | SIMPLE | tbl_1 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | | 1 | SIMPLE | tmp | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where; Using join buffer | +----+-------------+-------+------+---------------+------+---------+------+------+----------+--------------------------------+ 2 rows in set, 1 warning (0.10 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Note Code: 1003 Message: select `test`.`tbl_1`.`a` AS `a`,`test`.`tmp`.`b` AS `b` from `test`.`tbl_1` join `test`.`tbl_2` `tmp` where ((`test`.`tmp`.`b` = `test`.`tbl_1`.`a`) and (`fn_1`(`test`.`tbl_1`.`a`) = 1)) 1 row in set (0.00 sec) So, query is (mistakenly) rewritten as JOIN instead of LEFT JOIN. Compare to this: mysql> explain extended SELECT * FROM tbl_1 LEFT JOIN tbl_2 AS tmp ON tbl_1.a = tmp.b WHERE fn_1(tmp.b) <=> 1; +----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | tbl_1 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | | | 1 | SIMPLE | tmp | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Note Code: 1003 Message: select `test`.`tbl_1`.`a` AS `a`,`test`.`tmp`.`b` AS `b` from `test`.`tbl_1` left join `test`.`tbl_2` `tmp` on((`test`.`tmp`.`b` = `test`.`tbl_1`.`a`)) where (`fn_1`(`test`.`tmp`.`b`) <=> 1) 1 row in set (0.00 sec)