-->
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.  [ 12 posts ] 
Author Message
 Post subject: QueryException thrown when using parentheses
PostPosted: Thu Jan 27, 2005 3:57 pm 
Newbie

Joined: Mon Dec 13, 2004 5:56 pm
Posts: 12
Location: Montreal, QC
Hi,

I've been staring at this problem for a while and can't seem to find out what is happening. My only guess is that the hibernate parser is seeing parentheses and assuming i am trying to do an 'in elements' query when i'm not. Is grouping to assure order of operations ('or's before 'and's) not supported in HQL? Below is the relevant info I"ve collected.

thanks,
sara

Hibernate version:
3.0 beta

Mapping documents:
<?xml version="1.0" ?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping package="ca.mcgill.mcb.bias.hibernate.GO" auto-import="false">

<class name="GeneProduct" table="gene_product">
<id name="id" column="id" type="integer">
<generator class="hilo"/>
</id>

<property name="symbol"
column="symbol"
type="string"
/>

<many-to-one name="dbxref"
class="ca.mcgill.mcb.bias.hibernate.GO.DbxRef"
column="dbxref_id"
/>

<many-to-one name="species"
class="ca.mcgill.mcb.bias.hibernate.GO.GOSpecies"
column="species_id"
/>

<property name="type"
column="type_id"
type="integer"
/>

<property name="fullname"
column="fullname"
type="string"
/>


<set name="terms" table="Association" lazy="true" inverse="false">
<key column="gene_product_id"/>
<many-to-many class="ca.mcgill.mcb.bias.hibernate.GO.Term"
column="term_id"/>
</set>

<set name="synonyms" lazy="true" table="gene_product_synonym">
<key column="gene_product_id"/>
<one-to-many class="ca.mcgill.mcb.bias.hibernate.GO.GeneProductSynonym"/>
</set>

</class>
<!--snipped out other classes...-->

<class name="GeneProductSynonym" table="gene_product_synonym">

<composite-id>
<key-many-to-one name="gene_product_id"
class="ca.mcgill.mcb.bias.hibernate.GO.GeneProduct"
column="gene_product_id"
/>

<key-property name="productSynonym" column="product_synonym" type="string"/>
</composite-id>


</class>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

Iterator<GeneAnnotation> iter=gene.getAnnotations().iterator();
while(iter.hasNext())
{
ga=iter.next();
if(ga.getType().equals("Additional_name"))
synonym=ga.getDescription();
}



String querystring="select distinct geneprod from GeneProduct geneprod inner join geneprod.synonyms syns where (geneprod.symbol like '"+name+"'";

if(synonym!=null)
querystring+=" or geneprod.symbol like '"+synonym+"'";

querystring+=" or syns.productSynonym like '"+name+"'";

if(synonym!=null)
querystring+=" or syns.productSynonym like '"+synonym+"'";

querystring+=") and geneprod.species=?";

logger.info("query string: "+querystring);

Query hq=sess.createQuery(querystring);

hq.setEntity(0, go_spec);

list=hq.list();


Full stack trace of any exception that occurs:
org.hibernate.QueryException: in expected: geneprod [select distinct geneprod from GeneProduct geneprod inner join geneprod.synonyms syns where (geneprod.symbol like 'YPR016C' or geneprod.symbol like 'TIF6' or syns.productSynonym like 'YPR016C' or syns.productSynonym like 'TIF6') and geneprod.species=?]
at org.hibernate.hql.classic.FromParser.token(FromParser.java:106)
at org.hibernate.hql.classic.ClauseParser.token(ClauseParser.java:86)
at org.hibernate.hql.classic.PreprocessingParser.token(PreprocessingParser.java:109)
at org.hibernate.hql.classic.ParserHelper.parse(ParserHelper.java:29)
at org.hibernate.hql.classic.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:172)
at org.hibernate.hql.classic.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:148)
at org.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:403)
at org.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:941)
at org.hibernate.impl.SessionImpl.find(SessionImpl.java:918)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:84)
at ca.mcgill.mcb.bias.libraries.geneOntology.PairwiseStatistic.getGeneProductFromGene(PairwiseStatistic.java:400)
at ca.mcgill.mcb.bias.libraries.geneOntology.PairwiseStatistic.getDistance(PairwiseStatistic.java:432)
at ca.mcgill.mcb.bias.modules.pimp.ERData.getGODistance(ERData.java:505) at ca.mcgill.mcb.bias.modules.pimp.ERData.getGODistance(ERData.java:492) at ca.mcgill.mcb.bias.modules.pimp.ERStatistics.printGODistances(ERStatistics.java:74)
at ca.mcgill.mcb.bias.modules.pimp.ERStatistics.main(ERStatistics.java:170)

Name and version of the database you are using:
mysql. I dont have the version, but i know it supports subqueries.

The generated SQL (show_sql=true):
it never gets to that point!

Debug level Hibernate log excerpt:
I didn't get anything related.


Top
 Profile  
 
 Post subject:
PostPosted: Sun Jan 30, 2005 11:21 pm 
Contributor
Contributor

Joined: Thu Nov 06, 2003 9:49 pm
Posts: 104
Location: New York, NY
Try the new query translator, let us know if it works:
http://www.hibernate.org/250.html#A16


Top
 Profile  
 
 Post subject:
PostPosted: Mon Jan 31, 2005 2:30 pm 
Newbie

Joined: Mon Dec 13, 2004 5:56 pm
Posts: 12
Location: Montreal, QC
thanks for the prompt response!

i got the new query translator running, and am now having problems with the many-to-many relationships (trouble finding columns of the many-to-many tables), which seems to be a known issue (from what i've read) in the hibernate 3 beta. the same queries were working just fine before the switch to the new translator (and to the new Session class, i was using the classic). Will this be fixed in the RC?

i'm going to go over my code once more since the problem doesn't seem very consistent, and will try to repost something meaningful.

thanks,
sara


Top
 Profile  
 
 Post subject:
PostPosted: Mon Jan 31, 2005 5:03 pm 
Contributor
Contributor

Joined: Thu Nov 06, 2003 9:49 pm
Posts: 104
Location: New York, NY
Try it without the many-to-many, and/or try using Hibernate3 head. I just got some of the more complicated many-to-many test cases to work quite recently.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Jan 31, 2005 5:37 pm 
Newbie

Joined: Mon Dec 13, 2004 5:56 pm
Posts: 12
Location: Montreal, QC
ok, so here are the details of my new problem. essentially it's a many-to-many reference (ProtProtInteraction.evidence) to another many-to-many (Evidence.references). the latter is has 2 subclasses (LiteratureReference and BookReference).

what is hibernate 3 head?

thanks,
sara
--------------------------------------------------
This is the code i'm trying to execute:
Code:
try{
       
        references=session.createCriteria(Reference.class)
          .add(Expression.like("authors", first_author))
          .list();
       
        logger.info("got "+references.size()+" items for author "+first_author);
       
        for(int i=0;i<references.size();i++)
          {
            Query hq=session.createQuery("select distinct ppi from ProtProtInteraction ppi inner join ppi.evidence evid where ? in elements(evid.references)");
            hq.setEntity(0,references.get(0));
         
            list.addAll(hq.list());
         
          }
      }
      catch(Exception e){
        e.printStackTrace();
      }


and here are the mapping files. our architecture depends on the many-to-many tables we have (so it's impossible to remove them).
first the common package file:
Code:
<?xml version="1.0" ?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate//Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping package="ca.mcgill.mcb.bias.hibernate.common">

 
  <!--Litterature stuff-->
  <class name="Reference" table="Reference">
    <id name="id" column="ref_id" type="integer">
      <generator class="increment"/>
    </id>
   
    <property name="authors"  column="authors"    type="string" />
    <property name="title"    column="title"      type="string" />
    <property name="year"    column="year"      type="int"   />
    <property name="url"      column="URL"        type="string" />
    <property name="comment"  column="comment"    type="string" />
 
       <set name="evidences" lazy="true" table="Reference2Evidence">
     <key column="referenceId"/>
    <many-to-many
       class="ca.mcgill.mcb.bias.hibernate.common.Evidence"
       column="evidenceId" />
    </set>


      <set name="techniques" table="Reference2Technique" inverse="false"
      lazy="true">
      <key column="referenceId"/>
      <many-to-many class="ca.mcgill.mcb.bias.hibernate.common.Technique"
      column="techniqueId"/>
     </set>



    <joined-subclass name="LiteratureReference" table="LiteratureReference">
      <key column="ref_id"/>
      <property name="pubmed"  column="pubmed_id" type="long"  />
      <property name="doi"     column="doi"       type="string"/>
      <property name="journal" column="journal"   type="string"/>
      <property name="volume"  column="volume"    type="string"/>
      <property name="pages"   column="pages"     type="string"/>
      <property name="issue"   column="issue"     type="string"/>
    </joined-subclass>

    <joined-subclass name="BookReference" table="BookReference">
      <key column="ref_id"/>
      <property name="publisher" column="publisher" type="string"/>
      <property name="publisher_location" column="publisher_location"
        type="string"/>
      <property name="isbn" column="isbn" type="string"/>
      <property name="page_no" column="page_no" type="int"/>
    </joined-subclass>

  </class>
 
  <!--Evidence stuff-->

 
  <class name="Evidence" table="Evidence">
    <id name="evidenceId" column="evidenceId" type="integer">
      <generator class="increment"/>
    </id>
   
    <set name="references" inverse="true" lazy="true" table="Reference2Evidence">
     <key column="evidenceId"/>
    <many-to-many
       class="ca.mcgill.mcb.bias.hibernate.common.Reference"
       column="referenceId" />
    </set>
     
    <many-to-one name="technique"
      class="ca.mcgill.mcb.bias.hibernate.common.Technique"
      column="techniqueId"
      />
     
    <many-to-one name="parameters"
      class="ca.mcgill.mcb.bias.hibernate.common.TechniqueParameters"
      column="parametersId"
      />

    <property name="score"
      column="score"
      type="double"
      />
     
      <!--  many-to-one name="evidenceType"
      class="ca.mcgill.mcb.bias.hibernate.common.EvidenceType"
      column="evidenceType"
      / -->

    <property name="comment"
      column="comment"
      type="string"
      />
     
   
<!--we need to add these reverse pointers to update correctly-->
    <set name="matrices" inverse="true" lazy="true" table="Evidence2Matrix">
     <key column="evidenceId"/>
    <many-to-many
       class="ca.mcgill.mcb.bias.hibernate.transfac.Matrix"
       column="matrixID" />
    </set>

    <set name="protProtInteractions" table="Evidence2ProtProtInteraction"
    lazy="true">
     <key column="evidenceId"/>
    <many-to-many class="ca.mcgill.mcb.bias.hibernate.sig.ProtProtInteraction"
     column="protProtInteractionId"/>
    </set>
   
    <set name="complexes" table="Evidence2Complex"
    lazy="true">
     <key column="evidenceId"/>
    <many-to-many class="ca.mcgill.mcb.bias.hibernate.sig.Complex"
     column="complexId"/>
    </set>

    <set name="interactionControls" table="Evidence2InteractionControl"
    lazy="true">
     <key column="evidenceId"/>
    <many-to-many class="ca.mcgill.mcb.bias.hibernate.sig.InteractionControl"
     column="interactionControlId"/>
    </set>

    <set name="maskedDNASequences" table="Evidence2MaskedDNASequence" inverse="true"
    lazy="true">
     <key column="evidenceId"/>
    <many-to-many class="ca.mcgill.mcb.bias.hibernate.bio.MaskedDNASequence"
     column="maskedDNASequenceId"/>
    </set>

    <set name="geneticInteractionDescriptions" table="Evidence2GeneticInteractionDescription"
    lazy="true">
     <key column="evidenceId"/>
    <many-to-many class="ca.mcgill.mcb.bias.hibernate.bio.genetic.GeneticInteractionDescription"
     column="geneticInteractionDescriptionId"/>
    </set>

    <!--set name="protDNAInteractionEffects" table="Evidence2ProtDNAInteractionEffect"
    lazy="true">
     <key column="evidenceId"/>
    <many-to-many class="ca.mcgill.mcb.bias.hibernate.bio.protDNA.ProtDNAInteractionEffect"
     column="protDNAInteractionEffectId"/>
    </set-->

    <set name="modules" table="Evidence2Module" inverse="true"
    lazy="true">
     <key column="evidenceId"/>
    <many-to-many class="ca.mcgill.mcb.bias.hibernate.bio.protDNA.Module"
     column="moduleId"/>
    </set>

    <set name="proteinDNAInteractions" table="Evidence2ProteinDNAInteraction" inverse="true" lazy="true">
     <key column="evidenceId"/>
    <many-to-many class="ca.mcgill.mcb.bias.hibernate.bio.protDNA.ProteinDNAInteraction"
     column="pdiId"/>
    </set>

    <set name="protGeneRegulations" table="Evidence2ProtGeneRegulation" inverse="true"
    lazy="true">
     <key column="evidenceId"/>
    <many-to-many class="ca.mcgill.mcb.bias.hibernate.bio.protDNA.ProtGeneRegulation"
     column="protGeneRegulationId"/>
    </set>

  </class>
 
  <class  name="Technique" table="Technique">
    <id name="id" column="ID" type="integer">
      <generator class="increment" />
    </id>
     
    <property name="name" column="name"  type="string" />
    <property name="description" column="description" type="string" />
     
    <many-to-one name="techniqueType"
      class="ca.mcgill.mcb.bias.hibernate.common.TechniqueType"
      column="type_id" cascade="save-update" />

 
    <many-to-one name="relatedTechnique"
      class="ca.mcgill.mcb.bias.hibernate.common.Technique"
      column="techniqueId"
      />

    <set name="references" table="Reference2Technique" inverse="true"
      lazy="true">
    <key column="techniqueId"/>
    <many-to-many class="ca.mcgill.mcb.bias.hibernate.common.Reference"
    column="referenceId"/>
    </set>

  </class>

  <class name="Reference2Technique" table="Reference2Technique">
  <composite-id>
   <key-many-to-one name="reference" class="ca.mcgill.mcb.bias.hibernate.common.Reference" column="referenceId"/>
   <key-many-to-one name="technique" class="ca.mcgill.mcb.bias.hibernate.common.Technique" column="techniqueId"/>
  </composite-id>
  </class>

  <class  name="TechniqueType" table="TechniqueType">
    <id name="id" column="ID" type="integer">
      <generator class="increment"/>
    </id>
    <property name="name" column="name"  type="string"  />
  </class>


  <class  discriminator-value="0" name="TechniqueParameters" table="TechniqueParameters">
    <id name="ID" column="ID" type="integer" unsaved-value="null">
      <generator class="increment" />
    </id>
   
    <discriminator column="technique_id" type="integer" />
     
    <many-to-one name="technique"
      class="ca.mcgill.mcb.bias.hibernate.common.Technique"
      column="technique_id"
      insert="false" update="false"  />
     
     
    <subclass name="TransfacConvParameters3"  discriminator-value="3" lazy="true">
      <many-to-one name="originalMatrix"
        class="ca.mcgill.mcb.bias.hibernate.transfac.Matrix"
        column="intPar1"/>
    </subclass>
    <subclass name="TransfacConvParameters4"  discriminator-value="4" lazy="true">
      <many-to-one name="originalMatrix"
        class="ca.mcgill.mcb.bias.hibernate.transfac.Matrix"
        column="intPar1"/>
    </subclass>
    <subclass name="TransfacConvParameters5"  discriminator-value="5" lazy="true">
      <many-to-one name="originalMatrix"
        class="ca.mcgill.mcb.bias.hibernate.transfac.Matrix"
        column="intPar1"/>
    </subclass>
    <subclass name="TransfacConvParameters6"  discriminator-value="6" lazy="true">
      <many-to-one name="originalMatrix"
        class="ca.mcgill.mcb.bias.hibernate.transfac.Matrix"
        column="intPar1"/>
    </subclass>
    <subclass name="TransfacConvParameters7"  discriminator-value="7" lazy="true">
      <many-to-one name="originalMatrix"
        class="ca.mcgill.mcb.bias.hibernate.transfac.Matrix"
        column="intPar1"/>
    </subclass>
    <subclass name="TransfacConvParameters8"  discriminator-value="8" lazy="true">
      <many-to-one name="originalMatrix"
        class="ca.mcgill.mcb.bias.hibernate.transfac.Matrix"
        column="intPar1"/>
    </subclass>
    <subclass name="SimMatGenWideParameters"  discriminator-value="23"  lazy="true">
      <many-to-one name="matrix"
        class="ca.mcgill.mcb.bias.hibernate.transfac.Matrix"
        column="intPar1"/>
    </subclass>
    <subclass name="SimMatPromRegParameters"  discriminator-value="24"  lazy="true">
      <many-to-one name="matrix"
        class="ca.mcgill.mcb.bias.hibernate.transfac.Matrix"
        column="intPar1"/>
      <property name="upstreamLength"
        type="java.lang.Integer"
        column="intPar2"/>
      <property name="downstreamLength"
        type="java.lang.Integer"
        column="intPar3"/>
    </subclass>
    <subclass name="OneMCnMMSymGRE"   discriminator-value="25"  lazy="true">
      <many-to-one name="matrix"
        class="ca.mcgill.mcb.bias.hibernate.transfac.Matrix"
        column="intPar1"/>
      <property name="mismatches"                                                                                                                                 
        type="java.lang.Integer"
        column="intPar2"/>
    </subclass>
    <subclass name="LimmaParameters" discriminator-value="14" lazy="true">
      <property name="s2prior"
        column="par1"
        type="ca.mcgill.mcb.bias.hibernate.Microarray.DoubleType"
        />
      <property name="varprior"
        column="par2"
        type="ca.mcgill.mcb.bias.hibernate.Microarray.DoubleType"
        />
      <property name="dfprior"
        column="par3"
        type="ca.mcgill.mcb.bias.hibernate.Microarray.DoubleType"
        />
      <property name="proportion"
        column="par4"
        type="ca.mcgill.mcb.bias.hibernate.Microarray.DoubleType"
        />
       
    </subclass>
   
  </class>

<class name="Reference2Evidence" table="Reference2Evidence">
<composite-id>
<key-many-to-one name="reference"
   class="ca.mcgill.mcb.bias.hibernate.common.Reference"
   column="referenceId"/>

<key-many-to-one name="evidence"
   class="ca.mcgill.mcb.bias.hibernate.common.Evidence"
   column="evidenceId"/>
</composite-id>
</class>
</hibernate-mapping>


and below is the mapping file for the sig.* package:
Code:
<?xml version="1.0" ?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping package="ca.mcgill.mcb.bias.hibernate.sig">

<class  name="ProtInteractorState" table="SIG_ProtInteractorState">
<id name="id" column="id" type="integer">
<generator class="increment"/>
</id>


<many-to-one name="interactor"
class="ca.mcgill.mcb.bias.hibernate.sig.Interactor"
column="interactorId"
/>

<property name="name"
column="name"
type="string"
/>

<many-to-one name="compartment"
class="ca.mcgill.mcb.bias.hibernate.sig.CellCompartment"
column="compartment"
/>

<set name="interactions" table="SigInteractionPartners" lazy="true"
inverse="false">
<key column="interactorId"/>
<many-to-many
class="ca.mcgill.mcb.bias.hibernate.sig.ProtProtInteraction"
column="interactionId"/>
</set>

</class>

<!--class  name="EvidenceSet" table="SIG_EvidenceSet">

<composite-id>
<key-property name="id" column="id" type="integer"/>

<key-many-to-one name="evidenceId"
class="ca.mcgill.mcb.bias.hibernate.common.Evidence"
column="evidenceId"
/>
</composite-id>
</class-->

<class  name="Modification" table="SIG_Modification">
<id name="id" column="id" type="integer">
<generator class="increment"/>
</id>

<property name="position"
column="position"
type="integer"
/>

<many-to-one name="modificationType"
class="ca.mcgill.mcb.bias.hibernate.sig.ModificationType"
column="modificationType"
/>

<property name="necessary"
column="necessary"
type="boolean"
/>

<property name="sufficient"
column="sufficient"
type="boolean"
/>

</class>

<class  name="ProtProtInteraction" table="SIG_ProtProtInteraction">
<id name="id" column="id" type="integer">
<generator class="increment"/>
</id>

<set name="evidence" table="Evidence2ProtProtInteraction" inverse="true"
lazy="true">
<key column="protProtInteractionId"/>
<many-to-many class="ca.mcgill.mcb.bias.hibernate.common.Evidence"
column="evidenceId"/>
</set>

<!--many-to-one name="evidence"
   class="ca.mcgill.mcb.bias.hibernate.common.EvidenceSet"
   column="evidence"/-->

<!--many-to-one name="evidence"
class="ca.mcgill.mcb.bias.hibernate.common.EvidenceSet">
<column name="id"/>
<column name="evidenceId"/>
</many-to-one-->

<property name="description"
column="description"
type="string"
/>

<property name="confidence"
column="confidence"
type="string"
/>

<set name="interactors" table="SigInteractionPartners" lazy="true"
inverse="true">
<key column="interactionId"/>
<many-to-many
class="ca.mcgill.mcb.bias.hibernate.sig.ProtInteractorState"
column="interactorId"/>
</set>

</class>

<class name="Evidence2ProtProtInteraction" table="Evidence2ProtProtInteraction">
<composite-id>
<key-many-to-one name="evidence"
class="ca.mcgill.mcb.bias.hibernate.common.Evidence" column="evidenceId"/>
<key-many-to-one name="protProtInteraction"
class="ca.mcgill.mcb.bias.hibernate.sig.ProtProtInteraction"
column="protProtInteractionId"/>
</composite-id>
</class>

<class  name="CellCompartment" table="SIG_CellCompartment">
<id name="id" column="id" type="integer">
<generator class="increment"/>
</id>

<property name="name"
column="name"
type="string"
/>

</class>

<class  name="InteractionPartners" table="SIG_interactionPartners">

<composite-id>
<key-many-to-one name="interactionId"
class="ca.mcgill.mcb.bias.hibernate.sig.ProtProtInteraction"
column="interactionId"
/>

<key-many-to-one name="interactorId"
class="ca.mcgill.mcb.bias.hibernate.sig.ProtInteractorState"
column="interactorId"
/>
</composite-id>
</class>

<class  name="Dbxref" table="SIG_dbxref">
<id name="id" column="id" type="integer">
<generator class="increment"/>
</id>

<property name="xrefkey"
column="xrefkey"
type="string"
/>

<property name="xref_dbname"
column="xref_dbname"
type="string"
/>

<property name="xref_desc"
column="xref_desc"
type="string"
/>

</class>

<class  name="InteractionControl" table="SIG_InteractionControl">
<id name="id" column="id" type="integer">
<generator class="increment"/>
</id>

<many-to-one name="PPInteractionID"
class="ca.mcgill.mcb.bias.hibernate.sig.ProtProtInteraction"
column="PPInteractionID"
/>

<many-to-one name="controllerId"
class="ca.mcgill.mcb.bias.hibernate.sig.ProtInteractorState"
column="controllerId"
/>

<many-to-one name="controlType"
class="ca.mcgill.mcb.bias.hibernate.sig.ControlType"
column="controlType"
/>

<property name="necessary"
column="necessary"
type="boolean"
/>

<property name="sufficient"
column="sufficient"
type="boolean"
/>

<!--the many versions of the evidence system...-->
<set name="evidence" table="Evidence2InteractionControl" inverse="true" lazy="true">
<key column="interactionControlId"/>
<many-to-many class="ca.mcgill.mcb.bias.hibernate.common.Evidence"
column="evidenceId"/>
</set>

</class>

<class name="Evidence2InteractionControl"
table="Evidence2InteractionControl">
<composite-id>
<key-many-to-one name="evidence"
class="ca.mcgill.mcb.bias.hibernate.common.Evidence" column="evidenceId"/>
<key-many-to-one name="interactionControl"
class="ca.mcgill.mcb.bias.hibernate.sig.InteractionControl" column="interactionControlId"/>
</composite-id>
</class>


<class  name="ControlType" table="SIG_ControlType">
<id name="id" column="id" type="integer">
<generator class="increment"/>
</id>

<property name="name"
column="name"
type="string"
/>

</class>

<class  name="StateModifications" table="SIG_stateModifications">

<composite-id>
<key-many-to-one name="protStateId"
class="ca.mcgill.mcb.bias.hibernate.sig.ProtInteractorState"
column="protStateId"
/>

<key-many-to-one name="modificationId"
class="ca.mcgill.mcb.bias.hibernate.sig.Modification"
column="modificationId"
/>
</composite-id>
</class>

<class  name="Interactor" table="SIG_Interactor">
<id name="id" column="id" type="integer">
<generator class="increment"/>
</id>

<property name="name"
column="name"
type="string"
/>

<set name="dbxref" table="Dbxref2Interactor" inverse="true" lazy="true">
<key column="interactorId"/>
<many-to-many class="ca.mcgill.mcb.bias.hibernate.sig.Dbxref"
column="dbxrefId"/>
</set>

<set name="interactorStates" lazy="true" inverse="false">
<key column="interactorId"/>
<one-to-many class="ca.mcgill.mcb.bias.hibernate.sig.ProtInteractorState"/>
</set>


</class>

<class name="DbxrefSet2Interactor" table="dbxrefSet2Interactor">
<composite-id>
<key-many-to-one name="dbxref"
class="ca.mcgill.mcb.bias.hibernate.sig.Dbxref" column="dbxrefSetId"/>
<key-many-to-one name="interactor"
class="ca.mcgill.mcb.bias.hibernate.sig.Interactor" column="interactorId"/>
</composite-id>
</class>

<class  name="ModificationType" table="SIG_ModificationType">
<id name="id" column="id" type="integer">
<generator class="increment"/>
</id>

<set name="dbxref" table="Dbxref2ModificationType" inverse="true"
lazy="true">
<key column="modificationTypeId"/>
<many-to-many class="ca.mcgill.mcb.bias.hibernate.common.Evidence"
column="evidenceId"/>
</set>

<property name="name"
column="name"
type="string"
/>

</class>

<class name="Dbxref2ModificationType" table="Dbxref2ModificationType">
<composite-id>
<key-many-to-one name="dbxref"
class="ca.mcgill.mcb.bias.hibernate.sig.Dbxref" column="dbxrefId"/>
<key-many-to-one name="modificationType"
class="ca.mcgill.mcb.bias.hibernate.sig.ModificationType" column="modificationTypeId"/>
</composite-id>
</class>

<class  name="Complex" table="SIG_Complex">
<id name="id" column="id" type="integer">
<generator class="increment"/>
</id>

<!--many-to-one name="evidence"
class="ca.mcgill.mcb.bias.hibernate.common.EvidenceSet">
<column name="id"/>
<column name="evidenceId"/>
</many-to-one-->

<!--many-to-one name="evidence"
class="ca.mcgill.mcb.bias.hibernate.common.EvidenceSet"
column ="evidence"/-->

<set name="evidence" table="Evidence2Complex" inverse="true" lazy="true">
<key column="complexId"/>
<many-to-many class="ca.mcgill.mcb.bias.hibernate.common.Evidence"
column="evidenceId"/>
</set>
</class>

<class name="Evidence2Complex" table="Evidence2Complex">
<composite-id>
<key-many-to-one name="evidence"
class="ca.mcgill.mcb.bias.hibernate.common.Evidence" column="evidenceId"/>
<key-many-to-one name="complex"
class="ca.mcgill.mcb.bias.hibernate.sig.Complex" column="complexId"/>
</composite-id>
</class>

<class  name="ComplexMembership" table="SIG_complexMembership">

<composite-id>
<key-many-to-one name="complexId"
class="ca.mcgill.mcb.bias.hibernate.sig.Complex"
column="complexId"
/>

<key-many-to-one name="memberId"
class="ca.mcgill.mcb.bias.hibernate.sig.ProtInteractorState"
column="memberId"
/>
</composite-id>

</class>

<class  name="InteractionResults" table="SIG_interactionResults">

<composite-id>
<key-many-to-one name="protStateId"
class="ca.mcgill.mcb.bias.hibernate.sig.ProtInteractorState"
column="protStateId"
/>

<key-many-to-one name="modificationId"
class="ca.mcgill.mcb.bias.hibernate.sig.Modification"
column="modificationId"
/>
</composite-id>
</class>


</hibernate-mapping>

here is the stack trace
the exception:org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:70)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:1364)
at org.hibernate.loader.Loader.list(Loader.java:1344)
at org.hibernate.loader.QueryLoader.list(QueryLoader.java:349)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:265)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:994)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
at ca.mcgill.mcb.bias.modules.pimp.ERData.getInteractions(ERData.java:254)
at ca.mcgill.mcb.bias.modules.pimp.ERData.<init>(ERData.java:109)
at ca.mcgill.mcb.bias.modules.pimp.ERStatistics.<init>(ERStatistics.java:37)
at ca.mcgill.mcb.bias.modules.pimp.ERStatistics.main(ERStatistics.java:166)
Caused by: java.sql.SQLException: Unknown column 'reference4_.referenceId' in 'field list'
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1997)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1167)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1278)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2247)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1586)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:108)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1109)
at org.hibernate.loader.Loader.doQuery(Loader.java:349)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:195)
at org.hibernate.loader.Loader.doList(Loader.java:1361)
... 9 more

