# Different query generated for bidirectional associacion between v6.5.0 and v6.5.1 (and v6.6.x)

**URL:** <https://discourse.hibernate.org/t/different-query-generated-for-bidirectional-associacion-between-v6-5-0-and-v6-5-1-and-v6-6-x/10765>\
**Category:** Hibernate ORM\
**Created:** [December 10, 2024, 8:49am UTC](https://discourse.hibernate.org/t/different-query-generated-for-bidirectional-associacion-between-v6-5-0-and-v6-5-1-and-v6-6-x/10765 "2024-12-10T08:49:51Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![kamil-pociot](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/kamil-pociot/32/3676_2.png) [@kamil-pociot](https://discourse.hibernate.org/u/kamil-pociot)\
**Post date:** [December 10, 2024, 8:49am UTC](https://discourse.hibernate.org/t/different-query-generated-for-bidirectional-associacion-between-v6-5-0-and-v6-5-1-and-v6-6-x/10765/1 "2024-12-10T08:49:51Z")

</div>

Hello,  
First of all, great tool and I really appreciate your work! I noticed that for my particular use case there is different SQL query generated for the same HQL query in Hibernate versions 6.5.0 and 6.5.1. I created a test that reproduces this issues and I’ll try to explain why this is problem for me.  
I have 2 entities that have bi-directional associacion and a query that fetch joins them and filter by some values.

```sql
SELECT p from Parent p LEFT JOIN FETCH p.child c WHERE (c.externalId = :providedId or c.id = :providedId) and p.someFilter = :filterValue

```

In v6.5.0 this query would result following SQL query:

```sql
    select
        p1_0.id,
        c1_0.id,
        c1_0.external_id,
        p1_0.some_filter 
    from
        parents p1_0 
    left join
        children c1_0 
            on c1_0.id=p1_0.child_id 
    where
        (
            c1_0.external_id=? 
            or c1_0.id=?
        ) 
        and p1_0.some_filter=?

```

This query uses indexes correctly for OR condition as indicated by explain analyze query:

```auto
Nested Loop (cost=8.34..37.26 rows=1 width=104) (actual time=3.158..3.162 rows=1 loops=1)
  Join Filter: ((p1_0.child_id)::text = (c1_0.id)::text)
  -> Bitmap Heap Scan on children c1_0 (cost=8.34..16.83 rows=5 width=64) (actual time=3.144..3.145 rows=1 loops=1)
        Recheck Cond: (((external_id)::text = '1'::text) OR ((id)::text = '1'::text))
        Heap Blocks: exact=1
        -> BitmapOr (cost=8.34..8.34 rows=5 width=0) (actual time=3.138..3.139 rows=0 loops=1)
              -> Bitmap Index Scan on external_id_idx (cost=0.00..4.18 rows=4 width=0) (actual time=1.959..1.959 rows=1 loops=1)
                    Index Cond: ((external_id)::text = '1'::text)
              -> Bitmap Index Scan on children_pkey (cost=0.00..4.16 rows=1 width=0) (actual time=1.178..1.178 rows=1 loops=1)
                    Index Cond: ((id)::text = '1'::text)
  -> Materialize (cost=0.00..20.14 rows=4 width=72) (actual time=0.011..0.012 rows=1 loops=1)
        -> Seq Scan on parents p1_0 (cost=0.00..20.12 rows=4 width=72) (actual time=0.008..0.008 rows=1 loops=1)
              Filter: ((some_filter)::text = 'some-value'::text)

```

The same HQL query executed with Hibernate 6.5.1 results with following SQL query:

```sql
select
        p1_0.id,
        c1_0.id,
        c1_0.external_id,
        p1_0.some_filter 
    from
        parents p1_0 
    left join
        children c1_0 
            on c1_0.id=p1_0.child_id 
    where
        (
            c1_0.external_id=? 
            or p1_0.child_id=?
        ) 
        and p1_0.some_filter=?

```

The change is in OR condition - `c1_0.id=?` became `p1_0.child_id=?` which resulted in following query plan:

```auto
Hash Right Join (cost=20.18..42.34 rows=1 width=104) (actual time=0.020..0.024 rows=1 loops=1)
  Hash Cond: ((c1_0.id)::text = (p1_0.child_id)::text)
  Filter: (((c1_0.external_id)::text = '1'::text) OR ((p1_0.child_id)::text = '1'::text))
  -> Seq Scan on children c1_0 (cost=0.00..18.80 rows=880 width=64) (actual time=0.002..0.003 rows=1 loops=1)
  -> Hash (cost=20.12..20.12 rows=4 width=72) (actual time=0.011..0.011 rows=1 loops=1)
        Buckets: 1024 Batches: 1 Memory Usage: 9kB
        -> Seq Scan on parents p1_0 (cost=0.00..20.12 rows=4 width=72) (actual time=0.008..0.008 rows=1 loops=1)
              Filter: ((some_filter)::text = 'some-value'::text)

```

Where index scan on `children` table turned to sequence scan.

Provided example is rather trivial but in my production use case with multiple joins and millions of rows this becomes not very performant.

I have prepared 2 PRs that compare which table was used for the same query:  
6.5.0 - [Bi-directional query reproducer v.6.5.0 by kamil-pociot · Pull Request #455 · hibernate/hibernate-test-case-templates · GitHub](https://github.com/hibernate/hibernate-test-case-templates/pull/455)  
6.5.1 - [Bi-directional query reproducer v.6.5.1 by kamil-pociot · Pull Request #456 · hibernate/hibernate-test-case-templates · GitHub](https://github.com/hibernate/hibernate-test-case-templates/pull/456)

The same behaviour is present in v 6.6.x

I’d appreciate a feedback if this is intended behaviour.

Best,  
Kamil

---

<div class="post-metadata">

**Author:** ![mbladel](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/mbladel/32/2128_2.png) [@mbladel](https://discourse.hibernate.org/u/mbladel)\
**Post date:** [December 11, 2024, 9:14am UTC](https://discourse.hibernate.org/t/different-query-generated-for-bidirectional-associacion-between-v6-5-0-and-v6-5-1-and-v6-6-x/10765/2 "2024-12-11T09:14:08Z")

</div>

Hibernate will try to render the foreign-key column instead of the target table’s identifier when possible to handle queries where the join doesn’t need to be rendered. I understand in your case this is inconvenient, as the query interpretation is less efficient, though the same argument could be easily made for other queries where the target table’s mapping is used.

Could you please try using this syntax instead:

```sql
... WHERE (c.externalId = :providedId or id(c) = :providedId) 

```

And let us know if that renders the children table’s column instead?

---

<div class="post-metadata">

**Author:** ![kamil-pociot](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/kamil-pociot/32/3676_2.png) [@kamil-pociot](https://discourse.hibernate.org/u/kamil-pociot)\
**Post date:** [December 11, 2024, 12:06pm UTC](https://discourse.hibernate.org/t/different-query-generated-for-bidirectional-associacion-between-v6-5-0-and-v6-5-1-and-v6-6-x/10765/3 "2024-12-11T12:06:19Z")

</div>

Unfortunately it still renders parent table column

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [December 12, 2024, 1:16pm UTC](https://discourse.hibernate.org/t/different-query-generated-for-bidirectional-associacion-between-v6-5-0-and-v6-5-1-and-v6-6-x/10765/4 "2024-12-12T13:16:17Z")

</div>

I think this might be a bug, I experimented with a fix on this [PR](https://github.com/hibernate/hibernate-orm/pull/9410).

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [December 12, 2024, 2:12pm UTC](https://discourse.hibernate.org/t/different-query-generated-for-bidirectional-associacion-between-v6-5-0-and-v6-5-1-and-v6-6-x/10765/5 "2024-12-12T14:12:43Z")

</div>

I created [HHH-18932](https://hibernate.atlassian.net/browse/HHH-18932) for this issue.

---

<div class="post-metadata">

**Author:** ![kamil-pociot](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/kamil-pociot/32/3676_2.png) [@kamil-pociot](https://discourse.hibernate.org/u/kamil-pociot)\
**Post date:** [December 13, 2024, 7:54am UTC](https://discourse.hibernate.org/t/different-query-generated-for-bidirectional-associacion-between-v6-5-0-and-v6-5-1-and-v6-6-x/10765/6 "2024-12-13T07:54:11Z")

</div>

Great news, thanks a lot!

---

<div class="post-metadata">

**Author:** ![kamil-pociot](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/kamil-pociot/32/3676_2.png) [@kamil-pociot](https://discourse.hibernate.org/u/kamil-pociot)\
**Post date:** [December 13, 2024, 8:31am UTC](https://discourse.hibernate.org/t/different-query-generated-for-bidirectional-associacion-between-v6-5-0-and-v6-5-1-and-v6-6-x/10765/7 "2024-12-13T08:31:58Z")

</div>

Are you planning to backport this fix to 6.6.x?

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [December 13, 2024, 9:38am UTC](https://discourse.hibernate.org/t/different-query-generated-for-bidirectional-associacion-between-v6-5-0-and-v6-5-1-and-v6-6-x/10765/8 "2024-12-13T09:38:03Z")

</div>

I am considering to backport it, yes, but can’t promise anything yet.

---

<div class="post-metadata">

**Author:** ![kamil-pociot](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/kamil-pociot/32/3676_2.png) [@kamil-pociot](https://discourse.hibernate.org/u/kamil-pociot)\
**Post date:** [December 13, 2024, 9:50am UTC](https://discourse.hibernate.org/t/different-query-generated-for-bidirectional-associacion-between-v6-5-0-and-v6-5-1-and-v6-6-x/10765/9 "2024-12-13T09:50:14Z")

</div>

Understood, thanks 🤞