Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

Allow expansion of the "on" clause in joins.

Description

In english, I want a list of trades in a portfolio, whether tradeprices exist or not... but if they exist, they should be dated at least 7/1/2005.

In oracle, it looks like:
from trade t, tradeprice tp where t.portfolio=53 and tp.tradeid=t.tradeid and tp.effdate >= '1-Jul-2005'

In ansi, it looks like:
from trade t left outer join tradeprice tp on t.tradeid=tp.tradeid and tp.effdate >= '1-Jul-2005' where t.portfolio=53

In HQL, the closest I can get is:
from trade t left join fetch t.tradeprices tp where t.portfolio=53 and tp.effdate >= '1-Jul-2005'

But that HQL generates this incorrect SQL:
from trade t left outer join tradeprice tp on t.tradeid=tp.tradeid where t.portfolio=53 and tp.effdate >= '1-Jul-2005'

This SQL is not correct because the effdate condition needs to be in the "on" clause, not the "where" clause. But the "on" clause is generated internally by NHibernate, and I haven't found a way to alter it. Is there a way?

btw... tradeprice is a lazy init bag on trade object

related forum topic: http://forum.hibernate.org/viewtopic.php?t=952606&highlight=outer+join

Environment

None

Activity

Show:

Frederik Gheysels August 19, 2009 at 6:02 AM

So, it seems that this should be fixed, but, is it possible that there are some situations where this doesn't work ?
I've been receiving the exception "with-clause expressions did not reference from-clause element to which the with-clause was associated " on some of my queries.

After searching a bit, I found out that the Hibernate (java) version also gave some problems. This has been fixed in the Java version recently (about a month ago):

http://opensource.atlassian.com/projects/hibernate/browse/HHH-1930?focusedCommentId=33311&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_33311

Fabio Maulo December 15, 2008 at 8:55 AM

We are working on it
http://fabiomaulo.blogspot.com/2008/12/artorius.html

Check the grammar and passing tests and wait for the final solution.

Michal Kowalski December 15, 2008 at 5:32 AM

Cosmin December 15, 2008 at 5:13 AM

well... what if you had to also order the result set like this.

SELECT customer.id
from customer as c left outer join
adddress as a on (a.customer_fk == c.id and a.add_AddressType = 1)
order by a.Street

if you remove the extra join condition then the ids would be retrieved with wrong ordering. if you wanted to elminate the unwanted row then you would have to:

  • drop the extra condition (a.add_AddressType = 1)

  • get also the ordering row (a.Street)

  • eliminate the repeating entities by hand

  • order the remaining entities by hand

this is not a problem if the set is small but what if you return 10k customers and each having several, one or no addresses. instead of only getting the small ids, the query will be at least a little less efficient, and the elimination of duplactaes and ordering will use extra cpu and ram.

does anybody have any solutin to this in HQL?!

Dave McMullan November 18, 2008 at 6:18 AM

Since I originally opened this issue 3 years ago, I've found a few workarounds... 1) use named queries with SQL having any join syntax desired... 2) use all oracle outer join syntax in HQL with no mix of ansi join syntax... 3) simply remove the extra condition in the HQL, which would return more rows than desired, then manually eliminate unwanted rows in the business logic.

Fixed

Details

Assignee

Reporter

Components

Fix versions

Affects versions

Priority

Who's Looking?

Open Who's Looking?

Created December 28, 2005 at 1:04 PM
Updated August 19, 2009 at 6:02 AM
Resolved May 8, 2009 at 9:37 AM
Who's Looking?