MrCong233 opened a new issue, #21234:
URL: https://github.com/apache/shardingsphere/issues/21234
## Bug Report
During consistency check based on shardingsphere-proxy + openGauss 3.1.0 (1
coordination node and two data nodes ), an exception is found in one of the
conditions.
```sql
select
c.c_w_id, c.c_d_id, c.c_id, c.c_balance,
(select sum(h_amount) as c_h_amount from bmsql_history h where
h.h_c_w_id = c.c_w_id and h.h_c_d_id = c.c_d_id and h.h_c_id = c.c_id),
(select sum(ol_amount) as c_ol_amount from bmsql_order_line ol,
bmsql_oorder o where ol.ol_w_id = o.o_w_id and ol.ol_d_id = o.o_d_id and
ol.ol_o_id = o.o_id
and ol.ol_delivery_d is not null
and o.o_w_id = c.c_w_id and o.o_d_id = c.c_d_id and o.o_c_id =
c.c_id)
from bmsql_customer c where c.c_w_id = 1 and c.c_d_id = 1 and c.c_id =
997;
```
To simplify the problem description, query SQL is simplified as follows:
c_w_id、c_d_id、c_id are primary keys of a customer, bmsql_history means
payment records for all users.
Use select(select) composite sql to query the sum of all payment records of
a customer, c_h_amount = 4055.82, only one datasource is requested.
```sql
select
c.c_w_id, c.c_d_id, c.c_id, c.c_balance,
(select sum(h_amount) as c_h_amount from bmsql_history h where
h.h_c_w_id = c.c_w_id and h.h_c_d_id = c.c_d_id and h.h_c_id = c.c_id)
from bmsql_customer c where c.c_w_id = 1 and c.c_d_id = 1 and c.c_id = 997;
```
Directly query the total payment amount of the user, c_amount = 18086.50,
two datasources were requested.
```sql
select * from bmsql_history h where h.h_c_w_id = 1 and h.h_c_d_id = 1 and
h.h_c_id = 997;
select sum(h_amount) from bmsql_history h where h.h_c_w_id = 1 and
h.h_c_d_id = 1 and h.h_c_id = 997;
```
### Which version of ShardingSphere did you use?
5.2.0 snapshot， commit d4c0048a5c3fd61625b3b502380c65b7e88cd927
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-Proxy
### Expected behavior
The total payment amount of the customer in the select(select) query is
equal to the total payment amount of the user in the direct query.
### Actual behavior
The total payment amount of the customer in the select(select) query,
sum(h_amount) = 4055.82, only one datasource is requested.
The total payment amount of the customer in the direct query, sum(h_amount)
= 18086.5, two datasources are requested.
### Reason analyze (If you can)
When executing select(select) composite sql, shardingshere determines the
target datasources based on th table bmsql_customer's rules, doesn't consider
the table bmsql_history's rules.
b2e6a1.png)
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule
configuration, when exception occur etc.
* steps to reproduce the behavior
1. execute benchmark for an hour
2. gsql to shardingsphere
3. execute `select sum(h_amount) from bmsql_history h where h.h_c_w_id = 1
and h.h_c_d_id = 1 and h.h_c_id = 997;`,check sum(h_amount)
4. execute `select
c.c_w_id, c.c_d_id, c.c_id, c.c_balance,
(select sum(h_amount) as c_h_amount from bmsql_history h where
h.h_c_w_id = c.c_w_id and h.h_c_d_id = c.c_d_id and h.h_c_id = c.c_id)
from bmsql_customer c where c.c_w_id = 1 and c.c_d_id = 1 and c.c_id =
997;`, check c_h_amount.
* table bmsql_customer
复杂查询数据源路由问题.assets/image-20220928153431397.png)
* table bmsql_history
复杂查询数据源路由问题.assets/image-20220928153514800.png)
* config-sharding.yaml
```config-sharding.yaml
databaseName: sharding_db
dataSources:
ds_0:
connectionTimeoutMilliseconds: 3600000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 410
minPoolSize: 400
ds_1:
connectionTimeoutMilliseconds: 3600000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 410
minPoolSize: 400
rules:
- !SHARDING
bindingTables:
- bmsql_warehouse, bmsql_customer
- bmsql_stock, bmsql_district, bmsql_order_line
broadcastTables:
- bmsql_item
defaultDatabaseStrategy:
standard:
shardingAlgorithmName: database_inline
shardingColumn: ds_id
defaultTableStrategy:
none: null
shardingAlgorithms:
database_inline:
props:
algorithm-expression: ds_${ds_id % 1}
type: INLINE
ds_inline_mod:
props:
sharding-count: 2
type: MOD
tables:
bmsql_config:
actualDataNodes: ds_${0..1}.bmsql_config
databaseStrategy:
standard:
shardingAlgorithmName: ds_inline_mod
shardingColumn: cfg_id
bmsql_customer:
actualDataNodes: ds_${0..1}.bmsql_customer
databaseStrategy:
standard:
shardingAlgorithmName: ds_inline_mod
shardingColumn: c_w_id
bmsql_district:
actualDataNodes: ds_${0..1}.bmsql_district
databaseStrategy:
standard:
shardingAlgorithmName: ds_inline_mod
shardingColumn: d_w_id
bmsql_history:
actualDataNodes: ds_${0..1}.bmsql_history
databaseStrategy:
standard:
shardingAlgorithmName: ds_inline_mod
shardingColumn: h_w_id
bmsql_new_order:
actualDataNodes: ds_${0..1}.bmsql_new_order
databaseStrategy:
standard:
shardingAlgorithmName: ds_inline_mod
shardingColumn: no_w_id
bmsql_oorder:
actualDataNodes: ds_${0..1}.bmsql_oorder
databaseStrategy:
standard:
shardingAlgorithmName: ds_inline_mod
shardingColumn: o_w_id
bmsql_order_line:
actualDataNodes: ds_${0..1}.bmsql_order_line
databaseStrategy:
standard:
shardingAlgorithmName: ds_inline_mod
shardingColumn: ol_w_id
bmsql_stock:
actualDataNodes: ds_${0..1}.bmsql_stock
databaseStrategy:
standard:
shardingAlgorithmName: ds_inline_mod
shardingColumn: s_w_id
bmsql_warehouse:
actualDataNodes: ds_${0..1}.bmsql_warehouse
databaseStrategy:
standard:
shardingAlgorithmName: ds_inline_mod
shardingColumn: w_id
t_order:
actualDataNodes: ds_${0..0}.t_order
```
### 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]
