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

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;

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;

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=?

@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.

Thanks @quaff for helping pinpoint the root cause!

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

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?

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);

}

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

Thank you, @christophstrobl! Unless someone is working on it soon, I'm also happy to take a look and come up with a proposal.

thank you @scordio - PRs are always welcome :)

@scordio Could you try PR #3374 ?