|
|
php.net | support | documentation | report a bug | advanced search | search howto | statistics | random bug | login |
[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 ...
PatchesPull RequestsHistoryAllCommentsChangesGit/SVN commits
|
|||||||||||||||||||||||||||
Copyright © 2001-2025 The PHP GroupAll rights reserved. |
Last updated: Tue Oct 28 20:00:01 2025 UTC |
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.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)> $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/>