[hibernate-issues] [Hibernate-JIRA] Issue Comment Edited: (HHH-951) setMaxResults causes "ORA-00918: column ambiguously defined" exception
Grzegorz Olędzki (JIRA)
noreply at atlassian.com
Fri Apr 8 11:17:00 EDT 2011
[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-951?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=42004#action_42004 ]
Grzegorz Olędzki edited comment on HHH-951 at 4/8/11 10:15 AM:
---------------------------------------------------------------
I have just spent a few hours debugging what Hibernate does and the reasons for the problem. Let me sum things up and share my findings:
- Summary: a problem seems to happen on Oracle database when using {{INNER JOIN FETCH}}, a scalar in the {{SELECT}} clause and {{setMaxResults()}} at the same time. If one of these is omitted, everything seems to work. No error is thrown on other databases.
- I have been playing with a simple queries like:
{code}
SELECT b, 1 FROM Book b inner join fetch b.category // this one doesn't work
SELECT b FROM Book b inner join fetch b.category // this one works
{code}
being run with:
{code}
em.createQuery(queryString).setMaxResults(10).getResultList();
{code}
The outcome is {{Caused by: java.sql.SQLSyntaxErrorException: ORA-00918: column ambiguously defined}}.
- My knowledge about Hibernate internals is limited however I managed to learn something. For these who don't know what Hibernate does (Hibernate experts please correct me) when running a JPQL/HQL query, it parses and analyzes it, builds an AST (object representation) of the JPQL query, then something called {{HqlSqlWalker}} is called by {{QueryTranslatorImpl}} and they both are responsible for converting the AST representation of JPQL query into AST representation of SQL query, only then a String representation of such SQL AST is built and the query is sent to the database.
My findings are the problem happens someone near the {{HqlSqlWalker}}, that is when the HQL/JPQL AST is converted to SQL AST. This was my conclusion after observing no significant change between HQL/JPQL ASTs in case of these two queries:
{code}
\-[QUERY] Node: 'query'
\-[SELECT_FROM] Node: 'SELECT_FROM'
+-[FROM] Node: 'FROM'
| +-[RANGE] Node: 'RANGE'
| | +-[DOT] Node: '.'
| | | +-[IDENT] Node: 'hhh951'
| | | \-[IDENT] Node: 'Book'
| | \-[ALIAS] Node: 'b'
| \-[JOIN] Node: 'join'
| +-[INNER] Node: 'inner'
| +-[FETCH] Node: 'fetch'
| \-[DOT] Node: '.'
| +-[IDENT] Node: 'b'
| \-[IDENT] Node: 'category'
\-[SELECT] Node: 'SELECT'
\-[IDENT] Node: 'b'
{code}
vs
{code}
\-[QUERY] Node: 'query'
\-[SELECT_FROM] Node: 'SELECT_FROM'
+-[FROM] Node: 'FROM'
| +-[RANGE] Node: 'RANGE'
| | +-[DOT] Node: '.'
| | | +-[IDENT] Node: 'hhh951'
| | | \-[IDENT] Node: 'Book'
| | \-[ALIAS] Node: 'b'
| \-[JOIN] Node: 'join'
| +-[INNER] Node: 'inner'
| +-[FETCH] Node: 'fetch'
| \-[DOT] Node: '.'
| +-[IDENT] Node: 'b'
| \-[IDENT] Node: 'category'
\-[SELECT] Node: 'SELECT'
+-[IDENT] Node: 'b'
\-[NUM_INT] Node: '1'
{code}
However the generated SQL ASTs were importantly different:
{code}
\-[SELECT] QueryNode: 'SELECT' querySpaces (Book,Category)
+-[SELECT_CLAUSE] SelectClause: '{select clause}'
| +-[ALIAS_REF] IdentNode: 'book0_.id as id0_0_' {alias=b, className=hhh951.Book, tableAlias=book0_}
| +-[SELECT_EXPR] SelectExpressionImpl: 'category1_.id as id1_1_' {FromElement{explicit,not a collection join,fetch join,fetch non-lazy properties,classAlias=null,role=null,tableName=Category,tableAlias=category1_,origin=Book book0_,columns={book0_.categoryId ,className=hhh951.Category}}}
| +-[SQL_TOKEN] SqlFragment: 'book0_.categoryId as categoryId0_0_, book0_.title as title0_0_'
| \-[SQL_TOKEN] SqlFragment: 'category1_.name as name1_1_'
\-[FROM] FromClause: 'FROM' FromClause{level=1, fromElementCounter=2, fromElements=2, fromElementByClassAlias=[b], fromElementByTableAlias=[book0_, category1_], fromElementsByPath=[b.category], collectionJoinFromElementsByPath=[], impliedElements=[]}
\-[FROM_FRAGMENT] FromElement: 'Book book0_' FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=b,role=null,tableName=Book,tableAlias=book0_,origin=null,columns={,className=hhh951.Book}}
\-[JOIN_FRAGMENT] FromElement: 'inner join Category category1_ on book0_.categoryId=category1_.id' FromElement{explicit,not a collection join,fetch join,fetch non-lazy properties,classAlias=null,role=null,tableName=Category,tableAlias=category1_,origin=Book book0_,columns={book0_.categoryId ,className=hhh951.Category}}
{code}
vs
{code}
\-[SELECT] QueryNode: 'SELECT' querySpaces (Book,Category)
+-[SELECT_CLAUSE] SelectClause: '{select clause}'
| +-[ALIAS_REF] IdentNode: 'book0_.id as col_0_0_' {alias=b, className=hhh951.Book, tableAlias=book0_}
| +-[NUM_INT] LiteralNode: '1'
| +-[SELECT_COLUMNS] SqlNode: ' as col_1_0_'
| +-[SELECT_EXPR] SelectExpressionImpl: 'category1_.id as id1_1_' {FromElement{explicit,not a collection join,fetch join,fetch non-lazy properties,classAlias=null,role=null,tableName=Category,tableAlias=category1_,origin=Book book0_,columns={book0_.categoryId ,className=hhh951.Category}}}
| +-[SQL_TOKEN] SqlFragment: 'book0_.id as id0_0_'
| +-[SQL_TOKEN] SqlFragment: 'category1_.id as id1_1_'
| +-[SQL_TOKEN] SqlFragment: 'book0_.categoryId as categoryId0_0_, book0_.title as title0_0_'
| \-[SQL_TOKEN] SqlFragment: 'category1_.name as name1_1_'
\-[FROM] FromClause: 'FROM' FromClause{level=1, fromElementCounter=2, fromElements=2, fromElementByClassAlias=[b], fromElementByTableAlias=[book0_, category1_], fromElementsByPath=[b.category], collectionJoinFromElementsByPath=[], impliedElements=[]}
\-[FROM_FRAGMENT] FromElement: 'Book book0_' FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=b,role=null,tableName=Book,tableAlias=book0_,origin=null,columns={,className=hhh951.Book}}
\-[JOIN_FRAGMENT] FromElement: 'inner join Category category1_ on book0_.categoryId=category1_.id' FromElement{explicit,not a collection join,fetch join,fetch non-lazy properties,classAlias=null,role=null,tableName=Category,tableAlias=category1_,origin=Book book0_,columns={book0_.categoryId ,className=hhh951.Category}}
{code}
Notice the {{id1_1_}} which happens twice in case of the second query. This is the main cause of the problem in SQL. Oracle seems to be intolerant for such duplication (and only in case of paging which is implemented with {{SELECT * FROM (....) WHERE ROWNUM <= 10}}). The SQL generated for other databases seems exactly the same but they seem more tolerant.
Let's focus on these three lines (actually fragments of SQL generated by {{HqlSqlWalker}}):
{code}
| +-[SELECT_EXPR] SelectExpressionImpl: 'category1_.id as id1_1_' {FromElement{explicit,not a collection join,fetch join,fetch non-lazy properties,classAlias=null,role=null,tableName=Category,tableAlias=category1_,origin=Book book0_,columns={book0_.categoryId ,className=hhh951.Category}}}
| +-[SQL_TOKEN] SqlFragment: 'book0_.id as id0_0_'
| +-[SQL_TOKEN] SqlFragment: 'category1_.id as id1_1_'
{code}
I have been trying to find out what's the reason for generating the {{category1_.id as id1_1_}} section twice. Here is where the parts are generated:
- The first one ({{[SELECT_EXPR] SelectExpressionImpl: 'category1_.id as id1_1_'}}) is generated by [{{SelectClause.java:223}} being part of {{initializeExplicitSelectClause()}}|https://github.com/hibernate/hibernate-core/blob/3.6.3.Final/hibernate-core/src/main/java/org/hibernate/hql/ast/tree/SelectClause.java#L223]
- The second one and third one are generated in other method called {{renderNonScalarIdentifiers()}} of the same class [{{SelectClause.java:433}}|https://github.com/hibernate/hibernate-core/blob/3.6.3.Final/hibernate-core/src/main/java/org/hibernate/hql/ast/tree/SelectClause.java#L433]
Notice the control goes into the last method only if there's any scalar in the {{SELECT}} clause in the query ({{,1}} in my example). (This seems another thing I would suggest looking into. Why does the fact that one of the SELECT elements being a scalar, makes all the elements processed differently? Notice that the guards are {{!isScalarSelect(}} and not {{fromExpression.isScalar()}}).
My idea was to make the code avoid adding the same SQL fragment twice. That's why my solution (actually I would say it's more a workaround) was to add a {{Set alreadyAppendedTexts}} and to check for duplicates before any append is done. Basically each:
{code}
appender.append( SqlTokenTypes.SQL_TOKEN, text, false );
{code}
has been replaced with:
{code}
if (!alreadyAppendedTexts.contains(text)) {
appender.append( SqlTokenTypes.SQL_TOKEN, text, false );
}
alreadyAppendedTexts.add(text);
{code}
I am not 100% convinced this is a correct change, because as I saw in the code the texts of generated SQL fragments are sometimes being recalculated. So even if a check is done when appending a SQL fragment to output (SQL) AST that doesn't guarantee no part of code wouldn't change an existing SQL fragment to something that would be a duplicate.
Either way, the change works for me. I attach my patch against 3.5.3 (should I be doing this differently using github?) and a test Eclipse project I have used for debugging purposes.
*Would any Hibernate-insider be so kind to look into this and share his/her opinion?*
was (Author: grzegorz.oledzki):
I have just spent a few hours debugging what Hibernate does and the reasons for the problem. Let me sum things up and share my findings:
- Summary: a problem seems to happen on Oracle database when using {{INNER JOIN FETCH}}, a scalar in the {{SELECT}} clause and {{setMaxResults()}} at the same time. If one of these is omitted, everything seems to work. No error is thrown on other databases.
- I have been playing with a simple queries like:
{code}
SELECT b, 1 FROM Book b inner join fetch b.category // this one doesn't work
SELECT b FROM Book b inner join fetch b.category // this one works
{code}
being run with:
{code}
em.createQuery(queryString).setMaxResults(10).getResultList();
{code}
The outcome is {{Caused by: java.sql.SQLSyntaxErrorException: ORA-00918: column ambiguously defined}}.
- My knowledge about Hibernate internals is limited however I managed to learn something. For these who don't know what Hibernate does (Hibernate experts please correct me) when running a JPQL/HQL query, it parses and analyzes it, builds an AST (object representation) of the JPQL query, then something called {{HqlSqlWalker}} is called by {{QueryTranslatorImpl}} and they both are responsible for converting the AST representation of JPQL query into AST representation of SQL query, only then a String representation of such SQL AST is built and the query is sent to the database.
My findings are the problem happens someone near the {{HqlSqlWalker}}, that is when the HQL/JPQL AST is converted to SQL AST. This was my conclusion after observing no significant change between HQL/JPQL ASTs in case of these two queries:
{code}
\-[QUERY] Node: 'query'
\-[SELECT_FROM] Node: 'SELECT_FROM'
+-[FROM] Node: 'FROM'
| +-[RANGE] Node: 'RANGE'
| | +-[DOT] Node: '.'
| | | +-[IDENT] Node: 'hhh951'
| | | \-[IDENT] Node: 'Book'
| | \-[ALIAS] Node: 'b'
| \-[JOIN] Node: 'join'
| +-[INNER] Node: 'inner'
| +-[FETCH] Node: 'fetch'
| \-[DOT] Node: '.'
| +-[IDENT] Node: 'b'
| \-[IDENT] Node: 'category'
\-[SELECT] Node: 'SELECT'
\-[IDENT] Node: 'b'
{code}
vs
{code}
\-[QUERY] Node: 'query'
\-[SELECT_FROM] Node: 'SELECT_FROM'
+-[FROM] Node: 'FROM'
| +-[RANGE] Node: 'RANGE'
| | +-[DOT] Node: '.'
| | | +-[IDENT] Node: 'hhh951'
| | | \-[IDENT] Node: 'Book'
| | \-[ALIAS] Node: 'b'
| \-[JOIN] Node: 'join'
| +-[INNER] Node: 'inner'
| +-[FETCH] Node: 'fetch'
| \-[DOT] Node: '.'
| +-[IDENT] Node: 'b'
| \-[IDENT] Node: 'category'
\-[SELECT] Node: 'SELECT'
+-[IDENT] Node: 'b'
\-[NUM_INT] Node: '1'
{code}
However the generated SQL ASTs were importantly different:
{code}
\-[SELECT] QueryNode: 'SELECT' querySpaces (Book,Category)
+-[SELECT_CLAUSE] SelectClause: '{select clause}'
| +-[ALIAS_REF] IdentNode: 'book0_.id as id0_0_' {alias=b, className=hhh951.Book, tableAlias=book0_}
| +-[SELECT_EXPR] SelectExpressionImpl: 'category1_.id as id1_1_' {FromElement{explicit,not a collection join,fetch join,fetch non-lazy properties,classAlias=null,role=null,tableName=Category,tableAlias=category1_,origin=Book book0_,columns={book0_.categoryId ,className=hhh951.Category}}}
| +-[SQL_TOKEN] SqlFragment: 'book0_.categoryId as categoryId0_0_, book0_.title as title0_0_'
| \-[SQL_TOKEN] SqlFragment: 'category1_.name as name1_1_'
\-[FROM] FromClause: 'FROM' FromClause{level=1, fromElementCounter=2, fromElements=2, fromElementByClassAlias=[b], fromElementByTableAlias=[book0_, category1_], fromElementsByPath=[b.category], collectionJoinFromElementsByPath=[], impliedElements=[]}
\-[FROM_FRAGMENT] FromElement: 'Book book0_' FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=b,role=null,tableName=Book,tableAlias=book0_,origin=null,columns={,className=hhh951.Book}}
\-[JOIN_FRAGMENT] FromElement: 'inner join Category category1_ on book0_.categoryId=category1_.id' FromElement{explicit,not a collection join,fetch join,fetch non-lazy properties,classAlias=null,role=null,tableName=Category,tableAlias=category1_,origin=Book book0_,columns={book0_.categoryId ,className=hhh951.Category}}
{code}
vs
{code}
\-[SELECT] QueryNode: 'SELECT' querySpaces (Book,Category)
+-[SELECT_CLAUSE] SelectClause: '{select clause}'
| +-[ALIAS_REF] IdentNode: 'book0_.id as col_0_0_' {alias=b, className=hhh951.Book, tableAlias=book0_}
| +-[NUM_INT] LiteralNode: '1'
| +-[SELECT_COLUMNS] SqlNode: ' as col_1_0_'
| +-[SELECT_EXPR] SelectExpressionImpl: 'category1_.id as id1_1_' {FromElement{explicit,not a collection join,fetch join,fetch non-lazy properties,classAlias=null,role=null,tableName=Category,tableAlias=category1_,origin=Book book0_,columns={book0_.categoryId ,className=hhh951.Category}}}
| +-[SQL_TOKEN] SqlFragment: 'book0_.id as id0_0_'
| +-[SQL_TOKEN] SqlFragment: 'category1_.id as id1_1_'
| +-[SQL_TOKEN] SqlFragment: 'book0_.categoryId as categoryId0_0_, book0_.title as title0_0_'
| \-[SQL_TOKEN] SqlFragment: 'category1_.name as name1_1_'
\-[FROM] FromClause: 'FROM' FromClause{level=1, fromElementCounter=2, fromElements=2, fromElementByClassAlias=[b], fromElementByTableAlias=[book0_, category1_], fromElementsByPath=[b.category], collectionJoinFromElementsByPath=[], impliedElements=[]}
\-[FROM_FRAGMENT] FromElement: 'Book book0_' FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=b,role=null,tableName=Book,tableAlias=book0_,origin=null,columns={,className=hhh951.Book}}
\-[JOIN_FRAGMENT] FromElement: 'inner join Category category1_ on book0_.categoryId=category1_.id' FromElement{explicit,not a collection join,fetch join,fetch non-lazy properties,classAlias=null,role=null,tableName=Category,tableAlias=category1_,origin=Book book0_,columns={book0_.categoryId ,className=hhh951.Category}}
{code}
Notice the {{id1_1_}} which happens twice in case of the second query. This is the main cause of the problem in SQL. Oracle seems to be intolerant for such duplication (and only in case of paging which is implemented with {{SELECT * FROM (....) WHERE ROWNUM <= 10}}). The SQL generated for other databases seems exactly the same but they seem more tolerant.
Let's focus on these three lines (actually fragments of SQL generated by {{HqlSqlWalker}}):
{code}
| +-[SELECT_EXPR] SelectExpressionImpl: 'category1_.id as id1_1_' {FromElement{explicit,not a collection join,fetch join,fetch non-lazy properties,classAlias=null,role=null,tableName=Category,tableAlias=category1_,origin=Book book0_,columns={book0_.categoryId ,className=hhh951.Category}}}
| +-[SQL_TOKEN] SqlFragment: 'book0_.id as id0_0_'
| +-[SQL_TOKEN] SqlFragment: 'category1_.id as id1_1_'
{code}
I have been trying to find out what's the reason for generating the {{category1_.id as id1_1_}} section twice. Here is where the parts are generated:
- The first one ({{[SELECT_EXPR] SelectExpressionImpl: 'category1_.id as id1_1_'}}) is generated by [{{SelectClause.java:223}} being part of {{initializeExplicitSelectClause()}}|https://github.com/hibernate/hibernate-core/blob/3.6.3.Final/hibernate-core/src/main/java/org/hibernate/hql/ast/tree/SelectClause.java#L223]
- The second one and third one are generated in other method called {{renderNonScalarIdentifiers()}} of the same class [{{SelectClause.java:433}}|https://github.com/hibernate/hibernate-core/blob/3.6.3.Final/hibernate-core/src/main/java/org/hibernate/hql/ast/tree/SelectClause.java#L433]
Notice the control goes into the last method only if there's any scalar in the {{SELECT}} clause in the query ({{,1}} in my example). (This seems another thing I would suggest looking into. Why does the fact that one of the SELECT elements being a scalar, makes all the elements processed differently? Notice that the guards are {{!isScalarSelect(}} and not {{fromExpression.isScalar()}}).
My idea was to make the code avoid adding the same SQL fragment twice. That's my solution (actually I would say it's more a workaround) is to add a {{Set alreadyAppendedTexts}} and to check for duplicates before any append is done. Basically each:
{code}
appender.append( SqlTokenTypes.SQL_TOKEN, text, false );
{code}
has been replaced with:
{code}
if (!alreadyAppendedTexts.contains(text)) {
appender.append( SqlTokenTypes.SQL_TOKEN, text, false );
}
alreadyAppendedTexts.add(text);
{code}
I am not 100% convinced this is a correct change, because as I saw in the code the texts of generated SQL fragments are sometimes being recalculated. So even if a check is done when appending a SQL fragment to output (SQL) AST that doesn't guarantee no part of code wouldn't change an existing SQL fragment to something that would be a duplicate.
Either way, the change works for me. I attach my patch against 3.5.3 (should I be doing this differently using github?) and a test Eclipse project I have used for debugging purposes.
*Would any Hibernate-insider be so kind to look into this and share his/her opinion?*
> setMaxResults causes "ORA-00918: column ambiguously defined" exception
> -----------------------------------------------------------------------
>
> Key: HHH-951
> URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-951
> Project: Hibernate Core
> Issue Type: Bug
> Components: core
> Affects Versions: 3.0.5, 3.1 beta 2, 3.5.3, 3.6.1
> Environment: hibernate3.0.5, hibernate3.1b2, Oracle 9
> Reporter: Karel Sommer
> Attachments: HHH951Project.zip, Hibernate_SQL_HHH-5579.txt, Hibernate_StackTrace_HHH-5579.txt, patch.951, TestCase_for_Hibernate_Core_Issue_HHH-5579.zip
>
>
> when create criteria with associations, i get this error:
> ORA-00918: column ambiguously defined
> mapping:
> <class name="User" table="FRAME_USER" dynamic-update="true" dynamic-insert="true">
> <id name="id" type="long" unsaved-value="null">
> <column name="ID" not-null="true"/>
> <generator class="sequence">
> <param name="sequence">frame_user_seq</param>
> </generator>
> </id>
> <version type="timestamp" column="stamp" name="timestamp" unsaved-value="null"/>
> <property name="user_name" type="string" not-null="true"/>
> <property name="blocked" type="yes_no" not-null="true"/>
> <property name="access_logon" type="timestamp"/>
> <property name="denied_logon" type="timestamp"/>
> <property name="inactivity_time" type="long"/>
> <property name="session_count" type="long"/>
> <idbag name="terminalGroups" table="FRAME_USER_TERMINAL" fetch="join" outer-join="true">
> <collection-id column="ID" type="long">
> <generator class="sequence">
> <param name="sequence">frame_user_terminal_seq</param>
> </generator>
> </collection-id>
> <key column="id_user"/>
> <many-to-many column="id_terminal_groups" class="TerminalGroup" fetch="join" outer-join="true"/>
> </idbag>
> </class>
> <class name="TerminalGroup" table="FRAME_TERMINAL_GROUPS" dynamic-update="true" dynamic-insert="true">
> <id name="id" type="long" unsaved-value="null">
> <column name="ID" not-null="true"/>
> <generator class="sequence">
> <param name="sequence">frame_terminal_groups_seq</param>
> </generator>
> </id>
> <version type="timestamp" column="stamp" name="timestamp" unsaved-value="null"/>
> <property name="name" column="group_name" type="string" not-null="true"/>
> <idbag name="terminals" table="FRAME_TERMINAL_REL" fetch="join" outer-join="true">
> <collection-id column="ID" type="long" >
> <generator class="sequence">
> <param name="sequence">frame_terminal_rel_seq</param>
> </generator>
> </collection-id>
> <key column="id_term_group"/>
> <many-to-many column="id_term" class="Terminal" fetch="join" outer-join="true"/>
> </idbag>
> </class>
> <class name="Terminal" table="FRAME_TERMINAL" where="status != 'D'" dynamic-update="true" dynamic-insert="true">
> <id name="id" type="long" unsaved-value="null">
> <column name="ID" not-null="true"/>
> <generator class="sequence">
> <param name="sequence">frame_terminal_seq</param>
> </generator>
> </id>
> <version type="timestamp" column="stamp" name="timestamp" unsaved-value="null"/>
> <property name="status" type="char" not-null="true"/>
> <property name="mac" type="string" not-null="true"/>
> <property name="name" column="ident" type="string" not-null="true"/>
> <property name="description" type="string"/>
> code:
> session.createCriteria(User.class)
> .add(Restrictions.like("user_name", "%")
> .createCriteria("terminalGroups")
> .add( Restrictions.like("group_name", "%").
> .setProjection(Projections.rowCount())
> .uniqueResult();
--
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