How to fix H2 Syntax error in SQL statement?

Question

I asked earlier today about replacing a window function because H2 does not support it.

I rewrote SQL query but every time I get syntax error in the SQL query (posted below)

expected "UNION, MINUS, EXCEPT, INTERSECT, ORDER, OFFSET, FETCH, LIMIT, FOR, ).

Can you please tell where is error in this query?

@Query(value = "
SELECT se.id, se.code, se.created, se.ecid, se.psid
FROM (
    SELECT DISTINCT ps1, created1, created2 
    FROM ( 
        SELECT tmp1.code1, tmp1.created1, tmp1.ec1, tmp1.ps1, tmp2.code2, tmp2.created2, tmp2.ec2, tmp2.ps2, 
            MIN(tmp2.created2) OVER (PARTITION BY tmp2.ec2, tmp2.ps2, tmp1.created1) AS closest 
        FROM (
            (SELECT code as code1, created as created1, ecid as ec1, psid as ps1 
            FROM tableRE 
            WHERE code = ?1 
            GROUP BY code1, created1, ec1, ps1
            ) tmp1 
            LEFT JOIN 
                (SELECT code as code2, created as created2, ecid as ec2, psid as ps2 
                FROM tableRE) tmp2 
            ON tmp2.ps2 = tmp1.ps1 AND tmp2.ec2 = tmp1.ec1 AND tmp1.created1 < tmp2.created2
        )
        ORDER BY tmp1.created1
    ) tmpRes 
    WHERE tmpRes.created2 = tmpRes.closest OR tmpRes.closest IS NULL
) res 
LEFT JOIN tableSE se ON se.created > res.created1 
    AND (CASE WHEN res.created2 is null THEN CURRENT_TIMESTAMP ELSE res.created2 END) > se.created 
    AND se.psid = res.ps1 
ORDER BY se.created DESC", nativeQuery = true)

Error in Intellij:

2019-07-03 07:59:58.817 ERROR 22340 --- [           main] o.h.e.j.s.SqlExceptionHelper             : Syntax error in SQL statement "SELECT SE.ID, SE.CODE, SE.CREATED, SE.ECID, SE.PSID FROM (SELECT DISTINCT PS1, CREATED1, CREATED2 FROM ( SELECT TMP1.CODE1, TMP1.CREATED1, TMP1.EC1, TMP1.PS1, TMP2.CODE2, TMP2.CREATED2, TMP2.EC2, TMP2.PS2, MIN(TMP2.CREATED2) OVER (PARTITION BY TMP2.EC2, TMP2.PS2, TMP1.CREATED1) AS CLOSEST FROM((SELECT CODE AS CODE1, CREATED AS CREATED1, ECID AS EC1, PSID AS PS1 FROM TABLE1 WHERE CODE = ? GROUP BY CODE1, CREATED1, EC1, PS1) TMP1[*] LEFT JOIN (SELECT CODE AS CODE2, CREATED AS CREATED2, ECID AS EC2, PSID AS PS2 FROM TABLE1) TMP2 ON TMP2.PS2 = TMP1.PS1 AND TMP2.EC2 = TMP1.EC1 AND TMP1.CREATED1 < TMP2.CREATED2)ORDER BY TMP1.CREATED1) TMPRES WHERE TMPRES.CREATED2 = TMPRES.CLOSEST OR TMPRES.CLOSEST IS NULL) RES LEFT JOIN TABLE2 SE ON SE.CREATED > RES.CREATED1 AND (CASE WHEN RES.CREATED2 IS NULL THEN CURRENT_TIMESTAMP ELSE RES.CREATED2 END) > SE.CREATED AND SE.PSID = RES.PS1 ORDER BY SE.CREATED DESC "; expected "UNION, EXCEPT, MINUS, INTERSECT, ORDER, OFFSET, FETCH, LIMIT, SAMPLE_SIZE, FOR, )"; SQL statement:
SELECT se.id, se.code, se.created, se.ecid, se.psid FROM (SELECT DISTINCT ps1, created1, created2 FROM ( SELECT tmp1.barcode1, tmp1.created1, tmp1.ec1, tmp1.ps1, tmp2.barcode2, tmp2.created2, tmp2.ec2, tmp2.ps2, MIN(tmp2.created2) OVER (PARTITION BY tmp2.ec2, tmp2.ps2, tmp1.created1) AS closest FROM((SELECT code as code1, created as created1, ecid as ec1, psid as ps1 FROM table1 WHERE code = ? GROUP BY code1, created1, ec1, ps1) tmp1 LEFT JOIN (SELECT code as code2, created as created2, ecid as ec2, psid as ps2 FROM table1) tmp2 ON tmp2.ps2 = tmp1.ps1 AND tmp2.ec2 = tmp1.ec1 AND tmp1.created1 < tmp2.created2) ORDER BY tmp1.created1) tmpRes WHERE tmpRes.created2 = tmpRes.closest OR tmpRes.closest IS NULL) res LEFT JOIN table2 se on se.created > res.created1 and (CASE WHEN res.created2 IS NULL THEN CURRENT_TIMESTAMP ELSE res.created2 END) > se.created and se.psid= res.ps1 ORDER BY se.created DESC [42001-199]
2019-07-03 07:59:58.838 ERROR 22340 --- [           main] s.o.t.a.ExceptionResolver                : could not prepare statement; SQL [SELECT se.id, se.code, se.created, se.ecid, se.psid FROM (SELECT DISTINCT ps1, created1, created2 FROM ( SELECT tmp1.barcode1, tmp1.created1, tmp1.ec1, tmp1.ps1, tmp2.barcode2, tmp2.created2, tmp2.ec2, tmp2.ps2, MIN(tmp2.created2) OVER (PARTITION BY tmp2.ec2, tmp2.ps2, tmp1.created1) AS closest FROM((SELECT code as code1, created as created1, ecid as ec1, psid as ps1 FROM table1 WHERE code = ? GROUP BY code1, created1, ec1, ps1) tmp1 LEFT JOIN (SELECT code as code2, created as created2, ecid as ec2, psid as ps2 FROM table1) tmp2 ON tmp2.ps2 = tmp1.ps1 AND tmp2.ec2 = tmp1.ec1 AND tmp1.created1 < tmp2.created2) ORDER BY tmp1.created1) tmpRes WHERE tmpRes.created2 = tmpRes.closest OR tmpRes.closest IS NULL) res LEFT JOIN table2 se on se.created > res.created1 and (CASE WHEN res.created2 IS NULL THEN CURRENT_TIMESTAMP ELSE res.created2 END) > se.created and se.psid= res.ps1 ORDER BY se.created DESC]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement

org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement; SQL [SELECT se.id, se.code, se.created, se.ecid, se.psid FROM (SELECT DISTINCT ps1, created1, created2 FROM ( SELECT tmp1.barcode1, tmp1.created1, tmp1.ec1, tmp1.ps1, tmp2.barcode2, tmp2.created2, tmp2.ec2, tmp2.ps2, MIN(tmp2.created2) OVER (PARTITION BY tmp2.ec2, tmp2.ps2, tmp1.created1) AS closest FROM((SELECT code as code1, created as created1, ecid as ec1, psid as ps1 FROM table1 WHERE code = ? GROUP BY code1, created1, ec1, ps1) tmp1 LEFT JOIN (SELECT code as code2, created as created2, ecid as ec2, psid as ps2 FROM table1) tmp2 ON tmp2.ps2 = tmp1.ps1 AND tmp2.ec2 = tmp1.ec1 AND tmp1.created1 < tmp2.created2) ORDER BY tmp1.created1) tmpRes WHERE tmpRes.created2 = tmpRes.closest OR tmpRes.closest IS NULL) res LEFT JOIN table2 se on se.created > res.created1 and (CASE WHEN res.created2 IS NULL THEN CURRENT_TIMESTAMP ELSE res.created2 END) > se.created and se.psid= res.ps1 ORDER BY se.created DESC]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:242) ~[spring-orm-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:225) ~[spring-orm-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:527) ~[spring-orm-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61) ~[spring-tx-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242) ~[spring-tx-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:153) ~[spring-tx-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:135) ~[spring-data-jpa-2.0.8.RELEASE.jar:2.0.8.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:61) ~[spring-data-commons-2.0.8.RELEASE.jar:2.0.8.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at com.sun.proxy.$Proxy148.getAllProductsByMaterialUsage(Unknown Source) ~[?:?]
    at sk.optotune.trackerengine.webapp.server.events.EventTimelineServiceImpl.getMaterialUsage(EventTimelineServiceImpl.java:74) ~[classes/:?]
    at sk.optotune.trackerengine.webapp.server.events.EventTimelineServiceImpl$$FastClassBySpringCGLIB$$59afe36d.invoke(<generated>) ~[classes/:?]
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) ~[spring-core-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:746) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294) ~[spring-tx-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98) ~[spring-tx-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at sk.optotune.trackerengine.webapp.server.events.EventTimelineServiceImpl$$EnhancerBySpringCGLIB$$687e3773.getMaterialUsage(<generated>) ~[classes/:?]
    at sk.optotune.trackerengine.webapp.server.events.EventController.getMaterials(EventController.java:79) ~[classes/:?]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_201]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_201]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_201]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_201]
    at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:209) ~[spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136) ~[spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:102) ~[spring-webmvc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:877) ~[spring-webmvc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:783) ~[spring-webmvc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:991) [spring-webmvc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:925) [spring-webmvc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:974) [spring-webmvc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:866) [spring-webmvc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:635) [tomcat-embed-core-8.5.31.jar:8.5.31]
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:851) [spring-webmvc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.test.web.servlet.TestDispatcherServlet.service(TestDispatcherServlet.java:71) [spring-test-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:742) [tomcat-embed-core-8.5.31.jar:8.5.31]
    at org.springframework.mock.web.MockFilterChain$ServletFilterProxy.doFilter(MockFilterChain.java:166) [spring-test-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.mock.web.MockFilterChain.doFilter(MockFilterChain.java:133) [spring-test-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:320) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:127) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:91) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:119) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:137) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:111) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:170) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:116) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:66) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:105) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:56) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:215) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:178) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.security.web.debug.DebugFilter.invokeWithWrappedRequest(DebugFilter.java:90) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.security.web.debug.DebugFilter.doFilter(DebugFilter.java:77) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:357) [spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:270) [spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.mock.web.MockFilterChain.doFilter(MockFilterChain.java:133) [spring-test-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99) [spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.mock.web.MockFilterChain.doFilter(MockFilterChain.java:133) [spring-test-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.web.filter.HttpPutFormContentFilter.doFilterInternal(HttpPutFormContentFilter.java:109) [spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.mock.web.MockFilterChain.doFilter(MockFilterChain.java:133) [spring-test-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:93) [spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.mock.web.MockFilterChain.doFilter(MockFilterChain.java:133) [spring-test-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:200) [spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.mock.web.MockFilterChain.doFilter(MockFilterChain.java:133) [spring-test-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.test.web.servlet.MockMvc.perform(MockMvc.java:165) [spring-test-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at sk.optotune.trackerengine.application.events.EventTimelineServiceImplTest.getMaterialUsageForGivenValidBarcode(EventTimelineServiceImplTest.java:238) [test-classes/:?]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_201]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_201]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_201]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_201]
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50) [junit-4.12.jar:4.12]
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12) [junit-4.12.jar:4.12]
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47) [junit-4.12.jar:4.12]
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17) [junit-4.12.jar:4.12]
    at org.springframework.test.context.junit4.statements.RunBeforeTestExecutionCallbacks.evaluate(RunBeforeTestExecutionCallbacks.java:73) [spring-test-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.test.context.junit4.statements.RunAfterTestExecutionCallbacks.evaluate(RunAfterTestExecutionCallbacks.java:83) [spring-test-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26) [junit-4.12.jar:4.12]
    at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75) [spring-test-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86) [spring-test-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84) [spring-test-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325) [junit-4.12.jar:4.12]
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:251) [spring-test-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:97) [spring-test-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290) [junit-4.12.jar:4.12]
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71) [junit-4.12.jar:4.12]
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288) [junit-4.12.jar:4.12]
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58) [junit-4.12.jar:4.12]
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268) [junit-4.12.jar:4.12]
    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61) [spring-test-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70) [spring-test-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363) [junit-4.12.jar:4.12]
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:190) [spring-test-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.junit.runner.JUnitCore.run(JUnitCore.java:137) [junit-4.12.jar:4.12]
    at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68) [junit-rt.jar:?]
    at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47) [junit-rt.jar:?]
    at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242) [junit-rt.jar:?]
    at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70) [junit-rt.jar:?]
