spring-cloud-dataflow icon indicating copy to clipboard operation
spring-cloud-dataflow copied to clipboard

Published 20 hours ago • verified publisher icon spring-cloud

Pagination problems

Open esthermd opened this issue 3 years ago • 5 comments
trafficstars

Problems in pagination when we have tables with aliases. When getting the second page (method generateJumpToItemQuery), it gives an ambiguity error, because I need to specify the table in the sort column. This in the second page gives an error since it forms the query using the sort column as a field in the select, and therefore I can't specify the table in the sort column:

Code org.springframework.batch.item.database.support.SqlPagingQueryUtils:

sql.append("SELECT ").append(outerSelectClause).append(" FROM (SELECT ").append(outerSelectClause).append(", ")
				.append(StringUtils.hasText(provider.getGroupClause())
						? "MIN(ROWNUM) as TMP_ROW_NUM"
						: "ROWNUM as TMP_ROW_NUM");
		sql.append(" FROM (SELECT ").append(innerSelectClause).append(" FROM ").append(provider.getFromClause());
		buildWhereClause(provider, remainingPageQuery, sql);
		buildGroupByClause(provider, sql);
		sql.append(" ORDER BY ").append(buildSortClause(provider));
		sql.append(")) WHERE ").append(rowNumClause);

Where outerSelectClause= sortKeySelect SQL result: "SELECT JOB_EXECUTION_ID FROM (SELECT JOB_EXECUTION_ID, ROWNUM as TMP_ROW_NUM FROM (SELECT E.JOB_EXECUTION_ID FROM BATCH_JOB_EXECUTION E, BATCH_JOB_INSTANCE I, TASK_TASK_BATCH B WHERE E.JOB_INSTANCE_ID=I.JOB_INSTANCE_ID and B.JOB_EXECUTION_ID = E.JOB_EXECUTION_ID ORDER BY JOB_EXECUTION_ID DESC)) WHERE TMP_ROW_NUM = 20";

We found this error when executing the getJobExecutionsWithStepCount method in the JdbcSearchableJobExecutionDao class and we had to put aliases in the tables, but the same thing happens in the getJobExecutionsWithStepCountFilteredByTaskExecutionId method which already existed and has not changed.

I add log with the error without specifying table in the sort column :

org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [SELECT JOB_EXECUTION_ID FROM (SELECT JOB_EXECUTION_ID, ROWNUM as TMP_ROW_NUM FROM (SELECT JOB_EXECUTION_ID FROM BATCH_JOB_EXECUTION E, BATCH_JOB_INSTANCE I, TASK_TASK_BATCH B WHERE E.JOB_INSTANCE_ID=I.JOB_INSTANCE_ID and B.JOB_EXECUTION_ID = E.JOB_EXECUTION_ID ORDER BY JOB_EXECUTION_ID DESC)) WHERE TMP_ROW_NUM = 20]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00918: columna definida de forma ambigua

at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:93)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79)
at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1541)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:393)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:465)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:475)
at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:508)
at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:515)
at org.springframework.cloud.dataflow.server.batch.JdbcSearchableJobExecutionDao.getJobExecutionsWithStepCount(JdbcSearchableJobExecutionDao.java:410)
at org.springframework.cloud.dataflow.server.batch.SimpleJobService.listJobExecutionsWithStepCount(SimpleJobService.java:204)
at org.springframework.cloud.dataflow.server.service.impl.DefaultTaskJobService.listJobExecutionsWithStepCount(DefaultTaskJobService.java:121)
at org.springframework.cloud.dataflow.server.service.impl.DefaultTaskJobService$$FastClassBySpringCGLIB$$d486827e.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:783)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:753)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:753)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:698)
at org.springframework.cloud.dataflow.server.service.impl.DefaultTaskJobService$$EnhancerBySpringCGLIB$$94fc1923.listJobExecutionsWithStepCount(<generated>)
at org.springframework.cloud.dataflow.server.controller.JobExecutionThinController.listJobsOnly(JobExecutionThinController.java:93)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:568)
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:150)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:117)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:895)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:808)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1067)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:963)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:655)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:764)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:102)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:96)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:540)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:357)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:382)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:895)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1732)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.base/java.lang.Thread.run(Thread.java:833)

