Bug #3020 subselect still not work in FROM clause in prepared statements
Submitted: 1 Mar 2004 2:47 Modified: 18 Apr 2004 9:21
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

[1 Mar 2004 2:47] [ name withheld ]
Description:
When I try to prepare a PreparedStatement with a query:
"select count(1) from (select f.id from test1 f where f.id=?) as x"

This error happens:
"Error. SQLException: Communications link failure due to underlying exception: ** BEGIN NESTED EXCEPTION ** java.io.EOFException STACKTRACE: java.io.EOFException at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1633) at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:1845) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2201) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1359) at com.mysql.jdbc.ServerPreparedStatement.serverPrepare(ServerPreparedStatement.java:1242) at com.mysql.jdbc.ServerPreparedStatement.(ServerPreparedStatement.java:111) at com.mysql.jdbc.Connection.prepareStatement(Connection.java:1116) at com.mysql.jdbc.Connection.prepareStatement(Connection.java:1088)" - This means the server died, I think.

Comment:
This bug is related to bug #2641.
This might be a server bug, but I'm not sure, because if there are no bind variables (question marks) in the query the error does not occur.
Interestingly, it doesn't occurs either when there is no primary key defined in the table.

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),
  primary key (id)
) TYPE=InnoDB DEFAULT CHARSET=utf8;
insert into test1 values (1);

The code:
  String qry="select count(1) from (select f.id from test1 f where f.id=?) as x";

  try
  {
    java.sql.PreparedStatement pstmt=conn.prepareStatement(qry); //<-The error occures here.
    java.sql.ResultSet rs;

    pstmt.setInt(1,1);
    rs=pstmt.executeQuery();
    while(rs.next())
    {
      out.println(rs.getString(1)+"<br>");
    }
    rs.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());
  }
[1 Mar 2004 3:06] [ name withheld ]
Stack trace file

Attachment: stack.trc (application/octet-stream, text), 3.45 KiB.

[1 Mar 2004 20:19] Wil Williams
Also experienced using 4.1.1alpha. It seems to be a server bug since it causes the server to terminate all (other?) connections and produce this message (when running the server manually from the command line as a normal user):

Number of processes running now: 0
040302 12:11:23  mysqld restarted
[1 Mar 2004 20:24] Wil Williams
I am using JDBC connector "mysql-connector-java-3.1.1-alpha-bin.jar", Debian Linux with "mysql-standard-4.1.1-alpha-pc-linux-i686".

I am however doing a join rather than a subselect. The query is of form: "select fields from a,b where a.f=b.f and id=? order by something".

I get around the problem by using statements. It's also 100% reliable/repeatable so it's not a major issue since it's easily found an removed.
[6 Mar 2004 13:38] Dean Ellis
Verified against 4.1.2, and changing the category as this is an error in the server rather than Connector/J.  Thank you.
[8 Apr 2004 13:40] Oleksandr Byelkin
ChangeSet 
  1.1783 04/04/08 23:28:47 bell@sanja.is.com.ua +2 -0 
  fixed subquery in the FROM clause with parameter (BUG#3020)
[18 Apr 2004 9:21] Oleksandr Byelkin
Thank you for bugreport. Fix for this bug is pushed to our internal source 
repository and vill be present in next server release