MySQL Bugs: #64224: bug in fractional part of unsigned decimals

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

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)

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.

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)

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)

Syntax is accepted and leads to unexpected results. This is a bug.

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)

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)

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)
=

Same as
Bug #77636 decimal field takes in values from other fields
Fixed in 5.7.9