MySQL Bugs: #2158: Order By on BIGINT not working?

Bug #2158	Order By on BIGINT not working?
Submitted:	18 Dec 2003 9:43	Modified:	24 Dec 2003 3:56
Reporter:	Steve Gare	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.0.14	OS:	MacOS (OS X Server 10.3)
Assigned to:		CPU Architecture:	Any

Description:
Sorting on a field that is a bigint doesn't appear to work.. example...

I even created a test table and got a similar result, but the data below is from my actual database..

If anyone requires access to the machine, it can be arranged.. This is the default install that come 
with OS X Server!!

mysql> SELECT * FROM Report_Data WHERE ReportID = 1 ORDER BY Octets Desc;
+------+----------+-------+-------+----------+
| RDID | ReportID | S_ASN | D_ASN | Octets   |
+------+----------+-------+-------+----------+
| 1151 |        1 |   786 |     0 |   263035 |
| 1103 |        1 |     0 |  8553 | 18385786 |
| 1102 |        1 |     0 |  8943 | 39650678 |
| 1144 |        1 |  2914 |     0 |   338801 |
| 1162 |        1 |   702 |     0 |   211054 |
| 1126 |        1 | 29164 |     0 |   913514 |
| 1132 |        1 |     0 |  4813 |   485737 |
| 1113 |        1 | 20799 |     0 |  2019685 |
| 1128 |        1 |     0 |  6728 |   799843 |
| 1172 |        1 |     0 |  4837 |   182113 |
| 1175 |        1 |  5388 |     0 |   176480 |
| 1112 |        1 |     0 |  6871 |  2418527 |
| 1156 |        1 |  4355 |     0 |   239186 |
| 1125 |        1 |  7091 |     0 |   958545 |
| 1161 |        1 | 11631 |     0 |   215377 |
| 1171 |        1 |     0 |  5089 |   190281 |
| 1189 |        1 |  5927 |     0 |   155975 |
| 1124 |        1 | 13285 |     0 |   963397 |
<snip>

mysql> explain Report_Data;
+----------+------------+------+-----+---------+----------------+
| Field    | Type       | Null | Key | Default | Extra          |
+----------+------------+------+-----+---------+----------------+
| RDID     | int(11)    |      | PRI | NULL    | auto_increment |
| ReportID | int(11)    |      |     | 0       |                |
| S_ASN    | int(11)    |      |     | 0       |                |
| D_ASN    | int(11)    |      |     | 0       |                |
| Octets   | bigint(20) |      | MUL | 0       |                |
+----------+------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

mysql> show indexes from Report_Data;
+-------------+------------+----------+--------------+-------------+-----------
+-------------+----------+--------+------+------------+---------+
| Table       | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | 
Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+----------+--------------+-------------+-----------
+-------------+----------+--------+------+------------+---------+
| Report_Data |          0 | PRIMARY  |            1 | RDID        | A         |        1100 |     NULL | NULL   |      
| BTREE      |         |
| Report_Data |          1 | Octets   |            1 | Octets      | A         |        1100 |     NULL | NULL   |      
| BTREE      |         |
+-------------+------------+----------+--------------+-------------+-----------
+-------------+----------+--------+------+------------+---------+
2 rows in set (0.01 sec)

How to repeat:
See above, just created a test table with one field that is a bigint and it produced the same results 
as above....

mysql> select * from test order by test;
+------------+
| test       |
+------------+
|       4534 |
|       4535 |
|        453 |
|          0 |
|   55345432 |
|    5425435 |
| 6543565436 |
+------------+
7 rows in set (0.00 sec)

mysql> explain test;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| test  | bigint(20) |      |     | 0       |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)

It does appear to be just the 4.0.14 that come with OS X Server, However, I don't/didn't want to 
touch it as install in the MySQL.COM version will require down time I can't afford at the moment..

Any suggestions welcome..

We  need  the entire table  to be able   to verify  this.

Your  test table with  example query  not  functioning   is  necessary.

However, if  it works  with  our 4.0.17 binary all   we can do is recommend you
to upgrade to our latest 4.0.17 binary.

uploaded sql file, We'll I guess I'll have to upgrade to 4.0.17 :/ What a pain!

With 4.0.17 ORDER BY worked as a charm:

