MySQL Bugs: #3356: bug in field type float

Bug #3356	bug in field type float
Submitted:	1 Apr 2004 4:14	Modified:	27 Apr 2004 8:28
Reporter:	Junior santiago	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MaxDB	Severity:	S1 (Critical)
Version:	4.0.18	OS:	Linux (Linux Intel X86)
Assigned to:	michel verwey	CPU Architecture:	Any

Description:
Hi,
When I make the following one: 

create table test(vlgtinicial decimal(12,2) not null, vlgtfinal float(12,2) not null); 

insert into test(vlgtinicial, vlgtfinal) values(764250.80,764250.80); 

select * from test 

We get the following result in select: 
vlgtinicial    vlgtfinal    
-------------- ------------ 
764250.80      764250.81    

Observes that in the vlgtfinal field that is of the type float the Database  return the wrong value.

Thank's

     Júnior
jrveiga@mma.com.br

How to repeat:
Hi,
When I make the following one: 

create table test(vlgtinicial decimal(12,2) not null, vlgtfinal float(12,2) not null); 

insert into test(vlgtinicial, vlgtfinal) values(764250.80,764250.80); 

select * from test 

We get the following result in select: 
vlgtinicial    vlgtfinal    
-------------- ------------ 
764250.80      764250.81    

Observes that in the vlgtfinal field that is of the type float the Database  return the wrong value.

Thank's

     Júnior
jrveiga@mma.com.br

We have java code which creates tables, then inserts values, including values of type float, into those tables. Furthermore, we have a MySQL server, version 4.0.16, on an Apple Xserve box (OS X) and another MySQL server, version 4.0.17, on a Windows 2000 (Intel) machine. The CREATE TABLE command includes lines such as 
	`altitude` float default NULL,
	`airspeed` float default NULL,
	`heading` float default NULL,
	`groundTrackAngle` float default NULL,
	`groundspeed` float default NULL,
The INSERT statements then fill records for the table with values, includng values for the items listed above (altitude, airspeed,heading, etc.) Tables are of type MyISAM.

Sometimes we have run our java routines using the same data, intending to create identical tables on each of our MySQL servers. So we were surprised to discover that, for data of type float, a SELECT call which should return identical data from both servers sometimes returns different values. 

To confirm this, we ran a script which compares data in tables, SELECT call by SELECT call. A few lines of the resulting output are below. The top line is a Header, which describes the output fields. Following the Header are pairs of output rows, which should be identical, but which differ for a float value. For example, the 1st pair of rows shows a difference in the field 'altitiude' of '28432.3' and '28432.2', while the second pair shows a difference in the field 'airspeed' of '250.563' and '250.562'. The database s1_run101_1cpu_5gm_4800 is on the Windows MySQL server while run101_opt is on the Apple Xserve MySQL server. When we looked at the input data, we saw what appears to be a rounding discrepency. For example an inserted value of '28432.25' gives '28432.2' on Xserve MySQL and '28432.3' on Windows MySQL.

Database, Row,'airspeed', 'altitude', 'flightId', 'groundTrackAngle', 'groundspeed', 'heading', 'isInFlight', 'latitude', 'longitude', 'onRouteStatus', 'sequenceId', 'simulationTime', 
s1_run101_1cpu_5gm_4800,380,'209.923', '28432.3', '3', '-61.5933', '325.3', '298.407', '1', '41.2456800897', '-74.8978999195', '0', '43', '1058312400000', 
run101_opt,380,'209.923', '28432.2', '3', '-61.5933', '325.3', '298.407', '1', '41.2456800897', '-74.8978999195', '0', '43', '1058312400000', 

s1_run101_1cpu_5gm_4800,138243,'250.563', '10547.8', '1456', '-179.34', '291.84', '180.66', '1', '38.9167542474', '-80.990784742', '0', '44', '1058315760000', 
run101_opt,138243,'250.562', '10547.8', '1456', '-179.34', '291.84', '180.66', '1', '38.9167542474', '-80.990784742', '0', '44', '1058315760000', 

Additionally, a separate, possibly related discrepency appeared when we tried to follow up on this discovery by creating test tables and inserting float values by hand. A TABLE CREATE statement was created, setting floats
  `altitude` float(10,5) default NULL,
  `airspeed` float(10,5) default NULL,
  `heading` float(10,5) default NULL,
  `groundTrackAngle` float(10,5) default NULL,
  `groundspeed` float(10,5) default NULL,

On Windows, an INSERT for 'altitude' of 33000.055 resulted in SELECT showing a value of 33000.05469 and an INSERT for 'altitude'  of 19000.062 resulted in SELECT showing a value of 19000.0625.

here is my experience:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.0.17

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

