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 = ?)} 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
EXISTSwith 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.
Did you try to enable sql logging to see the actual query issues to the JDBC driver statement?
Also looks like an exception under jpa layer (either jdbc pool or the driver) so checking the actually sent statement will likely give back the issue if openjpa rewrote it (shouldn’t) or not.
Side note: you can likely do the same with hibernate to compare ;).
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.
