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/08/11 11:57:31 UTC
[GitHub] [shardingsphere] peilinqian commented on issue #20084: Opengauss select syntax test, there are problems in some scenarios
peilinqian commented on issue #20084:
URL: https://github.com/apache/shardingsphere/issues/20084#issuecomment-1211891894
opengauss
```
drop table t_order;
DROP TABLE
create table t_order (user_id int,order_id int ,ordername text);
CREATE TABLE
--insert into t_order values(0,0,'00'),(0,1,'01'),(0,2,'02'),(0,3,''),(0,4,''),(1,0,'10'),(1,1,'11'),(1,2,'12'),(1,3,''),(1,4,''),(0,3,'03'),(4,4,'a44'),(4,5,'b45'),(4,6,'A56'),(5,4,'a44'),(5,5,'b45'),(5,6,'A56');
insert into t_order values(0,0,'00'),(0,1,'01'),(0,2,'02'),(0,3,'a03'),(0,4,'b04'),(0,3,''),(0,4,''),(1,0,'10'),(1,1,'11'),(1,2,'12'),(1,3,'A13'),(1,3,'A13'),(1,4,'A14'),(1,3,''),(1,4,''),(0,3,'A03'),(4,4,'a44'),(4,5,'b45'),(4,6,'A56'),(5,4,'a44'),(5,5,'b45'),(5,6,'A56');
INSERT 0 22
--1、distinct，groupby
select distinct(user_id),count(ordername) as countnum from t_order group by user_id order by user_id desc;
user_id | countnum
---------+----------
5 | 3
4 | 3
1 | 6
0 | 6
(4 rows)
--2、hint
explain (costs off,verbose) select /*+ tablescan(t_order)*/ * from t_order where order_id>2 and user_id =1;
QUERY PLAN
--------------------------------------------------------------
Seq Scan on public.t_order
Output: user_id, order_id, ordername
Filter: ((t_order.order_id > 2) AND (t_order.user_id = 1))
(3 rows)
--3、limit
select * from t_order where order_id >1 order by user_id,order_id desc limit 5;
user_id | order_id | ordername
---------+----------+-----------
0 | 4 |
0 | 4 | b04
0 | 3 |
0 | 3 | a03
0 | 3 | A03
(5 rows)
--4、limit offset
select * from t_order where order_id >1 order by user_id,order_id desc limit 1 offset 8;
user_id | order_id | ordername
---------+----------+-----------
1 | 3 |
(1 row)
--5、offset
select * from t_order where order_id >1 order by user_id,order_id desc offset 6;
user_id | order_id | ordername
---------+----------+-----------
1 | 4 |
1 | 4 | A14
1 | 3 |
1 | 3 | A13
1 | 3 | A13
1 | 2 | 12
4 | 6 | A56
4 | 5 | b45
4 | 4 | a44
5 | 6 | A56
5 | 5 | b45
5 | 4 | a44
(12 rows)
--6、fetch
select * from t_order where order_id >=1 order by user_id,order_id desc offset 1 fetch next 3 row only;
user_id | order_id | ordername
---------+----------+-----------
0 | 4 | b04
0 | 3 | a03
0 | 3 |
(3 rows)
--7、with
with temp_tb(i,j,k) as (select * from t_order) select * from temp_tb where j =2;
i | j | k
---+---+----
0 | 2 | 02
1 | 2 | 12
(2 rows)
--8、order by null
select * from t_order order by ordername nulls last;
user_id | order_id | ordername
---------+----------+-----------
0 | 0 | 00
0 | 1 | 01
0 | 2 | 02
1 | 0 | 10
1 | 1 | 11
1 | 2 | 12
0 | 3 | A03
1 | 3 | A13
1 | 3 | A13
1 | 4 | A14
4 | 6 | A56
5 | 6 | A56
0 | 3 | a03
5 | 4 | a44
4 | 4 | a44
0 | 4 | b04
4 | 5 | b45
5 | 5 | b45
0 | 4 |
1 | 3 |
1 | 4 |
0 | 3 |
(22 rows)
select * from t_order order by ordername nulls first;
user_id | order_id | ordername
---------+----------+-----------
1 | 4 |
1 | 3 |
0 | 4 |
0 | 3 |
0 | 0 | 00
0 | 1 | 01
0 | 2 | 02
1 | 0 | 10
1 | 1 | 11
1 | 2 | 12
0 | 3 | A03
1 | 3 | A13
1 | 3 | A13
1 | 4 | A14
5 | 6 | A56
4 | 6 | A56
0 | 3 | a03
4 | 4 | a44
5 | 4 | a44
0 | 4 | b04
4 | 5 | b45
5 | 5 | b45
(22 rows)
--9、group by having
select user_id,count(order_id) from t_order group by user_id having count(order_id)=6;
user_id | count
---------+-------
(0 rows)
select user_id,count(order_id) as num from t_order group by user_id having count(order_id)>2;
user_id | num
---------+-----
1 | 8
4 | 3
0 | 8
5 | 3
(4 rows)
--10、group by cube
select user_id,count(order_id) from t_order group by cube(user_id) having count(order_id)=4;
user_id | count
---------+-------
(0 rows)
--11、window
select user_id,order_id,count(ordername) over window1 typecount from t_order where order_id = 0 window window1 as (partition by order_id);
user_id | order_id | typecount
---------+----------+-----------
0 | 0 | 2
1 | 0 | 2
(2 rows)
--12、union
select * from t_order where order_id =0 union select * from t_order where order_id =3;
user_id | order_id | ordername
---------+----------+-----------
0 | 3 |
1 | 0 | 10
0 | 3 | a03
1 | 3 | A13
0 | 3 | A03
0 | 0 | 00
1 | 3 |
(7 rows)
--13、except
select order_id,ordername from t_order where order_id >0 except select order_id,ordername from t_order where order_id >4;
order_id | ordername
----------+-----------
2 | 02
4 | a44
3 | A13
1 | 01
3 | A03
4 | A14
3 |
3 | a03
2 | 12
1 | 11
4 |
4 | b04
(12 rows)
--14、intersect
select * from t_order where order_id >2 intersect select * from t_order where order_id <=4 order by user_id,order_id desc;
user_id | order_id | ordername
---------+----------+-----------
0 | 4 | b04
0 | 4 |
0 | 3 | a03
0 | 3 | A03
0 | 3 |
1 | 4 | A14
1 | 4 |
1 | 3 |
1 | 3 | A13
4 | 4 | a44
5 | 4 | a44
(11 rows)
--15、order byBLS_SORT--Case-insensitive sorting
select * from t_order order by NLSSORT(ordername,'NLS_SORT = generic_m_ci') desc ,order_id,user_id;
user_id | order_id | ordername
---------+----------+-----------
0 | 3 |
1 | 3 |
0 | 4 |
1 | 4 |
4 | 5 | b45
5 | 5 | b45
0 | 4 | b04
4 | 6 | A56
5 | 6 | A56
4 | 4 | a44
5 | 4 | a44
1 | 4 | A14
1 | 3 | A13
1 | 3 | A13
0 | 3 | A03
0 | 3 | a03
1 | 2 | 12
1 | 1 | 11
1 | 0 | 10
0 | 2 | 02
0 | 1 | 01
0 | 0 | 00
(22 rows)
select * from t_order order by NLSSORT(ordername,'NLS_SORT = generic_m_ci'),order_id,user_id;
user_id | order_id | ordername
---------+----------+-----------
0 | 0 | 00
0 | 1 | 01
0 | 2 | 02
1 | 0 | 10
1 | 1 | 11
1 | 2 | 12
0 | 3 | A03
0 | 3 | a03
1 | 3 | A13
1 | 3 | A13
1 | 4 | A14
4 | 4 | a44
5 | 4 | a44
4 | 6 | A56
5 | 6 | A56
0 | 4 | b04
4 | 5 | b45
5 | 5 | b45
0 | 3 |
1 | 3 |
0 | 4 |
1 | 4 |
(22 rows)
--Case-sensitive sorting
select * from t_order order by ordername,user_id,order_id;
user_id | order_id | ordername
---------+----------+-----------
0 | 0 | 00
0 | 1 | 01
0 | 2 | 02
1 | 0 | 10
1 | 1 | 11
1 | 2 | 12
0 | 3 | A03
1 | 3 | A13
1 | 3 | A13
1 | 4 | A14
4 | 6 | A56
5 | 6 | A56
0 | 3 | a03
4 | 4 | a44
5 | 4 | a44
0 | 4 | b04
4 | 5 | b45
5 | 5 | b45
0 | 3 |
0 | 4 |
1 | 3 |
1 | 4 |
(22 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