Why does CAST(1 AS SIGNED INTEGER) return a BIGINT on MySQL? If I do CAST(1 AS SIGNED INTEGER) I always end up getting a BIGINT returned, for example:

$ mysql -u root -p --column-type-info
Enter password:

--- Copyright and help message snipped for brevity ---

mysql&gt; select cast(1 as signed integer);
Field   1:  `cast(1 as signed integer)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG            &lt;== LONGLONG i.e. 64 bit integer
Collation:  binary (63)
Length:     1
Max_length: 1
Decimals:   0
Flags:      NOT_NULL BINARY NUM


+---------------------------+
| cast(1 as signed integer) |
+---------------------------+
|                         1 |
+---------------------------+
1 row in set (0.00 sec)


I would have expected the return type from that cast to be a LONG (32 bit integer). 

If I select a column from a table that has an INT I see that it is indeed just a LONG:

mysql&gt; describe contact;

+------------+---------+------+-----+---------+----------------+
| Field      | Type    | Null | Key | Default | Extra          |
+------------+---------+------+-----+---------+----------------+
| contact_id | int(11) | NO   | PRI | NULL    | auto_increment |

      == remainder of table snipped ==

mysql&gt; select contact_id from contact where contact_id = 20;
Field   1:  `contact_id`
Catalog:    `def`
Database:   `centreon`
Table:      `contact`
Org_table:  `contact`
Type:       LONG                     &lt;== LONG i.e. 32 bit integer
Collation:  binary (63)
Length:     11
Max_length: 2
Decimals:   0
Flags:      NOT_NULL PRI_KEY AUTO_INCREMENT NUM PART_KEY


+------------+
| contact_id |
+------------+
|         20 |
+------------+
1 row in set (0.00 sec)

mysql&gt;


If I cast the same column to a signed integer, I again get a 64 bit integer returned:

mysql&gt; select CAST(contact_id as signed integer) from contact where contact_id = 20;
Field   1:  `CAST(contact_id as signed integer)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     11
Max_length: 2
Decimals:   0
Flags:      NOT_NULL BINARY NUM


+------------------------------------+
| CAST(contact_id as signed integer) |
+------------------------------------+
|                                 20 |
+------------------------------------+
1 row in set (0.00 sec)


There is a similarly reported issue here:


  http://bugs.mysql.com/bug.php?id=64084


But sadly the OP doesn't get a straight answer.

Is this a bug in the CAST() function or is this by design?
As you can see on https://www.w3schools.com/sql/func_mysql_cast.asp

SIGNED    Converts value to SIGNED (a signed 64-bit integer)
UNSIGNED  Converts value to UNSIGNED (an unsigned 64-bit integer)

And from the official MySQL documentation: https://dev.mysql.com/doc/refman/5.7/en/cast-functions.html

MySQL supports arithmetic with both signed and unsigned 64-bit values. For numeric operators (such as + or -) where one of the operands is an unsigned integer, the result is unsigned by default (see Section 12.6.1, “Arithmetic Operators”). To override this, use the SIGNED or UNSIGNED cast operator to cast a value to a signed or unsigned 64-bit integer, respectively.

So looks like the CAST output will actually be a 64 bits integer when you use SIGNED or UNSIGNED datatypes.