You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "x1ng-z (via GitHub)" <gi...@apache.org> on 2026/06/27 05:37:28 UTC

[I] SQLServer Pagination Merged Result Issue [shardingsphere]

x1ng-z opened a new issue, #38935:
URL: https://github.com/apache/shardingsphere/issues/38935

   ## Bug Report
   
   ### Which version of ShardingSphere did you use?
   5.5.3
   ### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
   ShardingSphere-JDBC
   ### Expected behavior
   At first, somebody post the same issue: [#30222](https://github.com/apache/shardingsphere/issues/30222),but noone fix it . the issue has been closed now.
   Summary: SQL Server OFFSET-FETCH pagination returns wrong row count after TopAndRowNumberDecoratorMergedResult merges results
   
   The question as follow:/
   when query througth pagination style. EX:   SELECT .... FROM ... WHERE ... ORDER BY XX DESC OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY. 
   as we known, shardingsphere will rewrite like these:
    SELECT .... FROM ... WHERE ... ORDER BY XX DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY
   and merged the results。let's see it:
   ```java
   / Constructor
   public TopAndRowNumberDecoratorMergedResult(MergedResult mergedResult, 
                                                PaginationContext paginationContext) {
       // ...
       skipAll = skipOffset();  // Skips 10 rows
   }
   
   private boolean skipOffset() throws SQLException {
       for (int i = 0; i < paginationContext.getActualOffset(); i++) {
           if (!getMergedResult().next()) {
               return true;
           }
           rowNumber++;  //  BUG: rowNumber becomes 10 after skip
       }
       return false;
   }
   
   @Override
   public boolean next() throws SQLException {
       // ...
       return ++rowNumber <= paginationContext.getActualRowCount().get() 
              && getMergedResult().next();
       // BUG: rowNumber=10, actualRowCount=10
       //        10 <= 10 → true (returns 1 row)
       //        11 <= 10 → false (stops)
       // Only 1 row returned instead of 10!
   }
   ```
   and check Mysql pagination merge behavior:
   ```java
   // LimitDecoratorMergedResult.java (MySQL)
   private boolean skipOffset() throws SQLException {
       for (int i = 0; i < paginationContext.getActualOffset(); i++) {
           if (!getMergedResult().next()) {
               return true;
           }
          rowNumber = 0; //  No rowNumber++ here
       }
       return false;
   }
   
   @Override
   public boolean next() throws SQLException {
       return ++rowNumber <= paginationContext.getActualRowCount().get() 
              && getMergedResult().next();
       //  rowNumber starts from 0, correctly returns 10 rows
   }
   ```
   so the minimal change as follow:
   ```java
     private boolean skipOffset() throws SQLException {
           long end = paginationContext.getActualOffset();
           for (long count = 0L; count < end; count++) {
               if (!getMergedResult().next()) {
                   return true;
               }
           }
           //rowNumber = end + 1L; //remove it
          rowNumber  = 1L;//add line
           return false;
       }
   ```
   
   


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


Re: [I] SQLServer Pagination Merged Result Issue [shardingsphere]

Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu closed issue #38935: SQLServer Pagination Merged Result Issue
URL: https://github.com/apache/shardingsphere/issues/38935


-- 
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] SQLServer Pagination Merged Result Issue [shardingsphere]

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

   Based on the above report, my thoughts are as follows
   
   1. Do not directly modify the counting logic of `TopAndRowNumberDecoratorMergedResult`, as this may affect the existing behavior of `TOP + ROW_NUMBER`. It is more appropriate to differentiate between `limit-style pagination` and `row-number-style pagination` in the SQL Server pagination decorator builder. 
   2. For pagination originating from `LimitValueSegment` such as `OFFSET FETCH`, use `LimitDecoratorMergedResult`; 
   3. for `TOP + ROW_NUMBER`, continue to retain the existing decorator. Also, add a multi-route merge regression test with `OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY`.
   
   If we follow this approach, the final changes will be minimal. If this direction looks good, I’d like to work on this and submit a PR.


