Skip to content

Regression for method-based query where predicate compares primary key of @ManyToOne relationship #3349

New issue
New issue
Open
#3922
Open
Regression for method-based query where predicate compares primary key of @ManyToOne relationship#3349
#3922
Assignees
christophstrobl
Labels
type: enhancementA general enhancement
@scordio

Description

@scordio
scordio
opened on Feb 2, 2024 · edited by scordio
Contributor

Given the following entities:

@Entity
class Author {

  @Id @GeneratedValue(strategy = GenerationType.AUTO)
  private Long id;

  private String name;

  // getters and setters omitted
}

@Entity
class Book {

  @Id @GeneratedValue(strategy = GenerationType.AUTO)
  private Long id;

  private String name;

  @ManyToOne(fetch = FetchType.LAZY)
  private Author author;

  // getters and setters omitted
}

and the following repository:

interface BookRepository extends Repository<Book, Long> {

  Book save(Book book);

  List<Book> findAllByAuthorId(Long authorId);

}

findAllByAuthorId seems to trigger an unnecessary JOIN.

I initially noticed this behavior on Spring Boot 2.7.14 with DB2 for z/OS so I tried to reproduce it locally on newer Spring Boot versions.

Surprisingly, 3.0.x and 3.1.x seem to work properly, i.e., no unnecessary JOIN, while the JOIN is back again on 3.2.x and 3.3.x. That's why I mentioned it as a regression in the title.

Here are the derived queries for each Spring Boot version with an H2 database, captured with P6Spy:

2.7.18

select book0_.id as id1_1_, book0_.author_id as author_i3_1_, book0_.name as name2_1_ from book book0_ left outer join author author1_ on book0_.author_id=author1_.id where author1_.id=1;

3.0.13

select b1_0.id,b1_0.author_id,b1_0.name from book b1_0 where b1_0.author_id=1;

3.1.8

select b1_0.id,b1_0.author_id,b1_0.name from book b1_0 where b1_0.author_id=1;

3.2.2

select b1_0.id,b1_0.author_id,b1_0.name from book b1_0 left join author a1_0 on a1_0.id=b1_0.author_id where a1_0.id=1;

3.3.0-SNAPSHOT

select b1_0.id,b1_0.author_id,b1_0.name from book b1_0 left join author a1_0 on a1_0.id=b1_0.author_id where a1_0.id=1;

Reproducers:

Activity

scordio
changed the title [-]Regression for method-based query where predicate compares primary key of @ManyToOne relationship[/-] [+]Regression for method-based query where predicate compares primary key of `@ManyToOne` relationship[/+] on Feb 2, 2024
spring-projects-issues
added
status: waiting-for-triageAn issue we've not yet triaged
on Feb 2, 2024
christophstrobl

christophstrobl commented on Feb 2, 2024

@christophstrobl
christophstrobl
on Feb 2, 2024
Member

thanks for reporting and the reproducer - have you tried to do the same query with plain JPA/hibernate as well?

scordio

scordio commented on Feb 3, 2024

@scordio
scordio
on Feb 3, 2024
ContributorAuthor

I suppose you're referring to JPQL, right @christophstrobl?

I've just added the following to the reproducers:

@Query("SELECT b FROM Book b WHERE b.author.id = :authorId")
List<Book> findAllByAuthorIdWithJPQL(@Param("authorId") Long authorId);

It looks good on all versions:

2.7.18

select book0_.id as id1_1_, book0_.author_id as author_i3_1_, book0_.name as name2_1_ from book book0_ where book0_.author_id=1;

3.0.13

select b1_0.id,b1_0.author_id,b1_0.name from book b1_0 where b1_0.author_id=1;

3.1.8

select b1_0.id,b1_0.author_id,b1_0.name from book b1_0 where b1_0.author_id=1;

3.2.2

select b1_0.id,b1_0.author_id,b1_0.name from book b1_0 where b1_0.author_id=1;

3.3.0-SNAPSHOT

select b1_0.id,b1_0.author_id,b1_0.name from book b1_0 where b1_0.author_id=1;
scordio

scordio commented on Feb 3, 2024

