2021-10-23

Hibernate fail with Postgresql : Row comparison operators must be associated with btree operator families.

Explanation of the problem

Recently I had the following issue with PostgreSQL ( 10+) :

SQL exception            : Exception while executing meta query: ERROR: could not determine interpretation of row  operator ~
  Hint: Row comparison operators must be associated with btree operator families.
  Position: 1119

Basically, the origin of the problem is linked with a JQPL query :

String qry = "from jpmi.model.compte.Pension p  p.numeroDePension IN :ndp";

numeroDePension field is an @Embeddable field so we are comparing a row with a multi-column operator.

Hibernate is generating the following code ( 5+)

 select ... from d_pension pension0_ where (pension0_.cle, pension0_.compte, pension0_.millesime, pension0_.sequence) like (?, ?, ?, ?)

We see the multi-column that is not supported.

Solutions

So you have the following solutions :

  • Replacing the LIKE operator by = or IN in the JPQL query.
  • Create a custom operator in PostgreSQL
  • Replace the @Embeddable with a field by field comparison

The helped to find the solution https://github.com/jOOQ/jOOQ/issues/8072.

Next to read  Code generation and data generator : the best use cases

Sylvain Leroy

Senior Software Quality Manager and Solution Architect in Switzerland, I have previously created my own company, Tocea, in Software Quality Assurance. Now I am offering my knowledge and services in a small IT Consulting company : Byoskill and a website www.byoskill.com Currently living in Lausanne (CH)

View all posts by Sylvain Leroy →