Bug #7425 inconsistent sort order on unsigned columns result of substraction
Submitted: 20 Dec 2004 11:35 Modified: 10 Mar 2005 17:57
Reporter: Martin Friebe (Gold Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.7 (probably others) OS:Any (any)
Assigned to: Timour Katchaounov CPU Architecture:Any

[20 Dec 2004 11:35] Martin Friebe
Description:
substracting 2 unsigned integers will return an unsigned integer. If the result would be negative, it will be "wraped" and become a large positive unsigned.
  select cast(-1 as unsigned); #  18446744073709551615

As far as I am aware this is the expected behaviour. This seems to be ignored during "order by", see example below (18446744073709551615 < 0)

The 2nd example shows that comparinfg the result against 0 works fine. Mysql is not handling the value as "-1" in the 2nd column, but it does in the "order by".

The inconsistence is even clearer when using having (example 3 and 4):
example 3 shows that mysql things of "a-b >=0" as true for all rows.
example 4 shows that this does not apply if "a-b >= 0" is in the having clause.

How to repeat:
create table t1 (a int(11) unsigned, b int(11) unsigned);
insert into t1 values (1,0), (1,1), (1,2);

select a-b  from t1 order by 1;
+----------------------+
| a-b                  |
+----------------------+
| 18446744073709551615 |
|                    0 |
|                    1 |
+----------------------+

select a-b , (a-b < 0)  from t1 order by 1;
+----------------------+-----------+
| a-b                  | (a-b < 0) |
+----------------------+-----------+
| 18446744073709551615 |         0 |
|                    0 |         0 |
|                    1 |         0 |
+----------------------+-----------+

# examples with having
select a-b as d,  (a-b >= 0) , b from t1 group by b ;
+----------------------+------------+------+
| d                    | (a-b >= 0) | b    |
+----------------------+------------+------+
|                    1 |          1 |    0 |
|                    0 |          1 |    1 |
| 18446744073709551615 |          1 |    2 |
+----------------------+------------+------+

select a-b as d,  (a-b >= 0) , b from t1 group by b having d >= 0;
+------+------------+------+
| d    | (a-b >= 0) | b    |
+------+------------+------+
|    1 |          1 |    0 |
|    0 |          1 |    1 |
+------+------------+------+

Suggested fix:
-
[9 Mar 2005 14:51] 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/internals/22844
[9 Mar 2005 14:54] Timour Katchaounov
The reported problems were due to two completely unrelated omissions.
1) The file sort procedure didn't correctly create the sort key in
   make_sortkey() when the sortkey was an unsigned integer.
2) The name resolution procedure for column references inside a HAVING
   clause did not propagate the unsigned_flag of the resolved references.
[10 Mar 2005 11:52] 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/internals/22887
[10 Mar 2005 16:03] Timour Katchaounov
Pushed in 4.1.11 and 5.0.3.
[10 Mar 2005 17:57] Paul DuBois
Noted in 4.1.11, 5.0.3 changelogs.