sharding 和mybatis存在sql分页不兼容

[wanghaipeng1205]

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?

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).

[wanghaipeng1205]

[wanghaipeng1205]

为什么会有2条统计的sql,导致我总数无法查出来

[wanghaipeng1205]

Rule Type: sharding
[2019-12-05 11:16:08.410] [http-nio-8258-exec-6] INFO Sharding-Sphere-SQL - Logic SQL: SELECT COUNT(1) FROM order_return_goods WHERE mall_code = ? AND is_delete = ?
[2019-12-05 11:16:08.410] [http-nio-8258-exec-6] INFO Sharding-Sphere-SQL - SQLStatement: SelectStatement(super=DQLStatement(super=AbstractSQLStatement(type=DQL, tables=Tables(tables=[Table(name=order_return_goods, alias=Optional.absent())]), conditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(beginPosition=21, skippedSchemaNameLength=0, originalLiterals=order_return_goods)], parametersIndex=2)), containStar=false, selectListLastPosition=16, groupByLastPosition=0, items=[AggregationSelectItem(type=COUNT, innerExpression=(1), alias=Optional.absent(), derivedAggregationSelectItems=[], index=1)], groupByItems=[], orderByItems=[], limit=null, subQueryStatement=null)
[2019-12-05 11:16:08.410] [http-nio-8258-exec-6] INFO Sharding-Sphere-SQL - Actual SQL: shardingds ::: SELECT COUNT(1) FROM order_return_goods WHERE mall_code = ? AND is_delete = ? ::: [[0202A003, 0]]
[2019-12-05 11:16:08.413] [http-nio-8258-exec-6] INFO Sharding-Sphere-SQL - Rule Type: sharding
[2019-12-05 11:16:08.413] [http-nio-8258-exec-6] INFO Sharding-Sphere-SQL - Logic SQL: SELECT COUNT(1) FROM order_return_goods WHERE mall_code = ? AND is_delete = ? LIMIT ? OFFSET ?
[2019-12-05 11:16:08.413] [http-nio-8258-exec-6] INFO Sharding-Sphere-SQL - SQLStatement: SelectStatement(super=DQLStatement(super=AbstractSQLStatement(type=DQL, tables=Tables(tables=[Table(name=order_return_goods, alias=Optional.absent())]), conditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(beginPosition=21, skippedSchemaNameLength=0, originalLiterals=order_return_goods)], parametersIndex=4)), containStar=false, selectListLastPosition=16, groupByLastPosition=0, items=[AggregationSelectItem(type=COUNT, innerExpression=(1), alias=Optional.absent(), derivedAggregationSelectItems=[], index=1)], groupByItems=[], orderByItems=[], limit=null, subQueryStatement=null)
[2019-12-05 11:16:08.413] [http-nio-8258-exec-6] INFO Sharding-Sphere-SQL - Actual SQL: shardingds ::: SELECT COUNT(1) FROM order_return_goods WHERE mall_code = ? AND is_delete = ? LIMIT ? OFFSET ? ::: [[0202A003, 0, 0, 10]]
[2019-12-05 11:16:08.415] [http-nio-8258-exec-6] INFO Sharding-Sphere-SQL - Rule Type: sharding
[2019-12-05 11:16:08.415] [http-nio-8258-exec-6] INFO Sharding-Sphere-SQL - Logic SQL: SELECT return_id,mall_code,return_state,approve_state,return_reason_type,return_reason,return_condition,order_no,order_sub_no,user_id,phone,real_name,order_type,commodity_name,commodity_type,pay_type,accounts_type,auto_point,return_quality,return_money,consume_id,consume_code,consume_time,consume_merchant_name,consume_merchant_code,consume_amount,create_by,create_source,complete_time,is_delete,create_time,update_time,consume_quality,return_trade_no FROM order_return_goods

