Re: [I] Get select sum decimal precision error [shardingsphere]

["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