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/09/28 07:51:21 UTC
[GitHub] [shardingsphere] MrCong233 opened a new issue, #21234: The data source of the select (select) composite sql request is incorrect
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: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] FlyingZC commented on issue #21234: The data source of the select (select) composite sql request is incorrect
Posted by GitBox <gi...@apache.org>.
FlyingZC commented on issue #21234:
URL: https://github.com/apache/shardingsphere/issues/21234#issuecomment-1260652743
preview
use one ds:
use two ts:
--
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
[GitHub] [shardingsphere] FlyingZC commented on issue #21234: The data source of the select (select) composite sql request is incorrect
Posted by GitBox <gi...@apache.org>.
FlyingZC commented on issue #21234:
URL: https://github.com/apache/shardingsphere/issues/21234#issuecomment-1260662909
Now ss does not support subqueries, you can try the sql-federation experiment function.You can config the `federation-type: ADVANCED` param in server.yaml to enable the sql-federation.
--
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
[GitHub] [shardingsphere] terrymanu commented on issue #21234: The data source of the select (select) composite sql request is incorrect
Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #21234:
URL: https://github.com/apache/shardingsphere/issues/21234#issuecomment-1270277400
I just set the issue as `invalid` because the author close it without any reason.
--
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
[GitHub] [shardingsphere] FlyingZC commented on issue #21234: The data source of the select (select) composite sql request is incorrect
Posted by GitBox <gi...@apache.org>.
FlyingZC commented on issue #21234:
URL: https://github.com/apache/shardingsphere/issues/21234#issuecomment-1260666258
You can check the sharding support at this page:
https://shardingsphere.apache.org/document/current/cn/features/sharding/limitation/
--
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
[GitHub] [shardingsphere] MrCong233 closed issue #21234: The data source of the select (select) composite sql request is incorrect
Posted by GitBox <gi...@apache.org>.
MrCong233 closed issue #21234: The data source of the select (select) composite sql request is incorrect
URL: https://github.com/apache/shardingsphere/issues/21234
--
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