Unable To Update Row With Float Field(s)

[vygi]

Right now I has tried to update some table data straight from the Table Data grid and it has failed.

My table consist of 20 rows, one of them is with data type float.

It HAS primary key which is integer and auto_increment.

When I try to update some data, SQLyog generates a statement which says:

UPDATE

SET field1=value1, field2=value2, … fieldN=valueN

WHERE field1=value1 AND field2=value2 AND … AND fieldN=valueN.

It fails because it includes also float field in the WHERE part (“where price=10.50”).

I think that in this case it only should use primary key (WHERE id=NNN) and not all possible fields, or am I wrong????

It probably affects not only 5.1 beta/RC.

I can also provide table structure if needed.

[peterlaursen]

I think that in this case it only should use primary key

>> I have also wondered (and asked and protested actually!) why this was changed in 5.1. 5.02 used the PK. This is due to support for DEFAULT (such as CURRENT_TIMESTAMPs on update CURRENT_TIMESTAMP). Such columns should be omitted in the insert. But still PK should be used in the 'where' part. And in case there is no PK a FLOAT must be omitted in the 'where'. Because it is never = !! se below.

or am I wrong????

>>> no I think you are right. Floats are not 'accurate'. It could be 10.5000007 and that is <> from 10.50 and the 'where' is not fulfilled.

And as I told Ritesh: Imagine a 4 GB LONGBLOB in a WHERE … it is completely unneeded.

[peterlaursen]

I remeber the first program I wrote 30 years ago was for a TI calculator.

It went into an infinite loop because of something like

start

t=9

n = 1

:loop

t=t/3

n = n+1

if not t = 0,001 goto loop

output n

end

FLOATs in a nutshell … it could still have been executing the calculation '0/3' 5 times per second (that was fast !!!) if there still was power … maybe it does somewhere .. don't remember what happend to that machine 🙂

EDIT: Nope .. I remember – it was an OLIVETTI sized as an ordinary PC keyboard today – only 4 times higher! Cost around 2000 USD. We were two highschools sharing one of those… and it knew about FLOATS 🙂

[Ritesh]

We dont use BLOB column for WHERE clause. As far as I remember, SQLyog only uses PRIMARY KEY if its available. I will check it up again now.

If a TABLE has PK, then SQLyog should not make any attempt to use other column.

Is the key PK or UNIQUE KEY because we dont consider UNIQUE KEY as PK. We plan to change it in v5.2 but not now.

@vygi: Can you post the CREATE TABLE statement of your table?

[peterlaursen]

“SQLyog only uses PRIMARY KEY if its available.”

It does not. It was changed with 5.1 BETA1 or possibly BETA2.

I believe it is some confusion due to DEFAULTs implementation.

Check your mailbox! I pointed to this 3 weeks ago or more! 🙂

Or 2 months ago probably.

[Ritesh]

Looks like a BUG coz in Result Tab it is working perfectly. Will be fixed in RC3, which is another 5 hours away.

[peterlaursen]

Yes it is true that it is OK in RESULT tab … I also noticed this …

[Ritesh]

Fixed in RC3 development tree.

[Ritesh]

RC3 at http://www.webyog.com/forums/index.php?sho…9745&#entry9745 fixes this issue. Can you confirm it?

[peterlaursen]

With a PK like

Code:

CREATE TABLE `pladder` (
`id` bigint(20) NOT NULL auto_increment,
`t` varchar(50) default NULL,
`f` float default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

there is no issue.

Without a PK like

Code:

CREATE TABLE `pladder` (
`id` bigint(20) default NULL,
`t` varchar(50) default NULL,
`f` float(6,4) default NULL — or another M,D setting (even plain nothing)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

I cannot reproduce an issue either, though I think it should be possible! And it annoys me that I can't 😡

Even this

Code:

update pladder set f = 10/3 where id = 1 — from SQL-pane
update `pladder` set `id`='1',`t`='dfd',`f`='44' where `id`='1' and `t`='dfd' and `f`='3.3333' — from GUI

does not raise an issue. And I dont understand because I think that f is not exactly 3.3333 internally and an error should arise.

@Vygi — comments? Can you create a definition and a sequence of SQL's generating an error when there is no PK ? (With a PK it should not be possible now)

[vygi]

Quote:

RC3 at http://www.webyog.com/forums/index.php?sho…65533;entry9745 fixes this issue. Can you confirm it?

Yes it works now: in same table it uses the PK only.

peterlaursen wrote on Apr 21 2006, 12:16 AM:

@Vygi — comments? Can you create a definition and a sequence of SQL's generating an error when there is no PK ? (With a PK it should not be possible now)

I have removed primary keys from the table I have and can't update anything anymore because of float in WHERE. I connot explain why you can'r reproduce it…. Maybe try this before updating:

Code:

UPDATE pladder SET f=1/7

[Ritesh]

Float and double datatypes are known to have such issues.

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

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

[peterlaursen]

“not even as a part of a WHERE clause.”

But you do ?!

[Ritesh]

Yes we are doing it now but after yesterday's issue, we plan to change it in v5.2.

[Author]

Posts