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.

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.