[hibernate-dev] Why does implicit join translate to inner join?
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
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
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
[hibernate-dev] [OGM] Validating our artifacts don't rely on JBoss-only dependencies
Hello, TL;DR: can I move hibernate-ogm-infinispan-remote tests to the integrationtest module, since they are actually integration tests? During one of our previous IRC meetings, I mentioned that we did some changes in the Hibernate Search POMs to remove the need for custom settings when building. The idea was to hard-code the JBoss repositories in the POMs of modules that need them, so that other modules would be built using Maven Central only. This way, we would make sure our modules only rely on dependencies that are available in Maven Central, not some obscure artifacts only available in the JBoss Maven repository. I am trying to do the same in OGM, but there is one problem: for this to be possible at all, integration tests that do require those exotic dependencies need to be in a separate Maven module. It turns out some tests implemented in the infinispan-remote module require to spin up an Infinispan server, which does have exotic dependencies (it's basically a WildFly server, if I understood correctly). Thus, in order to remove the need for custom settings when building, I would need to move those tests to the integrationtest module. They are integration tests, after all, since they need a running Infinispan server to work. Would anyone object to that? -- Yoann Rodiere yo...@hibernate.org / yrodi...@redhat.com Software Engineer Hibernate NoORM team ___ 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] [OGM] Validating our artifacts don't rely on JBoss-only dependencies
Not sure about it, those are specific integration tests for infinispan remote. The integrationtest folder is more to test the hibernate-ogm-modules and have some checks that the dialects work with WildFly really. > after all, since they need a running Infinispan server to work. All the tests in Hibernat OGM are basically integration tests, except for embedded databases they need a remote db to work. In fact, I think it was a mistake to put them in the maven test phase. They should be in the integration-test one. Can't we keep them in the same module but in a different profile disabled by default? Actually, it should already skip them with the option skipITs. On Thu, Feb 22, 2018 at 11:07 AM, Yoann Rodiere wrote: > Hello, > > TL;DR: can I move hibernate-ogm-infinispan-remote tests to the > integrationtest module, since they are actually integration tests? > > During one of our previous IRC meetings, I mentioned that we did some > changes in the Hibernate Search POMs to remove the need for custom settings > when building. The idea was to hard-code the JBoss repositories in the POMs > of modules that need them, so that other modules would be built using Maven > Central only. This way, we would make sure our modules only rely on > dependencies that are available in Maven Central, not some obscure > artifacts only available in the JBoss Maven repository. > > I am trying to do the same in OGM, but there is one problem: for this to be > possible at all, integration tests that do require those exotic > dependencies need to be in a separate Maven module. > It turns out some tests implemented in the infinispan-remote module require > to spin up an Infinispan server, which does have exotic dependencies (it's > basically a WildFly server, if I understood correctly). > > Thus, in order to remove the need for custom settings when building, I > would need to move those tests to the integrationtest module. They are > integration tests, after all, since they need a running Infinispan server > to work. > > Would anyone object to that? > > > -- > Yoann Rodiere > yo...@hibernate.org / yrodi...@redhat.com > Software Engineer > Hibernate NoORM team > ___ > 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] [OGM] Validating our artifacts don't rely on JBoss-only dependencies
> The integrationtest folder is more to test the hibernate-ogm-modules > and have some > checks that the dialects work with WildFly really. I could move the current integration tests to integrationtest/wildfly and create a new integrationtest/infinispan-remote module? Then it would be clear which test is about which integration. > Can't we keep them in the same module but in a different profile > disabled by default? > Actually, it should already skip them with the option skipITs. Maybe we could... But I'm a bit worried about having hard-coded repositories in POMs that will be consumed by our users. From what I understood it caused some problems in the past, because those hard-coded repos have the nasty habit of contaminating every consuming POM. Even with a profile, some users depending on infinispan-remote and activating an "integrationtest" profile of their own would unknowingly enable the JBoss repositories, I think. All in all, if we really need to keep them in the same module, I think it would be safer not to proceed with my changes. But of course my preference would be to move them to another module :) On Thu, 22 Feb 2018 at 13:30 Davide D'Alto wrote: > Not sure about it, those are specific integration tests for infinispan > remote. > The integrationtest folder is more to test the hibernate-ogm-modules > and have some > checks that the dialects work with WildFly really. > > > after all, since they need a running Infinispan server > to work. > > All the tests in Hibernat OGM are basically integration tests, except > for embedded databases > they need a remote db to work. > > In fact, I think it was a mistake to put them in the maven test phase. > They should be in the integration-test one. > > Can't we keep them in the same module but in a different profile > disabled by default? > Actually, it should already skip them with the option skipITs. > > On Thu, Feb 22, 2018 at 11:07 AM, Yoann Rodiere > wrote: > > Hello, > > > > TL;DR: can I move hibernate-ogm-infinispan-remote tests to the > > integrationtest module, since they are actually integration tests? > > > > During one of our previous IRC meetings, I mentioned that we did some > > changes in the Hibernate Search POMs to remove the need for custom > settings > > when building. The idea was to hard-code the JBoss repositories in the > POMs > > of modules that need them, so that other modules would be built using > Maven > > Central only. This way, we would make sure our modules only rely on > > dependencies that are available in Maven Central, not some obscure > > artifacts only available in the JBoss Maven repository. > > > > I am trying to do the same in OGM, but there is one problem: for this to > be > > possible at all, integration tests that do require those exotic > > dependencies need to be in a separate Maven module. > > It turns out some tests implemented in the infinispan-remote module > require > > to spin up an Infinispan server, which does have exotic dependencies > (it's > > basically a WildFly server, if I understood correctly). > > > > Thus, in order to remove the need for custom settings when building, I > > would need to move those tests to the integrationtest module. They are > > integration tests, after all, since they need a running Infinispan server > > to work. > > > > Would anyone object to that? > > > > > > -- > > Yoann Rodiere > > yo...@hibernate.org / yrodi...@redhat.com > > Software Engineer > > Hibernate NoORM team > > ___ > > hibernate-dev mailing list > > hibernate-dev@lists.jboss.org > > https://lists.jboss.org/mailman/listinfo/hibernate-dev > -- Yoann Rodiere yo...@hibernate.org / yrodi...@redhat.com Software Engineer Hibernate NoORM team ___ 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?
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?
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?
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
