Left Join is filtering out rows that I expect to show when using HQL

I have something similar to the following HQL:

select count(distinct car.id), 
count(distinct w1.id), 
count(distinct w2.id), 
count(distinct w3.id)
from Car car
left join Wheel w1
left join Wheel w2 with w2.IsFrontWheel == true
left join Wheel w3 with w3.IsFrontWheel == false
where w1.ManufacturedDate > :manufacturedDate
and w2.ManufacturedDate > :manufacturedDate
and w3.ManufacturedDate > :manufacturedDate
group by car.Make

This HQL is being dynamically generated through my user's selections when they are running a report. The important thing to note here, is that the user is dynamically choosing to filter, which causes this HQL to dynamically build a where clause.

I have the where clause generation working properly.

My problem is that, if you run the resulting query in SQL Server 2008, the end result is that if there are Wheels (w1 or w2 or w3) that don't have any ManufacturedDate greater than the given parameter, the report completely omits that record.

I discovered the reason for this, in this article here.

My problem is, I'm unable to add these filters to the with clause in the HQL. With the example above, I could add the constraint to the with clause, but the report engine I build let's the user do much more advanced filtering than this, most of which is not allowed in the with clause. I ever tried putting some of the generated HQL into the with and verified that this is indeed the case, and most of them queries won't run because they require additional constraints on other tables.

So my question is:

Is there any way to modify this HQL to get the missing records back? In the article I listed above, someone mentioned that if you take into account for NULLs, you can get the records back, but I don't know in what way he was referring to, and if that is possible I would definitely like to know how.

UPDATE

I'm forced to put the dynamic filter in the where clause because the user may also do something like this:

select count(distinct car.id), 
count(distinct w1.id), 
count(distinct w2.id), 
count(distinct w3.id)
from Car car
left join Wheel开发者_StackOverflow社区 w1
left join Wheel w2 with w2.IsFrontWheel == true
left join Wheel w3 with w3.IsFrontWheel == false
where w1.Buyer.FirstName = :firstName
and w2.Buyer.FirstName = :firstName
and w3.Buyer.FirstName = :firstName
group by car.Make

which if I rewrote in the with clause

select count(distinct car.id), 
count(distinct w1.id), 
count(distinct w2.id), 
count(distinct w3.id)
from Car car
left join Wheel w1 with w1.Buyer.FirstName = :firstName
left join Wheel w2 with w2.IsFrontWheel == true 
    and w2.Buyer.FirstName = :firstName
left join Wheel w3 with w3.IsFrontWheel == false 
    and w3.Buyer.FirstName = :firstName
group by car.Make

won't run, NHibernate throws an exception.

---

your problem is filtering left joined columns in the WHERE clause:

select count(distinct car.id), 
count(distinct w1.id), 
count(distinct w2.id), 
count(distinct w3.id)
from Car car
left join Wheel w1
left join Wheel w2 with w2.IsFrontWheel == true
left join Wheel w3 with w3.IsFrontWheel == false
where w1.ManufacturedDate > :manufacturedDate --<<<<
and w2.ManufacturedDate > :manufacturedDate   --<<<<
and w3.ManufacturedDate > :manufacturedDate   --<<<<
group by car.Make

try this:

select count(distinct car.id), 
count(distinct w1.id), 
count(distinct w2.id), 
count(distinct w3.id)
from Car car
left join Wheel w1 with w1.ManufacturedDate > :manufacturedDate
left join Wheel w2 with w2.IsFrontWheel == true AND w2.ManufacturedDate > :manufacturedDate
left join Wheel w3 with w3.IsFrontWheel == false AND w3.ManufacturedDate > :manufacturedDate
group by car.Make

when you compare w2.ManufacturedDate > :manufacturedDate it is like doing null > :manufacturedDate whihc results in NULL and the row is thrown away, juts like it was an inner join. moving the condition to the LEFT JOIN's ON prevents this.

EDIT
try this:

select count(distinct car.id), 
count(distinct w1.id), 
count(distinct w2.id), 
count(distinct w3.id)
from Car car
left join Wheel w1
left join Wheel w2 with w2.IsFrontWheel == true
left join Wheel w3 with w3.IsFrontWheel == false
where ISNULL(w1.ManufacturedDate,'99991231') > :manufacturedDate
and ISNULL(w2.ManufacturedDate,'99991231)' > :manufacturedDate
and ISNULL(w3.ManufacturedDate,'99991231') > :manufacturedDate
group by car.Make

---

Check out the 'with' keyword in HQL click

---

I'm going to dive in, though I don't know anything about HQL :)

If you need to avoid using the WITH, then you need the WHERE clause to be true when whatever you want to count as a result happens. That condition isn't entirely clear to me from your question, but something like:

where ISNULL(w1.ManufacturedDate, *some_future_value*) > :manufacturedDate

...where *some_future_value* is a value you somehow generate to be further into the future than the manufacturedDate parameter. So maybe your maximum possible date value, or ":manufacturedDate + 1', or whatever works for you.

---

Try the following:

left join Wheel w1
left join w1.Buyer b1 with b1.FirstName = :firstName

Same for all the others. That should do.