🐛 `where` float parameter passed as string

[gam6itko]

No duplicates 🥲.

• [X] I have searched for a similar issue in our bug tracker and didn't find any solutions.

Database

MySQL

What happened?

This code passes the parameter as a string

$amount = 300.05;
$s
   ->where(new Fragment('COALESCE(from_min, ?) <= ?', self::MIN_AMOUNT, $amount))
   ->where(new Fragment('COALESCE(from_max, ?) >= ?', self::MAX_AMOUNT, $amount));

In mysql server logs I see the following log

  (COALESCE(`from_min`, '1.0E-8') <= '300.05' AND COALESCE(`from_max`, 1000000000) >= '300.05' )

Correct work was achieved by such a design.

$amount = 300.05;
$s
   ->where(new Fragment(\sprintf('COALESCE(from_min, %.8f) <= %.8f', self::MIN_AMOUNT, $amount)))
   ->where(new Fragment(\sprintf('COALESCE(from_max, %d) >= %.8f', self::MAX_AMOUNT, $amount)));

In this case, everything is as it should be in the mysql logs

AND (COALESCE(from_min, 0.00000001) <= 300.05000000 AND COALESCE(from_max, 1000000000) >= 300.05000000 )

Version

database 2.5.1
PHP 8.1

[msmakouz]

The bindParam and bindValue methods of the PDO class, when given a float value, cast it to a string. In our code, we pass exactly the float value to this method, not the string: https://github.com/cycle/database/blob/2.x/src/Driver/Driver.php#L528

In PDO there is no constant with type float, numeric. There is only an int: https://www.php.net/manual/en/class.pdo.php I don't think we can do anything with it. If you need exactly a numeric value, I think you can use Fragment.