Bug #3245 Cross dependency when running LEFT JOIN PreparedStatement for the first time
Submitted: 20 Mar 2004 4:30 Modified: 9 Apr 2004 0:28
Reporter: [ name withheld ] Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.2 OS:Linux (Linux)
Assigned to: Oleksandr Byelkin CPU Architecture:Any

[20 Mar 2004 4:30] [ name withheld ]
Description:
When executing the select above THE FIRST TIME after the server starts, an
"Error. SQLException: Syntax error or access violation message from server: "Cross dependency found in OUTER JOIN. Examine your ON conditions" SQLState: 42000 VendorError: 1120" occures. 

This happens ONLY with PREPAREDSTATEMENT.

Any further executions returns successfully with value.

The select:
select
  t.id,
  p1.value,
  n1.value,
  p2.value,
  n2.value
from
  test1 t
  LEFT JOIN par1 p1 ON (p1.id=t.param1_id)
  LEFT JOIN par2 p2 ON (p2.id=t.param2_id)
  LEFT JOIN name_t n1 ON (n1.id=p1.name_id)
  LEFT JOIN name_t n2 ON (n2.id=p2.name_id)
where
  t.id=1;

Environment:
- MySQL server 4.1.2-alpha-nightly-20040229 on Linux. (Mandrake 8.1)
- mysql-connector-java-3.1-nightly-20040229-bin.jar (on Win98) 
- JSDK 1.4.2 b-28 (on Win98)
- Apache Tomcat 4.1.29 (on Win98)

How to repeat:
The script:

drop table test1;
create table test1 (
  id  int(8),
  param1_id int(8),
  param2_id int(8)
) TYPE=InnoDB DEFAULT CHARSET=utf8;

drop table par1;
create table par1 (
  id  int(8),
  name_id int(8),
  value varchar(10)
) TYPE=InnoDB DEFAULT CHARSET=utf8;

drop table par2;
create table par2 (
  id  int(8),
  name_id int(8),
  value varchar(10)
) TYPE=InnoDB DEFAULT CHARSET=utf8;

drop table name_t;
create table name_t(
  id  int(8),
  value varchar(10)
) TYPE=InnoDB DEFAULT CHARSET=utf8;

insert into test1 values (1,1,1);
insert into test1 values (2,2,null);
insert into par1 values (1,1,'aaa');
insert into par1 values (2,null,'bbb');
insert into par2 values (1,2,'ccc');
insert into name_t values (1,'Name1');
insert into name_t values (2,null);
commit;

The code (Java):
String qry="select"+
"  t.id,"+
"  p1.value,"+
"  n1.value,"+
"  p2.value,"+
"  n2.value"+
" from"+
"  test1 t"+
"  LEFT JOIN par1 p1 ON (p1.id=t.param1_id)"+
"  LEFT JOIN par2 p2 ON (p2.id=t.param2_id)"+
"  LEFT JOIN name_t n1 ON (n1.id=p1.name_id)"+
"  LEFT JOIN name_t n2 ON (n2.id=p2.name_id)"+
"where"+
"  t.id=1";

  try
  {
    java.sql.PreparedStatement pstmt=conn.prepareStatement(qry);
    java.sql.Statement stmt=conn.createStatement();

    java.sql.ResultSet rs;
    rs=stmt.executeQuery(qry);
    while(rs.next())
    {
      out.println(rs.getString(1)+"<br>");
    }
    rs.close();

    out.println("<br>");

    //pstmt.setInt(1,1);
    rs=pstmt.executeQuery();
    while(rs.next())
    {
      out.println(rs.getString(1)+"<br>");
    }
    rs.close();

    stmt.close();
    pstmt.close();
  }
  catch (java.sql.SQLException sqlEx)
  {
    // handle any errors 
    out.println("Error."); 
    out.println(" SQLException: " + sqlEx.getMessage()); 
    out.println(" SQLState:     " + sqlEx.getSQLState()); 
    out.println(" VendorError:  " + sqlEx.getErrorCode());
  }
[26 Mar 2004 11:36] Dean Ellis
Verified against 4.1.2; attaching test case.  Thank you.
[9 Apr 2004 0:28] Oleksandr Byelkin
Thank you for good bug report. But this bug was already fixed during other 
bugs fixing (BUG#2794), but I'll put your example of join in our test suite