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:
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

[5 Feb 2004 10:51] Andrew Carlon
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",


throws a java.sql.SQLException, whereas




do not.  Neither does


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.

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


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.


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

  id tinyint(3) default NULL,
  data varchar(255) default NULL
INSERT INTO foo VALUES (1,'male'),(2,'female');

  id tinyint(3) unsigned default NULL,
  data char(3) default '0'
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);
    String fooData = rs.getString(column);
[5 Feb 2004 11:23] Mark Matthews
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.
[14 Feb 2004 14:00] Carlos Proal Aguilar
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.
[30 Mar 2004 7:30] Michael Widenius
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

Additional info:

The fix will be in 4.1.2
[26 Oct 2007 9:04] Yi Meng
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-         installed
mysql-server.i386                        5.0.22-         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.