Bug #64224 bug in fractional part of unsigned decimals
Submitted: 3 Feb 2012 22:15 Modified: 4 Feb 2012 9:18
Reporter: Jim Rath Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S2 (Serious)
Version:5.1.52, 5.1.56, 5.5.20, 5.6.28 OS:Any
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D2 (Serious)

[3 Feb 2012 22:15] Jim Rath
Description:
There appears to be a bug in the way unsigned decimals' fractional part is handled in MySQL.  It may just be in the way the information in the table is displayed to the screen (or exported to CSV).

See attached create table statement.  Trying to insert a zero into an unsigned decimal(10,3) field works just fine, but if there's more than one column like that in a row, the first few get non-zero values.  The last column still gets the zero, though.

It may just be a display problem, oddly enough.  I have a bunch of tables and calculations too complicated to describe here, but when working with them it appears that the whole number part works just fine.  It's only the fractional part that gets wiggy.  Dunno if that helps diagnose the problem.

Also, in the example below I've used the memory engine.  However, I've also tried InnoDB and MyISAM and seen the same problem.

A workaround is to simply use a signed type.  The problem goes away then.

I've tried asking the Google if anyone else has experienced this problem, but I came up empty.

How to repeat:
mysql> create table foo (bar decimal(10,3) unsigned) engine=memory select 0.000 as bar;
Query OK, 1 row affected (0.33 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from foo;
+-------+
| bar   |
+-------+
| 0.000 |
+-------+
1 row in set (0.00 sec)

mysql> drop table foo;
Query OK, 0 rows affected (0.00 sec)

mysql> create table foo (biz decimal(10,3) unsigned, baz decimal(10,3) unsigned, buz decimal(10,3) unsigned) engine=memory select 0.000 as biz, 0.000 as baz, 0.000 as buz;
Query OK, 1 row affected (0.41 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from foo;
+-------+-------+-------+
| biz   | baz   | buz   |
+-------+-------+-------+
| 0.128 | 0.128 | 0.000 |
+-------+-------+-------+
1 row in set (0.00 sec)

mysql> drop table foo;
Query OK, 0 rows affected (0.01 sec)
[3 Feb 2012 22:33] Jim Rath
Oops: corrected the versions to read 5.1.52 and 5.1.56.  The 5.1.52 version I have running on Mac OS X 10.6.8.  The 5.1.56 version I have running on Redhat Enterprise Server 5.6.  They're both 64 bit.
[4 Feb 2012 8:48] Peter Laursen
Is the syntax you use valid for inserting to a table? I never saw it before.

If I do:

CREATE TABLE foo (biz DECIMAL(10,3) UNSIGNED, baz DECIMAL(10,3) UNSIGNED, buz
DECIMAL(10,3) UNSIGNED) ENGINE=MEMORY;
INSERT INTO foo VALUES (0,0,0);
SELECT * FROM foo;

/* I get the result

   biz     baz     buz  
------  ------  --------
 0.000   0.000     0.000
 */

Peter
(not a MySQL person)
[4 Feb 2012 8:55] Peter Laursen
but this (like yours)

DROP TABLE foo;
CREATE TABLE foo (biz DECIMAL(10,3) UNSIGNED, baz DECIMAL(10,3) UNSIGNED, buz
DECIMAL(10,3) UNSIGNED) ENGINE=MEMORY SELECT 0.000 AS biz, 0.000 AS baz, 0.000 AS buz;
SELECT * FROM foo;

/* returns

   biz     baz     buz  
------  ------  --------
 0.128   0.128     0.000*/

-- on all vesions (5.090, 5.1.60, 5.5.20)
[4 Feb 2012 9:18] Valeriy Kravchuk
Syntax is accepted and leads to unexpected results. This is a bug.
[3 Feb 2013 14:40] Shane Bester
The real problem is likely this: (5.5.29) valgrind output:

 Invalid write of size 1
at: decimal2bin (decimal.c:1284)
by: my_decimal2binary (my_decimal.cc:209)
by: Field_new_decimal::store_value(my_decimal const*) (field.cc:2634)
by: Field_new_decimal::reset (field.cc:2562)
by: mysql_create_frm (unireg.cc:1150)
by: rea_create_table (unireg.cc:519)
by: mysql_create_table_no_lock (sql_table.cc:4293)
by: create_table_from_items (sql_insert.cc:3720)
by: select_create::prepare (sql_insert.cc:3873)
by: JOIN::prepare (sql_select.cc:728)
by: mysql_select (sql_select.cc:2569)
by: handle_select (sql_select.cc:297)
by: mysql_execute_command (sql_parse.cc:2536)
by: mysql_parse (sql_parse.cc:5627)
by: dispatch_command(enum_server_command, THD*, char*, unsigned int) (sql_parse.cc:1037)
by: do_handle_one_connection(THD*) (sql_connect.cc:853)
by: handle_one_connection (sql_connect.cc:772)
by7D13: start_thread (pthread_create.c:309)
by168C: clone (clone.S:115)
 Address 0x2f0bd5a0 is 0 bytes after a block of size 16 alloc'd
at: malloc (vg_replace_malloc.c:270)
by: my_malloc (my_malloc.c:38)
by: mysql_create_frm (unireg.cc:1070)
by: rea_create_table (unireg.cc:519)
by: mysql_create_table_no_lock (sql_table.cc:4293)
by: create_table_from_items (sql_insert.cc:3720)
by: select_create::prepare (sql_insert.cc:3873)
by: JOIN::prepare (sql_select.cc:728)
by: mysql_select (sql_select.cc:2569)
by: handle_select (sql_select.cc:297)
by: mysql_execute_command(THD*) (sql_parse.cc:2536)
by: mysql_parse (sql_parse.cc:5627)
by: dispatch_command(enum_server_command, THD*, char*, unsigned int) (sql_parse.cc:1037)
by: do_handle_one_connection(THD*) (sql_connect.cc:853)
by: handle_one_connection (sql_connect.cc:772)
by7D13: start_thread (pthread_create.c:309)
by168C: clone (clone.S:115)
[3 Feb 2016 16:13] Shane Bester
this looks fixed in current version.

mysql> create table foo (biz decimal(10,3) unsigned, baz decimal(10,3) unsigned,
    -> buz decimal(10,3) unsigned) engine=memory select 0.000 as biz, 0.000 as baz, 0.000 as buz;
Query OK, 1 row affected (0.22 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from foo;
+-------+-------+-------+
| biz   | baz   | buz   |
+-------+-------+-------+
| 0.000 | 0.000 | 0.000 |
+-------+-------+-------+
1 row in set (0.09 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.12    |
+-----------+
1 row in set (0.01 sec)
[3 Feb 2016 16:15] Shane Bester
5.6.28 still shows valgrind error though.

Version: '5.6.28'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)
==21064== Thread 22:
==21064== Invalid write of size 1
==21064==    at 0xB792B8: decimal2bin (decimal.c:1450)
==21064==    by 0x66A82C: my_decimal2binary(unsigned int, my_decimal const*, unsigned char*, int, int) (my_decimal.cc:209)
==21064==    by 0x7E4DDD: Field_new_decimal::store_value(my_decimal const*) (field.cc:2691)
==21064==    by 0x7E50FF: Field_new_decimal::reset() (field.cc:2618)
==21064==    by 0x7999E9: make_empty_rec (unireg.cc:1164)
==21064==    by 0x7999E9: mysql_create_frm(THD*, char const*, char const*, char const*, st_ha_create_information*, List<Create_field>&, unsigned int, st_key*, handler*) (unireg.cc:310)
=
[4 Feb 2016 10:22] Tor Didriksen
Same as
Bug #77636 decimal field takes in values from other fields
Fixed in 5.7.9