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 <andrea(a)hibernate.org&gt;:

2018-02-22 13:19 GMT+01:00 Vlad Mihalcea <mihalcea.vlad(a)gmail.com&gt;: Sure, but if paths generated only outer joins, your statement would still be true. 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

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 <steve(a)hibernate.org&gt;: I don't disagree at all. In my opinion, the ideal approach would be "implicit joins are always outer joins" Why not? There's just an additional keyword between the generated tables: LEFT (or if you will, LEFT OUTER). 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?

2018-02-22 21:39 GMT+01:00 Steve Ebersole <steve(a)hibernate.org&gt;: I take you kinda regret the feature? :-) But I'm certain that many users find it extremely useful. 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. 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. Sure, that's always an option! Perhaps a nice blog post for Vlad? ;-) 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. 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 null from a Stream.map() operation. It is equivalent to returning a tuple / object where one of the attributes is null, which is probably a bit less bad form. Technically, the canonical approach to implementing "implicit joins" is not to implement joins at all, but correlated subqueries. My original query example: SELECT c.firstName, c.lastName, c.address.city.country.code FROM customer c Could easily be translated to: SELECT c.first_name, c.last_name, ( SELECT co.code FROM country co JOIN city ci USING (country_id) JOIN address a USING (city_id) WHERE a.address_id = c.address_id ) FROM customer c While quickly being more verbose than a join-based solution, this looks very natural. In addition to that, we get the desired behaviour of having either the country code if all of address, city, country, and code are present, or NULL if any of these things are absent. But never an implicit filter. This approach would work well logically, regardless where the implicit join is located (including WHERE, GROUP BY, ORDER BY). However, it is quickly prohibitive, performance wise, in pretty much every database. Even DB2 has trouble factoring out similar join graphs to avoid doing them several times. Yet in principle, correlated subqueries and left joins can be transformed into each other in many cases, so left join is a pragmatic improvement here, both from a readability and from a performance perspective. Inner joins are not, because they are semantically very different. I hope this clarifies why I don't think that modifying the "query domain", as you call it, is inconsistent with my mental model of SQL-style relational algebra. At least as long as the behaviour of SELECT * is maintained correctly (i.e. the implicitly joined columns will not be projected). Lukas