there is ALOT of debug output, but since the problem is in the query formation, here is the original HQL:
53565 DEBUG QueryTranslatorImpl - parse() - HQL: select distinct ppi from ca.mcgill.mcb.bias.hibernate.sig.ProtProtInteraction ppi inner join ppi.evidence evid where ? in elements(evid.references)
53582 DEBUG AST - --- HQL AST ---
\-'query' [QUERY]
+-'SELECT_FROM' [SELECT_FROM]
| +-'from' [FROM]
| | +-'.' [DOT]
| | | +-'.' [DOT]
| | | | +-'.' [DOT]
| | | | | +-'.' [DOT]
| | | | | | +-'.' [DOT]
| | | | | | | +-'.' [DOT]
| | | | | | | | +-'ca' [IDENT]
| | | | | | | | \-'mcgill' [IDENT]
| | | | | | | \-'mcb' [IDENT]
| | | | | | \-'bias' [IDENT]
| | | | | \-'hibernate' [IDENT]
| | | | \-'sig' [IDENT]
| | | \-'ProtProtInteraction' [IDENT]
| | +-'ppi' [ALIAS]
| | \-'join' [JOIN]
| | +-'inner' [INNER]
| | +-'.' [DOT]
| | | +-'ppi' [IDENT]
| | | \-'evidence' [IDENT]
| | \-'evid' [ALIAS]
| \-'select' [SELECT]
| +-'distinct' [DISTINCT]
| \-'ppi' [IDENT]
\-'where' [WHERE]
\-'in' [IN]
+-'?' [PARAM]
\-'inList' [IN_LIST]
\-'elements' [ELEMENTS]
\-'.' [DOT]
+-'evid' [IDENT]
\-'references' [IDENT]

