Hi

I am running a database in which I store lots of float values in a blob. Not always I need the whole set, sometimes a part of it and often just one value. So I wrote a small piece of C-Code, to extract 4 bytes from the blob and cast it to a float.

This function works well on version 5.0 and earlier, but since i migrated to mysql 5.1 64 bits The return values are corrupted. Within the c-code you can write to the error log and at the last moment before I leave the c-code the value of the float is still correct.

My questions:
Is this a known 5.1 issue and do I have to change my mysql version?
Is this a 64 bits version?

Thanks
Kees Kling

Hi Kees,

I am not sure I follow what you are saying above? The c-code is working fine when you print it to the error log prior to the end of your C-code but then what happens? Is is that the calling process using the returned value is incorrect?

What version of the client are you using? Are you also using the 64 bit version or the 32 bit version?

Hi

here is the piece of code I'm talking about
It is inserted in the db
CREATE FUNCTION bytes2float RETURNS REAL SONAME 'bytes2float.so';

Normally this piece of code is called within a stored procedure, but I also tried it in the mysql commandline interface

SELECT bytes2float( 'AK33'[) which are the bytes for float 12.70
You can see in one of the last lines that I print the calculated value to the errorlogflie and that gives me '12.70' and that is correct, but mysql is returning 5.41222900980635e-315

I am working with a complete 64 bits ubuntu linux version.
I tried this code also on my laptop with version 5.0 but then the 32 bits version and there the returnvalue is correct.

Regards Kees

Hi Kees,

the code should work i.e. is returning the float at the pointer fp. I have not checked this but on a 64 bit version does the float retain the same number of bits as with a 32 bit OS? Have you tried returning a fixed float number i.e.

float test = 123.43;
return test;

and see what results you get? If you get the correct answer it could be that the number of bits used in the float has changed between 32 and 64 bit versions of the OS which implies that it is using the contents at the pointer location of the float plus more data which is causing this result.

Hi

I did what you suggested. made a float

float test = 123.43;
fprintf(stderr,"return is %3.5f\n",test);
return test;

In the logfile I get the value 123.43000 and mysql is returning:

mysql> select bytes2float('AK33');
+-----------------------+
| bytes2float('AK33') |
+-----------------------+
| 5.55070141088899e-315 |
+-----------------------+
1 row in set (0.00 sec)

So i am a bit stuck with it

Kees

Hi Kees,

actually looking at your definition of the function you are returning REAL. Should this not be FLOAT?

Ronan

hi,

if you read the docu you only can return STRING,INTEGER,REAL or DECIMAL and DECIMAL is a stringtype return. If you would try FLOAT, there is an errormessage generated. The reason I think the all the declaration are good is bcause the same code is running fine on a 5.0 server version.

Kees

Hi Kees,

I found this link about precisions that have changed between before 5.0.3 and from that point onwards. This may give more information as to why this is now not working? MySQL :: MySQL 5.0 Reference Manual :: B.5.5.8 Problems with Floating-Point Values

Ronan

I have read the article, but it doesn't give a solution. I'm going to try it at the forum on forums.mysql.com. If I find a solution I will post it to this threat

Many thanks till now

Regards
Kees