MySQL Bugs: #14897: ResultSet.getString("table.column") sometimes doesn't find the column

Bug #14897	ResultSet.getString("table.column") sometimes doesn't find the column
Submitted:	12 Nov 2005 11:42	Modified:	12 Sep 2006 0:57
Reporter:	Filip Rachunek	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S2 (Serious)
Version:	5.0	OS:	Windows (WinXP)
Assigned to:	Konstantin Osipov	CPU Architecture:	Any

Description:
I am just trying to upgrade to MySQL 5.0.15 with my Java web application and I've experienced some strange behaviour when executing a select from three tables as a join:

select a.id, b.id, c.en, c.cz from table1 as a, table2 as b, lang_table as c where a.id = b.id and a.name_id = c.id;

When I create a ResultSet instance for this query and call rs.getString("c.cz"), the driver throws this exception:

java.sql.SQLException: Column 'c.cz' not found.
        at com.mysql.jdbc.ResultSet.findColumn(ResultSet.java:910)
        at com.mysql.jdbc.ResultSet.getString(ResultSet.java:4947)

The c.en and c.cz columns are of varchar(255) type in utf_general_ci.
The interesting thing is that it perfectly works with MySQL 4.1.14 but doesn't work with MySQL 5.0.15. I've also tried the nightly build of Connector/J 5.0 with the same result.

How to repeat:
I am not sure because I don't know if it's a problem of the connector or the database server. Please let me know if you need more information.

Filip,

Any chance you could add the schema as a file here to this bug so we're looking at the same schema/data?

I'm having the same problem.  Version: 3.1.11, OS: Mac OS X.
mysql  Ver 14.12 Distrib 5.0.15, for apple-darwin8.2.0 (powerpc) using readline 5.0

thisUser.setUserId(rst.getInt(1)); works, but thisUser.setUserId(rst.getInt("USER_ID")); throws an SQLException: Invalid value for getInt() - ''

Here's the Java I'm using
            String sql = "SELECT * FROM RA_USERS ORDER BY LOGIN";
            Statement statement = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ResultSet rst = statement.executeQuery(sql);
            Vector results = new Vector();
            while (rst.next())
            {
                User thisUser = new User();
                thisUser.setLogin(rst.getString("LOGIN"));
                logger.debug("ResutSet.getObject('USER_ID') =" +  rst.getObject("USER_ID").getClass().getName());
//                thisUser.setUserId(rst.getInt("USER_ID"));
                thisUser.setUserId(rst.getInt(1));
                logger.debug("ResutSet.getObject('IS_ACTIVE') =" +  rst.getObject("IS_ACTIVE").getClass().getName());
                thisUser.setIsActive(rst.getInt("IS_ACTIVE"));
                results.add(thisUser);
            }
            rst.close();
            statement.close();
            return results;

mysql> desc ra_users;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| USER_ID   | int(11)      | NO   | PRI | NULL    | auto_increment |
| LOGIN     | varchar(255) | NO   |     |         |                |
| IS_ADMIN  | tinyint(1)   | NO   |     |         |                |
| IS_ACTIVE | tinyint(1)   | NO   |     |         |                |
+-----------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> select * from ra_users;
+---------+-------+----------+-----------+
| USER_ID | LOGIN | IS_ADMIN | IS_ACTIVE |
+---------+-------+----------+-----------+
|       1 | USER  |        0 |         1 |
+---------+-------+----------+-----------+
1 row in set (0.00 sec)

Guess it's not the same problem, but I believe the issue is in ResultSet.findColumn(String).

Please ignore my comments.  I didn't restart my server (Tomcat) properly and I think it was still using version 3.1.10.  With 3.1.11 my issue seems to be fixed.  Sorry for the confusion.

Filip,

Here's a testcase I've tried that works, so maybe it's related to the data in your table, or some other structure I'm not replicating? I'm also thinking that maybe this is related to the following bug (which you can test by issuing your query in the command line client "mysql"):

http://bugs.mysql.com/bug.php?id=14861

createTable("table1", "(id int, name_id int)");
		createTable("table2",  "(id int)");
		createTable("lang_table", "(id int, en varchar(255) CHARACTER SET utf8, cz varchar(255) CHARACTER SET utf8)");
		
		this.stmt.executeUpdate("insert into table1 values (0, 0)");
		this.stmt.executeUpdate("insert into table2 values (0)");
		this.stmt.executeUpdate("insert into lang_table values (0, 'abcdef', 'ghijkl')");
		this.rs = this.stmt.executeQuery("select a.id, b.id, c.en, c.cz from table1 as a, table2 as b, lang_table as c where a.id = b.id and a.name_id = c.id");
		assertTrue(this.rs.next());
		this.rs.getString("c.cz");