and here is the final query:
53672 DEBUG AST - --- SQL AST ---
\-'SELECT' [SELECT] querySpaces (Reference2Evidence,Evidence2ProtProtInteraction,SIG_ProtProtInteraction,Evidence,Reference)
+-'{select clause}' [SELECT_CLAUSE]
| +-'distinct' [DISTINCT]
| +-'protprotin0_.id as id' [ALIAS_REF] {alias=ppi, className=ca.mcgill.mcb.bias.hibernate.sig.ProtProtInteraction, tableAlias=protprotin0_}
| \-'protprotin0_.description as descript2_177_, protprotin0_.confidence as confidence177_' [SQL_TOKEN]
+-'from' [FROM] FromClause{from}
| \-'SIG_ProtProtInteraction protprotin0_' [FROM_FRAGMENT] FromElement{className=ca.mcgill.mcb.bias.hibernate.sig.ProtProtInteraction,explicit,not a collection join,classAlias=ppi,tableName=SIG_ProtProtInteraction,tableAlias=protprotin0_,colums={}}
| \-'inner join Evidence2ProtProtInteraction evidence1_ on protprotin0_.id=evidence1_.protProtInteractionId inner join Evidence evidence2_ on evidence1_.evidenceId=evidence2_.evidenceId' [JOIN_FRAGMENT] FromElement{className=ca.mcgill.mcb.bias.hibernate.common.Evidence,explicit,not a collection join,classAlias=evid,tableName=Evidence,tableAlias=evidence2_,colums={protprotin0_.id }}
\-'where' [WHERE]
\-'in' [IN]
+-'?' [PARAM]
\-'inList' [IN_LIST]
\-'SELECT' [SELECT] querySpaces (Reference2Evidence,Evidence2ProtProtInteraction,SIG_ProtProtInteraction,Evidence,Reference)
+-'{select clause}' [SELECT_CLAUSE]
| \-'(select reference4_.referenceId from Reference2Evidence references3_ where evidence2_.evidenceId=references3_.evidenceId)' [SQL_TOKEN] {method=elements,selectColumns=[(select reference4_.referenceId from Reference2Evidence references3_ where evidence2_.evidenceId=references3_.evidenceId)],fromElement=FromElement{<1> tableAlias='reference4_', className='ca.mcgill.mcb.bias.hibernate.common.Reference', classAlias='null', role='null', includeSubclasses=true, collectionJoin=false, fetch=false, origin=FromElement{<0> tableAlias='references3_', className='ca.mcgill.mcb.bias.hibernate.common.Reference', classAlias='null', role='ca.mcgill.mcb.bias.hibernate.common.Evidence.references', includeSubclasses=true, collectionJoin=false, fetch=false, origin=FromElement{<1> tableAlias='evidence2_', className='ca.mcgill.mcb.bias.hibernate.common.Evidence', classAlias='evid', role='ca.mcgill.mcb.bias.hibernate.sig.ProtProtInteraction.evidence', includeSubclasses=false, collectionJoin=false, fetch=false, origin=FromElement{<0> tableAlias='protprotin0_', className='ca.mcgill.mcb.bias.hibernate.sig.ProtProtInteraction', classAlias='ppi', role='null', includeSubclasses=true, collectionJoin=false, fetch=false, origin=null, persister=SingleTableEntityPersister(ca.mcgill.mcb.bias.hibernate.sig.ProtProtInteraction), queryableCollection=null, joinSequence=null, columns=null}, persister=SingleTableEntityPersister(ca.mcgill.mcb.bias.hibernate.common.Evidence), queryableCollection=BasicCollectionPersister(ca.mcgill.mcb.bias.hibernate.sig.ProtProtInteraction.evidence), joinSequence=JoinSequence{->BasicCollectionPersister(ca.mcgill.mcb.bias.hibernate.sig.ProtProtInteraction.evidence)[evidence1_]->SingleTableEntityPersister(ca.mcgill.mcb.bias.hibernate.common.Evidence)[evidence2_]}, columns=[protprotin0_.id]}, persister=JoinedSubclassEntityPersister(ca.mcgill.mcb.bias.hibernate.common.Reference), queryableCollection=BasicCollectionPersister(ca.mcgill.mcb.bias.hibernate.common.Evidence.references), joinSequence=JoinSequence{->BasicCollectionPersister(ca.mcgill.mcb.bias.hibernate.common.Evidence.references)[references3_]}, columns=[evidence2_.evidenceId]}, persister=JoinedSubclassEntityPersister(ca.mcgill.mcb.bias.hibernate.common.Reference), queryableCollection=null, joinSequence=JoinSequence{->JoinedSubclassEntityPersister(ca.mcgill.mcb.bias.hibernate.common.Reference)[reference4_]}, columns=[references3_.referenceId]}}
| \-'.' [DOT] {propertyName=references,dereferenceType=DEREF_COLLECTION,propertyPath=references,path=evid.references,tableAlias=references3_,className=ca.mcgill.mcb.bias.hibernate.common.Reference,classAlias=ca.mcgill.mcb.bias.hibernate.common.Reference}
| +-'evidence2_.evidenceId' [ALIAS_REF] {alias=evid, className=ca.mcgill.mcb.bias.hibernate.common.Evidence, tableAlias=evidence2_}
| \-'references' [IDENT] {originalText=references}
+-'{from}' [FROM] FromClause{{from}}
| +-'Reference references3_' [JOIN_FRAGMENT] FromElement{className=ca.mcgill.mcb.bias.hibernate.common.Reference,implied,not a collection join,classAlias=ca.mcgill.mcb.bias.hibernate.common.Reference,tableName=Reference,tableAlias=references3_,colums={evidence2_.evidenceId }}
| \-'Reference reference4_' [JOIN_FRAGMENT] FromElement{className=ca.mcgill.mcb.bias.hibernate.common.Reference,implied,not a collection join,classAlias=ca.mcgill.mcb.bias.hibernate.common.Reference,tableName=Reference,tableAlias=reference4_,colums={references3_.referenceId }}
\-'WHERE' [WHERE]
\-'{and}' [SYNTHETIC_AND] MULTIPLE_FRAGMENTS FromElement{className=ca.mcgill.mcb.bias.hibernate.common.Reference,implied,not a collection join,classAlias=ca.mcgill.mcb.bias.hibernate.common.Reference,tableName=Reference,tableAlias=reference4_,colums={references3_.referenceId }}
+-'evidence2_.evidenceId=references3_.evidenceId' [SQL_TOKEN]
\-'references3_.referenceId=reference4_.ref_id' [SQL_TOKEN]

