ParsingSQLRouter has a problem when select like this select * from table where xxx in () limit 0,20

[scuwuyu]

version:
shardingsphere 3.0.0.M2
select * from table where xxx in () limit 0,20
xxx is shardingColumn,
when one select is isSingleRouting select,the next select which is not SingleRouting select will have a NullPointerException.bacause you set the selectStatement's feild limit to null in ParsingSQLRouter.

[terrymanu]

Hi, could you follow issue template and give us more information?
The template is:

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: Sharding-Sphere 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 Sharding-Sphere did you use?

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

Expected behavior

Actual behavior

Reason analyze (If you can)

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

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

[scuwuyu]

Which version of Sharding-Sphere did you use?

shardingsphere 3.0.0.M3

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

Sharding-JDBC

Expected behavior

select * from table where xxx in () limit 0,20，xxx is shardingColumn.
select normally.

Actual behavior

when one select is isSingleRouting select,the next select which is not SingleRouting select will have a NullPointerException:

Caused by: java.lang.NullPointerException
	at io.shardingsphere.core.rewrite.SQLRewriteEngine.appendLimitRowCount(SQLRewriteEngine.java:205) ~[sharding-core-3.0.0.M3.jar:?]
	at io.shardingsphere.core.rewrite.SQLRewriteEngine.rewrite(SQLRewriteEngine.java:134) ~[sharding-core-3.0.0.M3.jar:?]
	at io.shardingsphere.core.routing.router.sharding.ParsingSQLRouter.route(ParsingSQLRouter.java:105) ~[sharding-core-3.0.0.M3.jar:?]
	at io.shardingsphere.core.routing.PreparedStatementRoutingEngine.route(PreparedStatementRoutingEngine.java:66) ~[sharding-core-3.0.0.M3.jar:?]
	at io.shardingsphere.core.jdbc.core.statement.ShardingPreparedStatement.sqlRoute(ShardingPreparedStatement.java:245) ~[sharding-jdbc-3.0.0.M3.jar:?]
	at io.shardingsphere.core.jdbc.core.statement.ShardingPreparedStatement.execute(ShardingPreparedStatement.java:188) ~[sharding-jdbc-3.0.0.M3.jar:?]
	at sun.reflect.GeneratedMethodAccessor136.invoke(Unknown Source) ~[?:?]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_73]
	at java.lang.reflect.Method.invoke(Method.java:497) ~[?:1.8.0_73]
	at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59) ~[mybatis-3.4.2.jar:3.4.2]
	at com.sun.proxy.$Proxy140.execute(Unknown Source) ~[?:?]
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:63) ~[mybatis-3.4.2.jar:3.4.2]
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79) ~[mybatis-3.4.2.jar:3.4.2]
	at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63) ~[mybatis-3.4.2.jar:3.4.2]
	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324) ~[mybatis-3.4.2.jar:3.4.2]
	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156) ~[mybatis-3.4.2.jar:3.4.2]
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109) ~[mybatis-3.4.2.jar:3.4.2]
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:83) ~[mybatis-3.4.2.jar:3.4.2]
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148) ~[mybatis-3.4.2.jar:3.4.2]
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141) ~[mybatis-3.4.2.jar:3.4.2]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_73]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_73]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_73]
	at java.lang.reflect.Method.invoke(Method.java:497) ~[?:1.8.0_73]

Reason analyze (If you can)

When one select is isSingleRouting,you will remove the selectStatement's limit field,but if the next select is not SingleRouting,you will use this field,then NullPointerException.

The object selectStatement has been cached in ParsingResultCache's cache field.

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
Example codes for reproduce this issue (such as a github link).

[terrymanu]

please provide your SQL, log of show.sql and sharding rule configuration
you use spring and mybatis, we cannot locate only with exception.

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

[scuwuyu]

I‘ve reproduced this issue：
https://github.com/scuwuyu/sharding-sphere-test
I hope to get your help.

[flycjh]

any progress?

[loxp]

PreparedStatementRoutingEngine cache the SQLStatement objects. If the first SQL is single routing, the limit field of SQLStatement is set to null, and if the second SQL is multi routing, it get the cached SQLStatement first, and access to the limit field cause NPE.

public final class ParsingSQLRouter implements ShardingRouter {
    ...
    @Override
    public SQLRouteResult route(final String logicSQL, final List<Object> parameters, final SQLStatement sqlStatement) {
        ...
        boolean isSingleRouting = routingResult.isSingleRouting();
        if (sqlStatement instanceof SelectStatement && null != ((SelectStatement) sqlStatement).getLimit()) {
            processLimit(parameters, (SelectStatement) sqlStatement, isSingleRouting); // single routing set limit to null
        }
        ...
    }
    ...
}

public final class PreparedStatementRoutingEngine {
    ...
    public SQLRouteResult route(final List<Object> parameters) {
        if (null == sqlStatement) {
            sqlStatement = shardingRouter.parse(logicSQL, true); // get the origin object, whose limit field is set to null
        }
        return masterSlaveRouter.route(shardingRouter.route(logicSQL, parameters, sqlStatement));
    }
    ...
}

public final class SQLRewriteEngine {
    ...
    private void appendLimitRowCount(final SQLBuilder sqlBuilder, final RowCountToken rowCountToken, final int count, final List<SQLToken> sqlTokens, final boolean isRewrite) {
        SelectStatement selectStatement = (SelectStatement) sqlStatement;
        Limit limit = selectStatement.getLimit();
        if (!isRewrite) {
            sqlBuilder.appendLiterals(String.valueOf(rowCountToken.getRowCount()));
        } else if ((!selectStatement.getGroupByItems().isEmpty() || !selectStatement.getAggregationSelectItems().isEmpty()) && !selectStatement.isSameGroupByAndOrderByItems()) {
            sqlBuilder.appendLiterals(String.valueOf(Integer.MAX_VALUE));
        } else {
            sqlBuilder.appendLiterals(String.valueOf(limit.isNeedRewriteRowCount() ? rowCountToken.getRowCount() + limit.getOffsetValue() : rowCountToken.getRowCount()));
        }  // here cause NPE
        int beginPosition = rowCountToken.getBeginPosition() + String.valueOf(rowCountToken.getRowCount()).length();
        appendRest(sqlBuilder, count, sqlTokens, beginPosition);
    }
    ...
}