[GitHub] lalicw opened a new issue #1896: String range sharding is not supported!!!! not a duplicated issue, it's a bug.

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

lalicw opened a new issue #1896: String range sharding is not supported!!!! not a duplicated issue, it's a bug.
URL: https://github.com/apache/incubator-shardingsphere/issues/1896
 
 
   ## Bug Report
   SQL like "select * from t_order where create_time between '2018-01-01 00:00:00' and '2018-01-02 00:00:00'" is not supported, we use create_time for RangeShardingAlgorithm 
   and we always get the wrong RangeShardingValue ( we expected Range ['2018-01-01 00:00:00', '2018-01-02 00:00:00'] bu we received Range [-1, -1] ), the column type of create_time is datetime.
   
   in the OrConditionFilter.java line 206, 
   
   ```java
   private com.google.common.base.Optional<SQLExpression> buildExpression(final SelectStatement selectStatement, final ExpressionSegment expressionSegment, final String sql,
                                                                              final ShardingRule shardingRule, final ShardingTableMetaData shardingTableMetaData) {
           if (!(expressionSegment instanceof CommonExpressionSegment)) {
               new ExpressionFiller().fill(expressionSegment, selectStatement, sql, shardingRule, shardingTableMetaData);
               return com.google.common.base.Optional.absent();
           }
           CommonExpressionSegment commonExpressionSegment = (CommonExpressionSegment) expressionSegment;
           if (-1 < commonExpressionSegment.getIndex()) {
               return com.google.common.base.Optional.<SQLExpression>of(new SQLPlaceholderExpression(commonExpressionSegment.getIndex()));
           }
          // it will always return SQLNumberExpression because there is a default value -1 for commonExpressionSegment.getValue()
           if (null != commonExpressionSegment.getValue()) {
               return com.google.common.base.Optional.<SQLExpression>of(new SQLNumberExpression(commonExpressionSegment.getValue()));
           }
           String expression = sql.substring(commonExpressionSegment.getStartPosition(), commonExpressionSegment.getEndPosition() + 1);
           return Optional.<SQLExpression>of(new SQLTextExpression(expression));
       }
   ```
   
   
   
   but the value field in CommonExpressionSegment has a default value -1. so it will be always consider as SQLNumberExpression.
   
   after i remove the default value -1 for field value in CommonExpressionSegment.java, it's ok.
   ```java
   public final class CommonExpressionSegment extends ExpressionWithAliasSegment
   {
   
       private final int startPosition;
   
       private final int endPosition;
   
       private int index = -1;
   
       //remove the default value -1
       private Number value;
   }
   ```
   but the range value keep the single quote for the string like this:
   ['2018-01-01 00:00:00', '2018-01-01 23:59:59']  
   lowerEndpoint: '2018-01-01 00:00:00' upperEndpoint:  '2018-01-01 23:59:59'
   and what we expected is like this:
   [2018-01-01 00:00:00, 2018-01-01 23:59:59]
   lowerEndpoint: 2018-01-01 00:00:00 upperEndpoint:  2018-01-01 23:59:59
   
   the source code for RangeShardingAlgorithm is like this:
   ```java
   public Collection<String> doSharding(Collection<String> availableTargetNames,  RangeShardingValue<String> value)
   ```
   
   ### Which version of ShardingSphere did you use?
   3.1.0
   
   ### Which project did you use? Sharding-JDBC or Sharding-Proxy?
   Sharding-JDBC
   
   ### Expected behavior
   supported
   
   ### Actual behavior
   not supported
   
   ### Reason analyze (If you can)
   as mentioned above
   
   ### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
   as mentioned above
   
   ### Example codes for reproduce this issue (such as a github link).
   as mentioned above

----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on 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