Hello,
I encountered a problem of a bad sql generation. I would like to ask Hibernate authors if this is somehow my fault or is this a symptom of a bug in Hibernate.
Is short: there is a 'User' entity, it contains component 'Address'. Address is associated to the entity 'Country'. Country contains map of elements. I create HQL query as shown below. When I run the query, exception is thrown because of missing table in from clause.
I did my best to isolate problem as precisely as possible, so hoping for some hint. I can provide more information if required.
Hibernate version:
3.2.5
Mapping documents:
Code:
<class name="User" table="user">
<id name="id" type="int" column="IDuser" >
<generator class="native"/>
</id>
<component name="address">
<many-to-one name="country" class="Country" column="FKcountry" not-null="true"></many-to-one>
</component>
</class>
Code:
<class name="Country" table="country">
<id name="id" column="IDcountry" type="int">
<generator class="native"></generator>
</id>
<map name="name" table="country_resource" cascade="all" >
<key column="FKcountry" />
<map-key type="string" column="locale"/>
<element column="value" type="string" not-null="true"/>
</map>
</class>
Code between sessionFactory.openSession() and session.close():This is my test case:
Code:
Session hs = HibernateHelper.getSessionFactory().getCurrentSession();
Transaction tx = hs.beginTransaction();
Query query = hs.createQuery("from User u where u.address.country.name['pl']=:name");
query.setString("name", "abc");
query.list();
tx.commit();
Full stack trace of any exception that occurs:Code:
2007-11-15 19:42:38,718 DEBUG - com.mchange.v2.c3p0.impl.NewPooledConnection@19f9d2 handling a throwable. [[com.mchange.v2.c3p0.impl.NewPooledConnection]]
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'country1_.IDcountry' in 'where clause'
Name and version of the database you are using:MySql 5.0.44
Debug level Hibernate log excerpt:In the excerpt below you can see the line:
Code:
[FROM_FRAGMENT] ImpliedFromElement: '' ImpliedFromElement....
notice empty string '' after 'ImpliedFromElement
Code:
2007-11-15 19:42:38,656 DEBUG - --- SQL AST ---
\-[SELECT] QueryNode: 'SELECT' querySpaces (country_resource,user,country)
+-[SELECT_CLAUSE] SelectClause: '{derived select clause}'
| +-[SELECT_EXPR] SelectExpressionImpl: 'user0_.IDuser as IDuser2_' {FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=u,role=null,tableName=user,tableAlias=user0_,origin=null,colums={,className=test1.User}}}
| \-[SQL_TOKEN] SqlFragment: 'user0_.FKcountry as FKcountry2_'
+-[FROM] FromClause: 'from' FromClause{level=1, fromElementCounter=2, fromElements=3, fromElementByClassAlias=[u], fromElementByTableAlias=[country1_, user0_, name2_], fromElementsByPath=[u.address.country], collectionJoinFromElementsByPath=[u.address.country.name], impliedElements=[]}
| +-[FROM_FRAGMENT] FromElement: 'user user0_' FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=u,role=null,tableName=user,tableAlias=user0_,origin=null,colums={,className=test1.User}}
| +-[FROM_FRAGMENT] ImpliedFromElement: '' ImpliedFromElement{implied,collection join,not a fetch join,fetch non-lazy properties,classAlias=null,role=null,tableName=country,tableAlias=country1_,origin=user user0_,colums={user0_.FKcountry ,className=test1.Country}}
| \-[FROM_FRAGMENT] ImpliedFromElement: 'country_resource name2_' ImpliedFromElement{implied,collection join,not a fetch join,fetch non-lazy properties,classAlias=null,role=test1.Country.name,tableName={none},tableAlias=name2_,origin=user user0_,colums={,className=null}}
\-[WHERE] SqlNode: 'where'
+-[THETA_JOINS] SqlNode: '{theta joins}'
| +-[SQL_TOKEN] SqlFragment: 'user0_.FKcountry=country1_.IDcountry'
| \-[SQL_TOKEN] SqlFragment: 'country1_.IDcountry=name2_.FKcountry and name2_.locale = 'pl''
\-[EQ] BinaryLogicOperatorNode: '='
+-[INDEX_OP] IndexNode: 'name2_.value' {ImpliedFromElement{implied,collection join,not a fetch join,fetch non-lazy properties,classAlias=null,role=test1.Country.name,tableName={none},tableAlias=name2_,origin=user user0_,colums={,className=null}}}
| +-[DOT] DotNode: '.' {propertyName=name,dereferenceType=3,propertyPath=name,path=u.address.country.name,tableAlias=name2_,className=null,classAlias=null}
| | +-[DOT] DotNode: 'user0_.FKcountry' {propertyName=country,dereferenceType=1,propertyPath=address.country,path=u.address.country,tableAlias=country1_,className=test1.Country,classAlias=null}
| | | +-[DOT] DotNode: 'user0_.FKcountry' {propertyName=country,dereferenceType=2,propertyPath=address.country,path=u.address,tableAlias=user0_,className=test1.User,classAlias=u}
| | | | +-[ALIAS_REF] IdentNode: 'user0_.IDuser' {alias=u, className=test1.User, tableAlias=user0_}
| | | | \-[IDENT] IdentNode: 'address' {originalText=address}
| | | \-[IDENT] IdentNode: 'country' {originalText=country}
| | \-[IDENT] IdentNode: 'name' {originalText=name}
| \-[QUOTED_STRING] LiteralNode: ''pl''
\-[NAMED_PARAM] ParameterNode: '?' {name=name, expectedType=null}
...
Code:
2007-11-15 19:42:38,671 DEBUG - about to open PreparedStatement (open PreparedStatements: 0, globally: 0) [[org.hibernate.jdbc.AbstractBatcher]]
2007-11-15 19:42:38,671 DEBUG -
select
user0_.IDuser as IDuser2_,
user0_.FKcountry as FKcountry2_
from
user user0_, <----- country1_ is missing, but used in 'where'
country_resource name2_
where
user0_.FKcountry=country1_.IDcountry
and country1_.IDcountry=name2_.FKcountry
and name2_.locale = 'pl'
and name2_.value=?
excerpt from hibernate.cfg.xmlCode:
<property name="connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="connection.url">jdbc:mysql://servernamel/dbname</property>
<property name="connection.username">username</property>
<property name="connection.password">*****</property>
<property name="dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property>
<property name="use_outer_join">true</property>
<property name="current_session_context_class">thread</property>