peilinqian opened a new issue, #22388: URL: https://github.com/apache/shardingsphere/issues/22388
### Which version of ShardingSphere did you use? we find java version: java8, full_version=1.8.0_342, full_path=/home/peilq_sharding/bisheng-jdk1.8.0_342//bin/java ShardingSphere-5.2.2-SNAPSHOT Commit ID: dirty-753c0cee8ee6fd3db00536da55b64bc5198a3758 Commit Message: Optimize sqlFederationExecutor init logic when sqlFederationType modify dynamically (https://github.com/apache/shardingsphere/pull/22209) Branch: https://github.com/apache/shardingsphere/commit/753c0cee8ee6fd3db00536da55b64bc5198a3758 Build time: 2022-11-19T10:18:41+0800 ### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy? ShardingSphere-Proxy ### Expected behavior The select syntax of ‘group by cube/rollup/grouping sets’ support； ### Actual behavior The select syntax of ‘group by cube/rollup/grouping sets’ does not support； ``` test_db=> select fact1_id, test_db-> fact2_id, test_db-> fact3_id, test_db-> fact4_id, test_db-> sum(sales_value) as tt test_db-> from t_group test_db-> group by rollup(fact1_id,fact2_id,fact3_id,fact4_id) test_db-> order by fact1_id,fact2_id,fact3_id,fact4_id limit 10; ERROR: function rollup(numeric, numeric, numeric, numeric) does not exist LINE 6: from t_group ^ 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 test_db=> select fact1_id, test_db-> fact2_id, test_db-> fact3_id, test_db-> fact4_id, test_db-> sum(sales_value) as tt test_db-> from t_group test_db-> group by cube(fact1_id,fact2_id,fact3_id,fact4_id) test_db-> order by fact1_id,fact2_id,fact3_id,fact4_id limit 10; ERROR: function cube(numeric, numeric, numeric, numeric) does not exist LINE 6: from t_group ^ 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 test_db=> test_db=> select fact1_id, test_db-> fact2_id, test_db-> fact3_id, test_db-> fact4_id, test_db-> sum(sales_value) as tt test_db-> from t_group test_db-> group by grouping sets(fact1_id,fact2_id,fact3_id,fact4_id) test_db-> order by fact1_id,fact2_id,fact3_id,fact4_id; ERROR: syntax error at or near "sets" LINE 6: from t_group ``` ### Reason analyze (If you can) ### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc. ``` --create drop table if exists t_group; --sharding datbase and table create table t_group (fact1_id number not null,fact2_id number not null,fact3_id number not null,fact4_id number not null,sales_value number(10,2) not null); insert into t_group values (1,2,3,5,10.67); insert into t_group values (2,9,3,5,10.67); insert into t_group values (3,9,3,5,10.67); insert into t_group values (4,9,3,8,10.37); insert into t_group values (5,9,3,8,10.37); insert into t_group values (6,2,3,8,10.37); insert into t_group values (7,2,3,8,10.37); insert into t_group values (8,2,4,5,10.67); insert into t_group values (9,2,4,5,5.67); insert into t_group values (10,2,4,5,5.67); insert into t_group values (11,2,3,5,10.68); insert into t_group values (12,7,3,5,10.68); insert into t_group values (13,7,3,5,10.68); insert into t_group values (14,7,9,5,10.27); insert into t_group values (15,2,9,6,10.27); insert into t_group values (16,2,9,6,0.27); insert into t_group values (17,87,3,6,0.27); insert into t_group values (18,87,3,5,0.22); insert into t_group values (19,87,22,5,10.22); insert into t_group values (20,87,3,5,10.22); --step3: group by + rollup; except：correct select fact1_id, fact2_id, fact3_id, fact4_id, sum(sales_value) as tt from t_group group by rollup(fact1_id,fact2_id,fact3_id,fact4_id) order by fact1_id,fact2_id,fact3_id,fact4_id limit 10; --step4: group by + cube; except：correct select fact1_id, fact2_id, fact3_id, fact4_id, sum(sales_value) as tt from t_group group by cube(fact1_id,fact2_id,fact3_id,fact4_id) order by fact1_id,fact2_id,fact3_id,fact4_id limit 10; --step4: group by + grouping sets ;except:correct select fact1_id, fact2_id, fact3_id, fact4_id, sum(sales_value) as tt from t_group group by grouping sets(fact1_id,fact2_id,fact3_id,fact4_id) order by fact1_id,fact2_id,fact3_id,fact4_id; ``` ### 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_group: actualDataNodes: ds_${0..1}.t_group_${0..1} databaseStrategy: standard: shardingAlgorithmName: database_group_inline shardingColumn: fact1_id tableStrategy: standard: shardingColumn: fact2_id shardingAlgorithmName: table_group_inline shardingAlgorithms: database_group_inline: type: INLINE props: algorithm-expression: ds_${fact1_id % 2} allow-range-query-with-inline-sharding: true table_group_inline: type: INLINE props: algorithm-expression: t_group_${fact2_id % 2} allow-range-query-with-inline-sharding: true ``` -- 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]