Thank you Mark. I will try to make a testcase ASAP.

I am experiencing the same or a similar problem in MySQL 5.0.16 running on Solaris 9; however my problem does not involve aliasing tables.

In previous versions of MySQL, I've always been able get the value of a column from a ResultSet by using the table.column notation.  This appears to no longer be a valid way of specifying the column for which to retrieve the value.  

I've used this notation in the past to safeguard against the possibility of duplicate column names in complex select statements.  For example if I have two tables, t1 and t2, both of which have an id column, I could still write statements like: 

Connection conn = DriverManager.getConnection( ... );
Satement stmt = conn.createStatement( );
ResultSet rs = stmt.executeQuery( "select t1.*, t2.* from t1, t2 where ..." );

and I could retrieve the respective ids as follows:

int t1ID = rs.getInt( "t1.id" );
int t2ID = rs.getInt( "t2.id" );

This was working as of MySQL version 4.1.14 using Connector/J version 3.1.11.  I've recently upgraded to MySQL version 5.0.16 (still using the same connector), and noticed that attempting to retrieve the IDs this way results in a SQLException being thrown:

java.sql.SQLException: Column 't1.id' not found.

The only way that I have been able to get this to work is to alias the columns so the names are unique, and retrieve the values by the column name only.

In fact, I have even seen this problem on columns that are not duplicated in a result set.  For example, if I have two tables, t1 (id int, name varchar(64)) and t2 (id int, type int), and I issue the following select:

ResultSet rs = stmt.executeQuery( "select t1.*, t2.type from t1, t2 where ..." );

and I try to get the value of t1's name, I get the following exception:

java.sql.SQLException: Column 't1.name' not found.

I have grown accustomed to using this notation to prevent the possibility of ambiguity, but this appears to no longer work.  If I alias t1.name to t1Name and attempt to retrieve its value from the result set that way, it works fine.  Any help would be appreciated.
-Jim

Jim,

I'm still not able to reproduce this issue, maybe your schema has something in common with Filips?

The following small test works for me w/ 3.1.11 and MySQL-5.0.17 (latest build from BitKeeper):

this.rs = this.stmt.executeQuery("select table1.*, table2.* FROM table1, table2");
this.rs.findColumn("table1.id");
this.rs.findColumn("table2.id");

Can you create a standalone _full_ testcase that we can test?

Mark,
I spent the morning looking at this, and I believe I have figured out what is causing the problem.  This appears to be more of an upgrade issue (going from 4.1 to 5.0) than an issue with the 5.0 database or connector.  Furthermore, this issue does not affect all columns.  Integer types are fine, but columns of type VARCHAR are affected.

I attempted to create a small test database this morning to reproduce the problems that I was seeing with my real database, but I was unable to do so.  However, I was able to get the following scenario to fail:

Create the following tables (Borrowing the example from one of the linked bug reports), and populate them, in a database under version 4.1.14 (Any 4.1 database would likely suffice)

