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/11/24 10:51:51 UTC
[GitHub] [shardingsphere] peilinqian opened a new issue, #22388: The select syntax of ‘group by cube/rollup/grouping sets’ does not support；
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: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #22388: The select syntax of ‘group by cube/rollup/grouping sets’ does not support；
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #22388:
URL: https://github.com/apache/shardingsphere/issues/22388#issuecomment-1326960021
Thank you for your feedback. ShardingSphere doesn't support group by cube/rollup/grouping sets now, we will optimize it in the future.
--
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