SelectStatementContext#isSameGroupByAndOrderByItems has bug

[H-Jason]

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

[strongduanmu]

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

[H-Jason]

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 mysql 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";
    }
}

[terrymanu]

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:

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