533646 - [MS-SQL] numeric values greater than 2147483647 throw 'Numeric value out of range'

Status: RESOLVED WONTFIX

(Bugzilla :: Database, defect)

Description

[Michael Thomas (Mockodin)]

Numbers larger than 2147483647 will cause mssql to throw fatal 'Numeric value out of range' errors.

However different databases handle them differently. For example MySQL will silently truncates the data:
http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html
(The precise behavior is operating system-specific, but generally the effect is truncation to the allowable number of digits.)

Bugzilla shouldn't rely on this however and should throw a graceful error when possible.. before it hits the database so that we can tell the user what they did wrong. For example /show_bug.cgi?id=2147483648 could be intercepted in Bugzilla::Object do something like:

 ThrowCodeError('my_error_string_here_explaining_what_happened',
                            {function => $class . '::_init'}) if $id > 214748367;

Alternatively bitint could be used, then max value is 9223372036854775807. Not that it solves the problem as then a number > 9223372036854775807 will do the same thing. Bugzilla is unlikely to have a bug value that high in the next 30 years but none the less allowing the user to input something that the database will choke on is not advisable.

Comment 1

[Max Kanat-Alexander]

 <mockodin> mkanat: bleh, see my ticket on int values and mssql handling?
<mkanat> mockodin: I did, and I'm probably going to WONTFIX it, but I will explain why.
 mockodin: If the column is too small, the solution is to make the column larger.
 mockodin: If you try to put a value into the column that's too large, I'm totally happy to have the database reject it.
<mockodin> mkanat: yes, but gracefulness is more my point I guess
<mkanat> mockodin: Yeah, but that's not something we can check for everywhere.
 mockodin: If we used an ORM that knew the type of every column, we could.
 mockodin: But I'd rather just leave the database to do that level of validation.
<mkanat> mockodin: I mean, we'd be adding massive amounts of code to Bugzilla just to handle a situation that I've never, ever experienced, and nobody has ever reported as an actual problem.
 mockodin: That's another thing--in a way, it's fixing a problem that hasn't ever happened.

Comment 2

[Michael Thomas (Mockodin)]

Likely fix for this will be to set raiserror=0 and process errors and warnings code side. Default behavior would mimic the database, die for errors, ignore warnings. Then errors could be selectively handled with sane error messages.