Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11, 11.0(EOL)
Description
--source include/have_innodb.inc
|
|
|
create table t (a int) engine=InnoDB; |
insert into t values (0); |
alter table t add b int default 0 check(b!=a); |
show create table t; |
select * from t; |
|
|
# Cleanup
|
drop table t; |
|
10.4 ff3d4395 |
alter table t add b int default 0 check(b!=a); |
show create table t; |
Table Create Table |
t CREATE TABLE `t` ( |
`a` int(11) DEFAULT NULL, |
`b` int(11) DEFAULT 0 CHECK (`b` <> `a`) |
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
select * from t; |
a b
|
0 0
|
An even simpler, but a degenerate test case is
create table t (a int) engine=InnoDB; |
insert into t values (0); |
alter table t add b int check(a!=a); |
Result is the same.
Not reproducible with algorithm=copy.
Attachments
Issue Links
- blocks
-
MDEV-16356 Allow ALGORITHM=NOCOPY for ADD CONSTRAINT
-
- Open
-
Part of the problem here is that there no flag exists for adding a CHECK constraint. The only flag that will be set is one for adding the column:
11.1 2b61ff8f2221745f0a96855a0feb0825c426f993
0x0000561ffffe56f3 in fill_alter_inplace_info (thd=thd@entry=0x7f5c08002108,table=table@entry=0x7f5c080a2658, varchar=<optimized out>,ha_alter_info=ha_alter_info@entry=0x7f5c2c0a6c20)at /mariadb/11/sql/sql_table.cc:68326832 ha_alter_info->handler_flags|= ALTER_ADD_STORED_BASE_COLUMN;The same problem occurs also with the following:
In this case, a different flag (which is ignored by InnoDB) will be set by fill_alter_inplace_info():
6596 if (alter_info->flags & ALTER_CHANGE_COLUMN)6597 ha_alter_info->handler_flags|= ALTER_COLUMN_DEFAULT;I think that by default, adding any CHECK constraints must imply ALGORITHM=COPY. Similar to adding FOREIGN KEY constraints, there could be an override mechanism. Ultimately, in MDEV-16356, we can allow ALGORITHM=NOCOPY for adding such constraints, by implementing a validation pass.
This is something that needs to be fixed outside the storage engine. InnoDB has nothing to do with CHECK constraints. (I assume that it also fails to enforce them related to FOREIGN KEY, similar to triggers: MDEV-12302.)