Caused by: java.sql.SQLSyntaxErrorException: ORA-00918: columna definida de forma ambigua

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:630)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:564)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1151)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:771)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:299)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:498)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:112)
at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:896)
at oracle.jdbc.driver.OracleStatement.prepareDefineBufferAndExecute(OracleStatement.java:1172)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1101)
at oracle.jdbc.driver.OracleStatement.executeSQLSelect(OracleStatement.java:1426)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1309)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1713)
at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:394)
at com.zaxxer.hikari.pool.ProxyStatement.executeQuery(ProxyStatement.java:110)
at com.zaxxer.hikari.pool.HikariProxyStatement.executeQuery(HikariProxyStatement.java)
at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:452)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:381)
... 77 common frames omitted

Caused by: oracle.jdbc.OracleDatabaseException: ORA-00918: columna definida de forma ambigua

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:637)
... 94 common frames omitted

2022-03-16 12:24:04.716 WARN 3120 --- [nio-9393-exec-2] .m.m.a.ExceptionHandlerExceptionResolver : Resolved [org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [SELECT JOB_EXECUTION_ID FROM (SELECT JOB_EXECUTION_ID, ROWNUM as TMP_ROW_NUM FROM (SELECT JOB_EXECUTION_ID FROM BATCH_JOB_EXECUTION E, BATCH_JOB_INSTANCE I, TASK_TASK_BATCH B WHERE E.JOB_INSTANCE_ID=I.JOB_INSTANCE_ID and B.JOB_EXECUTION_ID = E.JOB_EXECUTION_ID ORDER BY JOB_EXECUTION_ID DESC)) WHERE TMP_ROW_NUM = 20]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00918: columna definida de forma ambigua<EOL>

specifying the table in the sort column:

org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [SELECT B.JOB_EXECUTION_ID FROM (SELECT B.JOB_EXECUTION_ID, ROWNUM as TMP_ROW_NUM FROM (SELECT B.JOB_EXECUTION_ID FROM BATCH_JOB_EXECUTION E, BATCH_JOB_INSTANCE I, TASK_TASK_BATCH B WHERE E.JOB_INSTANCE_ID=I.JOB_INSTANCE_ID and B.JOB_EXECUTION_ID = E.JOB_EXECUTION_ID ORDER BY B.JOB_EXECUTION_ID DESC)) WHERE TMP_ROW_NUM = 20]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00904: "B"."JOB_EXECUTION_ID": identificador no válido

at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:239)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1541)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:393)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:465)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:475)
at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:508)
at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:515)
at org.springframework.cloud.dataflow.server.batch.JdbcSearchableJobExecutionDao.getJobExecutionsWithStepCount(JdbcSearchableJobExecutionDao.java:410)
at org.springframework.cloud.dataflow.server.batch.SimpleJobService.listJobExecutionsWithStepCount(SimpleJobService.java:204)
at org.springframework.cloud.dataflow.server.service.impl.DefaultTaskJobService.listJobExecutionsWithStepCount(DefaultTaskJobService.java:121)
at org.springframework.cloud.dataflow.server.service.impl.DefaultTaskJobService$$FastClassBySpringCGLIB$$d486827e.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:783)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:753)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:753)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:698)
at org.springframework.cloud.dataflow.server.service.impl.DefaultTaskJobService$$EnhancerBySpringCGLIB$$94fc1923.listJobExecutionsWithStepCount(<generated>)
at org.springframework.cloud.dataflow.server.controller.JobExecutionThinController.listJobsOnly(JobExecutionThinController.java:93)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:568)
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:150)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:117)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:895)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:808)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1067)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:963)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:655)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:764)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:102)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:96)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:540)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:357)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:382)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:895)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1732)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.base/java.lang.Thread.run(Thread.java:833)

Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "B"."JOB_EXECUTION_ID": identificador no válido

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:630)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:564)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1151)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:771)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:299)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:498)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:112)
at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:896)
at oracle.jdbc.driver.OracleStatement.prepareDefineBufferAndExecute(OracleStatement.java:1172)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1101)
at oracle.jdbc.driver.OracleStatement.executeSQLSelect(OracleStatement.java:1426)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1309)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1713)
at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:394)
at com.zaxxer.hikari.pool.ProxyStatement.executeQuery(ProxyStatement.java:110)
at com.zaxxer.hikari.pool.HikariProxyStatement.executeQuery(HikariProxyStatement.java)
at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:452)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:381)
... 77 common frames omitted

Caused by: oracle.jdbc.OracleDatabaseException: ORA-00904: "B"."JOB_EXECUTION_ID": identificador no válido

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:637)
... 94 common frames omitted

