Hibernate version: 3.0.5

Mapping documents: Isolated test case at the bottom, including mapping.

Code between sessionFactory.openSession() and session.close(): Isolated test case at the bottom, including mapping.

Full stack trace of any exception that occurs: No exception occurs

Name and version of the database you are using: Oracle 10.1.0.2

The generated SQL (show_sql=true): See below

Debug level Hibernate log excerpt: Not relevant


Summary:
When using HQL to query through a many-to-many relationship by id, the generated sql joins in one unnecessary table per condition. The generated sql does give the correct result. I would like to figure out how to phrase my HQL query or set up the relationship in a way that doesn't result in hibernate generating sql with unnecessary joins. If the unnecessary joins are going to be optimized away as a future targetted enhancement to hibernate, that would also be welcome information. I couldn't find anything in JIRA or on the forums that seemed related.

I've generated an artificial, isolated, hopefully reproducible test case, included below inline. I'll try to attach the files directly after I submit.

The artificial test case consists of two tables, machines and components, with a many to many relationship by id expressed in a machine_component_assns table. I want to be able to query for all machines which use two particular components. An example would be to query for all machines which use both a display and a sparkplug.

The HQL looks like:
SELECT m FROM Machine m
WHERE
m.components.id = :component_id1 and
m.components.id = :component_id2

The generated sql for a query like this looks like:
SELECT machine0_.machine_id AS col_0_0_
FROM machines machine0_,
machine_component_assns components1_,
components component2_,
machine_component_assns components3_,
components component4_
WHERE machine0_.machine_id = components3_.machine_id
AND components3_.component_id = component4_.component_id
AND machine0_.machine_id = components1_.machine_id
AND components1_.component_id = component2_.component_id
AND component2_.component_id = :1
AND component4_.component_id = :2

Notice the unnecessary joins to the component table, given that I am querying based on a specified component id, which is present in the associations table. I assert the generated sql should look like:

SELECT machine0_.machine_id AS col_0_0_
FROM machines machine0_,
machine_component_assns components1_,
machine_component_assns components3_,
WHERE machine0_.machine_id = components3_.machine_id
AND components3_.component_id = :1
AND machine0_.machine_id = components1_.machine_id
AND components1_.component_id = :2

Because I'm not querying on a component property, but instead on an id, there is no need to join in the component table through the association.

Is there a way to express a query like this, or structure the tables in a different manner, to achieve the same results without the unnecessary joins? Only specifying two components is just an aspect of the artificial test case -- I could certainly query based on six or seven components, causing 14 joins, in a more complicated example.


Isolated test case (I'll attach as files if possible after this post):
hibernate.cfg.xml
<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration
PUBLIC "-//Hibernate/Hibernate Configuration DTD//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>

<session-factory>

<property name="connection.datasource">java:/OracleDS</property>
<property name="show_sql">false</property>
<property name="dialect">org.hibernate.dialect.Oracle9Dialect</property>
<property name="cache.use_query_cache">true</property>
<property name="jdbc.batch_size">50</property>

<!-- Mapping files -->
<mapping resource="Component.hbm.xml"/>
<mapping resource="Machine.hbm.xml"/>

</session-factory>

</hibernate-configuration>

Machine.hbm.xml
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping
PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
<class name="Machine" table="machines" mutable="false">
<id
name="machineId"
column="machine_id" />

<property name="machineName">
<column name="machine_name" not-null="true"/>
</property>

<set name="components"
table="machine_component_assns"
lazy="true">
<key column="machine_id" />
<many-to-many class="Component" column="component_id" />
</set>
</class>
<query name="findMachinesUsingTwoSpecificComponents"><![CDATA[SELECT m FROM Machine m
WHERE
m.components.id = :component_id1 and
m.components.id = :component_id2]]></query>


</hibernate-mapping>


Component.hbm.xml
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping
PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
<class name="Component" table="components" mutable="false">
<cache usage="read-write" />
<id
name="componentId"
column="component_id" />

<property name="componentName">
<column name="component_name" not-null="true"/>
</property>
</class>

</hibernate-mapping>


Query code:
Session session = HibernateUtil.currentSession();
Query query = session.getNamedQuery("findMachinesUsingTwoSpecificComponents");
query.setParameter("component_id1", 2);
query.setParameter("component_id2", 1);
query.iterate();
HibernateUtil.closeSession();

I was hoping to figure out how to attach files, but I can not. Here is the remainder of the inline code to set up the test case:

import java.util.Set;

public class Machine
{
private long machineId;

private String machineName;

private Set<Component> components;

public Set<Component> getComponents() {
return components;
}

public void setComponents(Set<Component> components) {
this.components = components;
}

public Machine() {
}

public long getMachineId() {
return machineId;
}

public void setMachineId(long machineId) {
this.machineId = machineId;
}

public String getMachineName() {
return machineName;
}

public void setMachineName(String machineName) {
this.machineName = machineName;
}

}





public class Component
{
private long componentId;

private String componentName;

public Component() {
}

public long getComponentId() {
return componentId;
}

public void setComponentId(long componentId) {
this.componentId = componentId;
}

public String getComponentName() {
return componentName;
}

public void setComponentName(String componentName) {
this.componentName = componentName;
}

}


And some sql to create the tables and insert some sample data:
CREATE TABLE MACHINES
(
MACHINE_ID NUMBER,
MACHINE_NAME VARCHAR2(150 BYTE)
);


CREATE UNIQUE INDEX PK_MACHINES ON MACHINES
(MACHINE_ID)
LOGGING
NOPARALLEL;


ALTER TABLE MACHINES ADD (
CONSTRAINT PK_MACHINES PRIMARY KEY (MACHINE_ID));

CREATE TABLE COMPONENTS
(
COMPONENT_ID NUMBER,
COMPONENT_NAME VARCHAR2(150 BYTE)
);


CREATE UNIQUE INDEX PK_COMPONENTS ON COMPONENTS
(COMPONENT_ID)
LOGGING
NOPARALLEL;


ALTER TABLE COMPONENTS ADD (
CONSTRAINT PK_COMPONENTS PRIMARY KEY (COMPONENT_ID));

CREATE TABLE machine_component_assns
(
MACHINE_ID NUMBER,
COMPONENT_ID NUMBER
);

insert into machines (machine_id, machine_name) values (1, 'lexus');
insert into machines (machine_id, machine_name) values (2, 'ford taurus');
insert into machines (machine_id, machine_name) values (3, 'computer');

insert into components (component_id, component_name) values (1, 'spark plug');
insert into components (component_id, component_name) values (2, 'display');
insert into components (component_id, component_name) values (3, 'cpu fan');

insert into machine_component_assns (machine_id, component_id) values (1, 1);
insert into machine_component_assns (machine_id, component_id) values (2, 1);
insert into machine_component_assns (machine_id, component_id) values (1, 2);
insert into machine_component_assns (machine_id, component_id) values (3, 2);
insert into machine_component_assns (machine_id, component_id) values (3, 3);