| Bug #19816 | Optimizer wrongly rewrites LEFT as INNER JOIN and produces wrong results | ||
|---|---|---|---|
| Submitted: | 15 May 2006 15:35 | Modified: | 25 May 2006 22:27 |
| Reporter: | Beat Vontobel (Silver Quality Contributor) | ||
| Status: | Closed | ||
| Category: | Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 5.0.21/5.0BK/5.1BK | OS: | Any (any) |
| Assigned to: | Igor Babaev | Target Version: | |
[15 May 2006 15:54]
Miguel Solorzano
Thank you for the bug report. Verified as described and this bug not affects 4.1: miguel@hegel:~/dbs/4.1$ bin/mysql -uroot test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.1.20-debug Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> CREATE TABLE a (i INT); Query OK, 0 rows affected (1.32 sec) mysql> CREATE TABLE b (j INT); Query OK, 0 rows affected (0.03 sec) mysql> INSERT INTO a VALUES (1), (2), (3); Query OK, 3 rows affected (0.08 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> INSERT INTO b VALUES (2); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM a LEFT JOIN b ON a.i = b.j WHERE (1); +------+------+ | i | j | +------+------+ | 1 | NULL | | 2 | 2 | | 3 | NULL | +------+------+ 3 rows in set (0.05 sec) mysql> SELECT * FROM a LEFT JOIN b ON a.i = b.j WHERE (1 OR 1); +------+------+ | i | j | +------+------+ | 1 | NULL | | 2 | 2 | | 3 | NULL | +------+------+ 3 rows in set (0.00 sec) mysql> miguel@hegel:~/dbs/5.0$ 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 1 to server version: 5.0.22-debug Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> CREATE TABLE a (i INT); Query OK, 0 rows affected (0.02 sec) mysql> CREATE TABLE b (j INT); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO a VALUES (1), (2), (3); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> INSERT INTO b VALUES (2); Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM a LEFT JOIN b ON a.i = b.j WHERE (1); +------+------+ | i | j | +------+------+ | 1 | NULL | | 2 | 2 | | 3 | NULL | +------+------+ 3 rows in set (0.01 sec) mysql> SELECT * FROM a LEFT JOIN b ON a.i = b.j WHERE (1 OR 1); +------+------+ | i | j | +------+------+ | 2 | 2 | +------+------+ 1 row in set (0.00 sec) mysql> miguel@hegel:~/dbs/5.1$ 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 1 to server version: 5.1.11-beta-debug Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> CREATE TABLE a (i INT); Query OK, 0 rows affected (0.03 sec) mysql> CREATE TABLE b (j INT); Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO a VALUES (1), (2), (3); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> INSERT INTO b VALUES (2); Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM a LEFT JOIN b ON a.i = b.j WHERE (1); +------+------+ | i | j | +------+------+ | 1 | NULL | | 2 | 2 | | 3 | NULL | +------+------+ 3 rows in set (0.02 sec) mysql> SELECT * FROM a LEFT JOIN b ON a.i = b.j WHERE (1 OR 1); +------+------+ | i | j | +------+------+ | 2 | 2 | +------+------+ 1 row in set (0.01 sec) mysql>
[15 May 2006 16:15]
Miguel Solorzano
Looks like this is a 4.1 bug, not 5.X
[15 May 2006 16:19]
Miguel Solorzano
Sorry disregard my last comment.
[18 May 2006 5:49]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/6546
[21 May 2006 7:57]
Igor Babaev
ChangeSet 1.2146 06/05/17 20:48:48 igor@rurik.mysql.com +3 -0 Fixed bug #19816. This bug was introduced when the patch resolving the performance problem 17164 was applied. As a result of that modification the not_null_tables attributes were calculated incorrectly for constant OR conditions. This triggered invalid conversion of outer joins into inner joins. The fix will appear in 5.0.22 and 5.1.10
[25 May 2006 22:27]
Paul DuBois
Noted in 5.0.23, 5.1.10 changelogs.
Description: On some of the most simple logical expressions the query transformation part of the optimizer seems to rewrite JOINs in a way that they produce wrong results (LEFT JOIN rewritten as JOIN). How to repeat: CREATE TABLE a (i INT); CREATE TABLE b (j INT); INSERT INTO a VALUES (1), (2), (3); INSERT INTO b VALUES (2); Now let's do a simple left join: SELECT * FROM a LEFT JOIN b ON a.i = b.j WHERE (1); +------+------+ | i | j | +------+------+ | 1 | NULL | | 2 | 2 | | 3 | NULL | +------+------+ 3 rows in set (0.00 sec) Everything okay so far. Now let's just keep the same query but replace the (1) by the very simple but equivalent expression (1 OR 1) - and look what you get: SELECT * FROM a LEFT JOIN b ON a.i = b.j WHERE (1 OR 1); +------+------+ | i | j | +------+------+ | 2 | 2 | +------+------+ 1 row in set (0.00 sec) An EXPLAIN EXTENDED shows what actually happened. The query was rewritten to use JOIN instead of LEFT JOIN: mysql1.intern-test [admin] > EXPLAIN EXTENDED SELECT * FROM a LEFT JOIN b ON a.i = b.j WHERE (1 OR 1); +----+-------------+-------+--------+---------------+------+---------+------+------+------ -------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+------+---------+------+------+------ -------+ | 1 | SIMPLE | b | system | NULL | NULL | NULL | NULL | 1 | | | 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 3 | Using where | +----+-------------+-------+--------+---------------+------+---------+------+------+------ -------+ 2 rows in set, 1 warning (0.00 sec) mysql1.intern-test [admin] > SHOW WARNINGS; +-------+------+-------------------------------------------------------------------------- --------------------------------------------------+ | Level | Code | Message | +-------+------+-------------------------------------------------------------------------- --------------------------------------------------+ | Note | 1003 | select `test`.`a`.`i` AS `i`,`test`.`b`.`j` AS `j` from `test`.`a` join `test`.`b` where (`test`.`a`.`i` = `test`.`b`.`j`) | +-------+------+-------------------------------------------------------------------------- --------------------------------------------------+ 1 row in set (0.00 sec)