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 ]
• Status: Closed
• 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 [e-mail] +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.