MySQL Bugs: #65031: Expression type BIGINT UNSIGNED

Bug #65031	Expression type BIGINT UNSIGNED
Submitted:	19 Apr 2012 8:24	Modified:	8 Nov 2014 8:52
Reporter:	Milos Tomic	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.5.21-log, 5.5.23	OS:	Windows (win7 x64)
Assigned to:		CPU Architecture:	Any

Description:
When tbl.col is int unsigned
SELECT @a:=1;
SELECT @b:=20;
UPDATE tbl SET col = col + (@a - @b) WHERE col > @b;
Produces error
BIGINT UNSIGNED value is out of range in '((@a) - (@b))'

but w/out () like
UPDATE tbl SET col = col + @a - @b WHERE col > @b;
it works and does not break

Also, if col is int (not unsigned) it works.

How to repeat:
DROP TABLE IF EXISTS `TreeTest`;
CREATE TABLE `TreeTest` (
  `Id` INT(10) UNSIGNED NOT NULL,
  `Title` VARCHAR(100) NOT NULL,
  `ParentID` INT(10) UNSIGNED DEFAULT NULL,
  `Level` TINYINT(3) UNSIGNED NOT NULL,
  `Lft` INT(10) UNSIGNED NOT NULL,
  `Rgt` INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT  INTO `TreeTest`(`Id`,`Title`,`ParentID`,`Level`,`Lft`,`Rgt`) VALUES (1,'TOPP',NULL,1,1,18),(2,'IKKE REGISTRERT',1,2,16,17),(101,'OSLO',105,3,3,8),(102,'BERGEN',105,3,9,10),(103,'ADMINISTRASJON',101,4,4,5),(104,'PRODUKSJON',101,4,6,7),(105,'HULDT & LILLEVIK',1,2,2,15),(106,'Manual Minus',105,3,11,12),(107,'Default Minus',105,3,13,14);

START TRANSACTION;

SELECT @NodeLft:= Lft -- 16
  ,@NodeRgt:= Rgt -- 17
  ,@NodeDist:= Rgt - Lft + 1 -- 2
  ,@NodeLevel:= LEVEL -- 2
FROM TreeTest
WHERE Id = 2;

SELECT @RefNodeLft:= Lft -- 4
  ,@RefNodeRgt:= Rgt -- 5
  ,@RefNodeLevel:= `Level` -- 4
  ,@RefNodeParentID := ParentID -- 101
  ,(Lft BETWEEN @NodeLft AND @NodeRgt) AS IsChild -- 0
  ,@MovedNodeLft := IF(@NodeLft < @RefNodeLft, @NodeLft, @NodeLft + @NodeDist) -- 18
FROM TreeTest
WHERE Id = 103;

UPDATE TreeTest SET
  Lft = IF(Lft >= @RefNodeLft, Lft + @NodeDist, Lft)
  ,Rgt = IF(Rgt >= @RefNodeLft, Rgt + @NodeDist, Rgt)
WHERE Rgt >= @RefNodeLft;

UPDATE TreeTest SET
  Lft = Lft + (@RefNodeLft - @MovedNodeLft) -- works w/out ()
  ,Rgt = Rgt + (@RefNodeLft - @MovedNodeLft) 
  ,ParentID = IF( Id = 2 , @RefNodeParentID, ParentID)
  ,`Level` = ( `Level` - @NodeLevel + @RefNodeLevel )
WHERE Lft >= @MovedNodeLft AND Rgt <= @MovedNodeLft + @NodeDist - 1;

UPDATE TreeTest SET
  Lft = IF(Lft >= @MovedNodeLft , Lft - @NodeDist , Lft)
  ,Rgt = IF(Rgt > @MovedNodeLft , Rgt - @NodeDist , Rgt)
WHERE Rgt > @MovedNodeLft;

COMMIT;

Suggested fix:
Do not determine (expression) data type in col = col + (expression) based on the assigning column type

Verified with just one row in the table and a bit more simple test case:

mysql> select * from `Treetest`;
+----+-------+----------+-------+-----+-----+
| Id | Title | ParentID | Level | Lft | Rgt |
+----+-------+----------+-------+-----+-----+
|  1 | TOPP  |     NULL |     1 |   1 |  18 |
+----+-------+----------+-------+-----+-----+
1 row in set (0.00 sec)

mysql> update `TreeTest` set rgt = rgt + (@a - @b);
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`test`.`treetest`
.`Rgt` + ((@a) - (@b)))'
mysql> select @a, @b;
+------+------+
| @a   | @b   |
+------+------+
|    1 |   20 |
+------+------+
1 row in set (0.00 sec)

Now, this is more strange, as resulting value is > 0:

mysql> update `TreeTest` set rgt = rgt + (@a - @b) + 2;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`test`.`treetest`
.`Rgt` + ((@a) - (@b)))'

and this way it works:

mysql> update `TreeTest` set rgt = rgt + 2 + (@a - @b);
Query OK, 1 row affected (0.44 sec)
Rows matched: 1  Changed: 1  Warnings: 0

A bit unexpected to get different behavior by changing order of evaluation explicitly if final value to assign to the column is the same.

Similiar error output when doing this on 64bit Windows machines:

DROP TABLE tt;
CREATE TABLE tt (value SMALLINT UNSIGNED);

INSERT INTO tt VALUES (10);

SELECT * FROM tt;

SELECT * FROM tt ORDER BY (5-value);

On 32bit everything is runnign fine. I don't know if both problems are related.

For got the error message:

ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in tt.value

Posted by developer:
 
Bug is not repeatable with version 5.6.21.

Posted by developer:
 
Ups! I forgot to set variable values. SO only ORDER BY case is not repeatable.

[7 Nov 2014 20:54] Chaithra Gopalareddy

@Chaithra -
From the bug page, it looks like its not a bug.
This is what we find in our documentation currently.
"By default, subtraction between integer operands produces an UNSIGNED result
if any operand is UNSIGNED."

So when we have an expression like "value - 10" and if value is < 10, then we
are trying to store a signed value in an unsigned result type. Hence the
error.

For this we have a documented work-around -
Setting sql_mode to - NO_UNSIGNED_SUBTRACTION.
"When NO_UNSIGNED_SUBTRACTION is enabled, the subtraction result is signed,
even if any operand is unsigned. "

The existing behavior w.r.t the error being thrown is due to a bugfix/WL that
was done once to disallow out-of-range numeric values and give an error in
those cases. The bugfix was necessary in order to align MySQL with standard
SQL.

The following alternative has been suggested.
To make sure that the operands are signed. Like in this case:

 CREATE TABLE t1(
    id INTEGER UNSIGNED PRIMARY KEY,
    a INTEGER UNSIGNED,
    b INTEGER UNSIGNED);

  INSERT INTO t1 VALUES(1, 7, 4);

  SELECT @a:= a, @b:= b from t1;

  SELECT b + (@b - @a) from t1;

The above SELECT will fail.

But with the following CASTs, the subtraction is carried out using signed
arithmetic:

  SELECT b + (CAST(@b AS SIGNED INTEGER) - CAST(@a AS SIGNED INTEGER)) from
t1;

We can also make the variables signed in the assignment:

  SELECT @a:= CAST(a AS SIGNED INTEGER), @b:= CAST(b AS SIGNED INTEGER) from
t1;

and the original SELECT will succeed.