Syntax error in 1.4.197

[Pravin Patil]

Hi,

I'm new to H2 db. I'm getting following exception when I try to run java application using hibernate. 

h2 version - 1.4.197

jdk - 8

Please help me.

Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "SELECT MAX(SCOREBOARD0_.SCORE_BOARD) AS COL_0_0_ FROM SCORE_BOARD SCOREBOARD0_ WHERE SCOREBOARD0_.SCORE_ID IN () [*]"; SQL statement:

select max(scoreboard0_.score_board) as col_0_0_ from score_board scoreboard0_ where scoreboard0_.score_id in () [42000-197]

at org.h2.message.DbException.getJdbcSQLException(DbException.java:357)

at org.h2.message.DbException.get(DbException.java:179)

at org.h2.message.DbException.get(DbException.java:155)

at org.h2.message.DbException.getSyntaxError(DbException.java:203)

at org.h2.command.Parser.getSyntaxError(Parser.java:548)

at org.h2.command.Parser.readCondition(Parser.java:2436)

at org.h2.command.Parser.readAnd(Parser.java:2342)

at org.h2.command.Parser.readExpression(Parser.java:2334)

at org.h2.command.Parser.parseSelectSimple(Parser.java:2291)

at org.h2.command.Parser.parseSelectSub(Parser.java:2133)

at org.h2.command.Parser.parseSelectUnion(Parser.java:1946)

at org.h2.command.Parser.parseSelect(Parser.java:1919)

at org.h2.command.Parser.parsePrepared(Parser.java:463)

at org.h2.command.Parser.parse(Parser.java:335)

at org.h2.command.Parser.parse(Parser.java:311)

at org.h2.command.Parser.prepareCommand(Parser.java:278)

at org.h2.engine.Session.prepareLocal(Session.java:611)

at org.h2.engine.Session.prepareCommand(Session.java:549)

at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1247)

at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:76)

at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:304)

at org.apache.commons.dbcp2.DelegatingConnection.prepareStatement(DelegatingConnection.java:292)

at org.apache.commons.dbcp2.DelegatingConnection.prepareStatement(DelegatingConnection.java:292)

at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:146)

at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:172)

... 37 more

Note - It was working fine in 1.4.188 h2 version.

[Evgenij Ryazanov]

Hello.

IN predicate with empty list is not valid according to the SQL Standard. However, some databases, including H2, support it.

H2 1.4.193 and later versions don't allow it in DB2, MySQL, Oracle, and PostgreSQL compatibility modes because these databases don't allow it.

When you use a compatibility mode you shouldn't rely on the H2-specific features.

[Pravin Patil]

Thank you Evgenij, Life saver.

[Evgenij Ryazanov]

BTW, take a look on the section “Prepared Statements and IN(...)” in the documentation:

https://h2database.com/html/performance.html#database_performance_tuning

Please note that ANY(?) is a non-standard PostgreSQL-style feature that is supported since H2 1.4.197. It can be not suitable for cross-database applications.

[Pravin Patil]

thanks!