SQL_FLOAT handler casts from float to internal double which causes precision issues.

[mlazdans]

Code in question:

php-firebird/ibase_query.c

Lines 1320 to 1322 in 23aca98

	case SQL_FLOAT:
	ZVAL_DOUBLE(val, *(float *) data);
	break;

Test case:

CREATE TABLE FLOAT_TEST (
    FLOAT_FIELD FLOAT DEFAULT 3.14
);

ibase_query("INSERT INTO FLOAT_TEST (FLOAT_FIELD) VALUES (1.23)") or die ("ibase_query failed");
$q = ibase_query("SELECT * FROM FLOAT_TEST") or die ("ibase_query failed");
$r = ibase_fetch_object($q) or die ("ibase_fetch_object failed");
var_dump($r);

object(stdClass)#1 (1) {
  ["FLOAT_FIELD"]=>
  float(1.2300000190734863)
}

Should be: 1.23

[MartinKoeditz]

I think this is correct. See reference

If no precision was given, precision is 24 digits (Firebird >= 4).

Notice for Firebird 3:
The FLOAT data type has an approximate precision of 7 digits after the decimal point. To ensure the safety of storage, rely on 6 digits.

Also the PHP doc says:
So never trust floating number results to the last digit, and do not compare floating point numbers directly for equality.

[mlazdans]

Yes, that is all true. However, here the issue is when I'm inserting FLOAT, I'm getting back a DOUBLE. And potentially with garbage. In latest versions they introduced FLOAT with precision and essentially converting it to DOUBLE when using precision > 24. So no issues there.

[mlazdans]

Looks like I have to investigate this more thoroughly 😅

[mlazdans]

isql trims floats to 7 digits
mysqlnd extension trims floats to 6 digits

ibase extension does not trim anything currently

[MartinKoeditz]

I think that's OK. The user can round to what ever he wants.

Everyone using FLOAT should know about the precision problematic. It's better to use NUMERIC if you need better precision.

[mlazdans]

I'm running now some tests. data_types_fb25.phpt fails on Windows PHP 7.4

023+   float(3.1400001049042)
023-   float(3.140000104904175)

Field:

FLOAT_FIELD FLOAT DEFAULT 3.14

PHP 7.4 3.1400001049042 Windows
PHP 8.4 3.140000104904175 both on Linux / Windows

[mlazdans]

Almost feels like this trimming could be an INI entry. By default one would get whole FLOAT/DOUBLE but then one could specify digits of interest.

There us a php function for that: php_gcvt(). I already did some experiments:

php-firebird/ibase_query.c

Lines 1077 to 1089 in c49a99a

	case SQL_FLOAT:
	// Experiments with trimming
	// https://firebirdsql.org/file/documentation/html/en/refdocs/fblangref50/firebird-50-language-reference.html#fblangref50-datatypes-floattypes
	// The FLOAT data type defaults to a 32-bit single precision
	// floating-point type with an approximate precision of 7 decimal
	// digits after the decimal point (24 binary digits). To ensure the
	// safety of storage, rely on 6 decimal digits of precision.
	// char _fbfloat_buf[32] = {0};
	// php_gcvt(*(float *) data, 7, '.', 'e', _fbfloat_buf);
	// php_printf("float: %f, converted to: %s\n", *(float *) data, _fbfloat_buf);
	// ZVAL_DOUBLE(val, zend_strtod(_fbfloat_buf, NULL));
	ZVAL_DOUBLE(val, *(float *) data);
	break;

[MartinKoeditz]

If there is an INI entry, we could use it. But I don't know if that would be a good choice.

On the other hand we can check for the most interesting digit:

function floatEquals($a, $b, $epsilon = 0.0000001) {
    return abs($a - $b) < $epsilon;
}

It's a technical reason why floats are never identical. So we must not find a solution for that. But we have to deliver the data of the database itself.

[mlazdans]

Yeah but in this case the same data yields different results depending on PHP version.

I'm aware about espilon check. For convenience we could introduce something like this

ibase.float_accuracy
ibase.double_accuracy

just an idea, low priority

[mlazdans]

Same stored bits should be equal once retrieved back though.

[MartinKoeditz]

I'm fine the the INI params.

[MartinKoeditz]

Same stored bits should be equal once retrieved back though.

Here is a technical hint:
Floating-point values are not stored exactly (IEEE 754 binary approximation). Small rounding differences can occur depending on CPU, compiler, and operation order. Additionally, formatting/printing may round differently. As a result, the same float may appear slightly different across systems and should not be compared for exact equality.