Hibernate Criteria query lists tables in wrong order in generated SQL

Question

I have a Criteria query using several joins, and the generated SQL lists the tables out of order so that an ON clause refers to a table that hasn't been declared yet.

To reproduce the problem, I created a small data model with three tables: Bill, Event, and a junction table BillEvent (I've listed a runnable JUnit test with entity definitions at the end of the question). The following Criteria query fails with a syntax error because event1 is declared after it's referenced. How can I rewrite this query so that the tables are declared in the right order?

// Get the most recent BillEvent for a bill
final Criteria criteria = session.createCriteria(BillEvent.class, "be1")
                    .createCriteria("event", "event1")
                    .createCriteria("be1.bill")
                    .add(Restrictions.eq("id", billId))
                    .createCriteria("billEvents", "be2")
                    .createCriteria("event", "event2", JoinType.LEFT_OUTER_JOIN,
                            Restrictions.ltProperty("event1.time", "time"))
                    .add(Restrictions.isNull("event2.id"));

The error:

Caused by: org.h2.jdbc.JdbcSQLException: Column "EVENT1X1_.TIME" not found; SQL statement:

select 
    this_.id as id1_1_4_, 
    this_.billId as billId3_1_4_, 
    this_.eventId as eventId4_1_4_, 
    this_.note as note2_1_4_, 
    hibernatej2_.id as id1_0_0_, 
    hibernatej2_.label as label2_0_0_, 
    be2x3_.id as id1_1_1_, 
    be2x3_.billId as billId3_1_1_, 
    be2x3_.eventId as eventId4_1_1_, 
    be2x3_.note as note2_1_1_, 
    event2x4_.id as id1_2_2_, 
    event2x4_.time as time2_2_2_, 
    event1x1_.id as id1_2_3_, 
    event1x1_.time as time2_2_3_ 
from 
    test.billEvent this_ 
    inner join test.bill hibernatej2_ on this_.billId=hibernatej2_.id 
    inner join test.billEvent be2x3_ on hibernatej2_.id=be2x3_.billId 
    left outer join test.event event2x4_ 
        on be2x3_.eventId=event2x4_.id 
        and ( event1x1_.time<event2x4_.time ) 
    inner join test.event event1x1_ on this_.eventId=event1x1_.id 
where 
    hibernatej2_.id=? 
    and event2x4_.id is null

JUnit test using Hibernate 5 and H2:

package com.stackoverflow.repro;

import static javax.persistence.GenerationType.IDENTITY;

import java.sql.Timestamp;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.OneToMany;
import javax.persistence.Table;
import javax.persistence.UniqueConstraint;

import org.h2.Driver;
import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.boot.Metadata;
import org.hibernate.boot.MetadataSources;
import org.hibernate.boot.registry.StandardServiceRegistry;
import org.hibernate.boot.spi.MetadataImplementor;
import org.hibernate.cfg.Configuration;
import org.hibernate.cfg.Environment;
import org.hibernate.criterion.Restrictions;
import org.hibernate.dialect.H2Dialect;
import org.hibernate.sql.JoinType;
import org.hibernate.tool.hbm2ddl.SchemaExport;
import org.junit.Assert;
import org.junit.Rule;
import org.junit.Test;
import org.junit.rules.TestName;

public class HibernateJoinTest {
    private static final String TEST_CATALOG = "test";

    @Rule public TestName name = new TestName();

    @Entity
    @Table(name = "bill", catalog = TEST_CATALOG)
    public static class Bill implements java.io.Serializable {
        private Integer id;
        private String label;
        private Set<BillEvent> billEvents = new HashSet<BillEvent>(0);

        public Bill() {
        }

        public Bill(String label) {
            this.label = label;
        }

        public Bill(String label, Set<BillEvent> billEvents) {
            this.label = label;
            this.billEvents = billEvents;
        }

        @Id
        @GeneratedValue(strategy = IDENTITY)
        @Column(name = "id", unique = true, nullable = false)
        public Integer getId() {
            return this.id;
        }

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

        @Column(name = "label", unique = true, nullable = false, length = 45)
        public String getLabel() {
            return this.label;
        }

        public void setLabel(String label) {
            this.label = label;
        }

        @OneToMany(fetch = FetchType.LAZY, mappedBy = "bill", cascade = { CascadeType.ALL })
        public Set<BillEvent> getBillEvents() {
            return this.billEvents;
        }

        public void setBillEvents(Set<BillEvent> billEvents) {
            this.billEvents = billEvents;
        }
    }

    @Entity
    @Table(name = "event", catalog = TEST_CATALOG)
    public static class Event implements java.io.Serializable {
        private Integer id;
        private Timestamp time;
        private Set<BillEvent> billEvents = new HashSet<>(0);

        public Event() {
        }

        public Event(Timestamp time) {
            this.time = time;
        }

        public Event(Timestamp time, Set<BillEvent> billEvents) {
            this.time = time;
            this.billEvents = billEvents;
        }

        @Id
        @GeneratedValue(strategy = IDENTITY)
        @Column(name = "id", unique = true, nullable = false)
        public Integer getId() {
            return this.id;
        }

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

        @Column(name = "time", nullable = false)
        public Timestamp getTime() {
            return this.time;
        }

        public void setTime(Timestamp time) {
            this.time = time;
        }

        @OneToMany(fetch = FetchType.LAZY, mappedBy = "event", cascade = { CascadeType.ALL })
        public Set<BillEvent> getBillEvents() {
            return this.billEvents;
        }

        public void setBillEvents(Set<BillEvent> billEvents) {
            this.billEvents = billEvents;
        }
    }

    @Entity
    @Table(name = "billEvent", catalog = TEST_CATALOG, uniqueConstraints = @UniqueConstraint(columnNames = {"billId", "eventId"}) )
    public static class BillEvent implements java.io.Serializable {

        private Integer id;
        private Bill bill;
        private Event event;
        private String note;

        public BillEvent() {
        }

        public BillEvent(Bill bill, Event event) {
            this.bill = bill;
            this.event = event;
        }

        public BillEvent(Bill bill, Event event, String note) {
            this.bill = bill;
            this.event = event;
            this.note = note;
        }

        @Id
        @GeneratedValue(strategy = IDENTITY)
        @Column(name = "id", unique = true, nullable = false)
        public Integer getId() {
            return this.id;
        }

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

        @ManyToOne(fetch = FetchType.LAZY, cascade = { CascadeType.ALL })
        @JoinColumn(name = "billId", nullable = false)
        public Bill getBill() {
            return this.bill;
        }

        public void setBill(Bill bill) {
            this.bill = bill;
        }

        @ManyToOne(fetch = FetchType.LAZY, cascade = { CascadeType.ALL })
        @JoinColumn(name = "eventId", nullable = false)
        public Event getEvent() {
            return this.event;
        }

        public void setEvent(Event event) {
            this.event = event;
        }

        @Column(name = "note", unique = true, nullable = false, length = 120)
        public String getNote() {
            return this.note;
        }

        public void setNote(String note) {
            this.note = note;
        }
    }

    @Test
    public void testOuterJoin() {
        final SessionFactory sessionFactory = createSessionFactory();

        final String label = "B0001";
        final Timestamp ts = new Timestamp(System.currentTimeMillis());
        final Timestamp ts2 = new Timestamp(ts.getTime() + 1000);
        final String note1 = "First note";
        final String note2 = "Second note";

        final int billId;

        try (final Session session = sessionFactory.openSession();) {
            final Transaction tx = session.beginTransaction();

            final Bill bill = new Bill(label);
            session.save(bill);
            billId = bill.getId();

            final Event event1 = new Event(ts);
            session.save(event1);

            final Event event2 = new Event(ts2);
            session.save(event2);

            session.save(new BillEvent(bill, event1, note1));
            session.save(new BillEvent(bill, event2, note2));

            session.flush();
            tx.commit();
        }

        try (final Session session = sessionFactory.openSession()) {
            final Criteria criteria = session.createCriteria(BillEvent.class, "be1")
                    .createCriteria("event", "event1")
                    .createCriteria("be1.bill")
                    .add(Restrictions.eq("id", billId))
                    .createCriteria("billEvents", "be2")
                    .createCriteria("event", "event2", JoinType.LEFT_OUTER_JOIN,
                            Restrictions.ltProperty("event1.time", "time"))
                    .add(Restrictions.isNull("event2.id"));


            @SuppressWarnings("unchecked")
            final List<BillEvent> results = criteria.list();

            Assert.assertEquals(1, results.size());

            final BillEvent billEvent = results.get(0);
            Assert.assertEquals(note2, billEvent.getNote());
            Assert.assertEquals(ts2, billEvent.getEvent().getTime());
        }
    }

    private SessionFactory createSessionFactory() {
        final String dialectClassName = H2Dialect.class.getName();
        final Configuration config = 
                new Configuration()
                .addAnnotatedClass(Bill.class)
                .addAnnotatedClass(Event.class)
                .addAnnotatedClass(BillEvent.class);

        final String dbName = name.getMethodName();

        config.setProperty(Environment.DIALECT, dialectClassName);
        config.setProperty(Environment.DRIVER, Driver.class.getName());
        config.setProperty(Environment.URL, "jdbc:h2:mem:"+dbName+";DB_CLOSE_DELAY=-1;INIT=CREATE SCHEMA IF NOT EXISTS TEST\\; SET SCHEMA TEST");
        config.setProperty(Environment.USER, "SA");
        config.setProperty(Environment.PASS, "");
        config.setProperty(Environment.SHOW_SQL, "true");
        config.setProperty(Environment.FORMAT_SQL, "true");

        final StandardServiceRegistry serviceRegistry = config.getStandardServiceRegistryBuilder().applySettings(config.getProperties()).build();

        final MetadataSources sources = 
                new MetadataSources(serviceRegistry)
                .addAnnotatedClass(Bill.class)
                .addAnnotatedClass(Event.class)
                .addAnnotatedClass(BillEvent.class);

        final Metadata metadata = sources.buildMetadata();

        final SchemaExport export = new SchemaExport((MetadataImplementor) metadata);
        export.create(false, true);

        final SessionFactory sessionFactory = config.buildSessionFactory();
        return sessionFactory;
    }
}

Edit: The issue here seems to be that Hibernate enumerates the tables alphabetically by their propery name. So if there were the following joins:

from root
inner join root.z
inner join root.z.b
inner join root.z.a
inner join root.a on (... and root.z.prop = root.a.prop)

The generated order would be

from root
inner join root.a on (... and root.z.prop = root.a.prop)
inner join root.z
inner join root.z.a
inner join root.z.b

Renaming BillEvent.bill to BillEvent.zBill (or anything alphabetically after event) fixes the syntax error in this query. This is not scalable, though: if you want to query from the other side of the junction table, that query will fail because it's now alphabetically out of order.

Beatriz

Try using "createAlias" instead of "createCriteria" – Dex

@Dex That does not work either. At least not with using the provided testcase at derby:memory-db. – Trinee

I suppose you are not interested in a workaround for that specific query but in a general solution? – Trinee

@Trinee - Yes, I could write this as a one-off in HQL or use JOOQ, but I'd prefer Criteria because I have a lot of similar queries to convert and it seems less brittle. – Beatriz

@Beatriz Ok :-) I solved this one using criteria, but I don't think this is a general pattern: Maybe it helps: final Criteria criteria = session.createCriteria(BillEvent.class, "be").createAlias("event", event").createCriteria("bill").add(Restrictions.eq("id", billId)).addOrder(Order.desc("event.time")); – Trinee

