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/11/14 10:55:11 UTC
[GitHub] [shardingsphere] peilinqian opened a new issue, #22170: The result of select syntax group by non-shard key（column exists null value）is incorrect，federation doesn't take effect
peilinqian opened a new issue, #22170:
URL: https://github.com/apache/shardingsphere/issues/22170
### 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.1-SNAPSHOT
Commit ID: dirty-20bf595dfeced4dd8ffee2f6d95de52fdf3e569d
Commit Message: Rename sub modules of shardingsphere-infra-datetime-type. (#21472)
Branch: 20bf595dfeced4dd8ffee2f6d95de52fdf3e569d
Build time: 2022-11-11T19:57:07+0800
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-Proxy
### Expected behavior
The result of select syntax group by non-shard key （column exists null value）is incorrect
### Actual behavior
The result of select syntax group by non-shard key （column exists null value）is correct
### Reason analyze (If you can)
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
1、server.yaml
props:
sql-show: true
sql-federation-type: ADVANCED
2、gsql connect
```
drop table if exists t_ssdb_tb;
create table t_ssdb_tb(id int,c_id int,a int,b char(10));
insert into t_ssdb_tb values (1,1,10,'test11'),(1,2,10,'Test12'),(1,2,null,'test12'),
(2,1,20,'test21'),(2,2,20,'test22'),(2,3,null,'Test22'),
(3,3,null,'test3'),(3,3,null,'test3'),(4,4,40,'test4'),(5,5,50,'Test5');
select avg(id),a from t_ssdb_tb group by a;
```
**ss-proxy result**
```
test_db=> select avg(id),a from t_ssdb_tb group by a;
avg | a
--------+----
1.0000 | 10
1.0000 |
1.0000 | 10
2.0000 | 20
2.0000 |
4.0000 | 40
5.0000 | 50
3.0000 |
(8 rows)
```
**og result**
```
openGauss=# select avg(id),a from t_ssdb_tb group by a;
avg | a
------------------------+----
2.0000000000000000 | 20
4.0000000000000000 | 40
5.0000000000000000 | 50
1.00000000000000000000 | 10
2.2500000000000000 |
(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-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 #22170: The result of select syntax group by non-shard key（column exists null value）is incorrect，federation doesn't take effect
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #22170:
URL: https://github.com/apache/shardingsphere/issues/22170#issuecomment-1319493004
According to PostgreSQL document——https://www.postgresql.org/docs/current/queries-order.html, The NULLS FIRST and NULLS LAST options can be used to determine whether nulls appear before or after non-null values in the sort ordering. By default, null values sort as if larger than any non-null value; that is, NULLS FIRST is the default for DESC order, and NULLS LAST otherwise.
```sql
demo_ds_0=# SELECT * FROM t_ssdb_tb_1 order by a asc;
id | c_id | a | b
----+------+----+------------
2 | 1 | 20 | test21
2 | 3 | | Test22
(2 rows)
demo_ds_0=# SELECT * FROM t_ssdb_tb_1 order by a desc;
id | c_id | a | b
----+------+----+------------
2 | 3 | | Test22
2 | 1 | 20 | test21
(2 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 #22170: The result of select syntax group by non-shard key（column exists null value）is incorrect，federation doesn't take effect
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #22170:
URL: https://github.com/apache/shardingsphere/issues/22170#issuecomment-1319503531
According to oracle document——https://docs.oracle.com/javadb/10.6.2.1/ref/rrefsqlj13658.html, If the null ordering is not specified then the handling of the null values is: NULLS LAST if the sort is ASC, NULLS FIRST if the sort is DESC. If neither ascending nor descending order is specified, and the null ordering is also not specified, then both defaults are used and thus the order will be ascending with NULLS LAST.
```SQL
SELECT * FROM test2 ORDER BY content2 ASC;
-- ID CONTENT2
-- 2 TEST
-- 1 -
SELECT * FROM test2 ORDER BY content2 DESC;
-- ID CONTENT2
-- 1 -
-- 2 TEST
SELECT * FROM test2 ORDER BY content2 DESC NULLS LAST;
-- 2 TEST
-- 1 -
```
--
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 #22170: The result of select syntax group by non-shard key（column exists null value）is incorrect，federation doesn't take effect
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #22170:
URL: https://github.com/apache/shardingsphere/issues/22170#issuecomment-1316165415
I did some investigation and the issue was caused by an inconsistency between the ShardingSphere merge engine's handling of sorting NULL values and the database, which I'll fix later.
--
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 #22170: The result of select syntax group by non-shard key（column exists null value）is incorrect，federation doesn't take effect
Posted by GitBox <gi...@apache.org>.
peilinqian commented on issue #22170:
URL: https://github.com/apache/shardingsphere/issues/22170#issuecomment-1348099695
**version**
```
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
```
**result ：pass**
```
new_db=> create table t_ssdb_tb(id int,c_id int,a int,b char(10));
CREATE TABLE
new_db=> insert into t_ssdb_tb values (1,1,10,'test11'),(1,2,10,'Test12'),(1,2,null,'test12'),
new_db-> (2,1,20,'test21'),(2,2,20,'test22'),(2,3,null,'Test22'),
new_db-> (3,3,null,'test3'),(3,3,null,'test3'),(4,4,40,'test4'),(5,5,50,'Test5');
INSERT 0 10
new_db=> select avg(id),a from t_ssdb_tb group by a;
avg | a
--------+----
1.0000 | 10
2.0000 | 20
4.0000 | 40
5.0000 | 50
2.2500 |
(5 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] zhaojinchao95 closed issue #22170: The result of select syntax group by non-shard key（column exists null value）is incorrect，federation doesn't take effect
Posted by GitBox <gi...@apache.org>.
zhaojinchao95 closed issue #22170: The result of select syntax group by non-shard key（column exists null value）is incorrect，federation doesn't take effect
URL: https://github.com/apache/shardingsphere/issues/22170
--
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 #22170: The result of select syntax group by non-shard key（column exists null value）is incorrect，federation doesn't take effect
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #22170:
URL: https://github.com/apache/shardingsphere/issues/22170#issuecomment-1319480982
In MySQL, there is no parameter for sorting NULL values - https://dev.mysql.com/doc/refman/8.0/en/select.html.
When using asc in ascending order, the null value will be displayed first, which corresponds to the nulls first parameter, and when using desc in reverse order, the null value will be displayed last, which corresponds to the nulls last parameter.
```sql
mysql> select * from t_order_0 order by content asc;
+----------+---------+---------+
| order_id | user_id | content |
+----------+---------+---------+
| 3 | 3 | NULL |
| 2 | 2 | TEST2 |
+----------+---------+---------+
2 rows in set (0.00 sec)
mysql> select * from t_order_0 order by content desc;
+----------+---------+---------+
| order_id | user_id | content |
+----------+---------+---------+
| 2 | 2 | TEST2 |
| 3 | 3 | NULL |
+----------+---------+---------+
2 rows in set (0.00 sec)
```
--
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 #22170: The result of select syntax group by non-shard key（column exists null value）is incorrect，federation doesn't take effect
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #22170:
URL: https://github.com/apache/shardingsphere/issues/22170#issuecomment-1319506460
In SQLServer, there is no parameter for sorting NULL values——https://learn.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-ver16.
When using asc in ascending order, the null value will be displayed first, which corresponds to the nulls first parameter, and when using desc in reverse order, the null value will be displayed last, which corresponds to the nulls last parameter.
```sql
SELECT * FROM test2 ORDER BY content2 ASC;
-- id content2
-- 1 NULL
-- 2 TEST
SELECT * FROM test2 ORDER BY content2 DESC;
-- id content2
-- 2 TEST
-- 1 NULL
```
--
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 #22170: The result of select syntax group by non-shard key（column exists null value）is incorrect，federation doesn't take effect
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #22170:
URL: https://github.com/apache/shardingsphere/issues/22170#issuecomment-1319497223
openGuass currently has no documentation on sorting null values, but it is compatible with PostgreSQL.
```sql
db_0=> SELECT * FROM t_ssdb_tb_1 order by a asc;
id | c_id | a | b
----+------+----+------------
2 | 1 | 20 | test21
2 | 3 | | Test22
(2 rows)
db_0=> SELECT * FROM t_ssdb_tb_1 order by a desc;
id | c_id | a | b
----+------+----+------------
2 | 3 | | Test22
2 | 1 | 20 | test21
(2 rows)
db_0=> SELECT * FROM t_ssdb_tb_1 order by a desc nulls last;
id | c_id | a | b
----+------+----+------------
2 | 1 | 20 | test21
2 | 3 | | Test22
(2 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] wsm12138 commented on issue #22170: The result of select syntax group by non-shard key（column exists null value）is incorrect，federation doesn't take effect
Posted by GitBox <gi...@apache.org>.
wsm12138 commented on issue #22170:
URL: https://github.com/apache/shardingsphere/issues/22170#issuecomment-1324688003
## version
ShardingSphere-5.2.2-SNAPSHOT
Commit ID: 792829660d2bf7b2dbc7890d8431242a47541333
Commit Message: Add integration test case for select group by statement contains null value record (#22314)
Branch: 792829660d2bf7b2dbc7890d8431242a47541333
Build time: 2022-11-23T15:08:10+0800
## og result
<img width="652" alt="image" src="https://user-images.githubusercontent.com/86462784/203498408-d121f819-827b-43f8-93c1-829b8b3bc7fb.png">
## proxy result
--
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