timaebi opened a new issue, #14485:
URL: https://github.com/apache/grails-core/issues/14485
### Steps to Reproduce
The following query
```groovy
Author.where {
join('books', JoinType.LEFT)
books {
or {
isNull('name')
ilike('name', '%biography%')
}
}
}.list()
```
is executed with the following SQL code
```sql
select this_.id as id1_0_1_,
this_.version as version2_0_1_,
this_.name as name3_0_1_,
books_alia1_.author_id as author_i4_1_3_,
books_alia1_.id as id1_1_3_,
books_alia1_.id as id1_1_0_,
books_alia1_.version as version2_1_0_,
books_alia1_.name as name3_1_0_,
books_alia1_.author_id as author_i4_1_0_
from author this_
left outer join book books_alia1_ on this_.id =
books_alia1_.author_id
where (books_alia1_.name is null or lower(books_alia1_.name) like ?)
```
which is what is expected. Note that the books are joined using a LEFT OUTER
JOIN.
If I now take the exact same DetachedCriteria and use it as a sub query in
an other DetachedCriteria as follows
```groovy
Author.where {
'in'('id', Author.where {
join('books', JoinType.LEFT)
books {
or {
isNull('name')
ilike('name', '%biography%')
}
}
}.id())
}.list()
```
(I know this particular query is not very useful and serves for
demonstration purposes only.)
This results in the following SQL.
```sql
select this_.id as id1_0_0_, this_.version as version2_0_0_, this_.name as
name3_0_0_
from author this_
where this_.id in (select this_.id as y0_
from author this_
inner join book books_alia1_ on this_.id =
books_alia1_.author_id
where ((books_alia1_.name is null or
lower(books_alia1_.name) like ?)))
```
Note that the books in the subquery are now joined with an INNER JOIN
instead of a LEFT JOIN which is what one would expect.
### Expected Behaviour
The books in the subquery should also be joined with a left join, if
configured so with a `join('books, JoinType.LEFT)`.
### Actual Behaviour
The LEFT JOIN specification is not considered in subqueries
### Environment Information
- **Operating System**: macOS
- **GORM Version:** 7.0.8 (but the error already occurs in 6.1.10 and a
backport to 6.x would be appreciated 🙏 )
- **Grails Version (if using Grails):** 4.0.6
- **JDK Version:** 1.8.0_231
### Example Application
Example application with an integration test which should pass:
https://github.com/timaebi/gorm-subquerry-left-join-issue
--
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]
