Bug #4922 Queries with nested outer joins return wrong result sets for 1 row tables

• Submitted: 6 Aug 2004 9:02
• Modified: 7 Aug 2004 4:54
• Reporter: Igor Babaev
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.1
• OS: Any (Any)
• Assigned to: Igor Babaev
• CPU Architecture: Any

[6 Aug 2004 9:02] Igor Babaev

Description:
If all tables in the query contain not more than 1 row that queries with nested left (right) outer joins return wrong result sets 

How to repeat:
mysql> create table t1 (a int);
Query OK, 0 rows affected (0.26 sec)

mysql> create table t2 (a int);
Query OK, 0 rows affected (0.00 sec)

mysql> create table t3 (a int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values (1);
Query OK, 1 row affected (0.30 sec)

mysql> insert into t2 values (2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t3 values (2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1 left join t2 left join t3 on t2.a=t3.a on t1.a=t3.a;
+------+------+------+
| a      | a       | a      |
+------+------+------+
|      1 |       2 |      2 |
+------+------+------+

Apparenty this is a wrong result.

mysql> insert into t1 values (2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1 left join t2 left join t3 on t2.a=t3.a on t1.a=t3.a;
+------+------+------+
| a      | a       | a      |
+------+------+------+
|      1 | NULL  |  NULL|
|      2 |       2 |      2 |
+------+------+------+

This is ok.

mysql> delete from t1 where a=2;
Query OK, 1 row affected (0.00 sec)

mysql> delete from t2;
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1 left join t2 left join t3 on t2.a=t3.a on t1.a=t3.a;
Empty set (0.01 sec)

This is again a wrong result.

[7 Aug 2004 4:54] Igor Babaev

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

The bug was fixed in file sql_select.cc.
The fix just blockes an optimization for tables containing not more than 1 row if they are inner tables of nested outer joins.  

A typo in sql_yacc.yy that caused syntax error for the query in the report:
select * from t1 left join t2 left join t3 on t2.a=t3.a on t1.a=t3.a
was fixed as well.

ChangeSet
  1.1726 04/08/06 10:22:20 [e-mail] +4 -0
  join_nested.result, join_nested.test:
    Added a test case for bug #4922.
  sql_select.cc:
    Blocked an optimization performed by join_read_const_table when
    applied to an inner table of a nested outer join.
    It was done to fix bug #4922.
  sql_yacc.yy:
    Fixed a typo bug in the rule for join_table.