Re: MySQL 4.1.7: cast(1-2, unsigned) != cast('18446744073709551615' as unsigned)

[Robin Bryce]

Ah, Excelent. Thanks for looking at this. The use context that exposed 
this was using uuids as primary keys. I was breaking the result of 
uuid() into two parts, and then storing into a pair of bigint unsigned 
fields that formed a composite primary key:

CREATE TABLE `test`.`uuidkeys` (
 `uuidlo` bigint(20) unsigned NOT NULL default '0',
 `uuidhi` bigint(20) unsigned NOT NULL default '0',
 `meta_data` varchar(255) default NULL,
 PRIMARY KEY  (`uuidlo`,`uuidhi`)
) ENGINE=InnoDB;
set @uuidkey=uuid();
set @uuidkey='FFFFFFFF-FFFF-FFFF-FFFF-FFFFFFFF';
insert into test.uuidkeys (uuidlo, uuidhi) values ( 
conv(left(replace(convert(@uuidkey using 
latin1),'-',''),16),16,10),conv(right(replace(convert(@uuidkey using 
latin1),'-',''),16),16,10));

select * from `test`.`uuidkeys` where uuidlo = 18446744073709551615;
+----------------------+----------------------+-----------+
| uuidlo               | uuidhi               | meta_data |
+----------------------+----------------------+-----------+
| 18446744073709551615 | 18446744073709551615 | NULL      |
+----------------------+----------------------+-----------+
select * from `test`.`uuidkeys` where uuidlo = 
cast('18446744073709551615' as unsigned);

Empty set (0.00 sec)
Prety new to DB/SQL stuff so no idea if paired bigints offer any real 
advantage over char(32) in this case; Comments on this most welcome! My 
life is easier now I'm using the latter.

Cheers,
Robin
Gleb Paharenko wrote:
Hello.
I've submitted a bug  http://bugs.mysql.com/7036.
Robin Bryce <[EMAIL PROTECTED]> wrote:
 

Hi,
I'm having trouble converting to and from strings that represent 
unsigned BIGINT's.

My server is MySQL 4.1.7-standard and
the following selects were entered at the prompt of my client with 
version "mysql Ver 14.7 Distrib 4.1.7, for pc-linux (i686)".

select cast(1-2, unsigned);
gives the expected 18446744073709551615. This is one of the 'cast' 
examples on http://dev.mysql.com/doc/mysql/en/Cast_Functions.html.

select cast(18446744073709551615 as char);
yields the expected '18446744073709551615'.
The converse doesn't hold:
cast('18446744073709551615' as unsigned);
yields '9223372036854775807' better known as '0x7fffffffffffffff'.
Can anyone tell me what is happening here ?
Thanks,
Robin Bryce
   


 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]