Ad

Unit Testing With Laravel AssertDatabaseHas And A Float Column

Doing some tests on my Laravel app and am running into intermittent failures due to one of my database columns being defined as a FLOAT. When running a test with assertDatabaseHas() it sometimes fails due to floating point uncertainty.

<?php

namespace Test\Unit;

use App\Foo;

class MyModelTest extends TestCase
{
    public function testFooCanBar(): void
    {
        $stringvalue = "baz";
        $floatvalue = 234.281;
        $data = [
            "name" => $stringvalue,
            "data" => $floatvalue,
        ];
        Foo::bar($stringvalue, $floatvalue);
        $this->assertDatabaseHas("foos", $data);
    }
}

Sometimes result:

Failed asserting that a row in the table [foos] matches the attributes {
    "name": "baz",
    "data": 234.281
}.

Found similar results: [
    {
        "name": "baz",
        "data": 234.280999999999999995
    }
]

I can think of a few ways to work around this (change the column to an INT and multiply by 10^x, or just remove the column from the comparison) but I'm wondering if there are any methods I missed to properly check this. When comparing values directly we can use PHPUnit's assertEqualsWithDelta(); is there anything similar for a database check?

Ad

Answer

In MySQL databases (currently v8), a FLOAT or DOUBLE data type is always stored as an approximate value:

The FLOAT and DOUBLE types represent approximate numeric data values. MySQL uses four bytes for single-precision values and eight bytes for double-precision values.

— from: MySQL FLOAT and DOUBLE Data Type Reference

On the other hand, a DECIMAL data type allows you to store exact decimal values up to 65 digits long.

The DECIMAL and NUMERIC types store exact numeric data values. These types are used when it is important to preserve exact precision, for example with monetary data.

— from: MySQL DECIMAL Data Type Reference

Problems with FLOAT (reference)

MySQL documentation covers the issue with FLOAT and DOUBLE data types not being exact and shows a possible solution to use these types with delta differences at a database levels (full example in the referenced link):

SELECT i, SUM(d1) AS a, SUM(d2) AS b
FROM t1
GROUP BY i
HAVING ABS(a - b) > 0.0001;

Important Note: Keep in mind that FLOAT and DOUBLE are also platform dependant!

A SELECT statement like the one below could either return 0 and -0 or inf and -inf based on the runtime environment:

CREATE TABLE t1(c1 FLOAT(53,0), c2 FLOAT(53,0));
INSERT INTO t1 VALUES('1e+52','-1e+52');
SELECT * FROM t1;
Ad
source: stackoverflow.com
Ad