Opengauss select syntax test, there are problems in some scenarios

[peilinqian]

Bug Report

For English only, other languages will not accept.

Before report a bug, make sure you have:

• Searched open and closed GitHub issues.
• Read documentation: ShardingSphere Doc.

Please pay attention on issues you submitted, because we maybe need more details.
If no response anymore and we cannot reproduce it on current information, we will close it.

Please answer these questions before submitting your issue. Thanks!

Which version of ShardingSphere did you use?

we find java version: java8, full_version=1.8.0_282
ShardingSphere-5.1.3-SNAPSHOT
Commit ID: dirty-9dd0d3990c849d50c17c6dc7c92ec2d4ce0ad7e5
Commit Message: Implements openGauss version function by calcite (#19327)
Branch: 9dd0d39
Build time: 2022-08-04T19:57:18+0800

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

ShardingSphere-Proxy

Expected behavior

opengauss select syntax test, the test result is correct

Actual behavior

Opengauss select syntax test, there are problems in some scenarios

Reason analyze (If you can)

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

drop table t_order;
create table t_order (user_id int,order_id int ,ordername text);
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');

--1、distinct，groupby
select distinct(user_id),count(ordername) as countnum from t_order group by user_id order by user_id desc;
--2、hint
explain (costs off,verbose) select /*+ tablescan(t_order)*/ * from t_order where order_id>2 and user_id =1;
--3、limit
select * from t_order where order_id >1 order by user_id,order_id desc limit 5;
--4、limit offset
select * from t_order where order_id >1 order by user_id,order_id desc limit 1 offset 8;
--5、offset
select * from t_order where order_id >1 order by user_id,order_id desc offset 6;
--6、fetch  ---error
select * from t_order where order_id >=1 order by user_id,order_id desc offset 1 fetch next 3 row only;
--7、with   ---error
with temp_tb(i,j,k) as (select * from t_order) select * from temp_tb where j =2;
--8、order by null
select * from t_order order by ordername nulls last; ---error
select * from t_order order by ordername nulls first;
--9、group by having
select user_id,count(order_id) from t_order group by user_id having count(order_id)=6;  ---error
select user_id,count(order_id) as num from t_order group by user_id having count(order_id)>2;  ---error
--10、group by cube
select user_id,count(order_id) from t_order group by cube(user_id) having count(order_id)=4; ---error
--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);  ---error
--12、union
select * from t_order where order_id =0 union select * from t_order where order_id =3; ---error
--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; ---error
--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;
--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;  ---error，null  scenarios
select * from t_order order by NLSSORT(ordername,'NLS_SORT = generic_m_ci'),order_id,user_id;  ---error，null  scenarios
--Case-sensitive sorting
select * from t_order order by ordername,user_id,order_id;  ---error，null  scenarios

sharding-config

  tables:
    t_order:
      actualDataNodes: ds_${0..1}.t_order_${0..1}
      databaseStrategy:
        standard:
          shardingAlgorithmName: ds_order_database_inline
          shardingColumn: user_id
      tableStrategy:
        standard:
          shardingColumn: order_id
          shardingAlgorithmName: ts_order_inline
  defaultTableStrategy:
    none:
  shardingAlgorithms:
    ds_order_database_inline:
      type: INLINE
      props:
        algorithm-expression: ds_${user_id % 2}
    ts_order_inline:
      type: INLINE
      props:
        algorithm-expression: t_order_${order_id % 2}

Example codes for reproduce this issue (such as a github link).

[peilinqian]

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)

[peilinqian]

