MySQL Bugs: #70039: Fix for bug #20964 was a breaking change

Bug #70039	Fix for bug #20964 was a breaking change
Submitted:	14 Aug 2013 21:04	Modified:	28 Aug 2013 3:32
Reporter:	Jeremiah Gowdy	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	5.5.33	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
As I said in the comments of 20964 well after it was closed:

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

The fix for this bug was a breaking change, and isn't reflected in the latest documentation.

When upgrading from earlier point releases of MySQL, sticking with the same major branch, our code which does a type cast from the expected return type of BIGINT or signed 64bit integer, now throws an exception because the type returned is UNSIGNED BIGINT or unsigned 64bit integer.  I believe a less breaking bug fix would be to make the return value UNSIGNED BIGINT when the primary key of the table is UNSIGNED BIGINT, and otherwise leave the type as BIGINT.

Secondly, the documentation still claims that LAST_INSERT_ID() returns type BIGINT:

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_last-insert-id

This kind of breaking change to fix what is obviously an edge case, within the same GA branch of MySQL is really disruptive and unexpected.  Is there a reason a breaking API change was made mid-stream on a GA branch?  Can we at least update the documentation for LAST_INSERT_ID() to reflect that it returns BIGINT prior to this patch being merged, and UNSIGNED BIGINT after the change?

How to repeat:
Develop software for MySQL 5.5.11.  Upgrade MySQL to 5.5.33.  Notice exceptions in your data access code because LAST_INSERT_ID() is now an unsigned BIGINT.

Hello Jeremiah,

Thank you for the report.
Verified as described.

Thanks,
Umesh

// How to repeat

mysql> create table t1(id int(11) not null auto_increment primary key)engine=innodb;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 value();
Query OK, 1 row affected (0.00 sec)

mysql> create temporary table tlid as select id, last_insert_id() as a from t1;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> desc tlid;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id    | int(11)             | NO   |     | 0       |       |
| a     | bigint(21) unsigned | NO   |     | 0       |       |
+-------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Imho - this seems to be intended change and just should be documented to avoid any

Hello Jeremiah and Umesh,  we are working on this issue.

Thanks,
Srinivasarao.

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.

You're right, the documentation did not reflect this change; here's the revised changelog entry
(for 5.1.67, 5.5.29, 5.6.9, 5.7.0):

LAST_INSERT_ID(expr) did not work for expr values greater than the
largest signed BIGINT value. Such arguments now are accepted, with
some consequences for compatibility with previous versions:
      
* LAST_INSERT_ID() now returns a BIGINT UNSIGNED value, not a BIGINT
  (signed) value.

* For AUTO_INCREMENT columns, negative values are no longer supported.

I'll also revise the description of LAST_INSERT_ID() in the functions chapter
correspondingly.

Addendum to changelog entry:

LAST_INSERT_ID(expr) now returns an unsigned integer value, not a
signed integer value.