Bug #291 bug in NATURAL LEFT JOIN, MySQL 4.0 and 4.1.

• Submitted: 15 Apr 2003 9:37
• Modified: 16 May 2003 1:53
• Reporter: Jani Tolonen
• Status: Closed
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.0, 4.1
• OS: Any (All)
• CPU Architecture: Any

[15 Apr 2003 9:37] Jani Tolonen

Description:
drop table if exists t1,t2,t3;
create table t1 (i int);
create table t2 (i int);
create table t3 (i int);
insert into t1 values(1),(2);
insert into t2 values(2),(3);
insert into t3 values(2),(4);
select * from t1 natural left join t2 natural left join t3;

In 4.0 and 4.1 the results look like this:
+------+------+------+
| i    | i    | i    |
+------+------+------+
|    1 |    2 | NULL |
|    2 |    2 |    2 |
|    1 |    3 | NULL |
|    2 |    3 |    2 |
+------+------+------+

While it should look like this:

+------+------+------+
| i    | i    | i    |
+------+------+------+
|    1 | NULL | NULL |
|    2 |    2 |    2 |
+------+------+------+

Regards,
Jani

How to repeat:
See above.

[15 Apr 2003 12:20] MySQL Verification Team

This was a bug in the parser. This is a patch:

===== sql/sql_yacc.yy 1.203 vs edited =====
*** /tmp/sql_yacc.yy-1.203-31362        Fri Mar 28 15:36:40 2003
--- edited/sql/sql_yacc.yy      Tue Apr 15 22:10:25 2003
***************
*** 495,502 ****
  /* These don't actually affect the way the query is really evaluated, but
     they silence a few warnings for shift/reduce conflicts. */
  %left ','
! %left STRAIGHT_JOIN JOIN_SYM
! %nonassoc     CROSS INNER_SYM NATURAL LEFT RIGHT
  
  %type <lex_str>
        IDENT TEXT_STRING REAL_NUM FLOAT_NUM NUM LONG_NUM HEX_NUM LEX_HOSTNAME
--- 495,502 ----
  /* These don't actually affect the way the query is really evaluated, but
     they silence a few warnings for shift/reduce conflicts. */
  %left ','
! %left STRAIGHT_JOIN JOIN_SYM NATURAL
! %nonassoc     CROSS INNER_SYM  LEFT RIGHT
  
  %type <lex_str>
        IDENT TEXT_STRING REAL_NUM FLOAT_NUM NUM LONG_NUM HEX_NUM LEX_HOSTNAME
[/mnt/work/mysql-4.0]$

[16 May 2003 1:53] Michael Widenius

Some extra information about this bug:

It was not a bug in the parser but rather a logical error in how chained LEFT JOIN / RIGHT JOINS was handled.

This is now fixed and will be in the 4.0.13 and 4.1.1 releases