| Bug #28625 | decimal column used instead of bigint, for max-negative-bigint | ||
|---|---|---|---|
| Submitted: | 23 May 2007 16:36 | Modified: | 10 Jul 2007 22:35 |
| Reporter: | Martin Friebe (Gold Quality Contributor) (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0.42/5.1 | OS: | Any |
| Assigned to: | Gleb Shchepa | CPU Architecture: | Any |
| Tags: | BIGINT, create column, UNSIGNED | ||
[23 May 2007 17:38]
MySQL Verification Team
Thank you for the bug report.
[4 Jun 2007 20:57]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/28085 ChangeSet@1.2506, 2007-06-05 02:17:29+05:00, gshchepa@gleb.loc +10 -0 Fixed bug #28625: DECIMAL column was used instead of BIGINT for the minimal possible BIGINT (-9223372036854775808). The parser didn't recognize negative numeric constants: it created Item_num subclass objects to keep _absolute_ values of given input data and then used Item_func_neg to change sign of those objects at the execution time. The absolute value (9223372036854775808) of the minimal possible BIGINT (-9223372036854775808) is greater than maximal signed positive BIGINT (9223372036854775807), so -9223372036854775808 was saved as DECIMAL value. The grammar has been transformed to the equivalent form distinguishing negative numeric constants from expressions with unary minus operator. After that modification items for negative constants contains negative numbers instead of absolute values, and there is no more need to call Item_func_neg methods for these numbers at execution time. DESCRIBE/SHOW CREATE statement now shows negative numbers in the form of "-10" instead of "-(10)" as column names for CREATE TABLE ... SELECT statement.
[5 Jun 2007 13:20]
Martin Friebe
I hope it is welconme, if I make a comment on the patch
> quote
+ if (!zero)
+ {
+ tmp.length= num->length + 1; // + 1 for '-'
+ tmp.str= thd->alloc(tmp.length + 1); // + 1 for '\0'
+ tmp.str[0]= '-';
+ memcpy(tmp.str + 1, num->str, num->length);
+ tmp.str[tmp.length]= '\0';
+
+ if (!dot)
+ token= int_token(tmp.str, tmp.length);
+ }
Why making a copy of the string? Would it not be less resource-usage to add an optional argument to int_token?
static inline uint int_token(const char *str, uint length, bool IS_NEG = FALSE)
and use the original string?
[5 Jun 2007 14:05]
Gleb Shchepa
Hello Martin, thank you for feedback.
> Why making a copy of the string? Would it not be less
> resource-usage to add an optional argument to int_token?
That copy of string is used as item name in the following code:
item= new Item_int(tmp.str,
(longlong) my_strtoll10(tmp.str, NULL, &error),
tmp.length);
where:
class Item_int :public Item_num
{
...
Item_int(const char *str_arg,longlong i,uint length) :value(i)
{ max_length=length; name=(char*) str_arg; fixed= 1; }
Thus, it is necessary to allocate new string to include '-' sign into item name.
[8 Jun 2007 22:04]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/28443 ChangeSet@1.2506, 2007-06-09 03:26:52+05:00, gshchepa@gleb.loc +3 -0 Fixed bug #28625: DECIMAL column was used instead of BIGINT for the minimal possible BIGINT (-9223372036854775808). The Item_func_neg::fix_length_and_dec has been adjusted to not set hybrid_type to DECIMAL_RESULT for abs(LONGLONG_MIN) argument value.
[8 Jun 2007 22:14]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/28444 ChangeSet@1.2506, 2007-06-09 03:36:32+05:00, gshchepa@gleb.loc +3 -0 Fixed bug #28625: DECIMAL column was used instead of BIGINT for the minimal possible BIGINT (-9223372036854775808). The Item_func_neg::fix_length_and_dec has been adjusted to not set hybrid_type to DECIMAL_RESULT for abs(LONGLONG_MIN) argument value.
[9 Jun 2007 16:04]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/28464 ChangeSet@1.2506, 2007-06-09 21:27:06+05:00, gshchepa@gleb.loc +5 -0 Fixed bug #28625: DECIMAL column was used instead of BIGINT for the minimal possible BIGINT (-9223372036854775808). Partial evaluation mechanism was added to Item_func_neg for the case of LONGLONG_MIN constant argment.
[9 Jun 2007 18:55]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/28471 ChangeSet@1.2506, 2007-06-10 00:17:57+05:00, gshchepa@gleb.loc +3 -0 Fixed bug #28625: DECIMAL column was used instead of BIGINT for the minimal possible BIGINT (-9223372036854775808). On-the-fly evaluation has been added for unary minus operator of Item_uint of value ((ulonglong) LONGLONG_MIN).
[15 Jun 2007 12:04]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/28863 ChangeSet@1.2506, 2007-06-15 17:29:26+05:00, gshchepa@gleb.loc +5 -0 Fixed bug #28625: DECIMAL column was used instead of BIGINT for the minimal possible BIGINT (-9223372036854775808). Direct creation of Item_func_neg object has been replaced with call to wrapper function `neg_expression'. Than function returns new Item_int object instead of Item_func_neg object when underlying object is an Item_uint literal of value 9223372036854775808.
[16 Jun 2007 7:00]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/28919 ChangeSet@1.2506, 2007-06-16 12:25:31+05:00, gshchepa@gleb.loc +3 -0 Fixed bug #28625: DECIMAL column was used instead of BIGINT for the minimal possible BIGINT (-9223372036854775808). The Item_func_neg::fix_length_and_dec has been adjusted to keep the hybrid_type for Item_uint argument of value abs(LONGLONG_MIN) untouched.
[16 Jun 2007 7:39]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/28920 ChangeSet@1.2506, 2007-06-16 13:05:07+05:00, gshchepa@gleb.loc +3 -0 Fixed bug #28625: DECIMAL column was used instead of BIGINT for the minimal possible BIGINT (-9223372036854775808). The Item_func_neg::fix_length_and_dec has been adjusted to to inherit the type of the argument in the case when it's an Item_int object whose value is equal to LONGLONG_MIN.
[16 Jun 2007 11:34]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/28925 ChangeSet@1.2524, 2007-06-16 17:00:29+05:00, gshchepa@gleb.loc +1 -0 bigint.test: Updated test case for bug #28625.
[25 Jun 2007 21:49]
Bugs System
Pushed into 5.1.21-beta
[25 Jun 2007 21:51]
Bugs System
Pushed into 5.0.46
[10 Jul 2007 22:35]
Paul DuBois
Noted in 5.0.46, 5.1.21 changelogs. For a statement of the form CREATE t1 SELECT integer_constant, the server created the column using the DECIMAL data type for large negative values that are within the range of BIGINT.
Description: for a statement of the form create t1 select NUM_CONST; mysql choses the bigint if the value is positive and <= 9223372036854775807 it also chooses bigint for negative values >= -9223372036854775807 For values outside the rang it chooses decimal. However the maximal negative value is -9223372036854775808 How to repeat: drop table if exists t1; create table t1 select -9223372036854775808; desc t1; +----------------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------------+---------------+------+-----+---------+-------+ | -9223372036854775808 | decimal(19,0) | NO | | 0 | | +----------------------+---------------+------+-----+---------+-------+ 1 row in set (0.10 sec) drop table if exists t1; create table t1 select cast(-9223372036854775808 as signed); desc t1; select * from t1; +--------------------------------------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------------------------+------------+------+-----+---------+-------+ | cast(-9223372036854775808 as signed) | bigint(20) | NO | | 0 | | +--------------------------------------+------------+------+-----+---------+-------+ 1 row in set (0.06 sec) +--------------------------------------+ | cast(-9223372036854775808 as signed) | +--------------------------------------+ | -9223372036854775808 | +--------------------------------------+ 1 row in set (0.03 sec) Suggested fix: sql_parse.yy signed_literal: literal { $$ = $1; } | '+' NUM_literal { $$ = $2; } | '-' NUM_literal { $2->max_length++; $$= $2->neg(); } ; NUM_literal, always compares to the max POSITIVE value. which is not correct. in sql_lex.cc is int_token(...) which has handling for +/- signs. Just they never get passed in. This is why NUM_literal always sees the positive max.