Malformed Query for DB2 i with 6.2.1 release

Hibernate ORM
1

I connect to both MySQL and an IBM i and recently upgraded to org.hibernate.orm:hibernate-core:6.1.5.Final. Queries that were working fine in the earlier release started generating malformed SQL statements resulting in a plethora of …

“Caused by: com.ibm.as400.access.AS400JDBCSQLSyntaxErrorException: [SQL0199] Keyword BY not expected. Valid tokens: FOR USE SKIP WAIT WITH FETCH LIMIT ORDER UNION EXCEPT OFFSET.”

… errors.

Since I’m on a deadline I’m going to revert all of these statements back to createNativeQuery() statements from createQuery() statements, but I was wondering if anyone knows about this issue and can share a quick solution with me.

I did switch to the DB2iDialect.

Thanks.

2

Just to expand on the issue a bit. The following simple JPA statement …

            String s = "select ho from HostOrder ho where ho.orderNumber = :orderNumber ";
            Query q = em.createQuery(s, HostOrder.class);
            q.setParameter("orderNumber", orderNumber);

results in the following generated SQL …

select * from (select h1_0.CEOPNB c0,h1_0.CEACC3 c1,h1_0.CEFUQT c2,h1_0.CETGQT c3,
h1_0.CEBUS c4,h1_0.CECHD c5,h1_0.CECHT c6,h1_0.CECHU c7,h1_0.CECRD c8,h1_0.CECRT c9,
h1_0.CECRU c10,h1_0.CEMWNB c11,h1_0.CERSNB c12,h1_0.CENVDT c13,h1_0.CERXCD c14,h1_0.CEH1CD c15,
h1_0.CEOQNB c16,h1_0.CEKSCD c17,h1_0.CEW1PR c18,h1_0.CENSCD c19,h1_0.CENANB c20,
h1_0.CEA0VN c21,h1_0.CEDMCD c22,h1_0.CEA1VN c23,h1_0.CEIAST c24,h1_0.CEZMNB c25,
h1_0.CEZENB c26,h1_0.CEIBST c27,h1_0.CEKRCD c28,h1_0.CEGYST c29,h1_0.CEQHDT c30,
h1_0.CEZYNB c31,h1_0.CEAIC1 c32,h1_0.CEIDCD c33,h1_0.CEDYVA c34,h1_0.CEAST c35,
h1_0.CEFSST c36,h1_0.CETSNB c37,h1_0.CEZDNB c38,h1_0.CEPSPR c39,h1_0.CEORNB c40,
h1_0.CEORHN c41,h1_0.CEOBDT c42,h1_0.CEAETM c43,
row_number() over() rn 
from MD0026AA0D.ORHP h1_0 where h1_0.CEOPNB=?) r_0_ where  order by r_0_.rn

Of course, the “where order by” isn’t valid SQL on any platform I’m aware of, but it certainly isn’t on the DB2 platform.

3

Hello, your issue should have been addressed by [HHH-15890] - Hibernate JIRA and will be fixed in the next versions of Hibernate (6.2.0 and 6.1.7).

Please let us know if the problem persists after the release.