[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-5040) SQLQuery fails with auto-discovery of result set metadata and aliased columns

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.