When using SQL Server for the database, the paging plugin pageHelper cannot page properly

[zhangheng0027]

When using SQL Server for the database, the paging plugin pageHelper cannot page properly

[zhangheng0027]

@Test
public void test02() {
    PageHelper.startPage(1, 2, false).setOrderBy("equipment_id asc");
    List<EquipmentOee> list = service.list();
    System.out.println(list.size());
}

Caused by: java.sql.SQLException: Unknown exception: Index 2 out of bounds for length 2
	at org.apache.shardingsphere.infra.util.exception.external.sql.ShardingSphereSQLException.toSQLException(ShardingSphereSQLException.java:62)
	at org.apache.shardingsphere.dialect.SQLExceptionTransformEngine.toSQLException(SQLExceptionTransformEngine.java:62)
	at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.execute(ShardingSpherePreparedStatement.java:416)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
	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:336)
	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.pageQuery(ExecutorUtil.java:215)
	at com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:161)
	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:59)
	at jdk.proxy3/jdk.proxy3.$Proxy125.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: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.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:425)
	... 101 more
Caused by: java.lang.IndexOutOfBoundsException: Index 2 out of bounds for length 2
	at java.base/jdk.internal.util.Preconditions.outOfBounds(Preconditions.java:64)
	at java.base/jdk.internal.util.Preconditions.outOfBoundsCheckIndex(Preconditions.java:70)
	at java.base/jdk.internal.util.Preconditions.checkIndex(Preconditions.java:266)
	at java.base/java.util.Objects.checkIndex(Objects.java:361)
	at java.base/java.util.ArrayList.get(ArrayList.java:427)
	at org.apache.shardingsphere.infra.binder.segment.select.pagination.PaginationContext.getValue(PaginationContext.java:56)
	at org.apache.shardingsphere.infra.binder.segment.select.pagination.PaginationContext.<init>(PaginationContext.java:51)
	at org.apache.shardingsphere.infra.binder.segment.select.pagination.engine.LimitPaginationContextEngine.createPaginationContext(LimitPaginationContextEngine.java:38)
	at org.apache.shardingsphere.infra.binder.segment.select.pagination.engine.PaginationContextEngine.createPaginationContext(PaginationContextEngine.java:57)
	at org.apache.shardingsphere.infra.binder.statement.dml.SelectStatementContext.setUpParameters(SelectStatementContext.java:388)
	at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.createQueryContext(ShardingSpherePreparedStatement.java:559)
	at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.execute(ShardingSpherePreparedStatement.java:393)
	... 121 more

[TherChenYang]

@zhangheng0027 thank you for your feedback. Can you provide your configuration and table init sql to help us reproduce this bug?

[zhangheng0027]

init sql

CREATE TABLE equipment_oee (
       serial_no bigint NOT NULL,
       equipment_id nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
       statistics_cycle nvarchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
       statistics_time nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
       oee_ratio float DEFAULT NULL NULL,
       availability float DEFAULT NULL NULL,
       effectiveness_performance float DEFAULT NULL NULL,
       quality_ratio float DEFAULT NULL NULL,
       actual_production_time bigint DEFAULT NULL NULL,
       plan_occupancy_time bigint DEFAULT NULL NULL,
       beat_time float DEFAULT NULL NULL,
       produced_quantity int DEFAULT NULL NULL,
       good_quantity int DEFAULT NULL NULL,
       CONSTRAINT PK_equipment_oee_serial_no PRIMARY KEY (serial_no)
);

conf

dataSources:
  master:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver
    jdbcUrl: jdbc:sqlserver://localhost:1433;DatabaseName=oee;encrypt=false;
    username: sa
    password: Aa123456

props:
  sql-show: true


rules:
  - !SINGLE
    tables:
      - master.*
    defaultDataSource: master

[strongduanmu]

Hi @zhangheng0027, can you test with master branch?