MySQL Bugs: #24907: unpredictable (display) precision, if input precision increases

Bug #24907	unpredictable (display) precision, if input precision increases
Submitted:	8 Dec 2006 12:19	Modified:	14 Dec 2007 17:25
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.32-BK, 4.1.22	OS:	Linux (Linux, freebsd)
Assigned to:	Tatiana Azundris Nuernberg	CPU Architecture:	Any
Tags:	FLOAT, precision

Description:
This bug is not about the result calculated, but about the precission used to display it.

Also the numbers in the example work on my pc, but other numbers may be needed, depending on the enviroment mysql was compiled under / is running in

on http://dev.mysql.com/doc/refman/4.1/en/arithmetic-functions.html

the following is documented:
* In multiplication and division, the precision of the result when using two
integer values is the precision of the first argument + the value of the
div_precision_increment global variable. For example, the expression 5.05 /
0.0014 would have a precision of six decimal places (4.047976).

The first example:
 select 1.0000000000000000000000000001 / 10;
 results in 0.100000000000000005551115123126
but
 select 1.00000000000000000000000000001 / 10;
 results in 0.1

Even if the result in the 2nd case is 0.1, where are the trailing 0 (because the input request a higher precission.

To show that mysql is not normally cutting of trailing zeros
 select 1.00000 / 10;
 results in 0.1000000

There are also cases where the precission is cut to any other amount of less digits.

---
The 2nd example.
This happens without divisio to:
(depending on your enviroment, you may need differnt numbers / differnt precission)

Please note, that the 2nd number is the same as the 1st, except for one extra trailing zero, to increase the precission. They both convert to the same float number, mysql returns true, if comparing them with "="

 select 0.999999999999999888977697537484;
 returns 0.999999999999999888977697537484

 select 0.9999999999999998889776975374840;
 returns 1 # precission = 0 digits after point (therefore rounded)

 select 0.999999999999999888977697537484 = 0.9999999999999998889776975374840;
 returns 1

How to repeat:
select 1.0000000000000000000000000001 / 10;
select 1.00000000000000000000000000001 / 10;
select 1.00000 / 10;

select 0.999999999999999888977697537484;
select 0.9999999999999998889776975374840;
select 0.999999999999999888977697537484 = 0.9999999999999998889776975374840;

Suggested fix:
if the precission exceeds the maximum precission that mysql can handle/display, use the maximum precission, instead of cuttting it to a lower precission

I would expect
 select 0.9999999999999998889776975374840;
to either
  return 0.999999999999999888977697537484
  # as in the lower precission example

 or to return 1.00000000000000000000000000
 # with the appropriate precission

the 1st one prefered
 

mysql 5 seems to be fine

Create table shows additional info.
Look at the 2nd example, which does not have any precission in the field definitions, while all other examples have.

create table bb1 select 0.999999999999999888977697537484 as a ; desc bb1; drop table bb1;
+-------+---------------+------+-----+----------------------------------+-------+
| Field | Type          | Null | Key | Default                          | Extra |
+-------+---------------+------+-----+----------------------------------+-------+
| a     | double(32,30) |      |     | 0.000000000000000000000000000000 |       |
+-------+---------------+------+-----+----------------------------------+-------+

create table bb1 select 0.9999999999999998889776975374840 as a ; desc bb1; drop table bb1;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| a     | double |      |     | 0       |       |
+-------+--------+------+-----+---------+-------+

create table bb1 select 0.99999999999999988897769753748400 as a ; desc bb1; drop table bb1;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| a     | double(34,0) |      |     | 0       |       |
+-------+--------------+------+-----+---------+-------+

create table bb1 select 0.999999999999999888977697537484000 as a ; desc bb1; drop table bb1;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| a     | double(35,1) |      |     | 0.0     |       |
+-------+--------------+------+-----+---------+-------+

Thank you for a bug report. All your examples besides first one even lead to server crashes if 5.0.32-debug on 32-bit Linux is used. So, it is a serious bug, really:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.32-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table bb1 select 0.999999999999999888977697537484 as a ; desc bb1
;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

+-------+----------------+------+-----+----------------------------------+------
-+
| Field | Type           | Null | Key | Default                          | Extra
 |
+-------+----------------+------+-----+----------------------------------+------
-+
| a     | decimal(31,30) | NO   |     | 0.000000000000000000000000000000 |
 |
+-------+----------------+------+-----+----------------------------------+------
-+
1 row in set (0.01 sec)

mysql> drop table bbl;
ERROR 1051 (42S02): Unknown table 'bbl'
mysql> drop table bb1;
Query OK, 0 rows affected (0.00 sec)

mysql> create table bb1 select 0.9999999999999998889776975374840 as a ; desc bb
1;
ERROR 2013 (HY000): Lost connection to MySQL server during query
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mys
ql.sock' (111)
ERROR:
Can't connect to the server

mysql> select version();
No connection. Trying to reconnect...
Connection id:    1
Current database: test

+--------------+
| version()    |
+--------------+
| 5.0.32-debug |
+--------------+
1 row in set (0.00 sec)

mysql> exit
Bye
openxs@suse:~/dbs/5.0> tail -50 var/suse.
suse.err      suse.err-old  suse.log      suse.pid
openxs@suse:~/dbs/5.0> tail -50 var/suse.err
Cannot determine thread, fp=0x428d49e4, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x81d9338
0xffffe410
0x401b6b75
0x401ae903
0x81b1449
0x823d5b8
0x823da3e
0x8253f83
0x82543c1
0x822b2e0
0x822fc30
0x822a8e7
0x81f23c3
0x81f9103
0x81efa61
0x81ef2b2
0x81ee49e
0x40050aa7
0x40247c2e
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/using-stack-trace.html and follow
instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x8cc8410 = create table bb1 select 0.9999999999999998889776975374
840 as a
thd->thread_id=3
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.
...

Same for all other tests.

Resolved stack trace:

openxs@suse:~/dbs/5.0> bin/resolve_stack_dump  -s /tmp/mysqld50.sym -n 24907.st
ack
0x81d9338 handle_segfault + 412
0xffffe410 _end + -142012784
0x401b6b75 _end + 933533173
0x401ae903 _end + 933499779
0x81b1449 _ZN17Field_new_decimalC1EjbPKcP8st_tablehb + 191
0x823d5b8 _Z26create_tmp_field_from_itemP3THDP4ItemP8st_tablePPS2_bj + 672
0x823da3e _Z16create_tmp_fieldP3THDP8st_tableP4ItemNS3_4TypeEPPS4_PP5FieldSA_bbb
bj + 794
0x8253f83 _Z23create_table_from_itemsP3THDP24st_ha_create_informationP13st_table
_listP4ListI12create_fieldEPS5_I3KeyEPS5_I4ItemEPP13st_my + 465
0x82543c1 _ZN13select_create7prepareER4ListI4ItemEP18st_select_lex_unit + 109
0x822b2e0 _ZN4JOIN7prepareEPPP4ItemP13st_table_listjS1_jP8st_orderS7_S1_S7_P13st
_select_lexP18st_select_lex_unit + 2346
0x822fc30 _Z12mysql_selectP3THDPPP4ItemP13st_table_listjR4ListIS1_ES2_jP8st_orde
rSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_sel + 458
0x822a8e7 _Z13handle_selectP3THDP6st_lexP13select_resultm + 339
0x81f23c3 _Z21mysql_execute_commandP3THD + 4643
0x81f9103 _Z11mysql_parseP3THDPcj + 475
0x81efa61 _Z16dispatch_command19enum_server_commandP3THDPcj + 1951
0x81ef2b2 _Z10do_commandP3THD + 526
0x81ee49e handle_one_connection + 982
0x40050aa7 _end + 932066599
0x40247c2e _end + 934127278

1 - The quote in the original bug report is correct for mysqld 5.x only, as it pertains to precision math. 4.x does not have div_precision_increment etc. If we accidentally suggested otherwise in the 4.1 docs, this has been rectified since.

2 - Current 5.0 bails with assert() as Valeriy demonstrated; this deplorable state is rectified by attached ChangeSet.

3 - With ChangeSet in place, 5.0 returns the results expected by reporter, up to a precision of 30 decimal places:

DECIMAL[(M[,D])] ... M is the total number of digits (the precision) and D is the number of digits after the decimal point (the scale). ... The maximum number of digits (M) for DECIMAL is 65 (64 from 5.0.3 to 5.0.5). The maximum number of supported decimals (D) is 30.
http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html

semi-related to Bug#29417, but cannot be fixed the same way.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/35317

ChangeSet@1.2530, 2007-10-11 09:04:22+02:00, tnurnberg@sin.intern.azundris.com +3 -0
  Bug#24907: unpredictable (display) precission, if input precission increases
  
  Server failed in assert() when we tried to create a DECIMAL() temp field
  with a scale of more than the allowed 30. Now we limit the scale to the
  allowed maximum. If overall field length is exceeded, we lose fractional
  rather than integer places. A truncation warning is thrown in both cases.

Note that the crash can be demonstrated on a statement that doesn't use CREATE TABLE: 

create table t1(a int);
insert into t1 values (1),(2),(3),(4);
select 0.9999999999999998889776975374840 + a as Z from t1 group by Z;

The last statement causes mysqld to fail the same assertion in the same way.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/37993

ChangeSet@1.2530, 2007-11-17 19:05:31+01:00, tnurnberg@mysql.com +3 -0
  Bug#24907: unpredictable (display) precission, if input precission increases
  
  Server failed in assert() when we tried to create a DECIMAL() temp field
  with a scale of more than the allowed 30. Now we limit the scale to the
  allowed maximum. A truncation warning is thrown as necessary.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/37994

ChangeSet@1.2585, 2007-11-17 19:42:16+01:00, tnurnberg@mysql.com +2 -0
  Bug#24907: unpredictable (display) precission, if input precission increases
  
  post-merge fixes: some things work differently in 5.1

pushed to 5.0.52, 5.1.23 in maint

Pushed into 6.0.5-alpha

Pushed into 5.1.23-rc

Pushed into 5.0.54

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented in the 5.0.54, 5.1.23, and 6.0.5 changelogs as follows:

        If the expected precision of an arithmetic expression exceeded
        the maximum precision supported by MySQL, the precision of the
        result was reduced by an unpredictable or arbitrary amount,
        rather than to the maximum precision. In some cases, exceeding
        the maximum supported precision could also lead to a crash of
        the server.

Pushed into 5.1.37 (revid:joro@sun.com-20090708131116-kyz8iotbum8w9yic) (version source revid:alexey.kopytov@sun.com-20090703104354-pmbuhg3gv6pessx5) (merge vers: 5.1.37) (pib:11)

Pushed into 5.1.37 (revid:joro@sun.com-20090708131116-kyz8iotbum8w9yic) (version source revid:alexey.kopytov@sun.com-20090703104354-pmbuhg3gv6pessx5) (merge vers: 5.1.37) (pib:11)

Pushed into 5.4.4-alpha (revid:anozdrin@bk-internal.mysql.com-20090710111017-bnh2cau84ug1hvei) (version source revid:alexey.kopytov@sun.com-20090703104813-ls11ni4hyit20rw0) (merge vers: 5.4.4-alpha) (pib:11)

Pushed into 5.1.37-ndb-7.0.8 (revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)

Pushed into 5.1.37-ndb-6.3.27 (revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (version source revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)

Pushed into 5.1.37-ndb-6.2.19 (revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (version source revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)

Pushed into 5.1.35-ndb-7.1.0 (revid:magnus.blaudd@sun.com-20090827163030-6o3kk6r2oua159hr) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)