Hibernate criteria DB2 composite keys in IN clause

Question

Hibernate criteria, using DB2 dialect, generates the following sql with composite keys in the IN clause, but DB2 answers that the query is incorrect:

select * from tableA where (x, y) IN ( ( 'x1', y1) )

but, DB2 throws this:

SQL0104N An unexpected token "," was found following ", y) in ( ('x1'". Expected tokens may include: "+". SQLSTATE=42601

Answer 1

Correct syntax would be

select * from tableA where (x, y) IN (VALUES ( 'x1', y1) )

Note you specify the VALUES clause only once, so a multiple rows results could be obtained with

select * from tableA where (x, y) IN (VALUES ( 'x1', y1), ( 'x2', y2) )

Don't know how to make Hibernate do it, however.