[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-5040) SQLQuery fails with auto-discovery of result set metadata and aliased columns
Fred Toussi (JIRA)
noreply at atlassian.com
Fri Jun 25 09:49:09 EDT 2010
[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-5040?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=37581#action_37581 ]
Fred Toussi commented on HHH-5040:
----------------------------------
JDBC 4 clarifies to some extent the distinction between getColumnName() and getColumnLabel() in the JavaDoc for ResultSetMetaData.getColumnLabel() which states the method returns the label defined by the "AS" clause (which implies any label, even without the use of "AS", which is optional in SQL).
Variations can be tested with HSQLDB, which has a connection property "get_column_name={true|false}". When false, getColumnName() returns the same value as getColumnLabel(), otherwise it returns the name of the table column while getColumnLabel() always returns the AS label if one is defined, otherwise defaults to getColumnName().
> 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/index.html] 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/msg00876.html]
> 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.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
More information about the hibernate-issues
mailing list