MySQL Bugs: #35150: Inconsistency in column name causes Hibernate to fail.

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

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();
		}

Suggested patch

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

Ramón García Fernández
ramon.garcia@kotasoft.com
Kotasoft S.L.

Software developer
http://www.kotasoft.com

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."

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.

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).

Ok Ramon. Reopening for new investigation.

Have you found anything?

Ramon, I must apologize, I was drawn away by other, more urgent tasks... Will do today.

Ramon, verified as described. Thanks for your report and your interest in MySQL.

Test case

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

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.

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.

Bug#37038 was marked as duplicate of this report.

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).

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

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

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

What about a alias of a table, how can access it?

@Tomaz,

ResultSetMetadata.getColumnLabel() exists for this purpose.