summaryrefslogtreecommitdiff
path: root/mysql-test/suite/funcs_1
diff options
context:
space:
mode:
authorSergei Golubchik <serg@mariadb.org>2019-01-25 20:33:47 +0100
committerSergei Golubchik <serg@mariadb.org>2019-01-25 20:35:40 +0100
commit3b1b665fcbe1b9ae7ee0d627105cfb69c9886179 (patch)
tree94479c6cb014effd97d7c95a20c0338651f363b1 /mysql-test/suite/funcs_1
parenta4ab66c8f86d79a1b6bf8aadbe0282e2c71c55a8 (diff)
parent3fb6d2587d6498c50c82aac77062d95ad5f2237a (diff)
downloadmariadb-git-3b1b665fcbe1b9ae7ee0d627105cfb69c9886179.tar.gz
Merge branch '10.2' into 10.3
Diffstat (limited to 'mysql-test/suite/funcs_1')
-rw-r--r--mysql-test/suite/funcs_1/r/is_check_constraint.result121
-rw-r--r--mysql-test/suite/funcs_1/t/is_check_constraint.test92
2 files changed, 213 insertions, 0 deletions
diff --git a/mysql-test/suite/funcs_1/r/is_check_constraint.result b/mysql-test/suite/funcs_1/r/is_check_constraint.result
new file mode 100644
index 00000000000..be44a8867e8
--- /dev/null
+++ b/mysql-test/suite/funcs_1/r/is_check_constraint.result
@@ -0,0 +1,121 @@
+#
+# MDEV-17323: Backport INFORMATION_SCHEMA.CHECK_CONSTRAINTS to 10.2
+#
+CREATE user boo1;
+GRANT select,create,alter,drop on foo.* to boo1;
+SHOW GRANTS for boo1;
+Grants for boo1@%
+GRANT USAGE ON *.* TO 'boo1'@'%'
+GRANT SELECT, CREATE, DROP, ALTER ON `foo`.* TO 'boo1'@'%'
+CREATE user boo2;
+create database foo;
+CONNECT con1,localhost, boo1,, foo;
+SET check_constraint_checks=1;
+CREATE TABLE t0
+(
+t int, check (t>32) # table constraint
+) ENGINE=myisam;
+SELECT * from information_schema.check_constraints;
+CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_NAME CHECK_CLAUSE
+def foo CONSTRAINT_1 t0 `t` > 32
+ALTER TABLE t0
+ADD CONSTRAINT CHK_t0_t CHECK(t<100);
+SELECT * from information_schema.check_constraints;
+CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_NAME CHECK_CLAUSE
+def foo CHK_t0_t t0 `t` < 100
+def foo CONSTRAINT_1 t0 `t` > 32
+ALTER TABLE t0
+DROP CONSTRAINT CHK_t0_t;
+SELECT * from information_schema.check_constraints;
+CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_NAME CHECK_CLAUSE
+def foo CONSTRAINT_1 t0 `t` > 32
+ALTER TABLE t0
+ADD CONSTRAINT CHECK(t<50);
+SELECT * from information_schema.check_constraints;
+CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_NAME CHECK_CLAUSE
+def foo CONSTRAINT_1 t0 `t` > 32
+def foo CONSTRAINT_2 t0 `t` < 50
+CREATE TABLE t1
+( t int CHECK(t>2), # field constraint
+tt int,
+CONSTRAINT CHECK (tt > 32), CONSTRAINT CHECK (tt <50),# autogenerated names table constraints
+CONSTRAINT CHK_tt CHECK(tt<100) # named table constraint
+) ENGINE=InnoDB;
+SELECT * from information_schema.check_constraints;
+CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_NAME CHECK_CLAUSE
+def foo CHK_tt t1 `tt` < 100
+def foo CONSTRAINT_1 t0 `t` > 32
+def foo CONSTRAINT_1 t1 `tt` > 32
+def foo CONSTRAINT_2 t0 `t` < 50
+def foo CONSTRAINT_2 t1 `tt` < 50
+def foo t t1 `t` > 2
+ALTER TABLE t1
+DROP CONSTRAINT CHK_tt;
+SELECT * from information_schema.check_constraints;
+CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_NAME CHECK_CLAUSE
+def foo CONSTRAINT_1 t0 `t` > 32
+def foo CONSTRAINT_1 t1 `tt` > 32
+def foo CONSTRAINT_2 t0 `t` < 50
+def foo CONSTRAINT_2 t1 `tt` < 50
+def foo t t1 `t` > 2
+CREATE TABLE t2
+(
+name VARCHAR(30) CHECK(CHAR_LENGTH(name)>2), #field constraint
+start_date DATE,
+end_date DATE,
+CONSTRAINT CHK_dates CHECK(start_date IS NULL) #table constraint
+)ENGINE=Innodb;
+SELECT * from information_schema.check_constraints;
+CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_NAME CHECK_CLAUSE
+def foo CHK_dates t2 `start_date` is null
+def foo CONSTRAINT_1 t0 `t` > 32
+def foo CONSTRAINT_1 t1 `tt` > 32
+def foo CONSTRAINT_2 t0 `t` < 50
+def foo CONSTRAINT_2 t1 `tt` < 50
+def foo name t2 char_length(`name`) > 2
+def foo t t1 `t` > 2
+ALTER TABLE t1
+ADD CONSTRAINT CHK_new_ CHECK(t>tt);
+SELECT * from information_schema.check_constraints;
+CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_NAME CHECK_CLAUSE
+def foo CHK_dates t2 `start_date` is null
+def foo CHK_new_ t1 `t` > `tt`
+def foo CONSTRAINT_1 t0 `t` > 32
+def foo CONSTRAINT_1 t1 `tt` > 32
+def foo CONSTRAINT_2 t0 `t` < 50
+def foo CONSTRAINT_2 t1 `tt` < 50
+def foo name t2 char_length(`name`) > 2
+def foo t t1 `t` > 2
+CREATE TABLE t3
+(
+a int,
+b int check (b>0), # field constraint named 'b'
+CONSTRAINT b check (b>10) # table constraint
+) ENGINE=InnoDB;
+SELECT * from information_schema.check_constraints;
+CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_NAME CHECK_CLAUSE
+def foo CHK_dates t2 `start_date` is null
+def foo CHK_new_ t1 `t` > `tt`
+def foo CONSTRAINT_1 t0 `t` > 32
+def foo CONSTRAINT_1 t1 `tt` > 32
+def foo CONSTRAINT_2 t0 `t` < 50
+def foo CONSTRAINT_2 t1 `tt` < 50
+def foo b t3 `b` > 0
+def foo b t3 `b` > 10
+def foo name t2 char_length(`name`) > 2
+def foo t t1 `t` > 2
+disconnect con1;
+CONNECT con2, localhost, boo2,, test;
+SELECT * from information_schema.check_constraints;
+CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_NAME CHECK_CLAUSE
+disconnect con2;
+CONNECT con1, localhost, boo1,,foo;
+DROP TABLE t0;
+DROP TABLE t1;
+DROP TABLE t2;
+DROP TABLE t3;
+DROP DATABASE foo;
+disconnect con1;
+connection default;
+DROP USER boo1;
+DROP USER boo2;
diff --git a/mysql-test/suite/funcs_1/t/is_check_constraint.test b/mysql-test/suite/funcs_1/t/is_check_constraint.test
new file mode 100644
index 00000000000..30a72d02b34
--- /dev/null
+++ b/mysql-test/suite/funcs_1/t/is_check_constraint.test
@@ -0,0 +1,92 @@
+--source include/have_innodb.inc
+--source include/not_embedded.inc
+--echo #
+--echo # MDEV-17323: Backport INFORMATION_SCHEMA.CHECK_CONSTRAINTS to 10.2
+--echo #
+CREATE user boo1;
+GRANT select,create,alter,drop on foo.* to boo1;
+SHOW GRANTS for boo1;
+CREATE user boo2;
+create database foo;
+# Connect with user boo1
+CONNECT(con1,localhost, boo1,, foo);
+
+SET check_constraint_checks=1;
+CREATE TABLE t0
+(
+ t int, check (t>32) # table constraint
+) ENGINE=myisam;
+--sorted_result
+SELECT * from information_schema.check_constraints;
+
+ALTER TABLE t0
+ADD CONSTRAINT CHK_t0_t CHECK(t<100);
+--sorted_result
+SELECT * from information_schema.check_constraints;
+
+ALTER TABLE t0
+DROP CONSTRAINT CHK_t0_t;
+--sorted_result
+SELECT * from information_schema.check_constraints;
+
+ALTER TABLE t0
+ADD CONSTRAINT CHECK(t<50);
+--sorted_result
+SELECT * from information_schema.check_constraints;
+
+CREATE TABLE t1
+( t int CHECK(t>2), # field constraint
+ tt int,
+ CONSTRAINT CHECK (tt > 32), CONSTRAINT CHECK (tt <50),# autogenerated names table constraints
+ CONSTRAINT CHK_tt CHECK(tt<100) # named table constraint
+) ENGINE=InnoDB;
+ --sorted_result
+SELECT * from information_schema.check_constraints;
+
+ALTER TABLE t1
+DROP CONSTRAINT CHK_tt;
+--sorted_result
+SELECT * from information_schema.check_constraints;
+
+CREATE TABLE t2
+(
+name VARCHAR(30) CHECK(CHAR_LENGTH(name)>2), #field constraint
+start_date DATE,
+end_date DATE,
+CONSTRAINT CHK_dates CHECK(start_date IS NULL) #table constraint
+)ENGINE=Innodb;
+ --sorted_result
+SELECT * from information_schema.check_constraints;
+
+ALTER TABLE t1
+ADD CONSTRAINT CHK_new_ CHECK(t>tt);
+--sorted_result
+SELECT * from information_schema.check_constraints;
+
+# Create table with same field and table check constraint name
+CREATE TABLE t3
+(
+a int,
+b int check (b>0), # field constraint named 'b'
+CONSTRAINT b check (b>10) # table constraint
+) ENGINE=InnoDB;
+ --sorted_result
+SELECT * from information_schema.check_constraints;
+
+DISCONNECT con1;
+CONNECT(con2, localhost, boo2,, test);
+ --sorted_result
+SELECT * from information_schema.check_constraints;
+
+DISCONNECT con2;
+CONNECT(con1, localhost, boo1,,foo);
+DROP TABLE t0;
+DROP TABLE t1;
+DROP TABLE t2;
+DROP TABLE t3;
+DROP DATABASE foo;
+
+DISCONNECT con1;
+--CONNECTION default
+DROP USER boo1;
+DROP USER boo2;