[GitHub] [incubator-shardingsphere] kimmking opened a new issue #4573: Multi-values insertion bug with key-generator in sharding databases/tables

[GitBox]

kimmking opened a new issue #4573: Multi-values insertion bug with 
key-generator in sharding databases/tables
URL: https://github.com/apache/incubator-shardingsphere/issues/4573
 
 
   
   ## Multi-values insertion bug with key-generator in sharding databases/tables
   
   We can reproduce the bug in a rule: 
   > shard db by `id%2` : ds_0,ds_1
   > shard table by `id%3` : user0,user1,user2
   
   ### Case 1, literal multi-values insertion 
   
   Execute：
   `insert into user(`name`,`age`) values 
('name-11',11),('name-12',12),('name-13',13),('name-14',14)`
   
   Actual SQL：
   ```
    ..ds_1 ::: insert into user1(`name`,`age`, id) values ('name-11', 11, 
441401574401507332)
    ..ds_0 ::: insert into user2(`name`,`age`, id) values ('name-12', 12, 
441401574401507331)
    ..ds_1 ::: insert into user0(`name`,`age`, id) values ('name-13', 13, 
441401574401507330)
    ..ds_0 ::: insert into user1(`name`,`age`, id) values ('name-14', 14, 
441401574401507329)
   ```
   
   This are two issues：
   1. ids in descending order
   2. wrong sharding tables, lead to that those data can't be selected
   
   The first row, id=441401574401507332，id%3=>1，so shard to table `user1`，it's 
right。
   The second row, id=441401574401507331，id%3=>0，so should shard to table 
`user0`，but shard to table `user2`, it's wrong。
   The second row, id%3=>2，so should shard to table `user2`，but shard to table 
`user0`, it's wrong, too。
   The last row, id%3=>1，so shard to table `user1`，it's right。
   
   We can find if make ids reverse, remainders are 1,2,0,1，just match the 
sharding tables number.
   
   We learn more into shardingsphere sourcecodes，it generator 4 keys and 
binding them to sharding tables at first, like the ascending order. And then 
binding keys and literal columns fields(name, age)，it reverse keys to 
descending order by `List.descendingIterator`. Finally, each key orders can't 
match its own row.
   
   The related codes here：
   ```
   
org.apache.shardingsphere.sharding.rewrite.token.generator.impl.keygen.GeneratedKeyInsertValuesTokenGenerator：generateSQLToken()
   Iterator<Comparable<?>> generatedValues = 
generatedKey.getGeneratedValues().descendingIterator();
   ```
   
   ### Case 2, multi-values insertion with placeholders
   
   SQL：
   `insert into user(`name`,`age`) values (?,?),(?,?),(?,?),(?,?)`
   
   Java codes：
   ```
   try(PreparedStatement preparedStatement = con.prepareStatement(sql)){
         for (int i = 0; i < 4; i++){
                preparedStatement.setString(i * 2 + 1,"name-" + i);
                preparedStatement.setInt(i * 2 + 2,i);
         }
         preparedStatement.execute();
   }
   ```
   
   Actual SQL：
   ```
   ..ds_0 ::: insert into user1(`name`,`age`, id) values (?, ?, ?) ::: [name-0, 
0, 441306093067436035] 
   ..ds_1 ::: insert into user2(`name`,`age`, id) values (?, ?, ?) ::: [name-1, 
1, 441306093067436034] 
   ..ds_0 ::: insert into user0(`name`,`age`, id) values (?, ?, ?) ::: [name-2, 
2, 441306093067436033] 
   ..ds_1 ::: insert into user1(`name`,`age`, id) values (?, ?, ?) ::: [name-3, 
3, 441306093067436032]
   ```
   
   The same problem，but codes is in different class：
   ```
   
org.apache.shardingsphere.sharding.rewrite.parameter.impl.ShardingGeneratedKeyInsertValueParameterRewriter：rewrite()
   Iterator<Comparable<?>> generatedValues = 
shardingRouteContext.getGeneratedKey().get().getGeneratedValues().descendingIterator();
   ```
   
   ### fix
   We will fix it by replace `descendingIterator` to `iterator`. 
   

----------------------------------------------------------------
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:
us...@infra.apache.org


With regards,
Apache Git Services