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

Hibernate ORM
1

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:

-- 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 *:

    -- Fails on Db2 for i with SQL0216
select a.id1, a.id2, a.some_column
from MYTABLEONE a
where (a.id1, a.id2) in (
    select *
    from (values (?, ?)) v(c0, c1)
)

    This fails with:

    com.ibm.as400.access.AS400JDBCSQLSyntaxErrorException:
[SQL0216] Number of values in comparison predicate list not valid.

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

    -- Also generated by Hibernate 6.6.38.Final, for slightly different scenarios
select u.id1, u.id2, u.some_other_column
from MYTABLETWO u
where (u.id1, u.id2) in (
    select v.c0, v.c1
    from (values (?, ?)) v(c0, c1)
)

    This fails with:

    com.ibm.as400.access.AS400JDBCSQLSyntaxErrorException:
[SQL0584] NULL, UNKNOWN, or parameter marker in VALUES not allowed.

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 *:

    where (a.id1, a.id2) in (
    select *
    from (values (?, ?)) v(c0, c1)
)
         ^^^

    → This leads to SQL0216 on Db2 for i.

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

    where (u.id1, u.id2) in (
    select v.c0, v.c1
    from (values (?, ?)) v(c0, c1)
                 ^   ^
)

    Db2 for i disallows untyped parameter markers in VALUES for this kind of row fullselect in an IN predicate, and throws SQL0584:

    [SQL0584] NULL, UNKNOWN, or parameter marker in VALUES not allowed.

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!

DB2iDialect generates invalid SQL when calling merge on an entity
2

This was already reported here: Jira

2 Likes
3

Can you please build this PR and tell us if it fixes the problem?

4

@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.

5

@beikov
error returned in version 7.2.0.Final

6

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-20040
https://hibernate.atlassian.net/browse/HHH-20041

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

1 Like