MySQL Bugs: #41007: Update Problem when ORing w/ Long.MIN

Bug #41007	Update Problem when ORing w/ Long.MIN_VALUE
Submitted:	25 Nov 2008 11:21	Modified:	26 Nov 2008 10:13
Reporter:	Daniel Doubleday	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.0.67	OS:	Any (Tested on OSX and Linux Debian)
Assigned to:		CPU Architecture:	Any

Description:
Ok - I asked all mysql gurus I know + the list - so now I'm here :-)

I'm having a really weird problem with updates when OR'ing subselect with Long.Min_VALUE.

So here's how it goes:

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)

# 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)

How to repeat:
See description

Thank you for a problem report. What exact version of MySQL server, 5.0.x, do you use?

Reproduced on 5.0.51a and 5.0.51b

Please, try to repeat with a newer version, 5.0.67, and inform about the results.

Wow don't want to sound rude but I have formated the bugreport in a way that you just have to paste it in an sql client ...

Confirmed on 5.0.67

No change

Indeed, I've got similar results with 5.0.70 (just wanted to be sure there was no change of behaviour in between). But according to the manual (http://dev.mysql.com/doc/refman/5.0/en/bit-functions.html) this is not a bug, as the result of bit function is documented as unsigned 64-bit integer.

I know that the result o bitfunctions is unsigned.

But that was not my point:

update foo set fooval = (select barval from bar where barid = fooid) | 0x8000000000000000;

should evaluate as

update foo set fooval = 123 | 0x8000000000000000

but it does not.

Actually the result of the subselect basically ignored (the result is just unsigned 0x8000000000000000):

# +-------+---------------------+
# | fooid | fooval              |
# +-------+---------------------+
# |     1 | 9223372036854775807 | should be 123 | 0x8000000000000000 = 9223372036854775931
# |     2 | 9223372036854775807 | should be 345 | 0x8000000000000000 = 9223372036854776153
# +-------+---------------------+
# 2 rows in set (0.00 sec)

Sorry for taking that much time to get it right... All major MySQL versions give the same result here. Indeed, you get the same values for both rows and it looks like a bug, but had you noted the warnings:

mysql> update foo set fooval = (select barval from bar where barid = fooid) |
    -> 0x8000000000000000;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
Rows matched: 2  Changed: 0  Warnings: 2

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1264
Message: Out of range value for column 'fooval' at row 2
*************************** 2. row ***************************
  Level: Warning
   Code: 1264
Message: Out of range value for column 'fooval' at row 2
2 rows in set (0.02 sec)

As usual for MySQL, out of range value, if allowed at all (it depends on engine and sql_mode), is replaced by maximum possible value for a signed(!) fooval column. You will get the same result with explicit 123 | 0x8...:

mysql> update foo set fooval=1;
Query OK, 2 rows affected (0.06 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> update foo set fooval=123|0x8000000000000000 where fooid=1;
Query OK, 1 row affected, 1 warning (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> select * from foo;
+-------+---------------------+
| fooid | fooval              |
+-------+---------------------+
|     1 | 9223372036854775807 |
|     2 |                   1 |
+-------+---------------------+
2 rows in set (0.00 sec)

Autsch I see. My fooval is signed. Aargh.

Sorry about that and thanks for the lesson ...