[GitHub] [incubator-shardingsphere] KomachiSion edited a comment on issue #2687: "select code from t group by code" got duplicate records

[GitBox <gi...@apache.org>]

KomachiSion edited a comment 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, because you **set the COLLATE='utf8mb4_unicode_ci'**, so 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:
users@infra.apache.org


With regards,
Apache Git Services