You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2022/12/12 08:41:42 UTC
[GitHub] [shardingsphere] peilinqian opened a new issue, #22826: In the federation scenario，"select order by null first" or "create view order by null first",the result is incorrect;
peilinqian opened a new issue, #22826:
URL: https://github.com/apache/shardingsphere/issues/22826
### 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 (https://github.com/apache/shardingsphere/pull/22754)
Branch: https://github.com/apache/shardingsphere/commit/631fdf40f87223e176abe5c851a51b3287b4d6de
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，create view order by null ,the result is correct;
### Actual behavior
In the federation scenario，create view order by null ,the result is incorrect;
### 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 remark nulls first,1,2,3;
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;
select * from select_view;
drop view select_view;
```
**the result of sharding:**
```
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 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
-------------+----------+---------+--------+--------+-----------------------+------------+---------------+---------------+-------------+-----------------------
14 | 1301 | 13 | finish | TEST01 | 2017-08-18 00:00:00.0 | 1 | apple | 01000014 | 01100000014 | 2017-07-08 00:00:00.0
5 | 1100 | 11 | init | TESt | 2017-08-08 00:00:00.0 | 86 | lenovo | 86000005 | 86100000005 | 2017-08-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
6 | 1101 | 11 | init | test | 2017-08-08 00:00:00.0 | 86 | moutai | 86000006 | 86100000006 | 2017-12-08 00:00:00.0
18 | 1401 | 14 | init | test | 2017-08-18 00:00:00.0 | 1 | intel | 01000018 | 01100000018 | 2017-06-08 00:00:00.0
10 | 1201 | 12 | finish | test22 | 2017-08-18 00:00:00.0 | 86 | oppo | 86000010 | 86100000010 | 2017-08-08 00:00:00.0
13 | 1300 | 13 | finish | | 2017-08-18 00:00:00.0 | 1 | amazon | 01000013 | 01100000013 | 2017-08-08 00:00:00.0
17 | 1400 | 14 | init | | 2017-08-18 00:00:00.0 | 1 | johnson | 01000017 | 01100000017 | 2017-08-08 00:00:00.0
(8 rows)
test_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
test_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)
test_db=> drop view select_view;
DROP VIEW
```
**the result of opengauss**
```
og_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 00:00:00 | 1 | amazon | 01000013 | 01100000013 | 2017-08-08 00:00:00
17 | 1400 | 14 | init | | 2017-08-18 00:00:00 | 1 | johnson | 01000017 | 01100000017 | 2017-08-08 00:00:00
14 | 1301 | 13 | finish | TEST01 | 2017-08-18 00:00:00 | 1 | apple | 01000014 | 01100000014 | 2017-07-08 00:00:00
5 | 1100 | 11 | init | TESt | 2017-08-08 00:00:00 | 86 | lenovo | 86000005 | 86100000005 | 2017-08-08 00:00:00
9 | 1200 | 12 | finish | finish | 2017-08-08 00:00:00 | 86 | vivo | 86000009 | 86100000009 | 2017-11-08 00:00:00
6 | 1101 | 11 | init | test | 2017-08-08 00:00:00 | 86 | moutai | 86000006 | 86100000006 | 2017-12-08 00:00:00
18 | 1401 | 14 | init | test | 2017-08-18 00:00:00 | 1 | intel | 01000018 | 01100000018 | 2017-06-08 00:00:00
10 | 1201 | 12 | finish | test22 | 2017-08-18 00:00:00 | 86 | oppo | 86000010 | 86100000010 | 2017-08-08 00:00:00
(8 rows)
og_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
og_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)
og_db=# drop view select_view;
DROP VIEW
```
```
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
sql-federation-type: ADVANCED
```
--
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
[GitHub] [shardingsphere] strongduanmu commented on issue #22826: In the federation scenario，"select order by null first" or "create view order by null first",the result is incorrect;
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #22826:
URL: https://github.com/apache/shardingsphere/issues/22826#issuecomment-1350910592
When I test with master branch, the select result is as following:
```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)
```
--
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
[GitHub] [shardingsphere] strongduanmu commented on issue #22826: In the federation scenario，"select order by null first" or "create view order by null first",the result is incorrect;
Posted by GitBox <gi...@apache.org>.
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero closed issue #22826: In the federation scenario，"select order by null first" or "create view order by null first",the result is incorrect;
Posted by GitBox <gi...@apache.org>.
tristaZero closed issue #22826: In the federation scenario，"select order by null first" or "create view order by null first",the result is incorrect;
URL: https://github.com/apache/shardingsphere/issues/22826
--
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
[GitHub] [shardingsphere] wsm12138 commented on issue #22826: In the federation scenario，"select order by null first" or "create view order by null first",the result is incorrect;
Posted by GitBox <gi...@apache.org>.
wsm12138 commented on issue #22826:
URL: https://github.com/apache/shardingsphere/issues/22826#issuecomment-1352785006
0cbb5fb100de531e7e27382904197c1249baf2d6
### proxy
<img width="1309" alt="image" src="https://user-images.githubusercontent.com/86462784/207823343-36e03061-8b4c-454a-a03b-2a33a6dcc58e.png">
<img width="1747" alt="image" src="https://user-images.githubusercontent.com/86462784/207823405-bb21b168-7a7e-40ab-b5d0-7034313cb307.png">
### og
<img width="1682" alt="image" src="https://user-images.githubusercontent.com/86462784/207824289-c752966a-fc08-4ebf-8480-7428b5c33db7.png">
--
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
[GitHub] [shardingsphere] peilinqian commented on issue #22826: In the federation scenario，"select order by null first" or "create view order by null first",the result is incorrect;
Posted by GitBox <gi...@apache.org>.
peilinqian commented on issue #22826:
URL: https://github.com/apache/shardingsphere/issues/22826#issuecomment-1356244472
issue链接 issue描述 issue状态 问题版本 备注
https://github.com/apache/shardingsphere/issues/22173 sql-federation-type未进行有效值校验，每次执行sql都会进行初始化federation引擎 关闭 "ShardingSphere-5.2.1-SNAPSHOT
Commit ID: dirty-20bf595dfeced4dd8ffee2f6d95de52fdf3e569d"
https://github.com/apache/shardingsphere/issues/22170 select group by非分片键（列存在空值）查询结果不正确 关闭
https://github.com/apache/shardingsphere/issues/22355 join with USING(column)' 的查询结果存在问题；列名称和值不匹配。 关闭 "ShardingSphere-5.2.2-SNAPSHOT
dirty-753c0cee8ee6fd3db00536da55b64bc5198a3758"
https://github.com/apache/shardingsphere/issues/22359 select关联查询场景‘natural join’语法不支持，报错ERROR: The column index is out of range: 10, number of columns: 9. 关闭
https://github.com/apache/shardingsphere/issues/22375 group by 语法，查询字段不能包含除groupby列以外的其他列 打开 目前第三方插件calcite目前不支持该场景，短期内无法支持。后期解决 否
https://github.com/apache/shardingsphere/issues/22388 GROUPING SETS、ROLLUP、CUBE语法不支持 打开 语法实现复杂，未要求这些语句的实现。 否
https://github.com/apache/shardingsphere/issues/22412 sharding-proxy使用未适配过sharding的tpcc执行tpcc异常，主键冲突；目前涉及的insert、update跨库操作不支持。 打开 tpcc问题，目前仅支持select跨库，后续定位问题 否
https://github.com/apache/shardingsphere/issues/22432 sharding-proxy使用适配过sharding的tpcc（完美sharding的场景）进行建仓成功，runBenchmark失败； 关闭
https://github.com/apache/shardingsphere/issues/22438 select语法fetch offset等场景异常‘java.lang.NullPointerException’ 关闭
https://github.com/apache/shardingsphere/issues/22480 使用orderby的视图进行查询使用使用limit 5,3或者limit 3 offset 5，查询结果顺序不正确 关闭
https://github.com/apache/shardingsphere/issues/22514 使用distinct on语法查询结果不正确，未进行去重 打开 目前第三方插件calcite目前不支持该场景，短期内无法支持。后期解决 否
https://github.com/apache/shardingsphere/issues/22553 创建视图完成后，查询视图的结果概率性是数据源单个库的结果，而非所有数据源的查询结果 打开 yat测试出现问题，开发定位中，先sleep规避 否
https://github.com/apache/shardingsphere/issues/22569 创建视图完成后，查询视图的结果概率性报错视图不存在 打开 yat测试出现问题，开发定位中，先sleep规避 否
https://github.com/apache/shardingsphere/issues/22575 在federation场景下，zookeeper中的view信息存在重名，即使view已经drop了。 关闭 ZK工具的问题，非问题
https://github.com/apache/shardingsphere/issues/22576 在federation场景下，‘UNION/EXCEPT/INTERSECT’的优先级不正确。 关闭
https://github.com/apache/shardingsphere/issues/22823 在federation场景下，不区分大小写排序（order by nlssort）功能不支持，相关视图创建成功，查询报错。 打开 语法实现复杂，未要求这些语句的实现。 否
https://github.com/apache/shardingsphere/issues/22820 在federation场景下，视图创建子句为多个分片表进行union/intersect/except/minus，创建视图报错“ERROR: String index out of range: -7” 关闭 "ShardingSphere-5.2.2-SNAPSHOT
Commit ID: dirty-631fdf40f87223e176abe5c851a51b3287b4d6de"
https://github.com/apache/shardingsphere/issues/22822 在federation场景下，fetch子句查询结果不正确。Fetch count预期应该为fetch count，实际结果为偏移count。 打开 社区开放完成，预计2周时间 否
https://github.com/apache/shardingsphere/issues/22826 在federation场景下，“select order by null first/last”或者“create view order by null first/last”结果不正确；并未按空值先序。 关闭
https://github.com/apache/shardingsphere/issues/22841 在federation场景下，视图与表关联查询，group by场景下，如果where条件有视图相关字段，sql语句执行异常 打开 federation底层优化引擎抛出报错，源码修复周期长 否
"ShardingSphere-5.3.1-SNAPSHOT
Commit ID: dirty-0cbb5fb100de531e7e27382904197c1249baf2d6
"
--
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
[GitHub] [shardingsphere] peilinqian commented on issue #22826: In the federation scenario，"select order by null first" or "create view order by null first",the result is incorrect;
Posted by GitBox <gi...@apache.org>.
peilinqian commented on issue #22826:
URL: https://github.com/apache/shardingsphere/issues/22826#issuecomment-1352831273
**the result of opengauss**
```
og_db=# drop table if exists test;
DROP TABLE
og_db=# create table test (id int,name text,remark text);
CREATE TABLE
og_db=# insert into test values(1,'','空字符串');
INSERT 0 1
og_db=# insert into test values(2,null,'null');
INSERT 0 1
og_db=# insert into test values(3,'test','非空非null值');
INSERT 0 1
og_db=# select * from test order by name nulls last;
id | name | remark
----+------+--------------
3 | test | 非空非null值
1 | | 空字符串
2 | | null
(3 rows)
og_db=# select * from test order by name nulls first;
id | name | remark
----+------+--------------
1 | | 空字符串
2 | | null
3 | test | 非空非null值
(3 rows)
```
**the result of pg**
```
postgres=# drop table if exists test;
NOTICE: table "test" does not exist, skipping
DROP TABLE
postgres=# create table test (id int,name text,remark text);
CREATE TABLE
postgres=# insert into test values(1,'','空字符串');
INSERT 0 1
postgres=# insert into test values(2,null,'null');
INSERT 0 1
postgres=# insert into test values(3,'test','非空非null值');
INSERT 0 1
postgres=# select * from test order by name nulls last;
id | name | remark
----+------+--------------
1 | | 空字符串
3 | test | 非空非null值
2 | | null
(3 rows)
postgres=# select * from test order by name nulls first;
id | name | remark
----+------+--------------
2 | | null
1 | | 空字符串
3 | test | 非空非null值
(3 rows)
```
--
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