LIMIT keyword no longer supported

3,737 views
Skip to first unread message

Pierre Caron

unread,
Jan 13, 2022, 8:45:16 AM1/13/22
to H2 Database
Hi,

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

E.g. : SELECT * FROM TRADING_MESSAGE LIMIT 50


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

unread,
Jan 13, 2022, 9:56:05 AM1/13/22
to H2 Database
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

unread,
Jan 13, 2022, 2:34:01 PM1/13/22
to H2 Database
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

unread,
Jan 13, 2022, 3:11:14 PM1/13/22
to H2 Database
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

unread,
Mar 20, 2022, 4:16:18 AM3/20/22
to H2 Database
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

unread,
Apr 4, 2022, 8:23:45 PM4/4/22
to H2 Database
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

unread,
Sep 30, 2023, 1:40:18 AM9/30/23
to H2 Database
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

unread,
Oct 4, 2023, 2:12:02 AM10/4/23
to H2 Database
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;
}
Reply all
Reply to author
Forward
0 new messages