Search code examples
mysqlnested-setsoutofrangeexception

BIGINT Out-of-range Error since MySQL 5.5

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.

Solution

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