Description:
The minimum value of a signed BIGINT is -9223372036854775808.
Every value within the acceptable range should be allowed as a
DEFAULT value for columns defined as BIGINT. This works correctly
for both ends of the unsigned range and for the maximum value
of the signed range, but fails for the minimum value of the signed
range.
How to repeat:
mysql> create table t1 (col1 bigint default -9223372036854775807);
Query OK, 0 rows affected (0.01 sec)
mysql> show create table t1;
+-------+---------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`col1` bigint(20) default '-9223372036854775807'
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into t1 values (default);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+----------------------+
| col1 |
+----------------------+
| -9223372036854775807 |
+----------------------+
1 row in set (0.00 sec)
-- This is the correct result, and shows that MySQL correctly
accepts a DEFAULT that is one less than the absolute minimum.
mysql> create table t2 (col1 bigint default -9223372036854775808);
ERROR 1067 (42000): Invalid default value for 'col1'
-- This is not the correct result. The value shown is the
smallest value that can be accepted by BIGINT and should be
supported as a DEFAULT value as well.
The following examples show that MySQL is correctly accepting
DEFAULT values set for the other end points of the BIGINT range.
mysql> create table t2 (col1 bigint default 9223372036854775807);
Query OK, 0 rows affected (0.01 sec)
-- Upper end of a signed BIGINT
mysql> show create table t2;
+-------+--------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------+
| t2 | CREATE TABLE `t2` (
`col1` bigint(20) default '9223372036854775807'
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into t2 values (default);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t2;
+---------------------+
| col1 |
+---------------------+
| 9223372036854775807 |
+---------------------+
1 row in set (0.00 sec)
mysql> create table t3 (col1 bigint unsigned default 0);
Query OK, 0 rows affected (0.01 sec)
-- Lower end of an unsigned BIGINT
mysql> show create table t3;
+-------+-----------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------+
| t3 | CREATE TABLE `t3` (
`col1` bigint(20) unsigned default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> insert into t3 values (default);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t3;
+------+
| col1 |
+------+
| 0 |
+------+
1 row in set (0.00 sec)
mysql> create table t4 (col1 bigint unsigned default 18446744073709551615);
Query OK, 0 rows affected (0.01 sec)
-- Upper end of an unsigned BIGINT
mysql> show create table t4;
+-------+------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------+
| t4 | CREATE TABLE `t4` (
`col1` bigint(20) unsigned default '18446744073709551615'
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into t4 values (default);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t4;
+----------------------+
| col1 |
+----------------------+
| 18446744073709551615 |
+----------------------+
1 row in set (0.01 sec)
