LIMIT keyword no longer supported

[Pierre Caron]

Hi,

If I run a simple query with a LIMIT clause, H2 throws a syntax error

E.g. : SELECT * FROM TRADING_MESSAGE LIMIT 50

Syntax error in SQL statement "SELECT * FROM TRADING_MESSAGE LIMIT[*] 50"; SQL statement:

I'm using the latest version 2.0.206 with a Spring Boot (2.5.6) project. I also use MODE=Oracle.

The query is generated by Hibernate, so I have no control on how the limiting clause is implemented.

Is there any fix / workaround for this?

Thanks a lot.

Pierre C.

[Evgenij Ryazanov]

Hello!

This issue was fixed in sources of Hibernate ORM two years ago, but version 6.0 is still under development.

You can execute the following code during initialization of your application:

org.h2.engine.Mode mode = org.h2.engine.Mode.getInstance("ORACLE");
mode.limit = true;

Please note that this workaround uses internals of H2, they may be modified in every version of H2 without a notice.

[Pierre Caron]

Thanks a lot Evgenij for the tip.

However, sorry for not understanding.  I tried those two lines :

• in my application-test.properties
• concetenated at the end of the url 

and neither one did work.

What am I missing?

Thanks.

[Pierre Caron]

OK, thanks. I succeeded by putting the code in the datasource configuration as explained in https://www.baeldung.com/spring-testing-separate-data-source#using-spring-profiles.

Thansk again Evgenij!

[Mile Trajkov]

Hi,

Im struggling with same issue but could not set the  mode.limit.
Can somewhere be shared some piece of code?
Thank you

[vinay kumar]

Hi, 

I am facing the same issue as well. How do we set the mode.limit as true with application yaml. This is my configuration currently in spring boot application yaml under test/resources. Not sure where to set the mode limit as true. Please help !! 

application:

    datasource:

      database: H2

      url: jdbc:h2:mem:testdb:envers;Mode=Oracle;

      userName: sa

      password:  

      showSql: true

      driverClassName: org.h2.Driver

      jpaDialect: org.hibernate.dialect.H2Dialect

      generateDDL: true

      connectionPoolSize: 10

Like mentioned same as above in the thread, the query is generated by Hibernate having no control on how the limiting clause is implemented cause of pageable. Need a work around to resolve this. 

Thanks in advance. 

Vinay

[amdy diop]

HI,

I had the same error with the Limit clause.
I added the two lines and got another error

org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: Intégrité référentielle violation de contrainte: "FK55C5X9BQFTYA0DTX82SI280P6: PUBLIC.MON_FNR FOREIGN KEY(SPA_MAG_ID) REFERENCES PUBLIC.SPA_MAG(ICODE_SPA_MAG) (1851)"
Referential integrity constraint violation: "FK55C5X9BQFTYA0DTX82SI280P6: PUBLIC.MON_FNR FOREIGN KEY(SPA_MAG_ID) REFERENCES PUBLIC.SPA_MAG(ICODE_SPA_MAG) (1851)"; SQL statement:
delete from SPA_MAG [23503-220]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:520)
at org.h2.message.DbException.getJdbcSQLException(DbException.java:489)
at org.h2.message.DbException.get(DbException.java:223)
at org.h2.message.DbException.get(DbException.java:199)
at org.h2.constraint.ConstraintReferential.checkRow(ConstraintReferential.java:365)
at org.h2.constraint.ConstraintReferential.checkRowRefTable(ConstraintReferential.java:382)
at org.h2.constraint.ConstraintReferential.checkRow(ConstraintReferential.java:256)
at org.h2.table.Table.fireConstraints(Table.java:1200)
at org.h2.table.Table.fireAfterRow(Table.java:1218)
at org.h2.command.dml.Delete.update(Delete.java:92)
at org.h2.command.dml.DataChangeStatement.update(DataChangeStatement.java:74)
at org.h2.command.CommandContainer.update(CommandContainer.java:169)
at org.h2.command.Command.executeUpdate(Command.java:252)
at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:252)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:223)
at org.dbunit.database.statement.SimpleStatement.executeBatch(SimpleStatement.java:69)
at org.dbunit.operation.DeleteAllOperation.execute(DeleteAllOperation.java:126)
at org.dbunit.operation.CompositeOperation.execute(CompositeOperation.java:79)
at com.excilys.ebi.spring.dbunit.DbUnitDatabasePopulator.populate(DbUnitDatabasePopulator.java:61)
at com.excilys.ebi.spring.dbunit.DefaultDataLoader.executeOperation(DefaultDataLoader.java:58)
at com.excilys.ebi.spring.dbunit.DefaultDataLoader.execute(DefaultDataLoader.java:45)
at com.excilys.ebi.spring.dbunit.test.DataSetTestExecutionListener.beforeTestMethod(DataSetTestExecutionListener.java:63)
at org.springframework.test.context.TestContextManager.beforeTestMethod(TestContextManager.java:293)
at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:74)
at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:366)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:251)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:97)
at org.junit.runners.ParentRunner$4.run(ParentRunner.java:331)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:79)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:329)
at org.junit.runners.ParentRunner.access$100(ParentRunner.java:66)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:293)
at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
at org.junit.runners.ParentRunner.run(ParentRunner.java:413)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:190)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:93)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:40)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:529)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:756)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:452)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:210)

and nowhere did I get depressed about the tests.

Thanks for your help.

[Eric Njogu]

Thanks for the tip Evgenij Ryazanov. Was facing a similar issue. Added the following to a Junit5 integration test and it fixed the problem.

@BeforeEach
void setup() {

 org.h2.engine.Mode mode = org.h2.engine.Mode.getInstance("ORACLE");
 mode.limit = true;
}