Shardingsphere: mysql page limit Long parameter type error

[rboleynl]

Bug Report

For English only, other languages will not accept.

Before report a bug, make sure you have:

• Searched open and closed GitHub issues.
• Read documentation: ShardingSphere Doc.

Please pay attention on issues you submitted, because we maybe need more details.
If no response more than 7 days and we cannot reproduce it on current information, we will close it.

Please answer these questions before submitting your issue. Thanks!

Which version of ShardingSphere did you use?

4.0.0-RC2-SNAPSHOT(clone from dev branch)

Which project did you use? Sharding-JDBC or Sharding-Proxy?

Sharding-JDBC

Expected behavior

sql execute success

Actual behavior

Caused by: java.lang.ClassCastException: java.lang.Long cannot be cast to java.lang.Integer

Reason analyze (If you can)

my code below:
public List findUsersByPageIntType(Integer begin, Integer end){
String sql = "select id,name,age,email from user limit ?,? ";
Object[] params = new Object[] {begin, end};
List userList = jdbcTemplate.query(sql, params, getUserMapper());
return userList;
}
public List findUsersByPageLongType(Long begin, Long end){
String sql = "select id,name,age,email from user limit ?,? ";
Object[] params = new Object[] {begin, end};
List userList = jdbcTemplate.query(sql, params, getUserMapper());
return userList;
}

first, call findUsersByPageIntType method, it's ok
but call findUsersByPageLongType method there was an error -> Caused by: java.lang.ClassCastException: java.lang.Long cannot be cast to java.lang.Integer

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

1.application.properties
spring.shardingsphere.datasource.names=ds0
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://127.0.0.1:3306/ttx_util?useUnicode=true&useSSL=false&characterEncoding=utf8
spring.shardingsphere.datasource.ds0.username=test
spring.shardingsphere.datasource.ds0.password=test

spring.shardingsphere.sharding.tables.user.actual-data-nodes=ds0.user_$->{0..1}
spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=erp_id
spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression=user_$->{erp_id % 2}
spring.shardingsphere.sharding.tables.user.key-generator.column=id
spring.shardingsphere.sharding.tables.user.key-generator.type=SNOWFLAKE

是否开启SQL显示，默认值: false

spring.shardingsphere.props.sql.show=true
2.table create sql:
CREATE TABLE user_0 (
id bigint(20) NOT NULL COMMENT '主键ID',
erp_id int(11) NOT NULL COMMENT '分表erpId',
name varchar(30) DEFAULT NULL COMMENT '姓名',
age int(11) DEFAULT NULL COMMENT '年龄',
email varchar(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE user_1 (
id bigint(20) NOT NULL COMMENT '主键ID',
erp_id int(11) NOT NULL COMMENT '分表erpId',
name varchar(30) DEFAULT NULL COMMENT '姓名',
age int(11) DEFAULT NULL COMMENT '年龄',
email varchar(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Example codes for reproduce this issue (such as a github link).

[hqq2023623]

The code below shows that Long is not supported
May I ask why you need to use Long for limit ?

Code of org.apache.shardingsphere.core.optimize.sharding.segment.select.pagination.Pagination

    private final int actualOffset;
    private final Integer actualRowCount;

    public Pagination(final PaginationValueSegment offsetSegment, final PaginationValueSegment rowCountSegment, final List<Object> parameters) {
        hasPagination = null != offsetSegment || null != rowCountSegment;
        this.offsetSegment = offsetSegment;
        this.rowCountSegment = rowCountSegment;
        actualOffset = null == offsetSegment ? 0 : getValue(offsetSegment, parameters);
        actualRowCount = null == rowCountSegment ? null : getValue(rowCountSegment, parameters); 
    }

    private int getValue(final PaginationValueSegment paginationValueSegment, final List<Object> parameters) {
        return paginationValueSegment instanceof ParameterMarkerPaginationValueSegment
                ?
                (int) parameters.get(((ParameterMarkerPaginationValueSegment) paginationValueSegment).getParameterIndex())
                : ((NumberLiteralPaginationValueSegment) paginationValueSegment).getValue();
    }

[rboleynl]

@hqq2023623 I used mybatis-plus as orm, its paging plugin used long type parameter as limit parameter.

[rboleynl]

i just write the demo code to verify this problem

[KomachiSion]

I test in mysql 5.7.21, offset and row count of limit can be over INTEGER.MAX.

sql> select * from t_order limit 0, 9999999999
[2019-09-03 09:21:48] 5 rows retrieved starting from 1 in 28ms (execution: 17ms, fetching: 11ms)
sql> select * from t_order limit 9999999999, 9999999999
[2019-09-03 09:22:27] 0 rows retrieved in 37ms (execution: 21ms, fetching: 16ms)

So, I think Pagination in ShardingSphere should use type Long instead of Integer for offset and row count to solve this exception.

[liaoxianrong]

I had the same problem

[sunbufu]

I agree with KomachiSion, can i submit a PR ?

[terrymanu]

@sunbufu Welcome

[TOBACCOCN]

I meet the same problem. 4.0.0-RC3 version could fix it? when does 4.0.0-RC3 come?

[sunbufu]

Yes, you can clone dev branch and try with 4.0.0-RC3-SNAPSHOT again. And the new version is coming, please just wait.