MySQL Bugs: #113851: float type data query error

Bug #113851	float type data query error
Submitted:	1 Feb 2024 12:13	Modified:	18 Feb 2024 9:28
Reporter:	ximin liang (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.3.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
hello, i found a error about float type data.
it can be repeated in mysql 8.3.0
after debug, it happens in function my_gcvt
the input floating point number is -396623872, but the output string is -396624000

How to repeat:
create table t1(c1 float);
insert into t1(c1) values (-396623872);
mysql> select c1 from t1;
+------------+
| c1         |
+------------+
| -396624000 |
+------------+
1 row in set (0.00 sec)

mysql> select version();
+-------------+
| version()   |
+-------------+
| 8.3.0-debug |
+-------------+
1 row in set (0.00 sec)

Suggested fix:
check my_gcvt

may be it is about implement of dtoa function
result is not right since this func is called

Hi Mr. liang,

Thank you for your bug report.

However, this is not a bug.

According to the standard for floating point types, as set by IEEE, there are three types available. One with a precision of 8 digits, second one with a precision of 16 digits and third type with 20 digits.

You have used the type with 8 (eight) significant digits, while you have used 9 (nine) digits.

Try with a DOUBLE data type and you will get a better result.

Not a bug.

Dear Mysql team:
  Thanks for your patient reply and I got knowledge about IEEE 754.
  However, seems this is no precision loss when float point number -396623872 is processed.
  Under the IEEE 754 standard, this number is encoded as(can be calculated in https://www.h-schmidt.net/FloatConverter/IEEE754.html):
            Sign   Exponent          Mantissa
value        -1      2^28          1.4775390625
encode       1       155            4005888
binary       1     10011011   01111010010000000000000

  And we can calculated result (-1)^sign * 2^exponent * mantissa = -1 * 2^28 * 1.4775390625  = -396623872, it is accurate.
  I still think implement of dtoa function(convert double to ASCII string) in source code should be checked, in my debug, the input folat number is accurate but output string is not right. Here is part of backtrace when execute previous sql (source code 8.3.0):
 
#0  dtoa (dd=-396623872, mode=4, ndigits=6, decpt=0x7f55284f0790, sign=0x7f55284f078c, rve=0x7f55284f0780, buf=0x7f55284ef920 "", 
    buf_size=3680) at /data/code/mysql-server/strings/dtoa.cc:1964
#1  0x00000000056c4cff in my_gcvt (x=-396623872, type=MY_GCVT_ARG_FLOAT, width=341, to=0x7f55284f0830 "Dc\037\a", error=0x0)
    at /data/code/mysql-server/strings/dtoa.cc:313
#2  0x0000000003bb60b0 in floating_point_to_text (value=-396623872, decimals=31, gcvt_arg_type=MY_GCVT_ARG_FLOAT, 
    buffer=0x7f55284f0830 "Dc\037\a") at /data/code/mysql-server/sql/protocol_classic.cc:3398
#3  0x0000000003bb6105 in store_floating_point (value=-396623872, decimals=31, zerofill=0, gcvt_arg_type=MY_GCVT_ARG_FLOAT, 
    packet=0x7f547c009298) at /data/code/mysql-server/sql/protocol_classic.cc:3417
#4  0x0000000003bb620d in Protocol_text::store_float (this=0x7f547c08fa40, from=-396623872, decimals=31, zerofill=0)
    at /data/code/mysql-server/sql/protocol_classic.cc:3428
#5  0x000000000388aab3 in Field_float::send_to_protocol (this=0x7f547c0e93b0, protocol=0x7f547c08fa40)
    at /data/code/mysql-server/sql/field.cc:4190
#6  0x0000000003bb3196 in Protocol_classic::store_field (this=0x7f547c08fa40, field=0x7f547c0e93b0)
    at /data/code/mysql-server/sql/protocol_classic.cc:1283
#7  0x0000000003933f67 in Item_field::send (this=0x7f547c0e18c8, protocol=0x7f547c08fa40) at /data/code/mysql-server/sql/item.cc:8063
#8  0x0000000003404f2b in THD::send_result_set_row (this=0x7f547c0071c0, row_items=const mem_root_deque<Item*> & = {...})
    at /data/code/mysql-server/sql/sql_class.cc:2888

if execute `f 4` and `p from` in gdb, accurate value can be print
(gdb) p from 
$2 = -396623872

 Please consider it, thanks again.

Hi Mr. liang,

You are mixing some details here .....

Internally, floating point is always calculated with a maximum precision. That is why in any debugger you will see a correct value for 9 digits floating point number that is assigned to the floating point number with 8 significant digits.

When the calculus is completed, then rounding occurs in the FPU itself. That is also defined in IEEE standard.

This discussion is actually totally necessary. If you need more then eight significant digits, just use DOUBLE.

Not a bug.

Hi mysql team:
  Thank you for your patient reply before. I now realize the areas where I did not consider carefully enough.
  I found when send float number as text, mysql only ensures six significant digits. See following sqls:
  create table t1(c1 float);
  insert into t1 (c1) values (1234567);
  select c1 from t1 where c1 = 1234567;  // c1 result 1234570
  select c1 from t1 where c1 = 1234570; // empty result

  This is determined by the following line of code in func my_gcvt:
  ```cpp
   res = dtoa(x, 4, type == MY_GCVT_ARG_DOUBLE ? width : std::min(width, FLT_DIG), &decpt, &sign, &end, buf, sizeof(buf));
  ```
  FLT_DIG is defined as 6 in my environment. 
  I tried to modify this code to
  ```cpp
  res = dtoa(x, 4, type == MY_GCVT_ARG_DOUBLE ? width : width, &decpt, &sign, &end, buf, sizeof(buf));
  ```
  and result:
  select c1 from t1 where c1 = 1234567;  // c1 result 1234567
  
  I encountered some problems in my previous development work. Previous reply helped me solve these problems. Thanks again.

You are truly welcome ......