[GitHub] [incubator-shardingsphere] AlbertTao opened a new issue #2193: Table doesn't exist when sharding value in different datasource with in expression

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

AlbertTao opened a new issue #2193: Table doesn't exist when sharding value in different datasource with in expression
URL: https://github.com/apache/incubator-shardingsphere/issues/2193
 
 
   ## Table doesn't exist when sharding value in different data source with in expression
   
   ### Which version of ShardingSphere did you use?
   4.0.0-RC1
   ### Which project did you use? Sharding-JDBC or Sharding-Proxy?
   Sharding-JDBC
   ### Expected behavior
   21:31:45.941 [main] INFO  ShardingSphere-SQL - Actual SQL: ds_0 ::: select * from ts_order_0001 where id = 1 or id = 3
   21:31:45.941 [main] INFO  ShardingSphere-SQL - Actual SQL: ds_1 ::: select * from ts_order_0003 where id = 1 or id = 3
   ### Actual behavior
   21:08:30.000 [main] INFO  ShardingSphere-SQL - Actual SQL: ds_0 ::: select * from ts_order_0001 where id in (1, 3)
   21:08:30.000 [main] INFO  ShardingSphere-SQL - Actual SQL: ds_0 ::: select * from ts_order_0003 where id in (1, 3)
   21:08:30.000 [main] INFO  ShardingSphere-SQL - Actual SQL: ds_1 ::: select * from ts_order_0001 where id in (1, 3)
   21:08:30.000 [main] INFO  ShardingSphere-SQL - Actual SQL: ds_1 ::: select * from ts_order_0003 where id in (1, 3)
   Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'test_d_0.ts_order_0003' doesn't exist
   ### Reason analyze (If you can)
   
   ### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
   
   dependency
   
   ```xml
   	  <dependency>
   		  <groupId>com.alibaba</groupId>
   		  <artifactId>druid</artifactId>
   		  <version>1.0.9</version>
   	  </dependency>
   
   	  <dependency>
   		  <groupId>org.apache.shardingsphere</groupId>
   		  <artifactId>sharding-jdbc-core</artifactId>
   		  <version>4.0.0-RC1</version>
   	  </dependency>
   ```
   
   
   
   sql
   
   ```sql
   create database test_d_0;
   create database test_d_1;
   CREATE TABLE test_d_0.`ts_order_0000` (`id` BIGINT NOT NULL AUTO_INCREMENT,PRIMARY KEY (`id`));
   CREATE TABLE test_d_0.`ts_order_0001` (`id` BIGINT NOT NULL AUTO_INCREMENT,PRIMARY KEY (`id`));
   CREATE TABLE test_d_1.`ts_order_0002` (`id` BIGINT NOT NULL AUTO_INCREMENT,PRIMARY KEY (`id`));
   CREATE TABLE test_d_1.`ts_order_0003` (`id` BIGINT NOT NULL AUTO_INCREMENT,PRIMARY KEY (`id`));
   ```
   
   Java Code
   
   ```java
           BufferedReader reader = new BufferedReader(new InputStreamReader(ShardingDataSourceTest.class
                           .getResourceAsStream("/sharding-config-example-1.yaml"), "utf-8"));
           StringBuffer sb = new StringBuffer();
           CharBuffer charBuffer = CharBuffer.allocate(32);
           for (int count = reader.read(charBuffer); count > 0; count = reader.read(charBuffer)) {
               sb.append(charBuffer.flip());
           }
       
           DataSource dataSource = YamlShardingDataSourceFactory.createDataSource(sb.toString().getBytes("utf-8"));
           Connection connection = dataSource.getConnection();
           Statement st = connection.createStatement();
           ResultSet rs = st.executeQuery("select * from ts_order where id in (1, 3)");
   ```
   
   
   
   sharding rule
   sharding-config-example-1.yaml
   
   ```yaml
   dataSources: 
     ds_0: !!com.alibaba.druid.pool.DruidDataSource
       driverClassName: com.mysql.jdbc.Driver
       url: jdbc:mysql://localhost:3306/test_d_0
       username: root
       password: root135
     ds_1: !!com.alibaba.druid.pool.DruidDataSource
       driverClassName: com.mysql.jdbc.Driver
       url: jdbc:mysql://localhost:3306/test_d_1
       username: root
       password: root135
   
   shardingRule:
     tables:
       ts_order:
         actualDataNodes: ds_0.ts_order_0000,ds_0.ts_order_0001,ds_1.ts_order_0002,ds_1.ts_order_0003
         databaseStrategy:
           inline:
             shardingColumn: id
             algorithmExpression: ds_${new BigDecimal(id).abs().divideAndRemainder(4)[1].longValue().intdiv(2)}
         tableStrategy:
           inline:
             shardingColumn: id
             algorithmExpression: ts_order_${String.format("%04d",new BigDecimal(id).abs().divideAndRemainder(4)[1].longValue())}
   
   props:
     sql.show: true
   ```
   
   ### Example codes for reproduce this issue (such as a github link).
   

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