7:48 a.m.
SQLQuery fails with auto-discovery of result set metadata and aliased columns
-----------------------------------------------------------------------------
Key: HHH-5040
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-5040
Project: Hibernate Core
Issue Type: Bug
Components: core
Affects Versions: 3.3.2
Environment: 3.3.2 GA, MySQL Connector/J 5.0.
Reporter: Chris Wilson
Attachments: HibernateSqlQueryAliasTest.java
Hibernate assumes that the column name returned by ResultSetMetaData#getColumnName(int) is
the name it can use to extract the data from the ResultSet. However, if the SQLQuery uses
column aliases, then this will fail here:
{code|title=Stack Trace}
org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.loader.Loader.doList(Loader.java:2235)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2129)
at org.hibernate.loader.Loader.list(Loader.java:2124)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:312)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1723)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:175)
at
org.wfp.rita.test.hibernate.HibernateSqlQueryAliasTest.testFailingWithAliases(HibernateSqlQueryAliasTest.java:45)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.wfp.rita.test.base.HibernateTestBase.runTestMethod(HibernateTestBase.java:204)
at org.wfp.rita.test.base.HibernateTestBase.runTest(HibernateTestBase.java:117)
at junit.framework.TestCase.runBare(TestCase.java:130)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:120)
at junit.framework.TestSuite.runTest(TestSuite.java:230)
at junit.framework.TestSuite.run(TestSuite.java:225)
at
org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(JUnit3TestReference.java:130)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
Caused by: java.sql.SQLException: Column 'id' not found.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929)
at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1145)
at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2814)
at org.hibernate.type.IntegerType.get(IntegerType.java:51)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:184)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:210)
at
org.hibernate.loader.custom.CustomLoader$ScalarResultColumnProcessor.extract(CustomLoader.java:497)
at
org.hibernate.loader.custom.CustomLoader$ResultRowProcessor.buildResultRow(CustomLoader.java:443)
at
org.hibernate.loader.custom.CustomLoader.getResultColumnOrRow(CustomLoader.java:340)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:629)
at org.hibernate.loader.Loader.doQuery(Loader.java:724)
at
org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
at org.hibernate.loader.Loader.doList(Loader.java:2232)
... 26 more
{code}
Hibernate appears to support auto-discovery of result set columns, notwithstanding bug
[HHH-436|http://opensource.atlassian.com/projects/hibernate/browse/HHH-436] which was
closed as fixed. No QueryException is thrown if the returns of the query have not been
defined.
Instead, SQLQueryImpl.verifyParameters() sets SQLQueryImpl.autodiscovertypes, which ends
up in
QueryParameters.autodiscovertypes, which CustomLoader.doQuery() passes to
CustomLoader.getResultSet(), which then calls CustomLoader.autoDiscoverTypes(), which
calls
ScalarResultColumnProcessor.performDiscovery().
performDiscovery() calls ResultSetMetaData#getColumnName() to retrieve each column name
from the result set, which ends up in CustomLoader.ResultRowProcessor, where
ScalarResultColumnProcessor.extract() tries to use it to extract data from the result set.
Because the name doesn't match the actual alias used in the result set, this fails
with the exception given above.
[JDBC 4.0 Specification|http://jcp.org/aboutJava/communityprocess/final/jsr221/inde...]
does not specify whether ResultSetMetaData.getColumnName() should return the name of the
underlying column, or the name of the alias. It seems bizarre to me that it returns a
value that cannot be passed to ResultSet.getObject(String). However, both MySQL and H2
take the position that we should call ResultSetMetaData.getColumnLabel() instead to get
the name that can be used on the ResultSet:
* [MySQL bug 21379|http://bugs.mysql.com/bug.php?id=21379]
* [MySQL bug 21596|http://bugs.mysql.com/bug.php?id=21596]
* [H2 mailing list
discussion|http://www.mail-archive.com/h2-database@googlegroups.com/msg00...]
The fix would appear to be modifying ScalarResultColumnProcessor.performDiscovery() (or
CustomLoader.Metadata.getColumnName()) so that it calls getColumnLabel() instead of
getColumnName(). The workaround is to explicitly specify column aliases with
SQLQuery.addScalar().
Test case attached.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira