Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2(EOL), 10.3(EOL)
-
None
Description
This script with a bad literal in DEFAULT correctly returns the "Out of range" error:
create or replace table t1 (a int not null default 0) engine=MyISAM; |
alter table t1 add b tinyint not null default 1000; |
ERROR 1264 (22003): Out of range value for column 'b' at row 1
|
With ENGINE=InnoDB and the same literal it also return the same error:
create or replace table t1 (a int not null default 0) engine=InnoDB; |
alter table t1 add b tinyint not null default 1000; |
ERROR 1264 (22003): Out of range value for column 'b' at row 1
|
With ENGINE=MyISAM and a bad function in DEFAULT, it also returns the same error:
set timestamp=default; |
create or replace table t1 (a int not null default 0) engine=myisam; |
insert into t1 values (10); |
alter table t1 add b tinyint not null default if(unix_timestamp()>1,1000,0); |
ERROR 1264 (22003): Out of range value for column 'b' at row 1
|
So far so good.
With ENGINE=InnoDB and a bad function in DEFAULT, it does not return any errors:
set timestamp=default; |
create or replace table t1 (a int not null default 0) engine=innodb; |
insert into t1 values (10); |
alter table t1 add b tinyint not null default if(unix_timestamp()>1,1000,0); |
This looks wrong.
DESCRIBE reports that the ALTER did actually happen:
DESCRIBE t1;
|
+-------+------------+------+-----+---------------------------------+-------+
|
| Field | Type | Null | Key | Default | Extra |
|
+-------+------------+------+-----+---------------------------------+-------+
|
| a | int(11) | NO | | 0 | |
|
| b | tinyint(4) | NO | | if(unix_timestamp() > 1,1000,0) | |
|
+-------+------------+------+-----+---------------------------------+-------+
|
And this query tells that 1000 was silently changed to 127:
SELECT * FROM t1; |
+----+-----+
|
| a | b |
|
+----+-----+
|
| 10 | 127 |
|
+----+-----+
|
InnoDB should be fixed to return the error, like MyISAM does, and like InnoDB does with literals.