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.
Attachments
Activity
The same problem is repeatable with this script:
CREATE OR REPLACE TABLE t1 (a INT) ENGINE=InnoDB; |
ALTER TABLE t1 ADD COLUMN b LINESTRING DEFAULT POINT(1,1); |
DESCRIBE t1;
|
It correctly returns the error:
ERROR 1366 (22007): Incorrect LINESTRING value: 'POINT' for column 'b' at row 1
|
But the DESCRIBE query tells that the ALTER in fact happened:
+-------+------------+------+-----+------------+-------+
|
| Field | Type | Null | Key | Default | Extra |
|
+-------+------------+------+-----+------------+-------+
|
| a | int(11) | YES | | NULL | |
|
| b | linestring | YES | | point(1,1) | |
|
+-------+------------+------+-----+------------+-------+
|
Looks wrong. The column b should not have been added.
If I change ENGINE to MyISAM, it correctly refuses to do ALTER.
I think that InnoDB should behave the same as MyISAM when ALGORITHM=COPY or old_alter_table=1 is in effect.
The problem ought to be that Field::set_default() is not returning an error to ha_innobase::check_if_supported_inplace_alter(), so InnoDB did not know that the evaluation failed.