Sorry if this has been asked before. I've been poring over the archives and I can't explain this. I see that for non-fetch joins I can specify whether I want them to be inner or outer. But, for fetch joins, I'm not allowed to say "outer join fetch" or "inner join fetch". Hibernate in Action says (section 7.3.2) "In HQL, you can specify than an association should be eagerly fetched by an outer join using the fetch keyword in the from clause"



However, I have a simple join fetch (detailed below) and it's using an inner join, so that my "items" (A's) that don't have any "bids" (B's) aren't retrieved. What am I doing wrong?

Two questions: Why does my example differ from the book? And how do you tell Hibernate "I want you to fetch this dependent collection but I want you to use an outer (or inner) join."

Hibernate version: 3.1.3

Mapping documents:



Code between sessionFactory.openSession() and session.close():

Query q = sess.createQuery("from A a join fetch a.Bs bs");
List l = q.list();

Full stack trace of any exception that occurs: none

Name and version of the database you are using: hsql 1.8.0

The generated SQL (show_sql=true):
select a0_.id as id0_0_, bs1_.id as id1_1_, a0_.var as var0_0_, bs1_.A as A1_1_, bs1_.var as var1_1_, bs1_.A as A0__, bs1_.id as id0__ from A a0_ inner join B bs1_ on a0_.id=bs1_.A

Bump.

This continues to confuse me. The book clearly says that 'join fetch' results in an outer join, but I can demonstrate that it doesn't (see my above post). Using setFetchMode(FetchMode.JOIN) in a Criteria query will do an outer join, but I want to do it in HQL.

And apparently "inner join fetch" is valid syntax, but "outer join fetch" is not. So how do I do an outer join?

What am I doing wrong?

Thanks
Dan

I simply cannot get full outer joins to work using Hibernate 3.1 and PostgeSQL 8.0.3.

The PostgreSQL documentation indicates it supports left right and full outer joins.

I am able to get left and right outer joins to work, bolstering confidence that I just might know what I am doing.

Is this a specific enough question for any experts to address?

TIA,
BE

I'm not sure what your problem is, but I can use both LEFT JOIN FETCH and INNER JOIN FETCH. I can also just do LEFT JOIN or INNER JOIN (which just restricts or not my result set). I do the FETCH simply because I want to get everything in one SQL statement.

I'm using Hibernate 3.2.

oops - I just read the original post again. OUTER JOIN is not valid.

Ok, so JOIN FETCH is supposed to do an outer join (always) and INNER JOIN FETCH is the way to override it to do inner joins?

Makes sense, but as I said in my original post, I'm seeing INNER joins being executed on a simple query containing join fetch. I'm not sure what changed.

HQL and SQL:

(inner) join fetch = INNER JOIN
(left|outer) join fetch = LEFT OUTER JOIN

Thanks

I thought "outer join fetch" was not valid HQL. It throws an error when I do that. Also, it's not clear from what you said what happens if you just say "join fetch" i.e. what's the default?

HQL and SQL:

join fetch = INNER JOIN
inner join fetch = INNER JOIN
left join fetch = LEFT OUTER JOIN
left outer join fetch = LEFT OUTER JOIN

Easy, no?

Thanks a lot. It is easy when you put it that way :)

After reading posts about joins, everything is as clear as mud about OUTER JOINS for me.

Can any authority on this topic please respond with a yes or no to this question:

Is it possible to do full outer joins using Hibernate?

Of course, it's even in the reference documentation:

http://www.hibernate.org/hib_docs/v3/re ... yhql-joins

Thanks for the reply, Christian.

When I try to do FULL JOINS I always get
Yes, I have read section 14.3 many times and oh, how I yearn for things to work that way. Hibernate has been a truly excellent technology for us in every other respect.

I have been successful in using LEFT and RIGHT outer joins while joining 3 or more tables. I've combined that with various WHERE and ORDER BY clauses. I hope this convinces you that I have worked hard to understand the syntax of HQL and that I have had a lot of success using it in nontrivial queries.

