Bug #32265 Server returns different metadata if prepared statement is used
Submitted: 11 Nov 2007 19:23 Modified: 15 Mar 2008 11:01
Reporter: Franklin Schmidt Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Prepared statements Severity:S3 (Non-critical)
Version:5.0.50pb OS:Any
Assigned to: Davi Arnaut CPU Architecture:Any
Triage: D2 (Serious)

[11 Nov 2007 19:23] Franklin Schmidt
Description:
If I have a ResultSet on table 't' with field 's', then I can call ResultSet.getString("t.s").  But if I first call Statement.setFetchSize() then this field isn't found.

How to repeat:
import java.sql.*;
import java.util.Properties;

class FetchBug {
	static final String url = "jdbc:mysql://localhost/test";
	static final String user = "";
	static final String password = "";

	public static void main(String[] args) throws Exception {
		Class.forName("org.gjt.mm.mysql.Driver");
		Properties props = new Properties();
		props.setProperty("useCursorFetch","true");
		props.setProperty("user",user);
		props.setProperty("password",password);
		Connection con = DriverManager.getConnection(url,props);
		Statement stmt = con.createStatement();
		stmt.executeUpdate( "drop table if exists t" );
		stmt.executeUpdate( "create table t (i int)" );
		stmt.executeUpdate( "insert into t set i=1" );
		ResultSet rs = stmt.executeQuery( "select * from t" );
		rs.next();
		rs.getInt("t.i");  // ok
		rs.close();
		stmt.setFetchSize(10);
		rs = stmt.executeQuery( "select * from t" );
		rs.next();
		rs.getInt("t.i");  // fails
		rs.close();
		stmt.close();
		con.close();
	}
}
[13 Nov 2007 12:47] Tonci Grgin
Java test case

Attachment: TestBug32265.java (text/x-java), 1.76 KiB.

[13 Nov 2007 12:50] Tonci Grgin
Hi Franklin and thanks for your report.

I will try to explain what I think is happening here. Server is missing metadata, since setting "useCursorFetch=true", and then calling setFetchSize(>0) will actually create a server-side prepared statement behind the scenes, and use cursor-based fetching with it which probably ends up creating a temporary table on the server. Let me clarify this with general query log:

071113 13:43:52	     12 Connect     root@localhost on test
		     12 Query       SHOW SESSION VARIABLES
		     12 Query       SHOW COLLATION
		     12 Query       SET NAMES utf8
		     12 Query       SET character_set_results = NULL
		     12 Query       SET autocommit=1
		     12 Query       SET sql_mode='STRICT_TRANS_TABLES'
		     12 Query       SELECT VERSION()
		     12 Query       DROP TABLE IF EXISTS bug32265
		     12 Query       CREATE  TABLE bug32265 (Id INTEGER NOT NULL PRIMARY KEY)
		     12 Query       INSERT INTO bug32265 (Id) values (1)
		     12 Query       SELECT * FROM bug32265
		     12 Prepare     [1] SELECT * FROM bug32265
		     12 Execute     [1] SELECT * FROM bug32265
		     12 Query       DROP TABLE IF EXISTS bug32265
		     12 Quit
[11 Feb 2008 16:17] Davi Arnaut
FWIW, attached test case succeeds with connector/j 5.0.4 and fails with later versions.
[11 Feb 2008 18:17] Tonci Grgin
Davi, I fail to see the relevance... Every connector team leader is free to work or not work around server bug we described here. He is even free to drop a workaround appearing to be functional and wait for proper fix. My guess would be this came in packet of changes in 5.0.5...
[11 Feb 2008 19:30] Konstantin Osipov
Connector team workaround: use result set metadata from PREPARE phase in such case, it contains the original table name.
[14 Feb 2008 19:42] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/42303

ChangeSet@1.2578, 2008-02-14 17:42:46-02:00, davi@mysql.com +2 -0
  Bug#32265 Server returns different metadata if prepared statement is used
  
  Executing a prepared statement associated with a materialized
  cursor yields to the client a metadata packet with wrong table
  and database names. The problem was occurring because the server
  was sending the the name of the temporary table used by the cursor
  instead of the table name of the original table. The same problem
  occurs when selecting from views, in which case the table name was
  being sent and not the name of the view.
  
  The solution is to backup the table and database names of the fields
  of the original tables or views and restore then later into the
  fields of the temporary table.
[20 Feb 2008 13:57] Konstantin Osipov
Approved a slightly different solution. Please put back to In Progress if the suggested solution is not correct.
[20 Feb 2008 19:45] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/42691

ChangeSet@1.2580, 2008-02-20 16:45:24-03:00, davi@mysql.com +2 -0
  Bug#32265 Server returns different metadata if prepared statement is used
  
  Executing a prepared statement associated with a materialized
  cursor yields to the client a metadata packet with wrong table
  and database names. The problem was occurring because the server
  was sending the the name of the temporary table used by the cursor
  instead of the table name of the original table. The same problem
  occurs when selecting from views, in which case the table name was
  being sent and not the name of the view.
    
  The solution is to fill the list item from the temporary table but
  preserving the table and database names of the original fields. This
  is achieved by tweaking the Select_materialize to accept a pointer to
  the Materialized_cursor class which contains the item list to be filled.
[20 Feb 2008 20:40] Davi Arnaut
Queued in 5.0-runtime
[26 Feb 2008 10:56] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/42979

ChangeSet@1.2578, 2008-02-26 13:55:46+03:00, kostja@dipika.(none) +1 -0
  Use an API instead of looking into stmt internals to fetch fields
  (Test for Bug#32265)
[3 Mar 2008 18:14] Bugs System
Pushed into 5.0.58
[3 Mar 2008 18:18] Bugs System
Pushed into 6.0.5-alpha
[3 Mar 2008 18:18] Bugs System
Pushed into 5.1.24-rc
[15 Mar 2008 11:01] Jon Stephens
Documented bugfix in the 5.0.58, 5.1.24, and 6.0.5 changelogs as follows:

        Executing a prepared statement associated with a materialized
        cursor sent to the client a metadata packet with incorrect table
        and database names. The problem occurred because the server
        sent the the name of the temporary table used by the cursor
        instead of the table name of the original table. 

        The same problem occured when selecting from a view, in which case the
        name of the table name was sent, rather than the name of the view.
[31 Mar 2008 14:36] Jon Stephens
Also noted fix in the 5.1.23-ndb-6.3.11 changelog.