Search code examples
javasqlhibernatejpaopenjpa

Why does OpenJPA complain about "unexpected end of statement" when using EXISTS clause in named native query?

I have a @NamedNativeQuery in Hibernate, which works fine:

SELECT EXISTS (SELECT (ui.user_id) FROM USERS_INSTITUTION ui, USERS u WHERE u.id = ui.user_id AND ui.INSTITUTION_ID = ?1 AND u.username = ?2)

However, I am required to port this query to OpenJPA. Sadly, this results in an exception:

Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: unexpected end of statement in statement [SELECT EXISTS (SELECT (ui.user_id) FROM USERS_INSTITUTION ui, USERS u WHERE u.id = ui.user_id AND ui.INSTITUTION_ID = ? AND u.username = ?)] {SELECT EXISTS (SELECT (ui.user_id) FROM USERS_INSTITUTION ui, USERS u WHERE u.id = ui.user_id AND ui.INSTITUTION_ID = ? AND u.username = ?)} [code=-5590, state=42590]
    at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:219) ~[openjpa-3.1.0.jar:3.1.0]
    at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:199) ~[openjpa-3.1.0.jar:3.1.0]
    at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$000(LoggingConnectionDecorator.java:58) ~[openjpa-3.1.0.jar:3.1.0]
    at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection.prepareStatement(LoggingConnectionDecorator.java:252) ~[openjpa-3.1.0.jar:3.1.0]
    at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:138) ~[openjpa-3.1.0.jar:3.1.0]
    at org.apache.openjpa.lib.jdbc.ConfiguringConnectionDecorator$ConfiguringConnection.prepareStatement(ConfiguringConnectionDecorator.java:144) ~[openjpa-3.1.0.jar:3.1.0]
    at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:138) ~[openjpa-3.1.0.jar:3.1.0]
    at org.apache.openjpa.jdbc.kernel.JDBCStoreManager$RefCountConnection.prepareStatement(JDBCStoreManager.java:1695) ~[openjpa-3.1.0.jar:3.1.0]
    at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:127) ~[openjpa-3.1.0.jar:3.1.0]
    at org.apache.openjpa.jdbc.sql.SQLBuffer.prepareStatement(SQLBuffer.java:517) ~[openjpa-3.1.0.jar:3.1.0]
    at org.apache.openjpa.jdbc.sql.SQLBuffer.prepareStatement(SQLBuffer.java:497) ~[openjpa-3.1.0.jar:3.1.0]
    at org.apache.openjpa.jdbc.sql.SQLBuffer.prepareStatement(SQLBuffer.java:486) ~[openjpa-3.1.0.jar:3.1.0]
    at org.apache.openjpa.jdbc.kernel.SQLStoreQuery$SQLExecutor.prepareStatement(SQLStoreQuery.java:310) ~[openjpa-3.1.0.jar:3.1.0]
    at org.apache.openjpa.jdbc.kernel.SQLStoreQuery$SQLExecutor.executeQuery(SQLStoreQuery.java:217) ~[openjpa-3.1.0.jar:3.1.0]
    ... 59 more

My questions are:

  • What am I missing here?
  • Is this related to the JPA specification?
  • Am I not allowed to use EXISTS with a sub select in OpenJPA?

UPDATE / SOLUTION / CONTEXT

I was working in a JUnit test setup using HSQLDB. In my Hibernate version, I did specify sql.syntax_pgs=true in the connection property. However, I missed this part in the OpenJPA implementation. Without this property, HSQLDB does not understand the related sql statement.

Solution

  • The issue originated from my JUnit test setup in combination with HSQLDB.

    In my Hibernate environment, I was using sql.syntax_pgs=true with HSQLDB. However, I missed that part in my OpenJPA environment.

    It seems, that HSQLDB does not support SELECT EXISTS(...) clauses without sql.syntax_pgs=true resulting in the exception listed above.