Bug #11215 BIGINT: can't set DEFAULT to minimum end-range
Submitted: 9 Jun 2005 18:17 Modified: 29 Jun 2005 20:27
Reporter: Trudy Pelzer Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.8-beta-debug OS:Linux (SuSE 9.1)
Assigned to: Ramil Kalimullin

[9 Jun 2005 18:17] Trudy Pelzer
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)
[9 Jun 2005 18:25] Miguel Solorzano
Thank you for the bug report.
[22 Jun 2005 12:34] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26302
[29 Jun 2005 5:58] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26505
[29 Jun 2005 7:22] Ramil Kalimullin
fixed in 5.0.9
[29 Jun 2005 20:27] Mike Hillyer
Documented in 5.0.9 changelog.