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());
}