53672 DEBUG parser - throwQueryException() : no errors
53684 DEBUG QueryTranslatorImpl - HQL: select distinct ppi from ca.mcgill.mcb.bias.hibernate.sig.ProtProtInteraction ppi inner join ppi.evidence evid where ? in elements(evid.references)
53684 DEBUG QueryTranslatorImpl - SQL: select distinct protprotin0_.id as id, protprotin0_.description as descript2_177_, protprotin0_.confidence as confidence177_ from SIG_ProtProtInteraction protprotin0_ inner join Evidence2ProtProtInteraction evidence1_ on protprotin0_.id=evidence1_.protProtInteractionId inner join Evidence evidence2_ on evidence1_.evidenceId=evidence2_.evidenceId where (? in(select (select reference4_.referenceId from Reference2Evidence references3_ where evidence2_.evidenceId=references3_.evidenceId) from Reference references3_, Reference reference4_ where evidence2_.evidenceId=references3_.evidenceId and references3_.referenceId=reference4_.ref_id))


Top
 Profile  
 
 Post subject:
PostPosted: Mon Jan 31, 2005 6:23 pm 
Newbie

Joined: Mon Dec 13, 2004 5:56 pm
Posts: 12
Location: Montreal, QC
one more thing.

here's the unsuccessful query (with bugs highlighted):
---------------------------------------------------------------------
select distinct protprotin0_.id as id, protprotin0_.description as descript2_177_, protprotin0_.confidence as confidence177_
from SIG_ProtProtInteraction protprotin0_ inner join Evidence2ProtProtInteraction evidence1_ on protprotin0_.id=evidence1_.protProtInteractionId inner join Evidence evidence2_ on evidence1_.evidenceId=evidence2_.evidenceId
where
(? in(
select (
select reference4_.referenceId
from Reference2Evidence references3_
where evidence2_.evidenceId=references3_.evidenceId
)
from Reference references3_, Reference reference4_
where evidence2_.evidenceId=references3_.evidenceId and references3_.referenceId=reference4_.ref_id
)
)

