Using Hibernate 3.1 with PostgreSQL 8.0.3, we use are successfully using createQuery to make various inner, left and right joins across multiple tables.

When we try to do a full join, Hibernate always crashes with a stack trace like the one shown below.

(If there is no solution, we would like to discuss our efforts at creative workarounds. We have tried replacing full joins with left or right joins to tables that we really do not want to join to. They just serve as an endpoint in a left or right join. This creates other problems.)

TIA,
BE


STACK TRACE:

an assertion failure occured (this may indicate a bug in Hibernate, but is more likely due to unsafe use of the session)
org.hibernate.AssertionFailure: undefined join type 23
at org.hibernate.hql.ast.util.JoinProcessor.toHibernateJoinType(JoinProcessor.java:66)
at org.hibernate.hql.ast.HqlSqlWalker.setImpliedJoinType(HqlSqlWalker.java:370)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.joinElement(HqlSqlBaseWalker.java:3150)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.fromElement(HqlSqlBaseWalker.java:3060)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.fromElementList(HqlSqlBaseWalker.java:2938)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.fromClause(HqlSqlBaseWalker.java:688)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:544)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:281)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:229)
at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:218)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:158)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:105)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:75)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:54)
at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:71)
at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:134)
at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:113)
at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1600)

I need to amend what I said in my post. When we attempt any kind of FULL JOIN, we always get:


Changing FULL JOIN to RIGHT JOIN or LEFT JOIN makes our code run without the exception, but the result is that we always get an INNER JOIN.

What we believe may be happening is that the Hibernate query engine is deciding that an inner join is appropriate due to some clause in our HQL.

So, we've tried things like WHERE fieldname IS NULL, but that does not work. It is as if those records have already been excluded.

We have gone back to writing really simple queries trying to get basic outer joins to work.

We would like to ask these questions:

Could there be anything in our hbm.xml files that constrain Hibernate to always form inner joins?

Anything else in the configuration of Hibernate to constrain us to inner joins?

Would it be possible to build a query and then add to it certain records that have certain fields that are null? Would this be a subselect?

We're desperate. All thoughtful, nontrivial help and suggestions will be appreciated and will get credit for helping.

I am having the same problem, any suggestions, news

David.


>[quote="BullEarwig"]I need to amend what I said in my post. When we attempt any kind of FULL JOIN, we always get:

Hello David.
My sympathies. I got no help and only some insolent, huffy responses from "Team Members."

I got through my problems so I would love to help you with yours.

First off, I never got FULL JOINS to work. I ended up finding a way to do everything with LEFT joins. This might work for you to. Sometimes you can find a "target" for the right side of a LEFT join - it functions as a surrogate partner in the join to help you get things to work.

Another thing I would like to point out is a conceptual misunderstanding I suffered from because maybe it is hampering you. If you do any kind of outer join between two tables and then you do an inner join, this is will likely defeat the whole purpose of doing the original outer join. After all, what you wanted were to include those loner, orphan items that the outer join includes in your result set, right?

Some other tips in case they help:
1. Use the pagination methods if you have large record sets.
2. To determine the total number of records in a complex query with multiple joins, you could get list.size() of it, but for larger rowsets I got way faster results by running a count query.
3. I made a flexible GUI that allowed the user lots of options in showing three different levels of hierarchal information. What simplified things for me was to always return object A, A and B, or A, B and C in the select list and then to build the joins from the "lowest in the hierarchy" upwards, using LEFT joins only when it was logically needed. If that is like your problem and you are still stuck, write me back and I can probably help more.

Good luck,
BE

Thank you or your answer.

I did read the source code, it appears from line 57 of join processor that full join does not appear to be supported in HSQL.

But I took on board what you said,

I have query with 2 tables of the same type and 3rd of another.

Hibernate was doing an inner join with the two identical table and then merging the results with the 3 table. After your comments I told hibernate to do an inner join with one of my identical object and left the result of the calculation the same.

It now works as desired.

Moral tell hibernate what you WANT, don't allow it to make any assuptions of its own !

Dave.

Glad to hear it Dave. If I helped, please click on the place that says so. I am nearly out of gas.

The post helped 100 %

But I can find a place to rate your orginal post

Only to happy to post a rating.

Dave.