Caused by: org.hibernate.exception.SQLGrammarException: could not prepare statement
    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:181) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:147) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1985) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1915) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1893) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.loader.Loader.doQuery(Loader.java:938) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:341) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.loader.Loader.doList(Loader.java:2692) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.loader.Loader.doList(Loader.java:2675) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2507) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.loader.Loader.list(Loader.java:2502) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:335) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:2200) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.internal.AbstractSharedSessionContract.list(AbstractSharedSessionContract.java:1016) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.query.internal.NativeQueryImpl.doList(NativeQueryImpl.java:152) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1414) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.query.Query.getResultList(Query.java:146) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:129) ~[spring-data-jpa-2.0.8.RELEASE.jar:2.0.8.RELEASE]
    at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:91) ~[spring-data-jpa-2.0.8.RELEASE.jar:2.0.8.RELEASE]
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:136) ~[spring-data-jpa-2.0.8.RELEASE.jar:2.0.8.RELEASE]
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:125) ~[spring-data-jpa-2.0.8.RELEASE.jar:2.0.8.RELEASE]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:590) ~[spring-data-commons-2.0.8.RELEASE.jar:2.0.8.RELEASE]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:578) ~[spring-data-commons-2.0.8.RELEASE.jar:2.0.8.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:59) ~[spring-data-commons-2.0.8.RELEASE.jar:2.0.8.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294) ~[spring-tx-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98) ~[spring-tx-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139) ~[spring-tx-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    ... 116 more

Hippolytus

Break up the query into smaller pieces until those pieces start to work. Then, reasemble the query until you can isolate the problem. – Mountainous

Answer 1

H2 supports window functions since 1.4.198. If you can upgrade H2 to a latest version (1.4.199) it's better to do it instead of such workarounds with overcomplicated queries, they will be slower than a simple query with a window function.

Note that FROM table) part is invalid in recent versions of H2, because TABLE is a keyword, if you have a table with that name it needs to be quoted ("TABLE" or "table" depending on your settings).

