Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

Regression on comparing association primary key

Description

Given entities:

@Entity class Book { @Id private Long id; @ManyToOne private Author author; } @Entity class Author { @Id private Long id; }

Test code:

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.join("author", JoinType.LEFT).get("id"), 1L)); entityManager.createQuery(cq).getResultList();

 

Generated sql since 6.4.1 will contains unnecessary join:

select b1_0.id,b1_0.author_id from book b1_0 left join author a1_0 on a1_0.id=b1_0.author_id where a1_0.id=?

Generated sql by 6.2.x and 6.3.x and 6.4.0 is expected:

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

 

See https://github.com/spring-projects/spring-data-jpa/issues/3349

Activity

Show:

Yanming Zhou (quaff@github)February 7, 2024 at 9:41 AM

Total agreed! I thought it’s introduced by accident.

Gavin KingFebruary 5, 2024 at 2:12 PM

Just to add my 2c here: I think this is a perfectly good resolution. As pointed out below, we already optimize away the join when you use an implicit join.

But if you explicitly ask for a join by calling the join() method, it would be a bit weird to optimize it away.

Marco BelladelliFebruary 5, 2024 at 1:22 PM

Hi , thanks for taking the time to create this issue and providing a PR for it.

I would like to clarify our intentions regarding the change you say “caused” this regression: excessive joined path optimizations were leading to all kinds of problems with outer joins (e.g. the two issues fixed by that change) and the complexity of the checks needed to ensure we could perform those optimizations on all supported databases was getting out of hand. For this reasons, we opted to avoid optimizing explicitly joined path (reporting part of the conversation on the original pr):

Explicitly joined paths will now always be dereferenced using the target table's mapping thus triggering join creation in the query.

[…]

since the joins are explicit in HQL it makes sense to always create them in SQL

Now, I had a quick look at your proposed change and I don’t think the checks will be enough to prevent the kind of issues I was talking about earlier to happen. I’m also skeptical about the actual performance impact of creating the join since primary keys should be indexed and the query results are the same.

 

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.

Yanming Zhou (quaff@github)February 4, 2024 at 4:14 AM

Rejected

Details

Assignee

Reporter

Worked in

Components

Affects versions

Priority

Created February 4, 2024 at 3:11 AM
Updated February 7, 2024 at 9:41 AM
Resolved February 5, 2024 at 1:27 PM

Flag notifications