[MDEV-14038] ALTER TABLE does not exit on error with InnoDB + bad default function Created: 2017-10-10  Updated: 2017-10-11  Resolved: 2017-10-11

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table, Storage Engine - InnoDB
Affects Version/s: 10.2, 10.3
Fix Version/s: 10.2.10, 10.3.3

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Marko Mäkelä
Resolution: Fixed Votes: 0
Labels: 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.

 Comments   
Comment by Marko Mäkelä [ 2017-10-10 ]

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.

Comment by Alexander Barkov [ 2017-10-11 ]

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.

Generated at Thu Feb 08 08:10:25 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.