In the federation scenario,the select result data of fetch statement is incorrect.

[peilinqian]

Which version of ShardingSphere did you use?

we find java version: java8, full_version=1.8.0_282, full_path=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.282.b08-1.el7_9.x86_64/bin/java
ShardingSphere-5.2.2-SNAPSHOT
Commit ID: dirty-631fdf40f87223e176abe5c851a51b3287b4d6de
Commit Message: Fix wrong decide result when execute same sharding condition subquery with sql federation (#22754)
Branch: 631fdf4
Build time: 2022-12-12T10:48:40+0800

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

ShardingSphere-Proxy

Expected behavior

In the federation scenario,the select result data of fetch statement is correct.

Actual behavior

In the federation scenario,the select result data of fetch statement is wrong.

Reason analyze (If you can)

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

select * from t_new_order o inner join t_merchant m using(merchant_id) where o.user_id > 10 and o.user_id < 15 order by o.order_id, 7 fetch next 3 row only;
select * from t_new_order o inner join t_merchant m using(merchant_id) where o.user_id > 10 and o.user_id < 15 order by o.order_id, 7 fetch first 3 rows only;

the result of sharding

select * from t_new_order o inner join t_merchant m using(merchant_id) where o.user_id > 10 and o.user_id < 15 order by o.order_id, 7 fetch next 3 row only;
+-------------+----------+---------+--------+--------+-------------------------------+------------+---------------+---------------+-------------+-------------------------------+
| merchant_id | order_id | user_id | status | remark | creation_date                 | country_id | merchant_name | business_code | telephone   | creation_date                 |
+-------------+----------+---------+--------+--------+-------------------------------+------------+---------------+---------------+-------------+-------------------------------+
| 10          | 1201     | 12      | finish | test22 | 2017-08-18 00:00:00.000000000 | 86         | oppo          | 86000010      | 86100000010 | 2017-08-08 00:00:00.000000000 |
| 13          | 1300     | 13      | finish |        | 2017-08-18 00:00:00.000000000 | 1          | amazon        | 01000013      | 01100000013 | 2017-08-08 00:00:00.000000000 |
| 14          | 1301     | 13      | finish | TEST01 | 2017-08-18 00:00:00.000000000 | 1          | apple         | 01000014      | 01100000014 | 2017-07-08 00:00:00.000000000 |
| 17          | 1400     | 14      | init   |        | 2017-08-18 00:00:00.000000000 | 1          | johnson       | 01000017      | 01100000017 | 2017-08-08 00:00:00.000000000 |
| 18          | 1401     | 14      | init   | test   | 2017-08-18 00:00:00.000000000 | 1          | intel         | 01000018      | 01100000018 | 2017-06-08 00:00:00.000000000 |
+-------------+----------+---------+--------+--------+-------------------------------+------------+---------------+---------------+-------------+-------------------------------+
select * from t_new_order o inner join t_merchant m using(merchant_id) where o.user_id > 10 and o.user_id < 15 order by o.order_id, 7 fetch first 3 rows only;
+-------------+----------+---------+--------+--------+-------------------------------+------------+---------------+---------------+-------------+-------------------------------+
| merchant_id | order_id | user_id | status | remark | creation_date                 | country_id | merchant_name | business_code | telephone   | creation_date                 |
+-------------+----------+---------+--------+--------+-------------------------------+------------+---------------+---------------+-------------+-------------------------------+
| 10          | 1201     | 12      | finish | test22 | 2017-08-18 00:00:00.000000000 | 86         | oppo          | 86000010      | 86100000010 | 2017-08-08 00:00:00.000000000 |
| 13          | 1300     | 13      | finish |        | 2017-08-18 00:00:00.000000000 | 1          | amazon        | 01000013      | 01100000013 | 2017-08-08 00:00:00.000000000 |
| 14          | 1301     | 13      | finish | TEST01 | 2017-08-18 00:00:00.000000000 | 1          | apple         | 01000014      | 01100000014 | 2017-07-08 00:00:00.000000000 |
| 17          | 1400     | 14      | init   |        | 2017-08-18 00:00:00.000000000 | 1          | johnson       | 01000017      | 01100000017 | 2017-08-08 00:00:00.000000000 |
| 18          | 1401     | 14      | init   | test   | 2017-08-18 00:00:00.000000000 | 1          | intel         | 01000018      | 01100000018 | 2017-06-08 00:00:00.000000000 |
+-------------+----------+---------+--------+--------+-------------------------------+------------+---------------+---------------+-------------+-------------------------------+

the result of opengauss

select * from t_new_order o inner join t_merchant m using(merchant_id) where o.user_id > 10 and o.user_id < 15 order by o.order_id, 7 fetch next 3 row only;
+-------------+----------+---------+--------+--------+-------------------------------+------------+---------------+---------------+-------------+-------------------------------+
| merchant_id | order_id | user_id | status | remark | creation_date                 | country_id | merchant_name | business_code | telephone   | creation_date                 |
+-------------+----------+---------+--------+--------+-------------------------------+------------+---------------+---------------+-------------+-------------------------------+
| 5           | 1100     | 11      | init   | TESt   | 2017-08-08 00:00:00.000000000 | 86         | lenovo        | 86000005      | 86100000005 | 2017-08-08 00:00:00.000000000 |
| 6           | 1101     | 11      | init   | test   | 2017-08-08 00:00:00.000000000 | 86         | moutai        | 86000006      | 86100000006 | 2017-12-08 00:00:00.000000000 |
| 9           | 1200     | 12      | finish | finish | 2017-08-08 00:00:00.000000000 | 86         | vivo          | 86000009      | 86100000009 | 2017-11-08 00:00:00.000000000 |
+-------------+----------+---------+--------+--------+-------------------------------+------------+---------------+---------------+-------------+-------------------------------+
select * from t_new_order o inner join t_merchant m using(merchant_id) where o.user_id > 10 and o.user_id < 15 order by o.order_id, 7 fetch first 3 rows only;
+-------------+----------+---------+--------+--------+-------------------------------+------------+---------------+---------------+-------------+-------------------------------+
| merchant_id | order_id | user_id | status | remark | creation_date                 | country_id | merchant_name | business_code | telephone   | creation_date                 |
+-------------+----------+---------+--------+--------+-------------------------------+------------+---------------+---------------+-------------+-------------------------------+
| 5           | 1100     | 11      | init   | TESt   | 2017-08-08 00:00:00.000000000 | 86         | lenovo        | 86000005      | 86100000005 | 2017-08-08 00:00:00.000000000 |
| 6           | 1101     | 11      | init   | test   | 2017-08-08 00:00:00.000000000 | 86         | moutai        | 86000006      | 86100000006 | 2017-12-08 00:00:00.000000000 |
| 9           | 1200     | 12      | finish | finish | 2017-08-08 00:00:00.000000000 | 86         | vivo          | 86000009      | 86100000009 | 2017-11-08 00:00:00.000000000 |
+-------------+----------+---------+--------+--------+-------------------------------+------------+---------------+---------------+-------------+-------------------------------+

drop table if exists t_new_order;
drop table if exists t_merchant; 
create table t_new_order (order_id int primary key, user_id int not null, status varchar(50) not null, merchant_id int not null, remark varchar(50), creation_date date);
create table t_merchant (merchant_id int primary key, country_id int not null, merchant_name varchar(50) not null, business_code varchar(50) not null, telephone varchar(50) not null, creation_date date not null);

insert into t_new_order values(1000, 10, 'init', 1, 'test', '2017-07-08');
insert into t_new_order values(1001, 10, 'init', 2, 'test', '2017-07-08');
insert into t_new_order values(2000, 20, 'init', 3, 'test', '2017-08-08');
insert into t_new_order values(2001, 20, 'init', 4, 'Test', '2017-08-08');
insert into t_new_order values(1100, 11,  'init', 5, 'TESt', '2017-08-08');
insert into t_new_order values(1101, 11, 'init', 6, 'test', '2017-08-08');
insert into t_new_order values(2100, 21, 'finish', 7, 'test', '2017-08-08');
insert into t_new_order values(2101, 21, 'finish', 8, 'TEST', '2017-08-08');
insert into t_new_order values(1200, 12, 'finish', 9, 'finish', '2017-08-08');
insert into t_new_order values(1201, 12, 'finish', 10, 'test22', '2017-08-18');
insert into t_new_order values(2200, 22, 'finish', 11, 'test', '2017-08-18');
insert into t_new_order values(2201, 22, 'finish', 12, 'test', '2017-08-18');
insert into t_new_order values(1300, 13, 'finish', 13, '', '2017-08-18');
insert into t_new_order values(1301, 13, 'finish', 14, 'TEST01', '2017-08-18');
insert into t_new_order values(2300, 23, 'finish ', 15, 'test', '2017-08-18');
insert into t_new_order values(2301, 23, 'finish', 16, 'TESt16', '2017-08-18');
insert into t_new_order values(1400, 14, 'init', 17, '', '2017-08-18');
insert into t_new_order values(1401, 14, 'init', 18, 'test', '2017-08-18');
insert into t_new_order values(2400, 24, 'init', 19, 'test', '2017-08-18');
insert into t_new_order values(2401, 24, 'init', 20, 'test', '2017-08-18');
insert into t_new_order values(1500, 15, 'init', 1, '', '2017-08-28');
insert into t_new_order values(1501, 15, 'init', 2, 'test', '2017-08-28');
insert into t_new_order values(2500, 25, 'init', 3, 'test', '2017-08-28');
insert into t_new_order values(2501, 25, 'init', 4, 'test', '2017-08-28');
insert into t_new_order values(1600, 16, 'init', 5, 'test', '2017-08-28');
insert into t_new_order values(1601, 16, 'init', 6, '', '2017-08-28');
insert into t_new_order values(2600, 26, 'init', 7, 'test', '2017-08-28');
insert into t_new_order values(2601, 26, 'init', 8);
insert into t_new_order values(1700, 17, 'init', 9, 'test', '2017-08-28');
insert into t_new_order values(1701, 17, 'finish', 10, 'test', '2017-08-18');
insert into t_new_order values(2700, 27, 'finish', 11, 'test', '2017-08-18');
insert into t_new_order values(2701, 27, 'finish', 12, 'test', '2017-08-18');
insert into t_new_order values(1800, 18, 'finish', 13, 'test', '2017-08-18');
insert into t_new_order values(1801, 18, 'finish', 14);
insert into t_new_order values(2800, 28, 'finish', 15, 'test', '2017-08-18');
insert into t_new_order values(2801, 28, 'finish', 16, 'test', '2017-08-18');
insert into t_new_order values(1900, 19, 'init', 17, 'test', '2017-08-18');
insert into t_new_order values(1901, 19, 'init', 18, 'test', '2017-08-18');
insert into t_new_order values(2900, 29, 'init', 19, 'test', '2017-08-18');
insert into t_new_order values(2901, 29, 'init', 20, 'test', '2017-08-18');
insert into t_new_order values(1902, 19, 'init', 17, 'test11', '2017-08-18');
insert into t_new_order values(1903, 19, 'init', 18, 'test12', '2017-08-18');
insert into t_new_order values(2902, 29, 'init', 19, 'test', '2017-08-18');
insert into t_new_order values(2903, 29, 'init', 20, 'test', '2017-08-18');
insert into t_merchant values(1, 86, 'tencent', '86000001', '86100000001', '2017-08-08');
insert into t_merchant values(2, 86, 'haier', '86000002', '86100000002', '2017-08-08');
insert into t_merchant values(3, 86, 'huawei', '86000003', '86100000003', '2017-08-08');
insert into t_merchant values(4, 86, 'alibaba', '86000004', '86100000004', '2017-08-08');
insert into t_merchant values(5, 86, 'lenovo', '86000005', '86100000005', '2017-08-08');
insert into t_merchant values(6, 86, 'moutai', '86000006', '86100000006', '2017-12-08');
insert into t_merchant values(7, 86, 'baidu', '86000007', '86100000007', '2017-08-08');
insert into t_merchant values(8, 86, 'xiaomi', '86000008', '86100000008', '2017-08-08');
insert into t_merchant values(9, 86, 'vivo', '86000009', '86100000009', '2017-11-08');
insert into t_merchant values(10, 86, 'oppo', '86000010', '86100000010', '2017-08-08');
insert into t_merchant values(11, 1, 'google', '01000011', '01100000011', '2017-08-08');
insert into t_merchant values(12, 1, 'walmart', '01000012', '01100000012', '2017-08-18');
insert into t_merchant values(13, 1, 'amazon', '01000013', '01100000013', '2017-08-08');
insert into t_merchant values(14, 1, 'apple', '01000014', '01100000014', '2017-07-08');
insert into t_merchant values(15, 1, 'microsoft', '01000015', '01100000015', '2017-08-08');
insert into t_merchant values(16, 1, 'dell', '01000016', '01100000016', '2017-08-08');
insert into t_merchant values(17, 1, 'johnson', '01000017', '01100000017', '2017-08-08');
insert into t_merchant values(18, 1, 'intel', '01000018', '01100000018', '2017-06-08');
insert into t_merchant values(19, 1, 'hp', '01000019', '01100000019', '2017-08-08');
insert into t_merchant values(20, 1, 'tesla', '01000020', '01100000020', '2017-08-08');

Example codes for reproduce this issue (such as a github link).

schemaName: test_db
dataSources:
  ds_0:
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 260
    minPoolSize: 10
    password: Test@123
    url: jdbc:opengauss://90.90.44.171:14000/test_db?batchMode=on
    username: tpccuser
  ds_1:
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 260
    minPoolSize: 10
    password: Test@123
    url: jdbc:opengauss://90.90.44.171:15000/test_db?batchMode=on
    username: tpccuser
rules:
- !SHARDING
  tables:
    t_user:
      actualDataNodes: ds_0.t_user
    t_product:
      actualDataNodes: ds_0.t_product
    t_merchant:
      actualDataNodes: ds_1.t_merchant
    t_product_detail:
      actualDataNodes: ds_1.t_product_detail
    t_order:
      actualDataNodes: ds_${0..1}.t_order
      databaseStrategy:
        standard:
          shardingColumn: user_id
          shardingAlgorithmName: database_inline
    t_order_item:
      actualDataNodes: ds_${0..1}.t_order_item
      databaseStrategy:
        standard:
          shardingColumn: user_id
          shardingAlgorithmName: database_inline
    t_order_item1:
      actualDataNodes: ds_${0..1}.t_order_item1
      databaseStrategy:
        standard:
          shardingColumn: user_id
          shardingAlgorithmName: database_inline
    t_new_order:
      actualDataNodes: ds_${0..1}.t_new_order_${0..1}
      databaseStrategy:
        standard:
          shardingAlgorithmName: database_inline
          shardingColumn: user_id
      tableStrategy:
        standard:
          shardingColumn: order_id
          shardingAlgorithmName: table_inline
  bindingTables:
    - t_order,t_order_item
  broadcastTables:
    - t_product_category
    - t_country
  shardingAlgorithms:
    database_inline:
      type: INLINE
      props:
        algorithm-expression: ds_${user_id % 2}
        allow-range-query-with-inline-sharding: true
    table_inline:
      type: INLINE
      props:
        algorithm-expression: t_new_order_${order_id % 2}
        allow-range-query-with-inline-sharding: true
mode:
  type: Cluster
  repository:
    type: ZooKeeper
    props:
      namespace: governance_ds
      server-lists: 7.212.123.28:2181
      retryIntervalMilliseconds: 500
      timeToLiveSeconds: 60
      maxRetries: 3
      operationTimeoutMilliseconds: 500
authority:
  users:
    - user: root@%
      password: root
    - user: sharding
      password: sharding
  privilege:
    type: ALL_PERMITTED
rules:
- !TRANSACTION
  defaultType: XA
  providerType: Atomikos
props:
  sql-show: true

[strongduanmu]

@peilinqian Thank you for your feedback, I will investigate this problem.

[strongduanmu]

The latest result.

sharding_db=> select * from t_new_order o inner join t_merchant m using(merchant_id) where o.user_id > 10 and o.user_id < 15 order by o.order_id, 7 fetch first 3 rows only;
 merchant_id | order_id | user_id | status | remark | creation_date | country_id | merchant_name | business_code |  telephone  | creation_date
-------------+----------+---------+--------+--------+---------------+------------+---------------+---------------+-------------+---------------
           5 |     1100 |      11 | init   | TESt   | 2017-08-08    |         86 | lenovo        | 86000005      | 86100000005 | 2017-08-08
           6 |     1101 |      11 | init   | test   | 2017-08-08    |         86 | moutai        | 86000006      | 86100000006 | 2017-12-08
           9 |     1200 |      12 | finish | finish | 2017-08-08    |         86 | vivo          | 86000009      | 86100000009 | 2017-11-08
(3 rows)

sharding_db=>
sharding_db=> select * from t_new_order o inner join t_merchant m using(merchant_id) where o.user_id > 10 and o.user_id < 15 order by o.order_id, 7 fetch next 3 row only;
 merchant_id | order_id | user_id | status | remark | creation_date | country_id | merchant_name | business_code |  telephone  | creation_date
-------------+----------+---------+--------+--------+---------------+------------+---------------+---------------+-------------+---------------
           5 |     1100 |      11 | init   | TESt   | 2017-08-08    |         86 | lenovo        | 86000005      | 86100000005 | 2017-08-08
           6 |     1101 |      11 | init   | test   | 2017-08-08    |         86 | moutai        | 86000006      | 86100000006 | 2017-12-08
           9 |     1200 |      12 | finish | finish | 2017-08-08    |         86 | vivo          | 86000009      | 86100000009 | 2017-11-08
(3 rows)

[wsm12138]

we find java version: java17, full_version=17.0.2, full_path=/usr/local/openjdk-17/bin/java
OpenJDK 64-Bit Server VM warning: UseNUMA is not fully compatible with SHM/HugeTLBFS large pages, disabling adaptive resizing (-XX:-UseAdaptiveSizePolicy -XX:-UseAdaptiveNUMAChunkSizing)
OpenJDK 64-Bit Server VM warning: Failed to reserve and commit memory. req_addr: 0x0000000080000000 bytes: 2147483648 page size: 2097152 (errno = 12).
ShardingSphere-5.3.1-SNAPSHOT
Commit ID: fcb0e1e
Commit Message: Optimize PostgreSQL, openGauss select fetech statement optimize and add more integration test (#23195)
Branch: fcb0e1e
Build time: 2022-12-30T15:46:19+0800

set dist variable sql_federation_type='ADVANCED';

[peilinqian]

we find java version: java8, full_version=1.8.0_282, full_path=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.282.b08-1.el7_9.x86_64/bin/java
ShardingSphere-5.3.1-SNAPSHOT
Commit ID: dirty-fcb0e1ea635accbf34cf491ae3f5ad463a4cfbb9
Commit Message: Optimize PostgreSQL, openGauss select fetech statement optimize and add more integration test (#23195)
Branch: fcb0e1ea635accbf34cf491ae3f5ad463a4cfbb9
Build time: 2023-01-04T20:01:23+0800

test_db=> select * from t_new_order o inner join t_merchant m using(merchant_id) where o.user_id > 10 and o.user_id < 15 order by o.order_id, 7 fetch next 3 row only;
select * from t_new_order o inner join t_merchant m using(merchant_id) where o.user_id > 10 and o.user_id < 15 order by o.order_id, 7 fetch first 3 rows only; merchant_id | order_id | user_id | status | remark |     creation_date     | country_id | merchant_name | business_code |  telephone  |     creation_date
-------------+----------+---------+--------+--------+-----------------------+------------+---------------+---------------+-------------+-----------------------
           5 |     1100 |      11 | init   | TESt   | 2017-08-08 00:00:00.0 |         86 | lenovo        | 86000005      | 86100000005 | 2017-08-08 00:00:00.0
           6 |     1101 |      11 | init   | test   | 2017-08-08 00:00:00.0 |         86 | moutai        | 86000006      | 86100000006 | 2017-12-08 00:00:00.0
           9 |     1200 |      12 | finish | finish | 2017-08-08 00:00:00.0 |         86 | vivo          | 86000009      | 86100000009 | 2017-11-08 00:00:00.0
(3 rows)

test_db=> select * from t_new_order o inner join t_merchant m using(merchant_id) where o.user_id > 10 and o.user_id < 15 order by o.order_id, 7 fetch first 3 rows only;
 merchant_id | order_id | user_id | status | remark |     creation_date     | country_id | merchant_name | business_code |  telephone  |     creation_date
-------------+----------+---------+--------+--------+-----------------------+------------+---------------+---------------+-------------+-----------------------
           5 |     1100 |      11 | init   | TESt   | 2017-08-08 00:00:00.0 |         86 | lenovo        | 86000005      | 86100000005 | 2017-08-08 00:00:00.0
           6 |     1101 |      11 | init   | test   | 2017-08-08 00:00:00.0 |         86 | moutai        | 86000006      | 86100000006 | 2017-12-08 00:00:00.0
           9 |     1200 |      12 | finish | finish | 2017-08-08 00:00:00.0 |         86 | vivo          | 86000009      | 86100000009 | 2017-11-08 00:00:00.0
(3 rows)