I want to prevent the duplicate SQL queries executed by hibernate, as my underlying relation is a complex view, (although maybe postgresql might cache the result, but still I would vastly prefer if duplicate queries were not issued).

Here is a minimal example, with two entities XX and YY in which YY refers to XX via its single column, its primary key. The table xx has 2 rows with keys 0 and 1. The table yy has no rows.

The reverse mapping within XX does not affect the behavior, and it can be removed and duplicate SQL is still generated.

Here is the code:

import jakarta.persistence.*

@Entity
data class XX (@Id val k: Int, @OneToOne(mappedBy = "xx", fetch = FetchType.LAZY) val yy: YY)

@Entity
class YY (
    @Id @OneToOne(fetch = FetchType.LAZY) val xx: XX,
)

fun main() {
    val em = Persistence.createEntityManagerFactory("mine").createEntityManager()
    val session = em.unwrap(org.hibernate.Session::class.java)
    session.beginTransaction()
    println("starting")
    println("==========")

    println(); println("---------- fetch existing xx 0")
    val xx = em.find(XX::class.java, 0)

    println(); println("---------- fetch non-existing yy 0 via existing xx")
    val yy = xx.yy
    println("yy is $yy")

    println(); println("---------- fetch non-existing yy 0 directly")
    em.find(YY::class.java, 0)

    println(); println(".......... fetch non-existing yy 0 directly again")
    em.find(YY::class.java, 0)

    println(); println(".......... fetch non-existing yy 1 (xx 1 exists) directly")
    em.find(YY::class.java, 1)

    println(); println(".......... fetch non-existing yy 1 (xx 1 exists) directly again")
    em.find(YY::class.java, 1)

    println(); println(".......... fetch non-existing yy 2 (no xx 2) directly")
    em.find(YY::class.java, 2)

    println(); println(".......... fetch non-existing yy 2 (no xx 2) directly again")
    em.find(YY::class.java, 2)

    println(); println("==========")
    println("ended")
    session.transaction.commit()
    em.close()
}

and the resulting output is:

starting
==========

---------- fetch existing xx 0
Hibernate: select x1_0.k from wordchase_host.XX x1_0 where x1_0.k=?

---------- fetch non-existing yy 0 via existing xx
yy is null

---------- fetch non-existing yy 0 directly
Hibernate: select y1_0.xx_k from wordchase_host.YY y1_0 where y1_0.xx_k=?
Hibernate: select y1_0.xx_k from wordchase_host.YY y1_0 where y1_0.xx_k=?

.......... fetch non-existing yy 0 directly again
Hibernate: select y1_0.xx_k from wordchase_host.YY y1_0 where y1_0.xx_k=?
Hibernate: select y1_0.xx_k from wordchase_host.YY y1_0 where y1_0.xx_k=?

.......... fetch non-existing yy 1 (xx 1 exists) directly
Hibernate: select x1_0.k from wordchase_host.XX x1_0 where x1_0.k=?
Hibernate: select y1_0.xx_k from wordchase_host.YY y1_0 where y1_0.xx_k=?
Hibernate: select y1_0.xx_k from wordchase_host.YY y1_0 where y1_0.xx_k=?

.......... fetch non-existing yy 1 (xx 1 exists) directly again
Hibernate: select y1_0.xx_k from wordchase_host.YY y1_0 where y1_0.xx_k=?
Hibernate: select y1_0.xx_k from wordchase_host.YY y1_0 where y1_0.xx_k=?

.......... fetch non-existing yy 2 (no xx 2) directly
Hibernate: select x1_0.k from wordchase_host.XX x1_0 where x1_0.k=?
Hibernate: select y1_0.xx_k from wordchase_host.YY y1_0 where y1_0.xx_k=?
Hibernate: select y1_0.xx_k from wordchase_host.YY y1_0 where y1_0.xx_k=?

.......... fetch non-existing yy 2 (no xx 2) directly again
Hibernate: select x1_0.k from wordchase_host.XX x1_0 where x1_0.k=?
Hibernate: select y1_0.xx_k from wordchase_host.YY y1_0 where y1_0.xx_k=?
Hibernate: select y1_0.xx_k from wordchase_host.YY y1_0 where y1_0.xx_k=?
==========
ended

The table yy is empty, and the table xx has two rows with keys 0 and 1.

When there is a corresponding row in yy then it is only fetched once, otherwise finding a non-existent xx results in 3 queries: 1 to yy and 2 to xx

I am using hibernate version 6.5.2.Final

Any insight into this behavior or how to avoid it will be greatly appreciated.