Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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> select cast(1 as signed integer);
Field   1:  `cast(1 as signed integer)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG            <== 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> describe contact;

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

      == remainder of table snipped ==

mysql> select contact_id from contact where contact_id = 20;
Field   1:  `contact_id`
Catalog:    `def`
Database:   `centreon`
Table:      `contact`
Org_table:  `contact`
Type:       LONG                     <== 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>

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

mysql> 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?

share|improve this question
    
Looking at the docs for cast()/convert(), it only mentions 64-bit integers dev.mysql.com/doc/refman/5.7/en/… –  Phil Jan 23 at 12:48
    
@Phil - I did read that over and over. Why does it say SIGNED [INTEGER] in the section The type for the result can be one of the following values:. Is a SIGNED INTEGER in the context of a CAST not in fact a 32 bit integer? –  Kev Jan 23 at 12:55
    
I was reading the "MySQL supports arithmetic with both signed and unsigned 64-bit values. If you are using numeric operators (such as + or -) and one of the operands is an unsigned integer, the result is unsigned by default (see Section 12.6.1, “Arithmetic Operators”). You can override this by using the SIGNED or UNSIGNED cast operator to cast a value to a signed or unsigned 64-bit integer, respectively." part –  Phil Jan 23 at 13:54
    
@Phil - yeah I read that too, and indeed that behaves as expected i.e. doing SELECT 1+1 results in a BIGINT. But it still doesn't explain away why CAST() behaves contrary to the documentation (as I understand it) and produces a BIGINT even if asked to cast to SIGNED INTEGER or UNSIGNED INTEGER on a single scalar value. –  Kev Jan 23 at 14:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.