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

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.