CREATE TABLE `lang_table` ( 
    `id` int(11) default NULL, 
    `en` varchar(255) character set utf8 default NULL, 
    `cz` varchar(255) character set utf8 default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `lang_table` (`id`, `en`, `cz`) VALUES 
    (1,'en string 1','cz string 1'),
    (2,'en string 2','cz string 2'),
    (3,'en string 3','cz string 3');

CREATE TABLE `table1` ( 
    `id` int(11) default NULL, 
    `name_id` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `table1` (`id`, `name_id`) VALUES (1,1),(2,3),(3,3);

CREATE TABLE `table2` ( 
    `id` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `table2` (`id`) VALUES (1),(2),(3);

Use the following java code using the 3.1.11 connector to execute a statement

try
{
    Class.forName( "com.mysql.jdbc.Driver").newInstance( );
    Connection testConn = DriverManager.getConnection( 
                    "jdbc:mysql://localhost/testDB?user=jimremus" );
    String query = "select table1.*,table2.id,lang_table.en,lang_table.cz " + 
                   "from table1 left join table2 on table1.id=table2.id " + 
                   "left join lang_table on table1.id=lang_table.id " + 
                   "order by lang_table.id";
    Statement stmt = testConn.createStatement( );
    ResultSet rs = stmt.executeQuery( query );
    System.out.println( "Executing Query: " + query );
    for( int i = 1; rs.next( ); i++ )
    {
        System.out.println( "Result " + i + ":" );
        System.out.println( "\ttable1.id = " + rs.getInt( "table1.id" )
        
        System.out.println( "\ttable1.name_id = " + rs.getInt( "table1.name_id" ) );
        System.out.println( "\ttable2.id = " + rs.getInt( "table2.id" ) );
        System.out.println( "\tlang_table.en = " + rs.getString( "lang_table.en" ) );
        System.out.println( "\tlang_table.cz = " + rs.getString( "lang_table.cz" ) );
    }
    stmt.close( );
    testConn.close( );
}
catch( SQLException e )
{
    System.out.println( "Caught exception: " + e );
    e.printStackTrace( );
}

This code will work fine while running MySQL version 4.1.14, but as soon as I switch to 5.0.16, I get the following exception when I try to retrieve the value of one of the VARCHAR columns using the table.column notation:

Caught exception: java.sql.SQLException: Column 'lang_table.en' not found.
java.sql.SQLException: Column 'lang_table.en' not found.
        at com.mysql.jdbc.ResultSet.findColumn(ResultSet.java:929)
        at com.mysql.jdbc.ResultSet.getString(ResultSet.java:4977)

Now, if I drop the tables in version 5.0.16 and then recreate and repopulate them, running the same code works fine and the expected values are returned.  So the way I've managed to work around this issue is to dump my database, drop the tables, and repopulate the database using the dump file.

I was not aware of any incompatibilities with VARCHAR between version 4.1 and 5.0 (at least I didn't see anything listed in the 'Upgrading from Version 4.1 to 5.0' document) and running a CHECK TABLE on any/all of the tables returns an OK status.  So I'm not exactly sure what the problem is, but at least there is a way to fix/work around it.

I hope this helps.
-Jim

Seems to be an upgrade regression, since it only happens with 4.1.x tables running in a 5.0.x server....

I am able to replicate this bug on 5.0.x (5.0.18, 5.0.21) using Connector/J 3.1.2. The
bug seems to be that the column from one table in the join is not found.

Here's the Java code:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class JoinBug {

    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        Connection conn = DriverManager.getConnection("jdbc:mysql://ccdev2.jlab.org:3306/bug?user=XXX&password=YYY");
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("select * from t1 join t2 using (b);");
        rs.next();
        long t1b = rs.getLong("t1.b");
        System.out.println("t1.b=" + t1b);
        long t2b = rs.getLong("t2.b");
        System.out.println("t2.b=" + t2b);
    }

}
================================================================================

Here's the database dump:

CREATE TABLE `t1` (
  `a` bigint(20) default NULL,
  `b` bigint(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `t1` VALUES (1,2);

CREATE TABLE `t2` (
  `b` bigint(20) default NULL,
  `c` bigint(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `t2` VALUES (2,3);

================================================================================

Here is the output. Note that we can get t1.b from the ResultSet, but not t2.b. This worked in 4.1.x

# java -cp .:/tmp/mysql-connector-java-3.1.12-bin.jar JoinBug
t1.b=2
Exception in thread "main" java.sql.SQLException: Column 't2.b' not found.
        at com.mysql.jdbc.ResultSet.findColumn(ResultSet.java:950)
        at com.mysql.jdbc.ResultSet.getLong(ResultSet.java:2603)
        at JoinBug.main(JoinBug.java:16)

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/9356

Patch approved, but add a test case before pushing.

(convert_blob_length should not be a problem in create_tmp_field_from_field(), as this is only set when it's ok to do field conversation of blobs (for example 'min(blob_column)'))

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/11030

ChangeSet@1.2249, 2006-08-30 00:38:58+04:00, kostja@bodhi.local +4 -0
  A fix for Bug#14897 "ResultSet.getString("table.column") sometimes 
  doesn't find the column"
  
  When a user was using 4.1 tables with VARCHAR column and 5.0 server
  and a query that used a temporary table to resolve itself, the
  table metadata for the varchar column sent to client was incorrect:
  MYSQL_FIELD::table member was empty.
  
  The bug was caused by implicit "upgrade" from old VARCHAR to new
  VARCHAR hard-coded in Field::new_field, which did not preserve
  the information about the original table. Thus, the field metadata
  of the "upgraded" field pointed to an auxiliary temporary table
  created for query execution.
  
  The fix is to copy the pointer to the original table to the new field.

Pushed into 5.0-runtime

Bug#14915 "mysql_result(..., "tablename.fieldname") + Upgrade 4.1 -> 5.0"
was marked as a duplicate of this bug.

Pushed into 5.0.25 and 5.1.12

Noted in 5.0.25, 5.1.12 changelogs.

When using tables created under MySQL 4.1 with a 5.0 server, if the
tables contained VARCHAR columns, for some queries the metadata
sent to the client could have an empty column name.