apache / apache/shardingsphere

If the select column doesn't only contain group by column,and the group by column is primary key,returns error.

Open
#22,375 3 comments 0 reactions 1 assignee Claimed by @strongduanmu View on GitHub
db: openGauss feature: SQL federation type: bug
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
If the select column doesn't only contain group by column,and the group by column is primary key,returns normal.

### Actual behavior
If the select column doesn't only contain group by column,and the group by column is primary key,returns error.

### Reason analyze (If you can)

### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
```
drop table if exists t_order;
drop table if exists t_merchant;
create table t_order (order_id int primary key, user_id int not null, status varchar(50) not null, merchant_id int not null, remark varchar(50), creation_date date);
create table t_merchant (merchant_id int primary key, country_id int not null, merchant_name varchar(50) not null, business_code varchar(50) not null, telephone varchar(50) not null, creation_date date not null);

insert into t_order values(1000, 10, 'init', 1, 'test', '2017-07-08');
insert into t_order values(1001, 10, 'init', 2, 'test', '2017-07-08');
insert into t_order values(2000, 20, 'init', 3, 'test', '2017-08-08');
insert into t_order values(2001, 20, 'init', 4, 'test', '2017-08-08');
insert into t_order values(3100, 11, 'init', 5, 'test', '2017-08-08');
insert into t_order values(3000, 10, 'init', 1, 'test', '2017-07-08');
insert into t_order values(3001, 10, 'init', 2, 'test', '2017-07-08');
insert into t_order values(4000, 20, 'init', 3, 'test', '2017-08-08');
insert into t_order values(4001, 20, 'init', 4, 'test', '2017-08-08');
insert into t_order values(4100, 11, 'init', 5, 'test', '2017-08-08');
insert into t_merchant values(1, 86, 'tencent', '86000001', '86100000001', '2017-08-08');
insert into t_merchant values(2, 86, 'haier', '86000002', '86100000002', '2017-08-08');
insert into t_merchant values(3, 86, 'huawei', '86000003', '86100000003', '2017-08-08');
insert into t_merchant values(4, 86, 'alibaba', '86000004', '86100000004', '2017-08-08');
insert into t_merchant values(5, 86, 'lenovo', '86000005', '86100000005', '2017-08-08');
insert into t_merchant values(5, 86, 'lenovo1', '86000005', '86100000005', '2017-08-08');
select min(o.order_id), min(o.merchant_id), m.merchant_name from t_order o inner join t_merchant m on o.merchant_id = m.merchant_id group by m.merchant_id;
```
**the result of ss-proxy:**
```
test_db=> select min(o.order_id), min(o.merchant_id), m.merchant_name from t_order o inner join t_merchant m on o.merchant_id = m.merchant_id group by m.merchant_id;
ERROR: At line 0, column 0: Expression 'm.merchant_name' is not being grouped
test_db=> select min(o.order_id), min(o.merchant_id), m.merchant_id from t_new_order o inner join t_merchant m on o.merchant_id = m.merchant_id where o.user_id in(10,11,12,13) group by m.merchant_id order by 1,2,3;
min | min | merchant_id
------+-----+-------------
1000 | 1 | 1
1001 | 2 | 2
1100 | 5 | 5
(3 rows)

```
**the result of opengauss:**
```
tpccdb=# select min(o.order_id), min(o.merchant_id), m.merchant_name from t_order o inner join t_merchant m on o.merchant_id = m.merchant_id group by m.merchant_id;
min | min | merchant_name
------+-----+---------------
1000 | 1 | tencent
1001 | 2 | haier
2001 | 4 | alibaba
2000 | 3 | huawei
3100 | 5 | lenovo
(5 rows)
tpccdb=# select min(o.order_id), min(o.merchant_id), m.merchant_id from t_new_order o inner join t_merchant m on o.merchant_id = m.merchant_id where o.user_id in(10,11,12,13) group by m.merchant_id order by 1,2,3;
min | min | merchant_id
------+-----+-------------
1000 | 1 | 1
1001 | 2 | 2
1100 | 5 | 5
(3 rows)

```

### 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_user:
actualDataNodes: ds_0.t_user
t_product:
actualDataNodes: ds_0.t_product
t_merchant:
actualDataNodes: ds_1.t_merchant
t_product_detail:
actualDataNodes: ds_1.t_product_detail
t_order:
actualDataNodes: ds_${0..1}.t_order
databaseStrategy:
standard:
shardingColumn: user_id
shardingAlgorithmName: database_inline
t_order_item:
actualDataNodes: ds_${0..1}.t_order_item
databaseStrategy:
standard:
shardingColumn: user_id
shardingAlgorithmName: database_inline
t_order_item1:
actualDataNodes: ds_${0..1}.t_order_item1
databaseStrategy:
standard:
shardingColumn: user_id
shardingAlgorithmName: database_inline
t_new_order:
actualDataNodes: ds_${0..1}.t_new_order_${0..1}
databaseStrategy:
standard:
shardingAlgorithmName: database_inline
shardingColumn: user_id
tableStrategy:
standard:
shardingColumn: order_id
shardingAlgorithmName: table_inline
bindingTables:
- t_order,t_order_item
broadcastTables:
- t_product_category
- t_country
shardingAlgorithms:
database_inline:
type: INLINE
props:
algorithm-expression: ds_${user_id % 2}
allow-range-query-with-inline-sharding: true
table_inline:
type: INLINE
props:
algorithm-expression: t_new_order_${order_id % 2}
allow-range-query-with-inline-sharding: true
mode:
type: Cluster
repository:
type: ZooKeeper
props:
namespace: governance_ds
server-lists: 7.212.123.28:2181
retryIntervalMilliseconds: 500
timeToLiveSeconds: 60
maxRetries: 3
operationTimeoutMilliseconds: 500
authority:
users:
- user: root@%
password: root
- user: sharding
password: sharding
privilege:
type: ALL_PERMITTED
rules:
- !TRANSACTION
defaultType: XA
providerType: Atomikos
props:
sql-show: true
```

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.