JPA Inheritance: Query fails with unknown column

Question

Executing the following JPA query while using EclipseLink v2.7.4:

SELECT pr FROM AbstractProduct pr WHERE pr.shelve.superMarket.id = :1 ORDER BY pr.sortOrder

Gives the following error:

Unknown column 't0.SORTORDER' in 'order clause'
Error Code: 1054
Call: SELECT t2.ID, t2.SORTORDER, t2.SHELVE_ID FROM APPLE t2, SHELVE t1 WHERE ((t1.SUPERMARKET_ID = ?) AND (t1.ID = t2.SHELVE_ID)) ORDER BY t0.SORTORDER
    bind => [12]

The query is refering to t0 but nowhere in the generated query does it define which table t0 is.

These are the entities that I'm using:

@Entity
public class SuperMarket {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long id;

    @OneToMany(mappedBy = "superMarket")
    List<Shelve> shelves;
}

@Entity
public class Shelve {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long id;

    protected SuperMarket superMarket;

    @OneToMany(mappedBy = "shelve")
    protected List<AbstractProduct> products;
}

@Entity
@Inheritance(strategy = InheritanceType.TABLE_PER_CLASS)
public abstract class AbstractProduct {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long id;

    protected Shelve shelve;

    protected long sortOrder;
}

@Entity
public class Apple extends AbstractProduct {

}

@Entity
public class Banana extends AbstractProduct {

}

Which results in the following queries:

CREATE TABLE SUPERMARKET (ID BIGINT NOT NULL, PRIMARY KEY (ID));
CREATE TABLE SHELVE (ID BIGINT NOT NULL, SUPERMARKET_ID BIGINT, PRIMARY KEY (ID));
CREATE TABLE APPLE (ID BIGINT NOT NULL, SORTORDER BIGINT, SHELVE_ID BIGINT, PRIMARY KEY (ID));
CREATE TABLE BANANA (ID BIGINT NOT NULL, SORTORDER BIGINT, SHELVE_ID BIGINT, PRIMARY KEY (ID));
CREATE TABLE ABSTRACTPRODUCT (SHELVE_ID BIGINT);

The last table ABSTRACTPRODUCT should not be created since it is an Abstract Java Entity and I'm using the table per class inheritance style. This seems to be a bug in eclipselink it is also discussed in this question: Understanding of TABLE_PER_CLASS with keys in eclipselink It is the combination of the inheritance with the OneToMany relations that seems to trigger the create table statement. Not sure if this bug is related to the query error that I mentioned at the start. I think not as this table doesn't even have the sort order field.

When I remove the ORDER BY clause the query will execute succesfully. When I change the query to only go one level up it will also execute succesfully:

SELECT pr FROM AbstractProduct pr WHERE pr.shelve.id = :1 ORDER BY pr.sortOrder

For a test I got rid of the inheritance and let the Shelve entity have a OneToMany relation to Apple directly, where Apple did not extend any other class, in that case the query is also executed succesfully. But I need the abstract class and inheritance.

Any idea why the generated query is wrong in this case?

As is noted in an answer below I could use a different inheritance strategy to solve this problem in a different way. I choose the table per class type because that allows me to use the abstract entity in queries and the concrete classes get a table with all the fields in it. I was hoping that this helps with performance when doing a lot of inserts and selects from the concrete classes because that would only involve a single db table.

Update I think this is an error in EclipseLink I have created two bugreports: https://bugs.eclipse.org/bugs/show_bug.cgi?id=549866 for the abstract class creating a table https://bugs.eclipse.org/bugs/show_bug.cgi?id=549868 for the error in the query

Answer 1

I was able to reproduce your problem with the inheritance strategy TABLE_PER_CLASS. The query executed as expected once I changed it to InheritanceType.JOINED and recreated the schema of the database (in my case: PostgreSQL 10.9).

So the code should be changed to:

@Entity
@Inheritance(strategy = InheritanceType.JOINED)
public abstract class AbstractProduct {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long id;

    @ManyToOne
    protected Shelve shelve;

    protected long sortOrder;
}

Note well:

1. I added the missing @ManyToOne annotation to the attribute shelve.
2. @ManyToOne should also be added to protected SuperMarket superMarket; in the entity Shelve.

The resulting DB schema looks as follows:

CREATE TABLE public.abstractproduct
(
    id bigint NOT NULL,
    dtype character varying(31) COLLATE pg_catalog."default",
    sortorder bigint,
    shelve_id bigint,
    CONSTRAINT abstractproduct_pkey PRIMARY KEY (id),
    -- FK definitions left out for brevity
)

And Apple, for instance, becomes:

CREATE TABLE public.apple
(
    id bigint NOT NULL,
    CONSTRAINT apple_pkey PRIMARY KEY (id),
        -- FK definitions left out for brevity
)

Hope it helps.