I've edited the question to reflect my findings so far. A query with the same structure worked with a different set of tables, which was puzzling... it seems to be that the joins are enumerated alphabetically. – Beatriz

Is your model like this : Many-to-many tables (bill, event) + extra columns in join table (billevent) ? I gotta feeling it's your use case. If I'm right, by changing the model a little, I think it's possible to simplify the criteria request. Could you confirm please (or not)? – Unfix

The model above is just a toy model to illustrate the problem: Hibernate does not consider which properties appear in the ON clauses when it decides how to list joined tables. I read the source that generates the SQL from a Criteria query, and it's not a simple fix to enable queries like this. T – Beatriz

Would this query be a good candidate for a database view? – Gav

@Gav I actually do have some database views for queries like this. However, AFAIK a database view can't represent queries with similar structure like "get the most recent BillEvent as of date X." The restriction (event.time <= X) would apply to both event1 and event2 in the example query, and there's no way to represent that in a view (please correct me if I'm wrong). – Beatriz

@Beatriz You can always create the view without the restrictions where a join is done instead, then apply the restriction to the view. Whether or not that is going to be efficient is a different story. It depends on the size of the data set, complexity of the query and the way in which the database implements a query against the view. You don't want to bring back the whole database to fetch a few rows. It seems you have a good answer below with 2 options. Good luck. – Gav

Answer 1

When criteria is used hibernate actually traverses entity tree in Depth First Search way to build joins according to fields definition from configuration. In your case BillEvent is traversed bill first, then subfields of Bill class. So basically it creates event entity join after it creates all joins from bill association. You can define the order in hbm.xml but as you mentioned it's not very scalable.

So you have at least two options here:

1. Change criteria so root entity will be different, then add projection and result transformer to fetch BillEvent entities. For instance:

   final Criteria criteria = session.createCriteria(Event.class, "event1")
                   .createCriteria("event1.billEvents", "be1")
                   .createCriteria("be1.bill", "bill1")
                   .createCriteria("bill1.billEvents", "be2")
                   .createCriteria("be2.event", "event2", JoinType.LEFT_OUTER_JOIN,
                           Restrictions.ltProperty("event1.time", "event2.time"))
                   .add(Restrictions.eq("be1.id", billId))
                   .add(Restrictions.isNull("event2.id"))
                   .setProjection(Projections.projectionList()
                       .add(Projections.property("be1.event"), "event")
                       .add(Projections.property("be1.note"), "note"))
                   .setResultTransformer(Transformers.aliasToBean(BillEvent.class));
   

2. Another possible option is nto use hql rather then criteria api. It will allow you to control join order directly from request since it uses a different sql build mechanism.

Crestfallen