Bug #2794 PreparedStatement dismiss LEFT JOIN ON clause when WHERE clause is present
Submitted: 14 Feb 2004 9:40 Modified: 1 Apr 2004 13:30
Reporter: [ name withheld ] Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.2 OS:Linux (Linux)
Assigned to: Oleksandr Byelkin CPU Architecture:Any

[14 Feb 2004 9:40] [ name withheld ]
Description:
By executing the code below, Statement behaves differently from PreparedStatement. It seems like PreparedStatement dismiss the condition in the "ON" clause, and executes only the "where" clause.

Environment:
- MySQL server 4.1.2-alpha-nightly-20040211 on Linux. (Mandrake 8.1)
- mysql-connector-java-3.1-nightly-20040211-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 test6;
create table test6
(
  id int(8)
);
insert into test6 values (123);

drop table test7;
create table test7
(
  id int(8),
  filter int(8),
  value varchar(10)
);
insert into test7 values (123,1,'abc');
insert into test7 values (123,2,'bcd');
insert into test7 values (14,1,'cde');
insert into test7 values (14,2,'def');

The code:
  String qry="select value from test6 LEFT JOIN test7 ON (test6.id=test7.id) where test7.filter=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>");

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

The result is:

abc

abc
cde
[17 Feb 2004 11:00] Dean Ellis
Verified against 4.1.2.  It does appear to be producing a cartesian product, easily observed by adding unrelated rows to the test6 table.

Thank you.
[31 Mar 2004 16:06] Oleksandr Byelkin
ChangeSet 
  1.1757 04/04/01 03:00:38 bell@sanja.is.com.ua +5 -0 
  fix for table/field caching mechanism 
  save moving ON/USING tables conditions to WHERE clause (BUG#2794)
[1 Apr 2004 13:30] Oleksandr Byelkin
Thank you for bugreport! Patch for this bug is pushed to our local repository 
and will be present in next server release.