strongduanmu commented on issue #22826:
URL:
https://github.com/apache/shardingsphere/issues/22826#issuecomment-1352695739
I found that the initialized data was an empty string instead of null, which
would cause nulls first to fail, so I modified the SQL.
```sql
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, null, '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, null, '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, null, '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, null, '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');
```
Then I do some test with master branch.
```sql
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 remark
nulls first,1,2,3;
merchant_id | order_id | user_id | status | remark | creation_date |
country_id | merchant_name | business_code | telephone | creation_date
-------------+----------+---------+--------+--------+---------------+------------+---------------+---------------+-------------+---------------
13 | 1300 | 13 | finish | | 2017-08-18 |
1 | amazon | 01000013 | 01100000013 | 2017-08-08
17 | 1400 | 14 | init | | 2017-08-18 |
1 | johnson | 01000017 | 01100000017 | 2017-08-08
14 | 1301 | 13 | finish | TEST01 | 2017-08-18 |
1 | apple | 01000014 | 01100000014 | 2017-07-08
5 | 1100 | 11 | init | TESt | 2017-08-08 |
86 | lenovo | 86000005 | 86100000005 | 2017-08-08
9 | 1200 | 12 | finish | finish | 2017-08-08 |
86 | vivo | 86000009 | 86100000009 | 2017-11-08
6 | 1101 | 11 | init | test | 2017-08-08 |
86 | moutai | 86000006 | 86100000006 | 2017-12-08
18 | 1401 | 14 | init | test | 2017-08-18 |
1 | intel | 01000018 | 01100000018 | 2017-06-08
10 | 1201 | 12 | finish | test22 | 2017-08-18 |
86 | oppo | 86000010 | 86100000010 | 2017-08-08
(8 rows)
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 remark
nulls last,1,2,3;
merchant_id | order_id | user_id | status | remark | creation_date |
country_id | merchant_name | business_code | telephone | creation_date
-------------+----------+---------+--------+--------+---------------+------------+---------------+---------------+-------------+---------------
14 | 1301 | 13 | finish | TEST01 | 2017-08-18 |
1 | apple | 01000014 | 01100000014 | 2017-07-08
5 | 1100 | 11 | init | TESt | 2017-08-08 |
86 | lenovo | 86000005 | 86100000005 | 2017-08-08
9 | 1200 | 12 | finish | finish | 2017-08-08 |
86 | vivo | 86000009 | 86100000009 | 2017-11-08
6 | 1101 | 11 | init | test | 2017-08-08 |
86 | moutai | 86000006 | 86100000006 | 2017-12-08
18 | 1401 | 14 | init | test | 2017-08-18 |
1 | intel | 01000018 | 01100000018 | 2017-06-08
10 | 1201 | 12 | finish | test22 | 2017-08-18 |
86 | oppo | 86000010 | 86100000010 | 2017-08-08
13 | 1300 | 13 | finish | | 2017-08-18 |
1 | amazon | 01000013 | 01100000013 | 2017-08-08
17 | 1400 | 14 | init | | 2017-08-18 |
1 | johnson | 01000017 | 01100000017 | 2017-08-08
(8 rows)
sharding_db=> create view select_view as select
order_id,user_id,status,merchant_id,remark 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 remark nulls first,1,2,3;
CREATE VIEW
sharding_db=> select * from select_view;
order_id | user_id | status | merchant_id | remark
----------+---------+--------+-------------+--------
1300 | 13 | finish | 13 |
1400 | 14 | init | 17 |
1301 | 13 | finish | 14 | TEST01
1100 | 11 | init | 5 | TESt
1200 | 12 | finish | 9 | finish
1101 | 11 | init | 6 | test
1401 | 14 | init | 18 | test
1201 | 12 | finish | 10 | test22
(8 rows)
sharding_db=> drop view select_view;
DROP VIEW
sharding_db=> create view select_view as select
order_id,user_id,status,merchant_id,remark 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 remark nulls last,1,2,3;
CREATE VIEW
sharding_db=> select * from select_view;
order_id | user_id | status | merchant_id | remark
----------+---------+--------+-------------+--------
1301 | 13 | finish | 14 | TEST01
1100 | 11 | init | 5 | TESt
1200 | 12 | finish | 9 | finish
1101 | 11 | init | 6 | test
1401 | 14 | init | 18 | test
1201 | 12 | finish | 10 | test22
1300 | 13 | finish | 13 |
1400 | 14 | init | 17 |
(8 rows)
sharding_db=> drop view select_view;
DROP VIEW
```
--
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
