MySQL Bugs: #2654: JDBC Exception: "Column 'column.table' not found" when "order by" in query

Bug #2654	JDBC Exception: "Column 'column.table' not found" when "order by" in query
Submitted:	5 Feb 2004 10:51	Modified:	30 Mar 2004 7:30
Reporter:	Andrew Carlon	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.1-alpha-standard	OS:	Linux (Linux (2.4.21))
Assigned to:	Michael Widenius	CPU Architecture:	Any

Description:
The newest version of MySQL's stable JDBC driver (3.0.10) seems to have problems with ResultSets of queries that use "order by" clauses when the columns are requested by their full "table.column" format in getXXX(String columnName) methods.  This only occurs on 4.1; my 4.0.15 does not manifest this problem.

So, for example, where "rs" is the ResultSet for a query like "select foo.id, foo.data from foo order by foo.id",

   rs.getString("foo.data");

throws a java.sql.SQLException, whereas

   rs.getString("data");

and

   rs.getString(2);

do not.  Neither does

   rs.getString("foo.data");

if the "order by" clause is omitted.

In the query above, it is unnecessary to specify the tablename in the getXXX() method.  In the example I give under "How to Repeat", however, it is, because two tables with identically named columns (foo.data and bar.data) are joined.

Workarounds:
1) Don't sort (suboptimal)
2) If you know the order of the columns returned, and don't mind a potential hassle updating your code if you change your query, you can always use the getXXX(int i) method

   rs.getString(1);

3) The error is also not manifest if you use aliases, such as follows:

   "select foo.data as foo_data, bar.data as bar_data where foo.id=bar.id
    order by foo.id"

and use the alias name in your getXXX() method.

   rs.getString("foo_data");

How to repeat:
First, the tables:
-- MySQL dump 10.2
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version       4.1.1-alpha-standard

CREATE TABLE foo (
  id tinyint(3) default NULL,
  data varchar(255) default NULL
) TYPE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO foo VALUES (1,'male'),(2,'female');

CREATE TABLE bar (
  id tinyint(3) unsigned default NULL,
  data char(3) default '0'
) TYPE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO bar VALUES (1,'yes'),(2,'no');

Java Code:

    String statement = "select foo.id, foo.data, bar.data from foo, bar where "+
                       "foo.id = bar.id order by foo.id";
    String column = "foo.data";
    Connection conn = DriverManager.getConnection(dbURL);

    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery(statement);
    rs.next();
    String fooData = rs.getString(column);

It appears that this is actually a server problem. If you take a look at the packets coming back, the field-level metadata is all wrong. The table name is missing completely, and the 'original' non-aliased table name comes back as the name of the temp table that was used to do the order by a Fields.toString() in the JDBC driver ( in the format of database_name.table_label(original_table_name).column_label(original_column_name)) , gives us a value of:

Field 1:  . (/tmp/#sql_6f1_0) . id(id)
Field 2:  . (/tmp/#sql_6f1_0) . data(data)
Field 3:  . (/tmp/#sql_6f1_0) . data(data)

I am therefore changing the category of this bug to 'MySQL Server', as it is the server that is clobbering this data.

i also have this bug in the same version 4.1.1-alpha but with innodb and solaris 8.
I thing this bug is pretty serious because many queries require sorting and  even when speed is better using numbers to locate a column, names are more accurated  at developling time.
Hope the team can fix it in 4.1.2.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

The fix will be in 4.1.2

I am experiencing this bug again.

using centos 5
kernel.i686                              2.6.18-8.1.8.el5       installed
kernel.i686                              2.6.18-8.el5           installed
mysql.i386                               5.0.22-2.1.0.1         installed
mysql-server.i386                        5.0.22-2.1.0.1         installed

both mysql-connector-java-5.1.5-bin.jar/ and mysql-connector-java-5.0.6-bin.jar have problem. 

By the way, on windows vista business, "Server version: 5.0.41-community-nt MySQL Community Edition (GPL)" and mysql-connector-java-5.0.6-bin.jar, it is working fine.