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:
None 
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 16:36] Martin Friebe
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.
[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.