Cannot insert/update unsigned integers or numbers bigger than an integer
markus_petrux - November 13, 2008 - 12:32
| Project: | Drupal |
| Version: | 6.x-dev |
| Component: | database system |
| Category: | bug report |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | active |
Jump to:
Description
Schema API supports unsigned integers, but Database API does not allow inserting/updating unsigned integers.
I think it's because there's %u placeholder. Any chance to get this fixed for D6?
Please note that one could workaround this building the SQL statement by hand, but this is not an option when using a CCK field that implements an unsigned integer because CCK uses db_type_placeholder(). If %u was added in D6, then CCK would have to be fixed as well (to use db_type_placeholder properly for unsigneds).
#1
Oops! typo: I tried to mean "there's NOT %u placeholder".
#2
PHP does not support unsigned integers. What exactly do you want to do?
#3
Integer size in PHP is platform dependent, which means DB unsigned ints are supported by PHP on x64 CPUs. But on x32 CPUs, numbers beyond builtin integer bounds are interpreted as a float instead, so you can still use 4294967295 + 1.
Please, test the following on x32:
<?phpprint gettype(4294967295) ."\n"; // output: (double)
print sprintf("%u", 4294967295) ."\n"; // output: 4294967295
print sprintf("%d", 4294967295) ."\n"; // output: -1
?>
When using %d for an unsigned int in db_query(), 4294967295 is converted to -1 by the %d placeholder, and updating a INT UNSIGNED field in the DB with -1 you get 0. :(
But, if we could use %u, then it would work. :)
Example of what we need:
<?php
// This does not work as expected.
db_query("INSERT INTO {foo} (my_unsigned_int) VALUES(%d)", 4294967295);
// This is not supported by Database API due to lack of %u placeholder.
db_query("INSERT INTO {foo} (my_unsigned_int) VALUES(%u)", 4294967295);
?>
We also need to support unsigned integers for this:
http://drupal.org/project/formatted_number
#4
Please note that Schema API supports unsigned integers, and even supports bigint and unsigned bigint, but ....how do you insert data into such fields?
bigint support is much more complex, because it might be necessary to use BCMath/GMP extensions. That's another war I would not like to mix here.
But... support for unsigned integers? Please, consider adding this to D6. Otherwise we are restricted to -2147483648 / 2147483647 with both PHP and DB Layers able to deal with bigger numbers.
#5
subscribing.
#6
Ok, we should replace:
<?phpcase '%d': // We must use type casting to int to convert FALSE/NULL/(TRUE?)
return (int) array_shift($args); // We don't need db_escape_string as numbers are db-safe
?>
by
<?phpcase '%d': // We must use type casting to int to convert FALSE/NULL/(TRUE?)
return round(array_shift($args)); // We don't need db_escape_string as numbers are db-safe
?>
Right?
As a side note, the %d modifier is the dumbest modifier ever.
sprintf("%d", 4294967295) == "-1"? Come on, PHP is supposed to be loosely typed!#7
Ran into the bigint issue while using drupal_write_record().
Apparently the problem lies in db_type_placeholder() function, which uses '%d' as the placeholder for all 'int' type columns, regardless of size.
case 'serial':case 'int':
return '%d';
#8
I am, too, affected by the discrepancy between the ability to create 'unsigned int' columns in the db and the absence of the '%u' modifier equivalent in database.inc.
Substituting the int cast with round() is far from ideal, because of how the result of round() depends on the fraction (clearly seen in the examples in the php manual) and PHP_ROUND_HALF_DOWN is not implemented in versions of PHP earlier than 5.3. Also, augmenting the '%d' modifier in database.inc is also not advisable as this would constitute a departure from the printf convention which seems to be followed here, where '%d' signifies a signed integer. Both approaches could potentially break existing code that universally relies on those modifiers returning expected values within their appropriate ranges.
#9
Just ran into this due to Twitter module's issues with the number of total twitter statuses rolling over the signed int limit. To reiterate: we must do one of the following:
Currently, we're giving developers a data type that amounts to a trap.
#10
Yikes. Subscribing.
#11
I would opt for option 1.
http://api.drupal.org/api/function/db_type_placeholder
http://drupal.org/node/159605
Pass the size variable.
<?phpcase 'int':
return $size == 'big' ? '%n' : '%d';
?>
I don't see an easy way to handle unsigned ints right now.
EDIT:
Scratch the above idea... create a new type called bigint
#12
I advocate changing the priority of this ticket to critical and that once a reasonable fix is found, it be pushed into the next release of Drupal 6.
This is a multitude of problems just waiting to happen as the number of "things" get beyond signed 32 bit integers -- possibly including security holes.