KomachiSion commented on issue #2687: "select code from t group by code" got duplicate records URL: https://github.com/apache/incubator-shardingsphere/issues/2687#issuecomment-510349673 The reason of this issue is that MySQL and Java are different in the ordering for strings. When SQL `select code from t group by code` execute, ShardingSphere will route and rewrite to following SQL ``` select code from t_0 group by code order by code // result 110358011282D4 110358011282E9 110358011282h8 110358011282I0 110358011282l1 110358011282O7 110358011282P0 110358011282T4 110358011282w6 select code from t_1 group by code order by code // result 11035801128253 110358011282f5 110358011282G1 110358011282H5 110358011282T4 110358011282U5 110358011282z1 ``` As the result show, in MySQL, the lowercase is same as uppercase while sorting. But in Java, all lowercase are larger than uppercase, for example, `110358011282f5` is large than `110358011282G1`. And in ShardingSphere, there is one PriorityQueue to get ResultSet according to the smallest value, which caused SS can't aggregate the same value. For SQL `select max(code) from t group by upper(code)`, the order item is `upper(code)` the order of PriorityQueue is correct, so the result is correct.
---------------------------------------------------------------- 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
