When using the SQL function values with a single value list, a JPA query such as 'from Employee e where id in (values(?1), (?2))' fails when run against PostgreSQL:
javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1692) at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1602) at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:492) ... Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:106) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:79) at org.hibernate.loader.Loader.getResultSet(Loader.java:2115) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1898) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1874) at org.hibernate.loader.Loader.doQuery(Loader.java:919) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:336) at org.hibernate.loader.Loader.doList(Loader.java:2610) at org.hibernate.loader.Loader.doList(Loader.java:2593) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2422) at org.hibernate.loader.Loader.list(Loader.java:2417) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:501) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:371) at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216) at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1339) at org.hibernate.internal.QueryImpl.list(QueryImpl.java:87) at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:606) at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:483) ... Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "$2" Position: 181 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2284) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2003) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:200) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:424) at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:161) at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:114) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:70) ...
Interestingly, a similar query structure works (because the second pair is seen as a vector rather than anything related to the values function): ... values(?1,?2), (?3,?4) ... Since the query includes an SQL function which is outside of what is supported by the JPA specification, should the Hibernate parser raise an exception for the use of values? |