Bug #8009 SELECT failed on bigint unsigned
Submitted: 19 Jan 2005 13:55 Modified: 24 May 2005 15:28
Reporter: Gérard Toulgoat Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.8/4.1.10 BK OS:Microsoft Windows (Windows XP SP)
Assigned to: Jani Tolonen CPU Architecture:Any

[19 Jan 2005 13:55] Gérard Toulgoat
Description:
Field = BIGINT UNSIGNED
Select with hexadecimal format fails if negative value

... where hash=0x8000000000000000  -> failed
but the same in decimal:
... where hash=9223372036854775808  -> OK

This worked on mySQL 3.23.58 (see trace hereafter)

How to repeat:
********************************************************************************
Test sur mySQL 4.1.8 (NOK)
********************************************************************************
C:\TOOLS\MySQL\bin>mysqladmin version
mysqladmin  Ver 8.41 Distrib 4.1.8, for Win95/Win98 on i32

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

mysql> insert into test values (0x7FFFFFFFFFFFFFFF);
Query OK, 1 row affected (0.01 sec)

mysql> insert into test values (0x8000000000000000);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test where hash=0x7FFFFFFFFFFFFFFF;
+---------------------+
| hash                |
+---------------------+
| 9223372036854775807 |
+---------------------+
1 row in set (0.03 sec)

mysql> select * from test where hash=0x8000000000000000;
Empty set (0.00 sec)

mysql> select * from test where hash=9223372036854775808;
+---------------------+
| hash                |
+---------------------+
| 9223372036854775808 |
+---------------------+
1 row in set (0.00 sec)

********************************************************************************
Test sur mySQL 3.23.58 (OK)
********************************************************************************

D:\tools\mySQL\bin>mysqladmin version
mysqladmin  Ver 8.23 Distrib 3.23.58, for Win95/Win98 on i32

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

mysql> insert into test values (0x7FFFFFFFFFFFFFFF);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values (0x8000000000000000);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test where hash=0x7FFFFFFFFFFFFFFF;
+---------------------+
| hash                |
+---------------------+
| 9223372036854775807 |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from test where hash=0x8000000000000000;
+---------------------+
| hash                |
+---------------------+
| 9223372036854775808 |
+---------------------+
1 row in set (0.00 sec)
[19 Jan 2005 15:54] MySQL Verification Team
Tested also on Slackware Linux.
[24 May 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/25224
[24 May 2005 15:28] Jani Tolonen
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 bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html