| Bug #17957 | inserts to double data type inserts incorrect data | ||
|---|---|---|---|
| Submitted: | 6 Mar 2006 10:44 | Modified: | 7 Mar 2006 10:44 |
| Reporter: | Jerry Walsh | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 5.0.16-log | OS: | FreeBSD (FreeBSD 6-STABLE) |
| Assigned to: | CPU Architecture: | Any | |
[6 Mar 2006 10:45]
Jerry Walsh
More info: Values less than 10 go in as 1 Values less than 100 go in as 10 Values less than 1000 go in as 100 This is true for me, for any column that's of type DOUBLE (like pricec is in the example i posted)
[6 Mar 2006 10:48]
Jerry Walsh
Another example:
mysql> create table test (
-> id int(32),
-> pricea varchar(255),
-> priceb float,
-> pricec double,
-> primary key(id)
-> ) charset=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> insert into test values ('1','6.66','2.55',2.55);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into test values ('2','6.66','2.55',12.55);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into test values ('3','6.66','2.55',112.55);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into test values ('4','6.66','2.55',1112.55);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql>
mysql> select * from test;
+----+--------+--------+--------+
| id | pricea | priceb | pricec |
+----+--------+--------+--------+
| 1 | 6.66 | 2.55 | 1 |
| 2 | 6.66 | 2.55 | 10 |
| 3 | 6.66 | 2.55 | 100 |
| 4 | 6.66 | 2.55 | 1000 |
+----+--------+--------+--------+
4 rows in set (0.00 sec)
mysql> drop table test;
Query OK, 0 rows affected (0.01 sec)
[6 Mar 2006 10:55]
Jerry Walsh
Here's another example with unquoted values going into the FLOAT column too !
mysql> create table test (
-> id int(32),
-> pricea varchar(255),
-> priceb float,
-> pricec double,
-> primary key(id)
-> ) charset=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> insert into test values ('1','6.66',2.55,2.55);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into test values ('2','6.66',12.55,12.55);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into test values ('3','6.66',112.55,112.55);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into test values ('4','6.66',1112.55,1112.55);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql>
mysql> select * from test;
+----+--------+---------+--------+
| id | pricea | priceb | pricec |
+----+--------+---------+--------+
| 1 | 6.66 | 2.55 | 1 |
| 2 | 6.66 | 12.55 | 10 |
| 3 | 6.66 | 112.55 | 100 |
| 4 | 6.66 | 1112.55 | 1000 |
+----+--------+---------+--------+
4 rows in set (0.00 sec)
[6 Mar 2006 11:10]
Hartmut Holzgraefe
can't reproduce on linux, maybe a FreeBSD port build issue?
[6 Mar 2006 11:11]
Valeriy Kravchuk
Thank you for a problem report. Sorry, but I was not able to repeat the problem you described on latest 5.0.20-BK on Linux (last example):
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.20 |
+-----------+
1 row in set (0.00 sec)
mysql> create table test (
-> id int(32),
-> pricea varchar(255),
-> priceb float,
-> pricec double,
-> primary key(id)
-> ) charset=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test values ('1','6.66',2.55,2.55);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values ('2','6.66',12.55,12.55);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values ('3','6.66',112.55,112.55);
Query OK, 1 row affected (0.01 sec)
mysql> insert into test values ('4','6.66',1112.55,1112.55);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+----+--------+---------+---------+
| id | pricea | priceb | pricec |
+----+--------+---------+---------+
| 1 | 6.66 | 2.55 | 2.55 |
| 2 | 6.66 | 12.55 | 12.55 |
| 3 | 6.66 | 112.55 | 112.55 |
| 4 | 6.66 | 1112.55 | 1112.55 |
+----+--------+---------+---------+
4 rows in set (0.00 sec)
So, please, use newer version, 5.0.18.
[7 Mar 2006 10:44]
Jerry Walsh
update .. the value is actually stored OK. If you convert to float it gets the orrect values upon a subsequent select statement:
mysql> create table test (
-> id int(32),
-> pricea varchar(255),
-> priceb float,
-> pricec double,
-> primary key(id)
-> ) charset=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> insert into test values ('1','6.66','2.55',2.55);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql>
mysql> select * from test;
+----+--------+--------+--------+
| id | pricea | priceb | pricec |
+----+--------+--------+--------+
| 1 | 6.66 | 2.55 | 1 |
+----+--------+--------+--------+
1 row in set (0.00 sec)
mysql>
mysql>
mysql> alter table test modify pricec float;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from test;
+----+--------+--------+--------+
| id | pricea | priceb | pricec |
+----+--------+--------+--------+
| 1 | 6.66 | 2.55 | 2.55 |
+----+--------+--------+--------+
1 row in set (0.01 sec)
mysql>
mysql>
mysql>
mysql> drop table test;
Description: Here's my show variables like '%version%'; +-------------------------+--------------- | Variable_name | Value +-------------------------+----------- | protocol_version | 10 | | version | 5.0.16-log | version_bdb | Sleepycat Software: Berkeley DB 4.1.24: (November 15, 2005) | version_comment | FreeBSD port: mysql-server-5.0.16 | | version_compile_machine | i386 | | version_compile_os | portbld-freebsd6.0 | +-------------------------+-------------------------------------------------------------+ How to repeat: mysql> create table test ( -> id int(32), -> pricea varchar(255), -> priceb float, -> pricec double, -> primary key(id) -> ) charset=utf8; Query OK, 0 rows affected (0.00 sec) mysql> mysql> insert into test values ('1','6.66','2.55',2.55); Query OK, 1 row affected (0.00 sec) mysql> mysql> mysql> select * from test; +----+--------+--------+--------+ | id | pricea | priceb | pricec | +----+--------+--------+--------+ | 1 | 6.66 | 2.55 | 1 | +----+--------+--------+--------+ 1 row in set (0.01 sec) mysql> drop table test; Suggested fix: Use float instead if its smaller size will suffice.