Incorrect SQL generated for Polymorpic Queries involving TYPE() with @Inheritance(strategy=InheritanceType.JOINED)

Description

For @Inheritance(strategy=InheritanceType.JOINED), three forms of polymorphic queries involving TYPE() generated bad SQL Statements involving outer joins.

These queries execute correctly for @Inheritance(strategy=InheritanceType.SINGLE_TABLE)

EXAMPLE OF FAILURE:

e.g. for
public static final String JPQL_QUERY_FIND_ALL_PERSONS_USING_IN =
"SELECT p"
+ " FROM Party p"
+ " WHERE TYPE (p) IN ("
+ " Person"
+ " )";

The generated SQL fails as below:

Hibernate:
select
party0_.id as id2_1_,
...
from
Party party0_
left outer join
Person party0_1_
on party0_.id=party0_1_.id
left outer join
Organisation party0_2_
on party0_.id=party0_2_.id
where
case
when party0_1_.id is not null then Person
when party0_2_.id is not null then Organisation
when party0_.id is not null then 'Party'
end='Person'
Aug 23, 2014 2:26:43 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
WARN: SQL Error: 42122, SQLState: 42S22
Aug 23, 2014 2:26:43 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
ERROR: Column "PERSON" not found; SQL statement:

EXAMPLE PROJECT:
Exported Eclipse m2e example project/test (source only) attached.

Environment

Otacle Linux 6.5 64 Bit (VirtualBox VM on Windows 7 Pro 64)
java version "1.7.0_55"
Java(TM) SE Runtime Environment (build 1.7.0_55-b13)
Java HotSpot(TM) 64-Bit Server VM (build 24.55-b03, mixed mode)
Hibernate 4.3.6.Final (also 4.3.5 in WildFly 8.1 Final)
H2 1.4.181 In-memory

Activity

Show:
Ingo E
February 18, 2015, 6:28 PM
Edited

Hello,

I am in the midst of porting a project from Glassfish to Wildfly 8.1.
This is a maven-project.

This is my environment:

$ mvn -ver
mvn Apache Maven 3.0.5
Maven home: /usr/share/maven
Java version: 1.7.0_75, vendor: Oracle Corporation
Java home: /usr/lib/jvm/java-7-openjdk-amd64/jre
Default locale: en_US, platform encoding: UTF-8
OS name: "linux", version: "3.13.0-45-generic", arch: "amd64", family: "unix"

I am using eclipselink as the JPA-implmentation.
Tested the connection, saved and retrieved an entity from the database - so that is working.
This is a snippet from my pom.xml

<dependency>
<groupId>org.eclipse.persistence</groupId>
<artifactId>eclipselink</artifactId>
<version>2.5.2</version>
</dependency>
<dependency>
<groupId>org.eclipse.persistence</groupId>
<artifactId>org.eclipse.persistence.jpa.modelgen.processor</artifactId>
<version>2.5.2</version>
<scope>provided</scope>
</dependency>

And my JBOSS_SERVER holds : wildfly-8.1.0.Final/modules/system/layers/base/org/eclipse/persistence/main
1. eclipselink-2.5.2.jar
2. jipijapa-eclipselink-1.0.1.Final.jar
3. module.xml

Here is my super-class:

@Entity
@Table(name = "MEDIA")
@NamedQueries({
@NamedQuery(name = "Media.FindByUuid", query = "SELECT c FROM Media c where c.uuid = :uuid"),
})
@Inheritance(strategy = InheritanceType.JOINED)
public abstract class Media implements Serializable {

and here is my child-class:

@Entity
@Table(name = "IMAGE")
@NamedQueries({
@NamedQuery(name = Image.FIND_ALL, query = "SELECT i FROM Image i")
})
@XmlRootElement
public class Image extends Media implements Serializable {

The following is not working and giving me 'null' in Wildfly, but working in Glassfish.

Query query = em.createNamedQuery("Media.FindByUuid");
// hardcoding the UUID, which I have in my database :
query.setParameter("uuid", "000d8f7c-6c7b-4c56-9799-ac67666462b5");
T media = (T) query.getSingleResult();

Not understanding the advises given in this thread.
Could you help me to narrow my troubleshooting ?
am I doing something wrong or ...
Should I upgrade eclipselink ? Should I go for Wildfly 8.2 ?
Should I change Inheritance-strategy ?

regards, i

Ephemeris Lappis
June 11, 2015, 3:33 PM

Hello.

We've just discovered how Hibernate uses (or does not, in reality) the discriminator column to handle polymorphic requests on joined strategy inheritance, and the wrong generated code.

After reading this post and the HHH-6911, we've tested adding the property "hibernate.discriminator.ignore_explicit_for_joined=true". This makes the code work, but Hibernate still generates "huge" queries, building a join on the tables for all the classes of the hierarchy to determine the real type from the presence of the primary key in the matching table. In our case, about 20 tables while the query doesn't need to actually retrieve data or return objects.

The query is something like this :

We expected a SQL query like :

where "kind" is the discriminator column, and not a 20 tables "join" or a 20 lines "case"...

As said before, this works, but unfortunately this also leads to performance issues.

Is there another workaround (another property ?) to make Hibernate actually use the discriminator column ?

Thanks.

Regards.

Christian Beikov
August 15, 2015, 10:57 AM

Looks like a straight forward fix. Any reasons for not merging the PR?

Steve Ebersole
January 12, 2016, 11:53 PM

You applied this PR right?

Brett Meyer
January 13, 2016, 12:46 AM

Yep, not sure why I didn't resolve it...

Assignee

Brett Meyer

Reporter

Jim Nicolson

Fix versions

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

backportDecision

None

Components

Affects versions

Priority

Major
Configure