MySQL Bugs: #64084: Connector returning Long instead of Int

Bug #64084	Connector returning Long instead of Int
Submitted:	20 Jan 2012 16:47	Modified:	23 Feb 2012 19:19
Reporter:	Sam Kimmel	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	5	OS:	Windows (Win 7 Pro 64-bit)
Assigned to:	John Russell	CPU Architecture:	Any

Description:
Using MySQL Server 5.5.17, MySQL Connector/Net 6.4.4, and .Net 4.0

When attempting to unbox objects returned by MySqlCommand.ExecuteScalar() and MySqlDataReader.GetValue(int index) as an integer I receive "Specified cast is not valid" exceptions.

Examples:

* INSERT INTO test (Name) VALUES (@Name); SELECT LAST_INSERT_ID() As ID;
* INSERT INTO test (Name) VALUES (@Name); SELECT CONVERT(LAST_INSERT_ID(), SIGNED INTEGER) As ID;
* INSERT INTO test (Name) VALUES (@Name); SELECT CAST(LAST_INSERT_ID() As SIGNED) As ID;
* SELECT 1 AS ID;
* SELECT CONVERT(1, SIGNED INTEGER) As ID;
* SELECT CAST(1 As SIGNED) As ID;

I found that in all these instances, the values were boxed from long instead of int.

How to repeat:
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["skimmel"].ConnectionString)) 
{ 
    using (MySqlCommand cmd = new MySqlCommand(@"SELECT CONVERT(1, SIGNED INTEGER);", con)) // explicitly cast to signed integer
    { 
        con.Open(); 
 
        object val = cmd.ExecuteScalar(); 
        Type t = val.GetType(); // Reveals that it is coming back as Int64
        long l = (long)val; 
        int i1 = Convert.ToInt32(val); 
        int i2 = (int)val;  // <-- Error here because you cannot unbox Int64 to Int32 
    } 
}

You are right, server returns these data as LONGLONG, that is, 64 bit integer:

macbook-pro:5.5 openxs$ bin/mysql -uroot --column-type-info test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 25
Server version: 5.5.20-debug-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT CAST(1 As SIGNED) As ID;
Field   1:  `ID`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     1
Max_length: 1
Decimals:   0
Flags:      NOT_NULL BINARY NUM 

+----+
| ID |
+----+
|  1 |
+----+
1 row in set (0.11 sec)

mysql> SELECT LAST_INSERT_ID() As ID;
Field   1:  `ID`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     21
Max_length: 1
Decimals:   0
Flags:      NOT_NULL BINARY NUM 

+----+
| ID |
+----+
|  0 |
+----+
1 row in set (0.10 sec)

but a) why do expect something else, 32 bit int, and b) why do you think the bug is in connector, if it is just the metadata server returns?

Based on the information you posted, it is not a bug in the Connector. If the server is telling you it is LONG there isn't much you can do.

Would you say this is a bug in MySQL Server if I am explicitly converting to a 32-bit integer but it is returning it as LONG from the server?

Sorry, but where do you convert to 32-bit integer on server side? Please, read http://dev.mysql.com/doc/refman/5.5/en/cast-functions.html#function_cast:

"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 11.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."

64-bit is mentioned explicitly. I'd say this is not a bug actually.

Overlooked the part talking about 64-bit with CAST/CONVERT. Kind of curios as to why they don't support CAST as SMALLINT/BIGINT?

The only reason I am casting in the first place is that LAST_INSERT_ID() is returning a 64-bit number when the identity column is only 32-bits.

mysql> SELECT id FROM webpage LIMIT 1;
Field 1:	'ID'
Catalog:	'def'
Database:	'skimmel'
Table:		'webpage'
Org_table:	'webpage'
Type:		LONG
Collation:	binary (63)
Length:		11
Max_length:	1
Decimals:	0
Flags:		NOT_NULL PRI_KEY AUTO_INCREMENT NUM PART_KEY

+----+
| ID |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql> INSERT INTO webpage (Name) VALUES ('Test'); SELECT LAST_INSERT_ID();
Query OK, 1 row affected (0.00 sec)

Field 1:	'LAST_INSERT_ID()'
Catalog:	'def'
Database:	''
Table:		''
Org_table:	''
Type:		LONGLONG
Collation:	binary (63)
Length:		21
Max_length:	2
Decimals:	0
Flags:		NOT_NULL BINARY NUM

+------------------+
| LAST_INSERT_ID() |
+------------------+
|               10 |
+------------------+
1 row in set (0.00 sec)

Maybe I overlooked something in the documentation and LAST_INSERT_ID() will always return 64-bit integer as well?

I do not see this mentioned in the manual, http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_last-insert-id. But I assume it's by design.

For your CAST request, we have verified verified feature requests for a long time already. See bug #16904 and bug #62926.

Do you want me to make a documentation request out of this bug report?

Might be nice to have a line in there about it returning 64-bit integer. Thank you for your help; I really appreciate it.

Let's treat this as a documentation request for last_insert_id() for now. We shell see where it will end up.

Purely informational, as I do not know if this is the intended implementation or not...

Out of curiosity I went and created a test numbers table in which I included all the number types.

CREATE TABLE `test`.`IntTest` (
  `TestBigInt` BIGINT NOT NULL,
  `TestUBigInt` BIGINT UNSIGNED NOT NULL,
  `TestInt` INTEGER NOT NULL,
  `TestUInt` INTEGER UNSIGNED NOT NULL,
  `TestMedInt` MEDIUMINT NOT NULL,
  `TestUMedInt` MEDIUMINT UNSIGNED NOT NULL,
  `TestSmallInt` SMALLINT NOT NULL,
  `TestUSmallInt` SMALLINT UNSIGNED NOT NULL,
  `TestTinyInt` TINYINT NOT NULL,
  `TestUTinyInt` TINYINT UNSIGNED NOT NULL
)
ENGINE = InnoDB;

When I queried for column type info, I found it interesting that I got LONG as the type for INT, and LONG LONG for BigInt. If this is indeed the case, shouldn't the connector be converting LONG to int and LONG LONG to long? Or am I fundamentally misunderstanding something about MySQL?

mysql> SELECT * FROM IntTest;
Field   1:  `TestBigInt`
Catalog:    `def`
Database:   `test`
Table:      `IntTest`
Org_table:  `inttest`
Type:       LONGLONG
Collation:  binary (63)
Length:     20
Max_length: 0
Decimals:   0
Flags:      NOT_NULL NO_DEFAULT_VALUE NUM

Field   2:  `TestUBigInt`
Catalog:    `def`
Database:   `test`
Table:      `IntTest`
Org_table:  `inttest`
Type:       LONGLONG
Collation:  binary (63)
Length:     20
Max_length: 0
Decimals:   0
Flags:      NOT_NULL UNSIGNED NO_DEFAULT_VALUE NUM

Field   3:  `TestInt`
Catalog:    `def`
Database:   `test`
Table:      `IntTest`
Org_table:  `inttest`
Type:       LONG
Collation:  binary (63)
Length:     11
Max_length: 0
Decimals:   0
Flags:      NOT_NULL NO_DEFAULT_VALUE NUM

Field   4:  `TestUInt`
Catalog:    `def`
Database:   `test`
Table:      `IntTest`
Org_table:  `inttest`
Type:       LONG
Collation:  binary (63)
Length:     10
Max_length: 0
Decimals:   0
Flags:      NOT_NULL UNSIGNED NO_DEFAULT_VALUE NUM

Field   5:  `TestMedInt`
Catalog:    `def`
Database:   `test`
Table:      `IntTest`
Org_table:  `inttest`
Type:       INT24
Collation:  binary (63)
Length:     9
Max_length: 0
Decimals:   0
Flags:      NOT_NULL NO_DEFAULT_VALUE NUM

Field   6:  `TestUMedInt`
Catalog:    `def`
Database:   `test`
Table:      `IntTest`
Org_table:  `inttest`
Type:       INT24
Collation:  binary (63)
Length:     8
Max_length: 0
Decimals:   0
Flags:      NOT_NULL UNSIGNED NO_DEFAULT_VALUE NUM

Field   7:  `TestSmallInt`
Catalog:    `def`
Database:   `test`
Table:      `IntTest`
Org_table:  `inttest`
Type:       SHORT
Collation:  binary (63)
Length:     6
Max_length: 0
Decimals:   0
Flags:      NOT_NULL NO_DEFAULT_VALUE NUM

Field   8:  `TestUSmallInt`
Catalog:    `def`
Database:   `test`
Table:      `IntTest`
Org_table:  `inttest`
Type:       SHORT
Collation:  binary (63)
Length:     5
Max_length: 0
Decimals:   0
Flags:      NOT_NULL UNSIGNED NO_DEFAULT_VALUE NUM

Field   9:  `TestTinyInt`
Catalog:    `def`
Database:   `test`
Table:      `IntTest`
Org_table:  `inttest`
Type:       TINY
Collation:  binary (63)
Length:     4
Max_length: 0
Decimals:   0
Flags:      NOT_NULL NO_DEFAULT_VALUE NUM

Field  10:  `TestUTinyInt`
Catalog:    `def`
Database:   `test`
Table:      `IntTest`
Org_table:  `inttest`
Type:       TINY
Collation:  binary (63)
Length:     3
Max_length: 0
Decimals:   0
Flags:      NOT_NULL UNSIGNED NO_DEFAULT_VALUE NUM

0 rows in set (0.00 sec)

I downloaded the source and started stepping through. The MySqlReader does correctly return Int32 for INGETER. Now I'm trying to figure out why the Entity Framework provider converts all my INT, Auto Increment, Signed primary keys to long in C# when using database first.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Documented that LAST_INSERT_ID() returns BIGINT (64 bits).

The Multiple Birds Hypothesis
                                                by The Lazy MySQL Dev

One day a little boy wanted to kill seven birds and no less with one stone. He
carefully aimed, released the well tensioned slingshot and .... Oh sh--. The
ulonglong stone hit the BIGINT window of his grandmother's house. But don't 
worry, he quickly wrote in the house's documentation that the window is 
supposed to be broken and everyone lived Happily Ever After!, despite the rain,
snow, cold wind and scorching hit coming through the broken (by design) 
window.
 
In your honest opinion of expert architects and developers:
	Is this behaviour semantically correct?

Consider this:
	AUTO_INCREMENT can be used on at least the following types of data columns: 
		BIGINT,
		INT, 
		MEDIUMINT, 
		SMALLINT, 
		TINYINT,
		DOUBLE, 
		FLOAT.
		
	The LAST_INSERT_ID() is documented as returning the last generated 
	AUTO_INCREMENT field value for a table.
	
	I expect that if my column is typed INT(11) the function will return a 
	data type consistent with the type of my column (ergo INT(11)) and not
	a different type of data.
	
	I am sorry to say this, but upgrading the documentation in this case was 
	a shameless short-cut to avoiding to fix the bug.
	
In it's current state the LAST_INSERT_ID() in MySQL is as useful as a 
rubber hammer in a match against The Incredible Hulk.

I am sorry to be so a__l. I can only hope someone can understand how frustrated
I am...

Sincerely yours,
Strongly Typed Lt. Data,