-- 
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] SQLServer Pagination Merged Result Issue [shardingsphere]

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

   Hi @x1ng-z, thanks for reporting this.
   
   This looks like a bug in current SQL Server sharding pagination merge, not invalid usage. `OFFSET FETCH` is documented as supported after SQLServer 2012, but for multi-route SQLServer queries the merge path currently uses `TopAndRowNumberDecoratorMergedResult`, whose counter logic matches TOP + ROW_NUMBER upper-bound pagination and can return too few rows for normal `OFFSET ... FETCH NEXT ...` pagination when `OFFSET > 0`.
   
   I suggest keeping this issue open and labeling it as `type: bug`, `feature: sharding`, `in: kernel`, and `db: SQLServer`. Related stale issues such as #30222 and #20352 describe the same merge-counter symptom but were not fixed; #7032 / #7077 fixed an older parser/visitor problem and does not cover this merge path. A fix should distinguish SQL Server `OFFSET FETCH` semantics from TOP + ROW_NUMBER semantics and include regression tests for `OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY` across multiple routed results, while preserving the existing TOP + ROW_NUMBER behavior.
   
   The reply above is based on the analysis below; the detailed reasoning is kept here for reference and follow-up contributors.
   
   ### Problem Understanding
   
   - **Issue:** #38935 reports ShardingSphere-JDBC 5.5.3 with SQL Server pagination returning the wrong row count after `TopAndRowNumberDecoratorMergedResult` merges results for SQL like `ORDER BY ... OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY`. Source: https://github.com/apache/shardingsphere/issues/38935 (`OBS-1`)
   - **Topology:** JDBC; governance mode and registry/config center are not provided and do not change this merge-path classification. (`OBS-1`)
   - **Observed Evidence:** `OBS-1`, `OBS-2`, `OBS-3`, `OBS-4`, `OBS-5`, `OBS-6`, `OBS-7`, `OBS-8`
   
   ### Root Cause
   
   - **Observation:** The official sharding limitation documentation says SQL Server supports TOP + ROW_NUMBER pagination and also supports `OFFSET FETCH` after SQLServer 2012. Source: https://shardingsphere.apache.org/document/5.5.3/en/features/sharding/limitation/ and `docs/document/content/features/sharding/limitation.en.md:69`-`79` (`OBS-2`)
   - **Observation:** The parser test model for SQL Server `OFFSET FETCH` is a normal `<limit>` with `<offset>` and `<row-count>`, for example `SELECT * FROM t_order ORDER BY order_id OFFSET 0 ROW FETCH NEXT ? ROWS ONLY`. Sources: `test/it/parser/src/main/resources/sql/supported/dml/select-pagination.xml:37`, `test/it/parser/src/main/resources/case/dml/select-pagination.xml:1995`-`2008` (`OBS-3`)
   - **Observation:** `PaginationContextEngine` gives `LimitSegment` priority and creates a `PaginationContext` from the limit offset and row count. Source: `infra/binder/core/src/main/java/org/apache/shardingsphere/infra/binder/context/segment/select/pagination/engine/PaginationContextEngine.java:52`-`57` (`OBS-4`)
   - **Observation:** For multiple routed query results, `ShardingDQLResultMerger` decorates the merged result through the database-specific `PaginationDecoratorMergedResultBuilder`; the SQLServer builder returns `TopAndRowNumberDecoratorMergedResult`. Sources: `features/sharding/core/src/main/java/org/apache/shardingsphere/sharding/merge/dql/ShardingDQLResultMerger.java:129`-`135`, `features/sharding/core/src/main/java/org/apache/shardingsphere/sharding/merge/dql/pagination/builder/dialect/SQLServerPaginationDecoratorMergedResultBuilder.java:33`-`35` (`OBS-5`)
   - **Observation:** `TopAndRowNumberDecoratorMergedResult.skipOffset()` sets `rowNumber = end + 1L`, and `next()` checks `rowNumber++ <= paginationContext.getActualRowCount().get()` before reading the next merged row. Source: `features/sharding/core/src/main/java/org/apache/shardingsphere/sharding/merge/dql/pagination/TopAndRowNumberDecoratorMergedResult.java:43`-`62` (`OBS-6`)
   - **Observation:** The limit-style decorator used by MySQL/PostgreSQL/openGauss resets its returned-row counter to `0` after skipping the offset, then compares returned rows with `actualRowCount`. Source: `features/sharding/core/src/main/java/org/apache/shardingsphere/sharding/merge/dql/pagination/LimitDecoratorMergedResult.java:43`-`62` (`OBS-7`)
   - **Observation:** Current SQLServer merge tests cover TOP + ROW_NUMBER-like offset segments, not a SQL Server `LimitSegment` offset/fetch case where row count means “fetch next N rows.” Source: `features/sharding/core/src/test/java/org/apache/shardingsphere/sharding/merge/dql/pagination/TopAndRowNumberDecoratorMergedResultTest.java:107`-`143` (`OBS-8`)
   - **Inference:** For SQL Server `OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY`, `actualOffset` is `10` and `actualRowCount` is `10`; after skipping, `TopAndRowNumberDecoratorMergedResult` starts at `rowNumber = 11`, so the `rowNumber <= actualRowCount` check can stop before returning the expected 10 rows. This is a mismatch between documented/parsed `OFFSET FETCH` semantics and the SQLServer merge decorator. (`INF-1`, based on `OBS-2` through `OBS-8`)
   - **Confidence:** High
   
   ### Problem Analysis
   
   - **Issue Type:** Bug
   - **Evidence:** SQL Server `OFFSET FETCH` is supported and parsed as limit-style pagination (`OBS-2`, `OBS-3`), but the SQLServer sharding merge decorator applies TOP + ROW_NUMBER counter semantics to that pagination context (`OBS-5`, `OBS-6`), unlike the normal limit decorator (`OBS-7`).
   - **Duplicate / Prior Fix Check:** #30222 and #20352 describe the same merge-counter symptom, but both were closed as stale/question without a fixing PR. #38936 is a duplicate of this issue, not an earlier fix. #7032 was fixed by #7077, but that PR changed SQL Server parser/visitor handling, not `TopAndRowNumberDecoratorMergedResult`, so it does not fix this merge-path root cause.
   - **Compatibility Checklist:** Behavior affected; config unaffected; API-SPI unaffected if the fix stays inside merge decoration; SQL semantics affected only to restore documented SQL Server `OFFSET FETCH` behavior.
   
   ### Code-Level Design Suggestions
   
   - **Affected Modules:** `features/sharding/core` for merge decoration; `infra/binder/core` and parser tests are supporting evidence for the pagination context shape.
   - **Key Classes:** `TopAndRowNumberDecoratorMergedResult`, `SQLServerPaginationDecoratorMergedResultBuilder`, `PaginationContext`, and existing SQL Server pagination tests.
   - **Required Test Scope:** Add a regression case for SQL Server `OFFSET FETCH` represented by `LimitSegment`, for example offset `10` and row count `10`, across multiple routed results. Keep existing TOP + ROW_NUMBER tests so a fix does not regress row-number upper-bound pagination.
   - **Rollback Hint:** Keep the fix scoped to SQLServer pagination merge behavior so it can be reverted without changing parser, SQL rewrite, or public configuration contracts.
   
   ### Problem Conclusion
   
   - **Evidence Confidence:** High
   - **Severity:** S2
   - **Impact Scope:** SQL Server sharding pagination with `OFFSET FETCH` and multiple routed results, especially pages where `OFFSET > 0`.
   - **Topology:** JDBC + Standalone/unknown governance; registry/config center N/A or not provided.
   - **Issue Type:** Bug
   - **Recommended Labels:** `type: bug`, `feature: sharding`, `in: kernel`, `db: SQLServer`
   - **Next Action:** Keep #38935 open as the active bug report; do not close it as duplicate of stale issues #30222 or #20352. A contributor can submit a PR with the merge fix and focused regression tests.
   - **Compatibility:** Behavior / SQL
   - **Regression Scope:** SQLServer sharding pagination merge; existing TOP + ROW_NUMBER behavior; parser-backed `OFFSET FETCH` limit semantics.


-- 
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] SQLServer Pagination Merged Result Issue [shardingsphere]

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

   @Zhengcy05  I think your method is better, and I verify it works well.


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