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]
