Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version:
3.1.2
Mapping documents:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<!--
Auto-generated mapping file from
the hibernate.org cfg2hbm engine
-->
<class name="pinnacle.datavo.context.Ortholog" table="ORTHOLOG" schema="PINNACLE">
<id name="id" type="long">
<column name="ID" precision="22" scale="0" />
<generator class="assigned" />
</id>
<many-to-one name="gene1" class="pinnacle.datavo.context.Gene" fetch="select">
<column name="GENE_1" precision="22" scale="0" />
</many-to-one>

<many-to-one name="gene2" class="pinnacle.datavo.context.Gene" fetch="select">
<column name="GENE_2" precision="22" scale="0" />
</many-to-one>

<property name="percentIdentity" type="double">
<column name="PERCENT_IDENTITY" precision="10" scale="0" />
</property>

<property name="quality" type="string">
<column name="QUALITY" length="50" ></column>
</property>

</class>
</hibernate-mapping>

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<!--
Auto-generated mapping file from
the hibernate.org cfg2hbm engine
-->
<class name="pinnacle.datavo.context.Gene" table="GENE" schema="PINNACLE">
<id name="id" type="long">
<column name="ID"/>

<generator class="seqhilo">
<param name="sequence">sq_gene_id</param>

<param name="max_lo">2</param>
</generator>
</id>
<property name="buildId" type="string">
<column name="BUILD_ID" length="50"/>
</property>
<property name="externalGeneId" type="string">
<column name="EXTERNAL_GENE_ID" length="50"/>
</property>
<property name="organism" type="string">
<column name="ORGANISM" length="50"/>
</property>
<set name="geneTranscriptProteins" inverse="true">
<key>
<column name="GENE_ID" precision="22" scale="0"/>
</key>
<one-to-many class="pinnacle.datavo.context.GeneTranscriptProtein"/>
</set>

<set name="orthologs" inverse="true">
<key>
<column name="GENE_1" precision="22" scale="0"/>
</key>
<one-to-many class="pinnacle.datavo.context.Ortholog"/>
</set>

</class>
</hibernate-mapping>

Name and version of the database you are using:
Oracle 10g


The problem:
I code up the following HQL:

select distinct o
from Ortholog o
where
(o.gene1.id = :geneID
and o.gene2.organism = :organism)
or
(o.gene2.id = :geneID
and o.gene1.organism =:organism)


Notice how there are two queries that are essentially unioned by the or clause (I would also do this as a union, but hibernate doesn't support that, and I'm trying to avoid leaving HQL).

THe generated SQL is:

select
distinct ortholog0_.ID as ID5_,
ortholog0_.GENE_1 as GENE2_5_,
ortholog0_.GENE_2 as GENE3_5_,
ortholog0_.PERCENT_IDENTITY as PERCENT4_5_,
ortholog0_.QUALITY as QUALITY5_
from
PINNACLE.ORTHOLOG ortholog0_,
PINNACLE.GENE gene1_,
PINNACLE.GENE gene2_
where
ortholog0_.GENE_1=gene2_.ID
and ortholog0_.GENE_2=gene1_.ID
and (
ortholog0_.GENE_1=?
and gene1_.ORGANISM=?
or ortholog0_.GENE_2=?
and gene2_.ORGANISM=?
)

Notice how the parenthesis that I put between the two sets of statments is missing? It should be (logic 1) OR (logic 2). HQL -> SQL seems to have it mangled.

Is this a bug? Or is my HQL off? Should I be doing some kind of sub query?

For reference, the correct SQL should be:
select distinct o.*
from ortholog o, gene g1, gene g2
where o.gene_1 = g1.id
and o.gene_2 = g2.id
and (
(g1.id = ? and g2.organism = ?)
or
(g2.id = ? and g1.organism = ?)
)

I suspect that it's just not implemented yet - see this comment in the HQL grammar: http://fisheye.jboss.org/viewrep/~raw,r ... mmar/hql.g