[Solved] Interesting and buggy?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

[Solved] Interesting and buggy?

Post 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.
Last edited by kaisellgren on Thu Mar 12, 2009 6:12 pm, edited 1 time in total.
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Re: Interesting and buggy?

Post 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..
Post Reply