Bug #35150 Inconsistency in column name causes Hibernate to fail.
Submitted: 7 Mar 2008 16:27 Modified: 7 Jul 2008 13:01
Reporter: Ramon Garcia Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:5.1.6 OS:Any
Assigned to:
Triage: D2 (Serious)

[7 Mar 2008 16:27] Ramon Garcia
Description:
The JDBC driver uses a different method for evaluating column names in resultsetmetadata.getColumnName() and when looking for a column in resultset.getObject(columnName). This causes Hibernate to fail in queries where the two methods yield different result, like queries that use alias names:

SELECT column AS aliasName from table

How to repeat:
Here is a simple testcase:

try {
			loadDriver();
			Connection c = DriverManager.getConnection("jdbc:mysql://localhost/mydata", "user", "pass");
			String query = "SELECT table.column AS alias FROM table";
			PreparedStatement s = c.prepareStatement(query);
			ResultSet result = s.executeQuery();
			result.next();
			ResultSetMetaData metadata = result.getMetaData();
			String nameField = metadata.getColumnName(1);
			Object name = result.getObject(nameField);
			System.out.println(name);
		} catch (Exception e) {
			e.printStackTrace();
		}
[7 Mar 2008 16:27] Ramon Garcia
Suggested patch

Attachment: mysql.diff (application/octet-stream, text), 1.40 KiB.

[7 Mar 2008 16:32] Ramon Garcia
Ramón García Fernández
ramon.garcia@kotasoft.com
Kotasoft S.L.

Software developer
http://www.kotasoft.com
[10 Mar 2008 10:55] Tonci Grgin
Hi Ramon and thanks for your report. I believe this is a duplicate of Bug#31499.

"The behavior you ask for isn't actually JDBC-compliant (which is why there was a change in behavior in 5.1). If you want the old, non-compliant behavior, you should add "useOldAliasMetadataBehavior=true" as a configuration parameter in your JDBC URL. The documentation team has changed "Upgrading" section to mention
this change.
Note: The JDBC-compliant way of getting the information you're asking for, i.e. the "alias" for the column is by calling ResultSetMetaData.getColumnLabel(), not
getColumnName().
The reason that getColumnName() is _not_ supposed to return the alias, is because it is supposed to be possible for a consumer of this API to generate a DML statement based on the metadata present in the ResultSet and ResultSetMetaData, and thus getColumnName() should return the _physical_ column name, not the alias."
[10 Mar 2008 12:38] Ramon Garcia
Thanks for your quick replay.

So the bug is in Hibernate (a Java O-R mapping library), isn't it?

I will report to Hibernate project then.
[10 Mar 2008 12:45] Ramon Garcia
There might have been a misunderstanding.

This bug is not because getColumnName() returns the alias or the physical column name; this is not the issue. The issue is that the string returned by getColumnName() cannot be used a column name in the input to Result.getObject(columnName). In fact, the attached patch does not modify the output of getColumnName(): it arranges that result.getXXX(columnName) accepts as input the output of getColumnName().

The use case is a database tool that fetchs database metadata and uses that metadata to make queries to result.getObject(xxxx) to associate each field to each column name.

The descriptions in the Javadoc of getColumnName() and getColumnLabel():

String 	getColumnLabel(int column)
Gets the designated column's suggested title for use in printouts and displays.

String 	getColumnName(int column)
Get the designated column's name.

It does not look like one should use the result of getColumnLabel as input to result.getXXX(name).
[10 Mar 2008 12:55] Tonci Grgin
Ok Ramon. Reopening for new investigation.
[26 Mar 2008 12:30] Ramon Garcia
Have you found anything?
[26 Mar 2008 13:34] Tonci Grgin
Ramon, I must apologize, I was drawn away by other, more urgent tasks... Will do today.
[26 Mar 2008 14:38] Tonci Grgin
Ramon, verified as described. Thanks for your report and your interest in MySQL.
[26 Mar 2008 14:40] Tonci Grgin
Test case

Attachment: TestBug35150.java (text/x-java), 1.11 KiB.

[6 May 2008 2:09] Bill Culp
I dont know why the severity is marked as non-critical.  ResultSet.getMetaData is returning bogus col names when Aliases are used.  THe driver is essentially useless. Please bump this up.
[6 May 2008 6:30] Tonci Grgin
Bill, "Severity" field is a description of personal feeling of bug reporter, not MySQL staff. As it's personal we almost never touch it. As for my ruling, it's D2 (Serious).

I'll notify c/J team lead of this report now.
[28 May 2008 12:47] Tonci Grgin
Bug#37038 was marked as duplicate of this report.
[28 May 2008 13:19] Mark Matthews
The driver isn't returning "bogus" column names. The JDBC-4.0 specification states that ResultSet.get...(String), takes the column *label*, not the name. If you're writing spec-compliant software, you should be use ResultSetMetadata.getColumnLabel() to retrieve names to pass into ResultSet.get...(String).

We'll consider adding a non-compliant option that would also search the column *names* for ResultSet.findColumn(), which then will let you use column *names* for ResultSet.get...(String).
[28 May 2008 14:53] 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/47149
[7 Jul 2008 13:01] Tony Bedford
An entry has been added to the 5.1.6 Changelog:

The JDBC driver uses a different method for evaluating column names in resultsetmetadata.getColumnName() and when looking for a column in resultset.getObject(columnName). This causes Hibernate to fail in queries where the two methods yield different results, for example in queries that use alias names: 

SELECT column AS aliasName from table
[30 Jul 2008 14:53] 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/50724
[2 Oct 2009 8:17] tomaz majerhold
What about a alias of a table, how can access it?
[2 Oct 2009 13:44] Mark Matthews
@Tomaz,

ResultSetMetadata.getColumnLabel() exists for this purpose.