MySQL Bugs: #7425: inconsistent sort order on unsigned columns result of substraction

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

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:
-

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

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.

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

Pushed in 4.1.11 and 5.0.3.

Noted in 4.1.11, 5.0.3 changelogs.