RDID    ReportID        S_ASN   D_ASN   Octets
1101    1       3356    0       91672005
1102    1       0       8943    39650678
1103    1       0       8553    18385786
1104    1       8943    0       16648433
1105    1       0       9191    12453558
1106    1       0       13285   11818297
1107    1       0       29164   11582350
1108    1       0       4589    10740673
1109    1       0       0       10353381
1110    1       0       20799   8027163
1111    1       4589    0       4097788
1112    1       0       6871    2418527
1113    1       20799   0       2019685
1114    1       0       3356    1791743
1115    1       8553    0       1582113
1116    1       3402    0       1222195
1117    1       3557    0       1201284
1118    1       9146    0       1116346
1119    1       9191    0       1114360
1120    1       3352    0       1091203
1121    1       0       3402    1057982
1122    1       0       3557    1048086
1123    1       0       7091    965328
1124    1       13285   0       963397
1125    1       7091    0       958545
1126    1       29164   0       913514
1127    1       4813    0       814517
1128    1       0       6728    799843
1129    1       0       4134    748685
1130    1       4134    0       740151
1131    1       11840   0       586710
1132    1       0       4813    485737
1133    1       5617    0       481981
1134    1       0       15169   412724
1135    1       0       9146    405639
1136    1       6871    0       403915
1137    1       701     0       394705
1138    1       0       5462    378922
1139    1       7342    0       363660
1140    1       3292    0       360745
1141    1       12008   0       358460
1142    1       0       2856    346082
1143    1       13749   0       339146
1144    1       2914    0       338801
1145    1       0       577     327998
1146    1       0       702     289733
1147    1       10532   0       287683
1148    1       22653   0       287169
1149    1       0       20712   274464
1150    1       16260   0       265941
1151    1       786     0       263035
1152    1       20806   0       261660
1153    1       0       3352    258261
1154    1       14744   0       249608
1155    1       6079    0       240297
1156    1       4355    0       239186
1157    1       10912   0       233448
1158    1       0       3320    231346
1159    1       6992    0       228861
1160    1       8674    0       219204
1161    1       11631   0       215377
1162    1       702     0       211054
1163    1       0       701     208626
1164    1       19836   0       208111
1165    1       23148   0       204204
1166    1       3320    0       203066
1167    1       14745   0       201004
1168    1       2856    0       199314
1169    1       8220    0       194091
1170    1       7132    0       193794
1171    1       0       5089    190281
1172    1       0       4837    182113
1173    1       24730   0       178668
1174    1       10515   0       176662
1175    1       5388    0       176480
1176    1       3064    0       175007
1177    1       20848   0       173219
1178    1       13      0       173090
1179    1       29216   0       173056
1180    1       4       0       172583
1181    1       1290    0       171240
1182    1       27      0       167365
1183    1       2529    0       165899
1184    1       25152   0       164310
1185    1       6728    0       164260
1186    1       7500    0       163727
1187    1       12616   0       157491
1188    1       2149    0       157130
1189    1       5927    0       155975
1190    1       0       11840   151734
1191    1       14992   0       151334
1192    1       6395    0       150678
1193    1       0       13749   149665
1194    1       7018    0       148967
1195    1       20144   0       143908
1196    1       5089    0       143746
1197    1       0       23342   142141
1198    1       26499   0       141356
1199    1       8586    0       141335
1200    1       0       2914    140289

Hmmm, Apple released 10.3.2 Server which upgraded MySQL to 4.0.16, and it has the same 
problem.. Most odd, Any ideas?

Our binary or Apple's ??

If Apple's, then we shall have to add them to the long list of those that have
problems in building a proper MySQL binary.

Apple's :/ I've submitted a bug report to them, not that it'll achieve anything ;)

Any one else using the OS X Server 10.3.2 binary of 4.0.16 who can verify it's not just something 
weird with my XServe before I try the MySQL.COM binary

This issue is now fixed in the Mac OS X Server 10.3.3 update:

http://docs.info.apple.com/article.html?artnum=107851#sql

Mac OS X Server 10.3.3 includes a new binary of MySQL 4.0.18 that fixes the issue that was present in 
the included MySQL in Mac OS X Server 10.3, 10.3.1, and 10.3.2.