[I] Hibernate 7: `createAlias` joins the association twice, and ignores the join type for the alias [grails-core]

["sbglasius (via GitHub)" <gi...@apache.org>]

sbglasius opened a new issue, #16562:
URL: https://github.com/apache/grails-core/issues/16562

   ### Expected Behavior
   
   `createAlias('likes', 'l')` joins the association once, all criteria on `l.*` use that join, and
   `createAlias('like', 'l', CriteriaSpecification.LEFT_JOIN)` makes it a left outer join, as with Hibernate 5.
   
   ### Actual Behaviour
   
   With `grails-data-hibernate7` 8.0.0, the association is joined twice:
   
   ```groovy
   Team.withCriteria {
       createAlias('likes', 'l')
       eq('l.id', 1L)
   }
   ```
   
   ```sql
   from team t1_0
   join team_like l1_0 on t1_0.id=l1_0.team_likes_id   -- from the DetachedAssociationCriteria of createAlias, unused
   join team_like l2_0 on t1_0.id=l2_0.team_likes_id   -- the alias l
   where 1=1 and l2_0.like_id=?
   ```
   
   On a to-many association the unused join multiplies the rows. `handleAssociationCriteria` registers its join under the
   association path (`likes`), and `ExpressionResolver` resolves the alias `l` to a new join because `JoinTracker` only
   knows `likes`.
   
   The join for the alias is also always an inner join. `discoverAliases` registers the alias with
   `new HibernateAlias(dac.getAssociationPath(), dac.getAlias())`, without the join type `createAlias` was called with:
   
   ```groovy
   User.withCriteria {
       createAlias('like', 'l', 1) // LEFT_JOIN
       or {
           isNull('like')
           isNotNull('l.favoriteNumbers')
       }
   }
   ```
   
   ```sql
   from user u1_0
   left join like l1_0 on l1_0.id=u1_0.like_id
   join like l2_0 on l2_0.id=u1_0.like_id
   where 1=1 and (l1_0.id is null or l2_0.favorite_numbers is not null)
   ```
   
   Users without a like are not returned. Hibernate 5 returns them.
   
   ### Steps To Reproduce
   
   The two criteria above, with `Team hasMany [likes: Like]` and `User { Like like }`.
   
   ### Environment Information
   
   - Grails / GORM 8.0.0, `grails-data-hibernate7`, Hibernate 7.4.10.Final
   - PostgreSQL 17, Java 21
   
   ### Example Application
   
   https://github.com/gpc/grails-postgresql-extensions/pull/136, see `PgArrayCriteriaWithAliasIntegrationSpec`
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@grails.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] Hibernate 7: `createAlias` joins the association twice, and ignores the join type for the alias [grails-core]

["jdaugherty (via GitHub) via notifications" <no...@grails.apache.org>]

jdaugherty closed issue #16562: Hibernate 7: `createAlias` joins the association twice, and ignores the join type for the alias
URL: https://github.com/apache/grails-core/issues/16562


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@grails.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org