ID: 35518
User updated by: php at pjberkel dot com
Reported By: php at pjberkel dot com
Status: Bogus
Bug Type: MySQLi related
Operating System: RHEL 4
PHP Version: 6CVS-2005-12-02 (snap)
New Comment:
Ok, so technically you are correct that PHP is storing the value as a
float. My point is that the actual *number* is still an integer and
not a float. I understand that PHP automatically assigns the
underlying datatype of a variable based on what is sees fit (which is
fine) but in this case it is causing problems.
Consider a typical database application that has integer column types.
In MySQL, both 10000 and 3900002281 are valid (unsigned) 32bit integer
values (and thus stored in column type "unsigned int"), while in PHP
the former is stored as an "int" while the later as a "float".
Given that an application might expect a particular input value to
always be an integer (regardless of its internal representation in PHP)
it seems unreasonable and impractical to force the application to
determine the internal variable type (at runtime) before binding the
variable.
Demonstrating the problem with a small code fragment:
$integers = array(1, 500, 10000, 3800002281, 3900002281); // all valid
MySQL unsigned integers
foreach ($integers as $i) {
$stmt = $mysqli->prepare("SELECT id FROM temp_table WHERE id = ?");
$stmt->bind_param(is_float($i) ? "d" : "i", $i); // forced to
determine varible datatype at runtime
$stmt->execute();
}
I guess when dealing with large integer values (that might possibly be
stored as a float by PHP) it is safe to use the "d" type specifer
instead of "i" in bind_parm(). At the very least, the documentation
should be updated to make this point a little clearer.
Mental note to self: avoid using unsigned integer columns in mysql! :)
Previous Comments:
------------------------------------------------------------------------
[2005-12-02 15:08:20] [EMAIL PROTECTED]
>the variable being bound is an integer ($id_val = 3900002281)
Hmm.. No?
# php -r '$id_val = 3900002281; var_dump($id_val);'
float(3900002281)
>The real issue here is that "bind_param("i", $id_val)"
>should work correctly with integers of any size, not
>limited to php internal MAX_INT_SIZE limit of 2147483647
>(32bit signed integer).
It's not integer, it's double/float.
Binding floats as integers obviously may not work.
------------------------------------------------------------------------
[2005-12-02 14:55:07] php at pjberkel dot com
If you check the reproduce code, you'll see that the variable being
bound (and also the db column type) is an integer ($id_val =
3900002281) but the problem occurs because the actual integer value is
larger than the maximum (signed) 32bit integer value in PHP (while in
MySQL it is a valid unsigned 32bit integer value).
Setting the bind type to "d" is a hack / workaround to the problem
since double type is presumably 64bit (which will happily store a 32bit
unsigned integer value).
The real issue here is that "bind_param("i", $id_val)" should work
correctly with integers of any size, not limited to php internal
MAX_INT_SIZE limit of 2147483647 (32bit signed integer).
Please let me know if you require further clarification.
Thanks,
Pieter
------------------------------------------------------------------------
[2005-12-02 11:12:12] [EMAIL PROTECTED]
>If the bind type in $stmt->bind_param() is
>changed to "d" then the reproduce code works as expected.
Because you are binding a FLOAT?
I can hardly imagine a way to fit a float value in an integer var.
Doesn't look like a bug to me.
------------------------------------------------------------------------
[2005-12-02 10:51:44] php at pjberkel dot com
Description:
------------
This bug report is a follow-up to a previous bug:
http://bugs.php.net/bug.php?id=35103
It appears that mysqli_stmt_bind_param suffers from the same unsigned
integer problem as mysqli_stmt_bind_result (see bug #35103) when the
bind type is set to "i" and the integer value bound is MAX_UNSIGNED_INT
< x < MAX_SIGNED_INT. If the bind type in $stmt->bind_param() is
changed to "d" then the reproduce code works as expected.
(Could possibly be related to bug #35428 but the error in this bug
report is much more reproducible). I'm using php5.1-200512020130 +
mysql-5.0.16 on RHEL 4.
Reproduce code:
---------------
<?php
$mysqli = new mysqli("host", "user", "pass", "db");
$mysqli->query("CREATE TABLE temp (id INT UNSIGNED NOT NULL)");
$mysqli->query("INSERT INTO temp (id) VALUES (3900002281)");
$id_val = 3900002281;
/* BEGIN EXAMPLE OF BUG */
$stmt = $mysqli->prepare("SELECT id FROM temp WHERE id = ?");
$stmt->bind_param("i", $id_val);
$stmt->execute();
$stmt->bind_result($id);
$stmt->fetch();
var_dump($id);
$stmt->close();
/* END EXAMPLE OF BUG */
$mysqli->query("DROP TABLE temp");
$mysqli->close();
?>
Expected result:
----------------
string(10) "3000000897"
Actual result:
--------------
int(0)
------------------------------------------------------------------------
--
Edit this bug report at http://bugs.php.net/?id=35518&edit=1