WHERE mall_code = ? AND is_delete = ? ORDER BY create_time DESC LIMIT ?,?
[2019-12-05 11:16:08.415] [http-nio-8258-exec-6] INFO Sharding-Sphere-SQL - SQLStatement: SelectStatement(super=DQLStatement(super=AbstractSQLStatement(type=DQL, tables=Tables(tables=[Table(name=order_return_goods, alias=Optional.absent())]), conditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(beginPosition=460, skippedSchemaNameLength=0, originalLiterals=order_return_goods)], parametersIndex=4)), containStar=false, selectListLastPosition=455, groupByLastPosition=0, items=[CommonSelectItem(expression=mall_code, alias=Optional.absent()), CommonSelectItem(expression=user_id, alias=Optional.absent()), CommonSelectItem(expression=consume_code, alias=Optional.absent()), CommonSelectItem(expression=consume_merchant_code, alias=Optional.absent()), CommonSelectItem(expression=approve_state, alias=Optional.absent()), CommonSelectItem(expression=order_sub_no, alias=Optional.absent()), CommonSelectItem(expression=create_by, alias=Optional.absent()), CommonSelectItem(expression=consume_quality, alias=Optional.absent()), CommonSelectItem(expression=real_name, alias=Optional.absent()), CommonSelectItem(expression=is_delete, alias=Optional.absent()), CommonSelectItem(expression=auto_point, alias=Optional.absent()), CommonSelectItem(expression=accounts_type, alias=Optional.absent()), CommonSelectItem(expression=complete_time, alias=Optional.absent()), CommonSelectItem(expression=commodity_name, alias=Optional.absent()), CommonSelectItem(expression=return_reason, alias=Optional.absent()), CommonSelectItem(expression=return_trade_no, alias=Optional.absent()), CommonSelectItem(expression=return_id, alias=Optional.absent()), CommonSelectItem(expression=return_reason_type, alias=Optional.absent()), CommonSelectItem(expression=return_state, alias=Optional.absent()), CommonSelectItem(expression=order_no, alias=Optional.absent()), CommonSelectItem(expression=return_quality, alias=Optional.absent()), CommonSelectItem(expression=pay_type, alias=Optional.absent()), CommonSelectItem(expression=return_condition, alias=Optional.absent()), CommonSelectItem(expression=consume_time, alias=Optional.absent()), CommonSelectItem(expression=create_time, alias=Optional.absent()), CommonSelectItem(expression=return_money, alias=Optional.absent()), CommonSelectItem(expression=order_type, alias=Optional.absent()), CommonSelectItem(expression=consume_merchant_name, alias=Optional.absent()), CommonSelectItem(expression=phone, alias=Optional.absent()), CommonSelectItem(expression=update_time, alias=Optional.absent()), CommonSelectItem(expression=consume_id, alias=Optional.absent()), CommonSelectItem(expression=commodity_type, alias=Optional.absent()), CommonSelectItem(expression=create_source, alias=Optional.absent()), CommonSelectItem(expression=consume_amount, alias=Optional.absent())], groupByItems=[], orderByItems=[OrderItem(owner=Optional.absent(), name=Optional.of(create_time), orderDirection=DESC, nullOrderDirection=ASC, index=-1, alias=Optional.absent())], limit=null, subQueryStatement=null)
[2019-12-05 11:16:08.415] [http-nio-8258-exec-6] INFO Sharding-Sphere-SQL - Actual SQL: shardingds ::: SELECT return_id,mall_code,return_state,approve_state,return_reason_type,return_reason,return_condition,order_no,order_sub_no,user_id,phone,real_name,order_type,commodity_name,commodity_type,pay_type,accounts_type,auto_point,return_quality,return_money,consume_id,consume_code,consume_time,consume_merchant_name,consume_merchant_code,consume_amount,create_by,create_source,complete_time,is_delete,create_time,update_time,consume_quality,return_trade_no FROM order_return_goods

[dongzl]

Hi @wanghaipeng1205 , For English only, other languages will not accept.

You can close this issue and submit a new issue in English again.

and screenshot doesn't search friendly, can you submit a test project on github?

[terrymanu]

For English only, other languages will not accept.