MySQL Bugs: #52914: User defined function returns incorrect float value

Bug #52914	User defined function returns incorrect float value
Submitted:	17 Apr 2010 9:03	Modified:	30 Apr 2010 8:56
Reporter:	Kees Kling	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: User-defined functions ( UDF )	Severity:	S2 (Serious)
Version:	5.1.37, 5.0, 5.1, 5.6.99	OS:	Linux (ubuntu 9.10 64bits)
Assigned to:		CPU Architecture:	Any
Tags:	64bits, linux, udf
Triage:	Needs Triage: D2 (Serious)

Description:
My data, stored in a BLOB, consists of  alarge amount of floats. Sometimes I just need one float and for that I wrote a small UDF in c.

This is the main routine:
float bytes2float(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error) 
{
    int i;
    char *cbuf = args->args[0]; 
	for ( i=0; i<4; i++){
		fprintf(stderr,"cbuf[%d] = %d\n", i, cbuf[i]);
		} 
    if ( !is_bigendian() ){
	ReverseArray(args->args[0]);
    };
    	for ( i=0; i<4; i++){
		fprintf(stderr,"cbuf[%d] = %d\n", i, cbuf[i]);
		} 
	float *fp = (float *) cbuf;
	//printf("*fp = %3.3f\n\n", *fp);
	//fprintf(stderr,"return is %3.5f\n",*fp);

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

It is loaded in the server
CREATE FUNCTION bytes2float RETURNS REAL SONAME 'bytes2float.so';

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

In real li9ve 'AK33' means 12.70 and that is also the value which is printed in the errorlog file just before returning from the c-function.

How to repeat:
It occurs always, but when I test this function on a 5.0 mysql version 32 bits, the returnvalue is ok.

Suggested fix:
Looked at several forums, but didn't get an answer or solution.

Thank you for the report.

I can not repeat described behavior using last part of function you used:

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

Also based on the code provided expected result should be 123.43, but not what you wrote in the description. Please send us full function used and actual result you get after running this function.

Complete source of bytes2float UDF

Attachment: bytes2float.c (text/x-csrc), 1.50 KiB.

I just send you the complete source. In the snipet i sent before, I commented out some lines  and returned a fixed float just for testing purposes. This return value was also corrupt.

Regards 
Kees Kling

Thank you for the feedback.

I still can not repeat described behavior:

$mysql51
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.1.47-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE FUNCTION bytes2float RETURNS REAL SONAME 'bytes2float';
Query OK, 0 rows affected (0.03 sec)

mysql> select bytes2float('AK33');
+---------------------+
| bytes2float('AK33') |
+---------------------+
|    12.6999998092651 |
+---------------------+
1 row in set (0.02 sec)

Please try current version 5.1.46 and if problem still exists provide more information about MySQL server package you use: which package (file name you downloaded) or configure options if you built server yourself, your configuration settings.

Sorry I, but it took me some time to install a secundary server on my box, but it is up and running. It is now version 5.1.44, but the results are the same. Are there special compile options for a 64bits OS?
I compile the lib with the following lines:

gcc -fpic -Wall -I/usr/include/mysql -shared -o bytes2float.o -c bytes2float.c
ld -shared -o bytes2float.so bytes2float.o

Regards
Kees

Thank you for the feedback.

64-bit matters. Verified as described on 64-bit Linux. Not repeatable on 32-bit.

This is not a bug. The UDF was declared to return a value of the REAL type which is a synonym for DOUBLE PRECISION (64-bit) unless the REAL_AS_FLOAT SQL mode is enabled. So the server expects bytes2float() to return a 64-bit double precision value, but a 'float' (32-bit) value is actually returned.

This happens to work on 32-bit architectures simply because the float->double conversion is done by the FPU through its own stack, and thus is transparent for the server. However, on x86_64 floating point results are returned in SSE2 registers and this is when the difference between the actual data type and the expected one becomes visible.

That said, it is not currently possible to make the server expect a 32-bit floating point value as a result of a UDF, since both FLOAT and DOUBLE PRECISION data types are handled as 64-bit values internally. So the only solution is to change the UDF to return a 'double' value rather than a 'float' one.

Thanks for the description and I tried this, but still not working on 64 bits and it does on 32 bits. So there must be something with my data and I have to look into this a bit futher