apache / apache/shardingsphere
The select syntax of ‘group by cube/rollup/grouping sets’ does not support;
- Dominant language
- Java
- Stars
- 20.8k
- Forks
- 6.9k
- Avg merge
- 11h 38m
- Merged PRs (30d)
- 326
Description
### 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
```
Contributor guide
Assessment
This issue has not been assessed yet.