Laravel binding shows big number being applied to the query

Question

I have a function that casts and returns the following:

$price = (double)number_format(2.97, 5, '.', '');

But when doing the query through Laravel Eloquent like:

$query = \App\Models\CustomersPrices::where('price', $price)->exists();

I wasn't finding the price in database. After some digging I did the following:

DB::enableQueryLog();
$query = \App\Models\CustomersPrices::where('price', $price)->exists();
$sql = DB::getQueryLog();

\Log::error($sql);

And the result is:

array (
  0 => 
  array (
    'query' => 'select exists(select * from `customers_prices` where `price` = ? ) as `exists`',
    'bindings' => 
    array (
      0 => 2.970000000000000195399252334027551114559173583984375,
    ),
    'time' => 1.560000000000000053290705182007513940334320068359375,
  ),
)

Why is this happening? Doing var_dump($price) shows float(2.97).

What am I missing?

PHP number precision is inherently buggy. Don't cast the number as a float, since it will already be a float after your formatting. — aynber, Aug 05 '21 at 16:05
Some reading for reference: https://www.php.net/manual/en/language.types.float.php and https://stackoverflow.com/questions/3726721/php-floating-number-precision — Tim Lewis, Aug 05 '21 at 16:06
@aynber it's a string after formatting.. The function `number_format` returns a string. — Linesofcode, Aug 05 '21 at 16:06
@aynber and my fields in database are double, so comparing doubles with strings..leads to problems. Maybe I'll have to cast the string to double while querying.. — Linesofcode, Aug 05 '21 at 16:07
Yes, but it will be treated as a number, especially when passing it into your query — aynber, Aug 05 '21 at 16:07
Float comparisons are not accurate. You can rather store dollars and cents in different columns. — nice_dev, Aug 05 '21 at 16:07
@aynber it's not being treated as a number. It's actually being treated as '2.97' (from what I can see in the bindings..) — Linesofcode, Aug 05 '21 at 16:08
Right. If you run `select 2.97 = '2.97000';` in a database, it returns true. Quoted numbers still work when compared to non-quoted numbers. — aynber, Aug 05 '21 at 16:10
@aynber take a look at this image: https://i.postimg.cc/sD2DnDrm/wtf.png When comparing doubles with strings it's not returning anything. — Linesofcode, Aug 05 '21 at 16:19
If I do `WHERE CAST(unit_price AS CHAR(30)) = '2.97000'` it works. The field `unit_price` is a double (20,5). — Linesofcode, Aug 05 '21 at 16:21
How very odd. As shown in https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#operator_equal , it should work. — aynber, Aug 05 '21 at 16:34
After some reading, the same sort of precision issue that happens with PHP also happens with float/double columns in MySQL. Decimal is a lot more exact. This could be the reason. — aynber, Aug 05 '21 at 16:37
Why `decimal` is the preferred data type for price columns. Also, I am willing to bet that casting isnt fool proof either. — user3532758, Aug 05 '21 at 17:45

score 0 · Accepted Answer · answered Aug 05 '21 at 16:40

0

Solved.

Comparing as string didn't work https://i.postimg.cc/sD2DnDrm/wtf.png

The solution was to firstly cast the field into a string and then compare both values as string.

SELECT * FROM ... WHERE CAST(price AS CHAR(30)) = '2.97000';

https://i.postimg.cc/dwxcr9Lg/wtf2.png

answered Aug 05 '21 at 16:40

Linesofcode

5,327
13
62
116

1

Just curious. If value is 25.50, does it work? For example, `SELECT * FROM ... WHERE CAST(price AS CHAR(30)) = '25.50';` – nice_dev Aug 05 '21 at 17:44
It doesn't because the column has 5 decimals, not 2. That's not a problem for me because my numbers always have 5 decimals. – Linesofcode Aug 05 '21 at 21:03

Laravel binding shows big number being applied to the query

1 Answers1