MySQL Bugs: #28492: subselect returns LONG in >5.0.24a and LONGLONG in <=5.0.24a

Bug #28492	subselect returns LONG in >5.0.24a and LONGLONG in <=5.0.24a
Submitted:	17 May 2007 9:17	Modified:	10 Jun 2007 20:35
Reporter:	Shane Bester
Status:	Closed
Category:	Server: General	Severity:	S2 (Serious)
Version:	>5.0.24a	OS:	Any
Assigned to:	Georgi Kodinov	Target Version:
Tags:	regression, subquery

Description:
earlier version of mysql returned LONGLONG compared to the current LONG return in
5.0.42.

in 5.0.24a
----------
mysql> select a.* from (select 2147483648) a;
Field   1:  `2147483648`
Catalog:    `def`
Database:   ``
Table:      `a`
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     10
Max_length: 10
Decimals:   0
Flags:      NOT_NULL NUM

+------------+
| 2147483648 |
+------------+
| 2147483648 |
+------------+
1 row in set (0.11 sec)

in 5.0.42
--------------
mysql> select a.* from (select 2147483648) a;
Field   1:  `2147483648`
Catalog:    `def`
Database:   ``
Table:      `a`
Org_table:  ``
Type:       LONG
Collation:  binary (63)
Length:     10
Max_length: 10
Decimals:   0
Flags:      NOT_NULL NUM

+------------+
| 2147483648 |
+------------+
| 2147483647 |
+------------+
1 row in set (0.01 sec)

This problem is breaking applications that didn't expect such change in behaviour with a
minor release.

How to repeat:
run this on 5.0.24a and on 5.0.42

select a.* from (select 2147483648) a;

observe the different results.

Suggested fix:
.

The issue is triggered in at least 2 positions within the source.

sql_select (line 8841) / item_func (line 436)

    if (max_length > MY_INT32_NUM_DECIMAL_DIGITS)
      res= new Field_longlong(max_length, maybe_null, name, t_arg,
			      unsigned_flag);
    else
      res= new Field_long(max_length, maybe_null, name, t_arg,
			  unsigned_flag);

the IF condition should be ">=" and "MY_INT32.. -1"

    if (max_length >= MY_INT32_NUM_DECIMAL_DIGITS - 1)

because
MY_INT32_NUM_DECIMAL_DIGITS= 11 # includes space for "-" sign

and a number with a length equal the maximum space may be longong.

the error occurs with any number in the range from:
-2147483649 to -9999999999 
 2147483648 to  99999999999

it also happens without subselect 

create table t1 select 2147483648;

I haven't  yet checked for any other occurence of the above code in the source

The behavior change was introduced by bug #19714.

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/27636

ChangeSet@1.2504, 2007-05-30 09:55:38+03:00, gkodinov@magare.gmz +8 -0
  Bug #28492: subselect returns LONG in >5.0.24a and LONGLONG in <=5.0.24a
  
  Integer values with 10 digits may or may not fit into an int column 
  (e.g. 2147483647 vs 6147483647).
  Thus when creating a temp table column for such an int we must
  use bigint instead.
  Fixed to use bigint.
  Also subsituted a "magic number" with a named constant.

Pushed into 5.1.20-beta

Pushed into 5.0.44

Thank you for your bug report. This issue has been committed to our source repository of
that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available
version, including the bug fix. More information about accessing the source trees is
available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.0.44 and 5.1.20 changelogs.