mapper文件中的sql不支持数据库的sum,count,ifnull等函数

Blade 未结

关注

 1  991

夜尽 2020-05-08 13:45

一、该问题的重现步骤是什么？

如图sql，包含一个sum函数

二、你期待的结果是什么？实际看到的又是什么？

我期待调用返回正常，实际上这样：

### Error querying database. Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, please exclude the tableName or statementId.

Error SQL: select count(0) from (SELECT

o.id orderId,

o.tel tel,

o.product_name productName,

o.created_time orderTime,

o.select_type selectType,

o.patient_name patientName,

o.hospital_name hospitalName,

o.hospital_ward hospitalWard,

o.hospital_department hospitalDepartment,

n.real_name nurseName,

n.id nurseId,

a.id agentId,

a.agent_name nurseAgent,

o.start_time startServiceTime,

o.end_time endServiceTime,

o.time_num AS serviceTime,

sum(oa.receive_state = 3) AS realServiceTimes,

o.pay_amount payAmount,

o.doctor_name doctorName,

o.`status` orderStatus,

o.return_status returnStatus,

oa.des nursingNeeds,

oa.address_type AddType,

o.return_visit_content returnVisitContent,

o.return_visit_time returnVisitTime

FROM

care_order o

LEFT JOIN care_nurse n ON o.nurse_id = n.id

LEFT JOIN care_agent a ON a.id = n.agent_id

LEFT JOIN care_order_addition oa ON oa.order_id = o.id

WHERE o.is_deleted = '0'

and o.agent_id = ?

GROUP BY o.id

ORDER BY o.created_time desc) tmp_count

### The error may exist in file [D:\Code\IDEA\saas\blade-care-service\target\classes\com\xyts\platform\care\mapper\OrderMapper.xml]

### The error may involve com.xyts.platform.care.mapper.OrderMapper.orderList_COUNT

### The error occurred while executing a query

### Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, please exclude the tableName or statementId.

Error SQL: select count(0) from (SELECT

o.id orderId,

o.tel tel,

o.product_name productName,

o.created_time orderTime,

o.select_type selectType,

o.patient_name patientName,

o.hospital_name hospitalName,

o.hospital_ward hospitalWard,

o.hospital_department hospitalDepartment,

n.real_name nurseName,

n.id nurseId,

a.id agentId,

a.agent_name nurseAgent,

o.start_time startServiceTime,

o.end_time endServiceTime,

o.time_num AS serviceTime,

sum(oa.receive_state = 3) AS realServiceTimes,

o.pay_amount payAmount,

o.doctor_name doctorName,

o.`status` orderStatus,

o.return_status returnStatus,

oa.des nursingNeeds,

oa.address_type AddType,

o.return_visit_content returnVisitContent,

o.return_visit_time returnVisitTime

FROM

care_order o

LEFT JOIN care_nurse n ON o.nurse_id = n.id

LEFT JOIN care_agent a ON a.id = n.agent_id

LEFT JOIN care_order_addition oa ON oa.order_id = o.id

WHERE o.is_deleted = '0'

and o.agent_id = ?

GROUP BY o.id

ORDER BY o.created_time desc) tmp_count

如果我将函数那行sql 注释，返回值就是正确的

三、你正在使用的是什么产品，什么版本？在什么操作系统上？

bladex 2.4.0 win10

四、请提供详细的错误堆栈信息，这很重要。

com.xytx.common.exception.CommonException: 99999::服务内部异常

at com.xyts.platform.care.service.impl.OrderServiceImpl.orderList(OrderServiceImpl.java:75)

at com.xyts.platform.care.service.impl.OrderServiceImpl$$FastClassBySpringCGLIB$$12e70059.invoke()

at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)

at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:685)

at com.xyts.platform.care.service.impl.OrderServiceImpl$$EnhancerBySpringCGLIB$$f74704f2.orderList()

at com.xyts.platform.care.controller.OrderController.list(OrderController.java:47)

at com.xyts.platform.care.controller.OrderController$$FastClassBySpringCGLIB$$754775db.invoke()

at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)

at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:750)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)

at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:88)

at org.springblade.core.log.aspect.RequestLogAspect.aroundApi(RequestLogAspect.java:110)

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.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:93)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)

at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:689)

at com.xyts.platform.care.controller.OrderController$$EnhancerBySpringCGLIB$$75d52622.list()

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:105)

at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:893)

at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:798)

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.doPost(FrameworkServlet.java:909)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:665)

at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:750)

at io.undertow.servlet.handlers.ServletHandler.handleRequest(ServletHandler.java:74)

at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:129)

at com.github.xiaoymin.knife4j.spring.filter.ProductionSecurityFilter.doFilter(ProductionSecurityFilter.java:53)

at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)

at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)

at com.github.xiaoymin.knife4j.spring.filter.SecurityBasicAuthFilter.doFilter(SecurityBasicAuthFilter.java:90)

at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)

at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)

at org.springblade.core.tool.support.xss.XssFilter.doFilter(XssFilter.java:49)

at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)

at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)

at org.springframework.boot.actuate.web.trace.servlet.HttpTraceFilter.doFilterInternal(HttpTraceFilter.java:88)

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)

at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)

at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)

at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)

at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)

at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)

at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)

at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)

at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)

at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:94)

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)

at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)

at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)

at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.filterAndRecordMetrics(WebMvcMetricsFilter.java:114)

at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:104)

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)

at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)

at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)

at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)

at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)

at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)

at com.alibaba.csp.sentinel.adapter.servlet.CommonFilter.doFilter(CommonFilter.java:110)

