union all order by 同时使用count出错

---
name: PageHelper issue report template
about: If you would like to report a issue to PageHelper, please use this template.

---

- [ ] 我已在 [issues](https://github.com/pagehelper/Mybatis-PageHelper/issues) 搜索类似问题，并且不存在相同的问题.

## 异常模板

### 使用环境

* PageHelper 版本: 5.3.3
* 数据库类型和版本: sqlserver
* JDBC_URL: xxx

### SQL 解析错误
Cause: com.microsoft.sqlserver.jdbc.SQLServerException: 除非另外还指定了 TOP、OFFSET 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。
#### 分页参数

```java
PageHelper.startPage(1, 10);
xxMapper.select(model);
```

#### 原 SQL

```sql
select
	t1.[UniqueId] uniqueId ,
	t1.YnxtLoanContactNumber loanContractNumber ,
	CONVERT (NVARCHAR (20),
	t1.[DataCreateTime],
	120) createTime ,
	t1.[DataCreateTime] orderTime ,
	t1.[ProductCode] ,
	t1.ProcessStatus status ,
	CONVERT (NVARCHAR (20),
	t1.EndDate,
	120) endDate ,
	CONVERT (NVARCHAR (20),
	t1.BeginDate,
	120) stratDate ,
	CONVERT (NVARCHAR (20),
	t1.SignDate,
	120) signTime ,
	t1.IDCardNo idCardNo ,
	t1.ContractAmount contractAmount ,
	t1.ShortName shortName ,
	t1.ParentUniqueId parentUniqueId ,
	t1.loanType ,
	t2.Status PayStatus,
	t3.IsApprove,
	t2.ActExcutedTime
from
	(
	select
		a.UniqueId,
		a.YnxtLoanContactNumber,
		a.ContractAmount,
		a.SignDate,
		a.BeginDate,
		a.EndDate,
		a.DataCreateTime,
		a.DataLastChangeTime,
		a.ProductCode,
		a.ShortName,
		a.TelephoneNo,
		a.IDCardNo,
		14 ProcessStatus,
		a.ParentUniqueId,
		2 loanType
	from
		CA_SubLoan a with(nolock)
	where
		1 = 1
		and a.DataCreateTime >= '2018-11-11'
		and a.DataCreateTime < '2023-11-11'
union all
	select
		a.UniqueId,
		a.YnxtLoanContactNumber,
		a.ContractAmount,
		a.SignDate,
		a.BeginDate,
		a.EndDate,
		a.DataCreateTime,
		a.DataLastChangeTime,
		a.ProductCode,
		a.ShortName,
		a.TelephoneNo,
		a.IDCardNo,
		a.ProcessStatus,
		UniqueId ParentUniqueId,
		3 loanType
	from
		CA_Loan a with(nolock)
	where
		1 = 1
		and a.DataCreateTime >= '2018-11-11'
		and a.DataCreateTime < '2023-11-11' ) t1
LEFT JOIN CA_PaymentInfo AS t2 ON
	t2.UniqueId = t1.UniqueId
LEFT JOIN CA_EnterpriseProduct t3 ON
	t1.ProductCode = t3.ProductCode
where
	1 = 1
order by
	createTime DESC
```

#### 期望的结果：

```sql
SELECT
	count(0)
FROM
	(
	SELECT
		a.UniqueId,
		a.YnxtLoanContactNumber,
		a.ContractAmount,
		a.SignDate,
		a.BeginDate,
		a.EndDate,
		a.DataCreateTime,
		a.DataLastChangeTime,
		a.ProductCode,
		a.ShortName,
		a.TelephoneNo,
		a.IDCardNo,
		14 ProcessStatus,
		a.ParentUniqueId,
		2 loanType
	FROM
		CA_SubLoan a WITH(NOLOCK)
	WHERE
		1 = 1
		AND a.DataCreateTime >= '2018-11-11'
		AND a.DataCreateTime < '2023-11-11'
UNION ALL
	SELECT
		a.UniqueId,
		a.YnxtLoanContactNumber,
		a.ContractAmount,
		a.SignDate,
		a.BeginDate,
		a.EndDate,
		a.DataCreateTime,
		a.DataLastChangeTime,
		a.ProductCode,
		a.ShortName,
		a.TelephoneNo,
		a.IDCardNo,
		a.ProcessStatus,
		UniqueId ParentUniqueId,
		3 loanType
	FROM
		CA_Loan a WITH(NOLOCK)
	WHERE
		1 = 1
		AND a.DataCreateTime >= '2018-11-11'
		AND a.DataCreateTime < '2023-11-11') t1
LEFT JOIN CA_PaymentInfo AS t2 ON
	t2.UniqueId = t1.UniqueId
LEFT JOIN CA_EnterpriseProduct t3 ON
	t1.ProductCode = t3.ProductCode
WHERE
	1 = 1

```

### 完整异常信息

```
org.springframework.jdbc.UncategorizedSQLException: 
### Error querying database.  Cause: com.microsoft.sqlserver.jdbc.SQLServerException: 除非另外还指定了 TOP、OFFSET 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。
### The error may exist in com/yunxin/EnterpriseMapper/EnterpriseSigningRecordMapper.java (best guess)
### The error may involve com.yunxin.EnterpriseMapper.EnterpriseSigningRecordMapper.getAuthorizedSigningRecord-Inline
### The error occurred while setting parameters
### SQL: select count(0) from (  select t1.[UniqueId] uniqueId ,t1.YnxtLoanContactNumber loanContractNumber ,CONVERT (NVARCHAR (20),t1.[DataCreateTime],120) createTime ,t1.[DataCreateTime] orderTime ,t1.[ProductCode]  ,t1.ProcessStatus status ,CONVERT (NVARCHAR (20),t1.EndDate,120) endDate ,CONVERT (NVARCHAR (20),t1.BeginDate,120) stratDate ,CONVERT (NVARCHAR (20),t1.SignDate,120) signTime ,t1.IDCardNo idCardNo ,t1.ContractAmount contractAmount ,t1.ShortName shortName ,t1.ParentUniqueId parentUniqueId ,t1.loanType ,t2.Status PayStatus,t3.IsApprove,t2.ActExcutedTime from ( select  a.UniqueId,a.YnxtLoanContactNumber,a.ContractAmount,a.SignDate,a.BeginDate,a.EndDate,a.DataCreateTime,a.DataLastChangeTime,a.ProductCode,a.ShortName,a.TelephoneNo,a.IDCardNo,14 ProcessStatus,a.ParentUniqueId,2 loanType  from CA_SubLoan a WITH(NOLOCK) where 1=1        and a.DataCreateTime >= ?       and a.DataCreateTime < ?             union all select  a.UniqueId,a.YnxtLoanContactNumber,a.ContractAmount,a.SignDate,a.BeginDate,a.EndDate,a.DataCreateTime,a.DataLastChangeTime,a.ProductCode,a.ShortName,a.TelephoneNo,a.IDCardNo,a.ProcessStatus,UniqueId ParentUniqueId,3 loanType  from CA_Loan a WITH(NOLOCK) where 1=1      and a.DataCreateTime >= ?       and a.DataCreateTime < ?             ) t1 LEFT JOIN CA_PaymentInfo AS t2  ON t2.UniqueId = t1.UniqueId LEFT JOIN CA_EnterpriseProduct t3  ON t1.ProductCode = t3.ProductCode where 1=1   order by createTime DESC  ) tmp_count
### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: 除非另外还指定了 TOP、OFFSET 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。
; uncategorized SQLException; SQL state [S0001]; error code [1033]; 除非另外还指定了 TOP、OFFSET 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: 除非另外还指定了 TOP、OFFSET 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。

at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:93)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:439)
	at com.sun.proxy.$Proxy126.selectList(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:224)
	at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:147)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:80)
	at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:142)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:86)
	at com.sun.proxy.$Proxy223.getAuthorizedSigningRecord(Unknown Source)
	at com.yunxin.EnterpriseMapper.EnterpriseSigningRecordMapperTest.getAuthorizedSigningRecord(EnterpriseSigningRecordMapperTest.java:27)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:59)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:56)
	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
	at org.springframework.test.context.junit4.statements.RunBeforeTestExecutionCallbacks.evaluate(RunBeforeTestExecutionCallbacks.java:74)
	at org.springframework.test.context.junit4.statements.RunAfterTestExecutionCallbacks.evaluate(RunAfterTestExecutionCallbacks.java:84)
	at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
	at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
	at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:366)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:251)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:97)
	at org.junit.runners.ParentRunner$4.run(ParentRunner.java:331)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:79)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:329)
	at org.junit.runners.ParentRunner.access$100(ParentRunner.java:66)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:293)
	at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
	at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
	at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:413)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:190)
	at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
	at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:69)
	at com.intellij.rt.junit.IdeaTestRunner$Repeater$1.execute(IdeaTestRunner.java:38)
	at com.intellij.rt.execution.junit.TestsRepeater.repeat(TestsRepeater.java:11)
	at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:35)
	at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:232)
	at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:55)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: 除非另外还指定了 TOP、OFFSET 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:265)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1662)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:615)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:537)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7417)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3488)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:262)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:237)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:515)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3446)
	at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:434)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3444)
	at com.alibaba.druid.filter.FilterAdapter.preparedStatement_execute(FilterAdapter.java:1063)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3444)
	at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:152)
	at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:483)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:58)
	at com.sun.proxy.$Proxy289.execute(Unknown Source)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:65)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:80)
	at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:65)
	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:333)
	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:158)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:110)
	at com.github.pagehelper.util.ExecutorUtil.executeAutoCount(ExecutorUtil.java:169)
	at com.github.pagehelper.PageInterceptor.count(PageInterceptor.java:197)
	at com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:140)
	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:59)
	at com.sun.proxy.$Proxy480.query(Unknown Source)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:154)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:142)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:425)
	... 41 more
```

### 其他类型的错误

## 功能建议

详细说明，尽可能提供(伪)代码示例。

abel533/Mybatis_PageHelper

内容风险标识

评论 (1)

abel533/Mybatis_PageHelper .gitee-modal { width: 500px !important; }

内容风险标识