Bug #2844 PreparedStatement malfunction when using "not in" in a select

• Submitted: 17 Feb 2004 12:26
• Modified: 5 Apr 2004 5:40
• Reporter: [ name withheld ]
• Status: Duplicate
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.1.2
• OS: Any (Any)
• Assigned to: Assigned Account
• CPU Architecture: Any

[17 Feb 2004 12:26] [ name withheld ]

Description:
By executing the code below, the result of PreparedStatement is ecatly the opposit of the Statement. Statement works correctly.

This might related to bug 2794. (?)

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 test8;
create table test8
(
  id int(8)
);
insert into test8 values (1);
insert into test8 values (2);
insert into test8 values (3);
drop table test9;
create table test9
(
  id int(8)
);
insert into test9 values (2);
insert into test9 values (3);

The code:
  String qry="select id from test8 where id not in (select id from test9)";
   
  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:

1

2
3

[18 Feb 2004 15:22] Dean Ellis

Verified, thank you for the report.

IN (as opposed to NOT IN) also does not report correct results.

[1 Mar 2004 0:24] [ name withheld ]

I've checked against mysql-4.1.2-alpha-nightly-20040229.
It seems to be corrected.
You can close this bug.

[5 Apr 2004 5:40] Oleksandr Byelkin

Thank you for good bug report! 
 
We can't repeat in on current 4.1 tree, it looks like that it was fixed during 
Bug #2393 fixing.