PostgreSQL: operator does not exist '!=-' exception when using JPA not equals operator '<>' with negative number
Description
The generated SQL for a JPA query containing the not equals ('<>') operator and a negative number will cause PostgreSQL to throw a PSQLException with the message “ERROR: operator does not exist: integer !=- integer”.
This issue appears to have been introduced in 6.0 when the generated SQL for JPA queries started using ‘!=’ instead of ‘<>’ as was used in prior versions of hibernate (5.6.15).
Example JPA Query: SELECT x FROM IntegerTextMapEntity x WHERE x.intValue <> -1`
Generated SQL Query (column names replaced with '*'): select * from IntegerTextMapEntity itme1_0 where itme1_0.intValue!=-1
This query works just fine when using an H2 database (such as in unit tests) and only appears to crop up when executed against a PostgreSQL DB (in my case version 15).
There are two ways I see that this could be fixed. Either revert to using the old not-equals operator ‘<>’ in the generated SQL, or add whitespace around the new not-equals operator ‘!=’ text to help the PostgreSQL parser do the right thing. See org.hibernate.query.sqm.ComparisonOperator.NOT_EQUAL#sqlText
Unit test project reproducing the issue attached as tarball. Be sure to configure the persistence.xml file to hit a PostgreSQL DB to reproduce. Test shown below as well:
The generated SQL for a JPA query containing the not equals ('<>') operator and a negative number will cause PostgreSQL to throw a PSQLException with the message “ERROR: operator does not exist: integer !=- integer”.
This issue appears to have been introduced in 6.0 when the generated SQL for JPA queries started using ‘!=’ instead of ‘<>’ as was used in prior versions of hibernate (5.6.15).
Example JPA Query:
SELECT x FROM IntegerTextMapEntity x WHERE x.intValue <> -1`Generated SQL Query (column names replaced with '*'):
select * from IntegerTextMapEntity itme1_0 where itme1_0.intValue!=-1This query works just fine when using an H2 database (such as in unit tests) and only appears to crop up when executed against a PostgreSQL DB (in my case version 15).
There are two ways I see that this could be fixed. Either revert to using the old not-equals operator ‘<>’ in the generated SQL, or add whitespace around the new not-equals operator ‘!=’ text to help the PostgreSQL parser do the right thing. See
org.hibernate.query.sqm.ComparisonOperator.NOT_EQUAL#sqlTextUnit test project reproducing the issue attached as tarball. Be sure to configure the persistence.xml file to hit a PostgreSQL DB to reproduce. Test shown below as well: