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
|
