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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
