peilinqian opened a new issue, #20084: URL: https://github.com/apache/shardingsphere/issues/20084
## Bug Report **For English only**, other languages will not accept. Before report a bug, make sure you have: - Searched open and closed [GitHub issues](https://github.com/apache/shardingsphere/issues). - Read documentation: [ShardingSphere Doc](https://shardingsphere.apache.org/document/current/en/overview). 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: 9dd0d3990c849d50c17c6dc7c92ec2d4ce0ad7e5 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). -- 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]