at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)

at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)

at io.undertow.servlet.handlers.FilterHandler.handleRequest(FilterHandler.java:84)

at io.undertow.servlet.handlers.security.ServletSecurityRoleHandler.handleRequest(ServletSecurityRoleHandler.java:62)

at io.undertow.servlet.handlers.ServletChain$1.handleRequest(ServletChain.java:68)

at io.undertow.servlet.handlers.ServletDispatchingHandler.handleRequest(ServletDispatchingHandler.java:36)

at io.undertow.servlet.handlers.RedirectDirHandler.handleRequest(RedirectDirHandler.java:68)

at io.undertow.servlet.handlers.security.SSLInformationAssociationHandler.handleRequest(SSLInformationAssociationHandler.java:132)

at io.undertow.servlet.handlers.security.ServletAuthenticationCallHandler.handleRequest(ServletAuthenticationCallHandler.java:57)

at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)

at io.undertow.security.handlers.AbstractConfidentialityHandler.handleRequest(AbstractConfidentialityHandler.java:46)

at io.undertow.servlet.handlers.security.ServletConfidentialityConstraintHandler.handleRequest(ServletConfidentialityConstraintHandler.java:64)

at io.undertow.security.handlers.AuthenticationMechanismsHandler.handleRequest(AuthenticationMechanismsHandler.java:60)

at io.undertow.servlet.handlers.security.CachedAuthenticatedSessionHandler.handleRequest(CachedAuthenticatedSessionHandler.java:77)

at io.undertow.security.handlers.AbstractSecurityContextAssociationHandler.handleRequest(AbstractSecurityContextAssociationHandler.java:43)

at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)

at io.undertow.servlet.handlers.ServletInitialHandler.handleFirstRequest(ServletInitialHandler.java:269)

at io.undertow.servlet.handlers.ServletInitialHandler.access$100(ServletInitialHandler.java:78)

at io.undertow.servlet.handlers.ServletInitialHandler$2.call(ServletInitialHandler.java:133)

at io.undertow.servlet.handlers.ServletInitialHandler$2.call(ServletInitialHandler.java:130)

at io.undertow.servlet.core.ServletRequestContextThreadSetupAction$1.call(ServletRequestContextThreadSetupAction.java:48)

at io.undertow.servlet.core.ContextClassLoaderSetupAction$1.call(ContextClassLoaderSetupAction.java:43)

at io.undertow.servlet.handlers.ServletInitialHandler.dispatchRequest(ServletInitialHandler.java:249)

at io.undertow.servlet.handlers.ServletInitialHandler.access$000(ServletInitialHandler.java:78)

at io.undertow.servlet.handlers.ServletInitialHandler$1.handleRequest(ServletInitialHandler.java:99)

at io.undertow.server.Connectors.executeRootHandler(Connectors.java:376)

at io.undertow.server.HttpServerExchange$1.run(HttpServerExchange.java:830)

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)

at java.lang.Thread.run(Thread.java:748)

java.io.IOException: UT010029: Stream is closed

at io.undertow.servlet.spec.ServletInputStreamImpl.read(ServletInputStreamImpl.java:160)

at sun.nio.cs.StreamDecoder.readBytes(StreamDecoder.java:284)

at sun.nio.cs.StreamDecoder.implRead(StreamDecoder.java:326)

at sun.nio.cs.StreamDecoder.read(StreamDecoder.java:178)

at java.io.InputStreamReader.read(InputStreamReader.java:184)

at java.io.BufferedReader.fill(BufferedReader.java:161)

at java.io.BufferedReader.readLine(BufferedReader.java:324)

at java.io.BufferedReader.readLine(BufferedReader.java:389)

at org.springblade.core.tool.support.xss.XssHttpServletRequestWrapper.inputHandlers(XssHttpServletRequestWrapper.java:103)

at org.springblade.core.tool.support.xss.XssHttpServletRequestWrapper.getInputStream(XssHttpServletRequestWrapper.java:72)

at org.springblade.core.tool.utils.WebUtil.getRequestBytes(WebUtil.java:280)

at org.springblade.core.tool.utils.WebUtil.getRequestStr(WebUtil.java:263)

at org.springblade.core.tool.utils.WebUtil.getRequestParamString(WebUtil.java:245)

at org.springblade.core.log.utils.LogAbstractUtil.addRequestInfoToLog(LogAbstractUtil.java:49)

at org.springblade.core.log.publisher.ErrorLogPublisher.publishEvent(ErrorLogPublisher.java:57)

at org.springblade.core.log.error.BladeRestExceptionTranslator.handleError(BladeRestExceptionTranslator.java:72)

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:105)

at org.springframework.web.servlet.mvc.method.annotation.ExceptionHandlerExceptionResolver.doResolveHandlerMethodException(ExceptionHandlerExceptionResolver.java:413)

at org.springframework.web.servlet.handler.AbstractHandlerMethodExceptionResolver.doResolveException(AbstractHandlerMethodExceptionResolver.java:61)

at org.springframework.web.servlet.handler.AbstractHandlerExceptionResolver.resolveException(AbstractHandlerExceptionResolver.java:141)

at org.springframework.web.servlet.handler.HandlerExceptionResolverComposite.resolveException(HandlerExceptionResolverComposite.java:80)

at org.springframework.web.servlet.DispatcherServlet.processHandlerException(DispatcherServlet.java:1299)

at org.springframework.web.servlet.DispatcherServlet.processDispatchResult(DispatcherServlet.java:1111)

at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1057)