diff options
author | Sergei Golubchik <serg@mariadb.org> | 2022-11-03 19:17:25 +0100 |
---|---|---|
committer | Sergei Golubchik <serg@mariadb.org> | 2023-01-17 15:28:56 +0100 |
commit | cce76fef381a92bf76f39d4da13981472ebb4cb7 (patch) | |
tree | b62f5a2d281e07a97effd0ba0c89afb80457af65 | |
parent | a5eff044cb8543cc207ec51965a1d8fd51dd0576 (diff) | |
download | mariadb-git-cce76fef381a92bf76f39d4da13981472ebb4cb7.tar.gz |
ADD CONSTRAINT IF NOT EXISTS didn't work in SP
"if not exists" must be stored in a separate read-only property
-rw-r--r-- | mysql-test/main/constraints.result | 34 | ||||
-rw-r--r-- | mysql-test/main/constraints.test | 17 | ||||
-rw-r--r-- | sql/field.h | 2 | ||||
-rw-r--r-- | sql/sql_lex.h | 2 | ||||
-rw-r--r-- | sql/sql_table.cc | 4 |
5 files changed, 54 insertions, 5 deletions
diff --git a/mysql-test/main/constraints.result b/mysql-test/main/constraints.result index 53787fb5b65..105ea7cf1f4 100644 --- a/mysql-test/main/constraints.result +++ b/mysql-test/main/constraints.result @@ -183,7 +183,9 @@ t1 CREATE TABLE `t1` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP PROCEDURE sp; DROP TABLE t1; +# # End of 10.2 tests +# create table t1 (a int check (a>10)) select 100 as 'a'; show create table t1; Table Create Table @@ -201,3 +203,35 @@ a 19 ccc drop table t1; +create table t1 (a int, b int); +create procedure sp() alter table t1 add constraint if not exists foo check (b > 0); +call sp; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + CONSTRAINT `foo` CHECK (`b` > 0) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +call sp; +Warnings: +Note 1826 Duplicate CHECK constraint name 'foo' +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + CONSTRAINT `foo` CHECK (`b` > 0) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +call sp; +Warnings: +Note 1826 Duplicate CHECK constraint name 'foo' +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + CONSTRAINT `foo` CHECK (`b` > 0) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +drop procedure sp; +drop table t1; diff --git a/mysql-test/main/constraints.test b/mysql-test/main/constraints.test index 2f4dadcee9d..5c673f9be81 100644 --- a/mysql-test/main/constraints.test +++ b/mysql-test/main/constraints.test @@ -151,7 +151,9 @@ show create table t1; DROP PROCEDURE sp; DROP TABLE t1; +--echo # --echo # End of 10.2 tests +--echo # # # Check that we don't lose constraints as part of CREATE ... SELECT @@ -172,3 +174,18 @@ insert into t1 values ("ccc"); insert into t1 values (""); select * from t1; drop table t1; + +# +# add if not exists in SP +# + +create table t1 (a int, b int); +create procedure sp() alter table t1 add constraint if not exists foo check (b > 0); +call sp; +show create table t1; +call sp; +show create table t1; +call sp; +show create table t1; +drop procedure sp; +drop table t1; diff --git a/sql/field.h b/sql/field.h index 941090ed846..43bcfe5590a 100644 --- a/sql/field.h +++ b/sql/field.h @@ -558,7 +558,6 @@ static inline const char *vcol_type_name(enum_vcol_info_type type) #define VCOL_AUTO_INC 16 #define VCOL_IMPOSSIBLE 32 #define VCOL_NEXTVAL 64 /* NEXTVAL is not implemented for vcols */ -#define VCOL_CHECK_CONSTRAINT_IF_NOT_EXISTS 128 #define VCOL_NOT_STRICTLY_DETERMINISTIC \ (VCOL_NON_DETERMINISTIC | VCOL_TIME_FUNC | VCOL_SESSION_FUNC) @@ -590,6 +589,7 @@ public: bool stored_in_db; bool utf8; /* Already in utf8 */ bool automatic_name; + bool if_not_exists; Item *expr; Lex_ident name; /* Name of constraint */ /* see VCOL_* (VCOL_FIELD_REF, ...) */ diff --git a/sql/sql_lex.h b/sql/sql_lex.h index ecad3ea60ec..731ddbaefac 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -4375,7 +4375,7 @@ public: bool if_not_exists) { constr->name= name; - constr->flags= if_not_exists ? VCOL_CHECK_CONSTRAINT_IF_NOT_EXISTS : 0; + constr->if_not_exists= if_not_exists; alter_info.check_constraint_list.push_back(constr); return false; } diff --git a/sql/sql_table.cc b/sql/sql_table.cc index dbab4c0067c..24ce892fb12 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -6928,10 +6928,8 @@ remove_key: while ((check=it++)) { - if (!(check->flags & VCOL_CHECK_CONSTRAINT_IF_NOT_EXISTS) && - check->name.length) + if (!check->if_not_exists && check->name.length) continue; - check->flags= 0; for (c= share->field_check_constraints; c < share->table_check_constraints ; c++) { |