Page 1 of 1

[Solved] Interesting and buggy?

Posted: Thu Mar 12, 2009 3:50 pm
by kaisellgren
Hello,

I have been lately working with prepared statements and I noticed something rather buggy...

I have an integer column in MySQL InnoDB table which is unsigned so it is capable of handling values between 0 and 4294967295 (including the boundaries). However, something nasty is going on with the MySQLI STMT -class. Look:

Code: Select all

$stmt -> bind_param("i",$a);
$a = 0;
added value is: 0
expected? yes

Code: Select all

$stmt -> bind_param("i",$a);
$a = 1;
added value is: 1
expected? yes

Code: Select all

$stmt -> bind_param("i",$a);
$a = -1;
added value is: Out of range value for column 'inttest' at row
expected? yes

Code: Select all

$stmt -> bind_param("i",$a);
$a = 2147483647;
added value is: 2147483647
expected? yes

Code: Select all

$stmt -> bind_param("i",$a);
$a = 2147483648;
added value is: Out of range value for column 'inttest' at row
expected? NO! WTF ??

Code: Select all

$stmt -> bind_param("i",$a);
$a = 4294967295;
added value is: Out of range value for column 'inttest' at row
expected? NO! WTF ??

Code: Select all

$stmt -> bind_param("i",$a);
$a = 4294967296;
added value is: 0
expected? NO! WTF ?? No out of ranges? It should be!

Code: Select all

$stmt -> bind_param("i",$a);
$a = 4294967297;
added value is: 1
expected? NO! Should be out of range... but the value went over just like in the above example...

Why does it go over? For those "out of range" -values, it won't go over -.-

Rather interesting results... 8O... why can't I add add 2^31 into a field that is supposed to hold from 0 to 2^32 :crazy:

The prepared statement binding kind of sucks... it allows me to fed up to 2147483647 even though the column is unsigned and accepts up to 4294967295... not nice! If I use strings, like '4294967295', then it won't throw out of range errors, but it will silently convert that to 2147483647 <_<

Has this something to do with http://fi.php.net/manual/en/mysqli-stmt.attr-set.php ? Perhaps it defaults to 2^31 data size for integers by default and requires further actions from me? Still it's odd that integer version throws errors while numerical versions do not.

Re: Interesting and buggy?

Posted: Thu Mar 12, 2009 5:07 pm
by kaisellgren
Update:

There is definitely a bug...

TINYINT, SMALLINT and MEDIUMINT all work fine with STMTs. However, INT UNSIGNED, which is capable of storing numbers as high as 2^32-1 throws an out of range error after reaching 2^31-1 point. So UNSIGNEDs are useless. BIGINT, however, will work better. It is capable of storing numbers as high as 2^64-1, but with STMTs we only lose 1024 last numbers, so, we are capable of putting a number as high as (2^64-1)-2^10... crazy!

All string types seem to be fine. VARCHAR, CHAR, TEXT, etc.

Looks like that DOUBLE is also buggy.

EDIT: Ok. I figured it out it wasn't a bug, but bad documentationing..