joooohnli commented on issue #2062: Wrong LIMIT rewrite while GROUP BY and ORDER BY on different items URL: https://github.com/apache/incubator-shardingsphere/issues/2062#issuecomment-475150140 ``` 2019-03-21 14:57:16.416 INFO 70268 --- [nio-8090-exec-1] ShardingSphere-SQL : Rule Type: sharding 2019-03-21 14:57:16.416 INFO 70268 --- [nio-8090-exec-1] ShardingSphere-SQL : Logic SQL: select max(sid) as sid, max(sendTime) as sendTime, objectId, sum(0) as unreadCount from TEST where memberId = ? and status != -1 group by objectId order by sendTime desc limit ?, ? 2019-03-21 14:57:16.416 INFO 70268 --- [nio-8090-exec-1] ShardingSphere-SQL : SQLStatement: SelectStatement(super=DQLStatement(super=io.shardingsphere.core.parsing.parser.sql.dql.select.SelectStatement@4db4cf4f), containStar=false, firstSelectItemStartPosition=7, selectListLastPosition=83, groupByLastPosition=153, items=[AggregationSelectItem(type=MAX, innerExpression=(sid), alias=Optional.of(sid), derivedAggregationSelectItems=[], index=-1), AggregationSelectItem(type=MAX, innerExpression=(sendTime), alias=Optional.of(sendTime), derivedAggregationSelectItems=[], index=-1), CommonSelectItem(expression=objectId, alias=Optional.absent()), AggregationSelectItem(type=SUM, innerExpression=(0), alias=Optional.of(unreadCount), derivedAggregationSelectItems=[], index=-1)], groupByItems=[OrderItem(owner=Optional.absent(), name=Optional.of(objectId), orderDirection=ASC, nullOrderDirection=ASC, index=-1, expression=null, alias=Optional.absent())], orderByItems=[OrderItem(owner=Optional.absent(), name=Optional.of(sendTime), orderDirection=DESC, nullOrderDirection=ASC, index=-1, expression=null, alias=Optional.of(sendTime))], limit=Limit(offset=LimitValue(value=0, index=1, boundOpened=false), rowCount=LimitValue(value=10, index=2, boundOpened=false)), subQueryStatement=null, subQueryStatements=[], subQueryConditions=[]) 2019-03-21 14:57:16.416 INFO 70268 --- [nio-8090-exec-1] ShardingSphere-SQL : Actual SQL: test ::: select max(sid) as sid, max(sendTime) as sendTime, objectId, sum(0) as unreadCount from TEST where memberId = ? and status != -1 group by objectId order by sendTime desc limit ?, ? ::: [[123, 0, 2147483647]] ``` 1. Sharding log as ablove. 2. Are you sure the debug info does not help. Do you think rewriting limit count to **2147483647** is reseassonable in this case? 3. ok   4. There is nothing about my database. Sharding-jdbc rewrites the sql, it happens before the sql being sent to the database. 5. I am much clear what I want and what I get, which was represented in the issue report. 6. a. 10 rows; b. more than 10 rows This issue happened on my prod environment and leaded to some horrible consequences. I could 100% reproduce it. There is no special precondition. You can also reproduce it according to the **Steps to reproduce the behavior**. Just try it.
---------------------------------------------------------------- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: [email protected] With regards, Apache Git Services