2022-03-16 12:34:23.339 WARN 3120 --- [nio-9393-exec-2] .m.m.a.ExceptionHandlerExceptionResolver : Resolved [org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [SELECT B.JOB_EXECUTION_ID FROM (SELECT B.JOB_EXECUTION_ID, ROWNUM as TMP_ROW_NUM FROM (SELECT B.JOB_EXECUTION_ID FROM BATCH_JOB_EXECUTION E, BATCH_JOB_INSTANCE I, TASK_TASK_BATCH B WHERE E.JOB_INSTANCE_ID=I.JOB_INSTANCE_ID and B.JOB_EXECUTION_ID = E.JOB_EXECUTION_ID ORDER BY B.JOB_EXECUTION_ID DESC)) WHERE TMP_ROW_NUM = 20]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00904: "B"."JOB_EXECUTION_ID": identificador no válido<EOL>]

Data:

<parent>
		<groupId>org.springframework.cloud</groupId>
		<artifactId>spring-cloud-dataflow-parent</artifactId>
		<version>2.9.2</version>
	</parent>
	<artifactId>spring-cloud-dataflow-server</artifactId>
	<packaging>jar</packaging>
	<name>Spring Cloud Data Flow Server</name>
	<description>Spring Cloud Data Flow Server</description>

esthermd avatar Mar 16 '22 11:03 esthermd

Hello, Just need a little clarification:

  • What version of SCDF are you using?
  • What are the steps to reproduce the error?
  • Are you modifying the queries used by SCDF (the code) or the structure of the tables created by SCDF?

cppwfs avatar Mar 29 '22 15:03 cppwfs

Hi,

We are using the version 2.9.2

To reproduce the error: RestController: JobExecutionThinController --> retrieveJobsByTaskExecutionId From postman: curl --location --request GET ' http://localhost:9393/jobs/thinexecutions?taskExecutionId=56&page=0' this is ok but if we would like going to the second page: curl --location --request GET ' http://localhost:9393/jobs/thinexecutions?taskExecutionId=56&page=1' you have the next error:

{ "_embedded": { "errors": [ { "message": "PreparedStatementCallback; bad SQL grammar [SELECT JOB_EXECUTION_ID FROM (SELECT JOB_EXECUTION_ID, ROWNUM as TMP_ROW_NUM FROM (SELECT JOB_EXECUTION_ID FROM BATCH_JOB_EXECUTION E, BATCH_JOB_INSTANCE I, TASK_TASK_BATCH B WHERE E.JOB_INSTANCE_ID=I.JOB_INSTANCE_ID and B.JOB_EXECUTION_ID = E.JOB_EXECUTION_ID AND B.TASK_EXECUTION_ID = ? ORDER BY JOB_EXECUTION_ID DESC)) WHERE TMP_ROW_NUM = 20]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00918: *columna definida de forma ambigua*n", "logref": "BadSqlGrammarException", "_links": { "self": { "href": "/" } } } ] } }

[image: image.png]

And in the console you could read the same: 2022-03-30 12:57:39.107 ERROR 11596 --- [nio-9393-exec-6] o.s.c.d.s.c.RestControllerAdvice : Caught exception while handling a request

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT JOB_EXECUTION_ID FROM (SELECT JOB_EXECUTION_ID, ROWNUM as TMP_ROW_NUM FROM (SELECT JOB_EXECUTION_ID FROM BATCH_JOB_EXECUTION E, BATCH_JOB_INSTANCE I, TASK_TASK_BATCH B WHERE E.JOB_INSTANCE_ID=I.JOB_INSTANCE_ID and B.JOB_EXECUTION_ID = E.JOB_EXECUTION_ID AND B.TASK_EXECUTION_ID = ? ORDER BY JOB_EXECUTION_ID DESC)) WHERE TMP_ROW_NUM = 20]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00918: columna definida de forma ambigua

