Debian Codegraph @

mysql-test/suite/funcs_1/t/is_check_constraints.test

raw
1 --source include/have_innodb.inc
2 --source include/not_embedded.inc
3 --echo #
4 --echo # MDEV-14474: Create INFORMATION_SCHEMA.CHECK_CONSTRAINTS
5 --echo #
6
7 CREATE user boo1;
8 GRANT select,create,alter,drop on foo.* to boo1;
9 SHOW GRANTS for boo1;
10 CREATE user boo2;
11 create database foo;
12 # Connect with user boo1
13 CONNECT(con1,localhost, boo1,, foo);
14
15 SET check_constraint_checks=1;
16 CREATE TABLE t0
17 (
18 t int, check (t>32) # table constraint
19 ) ENGINE=myisam;
20 --sorted_result
21 SELECT * from information_schema.check_constraints;
22
23 ALTER TABLE t0
24 ADD CONSTRAINT CHK_t0_t CHECK(t<100);
25 --sorted_result
26 SELECT * from information_schema.check_constraints;
27
28 ALTER TABLE t0
29 DROP CONSTRAINT CHK_t0_t;
30 --sorted_result
31 SELECT * from information_schema.check_constraints;
32
33 ALTER TABLE t0
34 ADD CONSTRAINT CHECK(t<50);
35 --sorted_result
36 SELECT * from information_schema.check_constraints;
37
38 CREATE TABLE t1
39 ( t int CHECK(t>2), # field constraint
40 tt int,
41 CONSTRAINT CHECK (tt > 32), CONSTRAINT CHECK (tt <50),# autogenerated names table constraints
42 CONSTRAINT CHK_tt CHECK(tt<100) # named table constraint
43 ) ENGINE=InnoDB;
44 --sorted_result
45 SELECT * from information_schema.check_constraints;
46
47 ALTER TABLE t1
48 DROP CONSTRAINT CHK_tt;
49 --sorted_result
50 SELECT * from information_schema.check_constraints;
51
52 CREATE TABLE t2
53 (
54 name VARCHAR(30) CHECK(CHAR_LENGTH(name)>2), #field constraint
55 start_date DATE,
56 end_date DATE,
57 CONSTRAINT CHK_dates CHECK(start_date IS NULL) #table constraint
58 )ENGINE=Innodb;
59 --sorted_result
60 SELECT * from information_schema.check_constraints;
61
62 ALTER TABLE t1
63 ADD CONSTRAINT CHK_new_ CHECK(t>tt);
64 --sorted_result
65 SELECT * from information_schema.check_constraints;
66
67 # Create table with same field and table check constraint name
68 CREATE TABLE t3
69 (
70 a int,
71 b int check (b>0), # field constraint named 'b'
72 CONSTRAINT b check (b>10), # table constraint
73 # `CHECK_CLAUSE` should allow more then `var(64)` constraints
74 CONSTRAINT b1 check (b<123456789012345678901234567890123456789012345678901234567890123456789)
75 ) ENGINE=InnoDB;
76 --sorted_result
77 SELECT * from information_schema.check_constraints;
78
79 DISCONNECT con1;
80 CONNECT(con2, localhost, boo2,,"*NO-ONE*");
81 --sorted_result
82 SELECT * from information_schema.check_constraints;
83
84 DISCONNECT con2;
85 CONNECT(con1, localhost, boo1,,foo);
86 DROP TABLE t0;
87 DROP TABLE t1;
88 DROP TABLE t2;
89 DROP TABLE t3;
90 DROP DATABASE foo;
91
92 DISCONNECT con1;
93 --CONNECTION default
94 DROP USER boo1;
95 DROP USER boo2;
96
97 --echo #
98 --echo # MDEV-18440: Information_schema.check_constraints possible data leak
99 --echo #
100
101 CREATE USER foo;
102 CREATE DATABASE db;
103 USE db;
104 CREATE TABLE t1 (a int, b int, CONSTRAINT CHECK (b > 0));
105 INSERT INTO t1 VALUES (1, 2), (2, 3);
106 GRANT SELECT (a) ON t1 TO foo;
107
108 SHOW GRANTS FOR foo;
109 --sorted_result
110 SELECT * FROM information_schema.check_constraints;
111
112 CONNECT(con1,localhost, foo,, db);
113 SELECT a FROM t1;
114 --sorted_result
115 SELECT * FROM information_schema.check_constraints;
116
117 --CONNECTION default
118
119 DROP USER foo;
120 DROP DATABASE db;
121
122 --echo #
123 --echo # MDEV-24601: INFORMATION_SCHEMA doesn't differentiate between
124 --echo # column and table-level CHECK constraints
125 --echo #
126
127 # Mix of table (>0) and field (<0) constraints
128 # Note that there are 2 constraints `t2` - this is not allowed MDEV-24601
129 use test;
130 create table t(check (t0>0),
131 t0 int,
132 t1 int check (t1<0),
133 t2 int check (t2<-1),
134 CONSTRAINT tc_1 check(t1 > 1),
135 CONSTRAINT t2 check(t2 > 1));
136 show create table t;
137 --sorted_result
138 select * from information_schema.table_constraints where CONSTRAINT_TYPE='CHECK';
139 --sorted_result
140 select * from information_schema.check_constraints;
141 drop table t;