You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2022/12/21 08:42:45 UTC

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

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

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


Re: [I] SelectStatementContext#isSameGroupByAndOrderByItems has bug [shardingsphere]

Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu closed issue #23014: SelectStatementContext#isSameGroupByAndOrderByItems has bug
URL: https://github.com/apache/shardingsphere/issues/23014


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


Re: [I] SelectStatementContext#isSameGroupByAndOrderByItems has bug [shardingsphere]

Posted by "Ayasaz (via GitHub)" <gi...@apache.org>.
Ayasaz commented on issue #23014:
URL: https://github.com/apache/shardingsphere/issues/23014#issuecomment-4738945400

   I've opened #38863 implementing the fix along the lines of @terrymanu's analysis: `isSameGroupByAndOrderByItems` now compares the group by / order by item index sequence only and ignores sort direction, so `GROUP BY col1, col2 ORDER BY col1 DESC, col2 DESC` no longer expands pagination to `LIMIT 0, 2147483647` or falls back to memory group merge. Direction stays honored by `GroupByRowComparator`. Affected-module tests (`infra/binder/core`, `infra/rewrite/core`, `features/sharding/core`) pass locally. Review and feedback welcome.
   


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


Re: [I] SelectStatementContext#isSameGroupByAndOrderByItems has bug [shardingsphere]

Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu commented on issue #23014:
URL: https://github.com/apache/shardingsphere/issues/23014#issuecomment-3636959686

   ##  Issue Understanding
   
     - In 5.1.2, a query like GROUP BY col1,col2 + ORDER BY col1 DESC,col2 DESC gets rewritten to LIMIT 0,2147483647, causing full fetch, because SelectStatementContext#isSameGroupByAndOrderByItems returns false.
     - By SQL semantics, GROUP BY has no direction; if ORDER BY uses the same column list in the same order, regardless of ASC/DESC, streaming merge should still be valid and the original LIMIT can be preserved.
   
   ##  Root Cause
   
     - The current check includes sort direction: groupByContext treats direction as default ASC, so a user-specified DESC is deemed “not consistent,” triggering LIMIT expansion.
     - This is overly conservative and makes “same columns, different directions” fall back to full fetch.
   
   ##  Analysis
   
     - Per the official doc “Merge Engine” (docs/document/content/reference/sharding/merge.en.md), streaming group merge relies on grouping keys being the sorting keys so rows of the same group are contiguous; direction differences do not break contiguity and should only be
       honored in the comparator.
     - Therefore, the consistency check should focus on column/expression sequences, not on sort direction; direction should be applied during merge comparison.
   
   ##  Conclusion & Fix Proposal
   
     - Conclusion: It’s a logic defect—sorting direction is used in the consistency check, causing false negatives and LIMIT expansion.
     - Fix idea: In isSameGroupByAndOrderByItems, compare only the column/expression sequences (including order of columns), ignoring sort direction; keep direction for the merge comparator. Add tests for GROUP BY with matching column lists but DESC/mixed order to ensure
       pagination is no longer expanded. Example pseudo-code:
   
   ```java
       // Compare columns/expressions only, ignore direction
       private boolean isSameGroupByAndOrderByItems() {
           return groupByContext.getItems().size() == orderByContext.getItems().size()
               && IntStream.range(0, groupByContext.getItems().size())
                   .allMatch(i -> isSameExpression(groupByContext.getItems().get(i), orderByContext.getItems().get(i)));
       }
   ```
   
     - We warmly invite community contributors to submit a PR implementing this change and adding the relevant tests; we’ll gladly assist with review.


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

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