@scordio
scordio
on Feb 3, 2024 · edited by scordio
ContributorAuthor

For completeness, I also tried with JpaSpecificationExecutor and a Specification:

private static Specification<Book> byAuthorId(Long authorId) {
  return (root, query, builder) -> builder.equal(root.get("author").get("id"), authorId);
}

It also looks good on all versions:

2.7.18

select book0_.id as id1_1_, book0_.author_id as author_i3_1_, book0_.name as name2_1_ from book book0_ where book0_.author_id=1;

3.0.13

select b1_0.id,b1_0.author_id,b1_0.name from book b1_0 where b1_0.author_id=1;

3.1.8

select b1_0.id,b1_0.author_id,b1_0.name from book b1_0 where b1_0.author_id=1;

3.2.2

select b1_0.id,b1_0.author_id,b1_0.name from book b1_0 where b1_0.author_id=1;

3.3.0-SNAPSHOT

select b1_0.id,b1_0.author_id,b1_0.name from book b1_0 where b1_0.author_id=1;
quaff

quaff commented on Feb 4, 2024

@quaff
quaff
on Feb 4, 2024
Contributor

have you tried to do the same query with plain JPA/hibernate as well?

	CriteriaBuilder cb = entityManager.getCriteriaBuilder();
	CriteriaQuery<Book> cq = cb.createQuery(Book.class);
	Root<Book> root = cq.from(Book.class);
	cq.select(root).where(cb.equal(root.get("author").get("id"), homer.getId()));
	entityManager.createQuery(cq).getResultList();

will produce expected sql

select b1_0.id,b1_0.author_id,b1_0.name from book b1_0 where b1_0.author_id=?
quaff

quaff commented on Feb 4, 2024

@quaff
quaff
on Feb 4, 2024 · edited by quaff
Contributor

@scordio It works as expected if you downgrade hibernate to 6.2.20.Final which is used by 3.1.8, I confirm it is a regression introduced by hibernate 6.4.1.Final, I reported it to hibernate team, see https://hibernate.atlassian.net/browse/HHH-17706.

scordio

scordio commented on Feb 4, 2024

@scordio
scordio
on Feb 4, 2024 · edited by scordio
ContributorAuthor

Thanks @quaff for helping pinpoint the root cause!

Closing in favor of HHH-17706 and hibernate/hibernate-orm#7782.

scordio
closed this as not plannedon Feb 4, 2024
scordio

scordio commented on Feb 5, 2024

@scordio
scordio
on Feb 5, 2024
ContributorAuthor

I am reopening this issue as hibernate/hibernate-orm#7782 has been rejected.

@mbladel mentioned in HHH-17706:

I would suggest using implicit join paths (i.e. root.simpleEntity.id or root.get( "simpleEntity" ).get( "id" ) with Criteria) to take advantage of foreign-key optimization if really needed.

Is it something that could be done in Spring Data JPA?

scordio
reopened this on Feb 5, 2024
quaff

quaff commented on Feb 6, 2024

@quaff
quaff
on Feb 6, 2024
Contributor

Here is workaround:

interface BookRepository extends JpaRepository<Book, Long> {

    default List<Book> findAllByAuthorId(Long authorId) {
        Author author = new Author();
        author.setId(authorId);
        return findAllByAuthor(author);
    }

    List<Book> findAllByAuthor(Author author);

}
scordio
mentioned this on Feb 6, 2024
christophstrobl

christophstrobl commented on Feb 6, 2024

@christophstrobl
christophstrobl
on Feb 6, 2024
Member

Thank you both @scordio & @quaff - we'll see if there's anything we can do on our side.

christophstrobl
self-assigned this
on Feb 6, 2024

25 remaining items

Loading
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

type: enhancementA general enhancement

Type

No type

Projects

No projects

Milestone

No milestone

Relationships

None yet

    Development

    Participants

    @quaff@mp911de@christophstrobl@bukajsytlos@spring-projects-issues

    Issue actions
      Regression for method-based query where predicate compares primary key of `@ManyToOne` relationship · Issue #3349 · spring-projects/spring-data-jpa