eclipse-ee4j / eclipse-ee4j/eclipselink

Batch Fetch hints ignored for simple "find by id" JPQL query because Eclipselink swaps out query internally

Open
#862 6 comments 0 reactions 0 assignees View on GitHub
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

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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.