[GitHub] [shardingsphere] H-Jason opened a new issue, #23014: SelectStatementContext#isSameGroupByAndOrderByItems has bug

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

H-Jason opened a new issue, #23014:
URL: https://github.com/apache/shardingsphere/issues/23014

   https://github.com/apache/shardingsphere/blob/764c13f2f098d3f13911e31aa93a61f12d49a332/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/statement/dml/SelectStatementContext.java#L297
   
   ### Bug Report
   the method SelectStatementContext#isSameGroupByAndOrderByItems is not right;  OrderByItem in SelectStatementContext#groupByContext is default ASC，But OrderByItem in SelectStatementContext#orderByContext  couild be ASC or DESC;So when my sql is desc, it is always return false;
   
   ### Which version of ShardingSphere did you use?
   5.1.2
   
   ### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
   ShardingSphere-JDBC
   
   ### Expected behavior
   my sql is :
   select column1,column2,count(0) as statisticsnum from my_table a  group by column1,column2 order by column1 desc,column2 desc  limit 0,1000
   It should be rewrite to :
   select column1,column2,count(0) as statisticsnum from my_table_one group by column1,column2 order by column1 desc,column2 desc  limit 0,1000；
   select column1,column2,count(0) as statisticsnum from my_table_two group by column1,column2 order by column1 desc,column2 desc  limit 0,1000;
   
   ### Actual behavior
   It was  rewrited to :
   select column1,column2,count(0) as statisticsnum from my_table_one group by column1,column2 order by column1 desc,column2 desc  limit 0,2147483647；
   select column1,column2,count(0) as statisticsnum from my_table_two group by column1,column2 order by column1 desc,column2 desc  limit 0,2147483647;
   
   ### Reason analyze (If you can)
   
   when  PaginationContext#getRevisedRowCount  rewrite the limit part of sql，It use SelectStatementContext#isSameGroupByAndOrderByItems to judge if need to get max row count;but SelectStatementContext#isSameGroupByAndOrderByItems is always return false when orderByContext is desc;
   
   I think SelectStatementContext#isSameGroupByAndOrderByItems should be return true when groupByContext.getItems().size() == groupByContext.getItems() where orderByContext.getItems() is List<ColumnOrderByItemSegment>;
   
   


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

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] strongduanmu commented on issue #23014: SelectStatementContext#isSameGroupByAndOrderByItems has bug

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

strongduanmu commented on issue #23014:
URL: https://github.com/apache/shardingsphere/issues/23014#issuecomment-1361037354

   Hi @H-Jason, thank you for your feedback. Can you provide your configuration and table init sql to help us reproduce this exception?


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

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] H-Jason commented on issue #23014: SelectStatementContext#isSameGroupByAndOrderByItems has bug

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

H-Jason commented on issue #23014:
URL: https://github.com/apache/shardingsphere/issues/23014#issuecomment-1361188545

   > Hi @H-Jason, thank you for your feedback. Can you provide your configuration and table init sql to help us reproduce this exception?
   i just shard two table in one database;you can reproduct in any table ;
   `
   public class ShardByTableOperator {
       public ShardingRuleConfiguration buildShardConfig() {
           String shardField = "identifier";
           ShardingTableRuleConfiguration mainTableRuleConfig = new ShardingTableRuleConfiguration("MY_TABLE",
               "DBNAME.MY_TABLE{[0, 1]}");
           List<ShardingTableRuleConfiguration> tableRules = new ArrayList<>();
           tableRules.add(mainTableRuleConfig);
           ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
           shardingRuleConfig.getTables().addAll(tableRules);
           shardingRuleConfig.setDefaultTableShardingStrategy(
               new StandardShardingStrategyConfiguration(shardField, "my_algorithm"));
           shardingRuleConfig.getShardingAlgorithms()
               .put("my_algorithm", new ShardingSphereAlgorithmConfiguration("my_algorithm", new Properties()));
           return shardingRuleConfig;
       }
   }
   
   public class PreciseModuloShardingTableAlgorithm implements StandardShardingAlgorithm<String> {
   
       private Properties props;
   
       @Override
       public String doSharding(final Collection<String> tableNames, final PreciseShardingValue<String> shardingValue) {
           for (String each : tableNames) {
               int a = (shardingValue.getValue().hashCode() & Integer.MAX_VALUE) % 2;
               if (each.endsWith(String.valueOf(a))) {
                   return each;
               }
           }
           throw new UnsupportedOperationException();
       }
   
      @Override
       public String getType() {
           return "my_algorithm";
       }
   }`
   


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

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org