Bug #80445 [ReO]: Using float bind parameter returns weird error

Edit report at https://bugs.php.net/bug.php?id=80445&edit=1 ID: 80445 User updated by: domen at jollydeck dot com Reported by: domen at jollydeck dot com Summary: Using float bind parameter returns weird error Status: Re-Opened Type: Bug Package: PDO MySQL Operating System: Windows, CentOS PHP Version: 7.4.13 Block user comment: N Private report: N New Comment: MySQL bug: https://bugs.mysql.com/bug.php?id=101806 Previous Comments: ------------------------------------------------------------------------ [2020-11-30 13:33:39] domen at jollydeck dot com Hi, I've stripped the original query of unnecessary stuff so that bug report could be as simple as possible and to avoid wasting your time. Real use case is this: we have a table of "weights" which influence some application logic. After each user action this weight is multiplied by some factor, rounded and limited to lower and upper bound. This could be achieved by retrieving value, doing calculations in PHP and then updating value in table, but "simple" expression GREATEST(LEAST(ROUND(field * ?), ?), ?) does the job and also avoids any table locking that is needed between retrieving and updating the value. I've put word "simple" in quotes because expression has now become GREATEST(LEAST(ROUND(field * CAST(? AS FLOAT)), CAST(? AS UNSIGNED)), CAST(? AS UNSIGNED)) so that is works with stringified numeric bound parameters. I know PDO::PARAM_INT exists, but this is legacy application. ------------------------------------------------------------------------ [2020-11-30 13:13:02] cmb@php.net > $pdo->query("CREATE TABLE test > (field INT NOT NULL) ENGINE=InnoDB"); > $stmt = $pdo->prepare("UPDATE test SET > field = 100 * ?"); This looks plain wrong to me. Why don't you do the calculation in PHP, and send an integer in the first place? And if it is about monetary calculations with floats, you likely have bigger issues than this query[1]. [1] <http://www.floating-point-gui.de/> ------------------------------------------------------------------------ [2020-11-30 07:26:17] requinix@php.net I wouldn't hold my breath on that MySQL bug... Reopening because there is one easy fix we could make that should address the problem: change the error message. Perhaps dropping the data type and just saying "Invalid format". ------------------------------------------------------------------------ [2020-11-30 05:07:57] domen at jollydeck dot com Both workarounds (100.0 * ? and 100 * CAST(? AS FLOAT)) work. Thank you. I will file a MySQL bug report. SQLSTATE code is probably not the appropriate one, if not also the "integer-assuming" behaviour. It looks like MySQL dislikes "numbers as strings" more and more. Last year they've changed behaviour of GREATEST and LEAST functions to treat strings differently. There was no mention of this change in release notes or documentation. See https://bugs.mysql.com/bug.php?id=94267 ------------------------------------------------------------------------ [2020-11-30 04:42:21] requinix@php.net Not sure whether we can do anything about this... > 1. This is MySQL bug/unexpected behaviour. As an educated guess, the query may have been compiled to expect an integer in the ? position, so feeding it an invalid integer would error. > 2. Is SQL error 22007 ("Invalid datetime format") MySQL bug or is it wrongly > described in PHP? The MySQL error code is 1292, which means a truncated value, but that's a value specific to MySQL. They chose to map that to the standardized SQLSTATE code of 22007, which seems like it was meant for invalid date/time values. PDO uses SQLSTATE error codes, and so will report the 22007 with that message. So the problem is a bit of both: an issue with MySQL mapping their own error for a general case to a SQLSTATE error for a specific case, and an issue with PDO/pdo_mysql in that it can't (?) do driver-specific error reporting. > 3. Is there any workaround? I cannot find any PDO::PARAM_* value that corresponds to > PHP's float or MySQL's DECIMAL/FLOAT. Integers and strings only really have one binary representation (endian-ness aside), but floats have at least two because there are two different common types of floating-point values - which PHP hides from developers. (And then there are concepts like DECIMALs which are practically strings.) So having a PARAM_FLOAT isn't necessarily easy. Generally, it's recommended to pass floats as strings and have the database engine perform whatever conversions it wants. If my guess about the compiled query is correct, the simplest solution may be: 100.0 * ? with the hopes that the query parser will allow for the placeholder to be a non-integer. Otherwise there's the sort of solution that's more common to see in the PostgreSQL world, where types are stricter, and that's to add typecasts into the query: 100 * CAST(? AS FLOAT) ------------------------------------------------------------------------ The remainder of the comments for this report are too long. To view the rest of the comments, please view the bug report online at https://bugs.php.net/bug.php?id=80445 -- Edit this bug report at https://bugs.php.net/bug.php?id=80445&edit=1