[GitHub] [incubator-shardingsphere] cocoyadi opened a new issue #1979: Use PreciseShardingAlgorithm with IN throws exception that some tables could not be found in the database

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

cocoyadi opened a new issue #1979: Use PreciseShardingAlgorithm with IN  throws exception that some tables could not be found in the database
URL: https://github.com/apache/incubator-shardingsphere/issues/1979
 
 
   There are four databases in my project, each database has 250 tables ranges from 0 to 250, so the databases show like the below:
   database_1: table_000~ table_250
   database_2: table_251~ table_500
   ……
   
   And there is the sql need to be executed: 
   **select  *  from table where id in (?,?,?,?)**
   
   Then I configured the sharding algorithm follows the step below, but it always throws the exception just like "Table 'database_2.table_000' doesn't exist".
   
   - version: 3.0.0
   
   - ShardingDatabaseAlgorithm:
   
   ```
   public String doSharding(Collection<String> collection, PreciseShardingValue<Long> shardingValue) {
           long mod = (int) (shardingValue.getValue() % 1000);
           String databaseName = null;
           for (String each : collection) {
               if (mod < 250) {
                   databaseName = DATABASE + "_0";
                   if (databaseName.equals(each)) {
                       return each;
                   }
               } else if (mod < 500) {
                   databaseName = DATABASE + "_1";
                   if (databaseName.equals(each)) {
                       return each;
                   }
               } else if (mod < 750) {
                   databaseName = DATABASE + "_2";
                   if (databaseName.equals(each)) {
                       return each;
                   }
               } else {
                   databaseName = DATABASE + "_3";
                   if (databaseName.equals(each)) {
                       return each;
                   }
               }
           }
           throw new IllegalArgumentException();
       }
   ```
   
   - ShardingTablesAlgorithm:
   
   ``` 
   public String doSharding(Collection<String> collection, PreciseShardingValue<Long> shardingValue) {
           for (String each : collection) {
               long mod = (int)(shardingValue.getValue() % 1000);
               if(mod < 10){
                   return swichTableName(each)+"_00"+mod;
               }else if(mod < 100){
                   return swichTableName(each)+"_0"+mod;
               }else{
                   return swichTableName(each)+"_"+mod;
               }
           }
           throw new IllegalArgumentException();
       }
   ```
   
   After debug, I found it would shard every id to find suitable tables in each databases, so they can make the routing result like that :
   database_1.table_124
   database_2.table_124
   database_1.table_654
   databses_2.table_654
   
   It's impossible to create redundant tables in database, so how can I deal with it?
   Please give me some suggestion, thank you so much.
   

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