MySQL Bugs: #19816: Optimizer wrongly rewrites LEFT as INNER JOIN and produces wrong results

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	CPU Architecture:	Any

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)

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>

Looks like this is a 4.1 bug, not 5.X

Sorry disregard my last comment.

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

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

Noted in 5.0.23, 5.1.10 changelogs.