sharding-proxy

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_0
   Output: user_id, order_id, ordername
   Filter: ((t_order_0.order_id > 2) AND (t_order_0.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 | b04
       0 |        4 | 
       0 |        3 | a03
       0 |        3 | 
       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 | A13
(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 | A14
       1 |        4 | 
       1 |        3 | A13
       1 |        3 | A13
       1 |        3 | 
       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 | 
       0 |        3 | a03
       0 |        3 | 
       0 |        3 | A03
       0 |        2 | 02
       1 |        4 | A14
       1 |        4 | 
       1 |        3 | A13
       1 |        3 | A13
       1 |        3 | 
       1 |        2 | 12
(11 rows)

--7、with
with temp_tb(i,j,k) as (select * from t_order) select * from temp_tb where j =2;
ERROR:  relation "t_order" does not exist on dn_6001
LINE 1: with temp_tb(i,j,k) as (select * from t_order) select * from...
                                              ^
--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
       1 |        4 | 
       4 |        4 | a44
       0 |        4 | b04
       0 |        4 | 
       5 |        5 | b45
       1 |        3 | 
       4 |        5 | b45
       0 |        3 | 
(22 rows)

select * from t_order order by ordername nulls first;
 user_id | order_id | ordername 
---------+----------+-----------
       0 |        4 | 
       1 |        3 | 
       0 |        3 | 
       1 |        4 | 
       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
       5 |        5 | b45
       4 |        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 
---------+-----
       0 |   8
       1 |   8
(2 rows)

--10、group by cube
select user_id,count(order_id) from t_order group by cube(user_id) having count(order_id)=4;
ERROR:  function cube(integer) does not exist
LINE 1: select user_id,count(order_id) from t_order group by cube(us...
                                         ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
CONTEXT:  referenced column: group_by_derived_0
--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 row)

--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 |        0 | 00
       1 |        0 | 10
(2 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 
----------+-----------
        4 | a44
        3 | a03
        1 | 01
        3 | A03
        3 | 
        4 | 
        2 | 02
        4 | b04
        4 | a44
        4 | A14
        3 | A13
        2 | 12
        3 | 
        1 | 11
        4 | 
(15 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 | 
       0 |        4 | b04
       0 |        3 | A03
       0 |        3 | 
       0 |        3 | a03
       1 |        4 | A14
       1 |        4 | 
       1 |        3 | A13
       1 |        3 | 
       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 | 
       4 |        5 | b45
       0 |        3 | a03
       0 |        3 | A03
       0 |        1 | 01
       1 |        3 | 
       5 |        5 | b45
       1 |        3 | A13
       1 |        3 | A13
       1 |        1 | 11
       0 |        4 | 
       0 |        4 | b04
       4 |        6 | A56
       4 |        4 | a44
       0 |        2 | 02
       0 |        0 | 00
       1 |        4 | 
       5 |        6 | A56
       5 |        4 | a44
       1 |        4 | A14
       1 |        2 | 12
       1 |        0 | 10
(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
       1 |        4 | 
       0 |        4 | b04
       0 |        4 | 
       4 |        5 | b45
       0 |        3 | 
       5 |        5 | b45
       1 |        3 | 
(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
       1 |        4 | 
       0 |        4 | b04
       0 |        4 | 
       4 |        5 | b45
       0 |        3 | 
       5 |        5 | b45
       1 |        3 | 
(22 rows)

[strongduanmu]

This feature does not plan to support now.

--7、with
with temp_tb(i,j,k) as (select * from t_order) select * from temp_tb where j =2;
ERROR:  relation "t_order" does not exist on dn_6001
LINE 1: with temp_tb(i,j,k) as (select * from t_order) select * from...

[strongduanmu]

This exception will be fixed soon.

--9、group by having
sharding_db=> select user_id,count(order_id) from t_order group by user_id having count(order_id)=6;
ERROR:  java.lang.UnsupportedOperationException: unsupported TableSegment type: class org.apache.shardingsphere.sql.parser.sql.common.segment.dml.item.AggregationProjectionSegment

--10、group by cube
sharding_db=> select user_id,count(order_id) from t_order group by cube(user_id) having count(order_id)=4;
ERROR:  java.lang.UnsupportedOperationException: unsupported ExpressionOrderByItemSegment

--12、union
sharding_db=> select * from t_order where order_id =0 union select * from t_order where order_id =3;
ERROR:  Cannot find JDBC type '0' in PostgreSQL column type

--13、except
org.apache.calcite.plan.RelOptPlanner$CannotPlanException: There are not enough rules to produce a node with desired properties: convention=ENUMERABLE, sort=[].
Missing conversion is LogicalMinus[convention: NONE -> ENUMERABLE]
There is 1 empty subset: rel#116:RelSubset#3.ENUMERABLE.[], the relevant part of the original plan is as follows
114:LogicalMinus(all=[false])
  110:LogicalCalc(subset=[rel#111:RelSubset#1.NONE.[]], expr#0..2=[{inputs}], expr#3=[0], expr#4=[>($t1, $t3)], order_id=[$t1], ordername=[$t2], $condition=[$t4])
    78:LogicalTableScan(subset=[rel#109:RelSubset#0.NONE.[]], table=[[public, t_order]])
  112:LogicalCalc(subset=[rel#113:RelSubset#2.NONE.[]], expr#0..2=[{inputs}], expr#3=[4], expr#4=[>($t1, $t3)], order_id=[$t1], ordername=[$t2], $condition=[$t4])
    78:LogicalTableScan(subset=[rel#109:RelSubset#0.NONE.[]], table=[[public, t_order]])

Root: rel#116:RelSubset#3.ENUMERABLE.[]
Original rel:
LogicalMinus(subset=[rel#116:RelSubset#3.ENUMERABLE.[]], all=[false]): rowcount = 50.0, cumulative cost = {50.0 rows, 50.0 cpu, 0.0 io}, id = 114
  LogicalCalc(subset=[rel#111:RelSubset#1.NONE.[]], expr#0..2=[{inputs}], expr#3=[0], expr#4=[>($t1, $t3)], order_id=[$t1], ordername=[$t2], $condition=[$t4]): rowcount = 50.0, cumulative cost = {50.0 rows, 800.0 cpu, 0.0 io}, id = 110
    LogicalTableScan(subset=[rel#109:RelSubset#0.NONE.[]], table=[[public, t_order]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 78
  LogicalCalc(subset=[rel#113:RelSubset#2.NONE.[]], expr#0..2=[{inputs}], expr#3=[4], expr#4=[>($t1, $t3)], order_id=[$t1], ordername=[$t2], $condition=[$t4]): rowcount = 50.0, cumulative cost = {50.0 rows, 800.0 cpu, 0.0 io}, id = 112
    LogicalTableScan(subset=[rel#109:RelSubset#0.NONE.[]], table=[[public, t_order]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 78

Sets:
Set#0, type: RecordType(JavaType(class java.lang.Integer) user_id, JavaType(class java.lang.Integer) order_id, JavaType(class java.lang.String) ordername)
	rel#109:RelSubset#0.NONE.[], best=null
		rel#78:LogicalTableScan.NONE.[](table=[public, t_order]), rowcount=100.0, cumulative cost={inf}
	rel#119:RelSubset#0.ENUMERABLE.[], best=rel#118
		rel#118:EnumerableTableScan.ENUMERABLE.[](table=[public, t_order]), rowcount=100.0, cumulative cost={100.0 rows, 101.0 cpu, 0.0 io}
Set#1, type: RecordType(JavaType(class java.lang.Integer) order_id, JavaType(class java.lang.String) ordername)
	rel#111:RelSubset#1.NONE.[], best=null
		rel#110:LogicalCalc.NONE.[](input=RelSubset#109,expr#0..2={inputs},expr#3=0,expr#4=>($t1, $t3),0=$t1,1=$t2,$condition=$t4), rowcount=50.0, cumulative cost={inf}
	rel#121:RelSubset#1.ENUMERABLE.[], best=rel#120
		rel#120:EnumerableCalc.ENUMERABLE.[](input=RelSubset#119,expr#0..2={inputs},expr#3=0,expr#4=>($t1, $t3),0=$t1,1=$t2,$condition=$t4), rowcount=50.0, cumulative cost={150.0 rows, 901.0 cpu, 0.0 io}
Set#2, type: RecordType(JavaType(class java.lang.Integer) order_id, JavaType(class java.lang.String) ordername)
	rel#113:RelSubset#2.NONE.[], best=null
		rel#112:LogicalCalc.NONE.[](input=RelSubset#109,expr#0..2={inputs},expr#3=4,expr#4=>($t1, $t3),0=$t1,1=$t2,$condition=$t4), rowcount=50.0, cumulative cost={inf}
	rel#123:RelSubset#2.ENUMERABLE.[], best=rel#122
		rel#122:EnumerableCalc.ENUMERABLE.[](input=RelSubset#119,expr#0..2={inputs},expr#3=4,expr#4=>($t1, $t3),0=$t1,1=$t2,$condition=$t4), rowcount=50.0, cumulative cost={150.0 rows, 901.0 cpu, 0.0 io}
Set#3, type: RecordType(JavaType(class java.lang.Integer) order_id, VARCHAR ordername)
	rel#115:RelSubset#3.NONE.[], best=null
		rel#114:LogicalMinus.NONE.[](input#0=RelSubset#111,input#1=RelSubset#113,all=false), rowcount=50.0, cumulative cost={inf}
	rel#116:RelSubset#3.ENUMERABLE.[], best=null
		rel#117:AbstractConverter.ENUMERABLE.[](input=RelSubset#115,convention=ENUMERABLE,sort=[]), rowcount=50.0, cumulative cost={inf}

Graphviz:
digraph G {
	root [style=filled,label="Root"];
	subgraph cluster0{
		label="Set 0 RecordType(JavaType(class java.lang.Integer) user_id, JavaType(class java.lang.Integer) order_id, JavaType(class java.lang.String) ordername)";
		rel78 [label="rel#78:LogicalTableScan\ntable=[public, t_order]\nrows=100.0, cost={inf}",shape=box]
		rel118 [label="rel#118:EnumerableTableScan\ntable=[public, t_order]\nrows=100.0, cost={100.0 rows, 101.0 cpu, 0.0 io}",color=blue,shape=box]
		subset109 [label="rel#109:RelSubset#0.NONE.[]"]
		subset119 [label="rel#119:RelSubset#0.ENUMERABLE.[]"]
	}
	subgraph cluster1{
		label="Set 1 RecordType(JavaType(class java.lang.Integer) order_id, JavaType(class java.lang.String) ordername)";
		rel110 [label="rel#110:LogicalCalc\ninput=RelSubset#109,expr#0..2={inputs},expr#3=0,expr#4=>($t1, $t3),0=$t1,1=$t2,$condition=$t4\nrows=50.0, cost={inf}",shape=box]
		rel120 [label="rel#120:EnumerableCalc\ninput=RelSubset#119,expr#0..2={inputs},expr#3=0,expr#4=>($t1, $t3),0=$t1,1=$t2,$condition=$t4\nrows=50.0, cost={150.0 rows, 901.0 cpu, 0.0 io}",color=blue,shape=box]
		subset111 [label="rel#111:RelSubset#1.NONE.[]"]
		subset121 [label="rel#121:RelSubset#1.ENUMERABLE.[]"]
	}
	subgraph cluster2{
		label="Set 2 RecordType(JavaType(class java.lang.Integer) order_id, JavaType(class java.lang.String) ordername)";
		rel112 [label="rel#112:LogicalCalc\ninput=RelSubset#109,expr#0..2={inputs},expr#3=4,expr#4=>($t1, $t3),0=$t1,1=$t2,$condition=$t4\nrows=50.0, cost={inf}",shape=box]
		rel122 [label="rel#122:EnumerableCalc\ninput=RelSubset#119,expr#0..2={inputs},expr#3=4,expr#4=>($t1, $t3),0=$t1,1=$t2,$condition=$t4\nrows=50.0, cost={150.0 rows, 901.0 cpu, 0.0 io}",color=blue,shape=box]
		subset113 [label="rel#113:RelSubset#2.NONE.[]"]
		subset123 [label="rel#123:RelSubset#2.ENUMERABLE.[]"]
	}
	subgraph cluster3{
		label="Set 3 RecordType(JavaType(class java.lang.Integer) order_id, VARCHAR ordername)";
		rel114 [label="rel#114:LogicalMinus\ninput#0=RelSubset#111,input#1=RelSubset#113,all=false\nrows=50.0, cost={inf}",shape=box]
		rel117 [label="rel#117:AbstractConverter\ninput=RelSubset#115,convention=ENUMERABLE,sort=[]\nrows=50.0, cost={inf}",shape=box]
		subset115 [label="rel#115:RelSubset#3.NONE.[]"]
		subset116 [label="rel#116:RelSubset#3.ENUMERABLE.[]",color=red]
	}
	root -> subset116;
	subset109 -> rel78;
	subset119 -> rel118[color=blue];
	subset111 -> rel110; rel110 -> subset109;
	subset121 -> rel120[color=blue]; rel120 -> subset119[color=blue];
	subset113 -> rel112; rel112 -> subset109;
	subset123 -> rel122[color=blue]; rel122 -> subset119[color=blue];
	subset115 -> rel114; rel114 -> subset111[label="0"]; rel114 -> subset113[label="1"];
	subset116 -> rel117; rel117 -> subset115;
}
	at org.apache.calcite.plan.volcano.RelSubset$CheapestPlanReplacer.visit(RelSubset.java:709)
	at org.apache.calcite.plan.volcano.RelSubset.buildCheapestPlan(RelSubset.java:390)
	at org.apache.calcite.plan.volcano.VolcanoPlanner.findBestExp(VolcanoPlanner.java:533)
	at org.apache.shardingsphere.infra.federation.optimizer.ShardingSphereOptimizer.optimizeWithCBO(ShardingSphereOptimizer.java:65)
	at org.apache.shardingsphere.infra.federation.optimizer.ShardingSphereOptimizer.optimize(ShardingSphereOptimizer.java:51)
	at org.apache.shardingsphere.infra.federation.executor.advanced.AdvancedFederationExecutor.execute(AdvancedFederationExecutor.java:135)
	at org.apache.shardingsphere.infra.federation.executor.advanced.AdvancedFederationExecutor.executeQuery(AdvancedFederationExecutor.java:106)
	at org.apache.shardingsphere.proxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.doExecuteFederation(JDBCDatabaseCommunicationEngine.java:165)
	at org.apache.shardingsphere.proxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.execute(JDBCDatabaseCommunicationEngine.java:123)
	at org.apache.shardingsphere.proxy.frontend.opengauss.command.query.simple.OpenGaussComQueryExecutor.execute(OpenGaussComQueryExecutor.java:77)
	at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:112)
	at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:78)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
	at java.base/java.lang.Thread.run(Thread.java:833)

[github-actions]

Hello , this issue has not received a reply for several days.
This issue is supposed to be closed.

[RaigorJiang]

Hi @strongduanmu , Has this issue been fixed?