MySQL Bugs: #3020: subselect still not work in FROM clause in prepared statements

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

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

Stack trace file

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

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

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.

Verified against 4.1.2, and changing the category as this is an error in the server rather than Connector/J.  Thank you.

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)

Thank you for bugreport. Fix for this bug is pushed to our internal source 
repository and vill be present in next server release