[Hibernate-issues] [Hibernate-JIRA] Created: (HHH-1802) HQL select for table-per-subclass hierarchy

SourceForge Headquarters 1320 Columbia Street Suite 310 San Diego, CA 92101 +1 (858) 422-6466

HQL select for table-per-subclass hierarchy attempts to use superclass column on subclass table with implicit join
------------------------------------------------------------------------------------------------------------------

         Key: HHH-1802
         URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-1802
     Project: Hibernate3
        Type: Bug

  Components: query-hql  
    Versions: 3.2.0.cr2    
 Environment: Issue tested on DB2 and HSQL using hibernate-3.2.0.cr2, hibernate-annotations-3.2.0.cr1 and hibernate-entitymanager-3.2.0.cr1
    Reporter: Paul Kneeland

Attempting to do queries using parts of the superclass in a where clause fails to join in the superclass and instead applies the property to the subclass table and gets sql exception.

I boiled it down to a simple test with 4 classes:
Animal (abstract superclass with own table)
Cat (joined subclass of Animal)
Dog (joined subclass of Animal)
Family (can contain list of cats and list of dogs)
Test (executes some queries)

em.createQuery("SELECT animal FROM Animal animal WHERE animal.age > 5"); // works
em.createQuery("SELECT cat FROM Cat cat WHERE cat.age > 5"); // works
em.createQuery("SELECT dog FROM Dog dog WHERE dog.age > 5"); // works
em.createQuery("SELECT family FROM Family family WHERE family.dogs.age > 10"); // Fails applies age column to dog table instead of animal
em.createQuery("SELECT family FROM Family family JOIN family.dogs AS dog WHERE dog.age > 10"); // Works

My classes are:
import org.apache.commons.lang.builder.ToStringBuilder;

import javax.persistence.*;

/**
 */
@Entity
@Inheritance(strategy = InheritanceType.JOINED)
public abstract class Animal {
    private Integer id;
    private String name;
    private Integer age;

    protected Animal() {
    }

    protected Animal(String name, Integer age) {
        this.name = name;
        this.age = age;
    }

    @Id
    @GeneratedValue(strategy= GenerationType.IDENTITY)
    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String toString() {
        return ToStringBuilder.reflectionToString(this);
    }
}

import javax.persistence.Entity;

/**
 */
@Entity
public class Cat extends Animal {
    private Integer livesLeft;

    public Cat() {
        livesLeft = 9;
    }

    public Cat(String name, Integer age, Integer livesLeft) {
        super(name, age);
        this.livesLeft = livesLeft;
    }

    public Integer getLivesLeft() {
        return livesLeft;
    }

    public void setLivesLeft(Integer livesLeft) {
        this.livesLeft = livesLeft;
    }
}

import org.apache.commons.lang.builder.ToStringBuilder;

import javax.persistence.Entity;

/**
 */
@Entity
public class Dog extends Animal {
    private Boolean floppyEars;

    public Dog() {
    }

    public Dog(String name, Integer age, Boolean floppyEars) {
        super(name, age);
        this.floppyEars = floppyEars;
    }

    public Boolean getFloppyEars() {
        return floppyEars;
    }

    public void setFloppyEars(Boolean floppyEars) {
        this.floppyEars = floppyEars;
    }
}

import org.apache.commons.lang.builder.ToStringBuilder;

import javax.persistence.*;
import java.util.List;

/**
 */
@Entity
public class Family {
    private Integer id;
    private String name;
    private List<Cat> cats;
    private List<Dog> dogs;

    public Family() {
    }

    public Family(String name) {
        this.name = name;
    }

    @Id
    @GeneratedValue(strategy= GenerationType.IDENTITY)
    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @OneToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    @JoinColumn(name="family_id")
    public List<Cat> getCats() {
        return cats;
    }

    public void setCats(List<Cat> cats) {
        this.cats = cats;
    }

    @OneToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    @JoinColumn(name="family_id")
    public List<Dog> getDogs() {
        return dogs;
    }

    public void setDogs(List<Dog> dogs) {
        this.dogs = dogs;
    }

    public String toString() {
        return ToStringBuilder.reflectionToString(this);
    }
}

import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.EntityManager;
import java.util.ArrayList;

