Hi Gautam
nope yours is not a bug. That's all fine. Hex numbers are 64 bit
unsigned.
So for -1 you have to insert cast(0x as signed).
Cheers,
Daniel
Hi Daniel,
I can see the problem without using update. However, I am a newbie
at
mysql,
so can't say for certain if it's a bug:
mysql drop table if exists foo;
mysql create table foo (id int signed, val bigint signed);
mysql insert into foo values (0x, 0x), (-1,
-1);
mysql select hex(id), hex(val) from foo;
+--+--+
| hex(id) | hex(val) |
+--+--+
| 7FFF | 7FFF |
| | |
+--+--+
2 rows in set (0.00 sec)
Regards
Gautam
Daniel Doubleday wrote:
Hi everybody -
I'm experiencing some really weird update behaviour (mysql 5.0) when
or'ing results from subselects using Long.MIN_VALUE.
But before I post a bug report I wanted to ask if I'm missing
something.
drop table if exists foo;
drop table if exists bar;
create table foo (fooid int, fooval bigint);
create table bar (barid int, barval bigint);
insert into foo values (1, null), (2, null);
insert into bar values (1, 123), (2, 345);
update foo set fooval = (select barval from bar where barid =
fooid) |
0x8000;
select * from foo;
# +---+-+
# | fooid | fooval |
# +---+-+
# | 1 | 9223372036854775807 |
# | 2 | 9223372036854775807 |
# +---+-+
# 2 rows in set (0.00 sec)
# Oops result is Long.MAX_VALUE (as if subselect result was 0 - bit
logic result is always unsigned bigint)
# Same thing when you replace subselect by multi table update syntax
update foo, bar set fooval = barval | 0x8000 where
fooid =
barid;
select * from foo;
# +---+-+
# | fooid | fooval |
# +---+-+
# | 1 | 9223372036854775807 |
# | 2 | 9223372036854775807 |
# +---+-+
# 2 rows in set (0.00 sec)
# and it seems that its all about MSB sign bit, cause thats fine:
update foo, bar set fooval = barval | 0x7000 where
fooid =
barid;
select * from foo;
# +---+-+
# | fooid | fooval |
# +---+-+
# | 1 | 8070450532247928955 |
# | 2 | 8070450532247929177 |
# +---+-+
# 2 rows in set (0.00 sec)
# and casting the or result! does the trick too though I dont
understand why ...
update foo set fooval = cast((select barval from bar where barid =
fooid) | 0x8000 as signed);
select * from foo;
# +---+--+
# | fooid | fooval |
# +---+--+
# | 1 | -9223372036854775685 |
# | 2 | -9223372036854775463 |
# +---+--+
# 2 rows in set (0.00 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]