LEFT JOIN with detached subcriteria does not work

[timaebi]

Steps to Reproduce

The following query

Author.where {
            join('books', JoinType.LEFT)
            books {
                or {
                    isNull('name')
                    ilike('name', '%biography%')
                }
            }
}.list()

is executed with the following SQL code

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

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.

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

[timaebi]

Is there any chance that this could be fixed? Or is there a workaround available? We start having many places where we are blocked by this issue.

[snimavat]

I am facing the same issue - when using the join in subquery