and here's what it's supposed to be (with corrections highlighted):

select distinct protprotin0_.id as id, protprotin0_.description as descript2_177_, protprotin0_.confidence as confidence177_
from SIG_ProtProtInteraction protprotin0_ inner join Evidence2ProtProtInteraction evidence1_ on protprotin0_.id=evidence1_.protProtInteractionId inner join Evidence evidence2_ on evidence1_.evidenceId=evidence2_.evidenceId
where (
? in(
select (
select reference4_.ref_id
from Reference2Evidence references3_
where evidence2_.evidenceId=references3_.evidenceId
)
from Reference2Evidence references3_, Reference reference4_
where evidence2_.evidenceId=references3_.evidenceId and references3_.referenceId=reference4_.ref_id
)
)


Top
 Profile  
 
 Post subject:
PostPosted: Mon Jan 31, 2005 6:33 pm 
Contributor
Contributor

Joined: Thu Nov 06, 2003 9:49 pm
Posts: 104
Location: New York, NY
Is that from 'head' (as of today), beta 2, or beta 1?


Top
 Profile  
 
 Post subject:
PostPosted: Mon Jan 31, 2005 6:41 pm 
Newbie

Joined: Mon Dec 13, 2004 5:56 pm
Posts: 12
Location: Montreal, QC
Quote:

