Criteria API: Root.in(Subquery) incorrectly uses foreign key column instead of primary key

Hibernate ORM
1

When using the Criteria API to perform an IN operation between a Root<Entity> and a Subquery<Entity>, Hibernate generates SQL that compares the foreign key column of the entity in the main query instead of its primary key column against the results of the subquery.

This occurs in a @OneToOne relationship where the entity being queried contains the foreign key**
Environment**

  • Hibernate Versions: 7.1.16.Final, 7.2.4.Final, 6.6.42.Final

  • Java: 17+

  • Database: H2 (In-memory), also confirmed in MariaDB, likely database-agnostic.

Domain Model

@MappedSuperclass
public abstract class BaseModel {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    // getter/setter
}

@Entity
@Table(name = "audits")
public class Audit extends BaseModel {
    private String name;
    
    @OneToOne(mappedBy = "audit")
    private Project project;
    // getter/setter
}

@Entity
@Table(name = "projects")
public class Project extends BaseModel {
    private String name;
    
    @OneToOne
    @JoinColumn(name = "audit_id")
    private Audit audit;
    // getter/setter
}

Steps to Reproduce

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Project> criteriaQuery = cb.createQuery(Project.class);
Root<Project> root = criteriaQuery.from(Project.class);

// Subquery returning Project entities
Subquery<Project> subquery = criteriaQuery.subquery(Project.class);
Root<Audit> subroot = subquery.from(Audit.class);
subquery.select(subroot.join("project"))
        .where(cb.equal(subroot.get("name"), "Audit 1"));

// Buggy predicate: root (Project) IN subquery (Project entities)
criteriaQuery.select(root)
             .where(root.in(subquery));

List<Project> results = em.createQuery(criteriaQuery).getResultList();

Expected Behavior (Generated SQL)

The WHERE clause should compare the primary key id of the projects table:

SELECT p1_0.id, ...
FROM projects p1_0 
WHERE p1_0.id IN (
    SELECT p2_0.id 
    FROM audits a1_0 
    JOIN projects p2_0 ON a1_0.id = p2_0.audit_id
    WHERE a1_0.name = 'Audit 1'
)

Actual Behavior (Generated SQL)

Hibernate incorrectly uses the audit_id foreign key column for the comparison:

SELECT p1_0.id, ...
FROM projects p1_0 
WHERE p1_0.audit_id IN (  -- <--- BUG: Should be p1_0.id
    SELECT p2_0.id 
    FROM audits a1_0 
    JOIN projects p2_0 ON a1_0.id = p2_0.audit_id
    WHERE a1_0.name = 'Audit 1'
)

Additional Notes

  • The subquery correctly returns Project IDs, but the main query tries to match them against audit_id.

  • If you explicitly compare IDs using root.get("id").in(subquery), the generated SQL is correct.

  • This Bug only occures when the outer CriteraQuery root is of the class containing the Foreign Key. If classes are swapped the generated SQL is correct.

    • See demonstrateCorrectInverseQuery method in src/main/java/com/example/BugDemo.java of my example project
    • This behavior is inconsistent as it depends on the direction of the foreign key, whereas entity equality should always be identity-based.

  • The bug occures regardless if the property is accessed via MetaModel attributes ( subroot.join(Audit_.project) ) or strings ( subroot.join("project") ). also it does not Matter if I use .get() or .join()

  • The issue was found by me but I used AI tools to generate the boilerplate code for the example Project including the README of that project.

Reproducer Project

A minimal reproducible example is available at: GitHub - robinjoerke/hibernate-subquery-bug (I’m also willing to JPAUnitTestCase but i was unsure about the format and where to put the code for my Classes)

2

i tried to submit the bug to the bug tracker at https://hibernate.atlassian.net/jira/software/c/projects/HHH/issues but the Create Issue button doesn’t seem to work on my end, so I’m posting it here.

3

I suppose you created your account with Google SSO? There is an ongoing thread about the UI being a hassle to understand.

There is a “Log In” Button in the Account-Popup (where it has links to Profile and Account Settings). After clicking it, you should be prompted to join Hibernate’s Jira.

Then you can create these issue. Please let me know if that works for you.

4

thanks. i was able to solve the login problem and create the issue https://hibernate.atlassian.net/browse/HHH-20179

1 Like