JPA query always returns all rows rather than rows that match criteria

Question

I have a JPA query written like this:

public interface MyDataRepository extends CrudRepository<MyData, String> {
    @Query("select md " +
            "from MyData md " +
            "join fetch md.child c " +
            "where c.date = :date")
    List<MyData> getMyDataOfDate(@NotNull LocalDate date);


    @Query("select md " +
            "from MyData md " +
            "join fetch md.child c " +
            "where c.name = :name")
    List<MyData> getMyDataOfName(@NotNull String name);

    @Query("select md " +
            "from MyData md " +
            "join fetch md.child c " +
            "where md.type = :type")
    List<MyData> getMyDataOfType(@NotNull String type);
}

Class MyData and Child are defined as:

class MyData {
    String id;
    String type;

    @ManyToOne
    @JoinColumn(name = "CHILD_ID", referencedColumnName = "ID", nullable = false, updatable = false)
    Child child;        
}


class Child {
    String id;
    String name;
    LocalDate date;
}

The problem is that whenever I call the getMyDataOfDate method or getMyDataOfName method, they always return ALL rows rather than the rows that matches the where condition, as if the where clause never exists.

However, the getMyDataOfType method works fine. The difference of this method is that the where condition is on a property of md, not c.

What did I do wrong?

See if the following can help you: https://stackoverflow.com/questions/61997577/how-to-query-jpa-localdatetime-field-with-a-localdate-value — JCompetence, Feb 16 '22 at 18:25
@SMA Thanks. My problem doesn't seem to be related to LocalDate format. Actually, this is an simplified version of my code. It does not work for String field either, as if the where clause never exists. It always returns all rows. — ZZZ, Feb 16 '22 at 18:32
These queries should be throwing exceptions/errors, as you cannot filter on a fetch join - it isn't supported by JPQL/JPA. "join fetch" is used to determine what is returned, not what is used for filtering. Try "select md from MyData md join md.child c where c.name = :name" instead. — Chris, Feb 16 '22 at 18:48
@Chris Thanks for your reply. But the third method works with "join fetch" with condition on MyData. I also tried a query with "join" rather than "join fetch" and it still does not work. — ZZZ, Feb 16 '22 at 18:55
Join fetch is allowed, it just doesn't mean or do what you might think it does. I'll try to explain better in an answer, but try the query changes, as they are the correct way to do what you seem to want to do. The third option works because you aren't using the defined fetch join *c* in the filtering criteria, you are just telling JPA that all child instances for MyData need to be (eagerly) fetched in the same SQL. — Chris, Feb 16 '22 at 18:56
I tried the new query with "join" rather than "join fetch" but it still does not work. — ZZZ, Feb 16 '22 at 19:02
Then you'll have to turn on SQL logging to see what is being generated. Your query should be causing exceptions/warnings at the very least, so I suspect that it wasn't what was actually being used by your application, which means the new query definition might not be being used either. — Chris, Feb 16 '22 at 19:11

score 2 · Answer 1 · answered Feb 16 '22 at 19:09

JPA does not allow filtering on join fetches. Reasons being is that when you specify a join fetch, you are telling JPA to fetch the parent and all its children defined by that relationship in the managed entities it returns. If filtering were allowed, the list of children, the relationship in the parent, might not reflect what is actually in the database. Take the case of Parent with many children

"Select parents from Parent p fetch join p.children c where c.firstName = 'Bob'"

For such a query, when you get a list of parents and calling getChildren on them, do you expect to see all their children or a list that only contains children named Bob? If the later (which is the only way to do so), how should JPA handle changes to a parents children list, and know what to do with the not-fetched children?

This is why JPA doesn't allow filtering over fetch joins, and they restrict it across all relationships to be consistent. If you want the parents who have children with the firstName of 'Bob', it would look like:

"Select parents from Parent p join p.children c where c.firstName = 'Bob'"

Every parent returned will be a complete representation of its state in the database based on its mappings; so accessing parent.getChildren will return the current state of its children list and not something affected by the way it was fetched.

Thanks for your reply. However, query that uses join does not work either: "select md from MyData md join md.child c where c.date = :date" — ZZZ, Feb 16 '22 at 19:49
This is a Spring thing then, not a query or JPQL one and it is ignoring your query annotations. For the working one, Spring will create a query similar to what you put in the annotation based on the getMyDataOfType, so gets the same results with or without it. For getMyDataOfDate though it cannot as there is no Date with your instance, so it selects all rows. Spring is being a PITA by silently ignoring everything on you — Chris, Feb 16 '22 at 20:21

JPA query always returns all rows rather than rows that match criteria

1 Answers1