You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "gladydennyson (via GitHub)" <gi...@apache.org> on 2026/03/06 12:04:35 UTC
[I] Incorrect cross-shard merge result for arithmetic aggregate expression SUM(...) - SUM(...) [shardingsphere]
gladydennyson opened a new issue, #38363:
URL: https://github.com/apache/shardingsphere/issues/38363
I am seeing an issue with arithmetic aggregate expressions of the form:
SUM(...) - SUM(...)
when the query runs across multiple shards.
### Example query pattern
```sql
SELECT
SUM(CASE
WHEN t.record_date = DATE '2025-04-02' THEN t.value_col
ELSE 0
END)
- SUM(CASE
WHEN t.record_date = DATE '2025-03-26' THEN t.value_col
ELSE 0
END) AS delta_value
FROM source_table t;
```
### Example numeric scenario
Suppose the query runs across two shards.
On shard 1, the expression `SUM(a) - SUM(b)` evaluates to `1681`.
On shard 2, the expression `SUM(a) - SUM(b)` evaluates to `420`.
### Expected result
2101 (1681 + 420)
### Actual result
Only one shard's value is returned 1681 .
### Environment
ShardingSphere version: 5.5.2
Database: PostgreSQL with TimescaleDB extension
Some tables involved are hypertables.
### Simplified sharding rule
Tables are distributed across two data sources:
ds_0
ds_1
Example configuration:
```yaml
source_table:
actualDataNodes: ds_${0..1}.source_table
```
Other tables used in joins follow the same distribution pattern.
### Question
Is there any limitation with cross-shard merging for expressions like `SUM(...) - SUM(...)` that would explain why the final result is not matching the expected result above?
--
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
Re: [I] Incorrect cross-shard merge result for arithmetic aggregate expression SUM(...) - SUM(...) [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu closed issue #38363: Incorrect cross-shard merge result for arithmetic aggregate expression SUM(...) - SUM(...)
URL: https://github.com/apache/shardingsphere/issues/38363
--
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
Re: [I] Incorrect cross-shard merge result for arithmetic aggregate expression SUM(...) - SUM(...) [shardingsphere]
Posted by "gladydennyson (via GitHub)" <gi...@apache.org>.
gladydennyson commented on issue #38363:
URL: https://github.com/apache/shardingsphere/issues/38363#issuecomment-4071917485
Apologies for the delay in the response.
In our initial setup, we are using ShardingTableRuleConfiguration to map tables across two data sources:
ds_0
ds_1
For example (simplified):
tableA -> ds_${0..1}.tableA
tableB -> ds_${0..1}.tableB
tableC -> ds_${0..1}.tableC
We are not using a sharding key or sharding algorithm in this configuration. The same table structure exists on both data sources.
A single aggregate like:
`SELECT SUM(...) FROM table;`
correctly adds up results from both shards.
But when we use an arithmetic expression combining them like:
`SELECT SUM(...) - SUM(...) FROM table;`
the result is incorrect.
--
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
Re: [I] Incorrect cross-shard merge result for arithmetic aggregate expression SUM(...) - SUM(...) [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu commented on issue #38363:
URL: https://github.com/apache/shardingsphere/issues/38363#issuecomment-4012751160
Thanks for the report.
Could you please share the full effective ShardingSphere configuration (YAML or DistSQL), especially the sharding-key related parts?
--
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
Re: [I] Incorrect cross-shard merge result for arithmetic aggregate expression SUM(...) - SUM(...) [shardingsphere]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #38363:
URL: https://github.com/apache/shardingsphere/issues/38363#issuecomment-4058605077
There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.
--
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