Is that from 'head' (as of today), beta 2, or beta 1?

it looks like it's from 'head' since i downloaded it today from the download page. (the beta 3 version released yesterday).

sara


Top
 Profile  
 
 Post subject:
PostPosted: Mon Jan 31, 2005 6:48 pm 
Contributor
Contributor

Joined: Thu Nov 06, 2003 9:49 pm
Posts: 104
Location: New York, NY
Looks like " in (elements(thing.x))" isn't working when x is a many-to-many with joined subclass elements. I *just* got that working in the last couple of days. It might actually work now.


Top
 Profile  
 
 Post subject:
PostPosted: Tue Feb 01, 2005 12:03 pm 
Newbie

Joined: Mon Dec 13, 2004 5:56 pm
Posts: 12
Location: Montreal, QC
ahh, that's it. awesome work guys!

-sara


Top
 Profile  
 
 Post subject:
PostPosted: Tue Feb 01, 2005 9:28 pm 
Contributor
Contributor

Joined: Thu Nov 06, 2003 9:49 pm
Posts: 104
Location: New York, NY
So it works with Hibernate3 head?


Top
 Profile  
 
 Post subject:
PostPosted: Wed Feb 02, 2005 8:54 am 
Newbie

Joined: Mon Dec 13, 2004 5:56 pm
Posts: 12
Location: Montreal, QC
yes it does -- i downloaded the latest bits from CVS on Feb 1(yesterday) and they worked.

-sara


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 12 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:
cron
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.