Re: [hibernate-dev] Why does implicit join translate to inner join?
2018-02-22 21:39 GMT+01:00 Steve Ebersole : > On Thu, Feb 22, 2018 at 9:11 AM Lukas Eder wrote: > >> Hi Steve, >> >> Thanks for your message. Of course, being explicit always has the >> advantage of ... being explicit. But my question here is really about the >> implicit join feature and how it works. >> > > Sure, but that is also part of the answer. A big part > I take you kinda regret the feature? :-) But I'm certain that many users find it extremely useful. > Not to mention, adjusting the type of SQL join used for implicit jois >>> means I can no longer just look at the query and know what is happening in >>> terms of SQL joins - which is bad. >>> >> >> Why not? There's just an additional keyword between the generated tables: >> LEFT (or if you will, LEFT OUTER). >> > > I think you misunderstand... I was saying that I can no longer look at > the HQL/JPQL and tell what kind of SQL joins will be used for that if it is > dependent on the mapped association. > OK, I see. However, this could be said of a few other features as well (at least in SQL). For instance, Oracle's FETCH FIRST n ROWS ONLY is translated to nesting queries and filtering on ROW_NUMBER() window functions, which is indeed the same thing. FETCH FIRST n ROWS WITH TIES is translated to nesting them and filtering on RANK(). FETCH FIRST n PERCENT ROWS can be implemented with PERCENT_RANK() (although, I think that's not what's being done). PIVOT could be seen as syntax sugar for GROUP BY and a generated set of projections, UNPIVOT and GROUPING SETS for UNION ALL. That's the nature of syntax sugar. It hides verbosity in favour of simplicity and development ease, and in some cases, offers an option for a specific optimisation that is only possible when the syntax sugar is used, not the "expanded" version. For example, few databases are as mad as Oracle to translate FETCH FIRST to window function filtering - they have more optimal LIMIT implementations. That's just a historic Oracle issue. The price is, well, the verbose version (which is more explicit and thus more "clear") is hidden. But is that a high price to pay? I personally think not. > This approach was mentioned earlier in the thread. But you clarified > that you mean that implicit joins ought to just always be interpreted as an > outer join. > I may have misstated that. Personally, I prefer them to be implemented in the most optimal way because sadly, not all databases are able to transform outer joins to inner joins when this is appropriate. From the databases I checked (DB2 LUW, MySQL, Oracle, PostgreSQL, SQL Server), only DB2 does it. I can imagine some edge cases where an inner join produces better cardinality estimates in complex queries than a left join. On the other hand, not all databases can eliminate inner joins. E.g. PostgreSQL can only eliminate outer joins. This shouldn't apply in this discussion, but just to show, there are pros and cons to both join types from a performance perspective. But for most trivial cases (and I'm assuming that most JPQL/HQL generated queries, which don't support derived tables nor unions, will still match my definition of trivial), this doesn't really matter much anyway, so I understand that the pragmatic, preferred argument here is to always do it in the same way. > You could plugin in your own query handling and interpret implicit joins > however you want. That is current not the most trivial task though. > Sure, that's always an option! Perhaps a nice blog post for Vlad? ;-) > Not to mention, IMO interpreting these as inner joins is more OO-ish. >>> >> >> I'm curious about that, would you mind elaborating? >> > > Well in normal Java - Idiomatic Java ;) - calling `p.address.city` leads > to a NPE if `p.address` happens to be null, right? To me, using inner > joins naturally follows from that - you are saying no intermediate path is > null. > I see. Although the query language, even if based on Java-annotated entities, is a relational-ish one where NULL has a different semantics. E.g. I don't suppose that substring(NULL, 1, 2) throws a NPE in JPQL, it'll rather return NULL, just like NULL + 1 returns NULL. Likewise, I'm expecting a NULL = NULL comparison to yield NULL, not TRUE. I guess that's a matter of perspective. As a SQL person, I'd expect a language (JPQL) that so obviously translates to SQL to follow SQL idioms much more than Java idioms. > And what's your opinion on the Stream analogy, where the current behaviour >> (implicit joins from the context of a SELECT clause) corresponds to >> Stream.map() potentially applying filters? >> > > Well 2 things: > > 1) I personally think its not good form to put null in the output of a > Stream map operation, so to me that in fact does imply a filtering > You should try suggesting that to the Stream EG :-) I'm pretty sure everyone will agree that while your perception of good form is reasonable, your implication is not. Besides, my example isn't equivalent to returning nu
Re: [hibernate-dev] Why does implicit join translate to inner join?
On Thu, Feb 22, 2018 at 9:11 AM Lukas Eder wrote: > Hi Steve, > > Thanks for your message. Of course, being explicit always has the > advantage of ... being explicit. But my question here is really about the > implicit join feature and how it works. > Sure, but that is also part of the answer. A big part > Not to mention, adjusting the type of SQL join used for implicit jois >> means I can no longer just look at the query and know what is happening in >> terms of SQL joins - which is bad. >> > > Why not? There's just an additional keyword between the generated tables: > LEFT (or if you will, LEFT OUTER). > I think you misunderstand... I was saying that I can no longer look at the HQL/JPQL and tell what kind of SQL joins will be used for that if it is dependent on the mapped association. This approach was mentioned earlier in the thread. But you clarified that you mean that implicit joins ought to just always be interpreted as an outer join. You could plugin in your own query handling and interpret implicit joins however you want. That is current not the most trivial task though. > >> Not to mention, IMO interpreting these as inner joins is more OO-ish. >> > > I'm curious about that, would you mind elaborating? > Well in normal Java - Idiomatic Java ;) - calling `p.address.city` leads to a NPE if `p.address` happens to be null, right? To me, using inner joins naturally follows from that - you are saying no intermediate path is null. And what's your opinion on the Stream analogy, where the current behaviour > (implicit joins from the context of a SELECT clause) corresponds to > Stream.map() potentially applying filters? > Well 2 things: 1) I personally think its not good form to put null in the output of a Stream map operation, so to me that in fact does imply a filtering 2) You think its odd that the SELECT clause "applies a filter", but your ok with the fact that it can expand the query domain (from clause)? I think that is inconsistent. ___ hibernate-dev mailing list hibernate-dev@lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev
Re: [hibernate-dev] Why does implicit join translate to inner join?
Hi Steve, Thanks for your message. Of course, being explicit always has the advantage of ... being explicit. But my question here is really about the implicit join feature and how it works. 2018-02-22 15:57 GMT+01:00 Steve Ebersole : > it is better to be consistent in how implicit joins are handled. It is > far easier to impart to users that "implicit inner joins are always inner > joins" as opposed to "well implicit joins are interpreted relative to the > association being joined" > I don't disagree at all. In my opinion, the ideal approach would be "implicit joins are always outer joins" > Not to mention, adjusting the type of SQL join used for implicit jois > means I can no longer just look at the query and know what is happening in > terms of SQL joins - which is bad. > Why not? There's just an additional keyword between the generated tables: LEFT (or if you will, LEFT OUTER). > Not to mention, IMO interpreting these as inner joins is more OO-ish. > I'm curious about that, would you mind elaborating? And what's your opinion on the Stream analogy, where the current behaviour (implicit joins from the context of a SELECT clause) corresponds to Stream.map() potentially applying filters? ___ hibernate-dev mailing list hibernate-dev@lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev
Re: [hibernate-dev] Why does implicit join translate to inner join?
Yes, this could be made sensitive to where the implicit join occurs. However, there is another, better way... explicit joins. In our opinion originally (and nothing has changed my mind about this) it is better to be consistent in how implicit joins are handled. It is far easier to impart to users that "implicit inner joins are always inner joins" as opposed to "well implicit joins are interpreted relative to the association being joined". Not to mention, adjusting the type of SQL join used for implicit jois means I can no longer just look at the query and know what is happening in terms of SQL joins - which is bad. Not to mention, IMO interpreting these as inner joins is more OO-ish. I realize there are different points of view on this, but again, if you want explicit joining characteristics you can, you know, declare thew joins explicitly. On Thu, Feb 22, 2018 at 7:14 AM Lukas Eder wrote: > 2018-02-22 13:19 GMT+01:00 Vlad Mihalcea : > > > Hi, > > > > One possible reason was to have a single way of treating implicit joins. > > > > Sure, but if paths generated only outer joins, your statement would still > be true. > > > > In WHERE and ORDER BY clauses, implicit join makes sense to render an > > INNER JOIN. > > > > I agree with WHERE for most cases, but I decidedly do not agree with ORDER > BY. It is even more surprising when an ORDER BY clause implicitly filters > the results due to the presence of an implicit join path. For example (not > sure if that works in JPQL right now, but I don't see anything wrong with > the concept): > >SELECT c.firstName, c.lastName >FROM customer c >ORDER BY c.address.city.country.code > > So, this query would implicitly filter out customers without addresses (or > whose addresses had no cities, etc.) rather than applying default NULLS > FIRST / NULLS LAST semantics? > > There's also a case where an outer join is useful in the WHERE clause, > namely with IS NULL predicates: > >SELECT c.firstName, c.lastName >FROM customer c >WHERE c.address.city.country.code IS NULL > > In this case, the predicate could act like an Elvis operator on the whole > path as it evaluates to true if *any* of the values is null (address, city, > country, or country code). In the current case of generating inner joins, > only country codes that are NULL are retained. This is the only case I can > see where both join types would be reasonable in their own ways. For > consistency, I'd still opt for outer joins in this case as well. > > Lukas > ___ > hibernate-dev mailing list > hibernate-dev@lists.jboss.org > https://lists.jboss.org/mailman/listinfo/hibernate-dev > ___ hibernate-dev mailing list hibernate-dev@lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev
Re: [hibernate-dev] Why does implicit join translate to inner join?
2018-02-22 13:19 GMT+01:00 Vlad Mihalcea : > Hi, > > One possible reason was to have a single way of treating implicit joins. > Sure, but if paths generated only outer joins, your statement would still be true. > In WHERE and ORDER BY clauses, implicit join makes sense to render an > INNER JOIN. > I agree with WHERE for most cases, but I decidedly do not agree with ORDER BY. It is even more surprising when an ORDER BY clause implicitly filters the results due to the presence of an implicit join path. For example (not sure if that works in JPQL right now, but I don't see anything wrong with the concept): SELECT c.firstName, c.lastName FROM customer c ORDER BY c.address.city.country.code So, this query would implicitly filter out customers without addresses (or whose addresses had no cities, etc.) rather than applying default NULLS FIRST / NULLS LAST semantics? There's also a case where an outer join is useful in the WHERE clause, namely with IS NULL predicates: SELECT c.firstName, c.lastName FROM customer c WHERE c.address.city.country.code IS NULL In this case, the predicate could act like an Elvis operator on the whole path as it evaluates to true if *any* of the values is null (address, city, country, or country code). In the current case of generating inner joins, only country codes that are NULL are retained. This is the only case I can see where both join types would be reasonable in their own ways. For consistency, I'd still opt for outer joins in this case as well. Lukas ___ hibernate-dev mailing list hibernate-dev@lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev
Re: [hibernate-dev] Why does implicit join translate to inner join?
Hi, Maybe Steve remembers the reason why INNER JOIN was chosen. One possible reason was to have a single way of treating implicit joins. In WHERE and ORDER BY clauses, implicit join makes sense to render an INNER JOIN. Only when used in the SELECT clause only, LEFT JOIN would work just fine. So, I guess it was because we wanted to handle the implicit join in the same fashion no matter where it is used. But, I might be wrong, so I'm looking forward to Steve's answer too. Vlad On Thu, Feb 22, 2018 at 12:05 PM, Lukas Eder wrote: > Sure, there may be a chicken and egg situation between Hibernate and JPA, > but I'm trying to understand why this was specified the way it is, as I > find this quite surprising. > > 2018-02-22 10:59 GMT+01:00 andrea boriero : > > > Hi Lukas, > > > > I think it is based on JPA 2.1 spec, 4.4.4 Path Expressions , "Path > > expression navigability is composed using “inner join” semantics." > > > > On 22 February 2018 at 08:09, Lukas Eder wrote: > > > >> Hello, > >> > >> Vlad Mihalcea [1] was so kind to point me to this mailing list with my > >> question about implicit joins. The user guide [2] states that: > >> > >> "Implicit joins are always treated as inner joins." > >> > >> To me, this seems wrong, semantically, if implicit joins follow optional > >> (nullable) foreign key relationships. Let's assume that customers have > >> optional addresses. When we write > >> > >> SELECT c.firstName, c.lastName, c.address.city.country.code > >> FROM customer c > >> > >> The resulting query will INNER JOIN the customer / address / city / > >> country > >> tables, filtering out customers with no address, or addresses with no > >> cities, or cities with no countries (let's ignore the modelling aspect). > >> In > >> fact, I got a CROSS JOIN with join predicate in the WHERE clause, but > that > >> doesn't really change anything. Hence the SELECT clause applies a > filter, > >> which is rather surprising. To me, this seems simply wrong just like it > >> would be wrong for Stream.map() to apply any filters. > >> > >> However, I'm sure there must have been good reasons to default to this > >> behaviour, even in the presence of optional foreign keys. > >> > >> Does anyone know what those reasons are? > >> Cheers, > >> Lukas > >> > >> [1]: https://twitter.com/vlad_mihalcea/status/965927462684196864 > >> [2]: http://docs.jboss.org/hibernate/orm/5.2/userguide/ > >> html_single/Hibernate_User_Guide.html#hql-implicit-join > >> ___ > >> hibernate-dev mailing list > >> hibernate-dev@lists.jboss.org > >> https://lists.jboss.org/mailman/listinfo/hibernate-dev > >> > > > > > ___ > hibernate-dev mailing list > hibernate-dev@lists.jboss.org > https://lists.jboss.org/mailman/listinfo/hibernate-dev > ___ hibernate-dev mailing list hibernate-dev@lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev
Re: [hibernate-dev] Why does implicit join translate to inner join?
Sure, there may be a chicken and egg situation between Hibernate and JPA, but I'm trying to understand why this was specified the way it is, as I find this quite surprising. 2018-02-22 10:59 GMT+01:00 andrea boriero : > Hi Lukas, > > I think it is based on JPA 2.1 spec, 4.4.4 Path Expressions , "Path > expression navigability is composed using “inner join” semantics." > > On 22 February 2018 at 08:09, Lukas Eder wrote: > >> Hello, >> >> Vlad Mihalcea [1] was so kind to point me to this mailing list with my >> question about implicit joins. The user guide [2] states that: >> >> "Implicit joins are always treated as inner joins." >> >> To me, this seems wrong, semantically, if implicit joins follow optional >> (nullable) foreign key relationships. Let's assume that customers have >> optional addresses. When we write >> >> SELECT c.firstName, c.lastName, c.address.city.country.code >> FROM customer c >> >> The resulting query will INNER JOIN the customer / address / city / >> country >> tables, filtering out customers with no address, or addresses with no >> cities, or cities with no countries (let's ignore the modelling aspect). >> In >> fact, I got a CROSS JOIN with join predicate in the WHERE clause, but that >> doesn't really change anything. Hence the SELECT clause applies a filter, >> which is rather surprising. To me, this seems simply wrong just like it >> would be wrong for Stream.map() to apply any filters. >> >> However, I'm sure there must have been good reasons to default to this >> behaviour, even in the presence of optional foreign keys. >> >> Does anyone know what those reasons are? >> Cheers, >> Lukas >> >> [1]: https://twitter.com/vlad_mihalcea/status/965927462684196864 >> [2]: http://docs.jboss.org/hibernate/orm/5.2/userguide/ >> html_single/Hibernate_User_Guide.html#hql-implicit-join >> ___ >> hibernate-dev mailing list >> hibernate-dev@lists.jboss.org >> https://lists.jboss.org/mailman/listinfo/hibernate-dev >> > > ___ hibernate-dev mailing list hibernate-dev@lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev
Re: [hibernate-dev] Why does implicit join translate to inner join?
Hi Lukas, I think it is based on JPA 2.1 spec, 4.4.4 Path Expressions , "Path expression navigability is composed using “inner join” semantics." On 22 February 2018 at 08:09, Lukas Eder wrote: > Hello, > > Vlad Mihalcea [1] was so kind to point me to this mailing list with my > question about implicit joins. The user guide [2] states that: > > "Implicit joins are always treated as inner joins." > > To me, this seems wrong, semantically, if implicit joins follow optional > (nullable) foreign key relationships. Let's assume that customers have > optional addresses. When we write > > SELECT c.firstName, c.lastName, c.address.city.country.code > FROM customer c > > The resulting query will INNER JOIN the customer / address / city / country > tables, filtering out customers with no address, or addresses with no > cities, or cities with no countries (let's ignore the modelling aspect). In > fact, I got a CROSS JOIN with join predicate in the WHERE clause, but that > doesn't really change anything. Hence the SELECT clause applies a filter, > which is rather surprising. To me, this seems simply wrong just like it > would be wrong for Stream.map() to apply any filters. > > However, I'm sure there must have been good reasons to default to this > behaviour, even in the presence of optional foreign keys. > > Does anyone know what those reasons are? > Cheers, > Lukas > > [1]: https://twitter.com/vlad_mihalcea/status/965927462684196864 > [2]: http://docs.jboss.org/hibernate/orm/5.2/userguide/ > html_single/Hibernate_User_Guide.html#hql-implicit-join > ___ > hibernate-dev mailing list > hibernate-dev@lists.jboss.org > https://lists.jboss.org/mailman/listinfo/hibernate-dev > ___ hibernate-dev mailing list hibernate-dev@lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev
