MySQL Bugs: #34015: Problems with float fields using federated tables

Bug #34015	Problems with float fields using federated tables
Submitted:	23 Jan 2008 18:02	Modified:	11 Feb 2008 21:00
Reporter:	Rodrigo Serratto	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Federated storage engine	Severity:	S1 (Critical)
Version:	5.0.54	OS:	Linux
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any
Tags:	federated, FLOAT, problem

Description:
While using federated tables i facing problems while updating float fields.
it's really strange because when i insert data in main table using "." as decimal separator as operation in federated table fails.

i started a series of tests i found that no errors are given - rows affected runs as expected but the commit in main table did not happen.

Both servers have the same version and r using the same collation.

another test performed was: inserting register with integer values in float fields
all operations run as expected till updating data with a real float value....then just fails.

How to repeat:
//creating table in main server
// Main Server

CREATE DATABASE test_federated;
USE test_federated;
CREATE TABLE IF NOT EXISTS `test` (  `one` float(6,2) default NULL,  `two` float(6,2) default NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

// Federated
CREATE DATABASE test_federated;
USE test_federated;

CREATE TABLE IF NOT EXISTS `test_fed` (  `one` float(6,2) default NULL,  `two` float(6,2) default NULL) ENGINE=FEDERATED DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci CONNECTION='mysql://root:pass@HostMainServer/test_federated/test';

truncate test_fed;
insert into test_fed values (1,2);
insert into test_fed values (3,4);
insert into test_fed values (5.1,6.1);
insert into test_fed values (7,8);
insert into test_fed values ('9,1','10,1');

select * from test_fed;

+------+-------+
| one  | two  |+
 ------+-------+
| 1.00 |  2.00 |
| 3.00 |  4.00 |
| 5.10 |  6.10 |
| 7.00 |  8.00 |
| 9.00 | 10.00 |
+------+-------+

5 rows in set (0.01 sec)

update test_fed set one=10;

Query OK, 5 rows affected (0.01 sec)
Rows matched: 5  Changed: 5  Warnings: 0

select * from test_fed;

+-------+-------+
| one  | two  |
+-------+-------+
| 10.00 |  2.00 |
| 10.00 |  4.00 |
|  5.10 |  6.10 |   <-----here is the problem....it should be one=10.00
| 10.00 |  8.00 |         this is the only row with float values in insert
| 10.00 | 10.00 |         clause
+-------+-------+

5 rows in set (0.00 sec)

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.45 at least, and inform about the results.

I tested with 5.0.45.
The problem still happens.

i changeg all float fiels to decimal.
it works

Verified just as described with 5.0.54:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot test -P3308
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.54-enterprise-gpl-nt-log MySQL Enterprise Server (GPL)

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

mysql> CREATE DATABASE test_federated;
Query OK, 1 row affected (0.02 sec)

mysql> USE test_federated;
Database changed
mysql> CREATE TABLE IF NOT EXISTS `test` (  `one` float(6,2) default NULL,  `two
` float(6,2)
    -> default NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general
_ci;
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE TABLE IF NOT EXISTS `test_fed` (  `one` float(6,2) default NULL,
`two` float(6,2)
    -> default NULL) ENGINE=FEDERATED DEFAULT CHARSET=latin1 COLLATE=latin1_gene
ral_ci
    -> CONNECTION='mysql://root:root@localhost:3308/test_federated/test';
Query OK, 0 rows affected (0.06 sec)

mysql> insert into test_fed values (1,2);
Query OK, 1 row affected (0.08 sec)

mysql> insert into test_fed values (3,4);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_fed values (5.1,6.1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_fed values (7,8);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_fed values ('9,1','10,1');
ERROR 1265 (01000): Data truncated for column 'one' at row 1
mysql> insert into test_fed values ('9.1','10.1');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_fed;
+------+-------+
| one  | two   |
+------+-------+
| 1.00 |  2.00 |
| 3.00 |  4.00 |
| 5.10 |  6.10 |
| 7.00 |  8.00 |
| 9.10 | 10.10 |
+------+-------+
5 rows in set (0.00 sec)

mysql> update test_fed set one=10;
Query OK, 5 rows affected (0.03 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> select * from test_fed;
+-------+-------+
| one   | two   |
+-------+-------+
| 10.00 |  2.00 |
| 10.00 |  4.00 |
|  5.10 |  6.10 |
| 10.00 |  8.00 |
|  9.10 | 10.10 |
+-------+-------+
5 rows in set (0.00 sec)

workaround: use decimal instead of float

This is fixed in MySQL 6.0 (with dtoa).

Noted in 6.0.5 changelog.

Updates of floating-point columns in FEDERATED tables could produce
incorrect results.