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

[Sanne Grinovero]

On 8 February 2016 at 17:44, Steve Ebersole <steve at hibernate.org> wrote:
> 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.

+1