Grails 4.0.3 - Domain.findByOwningObject(owningObject) throws JdbcSQL error
- Dominant language
- Groovy
- Stars
- 2.9k
- Forks
- 975
- Avg merge
- 1d 22h
- Merged PRs (30d)
- 92
Description
When creating two Domain classes with a one-to-one relationship, if the foriegn_key field is on the owning object, attempting to use dynamic finders on the child object using the owning object causes an error:
`org.h2.jdbc.JdbcSQLDataException: Parameter "#2" is not set;`
In the sample application:
`Dog` belongs to `DogHouse`
`DogHouse` has a field "the_dog" on the DB table referencing the Dog that's in the dog house
The following code block will throw the above Exception:
```
DogHouse house = DogHouse.get(1)
Dog d = Dog.findByDogHouse(house)
```
The SQL generated is:
`select this_.id as id1_2_0_, this_.version as version2_2_0_, this_.name as name3_2_0_ from dog this_ where this_.id=? limit ? [90012-199]`
By comparison, in the example, `Cat` belongs to `CatHouse`, but the reference is the other way around... "cat" table has a column called "the_cat_house". Using the same logic above, you are able to find the CatHouse using
```
Cat c = Cat.findByCatHouse(catHouse)
```
The SQL to generate the tables is:
```
2020-08-25 14:24:00.070 DEBUG --- [ restartedMain] org.hibernate.SQL : create table cat (id bigint generated by default as identity, version bigint not null, name varchar(255) not null, the_cat_house bigint not null, primary key (id))
2020-08-25 14:24:00.076 DEBUG --- [ restartedMain] org.hibernate.SQL : create table cat_house (id bigint generated by default as identity, version bigint not null, size varchar(255) not null, primary key (id))
2020-08-25 14:24:00.077 DEBUG --- [ restartedMain] org.hibernate.SQL : create table dog (id bigint generated by default as identity, version bigint not null, name varchar(255) not null, primary key (id))
2020-08-25 14:24:00.078 DEBUG --- [ restartedMain] org.hibernate.SQL : create table dog_house (id bigint generated by default as identity, version bigint not null, the_dog bigint, size varchar(255) not null, primary key (id))
```
Full stack trace:
```
2020-08-25 14:24:07.830 ERROR --- [nio-8080-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper : Parameter "#2" is not set; SQL statement:
select this_.id as id1_2_0_, this_.version as version2_2_0_, this_.name as name3_2_0_ from dog this_ where this_.id=? limit ? [90012-199]
2020-08-25 14:24:07.863 ERROR --- [nio-8080-exec-2] o.g.web.errors.GrailsExceptionResolver : JdbcSQLDataException occurred when processing request: [GET] /sample/index
Parameter "#2" is not set; SQL statement:
select this_.id as id1_2_0_, this_.version as version2_2_0_, this_.name as name3_2_0_ from dog this_ where this_.id=? limit ? [90012-199]. Stacktrace follows:
java.lang.reflect.InvocationTargetException: null
at org.grails.core.DefaultGrailsControllerClass$ReflectionInvoker.invoke(DefaultGrailsControllerClass.java:211)
at org.grails.core.DefaultGrailsControllerClass.invoke(DefaultGrailsControllerClass.java:188)
at org.grails.web.mapping.mvc.UrlMappingsInfoHandlerAdapter.handle(UrlMappingsInfoHandlerAdapter.groovy:90)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1040)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:943)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
at org.grails.web.servlet.mvc.GrailsWebRequestFilter.doFilterInternal(GrailsWebRequestFilter.java:77)
at org.grails.web.filters.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:67)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: org.hibernate.exception.DataException: could not extract ResultSet
at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:52)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:69)
at org.hibernate.loader.Loader.getResultSet(Loader.java:2173)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1936)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1898)
at org.hibernate.loader.Loader.doQuery(Loader.java:937)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:340)
at org.hibernate.loader.Loader.doList(Loader.java:2695)
at org.hibernate.loader.Loader.doList(Loader.java:2678)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2512)
at org.hibernate.loader.Loader.list(Loader.java:2507)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:109)
at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1972)
at org.hibernate.internal.CriteriaImpl.list(CriteriaImpl.java:370)
at org.grails.orm.hibernate.query.AbstractHibernateQuery.singleResultViaListCall(AbstractHibernateQuery.java:807)
at org.grails.orm.hibernate.query.AbstractHibernateQuery.singleResult(AbstractHibernateQuery.java:794)
at org.grails.datastore.gorm.finders.AbstractFindByFinder.invokeQuery(AbstractFindByFinder.java:35)
at org.grails.datastore.gorm.finders.AbstractFindByFinder$1.doInSession(AbstractFindByFinder.java:29)
at org.grails.datastore.mapping.core.DatastoreUtils.execute(DatastoreUtils.java:319)
at org.grails.datastore.gorm.finders.AbstractFinder.execute(AbstractFinder.java:42)
at org.grails.datastore.gorm.finders.AbstractFindByFinder.doInvokeInternal(AbstractFindByFinder.java:27)
at org.grails.datastore.gorm.finders.DynamicFinder.invoke(DynamicFinder.java:254)
at org.grails.datastore.gorm.finders.DynamicFinder.invoke(DynamicFinder.java:392)
at org.grails.datastore.gorm.GormStaticApi.methodMissing(GormStaticApi.groovy:185)
at org.grails.datastore.gorm.GormEntity$Trait$Helper.staticMethodMissing(GormEntity.groovy:777)
at one.to.one.find.SampleController.index(SampleController.groovy:26)
... 13 common frames omitted
Caused by: org.h2.jdbc.JdbcSQLDataException: Parameter "#2" is not set; SQL statement:
select this_.id as id1_2_0_, this_.version as version2_2_0_, this_.name as name3_2_0_ from dog this_ where this_.id=? limit ? [90012-199]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:587)
at org.h2.message.DbException.getJdbcSQLException(DbException.java:427)
at org.h2.message.DbException.get(DbException.java:205)
at org.h2.message.DbException.get(DbException.java:181)
at org.h2.expression.Parameter.checkSet(Parameter.java:94)
at org.h2.command.Prepared.checkParameters(Prepared.java:180)
at org.h2.command.CommandContainer.query(CommandContainer.java:144)
at org.h2.command.Command.executeQuery(Command.java:202)
at org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:115)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:60)
... 37 common frames omitted
```
### Task List
- [X] Steps to reproduce provided
- [X] Stacktrace (if present) provided
- [X] Example that reproduces the problem uploaded to Github
- [X] Full description of the issue provided (see below)
### Steps to Reproduce
1. Download sample application
2. Start up sample application (data gets created in boot strap)
3. Go to SampleController index, log will show SQL and where data crashes
### Expected Behaviour
You should be able to find the object using the parent object, regardless of the database table structure
### Actual Behaviour
In the described situation, instead of finding the correct result, an error is thrown
### Environment Information
- **Operating System**: macOS Mojave Version 10.14.6
- **Grails Version:**
grails -version
| Grails Version: 4.0.3
| JVM Version: 1.8.0_262
- **JDK Version:**
java -version
openjdk version "1.8.0_262"
OpenJDK Runtime Environment (Zulu 8.48.0.51-CA-macosx) (build 1.8.0_262-b19)
OpenJDK 64-Bit Server VM (Zulu 8.48.0.51-CA-macosx) (build 25.262-b19, mixed mode)
- **Container Version (If Applicable):** N/A
### Example Application
https://github.com/Trebla7th/one-to-one-find
Contributor guide
Research direction
Start by downloading the linked one-to-one-find sample application and reproducing the failure from SampleController.index after startup. Compare the Dog/DogHouse and Cat/CatHouse mappings and generated SQL; done means Dog.findByDogHouse(house) returns the expected object without the H2 unset-parameter error.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- groovy
- Domain
- backend, database
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100