Add support for joining a subquery in hql

[maca88]

Basic hql syntax:

from Order o 
inner join (
  from Order where OrderId = 10248
) o2 on (o.OrderId - 1) = o2.OrderId

In case the subquery returns multiple values, each value can be referenced by using an alias:

select o, o2.ord, o2.cu
from Order o 
inner join (
  select o1 as ord, o1.Customer as cu
  from Order o1 where o1.OrderId = 10248
) o2 on (o.OrderId - 1) = o2.ord.OrderId

Aliases can be nested in case of nested subqueries:

select 
  o4.ord3, o4.ord3.ShippedTo, o4.cu3, o4.ord3.ShippingAddress.City,
  o4.sub2.ord2, o4.sub2.ord2.ShippedTo, o4.sub2.cu2, o4.sub2.cu2.Address.City,
  o4.sub2.sub1.ord1, o4.sub2.sub1.ord1.ShippedTo, o4.sub2.sub1.cu1, o4.sub2.sub1.cu1.Address.City
from Order o5
inner join (
  select o as ord3, o.Customer as cu3, o3 as sub2
  from Order o
  left join (
    select o1 as ord2, o1.Customer as cu2, o2 as sub1
    from Order o1
    left join (
      select o0 as ord1, o0.Customer as cu1
      from Order o0
    ) o2 on o1.OrderId = o2.ord1.OrderId-1
  ) o3 on o.OrderId = o3.ord2.OrderId-1
  where o.OrderId = 10248
) o4 on o5.OrderId = o4.ord3.OrderId

This feature is a requirement in order to support the Include method on all databases when paging results. Sql IN operator does not work with composite keys and in some databases even for normal keys (e.g. MySql), so with this feature we can write the following query:

select o
from CompositeOrder o 
inner join (
  select id
  from CompositeOrder
  order by OrderId skip 2 take 2
) o2 on o.id = o2.id
left join fetch o.OrderLines

Known limitation:

• Selecting a hql constructor inside a subquery is not supported

[bahusoid]

Does it work with parameters in subquery? I see no such tests...

from Order o 
inner join (
  from Order where OrderId = :id
) o2 on (o.OrderId - 1) = o2.OrderId

[maca88]

Forgot to test it, now it works and added tests.

[bahusoid]

There is I believe another limitation (which is also present for entity join). It's not possible to add association join dependent on subquery join. Something like:

from Order o 
inner join (from Order where OrderId = :id) o2 on (o.OrderId - 1) = o2.OrderId 
inner join o.Employee e on e = o2.Employee 

Employee e join will be added right after Order o join (and it seems only SQLite is able to handle such queries with invalid order of joins). AFAIK it's not a big issue for LINQ as we don't support association joins there. That's just FYI maybe you see an easy fix for this.

[bahusoid]

And your alias related changes seem to fix issue reported here: https://github.com/nhibernate/nhibernate-core/issues/2092

I see no duplicates in generated SQL

[maca88]

It's not possible to add association join dependent on subquery join

With the last commit the issue is fixed.

[bahusoid]

@maca88 Some tests are failed after merge... Can you take a look?

[maca88]

The tests were failing because a new formula was added to Order entity: <property name="RequiredOrderDate" formula="OrderDate" insert="false" update="false" /> I just increased the expected number of columns by two as the test is selecting two orders in one query.