BIGINT Out-of-range Error since MySQL 5.5

Question

I'm working with nested sets for my CMS but since MySQL 5.5 I can't move a node.
The following error gets thrown:

Error while reordering docs:Error in MySQL-DB: Invalid SQL:

 SELECT baum2.id AS id,
 COUNT(*) AS level
 FROM elisabeth_tree AS baum1,
 elisabeth_tree AS baum2
 WHERE baum2.lft BETWEEN baum1.lft AND baum1.rgt
 GROUP BY baum2.lft
 ORDER BY ABS(baum2.id - 6);

error: BIGINT UNSIGNED value is out of range in '(lektoren.baum2.id - 6)'
error number: 1690

Has anyone solved this Problem? I already tried to cast some parts but it wasn't successful.

Unblinking

Answer 1

BIGINT UNSIGNED is unsigned and cannot be negative.

Your expression ABS(lektoren.baum2.id - 6) will use a negative intermediate value if id is less than 6.

Presumably earlier versions implicitly converted to SIGNED. You need to do a cast.

Try

ORDER BY ABS(CAST(lectoren.baum2.id AS SIGNED) - 6)

Crack

According to the docs here: By default, subtraction between integer operands produces an UNSIGNED result if any operand is UNSIGNED. – Comminate

I have also faced the same problem after upgrading to Mysql5.5 See here dev.mysql.com/doc/refman/5.5/en/out-of-range-and-overflow.html – Assr

CAST(x AS BIGINT SIGNED) throws an error. According to docs, only SIGNED should be used (or SIGNED INTEGER). I edited answer according to that. – Uranian

Answer 2

SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';

Call this before the query is executed.

Gabler

Answer 3

ORDER BY ABS(CAST(lectoren.baum2.id AS BIGINT SIGNED) - 6)

That change would be mysql only.

instead, do

ORDER BY ABS(- 6 + baum2.id);

Hoicks