MySQL Bugs: #30782: Truncated UNSIGNED BIGINT columns only in SELECT w/ CASE, JOIN, and ORDER BY

Bug #30782	Truncated UNSIGNED BIGINT columns only in SELECT w/ CASE, JOIN, and ORDER BY
Submitted:	4 Sep 2007 8:34	Modified:	9 Jan 2008 14:36
Reporter:	Keefe Johnson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S2 (Serious)
Version:	5.0.45, 4.1, 5.1, 5.2 BK	OS:	Any (Linux, Windows XP SP2)
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any

Description:
I created a minimal example below illustrating what I think is a bug. Everything is fine until the last SELECT, where I use CASE, JOIN, and ORDER BY. Only when using all 3 together, the large unsigned bigint values get truncated to 9999999999999999999 (19 9's), causing the sorting to fail also. As illustrated, if I remove any one of the 3 keywords, the problem doesn't occur. 

How to repeat:
Using the command-line mysql client:

mysql> select version();
+---------------------+
| version()           |
+---------------------+
| 5.0.45-community-nt |
+---------------------+
1 row in set (0.00 sec)

mysql> create table t1 (a int, b bigint unsigned);
Query OK, 0 rows affected (0.07 sec)

mysql> create table t2 (c int);
Query OK, 0 rows affected (0.08 sec)

mysql> insert into t1 (a,b) values (1,4572794622775114594),(2,18196094287899841997),(3,11120436154190595086);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into t2 (c) values (1),(2),(3);
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select t1.a, t1.b d from t1 join t2 on t1.a=t2.c order by d;
+------+----------------------+
| a    | d                    |
+------+----------------------+
|    1 |  4572794622775114594 |
|    3 | 11120436154190595086 |
|    2 | 18196094287899841997 |
+------+----------------------+
3 rows in set (0.00 sec)

mysql> select t1.a, (case t1.a when 0 then 0 else t1.b end) d from t1 order by d;
+------+----------------------+
| a    | d                    |
+------+----------------------+
|    1 |  4572794622775114594 |
|    3 | 11120436154190595086 |
|    2 | 18196094287899841997 |
+------+----------------------+
3 rows in set (0.00 sec)

mysql> select t1.a, (case t1.a when 0 then 0 else t1.b end) d from t1 join t2 on t1.a=t2.c;
+------+----------------------+
| a    | d                    |
+------+----------------------+
|    1 |  4572794622775114594 |
|    2 | 18196094287899841997 |
|    3 | 11120436154190595086 |
+------+----------------------+
3 rows in set (0.00 sec)

mysql> select t1.a, (case t1.a when 0 then 0 else t1.b end) d from t1 join t2 on t1.a=t2.c order by d;
+------+---------------------+
| a    | d                   |
+------+---------------------+
|    1 | 4572794622775114594 |
|    2 | 9999999999999999999 |
|    3 | 9999999999999999999 |
+------+---------------------+
3 rows in set (0.00 sec)

Thank you for the report.

Verified as described.

Can be related with bug #30622

I doubt it is directly related to bug #30622, for the following reasons:
* Bug #30622 seems to only occur with BLOB types. This new bug affects UNSIGNED BIGINT.
* Bug #30622 caused rows to not be returned at all, but as another example of this new bug shows below, a WHERE clause still works, although with the same truncation of the values.
* This new bug only occurs when the SELECT has a CASE and a JOIN in addition to the ORDER BY and optional WHERE.

mysql> select t1.a, (case t1.a when 0 then 0 else t1.b end) d from t1 join t2 on t1.a=t2.c where b=11120436154190595086 order by d;
+------+---------------------+
| a    | d                   |
+------+---------------------+
|    3 | 9999999999999999999 |
+------+---------------------+
1 row in set (0.00 sec)

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

ChangeSet@1.2542, 2007-10-22 17:11:40+05:00, ramil@mysql.com +4 -0
  Fix for bug #30782: Truncated UNSIGNED BIGINT columns only in SELECT w/ CASE, 
  JOIN, and ORDER BY
  
  Problem: improper maximum length calculation of the CASE function leads to 
  decimal value truncation (storing/retrieving decimal field values).
  
  Fix: accurately calculate maximum length/unsigned flag/decimals parameters 
  of the CASE function.

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

ChangeSet@1.2542, 2007-10-29 11:17:51+04:00, ramil@mysql.com +4 -0
  Fix for bug #30782: Truncated UNSIGNED BIGINT columns only in SELECT w/ CASE, 
  JOIN, and ORDER BY
  
  Problem: improper maximum length calculation of the CASE function leads to 
  decimal value truncation (storing/retrieving decimal field values).
  
  Fix: accurately calculate maximum length/unsigned flag/decimals parameters 
  of the CASE function.

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

ChangeSet@1.2542, 2007-10-29 12:20:21+04:00, ramil@mysql.com +4 -0
  Fix for bug #30782: Truncated UNSIGNED BIGINT columns only in SELECT w/ CASE, 
  JOIN, and ORDER BY
  
  Problem: improper maximum length calculation of the CASE function leads to 
  decimal value truncation (storing/retrieving decimal field values).
  
  Fix: accurately calculate maximum length/unsigned flag/decimals parameters 
  of the CASE function.

Pushed into 6.0.5-alpha

Pushed into 5.1.23-rc

Pushed into 5.0.54

Noted in 5.0.54, 5.1.23, 6.0.5 changelogs.

Improper calculation of CASE expression results could lead to value
truncation.