at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:93) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79) at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1541) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:667) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:713) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:744) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:757) at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:879) at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:906) at org.springframework.cloud.dataflow.server.batch.JdbcSearchableJobExecutionDao.getJobExecutionsWithStepCountFilteredByTaskExecutionId(JdbcSearchableJobExecutionDao.java:299) at org.springframework.cloud.dataflow.server.batch.SimpleJobService.listJobExecutionsForJobWithStepCountFilteredByTaskExecutionId(SimpleJobService.java:466) at org.springframework.cloud.dataflow.server.service.impl.DefaultTaskJobService.listJobExecutionsForJobWithStepCountFilteredByTaskExecutionId(DefaultTaskJobService.java:155) at org.springframework.cloud.dataflow.server.service.impl.DefaultTaskJobService$$FastClassBySpringCGLIB$$d486827e.invoke() at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:783) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:753) at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:753) at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:698) at org.springframework.cloud.dataflow.server.service.impl.DefaultTaskJobService$$EnhancerBySpringCGLIB$$fee8c373.listJobExecutionsForJobWithStepCountFilteredByTaskExecutionId() at org.springframework.cloud.dataflow.server.controller.JobExecutionThinController.retrieveJobsByTaskExecutionId(JobExecutionThinController.java:174) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.base/java.lang.reflect.Method.invoke(Method.java:568) at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205) at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:150) at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:117) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:895) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:808) at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1067) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:963) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006) at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898) at javax.servlet.http.HttpServlet.service(HttpServlet.java:655) at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883) at javax.servlet.http.HttpServlet.service(HttpServlet.java:764) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:102) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:96) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:540) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:357) at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:382) at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:895) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1732) at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191) at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.base/java.lang.Thread.run(Thread.java:833) Caused by: java.sql.SQLSyntaxErrorException: ORA-00918: columna definida de forma ambigua

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:630) at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:564) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1151) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:771) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:299) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:498) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:152) at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:937) at oracle.jdbc.driver.OracleStatement.prepareDefineBufferAndExecute(OracleStatement.java:1172) at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1101) at oracle.jdbc.driver.OracleStatement.executeSQLSelect(OracleStatement.java:1426) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1309) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3746) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3855) at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1098) at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:722) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:651) ... 78 common frames omitted Caused by: oracle.jdbc.OracleDatabaseException: ORA-00918: columna definida de forma ambigua

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:637) ... 96 common frames omitted

2022-03-30 12:57:39.115 WARN 11596 --- [nio-9393-exec-6] .m.m.a.ExceptionHandlerExceptionResolver : Resolved [org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT JOB_EXECUTION_ID FROM (SELECT JOB_EXECUTION_ID, ROWNUM as TMP_ROW_NUM FROM (SELECT JOB_EXECUTION_ID FROM BATCH_JOB_EXECUTION E, BATCH_JOB_INSTANCE I, TASK_TASK_BATCH B WHERE E.JOB_INSTANCE_ID=I.JOB_INSTANCE_ID and B.JOB_EXECUTION_ID = E.JOB_EXECUTION_ID AND B.TASK_EXECUTION_ID = ? ORDER BY JOB_EXECUTION_ID DESC)) WHERE TMP_ROW_NUM = 20]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00918: columna definida de forma ambigua<EOL>]

In this case, we are modifying nothing.

I hope this information helps you.

Esther

El mar, 29 mar 2022 a las 17:41, Glenn Renfro @.***>) escribió:

Hello, Just need a little clarification:

  • What version of SCDF are you using?
  • What are the steps to reproduce the error?
  • Are you modifying the queries used by SCDF (the code) or the structure of the tables created by SCDF?

— Reply to this email directly, view it on GitHub https://github.com/spring-cloud/spring-cloud-dataflow/issues/4856#issuecomment-1082038922, or unsubscribe https://github.com/notifications/unsubscribe-auth/AWRJABMUSVW34RCEAFK75ATVCMQCXANCNFSM5Q3T232A . You are receiving this because you authored the thread.Message ID: @.***>

esthermd avatar Mar 30 '22 11:03 esthermd

The primary issue is that the code that generates the paginated query is Batch specific and thus does not include the proper alias for the introduction additional table (from task_batch) that has its own batch_execution_id. Thus new code will have to be added to dataflow that will generate the proper alias for batch_execution_id.

cppwfs avatar Mar 30 '22 22:03 cppwfs

We do not yet officially support Oracle, but some efforts have been made. Thanks for reporting

markpollack avatar Oct 07 '22 17:10 markpollack

I just opened a related issue in Spring Batch. Please take a look: https://github.com/spring-projects/spring-batch/issues/4396

Roughly, the generateJumpToItemQuery() method has been removed from PagingQueryProvider in Spring Batch 5. However, that brings the opportunity to generate the query in an alternative (and simpler) way, based on OFFSET/FETCH, instead of ROWNUM, which fixes the ambiguities in aliases as a side-effect.

aritzbastida avatar Jun 09 '23 11:06 aritzbastida