-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 6 posts ] 
Author Message
 Post subject: wrong sql generation from hql,missing table in 'from' clause
PostPosted: Thu Nov 15, 2007 3:21 pm 
Beginner
Beginner

Joined: Mon Feb 19, 2007 4:22 am
Posts: 22
Location: Poland
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.xml
Code:
  <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>


Last edited by ponetny_swintuch on Thu Nov 15, 2007 5:39 pm, edited 1 time in total.

Top
 Profile  
 
 Post subject: Reserved Column Names
PostPosted: Thu Nov 15, 2007 3:55 pm 
Newbie

Joined: Thu Nov 15, 2007 1:36 pm
Posts: 12
Location: Ahsburn, VA
I've run into problems where my column names sometimes conflict with the database's reserved words.

I believe name is a conflict with SQL-99 keyword. Hibernate may be doing the right thing, while the database rejects the DDL.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Nov 15, 2007 4:06 pm 
Beginner
Beginner

Joined: Mon Feb 19, 2007 4:22 am
Posts: 22
Location: Poland
Unfortunately it didn't solved the problem. I have changed 'name' to 'description', but it behaves exactly the same.

Thanks anyway


Top
 Profile  
 
 Post subject:
PostPosted: Fri Nov 16, 2007 10:41 am 
Expert
Expert

Joined: Mon Feb 14, 2005 12:32 pm
Posts: 609
Location: Atlanta, GA - USA
ponetny_swintuch wrote:
Unfortunately it didn't solved the problem. I have changed 'name' to 'description', but it behaves exactly the same.

Thanks anyway


Are you using annotations or mapping xml files ? Are you certain your country mapping file is included in your configuration ?

_________________
Preston

Please don't forget to give credit if/when you get helpful information.


Top
 Profile  
 
 Post subject:
PostPosted: Fri Nov 16, 2007 10:51 am 
Beginner
Beginner

Joined: Mon Feb 19, 2007 4:22 am
Posts: 22
Location: Poland
I am using xml mapping. Yes, I am sure that Country mapping is present in hibernate.cfg.xml.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Nov 19, 2007 7:24 am 
Newbie

Joined: Mon Nov 19, 2007 7:19 am
Posts: 1
hmm, i'm not sure, but a few months ago something similar happened in our project... we didn't find any solution...


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 6 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.