Wrong column value bound for transitive self-referencing parent

[onacit]

Sorry for the vague title.

I have the following tables

[Hibernate] 
    create table h2_other (
        id bigint generated by default as identity,
        some_id bigint not null unique,
        primary key (id)
    )
[Hibernate] 
    create table h2_other_history (
        id bigint generated by default as identity,
        some_id bigint not null,
        primary key (id)
    )
[Hibernate] 
    create table h2_some (
        id bigint generated by default as identity,
        parent_id bigint,
        name varchar(255) not null,
        primary key (id)
    )

Java classes look like this. No reproducible classes/project required these are all I recreated for reproducing the issue.

import jakarta.persistence.Basic;
import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.JoinColumn;
import jakarta.persistence.OneToOne;
import jakarta.persistence.Table;
import jakarta.validation.Valid;
import jakarta.validation.constraints.NotNull;
import lombok.Getter;
import lombok.Setter;

import java.util.Optional;

@Entity
@Table(name = "h2_other")
@Setter
@Getter
public class H2Other {

    public H2Some getSome() {
        return some;
    }

    public void setSome(H2Some some) {
        this.some = some;
        setSomeId(
                Optional.ofNullable(this.some).map(H2Some::getId).orElse(null)
        );
    }

    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Id
    @Column(name = "id", nullable = false, insertable = true, updatable = false)
    private Long id;

    @NotNull
    @Basic(optional = false)
    @Column(name = "some_id", nullable = false, insertable = true, updatable = false, unique = true)
    private Long someId;

    @Valid
    @NotNull
    @OneToOne(optional = false)
    @JoinColumn(name = "some_id", nullable = false, insertable = false, updatable = false, unique = true)
    private H2Some some;
}

import jakarta.persistence.Basic;
import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.JoinColumn;
import jakarta.persistence.ManyToOne;
import jakarta.persistence.Table;
import jakarta.validation.constraints.NotNull;
import lombok.Getter;
import lombok.Setter;

@Entity
@Table(name = "h2_some")
@Setter
@Getter
public class H2Some {

    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Id
    @Column(name = "id", nullable = false, insertable = true, updatable = false)
    private Long id;

    @NotNull
    @Basic(optional = false)
    @Column(name = "name", nullable = false, insertable = true, updatable = true)
    private String name = "whatever";

    @ManyToOne(optional = true)
    @JoinColumn(name = "parent_id", nullable = true, insertable = true, updatable = true)
    private H2Some parent;
}

import jakarta.persistence.Basic;
import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.FetchType;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.JoinColumn;
import jakarta.persistence.ManyToOne;
import jakarta.persistence.OneToOne;
import jakarta.persistence.Table;
import jakarta.validation.Valid;
import jakarta.validation.constraints.NotNull;
import lombok.Getter;
import lombok.Setter;
import org.hibernate.annotations.NotFound;
import org.hibernate.annotations.NotFoundAction;

import java.util.Optional;

@Entity
@Table(name = "h2_other_history")
@Setter
@Getter
public class H2OtherHistory {

    public H2Some getSome() {
        return some;
    }

    public void setSome(H2Some some) {
        this.some = some;
        setSomeId(
                Optional.ofNullable(this.some).map(H2Some::getId).orElse(null)
        );
    }

    public H2Other getOther() {
        return other;
    }

    public void setOther(H2Other other) {
        this.other = other;
        setSome(
                Optional.ofNullable(this.other).map(H2Other::getSome).orElse(null)
        );
    }

    // -----------------------------------------------------------------------------------------------------------------
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Id
    @Column(name = "id", nullable = false, insertable = true, updatable = false)
    private Long id;

    // -----------------------------------------------------------------------------------------------------------------
    @Valid
    @NotNull
    @Basic(optional = false)
    @Column(name = "some_id", nullable = false, insertable = true, updatable = false)
    private Long someId;

    @Valid
    @NotNull
    @OneToOne(optional = false)
    @JoinColumn(name = "some_id", nullable = false, insertable = false, updatable = false)
    private H2Some some;

    // -----------------------------------------------------------------------------------------------------------------
    @NotFound(action = NotFoundAction.IGNORE) // -> EAGER!!!
    @ManyToOne(optional = false,
               fetch = FetchType.LAZY // WON'T WORK!!!!
               )
    @JoinColumn(name = "some_id", referencedColumnName = "some_id", nullable = false, insertable = false,
                updatable = false)
    private H2Other other;
}

With the following repository interface,

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface H2OtherHistoryRepository extends JpaRepository<H2OtherHistory, Long> {

    Page<H2OtherHistory> findAllBySomeId(Long someId, Pageable pageable);

    Page<H2OtherHistory> findAllByOther(H2Other other, Pageable pageable);
}

I tried the following tests.

import org.junit.jupiter.api.Nested;
import org.junit.jupiter.api.RepeatedTest;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest;
import org.springframework.boot.test.autoconfigure.orm.jpa.TestEntityManager;
import org.springframework.data.domain.Pageable;

import static org.assertj.core.api.Assertions.assertThat;

@DataJpaTest
class H2OtherHistoryRepositoryDataJpaTest {

    @Nested
    class FindBySomeTest {

    }

    @Nested
    class FindAllByOtherTest {

        @RepeatedTest(10)
//        @Test
        void __() {
            // ---------------------------------------------------------------------------------------------------------
            final var some = testEntityManager.persist(new H2Some());


            // toggle!!!
            // !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
            some.setParent(testEntityManager.persist(new H2Some()));
            // !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

            // ---------------------------------------------------------------------------------------------------------
            final var other = new H2Other();
            other.setSome(some);
            testEntityManager.persist(other);
            // ---------------------------------------------------------------------------------------------------------
            final var otherHistory = new H2OtherHistory();
            otherHistory.setOther(other);
            testEntityManager.persist(otherHistory);
            // ---------------------------------------------------------------------------------------------------------
            final var found = otherHistoryRepository.findAllByOther(other, Pageable.unpaged());
            assertThat(found).contains(otherHistory);
        }
    }

    @Autowired
    private TestEntityManager testEntityManager;

    @Autowired
    private H2OtherHistoryRepository otherHistoryRepository;
}

Now it 100 % fails while 100 % passes without the,

            // !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
            some.setParent(testEntityManager.persist(new H2Some()));
            // !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

[mp911de]

Have you tried replacing otherHistoryRepository.findAllByOther(…) with a query to EntityManager.createQuery(…)? This issue looks like a pure Hibernate issue.