mysql> use cb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table temp (a float(13,2) not null);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into temp (a) values (34182330.0);
Query OK, 1 row affected (0.00 sec)

mysql> select * from temp;
+-------------+
| a           |
+-------------+
| 34182328.00 |
+-------------+
1 row in set (0.00 sec)

mysql> insert into temp (a) values (34182430.0);
Query OK, 1 row affected (0.00 sec)

mysql> select * from temp;
+-------------+
| a           |
+-------------+
| 34182328.00 |
| 34182432.00 |
+-------------+
2 rows in set (0.16 sec)

This is expected behaviour for 'float'

The range of 'float' is not big enough to hold the given values. The different values you get on retrieval is 'equvialent' values, according to the precisions that "float" provides.

Fix: Change to use 'double' instead.

Although this is a long-winded example, this is my table and insert statement:

CREATE TABLE jobs
(
  job_id int(11) auto_increment not null,
  job_title varchar(100) not null,
  job_details text not null,
  job_hourly_rate double(2,2) null,
  job_yearly_rate int(8) null,
  job_hours int(3) null,
  job_duration enum('permanent','temporary') not null,
  job_date_added datetime not null,
  job_closing_date datetime null,
  job_of_the_week boolean null,
  emp_name varchar(100) not null,
  emp_address varchar(100) null,
  emp_address2 varchar(100) null,
  emp_city varchar(100) null,
  emp_county varchar(100) null,
  emp_postcode varchar(10) null,
  emp_country varchar(100) null,
  emp_phone varchar(18) null,
  emp_mobile varchar(18) null,
  emp_fax varchar(18) null,
  emp_email_contact varchar(100) null,
  PRIMARY KEY(job_id)
);

insert into jobs(job_title,job_details,job_hourly_rate,job_yearly_rate,job_hours,job_duration,job_date_added,job_closing_date,job_of_the_week,emp_name,emp_address,emp_address2,emp_city,emp_county,emp_postcode,emp_country,emp_phone,emp_mobile,emp_fax,emp_email_contact)
values("web dev","the hello world",2.22,130000,100,'temp','10-10-2006','10-10-2006',0,"performance house","aaaaaaaaaaaaaaaaa the street","bbbbbbbbbbbbb street","cty","county","p3jd3p4","eng",0000000000,3333333333333,000000000000,"a@gggggggg.com");

All fields are correct except the double field. I tried it as float and I got the same result. Originally I was getting the error:

"Out of range value adjusted for column"

But I'm using 2.22 in a field that has a length of 00.00 which should be fine shouldn't it? I looked up the above bug and was informed that the my.ini file's sql-mode should be changed out of strict to the following value.

sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

I did this and the error went away, the data added, but the data was wrong. I've change to double as someone said here but I still get the wrong data. I don't think my sql is wrong.

Although this is a long-winded example, this is my table and insert statement:

CREATE TABLE jobs
(
  job_id int(11) auto_increment not null,
  job_title varchar(100) not null,
  job_details text not null,
  job_hourly_rate double(2,2) null,
  job_yearly_rate int(8) null,
  job_hours int(3) null,
  job_duration enum('permanent','temporary') not null,
  job_date_added datetime not null,
  job_closing_date datetime null,
  job_of_the_week boolean null,
  emp_name varchar(100) not null,
  emp_address varchar(100) null,
  emp_address2 varchar(100) null,
  emp_city varchar(100) null,
  emp_county varchar(100) null,
  emp_postcode varchar(10) null,
  emp_country varchar(100) null,
  emp_phone varchar(18) null,
  emp_mobile varchar(18) null,
  emp_fax varchar(18) null,
  emp_email_contact varchar(100) null,
  PRIMARY KEY(job_id)
);

insert into jobs(job_title,job_details,job_hourly_rate,job_yearly_rate,job_hours,job_duration,job_date_added,job_closing_date,job_of_the_week,emp_name,emp_address,emp_address2,emp_city,emp_county,emp_postcode,emp_country,emp_phone,emp_mobile,emp_fax,emp_email_contact)
values("web dev","the hello world",2.22,130000,100,'temp','10-10-2006','10-10-2006',0,"performance house","aaaaaaaaaaaaaaaaa the street","bbbbbbbbbbbbb street","cty","county","p3jd3p4","eng",0000000000,3333333333333,000000000000,"a@gggggggg.com");

All fields are correct except the double field. I tried it as float and I got the same result. Originally I was getting the error:

"Out of range value adjusted for column"

But I'm using 2.22 in a field that has a length of 00.00 which should be fine shouldn't it? I looked up the above bug and was informed that the my.ini file's sql-mode should be changed out of strict to the following value.

sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

I did this and the error went away, the data added, but the data was wrong. I've change to double as someone said here but I still get the wrong data. I don't think my sql is wrong.