Re: Update Problem when ORing w/ Long.MIN_VALUE
For the curious: As usual select is not broken. Lesson learned: Always watch out for warnings: http://bugs.mysql.com/bug.php?id=41007 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update Problem when ORing w/ Long.MIN_VALUE
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]
Re: Update Problem when ORing w/ Long.MIN_VALUE
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]