Please also notice an earlier post matter of factly saying

My database is PostgreSQL 8.0.3. Its documentation claims it can do full joins. At this point, I don't believe anything I read so I tried running full joins with manually entered queries. I confirmed that PostgreSQL 8.0.3 does indeed do full joins.

I started to think that maybe the way for me would be to use session.createSqlQuery instead of session.createQuery. I got it to work and it did do full joins. It was also slow as hell. One query took 15 seconds to get a list of 21,000 records whereas before things were running well under one second.

Besides, once you get used to HQL it is tough going back to SQL.

Using Java 1.5 I get the following error when I try to do full joins.


OK, so I have been looking for creative workarounds. SInce I am able to do LEFT and RIGHT joins, or at least I can do them without getting an error, I tried joining an additional table at the beginning or end of the join chain and then use LEFT or RIGHT joins where I really wanted FULL joins. This resulted in INNER joins.

Perhaps I should word my question this way: under what conditions does Hibernate decide to give you and inner join despite your asking for some kind of outer join?

Certainly, the above sort of logic would make sense. Could it be that it is backfiring on me? I will admit that there are some times that due to a WHERE clause I really should be using an inner join and may have coded up an outer join out of expediency, but I know for a fact that full outer joins with no where clause present are not working for us.

I guess my next step is to do more stepping into the Hibernate code to see exactly what is going on. I sure would appreciate some kind of response to either tell me to forget about full outer joins, that they categorically do not work OR that they do routinely work for others and any hints about how they are getting them to work.

OK, I've learned some things the hard way and would like to try to explain them here. If you have been searching for help with outer joins and are frustrated with haughty, useless answers from the Hibernate team, welcome to my nightmare. If you are a member of the Hibernate team, then feel free to take me down a notch, out do me, and see if you can help us out and maybe even put more energy into an answer than this newbie puts into questions. Even better, update the on line documentation with some useful examples. The word full appears a total of two times that I can count as regards full joins.

The left join fetch syntax seems like a way to get outer joins, this is for sure. If you have been having trouble getting it to work, I suggest experimenting with it to discover the true behavior of Hibernate which we have been trying to coax the experts into explaining to us. For example, try writing a JUnit that focuses in on succinct queries.

One thing to consider is that with joins you do not necessarily have to go top down. Try putting whatever the "outer" table is on the left side of the HQL clause and work from there.

One thing that seems certain is that Hibernate can easily decide to change your outer join down into an inner join. If you have some ORDER BY or WHERE clauses then try removing them and see if you can get outer join behavior back. Or, if you use the dot notation to extend your reach into an adjoining table, then you are implicitly joining to it, right? My experience is that the Hibernate query engine will use just about any reason to devolve a query with outer joins in it down to all inner joins.

I would also suggest to those who are struggling to maybe forget about having a select clause with lots of different elements called out in it if you are using HQL.

Why? It seems that if you use the left join fetch syntax then the "owner" of the fetch needs to be present, implicitly or explicitly, in the select clause. It would be nice if that term was explained, but if you experiment around you might reach the conclusion that the owner is the table on the left side of the left join fetch. OK, simple enough so there's no need to document that. Thus, you can let it be specified implicitly by having an empty select clause, or you can include it. For me, I have a table named sample and a pojo that matched it named Sample and we commonly used s as the abbreviated name. My point here is that if your result set is going to return a reference to a pojo then don't bother making the query engine gather up fields that already exist in that pojo. Just get the pojo and work from there. Get your head back where it would rather be, doing OO programming rather than SQL-ish tomfoolery.

So if you get to that point I would ask the question "why even bother with the "left join fetch" syntax. Just query up all of the objects and that will certainly get you your outer join. BTW, if you don't know about pagination, then you are in for a good surprise. Be sure to check out:
query.setFirstResult();
query.setMaxResults();
The above are something really good about Hibernate.