davecramer
commented
1 month ago Any chance you could try with a newer version of the driver? I would need example data and table definitions to try to recreate the issue. Thanks, Dave
Open Shore001 opened 1 month ago
davecramer
commented
1 month ago Any chance you could try with a newer version of the driver? I would need example data and table definitions to try to recreate the issue. Thanks, Dave
Shore001
commented
1 month ago Any chance you could try with a newer version of the driver? I would need example data and table definitions to try to recreate the issue. Thanks, Dave
I created a temporary table and imported some data from the source table. I still encounter this issue when I try to execute it. It's quite strange that when I replace UNION ALL with UNION, the problem seems to go away. Here are the table creation statements, the query SQL, and the example data.
CREATE TABLE table_abc (
group_column1 varchar(30),
group_column2 varchar(30),
column_a numeric(16,4),
column_b numeric(16,4)
);
(
select
'01' level1,
'10' level2,
coalesce(sum(ibm.column_a)/ 5, 0) column_a,
coalesce(sum(ibm.column_b)/ 5, 0) column_b
from
table_abc ibm
) union all (
select
'01' level1,
group_column1 level2,
coalesce(sum(ibm.column_a)/ 5, 0) column_a,
coalesce(sum(ibm.column_b)/ 5, 0) column_b
from
table_abc ibm
group by
ibm.group_column1
) union all (
select
'02' level1,
ibm.group_column2 level2,
coalesce(sum(ibm.column_a)/ 5, 0) column_a,
coalesce(sum(ibm.column_b)/ 5, 0) column_b
from
table_abc ibm
group by
ibm.group_column2
)Here is the stack trace information I encountered while using DBeaver:
org.jkiss.dbeaver.model.sql.DBSQLException: SQL 错误 [22023]: ERROR: unsupported format code: 24432
at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:133)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:513)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$0(SQLQueryJob.java:444)
at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:171)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:431)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:816)
at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:3494)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:118)
at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:171)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:116)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:4821)
at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:105)
at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
Caused by: org.postgresql.util.PSQLException: ERROR: unsupported format code: 24432
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:322)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:308)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:284)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:279)
at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:330)
at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:130)
... 12 more
I'm unable to replicate the problem with your data.
Can you add
org.postgresql.core.v3.QueryExecutorImpl.level=FINEST
to your logging and send me the result ?
Dave
Shore001
commented
1 month ago I'm unable to replicate the problem with your data.
Can you add
org.postgresql.core.v3.QueryExecutorImpl.level=FINESTto your logging and send me the result ?Dave
This is the stack trace information after setting the log level to FINEST.
### Error querying database. Cause: org.postgresql.util.PSQLException: ERROR: unsupported format code: 21304
位置：文件：printtup.c，常式：printtup_prepare_info，行：294
服务器 SQLState：22023
### The error may exist in com/xxxxx/xxxxxMapper.xml
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: ( select '01' level1, '10' level2, coalesce(sum(ibm.column_a)/ 5, 0) column_a, coalesce(sum(ibm.column_b)/ 5, 0) column_b from table_abc ibm ) union all ( select '01' level1, group_column1 level2, coalesce(sum(ibm.column_a)/ 5, 0) column_a, coalesce(sum(ibm.column_b)/ 5, 0) column_b from table_abc ibm group by ibm.group_column1 ) union all ( select '02' level1, ibm.group_column2 level2, coalesce(sum(ibm.column_a)/ 5, 0) column_a, coalesce(sum(ibm.column_b)/ 5, 0) column_b from table_abc ibm group by ibm.group_column2 )
### Cause: org.postgresql.util.PSQLException: ERROR: unsupported format code: 21304
位置：文件：printtup.c，常式：printtup_prepare_info，行：294
服务器 SQLState：22023
; ERROR: unsupported format code: 21304
位置：文件：printtup.c，常式：printtup_prepare_info，行：294
服务器 SQLState：22023; nested exception is org.postgresql.util.PSQLException: ERROR: unsupported format code: 21304
位置：文件：printtup.c，常式：printtup_prepare_info，行：294
服务器 SQLState：22023
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:104)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:88)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440)
at com.sun.proxy.$Proxy125.selectList(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:223)
at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForMany(MybatisMapperMethod.java:166)
at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:77)
at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148)
at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89)
at com.sun.proxy.$Proxy159.testQuery(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at com.baomidou.dynamic.datasource.aop.DynamicDataSourceAnnotationInterceptor.invoke(DynamicDataSourceAnnotationInterceptor.java:57)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
at com.sun.proxy.$Proxy160.testQuery(Unknown Source)
at xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:769)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:747)
at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:88)
at xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:644)
at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:633)
at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:70)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:175)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:747)
at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:88)
at xxxxxxxxxxxxxxxxxxxxxxxxxxx
at xxxxxxxxxxxxxxxxxxxxxxxxxxx
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:644)
at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:633)
at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:70)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:175)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:747)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:95)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:747)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:689)
at xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:106)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:879)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:793)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1040)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:943)
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:634)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:741)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at com.lambo.skywalking.client.service.SkywalkingTracingFilter.doFilter(SkywalkingTracingFilter.java:31)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at com.lambo.arms.client.config.ArmsFilter.doFilter(ArmsFilter.java:28)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.shiro.web.servlet.ProxiedFilterChain.doFilter(ProxiedFilterChain.java:61)
at org.apache.shiro.web.servlet.AdviceFilter.executeChain(AdviceFilter.java:108)
at org.apache.shiro.web.servlet.AdviceFilter.doFilterInternal(AdviceFilter.java:137)
at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:154)
at org.apache.shiro.web.servlet.ProxiedFilterChain.doFilter(ProxiedFilterChain.java:66)
at org.apache.shiro.web.servlet.AdviceFilter.executeChain(AdviceFilter.java:108)
at org.apache.shiro.web.servlet.AdviceFilter.doFilterInternal(AdviceFilter.java:137)
at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:154)
at org.apache.shiro.web.servlet.ProxiedFilterChain.doFilter(ProxiedFilterChain.java:66)
at org.apache.shiro.web.servlet.AbstractShiroFilter.executeChain(AbstractShiroFilter.java:458)
at org.apache.shiro.web.servlet.AbstractShiroFilter$1.call(AbstractShiroFilter.java:373)
at org.apache.shiro.subject.support.SubjectCallable.doCall(SubjectCallable.java:90)
at org.apache.shiro.subject.support.SubjectCallable.call(SubjectCallable.java:83)
at org.apache.shiro.subject.support.DelegatingSubject.execute(DelegatingSubject.java:387)
at org.apache.shiro.web.servlet.AbstractShiroFilter.doFilterInternal(AbstractShiroFilter.java:370)
at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:154)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:109)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:367)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1639)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:750)
Caused by: org.postgresql.util.PSQLException: ERROR: unsupported format code: 21304
位置：文件：printtup.c，常式：printtup_prepare_info，行：294
服务器 SQLState：22023
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2433)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2178)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:155)
at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:144)
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3461)
at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3459)
at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:167)
at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:497)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
at com.sun.proxy.$Proxy281.execute(Unknown Source)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
at com.sun.proxy.$Proxy279.query(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
at com.sun.proxy.$Proxy279.query(Unknown Source)
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324)
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
at com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor.intercept(MybatisPlusInterceptor.java:81)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
at com.sun.proxy.$Proxy278.query(Unknown Source)
at com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:108)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
at com.sun.proxy.$Proxy278.query(Unknown Source)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426)
... 135 common frames omittedHmmm...
I'd expect to see messages from the QueryExecutorImpl.
Did you add the line exactly as above ?
Shore001
commented
4 weeks ago Hmmm...
I'd expect to see messages from the QueryExecutorImpl.
Did you add the line exactly as above ?
Sorry, my bad, I wasn't aware of how to set the parameter . My application uses Logback, which doesn't seem to support FINEST. Now I've changed the log level to TRACE, and I'm seeing some QueryExecutorImpl logs. I'm not sure if this is what you needed.
[2024-08-15 13:56:43:063] [DEBUG] [method:com.baomidou.dynamic.datasource.DynamicRoutingDataSource] [http-nio-8080-exec-3]dynamic-datasource switch to the datasource named [dws]
JDBC Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@67a417a9] will not be managed by Spring
==> Preparing: ( select '01' level1, '10' level2, coalesce(sum(ibm.column_a)/ 5, 0) column_a, coalesce(sum(ibm.column_b)/ 5, 0) column_b from table_abc ibm ) union all ( select '01' level1, group_column1 level2, coalesce(sum(ibm.column_a)/ 5, 0) column_a, coalesce(sum(ibm.column_b)/ 5, 0) column_b from table_abc ibm group by ibm.group_column1 ) union all ( select '02' level1, ibm.group_column2 level2, coalesce(sum(ibm.column_a)/ 5, 0) column_a, coalesce(sum(ibm.column_b)/ 5, 0) column_b from table_abc ibm group by ibm.group_column2 )
==> Parameters:
[2024-08-15 13:56:43:064] [TRACE] [method:org.postgresql.core.v3.QueryExecutorImpl] [http-nio-8080-exec-3] simple execute, handler=org.postgresql.jdbc.PgStatement$StatementResultHandler@4bf1beb1, maxRows=0, fetchSize=0, flags=16
[2024-08-15 13:56:43:064] [TRACE] [method:org.postgresql.core.v3.QueryExecutorImpl] [http-nio-8080-exec-3] FE=> Parse(stmt=S_1,query="(
select
'01' level1,
'10' level2,
coalesce(sum(ibm.column_a)/ 5, 0) column_a,
coalesce(sum(ibm.column_b)/ 5, 0) column_b
from
table_abc ibm
) union all (
select
'01' level1,
group_column1 level2,
coalesce(sum(ibm.column_a)/ 5, 0) column_a,
coalesce(sum(ibm.column_b)/ 5, 0) column_b
from
table_abc ibm
group by
ibm.group_column1
) union all (
select
'02' level1,
ibm.group_column2 level2,
coalesce(sum(ibm.column_a)/ 5, 0) column_a,
coalesce(sum(ibm.column_b)/ 5, 0) column_b
from
table_abc ibm
group by
ibm.group_column2
)",oids={})
[2024-08-15 13:56:43:064] [TRACE] [method:org.postgresql.core.v3.QueryExecutorImpl] [http-nio-8080-exec-3] FE=> Bind(stmt=S_1,portal=null)
[2024-08-15 13:56:43:064] [TRACE] [method:org.postgresql.core.v3.QueryExecutorImpl] [http-nio-8080-exec-3] FE=> Describe(portal=null)
[2024-08-15 13:56:43:064] [TRACE] [method:org.postgresql.core.v3.QueryExecutorImpl] [http-nio-8080-exec-3] FE=> Execute(portal=null,limit=0)
[2024-08-15 13:56:43:064] [TRACE] [method:org.postgresql.core.v3.QueryExecutorImpl] [http-nio-8080-exec-3] FE=> Sync
[2024-08-15 13:56:43:087] [TRACE] [method:org.postgresql.core.v3.QueryExecutorImpl] [http-nio-8080-exec-3] <=BE ParseComplete [S_1]
[2024-08-15 13:56:43:087] [TRACE] [method:org.postgresql.core.v3.QueryExecutorImpl] [http-nio-8080-exec-3] <=BE BindComplete [unnamed]
[2024-08-15 13:56:43:087] [TRACE] [method:org.postgresql.core.v3.QueryExecutorImpl] [http-nio-8080-exec-3] <=BE RowDescription(4)
[2024-08-15 13:56:43:087] [TRACE] [method:org.postgresql.core.v3.QueryExecutorImpl] [http-nio-8080-exec-3] Field(level1,TEXT,65535,T)
[2024-08-15 13:56:43:087] [TRACE] [method:org.postgresql.core.v3.QueryExecutorImpl] [http-nio-8080-exec-3] Field(level2,VARCHAR,65535,T)
[2024-08-15 13:56:43:087] [TRACE] [method:org.postgresql.core.v3.QueryExecutorImpl] [http-nio-8080-exec-3] Field(column_a,NUMERIC,65535,T)
[2024-08-15 13:56:43:087] [TRACE] [method:org.postgresql.core.v3.QueryExecutorImpl] [http-nio-8080-exec-3] Field(column_b,NUMERIC,65535,T)
[2024-08-15 13:56:43:087] [TRACE] [method:org.postgresql.core.v3.QueryExecutorImpl] [http-nio-8080-exec-3] <=BE DataRow(len=42)
[2024-08-15 13:56:43:087] [TRACE] [method:org.postgresql.core.v3.QueryExecutorImpl] [http-nio-8080-exec-3] <=BE ErrorMessage(ERROR: unsupported format code: 309
位置：文件：printtup.c，常式：printtup_prepare_info，行：294
服务器 SQLState：22023)
[2024-08-15 13:56:43:087] [TRACE] [method:org.postgresql.core.v3.QueryExecutorImpl] [http-nio-8080-exec-3] <=BE ReadyForQuery(I)
[2024-08-15 13:56:43:087] [TRACE] [method:org.postgresql.core.v3.QueryExecutorImpl] [http-nio-8080-exec-3] FE marking setPortalDescribed(false) for query SYNC
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3767a28b]
[2024-08-15 13:56:43:088] [DEBUG] [method:org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator] [http-nio-8080-exec-3]Unable to translate SQLException with SQL state '22023', error code '0, will now try the fallback translator
[2024-08-15 13:56:43:088] [DEBUG] [method:org.springframework.jdbc.support.SQLStateSQLExceptionTranslator] [http-nio-8080-exec-3]Extracted SQL state class '22' from value '22023'
[2024-08-15 13:56:43:088] [DEBUG] [method:org.springframework.web.servlet.mvc.method.annotation.ExceptionHandlerExceptionResolver] [http-nio-8080-exec-3]Using @ExceptionHandler
[2024-08-15 13:56:43:088] [TRACE] [method:org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod] [http-nio-8080-exec-3]Arguments: [org.apache.shiro.web.servlet.ShiroHttpServletRequest@1a073e8a, org.apache.shiro.web.servlet.ShiroHttpServletResponse@5c450f4d, org.springframework.dao.DataIntegrityViolationException:
### Error querying database. Cause: org.postgresql.util.PSQLException: ERROR: unsupported format code: 309
位置：文件：printtup.c，常式：printtup_prepare_info，行：294
服务器 SQLState：22023
### The error may exist in com/xxxxxxxApiMapper.xml
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: ( select '01' level1, '10' level2, coalesce(sum(ibm.column_a)/ 5, 0) column_a, coalesce(sum(ibm.column_b)/ 5, 0) column_b from table_abc ibm ) union all ( select '01' level1, group_column1 level2, coalesce(sum(ibm.column_a)/ 5, 0) column_a, coalesce(sum(ibm.column_b)/ 5, 0) column_b from table_abc ibm group by ibm.group_column1 ) union all ( select '02' level1, ibm.group_column2 level2, coalesce(sum(ibm.column_a)/ 5, 0) column_a, coalesce(sum(ibm.column_b)/ 5, 0) column_b from table_abc ibm group by ibm.group_column2 )
### Cause: org.postgresql.util.PSQLException: ERROR: unsupported format code: 309
位置：文件：printtup.c，常式：printtup_prepare_info，行：294
服务器 SQLState：22023
; ERROR: unsupported format code: 309
位置：文件：printtup.c，常式：printtup_prepare_info，行：294
服务器 SQLState：22023; nested exception is org.postgresql.util.PSQLException: ERROR: unsupported format code: 309
位置：文件：printtup.c，常式：printtup_prepare_info，行：294
服务器 SQLState：22023]So it doesn't look like we log the format fields that we send. https://www.postgresql.org/docs/current/protocol-message-formats.html#PROTOCOL-MESSAGE-FORMATS-BIND If I were to give you a snapshot jar which logs it, could you use it ? The format codes need to be 0 or 1. You seem to be sending random data. Network issue possibly ?
Dave
Shore001
commented
4 weeks ago So it doesn't look like we log the format fields that we send. https://www.postgresql.org/docs/current/protocol-message-formats.html#PROTOCOL-MESSAGE-FORMATS-BIND If I were to give you a snapshot jar which logs it, could you use it ? The format codes need to be 0 or 1. You seem to be sending random data. Network issue possibly ?
Dave
Perhaps I can try using the snapshot JAR you mentioned. I've changed union all to union in my application, and the issue hasn't reappeared. However, I'm not sure what caused the problem in the first place.
I am encountering an issue where the same SQL query sometimes executes successfully and other times fails with an unsupported format code error. This inconsistency is causing problems in our application. Here is the stack trace from one of the failed executions:
Driver Version? postgresql-42.2.20
Java Version? 1.8.0_322
OS Version? unknown
PostgreSQL Version? 9.4.24
To Reproduce Steps to reproduce the behaviour: 1.Execute the following SQL query multiple times: (The table and column names have been replaced with non-sensitive information for privacy reasons.)
2.Sometimes the query executes successfully, but other times it fails with the unsupported format code error. I suspect that the data type of the
level1andlevel2fields might be the issue. When each subquery is executed separately, the value'10'forlevel2is being recognized as anunknowntype, whereasgroup_column1forlevel2is explicitly avarchartype. However, when I use'10'::varchar for level2andgroup_column1::varchar for level2, the number of errors decreased, but errors still occur occasionally.Expected behaviour The SQL query should execute consistently without any errors.
Actual Behavior The SQL query sometimes fails with the unsupported format code error.