Unit testing with Laravel assertDatabaseHas and a float column

Question

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?

If you need to store precise decimal values (like prices) go for DECIMAL column type instead of FLOAT. This should always store and return the correct value — mdexp
– mdexp, Commented Feb 4, 2020 at 22:48
That seems to have done the trick, and with no code changes needed! If you want to flesh that out into an answer (a reference for this behaviour would be great) I'll mark it accepted. — miken32
– miken32, Commented Feb 5, 2020 at 18:04

mdexp · Accepted Answer · 2020-02-06 00:23:42Z

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;

Collectives™ on Stack Overflow

Unit testing with Laravel assertDatabaseHas and a float column

1 Answer 1

Problems with FLOAT (reference)

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Problems with FLOAT (reference)

Comments

Your Answer

Sign up or log in

Post as a guest

Related