If you're really required to use some old unsupported version of H2 such as 1.4.197 or older, you need to post the complete error message here. It includes your SQL command with a [*] mark that indicates a place with a syntax problem.

Ralphralston

With new version of H2, simplier queries works. Table name is too long so I renamed it here for better understanding. I edited query and attached error from console. – Hippolytus

H2 accepts queries like SELECT * FROM (SELECT * FROM T) T2, but does not accept queries like SELECT * FROM ((SELECT * FROM T) T2) (note the additional parentheses) and PostgreSQL also can't parse such query, it is not vaild. – Ralphralston

In your query there is a problem with TMP1 alias. I guess you can specify the alias directly in the inner query like … FROM TABLE1 TMP1 WHERE CODE = ? GROUP BY …. – Ralphralston

PostgreSQL does not have problem with that query, I edited like you suggested in last comment and mark [*] is after LEFT. ...FROM table1 TMP1 WHERE CODE = ? GROUP BY CODE1, CREATED1, EC1, PS1) LEFT[*] JOIN (SELECT ... – Hippolytus

Also the join is a part of a table reference, you can use SELECT * FROM (SELECT …) LEFT JOIN (SELECT …), but you can't write SELECT * FROM ((SELECT …) LEFT JOIN (SELECT …)), it is not valid here. – Ralphralston

h2-2.2.224 (newest atm) and simple select * from A limit 5 --> not supported IF you also have MODE=Oracle; enabled (btw. this is obviously supported in oracle). – Postdiluvian

No, it doesn't. Oracle 23c throws ORA-00933: unexpected token at or near 5. Standard FETCH FIRST 5 ROWS ONLY must be used instead. – Ralphralston

Answer 2

(EDITED) Since recent versions of H2 (2.x) don't understand the SQL LIMIT keyword even in basic queries, you need to explicitly enable it with the mode.limit = true; instruction:

This can be done by defining your datasource programmatically and adding the 2 lines for defining the H2's Mode to Oracle and setting its limit option to true :

@Configuration
@EnableJpaRepositories
@EnableTransactionManagement
public class ServiceConfiguration {

  @Bean
  @Profile("test")
  public DataSource dataSource() {
    DriverManagerDataSource dataSource = new DriverManagerDataSource();
    dataSource.setDriverClassName("org.h2.Driver");
    dataSource.setUrl("jdbc:h2:mem:mydb;DB_CLOSE_DELAY=-1");
    dataSource.setUsername("sa");
    dataSource.setPassword("sa");

    // Ref https://groups.google.com/g/h2-database/c/yxnv64Ak-u8/m/n-kqYV_yBQAJ
    org.h2.engine.Mode mode = org.h2.engine.Mode.getInstance("ORACLE");
    mode.limit = true;

    return dataSource;
  }

}

Alternately, if your datasource is defined via a test properties file (i.e. not programmatically), you can also set the mode.limit property elsewhere. For instance, in a set up method executed before all tests inside your integration test file, e.g. :

@SpringBootTest
public class MyIntegrationTest {

...

  @BeforeAll
  public static void setUpH2() {
    // Hack to fix H2 version 2.x not understanding the LIMIT keyword
    org.h2.engine.Mode mode = org.h2.engine.Mode.getInstance("Oracle");
    mode.limit = true;
  }

...

Having an handle on the org.h2.engine.Modeobject you could also set its other properties by having a look at H2's enums in the Mode class on GitHub.

This solution was kindly provided to me by Evgenij Ryazanov on H2's Google group and as he stated, it involves using internal code that might be changed without warning.

As for using profiles for tests, please refer to Baeldung.

Holbert

Thank you for your answer. It worked for me after setting mode in the url (I am using flyway migration ) dataSource.setUrl("jdbc:h2:mem:testdb:security_permission;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=TRUE;MODE=Oracle" – Rasorial

Life saving answer! Thanks – Nympho

Answer 3

I could solve this by defining a datasource bean as @TestConfiguration

    @Bean
    public DataSource dataSource() {
        // Fix for the H2 2.x issue with spring boot 2.7.
        org.h2.engine.Mode mode = org.h2.engine.Mode.getInstance("Oracle");
        mode.limit = true;
        return new EmbeddedDatabaseBuilder() //
                .setType(H2) //
                .setName("testdb;MODE=Oracle") //
                .ignoreFailedDrops(true) //
                .build();
    }

Rasorial

Answer 4

The provided solutions do work. But they do overwrite parts of the spring boot default data source configuration.

Since it's enough to set the H2 properties just before the first SQL is sent to the DB, we can make use of spring events. This way we do not have to mess with the data source configuration at all.

@Component
public class H2Configuration {

    private final DataSource dataSource;

    public H2Configuration(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @EventListener
    void handleEvent(ApplicationPreparedEvent event) {
        try (Connection connection = dataSource.getConnection()) {
            DatabaseMetaData databaseMetaData = connection.getMetaData();
            if (databaseMetaData.getDatabaseProductName().equalsIgnoreCase("h2")) {
                org.h2.engine.Mode mode = org.h2.engine.Mode.getInstance("ORACLE");
                mode.limit = true;
                mode.numericWithBooleanComparison = true;
                mode.allowEmptyInPredicate = true;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

}

Selfreliant

Answer 5

Using the below configuration seems to fixing the issue for me on spring-boot:2.7.8 :

Soane