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)
