Greetings, all,
I'm having a problem where the HQL and the SQL generated from it have the parameters in a different order, and the parameters get inserted into the SQL in the HQL order. The problem persists between HQL and MySQL (InnoDB) and also between annotations driven mapping and hand coded hbm.xml files.
I have a domain model that involves 'Assoc's which have a 'Topic' field called 'type' as well as a list of 'Topic's called 'constituents'.
The HQL is:
Code:
from Assoc a where a.type.oid = ? and a.constituents[?].oid = ?
So the order in the HQL is (type.oid, index, constituent.oid).
The generated SQL is:
Code:
select
reorderedp0_.oid as oid1_,
reorderedp0_.type_oid as type2_1_
from
Assoc reorderedp0_,
Assoc_Topic constituen1_,
Topic reorderedp2_
where
reorderedp0_.oid=constituen1_.Assoc_oid
and constituen1_.topic_index = ?
and constituen1_.constituents_oid=reorderedp2_.oid
and reorderedp0_.type_oid=?
and reorderedp2_.oid=?
So the order in the SQL is (index, type.oid, constituent.oid).
Using annotations, I managed to fit all of the code into one class, so I could easily post that if anyone wants to get their hands on a test case quickly.
Any ideas?
Cheers,
Ben.
Hibernate version: 3.2.5.ga
Mapping documents:Code:
package com.finantix;
public class ReorderedParameters {
@Entity
public static class Assoc {
private Long oid;
private Topic type;
private List<Topic> constituents;
public Assoc() { }
public Assoc(Topic type, List<Topic> constituents) {
this.type = type;
this.constituents = constituents;
}
@ManyToMany @IndexColumn(name = "topic_index", nullable = false, base = 0)
public List<Topic> getConstituents() { return constituents; }
@Id @GeneratedValue(strategy = GenerationType.AUTO)
public Long getOid() { return oid; }
@ManyToOne(optional = false)
public Topic getType() { return type; }
public void setConstituents(List<Topic> constituents) { this.constituents = constituents; }
public void setOid(Long oid) { this.oid = oid; }
public void setType(Topic type) { this.type = type; }
}
@Entity
public static class Topic {
private Long oid;
private String name;
public Topic() { }
public Topic(String name) { this.name = name; }
@Column(name = "name", length = 255)
public String getName() { return name; }
@Id @GeneratedValue(strategy = GenerationType.AUTO)
public Long getOid() { return oid; }
public void setName(String name) { this.name = name; }
public void setOid(Long oid) { this.oid = oid; }
}
}
Code between sessionFactory.openSession() and session.close():Code:
final Query query = session.createQuery("" + //
"from com.finantix.ReorderedParameters$Assoc a" //
+ " where a.type.oid = ?" //
+ " and a.constituents[?].oid = ?");
query.setParameter(0, type.getOid());
query.setParameter(1, Integer.valueOf(position));
query.setParameter(2, topic.getOid());
query.list();
Full stack trace of any exception that occurs:Name and version of the database you are using:H2 1.0.20070304
The generated SQL (show_sql=true):Code:
Hibernate:
/*
from
com.finantix.ReorderedParameters$Assoc a
where
a.type.oid = ?
and a.constituents[?].oid = ? */
select
reorderedp0_.oid as oid1_,
reorderedp0_.type_oid as type2_1_
from
Assoc reorderedp0_,
Assoc_Topic constituen1_,
Topic reorderedp2_
where
reorderedp0_.oid=constituen1_.Assoc_oid
and constituen1_.topic_index = ?
and constituen1_.constituents_oid=reorderedp2_.oid
and reorderedp0_.type_oid=?
and reorderedp2_.oid=?
Debug level Hibernate log excerpt:Code:
21:59:25,607 DEBUG QueryTranslatorImpl:246 - parse() - HQL: from com.finantix.ReorderedParameters$Assoc a where a.type.oid = ? and a.constituents[?].oid = ?
21:59:25,610 DEBUG AST:266 - --- HQL AST ---
\-[QUERY] 'query'
+-[SELECT_FROM] 'SELECT_FROM'
| \-[FROM] 'from'
| \-[RANGE] 'RANGE'
| +-[DOT] '.'
| | +-[DOT] '.'
| | | +-[IDENT] 'com'
| | | \-[IDENT] 'finantix'
| | \-[IDENT] 'ReorderedParameters$Assoc'
| \-[ALIAS] 'a'
\-[WHERE] 'where'
\-[AND] 'and'
+-[EQ] '='
| +-[DOT] '.'
| | +-[DOT] '.'
| | | +-[IDENT] 'a'
| | | \-[IDENT] 'type'
| | \-[IDENT] 'oid'
| \-[PARAM] '?'
\-[EQ] '='
+-[DOT] '.'
| +-[INDEX_OP] '['
| | +-[DOT] '.'
| | | +-[IDENT] 'a'
| | | \-[IDENT] 'constituents'
| | \-[PARAM] '?'
| \-[IDENT] 'oid'
\-[PARAM] '?'
21:59:25,611 DEBUG ErrorCounter:68 - throwQueryException() : no errors
21:59:25,611 DEBUG HqlSqlBaseWalker:111 - select << begin [level=1, statement=select]
21:59:25,611 DEBUG FromElement:108 - FromClause{level=1} : com.finantix.ReorderedParameters$Assoc (a) -> reorderedp0_
21:59:25,612 DEBUG FromReferenceNode:51 - Resolved : a -> reorderedp0_.oid
21:59:25,613 DEBUG FromElement:525 - handling property dereference [com.finantix.ReorderedParameters$Assoc (a) -> type (class)]
21:59:25,613 DEBUG DotNode:568 - getDataType() : type -> org.hibernate.type.ManyToOneType(com.finantix.ReorderedParameters$Topic)
21:59:25,614 DEBUG DotNode:525 - dereferenceShortcut() : property oid in com.finantix.ReorderedParameters$Assoc does not require a join.
21:59:25,619 DEBUG DotNode:549 - Unresolved property path is now 'type.oid'
21:59:25,620 DEBUG FromReferenceNode:51 - Resolved : a.type -> reorderedp0_.type_oid
21:59:25,620 DEBUG FromElement:525 - handling property dereference [com.finantix.ReorderedParameters$Assoc (a) -> oid (class)]
21:59:25,621 DEBUG DotNode:568 - getDataType() : type.oid -> org.hibernate.type.LongType@29cda59b
21:59:25,621 DEBUG FromReferenceNode:51 - Resolved : a.type.oid -> reorderedp0_.type_oid
21:59:25,622 DEBUG FromReferenceNode:51 - Resolved : a -> reorderedp0_.oid
21:59:25,623 DEBUG FromElement:525 - handling property dereference [com.finantix.ReorderedParameters$Assoc (a) -> constituents (class)]
21:59:25,623 DEBUG DotNode:568 - getDataType() : constituents -> org.hibernate.type.ListType(com.finantix.ReorderedParameters$Assoc.constituents)
21:59:25,624 DEBUG FromElementFactory:368 - createManyToMany() : path = a.constituents role = com.finantix.ReorderedParameters$Assoc.constituents associatedEntityName = com.finantix.ReorderedParameters$Topic
21:59:25,626 DEBUG FromElement:108 - FromClause{level=1} : com.finantix.ReorderedParameters$Topic (no alias) -> constituen1_
21:59:25,627 DEBUG FromClause:233 - addJoinByPathMap() : a.constituents -> Topic constituen1_
21:59:25,627 DEBUG DotNode:284 - dereferenceCollection() : Created new FROM element for a.constituents : Assoc_Topic constituen1_
21:59:25,628 DEBUG FromElement:108 - FromClause{level=1} : null (no alias) -> null
21:59:25,628 DEBUG FromClause:323 - addCollectionJoinFromElementByPath() : a.constituents -> Assoc_Topic
21:59:25,628 DEBUG IndexNode:86 - No FROM element found for the elements of collection join path a.constituents, created Assoc_Topic
21:59:25,629 DEBUG FromReferenceNode:51 - Resolved : [ -> constituen1_.constituents_oid
21:59:25,629 DEBUG IndexNode:43 - Creating join for many-to-many elements for a.constituents[].oid
21:59:25,630 DEBUG FromElement:108 - FromClause{level=1} : com.finantix.ReorderedParameters$Topic (no alias) -> reorderedp2_
21:59:25,630 DEBUG FromClause:323 - addCollectionJoinFromElementByPath() : a.constituents[].oid -> Topic reorderedp2_
21:59:25,631 DEBUG FromClause:233 - addJoinByPathMap() : a.constituents[].oid -> Topic reorderedp2_
21:59:25,631 DEBUG DotNode:568 - getDataType() : oid -> org.hibernate.type.LongType@29cda59b
21:59:25,631 DEBUG FromReferenceNode:51 - Resolved : [.oid -> reorderedp2_.oid
21:59:25,632 DEBUG HqlSqlBaseWalker:117 - select : finishing up [level=1, statement=select]
21:59:25,632 DEBUG HqlSqlWalker:509 - processQuery() : ( SELECT ( FromClause{level=1} Assoc reorderedp0_ Assoc_Topic Topic reorderedp2_ ) ( where ( and ( = ( reorderedp0_.type_oid ( reorderedp0_.type_oid reorderedp0_.oid type ) oid ) ? ) ( = ( reorderedp2_.oid ( constituen1_.constituents_oid ( . reorderedp0_.oid constituents ) ? ) oid ) ? ) ) ) )
21:59:25,632 DEBUG HqlSqlWalker:716 - Derived SELECT clause created.
21:59:25,633 DEBUG JoinProcessor:148 - Using FROM fragment [Assoc reorderedp0_]
21:59:25,633 DEBUG SyntheticAndFactory:58 - Using WHERE fragment [reorderedp0_.oid=constituen1_.Assoc_oid and constituen1_.topic_index = ?]
21:59:25,633 DEBUG JoinProcessor:148 - Using FROM fragment [Assoc_Topic constituen1_]
21:59:25,634 DEBUG JoinProcessor:148 - Using FROM fragment [Topic reorderedp2_]
21:59:25,634 DEBUG SyntheticAndFactory:58 - Using WHERE fragment [constituen1_.constituents_oid=reorderedp2_.oid]
21:59:25,634 DEBUG HqlSqlBaseWalker:123 - select >> end [level=1, statement=select]
21:59:25,637 DEBUG AST:232 - --- SQL AST ---
\-[SELECT] QueryNode: 'SELECT' querySpaces (Topic,Assoc_Topic,Assoc)
+-[SELECT_CLAUSE] SelectClause: '{derived select clause}'
| +-[SELECT_EXPR] SelectExpressionImpl: 'reorderedp0_.oid as oid1_' {FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=a,role=null,tableName=Assoc,tableAlias=reorderedp0_,origin=null,colums={,className=com.finantix.ReorderedParameters$Assoc}}}
| \-[SQL_TOKEN] SqlFragment: 'reorderedp0_.type_oid as type2_1_'
+-[FROM] FromClause: 'from' FromClause{level=1, fromElementCounter=3, fromElements=4, fromElementByClassAlias=[a], fromElementByTableAlias=[reorderedp2_, constituen1_, reorderedp0_], fromElementsByPath=[a.constituents, a.constituents[].oid], collectionJoinFromElementsByPath=[a.constituents, a.constituents[].oid], impliedElements=[]}
| +-[FROM_FRAGMENT] FromElement: 'Assoc reorderedp0_' FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=a,role=null,tableName=Assoc,tableAlias=reorderedp0_,origin=null,colums={,className=com.finantix.ReorderedParameters$Assoc}}
| +-[FROM_FRAGMENT] ImpliedFromElement: '' ImpliedFromElement{implied,collection join,not a fetch join,fetch non-lazy properties,classAlias=null,role=com.finantix.ReorderedParameters$Assoc.constituents,tableName=Topic,tableAlias=constituen1_,origin=Assoc reorderedp0_,colums={reorderedp0_.oid ,className=com.finantix.ReorderedParameters$Topic}}
| +-[FROM_FRAGMENT] FromElement: 'Assoc_Topic constituen1_' FromElement{explicit,collection join,not a fetch join,fetch non-lazy properties,classAlias=null,role=null,tableName={none},tableAlias=null,origin=Assoc reorderedp0_,colums={,className=null}}
| \-[FROM_FRAGMENT] ImpliedFromElement: 'Topic reorderedp2_' ImpliedFromElement{implied,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=null,role=null,tableName=Topic,tableAlias=reorderedp2_,origin=Assoc reorderedp0_,colums={constituen1_.constituents_oid ,className=com.finantix.ReorderedParameters$Topic}}
\-[WHERE] SqlNode: 'where'
+-[THETA_JOINS] SqlNode: '{theta joins}'
| +-[SQL_TOKEN] SqlFragment: 'reorderedp0_.oid=constituen1_.Assoc_oid and constituen1_.topic_index = ?'
| \-[SQL_TOKEN] SqlFragment: 'constituen1_.constituents_oid=reorderedp2_.oid'
\-[AND] SqlNode: 'and'
+-[EQ] BinaryLogicOperatorNode: '='
| +-[DOT] DotNode: 'reorderedp0_.type_oid' {propertyName=oid,dereferenceType=4,propertyPath=type.oid,path=a.type.oid,tableAlias=reorderedp0_,className=com.finantix.ReorderedParameters$Assoc,classAlias=a}
| | +-[DOT] DotNode: 'reorderedp0_.type_oid' {propertyName=oid,dereferenceType=ROOT_LEVEL,propertyPath=type.oid,path=a.type,tableAlias=reorderedp0_,className=com.finantix.ReorderedParameters$Assoc,classAlias=a}
| | | +-[ALIAS_REF] IdentNode: 'reorderedp0_.oid' {alias=a, className=com.finantix.ReorderedParameters$Assoc, tableAlias=reorderedp0_}
| | | \-[IDENT] IdentNode: 'type' {originalText=type}
| | \-[IDENT] IdentNode: 'oid' {originalText=oid}
| \-[PARAM] ParameterNode: '?' {ordinal=0, expectedType=org.hibernate.type.LongType@29cda59b}
\-[EQ] BinaryLogicOperatorNode: '='
+-[DOT] DotNode: 'reorderedp2_.oid' {propertyName=oid,dereferenceType=4,propertyPath=oid,path=[.oid,tableAlias=constituen1_,className=com.finantix.ReorderedParameters$Topic,classAlias=null}
| +-[INDEX_OP] IndexNode: 'constituen1_.constituents_oid' {ImpliedFromElement{implied,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=null,role=null,tableName=Topic,tableAlias=reorderedp2_,origin=Assoc reorderedp0_,colums={constituen1_.constituents_oid ,className=com.finantix.ReorderedParameters$Topic}}}
| | +-[DOT] DotNode: '.' {propertyName=constituents,dereferenceType=3,propertyPath=constituents,path=a.constituents,tableAlias=constituen1_,className=com.finantix.ReorderedParameters$Topic,classAlias=null}
| | | +-[ALIAS_REF] IdentNode: 'reorderedp0_.oid' {alias=a, className=com.finantix.ReorderedParameters$Assoc, tableAlias=reorderedp0_}
| | | \-[IDENT] IdentNode: 'constituents' {originalText=constituents}
| | \-[PARAM] ParameterNode: '?' {ordinal=1, expectedType=null}
| \-[IDENT] IdentNode: 'oid' {originalText=oid}
\-[PARAM] ParameterNode: '?' {ordinal=2, expectedType=org.hibernate.type.LongType@29cda59b}
21:59:25,637 DEBUG ErrorCounter:68 - throwQueryException() : no errors
21:59:25,637 DEBUG QueryTranslatorImpl:216 - HQL: from com.finantix.ReorderedParameters$Assoc a where a.type.oid = ? and a.constituents[?].oid = ?
21:59:25,637 DEBUG QueryTranslatorImpl:217 - SQL: select reorderedp0_.oid as oid1_, reorderedp0_.type_oid as type2_1_ from Assoc reorderedp0_, Assoc_Topic constituen1_, Topic reorderedp2_ where reorderedp0_.oid=constituen1_.Assoc_oid and constituen1_.topic_index = ? and constituen1_.constituents_oid=reorderedp2_.oid and reorderedp0_.type_oid=? and reorderedp2_.oid=?
Login
FAQ
Search