[3.1.4 + Hibernate4] DetachedCriteria returning list of domains used as subquery causes invalid SQL with empty SELECT clause to be generated
- Dominant language
- Groovy
- Stars
- 2.9k
- Forks
- 975
- Avg merge
- 1d 22h
- Merged PRs (30d)
- 92
Description
Having domains:
```
class Parent {
String name
static hasMany = [children: Child]
}
class Child {
String name
Related related
static belongsTo = [parent: Parent]
}
```
User could execute following query with detached criteria as subquery:
```
DetachedCriteria subquery = new DetachedCriteria(Child).build {
isNotNull 'name'
}
Parent.createCriteria().list {
inList 'children', subquery
}
```
But this will cause `Syntax error in SQL statement` error with `.. (SELECT FROM CHILD ..` as cause of the error. SELECT is empty. Workaround is to use projection in detached criteria, for example
```
projections {
groupProperty 'id'
}
```
Example project is here: https://github.com/droggo/GrailsSubqueries
Integration Spec to demo this problem: https://github.com/droggo/GrailsSubqueries/blob/master/src/integration-test/groovy/error/DetachedCriteriaDomainSubqueryIntegrationSpec.groovy
Contributor guide
Research direction
Start with src/integration-test/groovy/error/DetachedCriteriaDomainSubqueryIntegrationSpec.groovy from the linked example project and run the integration spec to reproduce the invalid SQL. Trace the DetachedCriteria subquery handling for Parent.createCriteria() and the children association. Done means the query works without a projection workaround and no longer generates an empty SELECT clause.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- groovy
- Domain
- backend, databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 38/100