Can someone check the status of update if the bind parameter for double decimal values?

I'm getting unexpected rounding errors after updating - the data in the column looks different than what was specified during the update statement.

Value: double;
Query.Params.ParamByName(sName).AsFloat:=Value;

1.) Update Value : 40734.825668912039 2.) Actual Value after update : 40734.825668912
3.) Actual Value on read : 40734.825668912003

As far as I know this is a relatively new problem. Could be with Ubuntu but was someone doing MYSQL work lately?

Does MySQL come with an "official" program that you can use to run queries manually? What happens when you use it to store and retrieve that number?

I think that SQL specifies that a double has 53 bits in the mantissa which is a bit better than 15 digits which is about what you're seeing.

Several things here:
- floattostr(40734.825668912039) equals 40734.825668912.
- update table set t_double = 40734.825668912039 where id =123; in the mysql client or the MySQL browser returns 40734.825668912 on a subsequent select (mysql 5.0.51 ubuntu)
- 40734.825668912003 and 40734.825668912039 are beyond the precision of ieee double (2^52 = 4503599627370496 values which is 15 digits)

How do you get the 40734.825668912003 value?

Ludo

Ludo Brands wrote

How do you get the 40734.825668912003 value?

Fields.FieldByName(Value).AsFloat();

Andrew Brunner wrote

2.) Actual Value after update : 40734.825668912

This is the value as read in the MYSQL admin program table data.

This is under Ubuntu 10.10 x64.

You are probably assigning the result to a variable of the type "extended", or directly passing it a routine that accepts parameters of the type "extended". The digits beyond the precision guaranteed by the double type are undefined in that case (at least in case the number cannot be represented exactly by a double precision value).

Jonas

Declaraions are all double.

Value: double; Query.Params.ParamByName(sName).AsFloat:=Value;

Also:
Test:double;
Test:=Query.Params.ParamByName(sName).AsFloat;

Test=Value << ok here. Problem must be somwhere else.

1.) Update Value : 40734.825668912039 2.) Actual Value after update : 40734.825668912
3.) Actual Value on read : 40734.825668912003

As far as I know this is a relatively new problem. Could be with Ubuntu but was someone doing MYSQL work lately?

Andrew Brunner wrote

Declaraions are all double.

You don't show what you do with the value before writing it out, nor how you write it out and determine that its value is "40734.825668912003" (which is probably also what Ludo meant with "How do you get the 40734.825668912003 value?"). As I mentioned in my previous reply, the value is probably simply getting converted to extended in the mean time and that is where the other digits come from.

Jonas

> How do you get the 40734.825668912003 value? Fields.FieldByName(Value).AsFloat();

Floattostr limits precision to 15 digits (40734.8256689120). When updating the mysql database it converts the float to a string and looses the last 2 digits. The last digits are returned by gdb or the str procedure with precision >15, but won't be displayed in data controls.

Ludo

On Mon, Jul 11, 2011 at 10:19 AM, Hans-Peter Diettrich

It doesn't make sense to display more digits than stored in the binary variable. There is no loss, only rounding errors in the conversion from binary to decimal text.

I need to compare values with complete precision. I don't think this problem was present a while ago.

When the memory is copied is there a missing 12bits? Is there going to be a fix for this?

I agree. I just tried to explain what happens behind the scenes.

There is no loss, only rounding errors in the conversion from binary to decimal text.

There is a small loss. 2^52 = 4503599627370496 which is almost 16 digits. 15 digits can be represented by a 50 bits mantissa. So you're losing 2 bits in precision by truncating to 15 digits.
I wrote earlier that the mysql client returned 40734.825668912 when set to 40734.825668912039. That is mysql truncating to 15 characters the string representation of the double for displaying. But when I do a select t_double
- 40734.825668 I do get 9.1204....e-07. In case of fpc setting the same value select t_double - 40734.825668 returns 9.1200....e-07. And that is because fpc converts to a string when updating the value losing the last 2 bits.

Ludo

Ludo Brands wrote

So you're losing 2 bits in precision by truncating to 15 digits.

Ok, so I'm going to go out on a limb here and propose the question: How to we fix this 2bit loser ;-)

It doesn't make sense to display more digits than stored in the binary variable. There is no loss, only rounding errors in the conversion from binary to decimal text.

DoDi

Exact compare with double is never a good idea. For MySQL read more here: http://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html

Hans-Peter Diettrich wrote

The binary value consists of an exponent and an significand (mantissa), most probably you forgot to count the exponent and sign bits.

The problem I'm trying to describe is with double precision floating point values. When the query is open and one of the parameters is double MySQL does not receive all of the memory. It's missing two bits. That is a problem with either the mysql5.1 driver or with the mysql51 component w/r/t the double data type.

I'd suggest that you read a bit about using floating point numbers. They never are accurate, and every decimal representation adds more inaccuracy

