HIbernate generates wrong query with multiple joins (for many-to-many) for MySQL [closed]

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center. Closed 11 years ago.

I'm having troubles with Hibernate + MySQL. Initially the problem appeared in system we develop and later I've managed to r开发者_如何转开发eproduce it in small test project.

So, there are 4 entities: Person, Event, City, Currency. Each entity except Currency has many-to-many unidirectional relationship with the next one: Person has many Events, Event has many Cities, City has many Currencies. Hibernate configuration is done via annotations.

This is how City looks, other entities are constructed in the same way.

@Entity
@Table(name = "CITY")
public class City {
    private Long id;
    private String name;
    private Set<Currency> currencies = new HashSet<Currency>();

    // setters, getters...

    @ManyToMany
    @JoinTable(name = "CITY_CURRENCY", 
            joinColumns = @JoinColumn(name="CITY_ID"),
            inverseJoinColumns = @JoinColumn(name="CURRENCY_ID"))
    public Set<Currency> getCurrencies() {
        return currencies;
    }
}

Okay, now to the point. Suppose, I want get all information about Persons, i.e. to query all tables, joining them together with left joins starting from Person.

HQL query for this case would look like this:

from Person as person 
left outer join person.events as event
left outer join event.cities as city 
left outer join city.currencies as currency

And this works. But we use criterias, so I'm composing analogous (as I thought) query using criteria API:

Criteria crit = session.createCriteria(Person.class, "person");
crit.createAlias("person.events", "event", Criteria.LEFT_JOIN);
crit.createAlias("event.cities", "city", Criteria.LEFT_JOIN);
crit.createAlias("city.currencies", "currency", Criteria.LEFT_JOIN);

crit.setProjection(
    Projections.projectionList()
        .add(Property.forName("person.firstname"), "name")
        .add(Property.forName("event.title"), "event")
        .add(Property.forName("city.name"), "city")
        .add(Property.forName("currency.name"), "currency")
);

But this code fails with an error "Unknown column 'currency3_.name' in 'field list'". What is happening is that Hibernate does not generate the last JOIN statement. Here is a query generated from criteria:

select this_.firstname as y0_, event1_.title as y1_, city2_.name as y2_, 
currency3_.name as y3_ from Person this_ 
left outer join PERSON_EVENT events5_ on this_.PERSON_ID=events5_.PERSON_ID 
left outer join Event event1_ on events5_.EVENT_ID=event1_.EVENT_ID 
left outer join EVENT_CITY cities7_ on event1_.EVENT_ID=cities7_.EVENT_ID 
left outer join CITY city2_ on cities7_.elt=city2_.CITY_ID 
left outer join CITY_CURRENCY currencies9_ on city2_.CITY_ID=currencies9_.CITY_ID

As you see there is no JOIN to CURRENCY table. Weird, huh?

This happens specifically with MySQL, on HSQLDB the previous code runs fine. This does not happen with less number of entities, only with four ones or more (with five entities query is "cut" in the same point).

The question is: what am I doing wrong? What can be done to fix this? I'm most interested in the nature of this bug (is it me or Hibernate?). Sure, if this is HB bug indeed, then I'll have to separate queries, use HQL or anything else.

[CLOSED] It seems that Hibernate community was aware of this bug and fixed it in the latest and greatest. Changed from 3.5.1 to 3.6.3 and bug was gone.