eclipse-ee4j / eclipse-ee4j/eclipselink
Batch Fetch hints ignored for simple "find by id" JPQL query because Eclipselink swaps out query internally
- Dominant language
- Java
- Stars
- 246
- Forks
- 202
- Avg merge
- 1d 22h
- Merged PRs (30d)
- 14
Description
We have a very simple JPQL query
```sql
var jpql = SELECT o FROM SomeObject o WHERE o.id = :id
entityManager().createQuery(jpql, SomeObject.class)
.setParameter("id", id)
.setHint(QueryHints.BATCH_TYPE, BatchFetchType.IN)
.setHint(QueryHints.BATCH, "o.relationA.relationB.relationC")
.setHint(QueryHints.BATCH, "o.relationD.relationA.relationB.relationC")
.getSingleResult();
```
and both batch fetches are ignored by EclipseLink and we see lots of small queries instead (n+1 problem).
However when changing the above query to
```sql
var jpql = SELECT o FROM SomeObject o JOIN o.relationA ra WHERE o.id = :id AND ra IS NOT NULL
```
all batch fetches start to work. In our case the `relationA` is `@OneToOne` and `not null` so the modified query behaves the same as the original one.
Debugging revealed that the built `ReadObjectQuery` does have a correct `batchFetchPolicy` (in parent class `ObjectLevelReadQuery`) assigned in both cases. However in the first case EclipseLink decides to throw away this query and replace it with another one which does not have a `batchFetchPolicy` assigned. Thus batch fetch does not work in the first case.
The code responsible is
https://github.com/eclipse-ee4j/eclipselink/blob/b7c997804f39187fb2907636d8403d694a318cfb/foundation/org.eclipse.persistence.core/src/org/eclipse/persistence/queries/DatabaseQuery.java#L858-L868
In line `866` the query in `queryToExecute` (with `batchFetchPolicy`) will be replaced with `customQuery`(without `batchFetchPolicy`) which causes the issue to appear.
The code above calls `ObjectLevelReadQuery.checkForCustomQuery` which in turn calls `ReadObjectQuery.checkCustomQueryFlag`:
https://github.com/eclipse-ee4j/eclipselink/blob/b7c997804f39187fb2907636d8403d694a318cfb/foundation/org.eclipse.persistence.core/src/org/eclipse/persistence/queries/ReadObjectQuery.java#L319-L356
The large `if` statement in line `333` evaluates to `true` and finally the method returns `true` in line `348`. I am pretty sure this large `if` isn't complete and should evaluate to `false` in the example above.
As can be seen the `if` checks for `JOIN` which is why the modified example fixes the issue. In that case the methods returns `NULL`, the original query will not be replaced by EclipseLink and batch fetch works as requested by application code.
Contributor guide
Research direction
Start in foundation/org.eclipse.persistence.core/src/org/eclipse/persistence/queries/DatabaseQuery.java around lines 858-868, then follow ObjectLevelReadQuery.checkForCustomQuery into ReadObjectQuery.checkCustomQueryFlag around lines 319-356. Reproduce the simple find-by-id JPQL query with the batch hints and compare it with the JOIN variant. Done means the original query preserves its batchFetchPolicy and the requested batch fetches work without the JOIN workaround.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- java
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Clearly specified
- Newbie friendliness
- 35/100