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 lev开发者_JAVA百科el
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)'
Has anyone solved this Problem? I already tried to cast some parts but it wasn't successful.
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)
SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';
Call this before the query is executed.
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);
精彩评论