Bug #18290 Problems with multiple joins

• Submitted: 16 Mar 2006 21:00
• Modified: 17 Mar 2006 20:25
• Reporter: Eric Johnson
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.19
• OS: Linux (Linux Centos 4.1)
• CPU Architecture: Any

[16 Mar 2006 21:00] Eric Johnson

Description:
After upgrading a test server to mysql 5.19 from 4.x I am going through and making sure that all my queries still work.  A bunch of my queries no longer work.  The problem seems to come from when ever i am dealing with a query that invloves atleast two straight joins and an outer join.  If I try to reference a table in an outer join that is not the last table in a straight join then the query will fail returning an error stating that the column dosnt exsist.

Example

mysql> select   n.note_id, u.user, n.user_id, 
    -> date_format(notedate, '%m/%d/%y %h:%i %p') "notedate", notes,
    -> scvn.note_id as cus_view, siex.msg_id 
    -> from     sar_notes n, sar_users u
    -> left outer join sar_cus_viewable_notes scvn on (n.id = scvn.sar_id and n.note_id = scvn.note_id) 
    -> left outer join sar_incoming_email_xref siex on (n.note_id = siex.xref_id and siex.type = 2) 
    -> where    id = 178685
    ->    and   u.user_id = n.user_id 
    -> order by note_id;
ERROR 1054 (42S22): Unknown column 'n.id' in 'on clause'

but If I change it so that sar_notes is the last straight join the query works fine:

mysql> select   n.note_id, u.user, n.user_id, 
    -> date_format(notedate, '%m/%d/%y %h:%i %p') "notedate", notes,
    -> scvn.note_id as cus_view, siex.msg_id 
    -> from     sar_users u, sar_notes n
    -> left outer join sar_cus_viewable_notes scvn on (n.id = scvn.sar_id and n.note_id = scvn.note_id) 
    -> left outer join sar_incoming_email_xref siex on (n.note_id = siex.xref_id and siex.type = 2) 
    -> where    id = 178685
    ->    and   u.user_id = n.user_id 
    -> order by note_id;
Empty set (0.02 sec)

The problem is that on some on my more complicated queries I can't rewrite them this way because I am outer joining against more that one table.

Thanks

How to repeat:
provided in description

[17 Mar 2006 20:13] Eric Johnson

here are complete instructions on howto replicate the problem:

mysql> create table test1 (id int(1), blah varchar(20));
Query OK, 0 rows affected (0.00 sec)

mysql> create table test2 (id int(1),ref int(1),  blah varchar(20));
Query OK, 0 rows affected (0.00 sec)

mysql> create table test3 (id int(1),ref int(1),  blah varchar(20));
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1, test2 left outer join test3 on (test1.id = test3.ref) where test1.id = test2.ref;
ERROR 1054 (42S22): Unknown column 'test1.id' in 'on clause'

but doing the same thing on mysql 4.x works fine.

[17 Mar 2006 20:25] MySQL Verification Team

Thank you for the bug report. Pleas read the Manual regarding the
Join syntax it was changed since version 5.0.12.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.20-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table test1 (id int(1), blah varchar(20));
Query OK, 0 rows affected (0.08 sec)

mysql> create table test2 (id int(1),ref int(1),  blah varchar(20));
Query OK, 0 rows affected (0.07 sec)

mysql> create table test3 (id int(1),ref int(1),  blah varchar(20));
Query OK, 0 rows affected (0.04 sec)

mysql> select * from test1, test2 left outer join test3 on (test1.id =
    -> test3.ref) where test1.id = test2.ref;
ERROR 1054 (42S22): Unknown column 'test1.id' in 'on clause'

mysql> select * from (test1, test2) left outer join test3 on (test1.id = test3.ref) where test1.id = test2.ref;
Empty set (0.00 sec)