Float Problem

[peterlaursen]

Jan Romanski proposed this a a FAQ entry. I think it is a Forums issue!

After creating a table in SQLYog with 'float' fields it is impossible to edit data in a grid (UPDATE query works ok). After changing a field type from 'float' to decimal corrects this problem. Any suggestions?

I can easily do so from DATA and RESULT pane as well.

From result-pane you must choose the table from the drop-down where it displays 'Read Only'. Was that it?

[CalEvans]

peterlaursen wrote on Mar 8 2006, 07:42 PM:

From result-pane you must choose the table from the drop-down where it displays 'Read Only'. Was that it?

That's been the case as long as this feature has been enabled. (4.x?) You've always had to select the table name instead of ReadOnly to be able to update.

=C=

[peterlaursen]

Just let me add that the primary reason for this is that RESULT pane may display colums from more tables (when using a SELECT … JOIN (implicit or explicit JOIN) statement). There might even be identically named columns with identical datatypes. So this protects against updating the wrong table.

[Ritesh]

Nobody replied to Jan's actual problem. His problem is that if he has a FLOAT datatype as a PK or part of a composite PK, then SQLyog uses that column in the where clause. Now I had a talk with the MySQL developers on the same subject. There are many issues when you use FLOAT datatype in a where clause.

Quote:

Try issuing the following query on both the Linux and Windows machines.

SELECT FORMAT(id, 0) FROM tablename1;

In my testing on Windows I received the following:

mysql> SELECT format(id, 0) FROM tablename1;

+

---

+

| format(id, 0) |

+

---

+

| 9,999,100,476,915,712 |

+

---

+

This shows that number provided is not stored exactly when using FLOAT. How/what is stored depends upon both the Operating System and the CPU. More on this can be found in the documentation at http://dev.mysql.com/doc/mysql/en/problems-with-float.html

This is general problem with floating point types. They are:

1) Stored as approximate values

2) Platform dependent

This problem is covered in MySQL Manual at http://dev.mysql.com/doc/mysql/en/problems-with-float.html

Besides everything floating point types should be *never* used as a Primary Key not even as a part of composite PK.

This is why when SQLyog does a WHERE clause against a FLOAT datatype it fails. Since this issue has been taken up, here is another comment by a MySQL developer:

Quote:

Explain to your customers that using FLOAT such way is like shooting themselves in their left leg. If they really insist on doing that neither MySQL nor SQLyog can stop them, but we strongly recommend finding alternative solution.

Thus, changing the data type from FLOAT to DECIMAL works.

His update query works because 99.99%, he is not using the column in the WHERE clause when he is writing the UPDATE SQL query.

[Author]

Posts