php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #80445 Using float bind parameter returns weird error
Submitted: 2020-11-30 01:06 UTC Modified: 2020-11-30 20:26 UTC
From: domen at jollydeck dot com Assigned:
Status: Re-Opened Package: PDO MySQL
PHP Version: 7.4.13 OS: Windows, CentOS
Private report: No CVE-ID: None
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: domen at jollydeck dot com
New email:
PHP Version: OS:

 

 [2020-11-30 01:06 UTC] domen at jollydeck dot com
Description:
------------
Following code produces weird error "SQLSTATE[22007]: Invalid datetime format: 1292 Truncated incorrect INTEGER value" although no date or time is used anywhere.

This only happens when PDO::ATTR_EMULATE_PREPARES is set to false and bound parameter is float. Table engine does not matter (tested with InnoDB and MyISAM).

Statement executes successfully if bind parameter is integer or the value "0.3" is not bound but hard-coded into the query (either as '0.3' or as 0.3).

Test script:
---------------
<?php
$pdo = new PDO('mysql:host=localhost;dbname=testdb', 'test_username', 'test_password');
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$pdo->query("CREATE TABLE `test` (`field` INT NOT NULL) ENGINE=InnoDB");
$pdo->query("INSERT INTO `test` (`field`) VALUES (0)");

$stmt = $pdo->prepare("UPDATE `test` SET `field` = 100 * ?");
$stmt->execute(array(0.3));


Expected result:
----------------
Query executes successfully

Actual result:
--------------
Fatal error:  Uncaught PDOException: SQLSTATE[22007]: Invalid datetime format: 1292 Truncated incorrect INTEGER value: '0.3' in ...

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2020-11-30 02:52 UTC] danack@php.net
It might be useful to get the actual statement being executed. Apparently this can be done by setting the a log entry in my.cnf according to: https://stackoverflow.com/a/1813818/778719
 [2020-11-30 03:26 UTC] domen at jollydeck dot com
MySQL general log says the following:

Prepare	CREATE TEMPORARY TABLE `test` (`field` INT NOT NULL)
Execute	CREATE TEMPORARY TABLE `test` (`field` INT NOT NULL)
Close stmt
Prepare	INSERT INTO `test` (`field`) VALUES (0)
Execute	INSERT INTO `test` (`field`) VALUES (0)
Close stmt
Prepare	UPDATE `test` SET `field` = 100 * ?
Execute	UPDATE `test` SET `field` = 100 * '0.3'
Close stmt

Indeed, when executing the following statements in mysqld, MySQL returns "ERROR 1292 (22007): Truncated incorrect INTEGER value: '0.3'":

[create temporary table]
[insert value]
PREPARE stmt FROM 'UPDATE `test` SET `field` = 100 * ?';
SET @a = '0.3';
EXECUTE stmt1 USING @a;

So:
1. This is MySQL bug/unexpected behaviour.
2. Is SQL error 22007 ("Invalid datetime format") MySQL bug or is it wrongly described in PHP?
3. Is there any workaround? I cannot find any PDO::PARAM_* value that corresponds to PHP's float or MySQL's DECIMAL/FLOAT.
 [2020-11-30 04:42 UTC] 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)
 [2020-11-30 05:07 UTC] domen at jollydeck dot com
-Status: Open +Status: Closed
 [2020-11-30 05:07 UTC] 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 07:26 UTC] requinix@php.net
-Status: Closed +Status: Re-Opened
 [2020-11-30 07:26 UTC] 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 13:13 UTC] 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 13:33 UTC] 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 16:41 UTC] domen at jollydeck dot com
MySQL bug: https://bugs.mysql.com/bug.php?id=101806
 [2020-11-30 20:26 UTC] cmb@php.net
Ah, okay, that makes sense. :)
 
PHP Copyright © 2001-2021 The PHP Group
All rights reserved.
Last updated: Sun Oct 24 01:03:33 2021 UTC