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

[Vlad Mihalcea]

Hi.

It's fine to assume that implicit joins and entity-joins don't mix and
document this behavior.
When using explicit joins:

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

We get a nice SQL join query, right?

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

Vlad

On Mon, Feb 8, 2016 at 8:03 PM, Sanne Grinovero <sanne at hibernate.org> wrote:

> 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
> _______________________________________________
> hibernate-dev mailing list
> hibernate-dev at lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>