Problem using fetch="select" with associations

sanastos · **Joined:** Wed Oct 07, 2009 4:52 pm **Posts:** 2

I've stumbled across a configuration which generates invalid SQL, and I think it should not.

Code:

<hibernate-mapping package="test" schema="test">
    <class name="Car" table="Car" lazy="false">
        <id name="id" column="id" type="long" unsaved-value="0" >
            <generator class="native"/>
        </id>
    </class>

    <class name="Parent" table="Parent">
        <id name="id" column="id" type="long" unsaved-value="0" >
            <generator class="native"/> 
        </id>
        <discriminator column="parentType" />

        <subclass name="Father" discriminator-value="FATHER">
            <join table="Father" fetch="select">
                <key column="id" />
                <many-to-one name="car" column="carId" />
            </join>
        </subclass>
    </class>
</hibernate-mapping>

There's nothing fancy about anything in my setup, and the classes are dumb beans containing only the properties shown.

As a test, I insert a new Car, then create a new Father, set the Car on the Father, and insert it. Then I try to retrieve the Father using the generated Id:

Code:

Car car = new Car();
session.saveOrUpdate(car);

Father newFather = new Father();
newFather.setCar(car);
session.saveOrUpdate(newFather);

session.evict(car);
session.evict(newFather);

Parent retrievedParent = (Parent) session.load(Parent.class, newFather.getId());

It generates the following output:

Quote:

Hibernate: insert into test.Car values ( )
Hibernate: insert into test.Parent (parentType) values ('FATHER')
Hibernate: insert into test.Father (carId, id) values (?, ?)
Hibernate: select parent0_.id as id30_1_, parent0_.parentType as parentType30_1_, car1_.id as id29_0_ from test.Parent parent0_ left outer join test.Car car1_ on parent0_1_.carId=car1_.id where parent0_.id=?
<snip>
org.hibernate.exception.SQLGrammarException: could not load an entity: [test.Parent#10]
<snip>
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'parent0_1_.carId' in 'on clause'

What I expect it to do is to join against Parent on id, then join against Father AND Car on id. Instead, it joins Car and Parent using the carId column which is only located on Father.

I can do several things to fix this issue:
1) Add fetch="select" to the many-to-one mapping for car on Father
2) Remove lazy="false" on Car
3) Remove fetch="select" on Father

I believe this is a bug. If the mapping is invalid, then it should fail during parse time, not run time.

I'm using hibernate 3.3.2 and MySql. For reference, here is the database schema for the test:

Code:

CREATE TABLE Parent (
  id int(11) not null auto_increment,
  parentType varchar(20) not null,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE Car (
   id int(11) not null auto_increment,
   PRIMARY KEY (id)
) ENGINE=InnoDB;


CREATE TABLE Father (
  id int(11) not null,
  carId int(11) not null,
  PRIMARY KEY (id),
  CONSTRAINT parentIdFK FOREIGN KEY (id) REFERENCES Parent (id),
  CONSTRAINT carIdFK FOREIGN KEY (carId) REFERENCES Car (id)
) ENGINE=InnoDB;

Froestel · **Posted:** Wed Oct 07, 2009 7:56 pm

Hi sanastos,

you are not right pertaining two issues:

Quote:

I've stumbled across a configuration which generates invalid SQL, and I think it should not.

No, the generated SQL is NOT erroneous.

Quote:

I believe this is a bug.

No, it's no bug.

You are probably not experienced enough with Hibernate. But you will get to it if you keep going on.

The problem is with your code line

Quote:

Code:

Parent retrievedParent = (Parent) session.load(Parent.class, newFather.getId());

You are trying to load a Parent entity and the parent does not "know" anything about Car entities since it is a property of the Father entity. This is the same if you'd try to call Parent.getCars() - it doesn't work since getCars() is a method of the Father sub-class.

Give this code line a try:

Quote:

Code:

Parent retrievedParent = (Parent) session.load(Father.class, newFather.getId());

In this case, you tell Hibernate to handle a Father instance (which is also a Parent instance of course). So it should know about associated cars.

CU
Froestel

sanastos · **Joined:** Wed Oct 07, 2009 4:52 pm **Posts:** 2

I believe you are mistaken about what I am attempting to accomplish.

I think that querying based on Parent.class and an ID, using fetch="join", should work like this:
1) select * from Parent
2) Discover the discriminator value maps to the Father class/Table
3) select * from Father
4) Create a new Father(), fill in the values based on the results from (1) and (3)

The fact that I'm supplying Parent.class and not Father.class is irrelevant. True, Father.class should hint to hibernate that it should attempt to load from Parent and Father, but if you already know what type you're retrieving, that makes the discriminator kind of pointless.

To prove to you that something is wrong, I added another test where I attempt to save/load a simple Parent object.

Code:

    @Test
    public void testSaveAndLoadParent() {
        Session session = sessionFactory.getCurrentSession();

        Parent newParent = new Parent();
        session.saveOrUpdate(newParent);

        session.evict(newParent);

        Parent retrievedParent = (Parent) session.load(Parent.class, newParent.getId());
        log.info(retrievedParent);
    }

This also generates invalid SQL that joins against Car.

As additional proof, I would like to point out that this works fine if I remove fetch="select" from the Father class join. Here's the SQL that generates:

Quote:

Hibernate: select parent0_.id as id30_1_, parent0_1_.carId as carId31_1_, parent0_.parentType as parentType30_1_, car1_.id as id29_0_ from test.Parent parent0_ left outer join test.Father parent0_1_ on parent0_.id=parent0_1_.id left outer join test.Car car1_ on parent0_1_.carId=car1_.id where parent0_.id=?

Froestel · **Posted:** Sat Oct 10, 2009 4:58 am

Hi sanastos,

hm, I see what you are up to. And you are right, one would expect the join to happen on the Father.car_id = Car.id triggered by the discriminator value. But this does not seem to be the case.

I ran a test and discovered even more strange things. I have an abstract parent that has five sub-classes mapped

Code:

like you did.
When I issue a Session.load(Parent.class, some_id) where some_id is the identifier of an instance of a sub-class the generated SQL left outer joins the tables of all sub-classes on the common id. So the discriminator is not used for actually discriminating when setting up the query. I wonder why that is, I think there might be a reason for that. But this is a huge performance issue, I think.

CU
Froestel