Missing join when using sub-type in a predicate and InheritanceType.JOINED #2055

Open
Ramblurr opened this Issue Nov 23, 2016 · 1 comment

Projects

None yet

1 participant

@Ramblurr

Summary:

Given a base Person class, and an Employee subtype specified with InheritanceType.JOINED, along with a PersonContainer that has a relationship to a person, invalid sql is generated given the predicate:

QPersonContainer.personContainer.person.as(QEmployee.class).name.likeIgnoreCase(name)

Details:

// Base type
@Entity
@Inheritance(strategy = InheritanceType.JOINED)
@DiscriminatorColumn(discriminatorType = DiscriminatorType.STRING)
class Person {
  @Id
  Long id;
}

// Sub type
@Entity
@DiscriminatorValue("employee")
class Employee extends Person {
 
  @Column
  String name;
}

// The object I'm querying by
@Entity
class PersonContainer {
  @OneToOne
  private Person person;
}

I create a predicate with:

BooleanBuilder bb = new BooleanBuilder();
bb.and(QPersonContainer.personContainer.person.as(QEmployee.class).name.likeIgnoreCase(name))
personContainerRepository.findAll(bb);

The generated SQL looks like this:

SELECT count(personcont0_.id) AS col_0_0_
FROM db.person_container personcont0_
  CROSS JOIN db.person person1_
WHERE personcont0_.person_id = person1_.id
      AND (lower(person1_1_.name) LIKE '%mary%')

This fails with the root cause:

org.postgresql.util.PSQLException: ERROR: missing FROM-clause entry for table \"person1_1_\"\

You can see in the generated sql the error: person1_1_ is referenced but is not defined in a join anywhere. The query would work fine if this line was added after the cross join:

INNER JOIN db.employee as person1_1_ on person1_1_.id = person1_.id

Since I'm using a spring-data repository I don't have access to the JPQLQuery and can't specify the join myself. But anyways, it should be automatically created no?

@Ramblurr

I went ahead and tried this out with straight up querydsl:

JPQLQuery<PersonContainer> searchQuery = this.getQuerydsl().createQuery()
                .select(qPersonContainer)
                .from(qPersonContainer)
                .where(QPersonContainer.personContainer.person.as(QEmployee.class).name.likeIgnoreCase(name));

Here is the rendered JPQL:

select personContainer
from PersonContainer personContainer
where lower(personContainer.person.name) like ?1 escape '!'

This causes postgres to error missing FROM-clause entry for table "person1_1_"

Adding in an innerJoin fixes it: query.innerJoin(qPersonContainer.person, qEmployee._super);

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment