Bug #11713 OUTER JOIN incorrectly converted to INNER for complex WHERE clause

• Submitted: 3 Jul 2005 20:36
• Modified: 21 Jul 2005 14:51
• Reporter: Sergey Petrunya
• Status: Not a Bug
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 4.1-bk
• Assigned to: Igor Babaev
• CPU Architecture: Any

[3 Jul 2005 20:36] Sergey Petrunya

Description:
Outer join is incorrectly converted to inner join for complex queries.  

How to repeat:
create table t1 (a int, b int);
insert into t1 values (1,1),(2,2);
create table t2 like t1;
insert into t2 values (1,1);

mysql> select * from t1 left join t2 on true where (t1.a= t2.a) <=> (t1.b = t2.b);
+------+------+------+------+
| a    | b    | a    | b    |
+------+------+------+------+
|    1 |    1 |    1 |    1 |
|    2 |    2 |    1 |    1 |
+------+------+------+------+
2 rows in set (0.00 sec)
The result doesn't contain row combinations with t2's NULL-complimented row, while they should be present, as WHERE clause is TRUE for them: 
  "(1 = NULL) <=> (1 = NULL)"  ==  "NULL <=> NULL"  ==  "TRUE". 

A check that they really should be there:
mysql> insert into t2 values (NULL,NULL);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1 left join t2 on true where (t1.a= t2.a) <=> (t1.b = t2.b);
+------+------+------+------+
| a    | b    | a    | b    |
+------+------+------+------+
|    1 |    1 |    1 |    1 |
|    1 |    1 | NULL | NULL |
|    2 |    2 |    1 |    1 |
|    2 |    2 | NULL | NULL |
+------+------+------+------+
4 rows in set (0.00 sec)

[21 Jul 2005 14:51] Sergey Petrunya

The described behavior is expected. in the 1st query, all rows from t1 have matching rows in t2 and so there are no NULL rows.