All,

We have just migrate from Hibernate 3.2.5 (GA) to Hibernate 3.6.7 (Final).
After updating dialect (we were using Sybase 12.5 with SybaseDialect and migrated to Sybase ASE 15 with SybaseASE15Dialect) and 2-3 Maven dependances (slf4j-log12,...) everything (almost...) seems to be working.

But, we discovered, with an initially huge query, that this version of Hibernate duplicates some joins (inner joins in our case), which may lead to overtake Sybase (specifications...) limits of 50 tables/views access with a single query.

Without taking care of the initial query which could certainly be improved, as a general result, all queries have more joins meaning all queries take more time. Which is not exactly what we expected when we started the migration!

Below is a sample case to illustrate this. (I'm french so fields in my DB are in french... but to help you understanding the sample, an Agriculteur is a Farmer, the EtatAgriculteur is the State of the farmer (from a professional point of view -does he grow cows, does he cultivate cereals, etc...) and the Parcelle are Field (to cultivate cereals, to grow cows...)
Relationnal point-of-view : An Agriculteur may have several EtatAgriculteur (many-to-one relation) while this Agriculteur may have 'n' Parcelle. A Parcelle may be owned only by one Agriculteur. For the Agriculteur, this is a one-to-many relation.
The query below, is trying to retrieve all Parcelle, associating with them the Agriculteur and their EtatAgriculteur.

I used P6Spy to catch the SQL produced by Hibernate after translating the HQL query.

Initial HQL query:


SQL translation done by Hibernate 3.2.5.GA


SQL translation done by Hibernate 3.6.7.Final :

The second inner join (where I tried to put the U & B BBCode tags is the one added by Hbn3.6.7.

Is there anyone who could help me trying to understand why Hb,3.6.7 duplicates the joins and how we could solve this?

Thanks,
Olivier.

It's related to a change in HHH-4091 how implicit and explicit joins are handled.

For example, you have an explicit join join p.agriculteur as agri, so you'll need to replace 'p.agriculteur' with the alias 'agri' in all other places to avoid the duplicate join problem. You'll have to repeat this for the second join as well. Then, you'll probably also notice that the where part is not really needed (or not doing what you think it is doing) because it will be: where (agri.agrCdn = agri.agrCdn and etat.etatCdn = etat.etatCdn) which is always true.

Thank you very much for your accurate reply. Let me give it a try and tell you how it goes.

Btw, as I told it was a sample query, written only for the purpose of providing something easy to read in this post. And yes, you're right, the where clause is absolutely not needed (I noticed that... but too late!). My bad on this one but... never mind, with or without the where clause the result is always the same (as expected with your explanation).

Brb with an answer concerning the new result.

Hello I'm back.

Well, you were right nordborg.

Here is my new HQL :


And here is the new SQL translation done by Hbn 3.6.7 :


Thanks a lot. You really helped me. I was just driving crazy with that problem.

Btw : to be absolutely honnest, we are a framework provider, and our framework encapsulates Hibernate. Our framework is used by more than 50 projects... and now I have to tell them they must all have a look to their HQL queries and maybe they must rewrite them. It will cost a lot of money and might be a killer for upgrading to the new version of our framework...

We'll see. But now I have both an answer and a solution.

Thanks again.