MySQL Bugs: #20964: last_insert(id) does not support bigint unsigned

Bug #20964	last_insert(id) does not support bigint unsigned
Submitted:	11 Jul 2006 11:22	Modified:	20 May 8:48
Reporter:	Martin Friebe (Gold Quality Contributor) (SCA)
Status:	Verified
Category:	Server: General	Severity:	S3 (Non-critical)
Version:	4.1.20, 5.0.22, 5.1.34	OS:	Linux (Linux, freebsd)
Assigned to:		Target Version:
Tags:	qc
Triage:	Triaged: D2 (Serious)

Description:
using last_insert_id on an auto_incremented bigint unsigned does not work for values
greater than max-bigint-signed

the same applies for setting max_insert_id(expr)

create table lid (a bigint unsigned not null auto_increment primary key, b int);
insert into lid select 0x7ffffffffffffffe,1;

insert into lid select null,2; select * from lid; select last_insert_id();
insert into lid select null,3; select * from lid; select last_insert_id();

drop table lid;

select last_insert_id(cast(0xffffffffffffffff as unsigned));
select last_insert_id();

How to repeat:
create table lid (a bigint unsigned not null auto_increment primary key, b int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into lid select 0x7ffffffffffffffe,1;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into lid select null,2; select * from lid; select last_insert_id();
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

+---------------------+------+
| a                   | b    |
+---------------------+------+
| 9223372036854775806 |    1 |
| 9223372036854775807 |    2 |
+---------------------+------+
2 rows in set (0.01 sec)

+---------------------+
| last_insert_id()    |
+---------------------+
| 9223372036854775807 |
+---------------------+
1 row in set (0.00 sec)

mysql> insert into lid select null,3; select * from lid; select last_insert_id();
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

+---------------------+------+
| a                   | b    |
+---------------------+------+
| 9223372036854775806 |    1 |
| 9223372036854775807 |    2 |
| 9223372036854775808 |    3 |
+---------------------+------+
3 rows in set (0.00 sec)

+----------------------+
| last_insert_id()     |
+----------------------+
| -9223372036854775808 |  ## should be the same as the 3rd row in the select above
+----------------------+
1 row in set (0.00 sec)

mysql>
mysql>
mysql> drop table lid;
Query OK, 0 rows affected (0.00 sec)

#  both of the following should be a positive number
mysql> select last_insert_id(cast(0xffffffffffffffff as unsigned));
+------------------------------------------------------+
| last_insert_id(cast(0xffffffffffffffff as unsigned)) |
+------------------------------------------------------+
|                                                   -1 |
+------------------------------------------------------+
1 row in set (0.02 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|               -1 |
+------------------+
1 row in set (0.00 sec)

Suggested fix:
-

Thank you for a bug report. Verified just as described with 5.0.25-BK on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot 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 1 to server version: 5.0.25

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table lid (a bigint unsigned not null auto_increment primary key,
 b
    -> int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into lid select 0x7ffffffffffffffe,1;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into lid select null,2; select * from lid; select last_insert_id(
);
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

+---------------------+------+
| a                   | b    |
+---------------------+------+
| 9223372036854775806 |    1 |
| 9223372036854775807 |    2 |
+---------------------+------+
2 rows in set (0.00 sec)

+---------------------+
| last_insert_id()    |
+---------------------+
| 9223372036854775807 |
+---------------------+
1 row in set (0.00 sec)

mysql> insert into lid select null,3; select * from lid; select last_insert_id(
);
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

+---------------------+------+
| a                   | b    |
+---------------------+------+
| 9223372036854775806 |    1 |
| 9223372036854775807 |    2 |
| 9223372036854775808 |    3 |
+---------------------+------+
3 rows in set (0.00 sec)

+----------------------+
| last_insert_id()     |
+----------------------+
| -9223372036854775808 |
+----------------------+
1 row in set (0.00 sec)

mysql> drop table lid;
Query OK, 0 rows affected (0.01 sec)

mysql> select last_insert_id(cast(0xffffffffffffffff as unsigned));
+------------------------------------------------------+
| last_insert_id(cast(0xffffffffffffffff as unsigned)) |
+------------------------------------------------------+
|                                                   -1 |
+------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|               -1 |
+------------------+
1 row in set (0.00 sec)

This bug is related to bug #20924. Same code changes must be implemented in order to fix
this one.

Bug #39309 was marked as duplicate of this one.

Same on Windows with 5.0.56sp1-enterprise-gpl-nt-log.

'To be fixed later'??? That's a joke, or? 
That's a no go in our planned professional environment. 

Is it fixed in 5.1.x?

Lars, 
thank you for your interest in MySQL.
If you would like to escalate this bug report, and have a support contract, please use
the support channel for that.

Exactly the same results in 5.1.34:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.34-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table lid (a bigint unsigned not null auto_increment primary key,
b int);
Query OK, 0 rows affected (0.39 sec)

mysql> insert into lid select 0x7ffffffffffffffe,1;
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql>
mysql> insert into lid select null,2; select * from lid; select last_insert_id()
;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

+---------------------+------+
| a                   | b    |
+---------------------+------+
| 9223372036854775806 |    1 |
| 9223372036854775807 |    2 |
+---------------------+------+
2 rows in set (0.03 sec)

+---------------------+
| last_insert_id()    |
+---------------------+
| 9223372036854775807 |
+---------------------+
1 row in set (0.06 sec)

mysql> insert into lid select null,3; select * from lid; select last_insert_id()
;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

+---------------------+------+
| a                   | b    |
+---------------------+------+
| 9223372036854775806 |    1 |
| 9223372036854775807 |    2 |
| 9223372036854775808 |    3 |
+---------------------+------+
3 rows in set (0.00 sec)

+----------------------+
| last_insert_id()     |
+----------------------+
| -9223372036854775808 |
+----------------------+
1 row in set (0.00 sec)

mysql>
mysql> drop table lid;
Query OK, 0 rows affected (0.11 sec)

mysql>
mysql> select last_insert_id(cast(0xffffffffffffffff as unsigned));
+------------------------------------------------------+
| last_insert_id(cast(0xffffffffffffffff as unsigned)) |
+------------------------------------------------------+
|                                                   -1 |
+------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|               -1 |
+------------------+
1 row in set (0.00 sec)

Why we expect them to be different?