Bug #19816 Optimizer wrongly rewrites LEFT as INNER JOIN and produces wrong results
Submitted: 15 May 2006 13:35 Modified: 25 May 2006 20:27
Reporter: Beat Vontobel (Silver Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.0.21/5.0BK/5.1BK OS:Any (any)
Assigned to: Igor Babaev

[15 May 2006 13:35] Beat Vontobel
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)
[15 May 2006 13: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 14:15] Miguel Solorzano
Looks like this is a 4.1 bug, not 5.X
[15 May 2006 14:19] Miguel Solorzano
Sorry disregard my last comment.
[18 May 2006 3: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 5: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 20:27] Paul Dubois
Noted in 5.0.23, 5.1.10 changelogs.