Conversion of bigint unsigned column to bigint signed fails

Question

I get a syntax error in a MySql database version 7.0

SELECT
    r.id,
    r.number,
    r.numbertype,
    r.forhandler,
    LAG(r.number) OVER (PARTITION BY r.numbertype ORDER BY r.number) AS last_row_number,
    LEAD(r.number) OVER (PARTITION BY r.numbertype ORDER BY r.number) AS next_row_number,
    r.number -(LAG(r.number) OVER (PARTITION BY r.numbertype ORDER BY r.number)) AS gap_last_rk,
    CAST (r.number-(LEAD(r.number) OVER (PARTITION BY r.numbertype ORDER BY r.`number`)) AS BIGINT SIGNED)  AS gap_next_rk

FROM admin.numberranges r
WHERE r.status=2
ORDER BY r.number;

The syntax error is in my CAST part. My column NUMBER that is a BIG INT UNSIGNED.

I tried convert as well -:(

Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'BIGINT SIGNED) AS neg_number

Answer 1

First, you have a space after CAST, which can result in other parse errors/issues with your question. You have to use CAST(...). Second, the type BIGINT SIGNED is not allowed, check the list for CAST(expr AS type). When you want a signed number you use the type SIGNED or SIGNED INTEGER, as described in the documentation:

The type can be one of the following values:

[...]

• SIGNED [INTEGER]

See the following queries on how to use the CAST() function (examples run on MySQL 8.0.23, the result might not be the same for MariaDB but the type restrictions are similar, see the MySQL documentation of CONVERT(expr, type)):

mysql> EXPLAIN Dummy;
+-------+-----------------+------+-----+---------+-------+
| Field | Type            | Null | Key | Default | Extra |
+-------+-----------------+------+-----+---------+-------+
| Test  | bigint unsigned | YES  |     | NULL    |       |
+-------+-----------------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> SELECT Test, CAST(Test AS BIGINT SIGNED) FROM Dummy;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
                    corresponds to your MySQL server version for the right syntax
                    to use near 'BIGINT SIGNED) FROM Dummy' at line 1
mysql> SELECT Test, CAST(Test AS SIGNED) FROM Dummy;
+------+----------------------+
| Test | CAST(Test AS SIGNED) |
+------+----------------------+
| 1234 |                 1234 |
+------+----------------------+
1 row in set (0.00 sec)