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 (0xffffffff, 0xffffffffffffffff), (-1, -1);
mysql> select hex(id), hex(val) from foo;
+------------------+------------------+
| hex(id) | hex(val) |
+------------------+------------------+
| 7FFFFFFF | 7FFFFFFFFFFFFFFF |
| FFFFFFFFFFFFFFFF | FFFFFFFFFFFFFFFF |
+------------------+------------------+
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) |
0x8000000000000000;
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 | 0x8000000000000000 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 | 0x7000000000000000 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) | 0x8000000000000000 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]
