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 Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:4.1-bk OS:
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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.049 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.