@Where not consistently applied across association boundaries

Description

I am experiencing strange behavior in the process of upgrading to Hibernate 6.x. It seems @Where annotation on entity class is ignored when using with entity graph or fetch join. Despite the same entity classes and queries, wrong SQL is generated after upgrading to Hibernate 6.x, which is not what I expected.

I have created a sample project to test the issue and show the situation in detail. Here is GitHub repository for the sample project:

Sample project (with Hibernate 5.6.14.Final): Refer to the repository below
Sample project (with Hibernate 6.1.6.Final): Refer to the repository below

I have explained the details on discourse topic - https://discourse.hibernate.org/t/where-clause-ignored-after-upgrading-to-hibernate-6/7120

I also forked hibernate-test-case-templates repository and added test cases: https://github.com/dev-jaehoonlee/hibernate-test-case-templates/tree/HHH-16019-2/orm

orm/hibernate-orm-5/src/test/java/org/hibernate/bugs/JPAUnitTestCase.java and orm/hibernate-orm-6/src/test/java/org/hibernate/bugs/JPAUnitTestCase.java have the same test cases, but tests are passed with Hibernate 5.6 and failed with Hibernate 6.1

Linked issues

depends on

HHH-16198

Fix splitting of SqlAstCreationState

relates to

HHH-16265

Remove `@Where#applyInToManyFetch`

Activity

Show:

Steve Ebersole February 24, 2023 at 1:25 PM
Edited

As you can see, the unique constraint has been added to the user_id column.
Perhaps this is an intentional change in behavior in version 6.2.0?

We did in fact change the behavior such that a unique constraint is always added for a 1-1 mapping. That was actually a bug in previous releases. But as as you can see from the link, that was actually changed in 6.0. Perhaps an additional condition was recognized later and addressed. But regardless, hopefully you can understand that a 1-1 mapping would more-than-correctly have unique constraints defined.

The is_active column on the UserDetail entity is a flag used for soft-delete purposes, so there can be multiple rows with the same user_id in the database.

So long as there is not a unique constraint on user_id that is true. See above comment.

Jaehoon Lee February 24, 2023 at 8:20 AM

@Steve Ebersole

Thank you for your response and I apologize for the late reply. I've been on vacation and haven't checked in.

"I assume you run the tests against a pre-existing schema"

I ran the tests using the H2 memory database set up in hibernate-test-case-templates.

"If Hibernate exports the schema, it will (properly) create a unique constraint for the @OneToOne used on UserDetail#user"

The is_active column on the UserDetail entity is a flag used for soft-delete purposes, so there can be multiple rows with the same user_id in the database.

In versions 5.6.14.Final, 5.6.15.Final, 6.1.6.Final, and 6.1.7.Final, schema generation does not seem to create a unique constraint on that field.

The following is the DDL for the user_details table generated by version 6.1.7.Final:

create table user_details (
    detail_id bigint generated by default as identity,
    is_active boolean,
    city varchar(255),
    user_id bigint,
    primary key (detail_id)
)

However, I found that the 6.2.0.CR1 and 6.2.0.CR2 versions create their own constraints on those fields during schema generation.

The following is the DDL for the user_details table generated by the 6.2.0.CR2 version:

create table user_details (
    is_active boolean,
    detail_id bigint generated by default as identity,
    user_id bigint unique,
    city varchar(255),
    primary key (detail_id)
)

As you can see, the unique constraint has been added to the user_id column.
Perhaps this is an intentional change in behavior in version 6.2.0?

Anyway, in 6.2.x and later versions, the unique constraint created as above causes a "Unique index or primary key violation" error when inserting sample data for testing, but that doesn't seem to be very relevant to this issue, so let's get back to the original issue.

"There is a lot going on in your tests, is that really the minimally reproducible domain model?"

I apologize that my tests are verbose and unclear. I have simplified the test code as much as I could.

Please check it out the following repository (the HHH-16019-2 branch):

https://github.com/dev-jaehoonlee/hibernate-test-case-templates/tree/HHH-16019-2/orm
- Hibernate 5 test case: /orm/hibernate-orm-5/src/test/java/org/hibernate/bugs/JPAUnitTestCase.java
- Hibernate 6 test case: /orm/hibernate-orm-6/src/test/java/org/hibernate/bugs/JPAUnitTestCase.java

In versions 5.6.14.Final, 5.6.15.Final, the following query is generated on find(), and the test passes.

select
    user0_.user_id as user_id1_1_0_,
    user0_.user_name as user_nam2_1_0_,
    userdetail1_.detail_id as detail_i1_0_1_,
    userdetail1_.is_active as is_active2_0_1_,
    userdetail1_.city as city3_0_1_,
    userdetail1_.user_id as user_id4_0_1_ 
from
    users user0_ 
left outer join
    user_details userdetail1_ 
        on user0_.user_id=userdetail1_.user_id 
        and (
            userdetail1_.is_active = true
        )  
where
    user0_.user_id=?

On the other hand, in versions 6.1.6.Final, 6.1.7.Final, the following query is generated on find().

select
    u1_0.user_id,
    d1_0.detail_id,
    d1_0.is_active,
    d1_0.city,
    d1_0.user_id,
    u1_0.user_name 
from
    users u1_0 
left join
    user_details d1_0 
        on u1_0.user_id=d1_0.user_id 
where
    u1_0.user_id=?

Despite the @Where(clause = "is_active = true") condition present in the UserDetail entity, the and ( userdetail1_.is_active = true ) statement is omitted from the on clause of the join.

Therefore, two rows are returned, which results in a "Duplicate row was found" error and the test fails.

What I expect is for the condition d1_0.is_active = true to exist in the on clause of the join, as it did in versions 5.6.14.Final, 5.6.15.Final.

Again, I apologize for the late response and thank you.

Steve Ebersole February 21, 2023 at 3:05 PM

Bueller…?

Steve Ebersole February 16, 2023 at 1:07 AM

@Jaehoon Lee I assume you run the tests against a pre-existing schema? If Hibernate exports the schema, it will (properly) create a unique constraint for the @OneToOne used on UserDetail#user. Many of the tests fail because they try to create multiple UserDetail rows for the same user.

Steve Ebersole February 15, 2023 at 9:03 PM

This is going to take some time to transfer your tests to the ORM repo.

There is a lot going in your tests. Is that really the minimally reproducible domain model?

Fixed

Details

Assignee

Steve Ebersole

Reporter

Jaehoon Lee

Priority

Major

Created January 11, 2023 at 3:32 AM

Updated March 17, 2023 at 8:19 PM

Resolved March 7, 2023 at 6:44 PM

Configure