当前使用版本(必填,否则不予处理)
3.5.1
该问题是如何引起的？(确定最新版也有问题再提!!!)
调用默认分页方法时，默认应该调用sleect count(*) from 表 where condition...，但如果字段使用了@TableField对字段名进行了修饰后（如Sybase关键字命名的字段修饰@TableField("[TYPE]")，MySql对图形数据进行转换查询@TableField("ST_AsBinary(polygon_data)")的字段修饰）。统计查询会变为select count(*) from (select 字段... from 表 where 条件.. order by xxx desc) TOTAL。这使得加了排序的分页进行统计时会诱发统计原表不能排序的报错。Sybase例：SQL 错误 [154] [S1000]: An ORDER BY clause is not allowed in a derived table.错误！请检查分页插件的判断。
重现步骤(如果有就写完整)
sybase建表sql：
CREATE TABLE GJJ.dbo.E_IM_IPPLDG (
PLEID varchar(100) NOT NULL,
PRIPID varchar(36) NULL,
ENTNAME varchar(100) NULL,
REGNO varchar(50) NULL,
UNISCID varchar(18) NULL,
TMREGNO varchar(100) NULL,
TMNAME varchar(200) NULL,
KINDS varchar(1) NULL,
PLEDGOR varchar(200) NULL,
IMPORG varchar(200) NULL,
PLEREGPERFROM date NULL,
PLEREGPERTO date NULL,
[TYPE] varchar(1) NULL,
TYPENAME varchar(8) NULL,
CANDATE date NULL,
EQUPLECANREA varchar(200) NULL,
INVALIDDATE date NULL,
INVALIDREA varchar(200) NULL,
PUBLICDATE date NULL,
S_EXT_FROMNODE varchar(6) NULL,
S_EXT_DATATIME date NULL,
CONSTRAINT PK_E_IM_IPPLDG PRIMARY KEY (PLEID)
);
复现实体@TableField修饰：
/**
* 状态（1.有效|2.无效）
*/
@TableField("[TYPE]")
@ApiModelProperty("状态")
private String type;
复现java调用：
String entNo = "3b51d573-015b-1000-e000-6fe10a0c0115";
page(new Page<>(1,10), new LambdaQueryWrapper<EImIppldg>()
.eq(EImIppldg::getPripid, entNo)
.eq(EImIppldg::getType, "1")
.orderByDesc(EImIppldg::getPublicdate));
报错信息
14:42:04.928 2bd6da56c1d1479a93e7e0729b5d03c7 DEBUG BaseJdbcLogger.java137 - ==> Preparing: SELECT COUNT(*) FROM (SELECT PLEID,PRIPID,ENTNAME,REGNO,UNISCID,TMREGNO,TMNAME,KINDS,PLEDGOR,IMPORG,PLEREGPERFROM,PLEREGPERTO,[TYPE],TYPENAME,CANDATE,EQUPLECANREA,INVALIDDATE,INVALIDREA,PUBLICDATE,S_EXT_FROMNODE,S_EXT_DATATIME FROM E_IM_IPPLDG WHERE (PRIPID = ? AND [TYPE] = ?) ORDER BY PUBLICDATE DESC) TOTAL
14:42:04.939 2bd6da56c1d1479a93e7e0729b5d03c7 DEBUG BaseJdbcLogger.java137 - ==> Parameters: 3b51d573-015b-1000-e000-6fe10a0c0115(String), 1(String)
14:42:05.061 2bd6da56c1d1479a93e7e0729b5d03c7 ERROR ExceptionHandlerAdvice.java126 - 数据库读写异常
org.springframework.jdbc.UncategorizedSQLException:
### Error querying database. Cause: java.sql.SQLException: An ORDER BY clause is not allowed in a derived table.
### The error may exist in com/chinaweal/gsxt/mapper/ent/EImIppldgMapper.java (best guess)
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT COUNT(*) FROM (SELECT PLEID,PRIPID,ENTNAME,REGNO,UNISCID,TMREGNO,TMNAME,KINDS,PLEDGOR,IMPORG,PLEREGPERFROM,PLEREGPERTO,[TYPE],TYPENAME,CANDATE,EQUPLECANREA,INVALIDDATE,INVALIDREA,PUBLICDATE,S_EXT_FROMNODE,S_EXT_DATATIME FROM E_IM_IPPLDG WHERE (PRIPID = ? AND [TYPE] = ?) ORDER BY PUBLICDATE DESC) TOTAL
### Cause: java.sql.SQLException: An ORDER BY clause is not allowed in a derived table.
; uncategorized SQLException; SQL state [ZZZZZ]; error code [154]; An ORDER BY clause is not allowed in a derived table.
; nested exception is java.sql.SQLException: An ORDER BY clause is not allowed in a derived table.
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89)
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:91)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441)
at com.sun.proxy.$Proxy104.selectList(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:224)
at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForIPage(MybatisMapperMethod.java:121)
at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:85)
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.$Proxy116.selectPage(Unknown Source)
at com.baomidou.mybatisplus.extension.service.IService.page(IService.java:389)
at com.chinaweal.gsxt.service.ent.impl.EImIppldgServiceImpl.listEImIppldg(EImIppldgServiceImpl.java:42)
at com.chinaweal.gsxt.service.ent.impl.EImIppldgServiceImpl$$FastClassBySpringCGLIB$$dfe2679d.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:687)
at com.chinaweal.gsxt.service.ent.impl.EImIppldgServiceImpl$$EnhancerBySpringCGLIB$$fd09aca9.listEImIppldg(<generated>)
at com.chinaweal.gsxt.service.ent.impl.EImIppldgServiceImpl$$FastClassBySpringCGLIB$$dfe2679d.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:687)
at com.chinaweal.gsxt.service.ent.impl.EImIppldgServiceImpl$$EnhancerBySpringCGLIB$$3221d7a5.listEImIppldg(<generated>)
at com.chinaweal.gsxt.controller.ent.RegisterInfoController.getIppldgPage(RegisterInfoController.java:135)
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:878)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:792)
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:652)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:733)
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 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:125)
at org.apache.shiro.web.servlet.ProxiedFilterChain.doFilter(ProxiedFilterChain.java:66)
at org.apache.shiro.web.servlet.AbstractShiroFilter.executeChain(AbstractShiroFilter.java:449)
at org.apache.shiro.web.servlet.AbstractShiroFilter$1.call(AbstractShiroFilter.java:365)
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:362)
at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at com.alibaba.druid.support.http.WebStatFilter.doFilter(WebStatFilter.java:124)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at com.chinaweal.gsxt.filter.xss.XssFilter.doFilter(XssFilter.java:21)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at com.chinaweal.youfool.framework.springboot.filter.RestLogFilter.doFilter(RestLogFilter.java:101)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at com.chinaweal.youfool.framework.springboot.filter.RepeatlyReadFilter.doFilter(RepeatlyReadFilter.java:28)
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.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:97)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:542)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:143)
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:343)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:374)
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:1590)
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:748)
Caused by: java.sql.SQLException: An ORDER BY clause is not allowed in a derived table.
at com.sybase.jdbc4.jdbc.SybConnection.getAllExceptions(Unknown Source)
at com.sybase.jdbc4.jdbc.SybStatement.handleSQLE(Unknown Source)
at com.sybase.jdbc4.jdbc.SybStatement.nextResult(Unknown Source)
at com.sybase.jdbc4.jdbc.SybStatement.nextResult(Unknown Source)
at com.sybase.jdbc4.jdbc.SybStatement.executeLoop(Unknown Source)
at com.sybase.jdbc4.jdbc.SybStatement.execute(Unknown Source)
at com.sybase.jdbc4.jdbc.SybPreparedStatement.execute(Unknown Source)
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.$Proxy253.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:64)
at com.sun.proxy.$Proxy251.query(Unknown Source)
at org.apache.ibatis.executor.ReuseExecutor.doQuery(ReuseExecutor.java:60)
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325)
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.inner.PaginationInnerInterceptor.willDoQuery(PaginationInnerInterceptor.java:135)
at com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor.intercept(MybatisPlusInterceptor.java:75)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:62)
at com.sun.proxy.$Proxy250.query(Unknown Source)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:151)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:145)
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:427)
... 94 common frames omitted
Comment From: qmdx
这种情况是使用函数导致，建议 XML 自定义 countId 或者传入 page.count(值) 一般 count 也是不需要 order by 的因为内置 sql 解析器无法识别函数语法，不能优化 sql 导致执行异常。