peilinqian opened a new issue, #22480: URL: https://github.com/apache/shardingsphere/issues/22480
### Which version of ShardingSphere did you use? ShardingSphere-5.2.2-SNAPSHOT Commit ID: dirty-753c0cee8ee6fd3db00536da55b64bc5198a3758 Commit Message: Optimize sqlFederationExecutor init logic when sqlFederationType modify dynamically (https://github.com/apache/shardingsphere/pull/22209) Branch: https://github.com/apache/shardingsphere/commit/753c0cee8ee6fd3db00536da55b64bc5198a3758 Build time: 2022-11-19T10:18:41+0800 ### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy? ShardingSphere-Proxy ### Expected behavior In the federation scenario, use ‘order by’ when creating a view, and use the ‘limit 10,5 ’clause when select the view, and the order of the result data is correct ### Actual behavior In the federation scenario, use ‘order by’ when creating a view, and use the ‘limit 10,5 ’clause when select the view, and the order of the result data is correct ### Reason analyze (If you can) ### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc. ``` --create table drop table if exists t_merchant; --single table drop table if exists t_order; --sharding db tb 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); create table t_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); -- t_order insert into t_order values(1000, 10, 'init', 1, 'test', '2017-07-08'); insert into t_order values(1001, 10, 'init', 2, 'Test', '2017-07-08'); insert into t_order values(2000, 20, 'init', 3, 'test', '2017-08-08'); insert into t_order values(2001, 20, 'init', 4, 'Test', '2017-08-08'); insert into t_order values(1100, 11, 'init', 5, 'TEst', '2017-08-08'); insert into t_order values(1101, 11, 'init', 6, 'test', '2017-08-08'); insert into t_order values(2100, 21, 'finish', 7, 'tEST', '2017-08-08'); insert into t_order values(2101, 21, 'finish', 8, 'test', '2017-08-08'); insert into t_order values(1200, 12, 'finish', 9, 'finish', '2017-08-08'); insert into t_order values(1201, 12, 'finish', 10, 'TEST22', '2017-08-18'); insert into t_order values(1902, 19, 'init', 17, 'test11', '2017-08-18'); insert into t_order values(1903, 19, 'init', 18, 'test12', '2017-08-18'); insert into t_order values(2902, 29, 'init', 19, 'test', '2017-08-18'); insert into t_order values(2903, 29, 'init', 20, 'test', '2017-08-18'); -- t_merchant 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'); --create view create view left_join_view as select o.order_id, o.user_id, m.merchant_name from t_new_order o left join t_merchant m on o.merchant_id = m.merchant_id where o.user_id > 20 order by 1,2,3; --select view select * from left_join_view; --select limit 4,5 select * from left_join_view limit 4,5; ``` result ``` test_db=> --select view test_db=> select * from left_join_view; order_id | user_id | merchant_name ----------+---------+--------------- 2100 | 21 | baidu 2101 | 21 | xiaomi 2200 | 22 | google 2201 | 22 | walmart 2300 | 23 | microsoft 2301 | 23 | dell 2400 | 24 | hp 2401 | 24 | tesla 2500 | 25 | huawei 2501 | 25 | alibaba 2600 | 26 | baidu 2601 | 26 | xiaomi 2700 | 27 | google 2701 | 27 | walmart 2800 | 28 | microsoft 2801 | 28 | dell 2900 | 29 | hp 2901 | 29 | tesla 2902 | 29 | hp 2903 | 29 | tesla (20 rows) test_db=> --select limit 4,5 ，the order of result is wrong test_db=> select * from left_join_view limit 4,5; order_id | user_id | merchant_name ----------+---------+--------------- 2201 | 22 | walmart 2401 | 24 | tesla 2601 | 26 | xiaomi 2801 | 28 | dell 2100 | 21 | baidu (5 rows) ``` **expect result** ``` test=# --select limit 4,5 test=# select * from left_join_view limit 4,5; order_id | user_id | merchant_name ----------+---------+--------------- 2300 | 23 | microsoft 2301 | 23 | dell 2400 | 24 | hp 2401 | 24 | tesla 2500 | 25 | huawei (5 rows) ``` ### Example codes for reproduce this issue (such as a github link). -- 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-unsubscr...@shardingsphere.apache.org.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
