# Db2 for i regression in 6.6.30+ (HHH-19768 / row-value IN) – SQL0216 and SQL0584 on basic loads

**URL:** <https://discourse.hibernate.org/t/db2-for-i-regression-in-6-6-30-hhh-19768-row-value-in-sql0216-and-sql0584-on-basic-loads/12016>\
**Category:** Hibernate ORM\
**Created:** [December 5, 2025, 1:35pm UTC](https://discourse.hibernate.org/t/db2-for-i-regression-in-6-6-30-hhh-19768-row-value-in-sql0216-and-sql0584-on-basic-loads/12016 "2025-12-05T13:35:45Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Viserius](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/viserius/32/4211_2.png) [@Viserius](https://discourse.hibernate.org/u/Viserius)\
**Post date:** [December 5, 2025, 1:35pm UTC](https://discourse.hibernate.org/t/db2-for-i-regression-in-6-6-30-hhh-19768-row-value-in-sql0216-and-sql0584-on-basic-loads/12016/1 "2025-12-05T13:35:45Z")

</div>

Hi all,

after upgrading Hibernate ORM from **6.6.29.Final** to **6.6.30.Final** , we’re seeing a regression on **Db2 for i** (IBM i / AS/400, using the jt400 JDBC driver) related to the change around `supportsRowValueConstructorSyntaxInInSubQuery` (HHH-19768 / related work).

From what I can see, the change that makes the Db2 dialect report support for `supportsRowValueConstructorSyntaxInInSubQuery()` causes Hibernate to generate **row-value `IN` predicates using `VALUES`**. That’s fine for many databases, but on Db2 for i this leads to **invalid SQL** and runtime failures, even for simple JPA operations.

* * *

### Environment (simplified)

- Hibernate ORM **6.6.30.Final**  
(same codebase works fine on **6.6.29.Final** )

- Db2 for i (IBM i / AS/400)

- jt400 JDBC driver

- Standard JPA usage, no custom SQL / HQL for the cases below

* * *

### What changed in the generated SQL

We have a simple entity with a composite key `(id1, id2)` and another entity that maps a one-to-many association using these columns.

On 6.6.29.Final, a basic load by id would generate SQL like:

```auto
-- Hibernate 6.6.29.Final (OK)
select a.id1, a.id2, a.some_column
from ANSWER a
where a.id1 = ? and a.id2 = ?

```

After upgrading to 6.6.33.Final, Hibernate now generates row-value `IN` predicates based on `VALUES`. We have seen two main shapes of SQL (simplified):

1. **Shape A – row-value IN with SELECT \*** :

2. **Shape B – row-value IN with explicit columns but VALUES parameter markers:**

Both examples are generated by Hibernate for **simple, standard JPA operations** :

- Loading an entity with an embedded id (`findById` / `EntityManager.find`).

- Loading collections / associations that rely on the composite key.

- Repository calls such as `findById` on an entity whose primary key is an embeddable.

No custom HQL or native queries are involved – these are plain CRUD operations.

* * *

### Why this is a problem on Db2 for i

From the Db2 for i documentation (row fullselect / subquery rules), when using row-value expressions and row fullselects in `IN` predicates:

- The select list of a row fullselect **must not contain `SELECT *`; explicit columns must be specified**.

- The row expression for an `IN` / `NOT IN` predicate **cannot contain an untyped parameter marker** , among other restrictions.

The generated SQL violates these rules in two ways:

1. In **Shape A** , the row fullselect uses `SELECT *`:

2. In **Shape B** , the select list is explicit, but the **`VALUES` row uses parameter markers** :

In other words:

- Db2 for i **does not fully support** the specific pattern Hibernate now generates for row-value `IN` with subqueries.

- The dialect’s claim that Db2 supports `supportsRowValueConstructorSyntaxInInSubQuery()` is **too optimistic** for Db2 for i, at least in combination with `VALUES (?, ?)`.

* * *

Thanks a lot for your time and for all the work on Hibernate!

---

<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 5, 2025, 4:14pm UTC](https://discourse.hibernate.org/t/db2-for-i-regression-in-6-6-30-hhh-19768-row-value-in-sql0216-and-sql0584-on-basic-loads/12016/2 "2025-12-05T16:14:20Z")

</div>

This was already reported here: [Jira](https://hibernate.atlassian.net/browse/HHH-19929)

---

<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, 2025, 3:18pm UTC](https://discourse.hibernate.org/t/db2-for-i-regression-in-6-6-30-hhh-19768-row-value-in-sql0216-and-sql0584-on-basic-loads/12016/3 "2025-12-12T15:18:53Z")

</div>

Can you please build [this PR](https://github.com/hibernate/hibernate-orm/pull/11427) and tell us if it fixes the problem?

---

<div class="post-metadata">

**Author:** ![Viserius](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/viserius/32/4211_2.png) [@Viserius](https://discourse.hibernate.org/u/Viserius)\
**Post date:** [December 13, 2025, 12:39pm UTC](https://discourse.hibernate.org/t/db2-for-i-regression-in-6-6-30-hhh-19768-row-value-in-sql0216-and-sql0584-on-basic-loads/12016/4 "2025-12-13T12:39:26Z")

</div>

@beikov Thank you for the quick work. Currently, I am on holiday leave, but will ask a colleague to try it out and report back.

---

<div class="post-metadata">

**Author:** ![kozla13](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/kozla13/32/4248_2.png) [@kozla13](https://discourse.hibernate.org/u/kozla13)\
**Post date:** [January 14, 2026, 10:18am UTC](https://discourse.hibernate.org/t/db2-for-i-regression-in-6-6-30-hhh-19768-row-value-in-sql0216-and-sql0584-on-basic-loads/12016/5 "2026-01-14T10:18:00Z")

</div>

@beikov  
error returned in version 7.2.0.Final

---

<div class="post-metadata">

**Author:** ![yrodiere](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/yrodiere/32/1760_2.png) [@yrodiere](https://discourse.hibernate.org/u/yrodiere)\
**Post date:** [January 14, 2026, 10:28am UTC](https://discourse.hibernate.org/t/db2-for-i-regression-in-6-6-30-hhh-19768-row-value-in-sql0216-and-sql0584-on-basic-loads/12016/6 "2026-01-14T10:28:32Z")

</div>

The relevant issues are marked as fixed in 7.2.1, not 7.2.0:

[https://hibernate.atlassian.net/browse/HHH-19929](https://hibernate.atlassian.net/browse/HHH-19929)  
[https://hibernate.atlassian.net/browse/HHH-20040](https://hibernate.atlassian.net/browse/HHH-20040)  
[https://hibernate.atlassian.net/browse/HHH-20041](https://hibernate.atlassian.net/browse/HHH-20041)

So you should get the fixes in 7.2.1, due to be released later this week.