Bug #28492 subselect returns LONG in >5.0.24a and LONGLONG in <=5.0.24a
Submitted: 17 May 2007 7:17 Modified: 10 Jun 2007 18:35
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: General Severity:S2 (Serious)
Version:>5.0.24a OS:Any
Assigned to: Georgi Kodinov CPU Architecture:Any
Tags: regression, subquery

[17 May 2007 7:17] Shane Bester
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:
.
[22 May 2007 12:44] Martin Friebe
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
[29 May 2007 16:18] Georgi Kodinov
The behavior change was introduced by bug #19714.
[30 May 2007 6:56] 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/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.
[4 Jun 2007 21:20] Bugs System
Pushed into 5.1.20-beta
[4 Jun 2007 21:22] Bugs System
Pushed into 5.0.44
[10 Jun 2007 18:35] Jon Stephens
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.