/**
 */
public class Test {
    public static void main(String[] args) {
        EntityManagerFactory factory = Persistence.createEntityManagerFactory("test");
        EntityManager em = factory.createEntityManager();

        // Populate the database...
        // Family 1
        Family fam1 = new Family("Jones");
        fam1.setCats(new ArrayList<Cat>());
        fam1.getCats().add(new Cat("Fluffy", 3, 9));
        fam1.getCats().add(new Cat("Fighter", 7, 3));
        fam1.setDogs(new ArrayList<Dog>());
        fam1.getDogs().add(new Dog("Fido", 15, false));

//        // Family 2
        Family fam2 = new Family("Smith");
        fam2.setDogs(new ArrayList<Dog>());
        fam2.getDogs().add(new Dog("buster", 11, true));

        // Write to the database
        em.getTransaction().begin();
        em.persist(fam1);
        em.persist(fam2);
        em.flush();
        em.getTransaction().commit();

        // Now do some selects...
        // Get all animals older than 5
        System.out.println("Animals older than 5: " + em.createQuery("SELECT animal FROM Animal animal WHERE animal.age > 5").getResultList());
        System.out.println("Cats older than 5: " + em.createQuery("SELECT cat FROM Cat cat WHERE cat.age > 5").getResultList());
        System.out.println("Dogs older than 5: " + em.createQuery("SELECT dog FROM Dog dog WHERE dog.age > 5").getResultList());
        // Get all families who have dogs with floppy ears...
        System.out.println("Families with floppy eared dogs: " + em.createQuery("SELECT family FROM Family family WHERE family.dogs.floppyEars = true").getResultList());
        // Get all families who have cats with less than their nine lives left
        System.out.println("Families with cats who have used more than half their lives: " + em.createQuery("SELECT family FROM Family family WHERE family.cats.livesLeft < 5").getResultList());
        // Get all families who have dogs that are older than 10 years.
// @todo broken version with implicit join
//        System.out.println("Families who have old dogs: " + em.createQuery("SELECT family FROM Family family WHERE family.dogs.age > 10").getResultList());
        System.out.println("Families who have old dogs: " + em.createQuery("SELECT family FROM Family family JOIN family.dogs AS dog WHERE dog.age > 10").getResultList());
    }
}

<persistence>
    <persistence-unit name="test">
        <provider>org.hibernate.ejb.HibernatePersistence</provider>
        <class>Animal</class>
        <class>Cat</class>
        <class>Dog</class>
        <properties>
            <property name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect"/>
            <property name="hibernate.hbm2ddl.auto" value="create-drop"/>
            <property name="hibernate.cache.provider_class" value="org.hibernate.cache.NoCacheProvider"/>

            <property name="hibernate.connection.driver_class" value="org.hsqldb.jdbcDriver"/>
            <property name="hibernate.connection.username" value="sa"/>
            <property name="hibernate.connection.password" value=""/>
            <property name="hibernate.connection.url" value="jdbc:hsqldb:mem:test"/>

            <property name="hibernate.max_fetch_depth" value="3"/>
            <property name="hibernate.show_sql" value="true"/>
            <property name="hibernate.format_sql" value="true"/>
        </properties>
    </persistence-unit>
</persistence>

Generated sql for family queries:
with implicit join (fails)
    select
        family0_.id as id3_,
        family0_.name as name3_ 
    from
        Family family0_,
        Dog dogs1_ 
    where
        family0_.id=dogs1_.family_id 
        and dogs1_1_.age>10

with explicit join (works)
    select
        family0_.id as id3_,
        family0_.name as name3_ 
    from
        Family family0_ 
    inner join
        Dog dogs1_ 
            on family0_.id=dogs1_.family_id 
    inner join
        Animal dogs1_1_ 
            on dogs1_.id=dogs1_1_.id 
    where
        dogs1_1_.age>10

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira

[Hibernate-issues] [Hibernate-JIRA] Created: (HHH-1802) HQL select for table-per-subclass hierarchy

An object relational-mapping (ORM) library for Java

[Hibernate-issues] [Hibernate-JIRA] Created: (HHH-1802) HQL select for table-per-subclass hierarchy attempts to use superclass column on subclass table with implicit join