[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-5040) SQLQuery fails with auto-discovery of result set metadata and aliased columns
Richard Kennard (JIRA)
noreply at atlassian.com
Mon Mar 14 19:18:08 EDT 2011
[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-5040?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=40202#action_40202 ]
Richard Kennard commented on HHH-5040:
--------------------------------------
I would like to add some weight to this issue. It is quite bad as it can run *without failing*, and just return corrupted results. If you do:
createNativeQuery( "select a.id, b.id from Foo a, Foo b where a.id != b.id" )
Then 'performDiscovery' will use getColumnName() and choose 'id' for both aliases. Then later AbstractBynaryType will call rs.getBytes('id') twice and return the same byte array for both columns. So you end up with a List<Object[]> where Object[0] == Object[1].
Perhaps 'performDiscovery' could make use of the 'aliases' parameter that is passed in, to see if a given alias is already in use?
Richard
> 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