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/10/10 06:41:24 UTC
[GitHub] [shardingsphere] cucuzi opened a new issue, #16787: Get select sum decimal precision error
cucuzi opened a new issue, #16787:
URL: https://github.com/apache/shardingsphere/issues/16787
## Bug Report
I select a list, but the decimal is wrong.
using shardingsphere-jdbc: 5.1.0
### Expected behavior
list string:
[BookClassStatistic(classChar=A, classNum=2, bookNum=3, sumPrice=89.10), BookClassStatistic(classChar=B, classNum=2, bookNum=3, sumPrice=121.60), BookClassStatistic(classChar=D, classNum=2, bookNum=2, sumPrice=38.80), BookClassStatistic(classChar=Z, classNum=1, bookNum=2, sumPrice=5.52)]
**By the way, changing database field ( book.price ) type to decimal will get true result.**
### Actual behavior
list string:
[BookClassStatistic(classChar=A, classNum=2, bookNum=3, sumPrice=89.10000228881836), BookClassStatistic(classChar=B, classNum=2, bookNum=3, sumPrice=121.5999984741211), BookClassStatistic(classChar=D, classNum=2, bookNum=2, sumPrice=38.79999923706055), BookClassStatistic(classChar=Z, classNum=1, bookNum=2, sumPrice=5.519999980926514)]
### Example codes
**SELECT SQL**
```
SELECT UPPER(LEFT(bg.class_no, 1)) class_char,
count(DISTINCT (bg.class_no)) class_num,
count(b.id) book_num,
sum(b.price) sum_price
FROM book b
JOIN bibliography bg ON b.bibliography_id = bg.id
WHERE b.user_id = ?
GROUP BY class_char
```
**Object**
```
@Data
public class BookClassStatistic {
private String classChar;
private Integer classNum;
private Integer bookNum;
private BigDecimal sumPrice;
}
```
**Sharding rule**
```
spring:
shardingsphere:
rules:
sharding:
tables:
book:
actual-data-nodes: db0.book_$->{0..2}
table-strategy:
standard:
sharding-column: user_id
sharding-algorithm-name: table-inline
key-generate-strategy:
column: id
key-generator-name: snowflake
sharding-algorithms:
table-inline:
type: INLINE
props:
algorithm-expression: book_$->{user_id % 3}
```
**SQL FILE**
[demo1.zip](https://github.com/apache/shardingsphere/files/8478431/demo1.zip)
--
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] github-actions[bot] closed issue #16787: Get select sum decimal precision error
Posted by GitBox <gi...@apache.org>.
github-actions[bot] closed issue #16787: Get select sum decimal precision error
URL: https://github.com/apache/shardingsphere/issues/16787
--
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] Get select sum decimal precision error [shardingsphere]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #16787:
URL: https://github.com/apache/shardingsphere/issues/16787#issuecomment-3564928508
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
[GitHub] [shardingsphere] strongduanmu commented on issue #16787: Get select sum decimal precision error
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #16787:
URL: https://github.com/apache/shardingsphere/issues/16787#issuecomment-1097582004
@cucuzi Thank you for your contribution, I will investigate this issue.
--
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] Get select sum decimal precision error [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu closed issue #16787: Get select sum decimal precision error
URL: https://github.com/apache/shardingsphere/issues/16787
--
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] github-actions[bot] commented on issue #16787: Get select sum decimal precision error
Posted by GitBox <gi...@apache.org>.
github-actions[bot] commented on issue #16787:
URL: https://github.com/apache/shardingsphere/issues/16787#issuecomment-1272350037
Hello , this issue has not received a reply for several days.
This issue is supposed to be closed.
--
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] Get select sum decimal precision error [shardingsphere]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #16787:
URL: https://github.com/apache/shardingsphere/issues/16787#issuecomment-2026020762
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
Re: [I] Get select sum decimal precision error [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu commented on issue #16787:
URL: https://github.com/apache/shardingsphere/issues/16787#issuecomment-3532552977
## Root Cause Analysis
After reviewing the issue details, this is not a ShardingSphere bug, but rather a database schema design issue. The key evidence is:
"The issue reporter notes that changing the database field (book.price) type to decimal resolves the problem."
This indicates:
1. The original field type was likely float or double
2. After changing to decimal type, the problem is resolved
3. This is a classic floating-point precision issue, not a ShardingSphere issue
## Technical Explanation
1. Floating-Point Precision Issue: float/double are binary floating-point numbers that cannot precisely represent decimal fractions
2. Solution: You should use decimal/numeric types to store precise decimal values
3. ShardingSphere Behavior: It faithfully passes through the results returned by the database without additional precision processing
## Recommendations
This is not a ShardingSphere bug, but a database design issue. My recommendations:
1. Modify Database Field Type: Change the price field from float/double to decimal(p,s) type:
ALTER TABLE book MODIFY COLUMN price DECIMAL(10,2);
2. Best Practice: Always use decimal type for scenarios requiring precise decimals such as financial calculations and amount storage
3. ShardingSphere Usage Guidelines:
- Ensure database table design meets precision requirements
- Use BigDecimal objects in Java to correspond to database decimal types
- ShardingSphere will handle these type conversions correctly
## Additional Information Needed (if issue persists)
If the problem still exists after changing to decimal type, please provide:
1. Database table structure (CREATE DDL statement for book table)
2. Exact data type of book.price field
3. Complete query SQL statement
4. ShardingSphere configuration file
5. Database type and version (MySQL, PostgreSQL, etc.)
## Summary
This is a classic floating-point precision issue that should be resolved by using the correct database field type rather than modifying ShardingSphere code.
--
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