Bug #109836 Decimal retention of results in string type data calculation
Submitted: 30 Jan 2023 12:11 Modified: 31 Jan 2023 7:44
Reporter: Chenglin Tian Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0.31, 8.0.32, 5.7.41 OS:MacOS
Assigned to: CPU Architecture:x86

[30 Jan 2023 12:11] Chenglin Tian 
Description:
When we use the division operator to calculate the result of string type and numeric type, we find that the result is inconsistent when the decimal is reserved

How to repeat:
The following is the test statement that we found the problem：
select 1/4; --0.2500
select '1'/4; --0.25

select 1/32; --0.03125
select '1'/32; --0.0313

Suggested fix:
Obviously, the numerical type calculation results will be retained to four decimal places, while the string type will not, but we expect the two calculation results to be consistent
[30 Jan 2023 13:21] MySQL Verification Team 
Hello Chenglin Tian,

Thank you for the report.

regards,
Umesh
[31 Jan 2023 7:44] Roy Lyseng 
Posted by developer:
 
This is not a bug.
The returned data type for a division between two integers is decimal.
The returned data type for a division between a string and an integer
is double precision. The semantics for decimal and double types are different,
and this is what causes the difference in results.