apache / apache/grails-core

[3.1.4 + Hibernate4] DetachedCriteria returning list of domains used as subquery causes invalid SQL with empty SELECT clause to be generated

Open
#14,648 1 comment 0 reactions 0 assignees View on GitHub
relates-to: grails-data-hibernate5
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.