MySQL Bugs: #33966: Alias are ignored on a select when using a combination of Hibernate/mysql 5.1

Bug #33966	Alias are ignored on a select when using a combination of Hibernate/mysql 5.1
Submitted:	21 Jan 2008 21:12	Modified:	25 Jan 2008 16:14
Reporter:	lkajsdlkfj lkajsdlfksdjf	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.1.22	OS:	Windows (XP)
Assigned to:		CPU Architecture:	Any
Tags:	alias, hibernate

Description:
Running a query against a MySQL5.1.22 database and 5.1.5 driver from a program that consists of Hibernate and Spring framework, I discovered that the query ignores aliases in a select statement. When switching back to driver 5.0.4, but still running queries against 5.1.22 database, the problem disappears.

How to repeat:
How I tested:
i ran my program with a similar query to this, and with connector driver 5.1.5:
 SELECT proc.name as process_name
 FROM procs

Got error:
aused by: java.sql.SQLException: Column 'name' not found.
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1056)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
	at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1080)
	at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5474)
	at com.mchange.v2.c3p0.impl.NewProxyResultSet.getString(NewProxyResultSet.java:3342)
	at org.hibernate.type.StringType.get(StringType.java:18)

I then changed the driver back to 5.0.4 (I knew things were working before the upgrade), and sure enough, the query worked. It appears that the alias which i defined as "process_name" was ignored and confused Hibernate into thinking it's "name".

Suggested fix:
Not sure. I cannot tell if this is a problem with Hibernate not reading the metadata correctly, or problem with the driver.

Meet the same problem few days ago, and finally read from the driver's doc that it is not a bug : jdbc driver 5.0.4 is the last version before a change on aliased column name return by jdbc driver, this is the reason why it worked when you tried this older version (try 5.0.5 or > and you should have the same trouble as with the latest version).

To solve it, in your driver connection url, add the option "useOldAliasMetadataBehavior" with value "true" (before 5.0.5, it was the default value, which has been changed to "false" from 5.0.5) -> ...?useOldAliasMetadataBehavior=true

More precision about jdbc driver parameters at :
http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-configuration-properties.html

Hi Yev and thanks for your report. It is as Sébastien said, to get as close as possible to Java specs we introduced this change in behavior.

Sébastien, thanks for your help and thanks to both of you for using MySQL.

A better solution would be to use getColumnLabel().

According to the jdk api:

"Gets the designated column's suggested title for use in printouts and displays. The suggested title is usually specified by the SQL AS clause. If a SQL AS is not specified, the value returned from getColumnLabel will be the same as the value returned by the getColumnName method. (http://java.sun.com/javase/6/docs/api/java/sql/ResultSetMetaData.html) "

This seems like a more elegant solution as long as you have access to the middleware code calling this method (in the case of hibernate, you would need to wait until they fix their code).  This will make the code compatible with any version of the driver.