NH-514 is a serious issue for our company, but it is beyond what we dare attempt ourselves. So, we would like to hire someone who has worked considerably on Hibernate/NHibernate to port the AST-based HQL parser from Hibernate 3.x in order to fix NH-514 (and NH-1051). We contacted JBoss sales/support back in September, and they originally indicated that they could do it, but now they say they "don't support NHibernate".

We're not looking for "support", we're looking to get development done (and done right). How do we go about doing this? In particular, we would like to contract someone on the Hibernate/NHibernate "team".

I've seen NH-514 mentioned many times before, but do people realize that it is not actually required to perform the kind of searches which are mentioned in that jira?

The provided HQL which does not work:


my HQL which will work:


Now what this says is give me all trades for portfolio #53 which either have no trade prices or if they do have trade prices, the trade prices are on or after the first of July 2005.

Note I don't actually know if the fetch will work here (if it does I really wouldn't use it since the collection would be invalid).

From the original poster of that Jira item they wanted: "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."

This looks like a match to me.

If this isn't the problem you are having maybe I can think of another way to solve it.

I have used this approach in a production environment, it does work. However, do keep in mind that the above query is not actually a true left join, with a left join if there are no prices effective on or after july 1st 2005 it would still return the trades, in this above case it would not. But again, it is possible to write the query in such a way to return the same results as the left join. You basically just need to check that tp is null or there does not exist any tradeprice >= July 1 2007.

I don't want to sound like I'm putting down your effort to contract someone, I am not. If someone is available for that I think it is awesome. But in case they will not there still may be other alternatives.

I also realize that this isn't the cleanest looking query, but it still performs the logic which was requested.[/code]

We've tried the workaround and it doesn't always work. From the JIRA entry itself:


although the condition for when it fails is incorrect -- it should be "If you have one or more records in the tradeprices table that matches the tradeid but none match the criteria, then you will NOT get the NULL record you expect."

BTW, we're looking to get this completed within 6 months. The amount of work involved is extensive, estimated at 1 to 3 months. We have one candidate so far ...

Nels, I don't think you read my whole post. I addressed this issue. I stated that it wasn't exactly like a left join and why, but it does do what the poster of the jira issue stated. The query to do the left join functionality (while ugly and a workaround) would be:


In this case the fetch is valid. If you don't want the fetch the result is simpler.

I still think there is a workaround for any query you could write which requires the left join behavior is described in NH-514.

I'm not saying that what is in NH-514 isn't superior, just that it should be possible to work around it.

I don't think the query you gave will work -- if there are tradeprices for a given trade that are both before and after the effective date (such data for those kinds of tables doesn't make sense to me, but this issue is about left joining to any one-to-many collection) your NOT EXISTS clause would eliminate that trade.

Let's look at a clearer example. Say you want to query all contacts and their cell phone number. If they don't have a cell phone, you still want the contact selected. You want to do this in one query, no massaging or filtering the result set afterwards in code.

Once your query contains this much:



then if a particular contact has one or more phones but none are cell phones, you will get no row back for that contact at all. I'm not aware of any possible additional logic you could put into the WHERE clause to tell it that the join should still succeed (i.e. that the contact should still be selected) but that the selected properties from the CellPhone should all be NULL. A far as I know, LEFT JOIN will only return a NULL substitute if nothing matches what is in its ON clause; if something does match what is in the ON clause (and using NHibernate HQL, it's currently only what is set up in the mapping), then it works like a regular JOIN, and particular phone row(s) are either joined to or the driving entity (at the start of the FROM clause) is not selected.

I imagine that if joined rows do exist but none match your criteria, using a subselect you could join to the top first phone row (which has the "wrong" data) and then map everything you select in the SELECT clause with CASE statements from "wrong" values to NULLs. Unfortunately I don't think you can have CASE statements in the SELECT clause in HQL, or even select a literal NULL. Even if you could, this would be a very confusing workaround.

Something like the above query with a UNION to also select contacts that have phones where none are cell phones might work in theory, but I'm not aware of NHibernate HQL supporting UNION. Even then, you'd have to select explicit NULLs as placeholders for the missing cell phone, and as previously mentioned, I don't think NHibernate HQL supports selecting literal NULLs.

Even if a "join to top 1 wrong row and map with SELECT CASE" or a UNION approach is possible in HQL, we have monster queries that have upwards of 30 joins, 5 or more of which need correct "LEFT JOIN with additional join criteria" behavior. Any workarounds would produce horrendous queries. Even if our developers would put up with it, our product is customizable and our customers can write their own HQL queries, and they wouldn't accept workarounds with such large and tricky amounts of extra query logic.

In any case, if you can come up with single NHibernate 1.2 HQL query that can return the correct results for the "contact and optional cell phone" example I gave, let me know. It needs to behave like this:

1. If the contact has no cell phone, the contact is returned with a NULL for the phone.
2. If the contact has cell phone(s), the contact is returned with cell phone, for each cell phone.
3. If the contact has any non-cell phone(s), that should not affect the behavior of #1 or #2.

If this is the problem you're trying to solve, then yes there is a bit of an issue. As I understood it the issue more specifically related to the ability to select contacts which either didn't have a phone or had a cell phone if they did have a phone.

Once you want to select elements from the child table in a single HQL select you will run in to an issue without a subquery for the select or a case statement (I don't think HQL supports either of those).

I guess my thinking was that if you can perform the query on the objects you at least have the correct subset of contacts and then you can use those returned contacts to select their cell phones (in your CLR code) or have that method return null if there is not one.

I still think what I'm saying is a workaround. Your issue with my last query could also be solved with two subqueries.

That being said I'm glad you have a candidate for this. I would like to see it implemented. I was just thinking it was going to be unlikely to see it completed in the relatively near future.