[hibernate-dev] Entity-joins (HHH-16 and HHH-7321)

[Steve Ebersole]

I am working mainly on HHH-16 which requests adding support for
entity-joins (aka "ad hoc" joins).

So long-story-short, there is a simple solution with some limitations and
then a more correct solution that unfortunately requires a lot of rework in
the HQL parser.

The crux of the problem is identifier scoping in the generated SQL and how
Hibernate handles implicit joins currently in HQL.

As an example, consider a query like:

select f.id, f.customer.name, f.postDate, u.username
from FinancialRecord f
    left join User u on f.lastUpdateBy = u.username

As I currently process this entity-join ("... join User on ...") I have to
attach it to the end of the FromClause.  The reason I have to attach it
there is a bit of a convoluted discussion that gets into the design of the
current HQL AST model and some poor assumptions made there.

Complicating the fact that I add the entity-join to the end of the
FromClause is the fact that Hibernate currently always handles implicit
joins as theta joins.

So all told the query above is currently rendered to SQL as:

select ...
from financial_record f,
    customer c
    left outer join `user` u
        on f.last_updt_by = u.username
where f.customer_id=c.id

So the problem with scoping is the comma.  In SQL terms, that delimits the
start of a new "table reference".  This is where a lot of databases diverge
on what is supported in terms of scoping references to aliases between
"table references".  H2 for example is fine with this as long as the join
to `user` is an inner join; but it chokes if the join is outer.

The simply solution would seem to be to have Hibernate render the implicit
join as an ANSI-style join rather than a theta-join.  However this is where
the poor design choices that I mentioned in the current parser come into
play.  Basically the parser overloads the flag for implicit joins to mean
many, many things.  So changing that one value really messes things up.  So
that's not realistically an option.  It is definitely something we want to
keep in mind for the new parser however!

Another option is to introduce a concept similar to SQL's "table reference"
into the AST model.  This is essentially the same this I do in SQM with
org.hibernate.sqm.query.from.FromElementSpace.  However, this is a massive
change in the parser.

I am inclined for now to simply say that implicit joins and entity-joins
cannot be combined, and to circle back to this later in terms of working
out support for using them in combination.