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.

score 10 · Accepted Answer · edited Sep 30 '13 at 09:34

10

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)

edited Sep 30 '13 at 09:34

lorenzo-s

16,603
15
54
86

answered Apr 21 '11 at 11:08

Ben

34,935
6
74
113

2

According to [the docs here](http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html#sqlmode_no_unsigned_subtraction): _By default, subtraction between integer operands produces an UNSIGNED result if any operand is UNSIGNED._ – Wiseguy Nov 12 '11 at 14:27
I have also faced the same problem after upgrading to Mysql5.5 See here http://dev.mysql.com/doc/refman/5.5/en/out-of-range-and-overflow.html – Omesh Aug 02 '12 at 10:22
`CAST(x AS BIGINT SIGNED`) throws an error. According to [docs](http://dev.mysql.com/doc/refman/5.5/en/cast-functions.html#function_cast), only `SIGNED` should be used (or `SIGNED INTEGER`). I edited answer according to that. – lorenzo-s Sep 30 '13 at 09:36

score 5 · Answer 2 · answered Jan 14 '14 at 11:22

5

SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';

Call this before the query is executed.

answered Jan 14 '14 at 11:22

cornfelt

91
1
5

score 1 · Answer 3 · edited Sep 27 '12 at 11:34

1

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

edited Sep 27 '12 at 11:34

Fluffeh

33,228
16
67
80

answered Aug 12 '12 at 16:24

Andreas

11
1

BIGINT Out-of-range Error since MySQL 5.5

3 Answers3

Linked

Related