I could understand if you wanted to bring this fact into argument over arithmetic and cumulative error. But the fact is - if I send a double precision data value to a double precision field in the SQL database - that value had better be the same - "bit for bit". Now if someone is being bit shifty on the conversion - that needs to be a addressed.

Floating point numbers never should be compared for equality. Better: if abs(a-b) < delta then ...
with delta corresponding to the available accuracy (e.g. 1E-15).

As a rule of thumb: 10^3 ~ 2^10, i.e. 3 decimal digits require 10 bits in the significand, so that 52 bits allow for 52*3/10 ~ 15 digits.

When the memory is copied is there a missing 12bits? Is there going to be a fix for this?

The binary value consists of an exponent and an significand (mantissa), most probably you forgot to count the exponent and sign bits.

I'd suggest that you read a bit about using floating point numbers. They never are accurate, and every decimal representation adds more inaccuracy :-(

DoDi

The only way to transfer double as binary data to the server is by using prepared statements and bound parameters. This is currently not implemented in fcl. All data are transferred as text to the database and are subject to conversion errors.

I don't think zeoslib uses prepared statements for mysql either.

Ludo

Ludo Brands wrote

The only way to transfer double as binary data to the server is by using prepared statements and bound parameters. This is currently not implemented in fcl. All data are transferred as text to the database and are subject to conversion errors.

Hmmm... I seem to recall prepare and bind parameters in fcl-db/mySQL component source?. Are you sure about this? I really, really, really need double precision data to work. I know i've used binary binds with Int64 arrays and things well beyond 8 bytes binary.

I see that the functions and structures are defined in mysql.inc but they aren't used anywhere. Parameter values are substituted in the sql string that is sent to the db and TConnectionName.PrepareStatement doesn't send anything to the db.
The Oracle component fe. uses bindings.

Ludo

So does Firebird, and I think postgres, but I'm not sure about the latter.

The reason is that when sqldb was created, MySQL (4.0 or 4.1) didn't support parameters yet. Support for this would mean that the mysql connection component needs to be rewritten for MySQL 5.0 and higher.

Michael.

Ludo Brands wrote

I don't think zeoslib uses prepared statements for mysql either.

MSEgui tmysqlconnection uses prepared statements and binary parameters.

Martin

May be, that this can be fixed in sqldb.pp in function TSQLConnection.GetAsSQLText(Param: TParam) : string; ...
- ftFloat : Result := FloatToStr(Param.AsFloat, FSQLFormatSettings);
+ ftFloat : Result := FloatToStrF(extended(Param.AsFloat), ffGeneral, 16, 0, FSQLFormatSettings);
...
for me it seems, that works ... See atached program. What do you think ?
-Laco.

May be, that this can be fixed in sqldb.pp in function TSQLConnection.GetAsSQLText(Param: TParam) : string; ...
- ftFloat : Result := FloatToStr(Param.AsFloat, FSQLFormatSettings);
+ ftFloat : Result := FloatToStrF(extended(Param.AsFloat), ffGeneral, 16, 0, FSQLFormatSettings);
...
for me it seems, that works ... See atached program. What do you think ?
-Laco.

But that won't solve the writing of truncated data as long as doubles are passed as a string to mysql.

Ludo

But that won't solve the writing of truncated data as long as doubles are passed as a string to mysql.

Right. When the actual DB interface doesn't allow to transfer floating point values as binary numbers, you should fall back to storing such numbers as text, with the required number of digits, or (safer) as binary BLOBs. Then you can store even Extended values instead of only Doubles.

Both workarounds may not be compatible with other applications, that expect numbers stored in the DB-specific number format.

DoDi

In our previous episode, Ludo Brands said:

But that won't solve the writing of truncated data as long as doubles are passed as a string to mysql.

Moreover, it is x86(_64) only.

In our previous episode, Michael Van Canneyt said:

As far as I can see, postgres is textprocessing based. Params are asstringed

1.) is a value declared as double and viewed under GDB. 2.) is a value viewed in table data in MySQL Console App under Linux. 3.) Is the value retrieved from the table by the Application using the Select statement and Fields.FieldByName(field_name).AsFloat()

I am still experiencing this issue and seeking alternatives b/c I'm using all 12 digits - and am expecting all 12 digits. Can someone update the (Fields.Data variant as double) with a binary send instead of converting to a string?

Andrew Brunner wrote / napísal(a):

IMO it will require use in mysqlconn.inc new API "prepare family" functions introduced in MySQL 5
http://dev.mysql.com/doc/refman/5.0/en/c-api-prepared-statement-function-overview.html

In current implementation (backward compatible with older MySQL versions) there is no only problem with sending values, but also retrieved values are strings (see
http://dev.mysql.com/doc/refman/5.0/en/mysql-fetch-row.html)

-Laco.