Debian Codegraph @

mysql-test/suite/funcs_1/r/is_check_constraints.result

raw
1 #
2 # MDEV-14474: Create INFORMATION_SCHEMA.CHECK_CONSTRAINTS
3 #
4 CREATE user boo1;
5 GRANT select,create,alter,drop on foo.* to boo1;
6 SHOW GRANTS for boo1;
7 Grants for boo1@%
8 GRANT USAGE ON *.* TO `boo1`@`%`
9 GRANT SELECT, CREATE, DROP, ALTER ON `foo`.* TO `boo1`@`%`
10 CREATE user boo2;
11 create database foo;
12 CONNECT con1,localhost, boo1,, foo;
13 SET check_constraint_checks=1;
14 CREATE TABLE t0
15 (
16 t int, check (t>32) # table constraint
17 ) ENGINE=myisam;
18 SELECT * from information_schema.check_constraints;
19 CONSTRAINT_CATALOG CONSTRAINT_SCHEMA TABLE_NAME CONSTRAINT_NAME LEVEL CHECK_CLAUSE
20 def foo t0 CONSTRAINT_1 Table `t` > 32
21 ALTER TABLE t0
22 ADD CONSTRAINT CHK_t0_t CHECK(t<100);
23 SELECT * from information_schema.check_constraints;
24 CONSTRAINT_CATALOG CONSTRAINT_SCHEMA TABLE_NAME CONSTRAINT_NAME LEVEL CHECK_CLAUSE
25 def foo t0 CHK_t0_t Table `t` < 100
26 def foo t0 CONSTRAINT_1 Table `t` > 32
27 ALTER TABLE t0
28 DROP CONSTRAINT CHK_t0_t;
29 SELECT * from information_schema.check_constraints;
30 CONSTRAINT_CATALOG CONSTRAINT_SCHEMA TABLE_NAME CONSTRAINT_NAME LEVEL CHECK_CLAUSE
31 def foo t0 CONSTRAINT_1 Table `t` > 32
32 ALTER TABLE t0
33 ADD CONSTRAINT CHECK(t<50);
34 SELECT * from information_schema.check_constraints;
35 CONSTRAINT_CATALOG CONSTRAINT_SCHEMA TABLE_NAME CONSTRAINT_NAME LEVEL CHECK_CLAUSE
36 def foo t0 CONSTRAINT_1 Table `t` > 32
37 def foo t0 CONSTRAINT_2 Table `t` < 50
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 SELECT * from information_schema.check_constraints;
45 CONSTRAINT_CATALOG CONSTRAINT_SCHEMA TABLE_NAME CONSTRAINT_NAME LEVEL CHECK_CLAUSE
46 def foo t0 CONSTRAINT_1 Table `t` > 32
47 def foo t0 CONSTRAINT_2 Table `t` < 50
48 def foo t1 CHK_tt Table `tt` < 100
49 def foo t1 CONSTRAINT_1 Table `tt` > 32
50 def foo t1 CONSTRAINT_2 Table `tt` < 50
51 def foo t1 t Column `t` > 2
52 ALTER TABLE t1
53 DROP CONSTRAINT CHK_tt;
54 SELECT * from information_schema.check_constraints;
55 CONSTRAINT_CATALOG CONSTRAINT_SCHEMA TABLE_NAME CONSTRAINT_NAME LEVEL CHECK_CLAUSE
56 def foo t0 CONSTRAINT_1 Table `t` > 32
57 def foo t0 CONSTRAINT_2 Table `t` < 50
58 def foo t1 CONSTRAINT_1 Table `tt` > 32
59 def foo t1 CONSTRAINT_2 Table `tt` < 50
60 def foo t1 t Column `t` > 2
61 CREATE TABLE t2
62 (
63 name VARCHAR(30) CHECK(CHAR_LENGTH(name)>2), #field constraint
64 start_date DATE,
65 end_date DATE,
66 CONSTRAINT CHK_dates CHECK(start_date IS NULL) #table constraint
67 )ENGINE=Innodb;
68 SELECT * from information_schema.check_constraints;
69 CONSTRAINT_CATALOG CONSTRAINT_SCHEMA TABLE_NAME CONSTRAINT_NAME LEVEL CHECK_CLAUSE
70 def foo t0 CONSTRAINT_1 Table `t` > 32
71 def foo t0 CONSTRAINT_2 Table `t` < 50
72 def foo t1 CONSTRAINT_1 Table `tt` > 32
73 def foo t1 CONSTRAINT_2 Table `tt` < 50
74 def foo t1 t Column `t` > 2
75 def foo t2 CHK_dates Table `start_date` is null
76 def foo t2 name Column char_length(`name`) > 2
77 ALTER TABLE t1
78 ADD CONSTRAINT CHK_new_ CHECK(t>tt);
79 SELECT * from information_schema.check_constraints;
80 CONSTRAINT_CATALOG CONSTRAINT_SCHEMA TABLE_NAME CONSTRAINT_NAME LEVEL CHECK_CLAUSE
81 def foo t0 CONSTRAINT_1 Table `t` > 32
82 def foo t0 CONSTRAINT_2 Table `t` < 50
83 def foo t1 CHK_new_ Table `t` > `tt`
84 def foo t1 CONSTRAINT_1 Table `tt` > 32
85 def foo t1 CONSTRAINT_2 Table `tt` < 50
86 def foo t1 t Column `t` > 2
87 def foo t2 CHK_dates Table `start_date` is null
88 def foo t2 name Column char_length(`name`) > 2
89 CREATE TABLE t3
90 (
91 a int,
92 b int check (b>0), # field constraint named 'b'
93 CONSTRAINT b check (b>10), # table constraint
94 # `CHECK_CLAUSE` should allow more then `var(64)` constraints
95 CONSTRAINT b1 check (b<123456789012345678901234567890123456789012345678901234567890123456789)
96 ) ENGINE=InnoDB;
97 SELECT * from information_schema.check_constraints;
98 CONSTRAINT_CATALOG CONSTRAINT_SCHEMA TABLE_NAME CONSTRAINT_NAME LEVEL CHECK_CLAUSE
99 def foo t0 CONSTRAINT_1 Table `t` > 32
100 def foo t0 CONSTRAINT_2 Table `t` < 50
101 def foo t1 CHK_new_ Table `t` > `tt`
102 def foo t1 CONSTRAINT_1 Table `tt` > 32
103 def foo t1 CONSTRAINT_2 Table `tt` < 50
104 def foo t1 t Column `t` > 2
105 def foo t2 CHK_dates Table `start_date` is null
106 def foo t2 name Column char_length(`name`) > 2
107 def foo t3 b Column `b` > 0
108 def foo t3 b Table `b` > 10
109 def foo t3 b1 Table `b` < 123456789012345678901234567890123456789012345678901234567890123456789
110 disconnect con1;
111 CONNECT con2, localhost, boo2,,"*NO-ONE*";
112 SELECT * from information_schema.check_constraints;
113 CONSTRAINT_CATALOG CONSTRAINT_SCHEMA TABLE_NAME CONSTRAINT_NAME LEVEL CHECK_CLAUSE
114 disconnect con2;
115 CONNECT con1, localhost, boo1,,foo;
116 DROP TABLE t0;
117 DROP TABLE t1;
118 DROP TABLE t2;
119 DROP TABLE t3;
120 DROP DATABASE foo;
121 disconnect con1;
122 connection default;
123 DROP USER boo1;
124 DROP USER boo2;
125 #
126 # MDEV-18440: Information_schema.check_constraints possible data leak
127 #
128 CREATE USER foo;
129 CREATE DATABASE db;
130 USE db;
131 CREATE TABLE t1 (a int, b int, CONSTRAINT CHECK (b > 0));
132 INSERT INTO t1 VALUES (1, 2), (2, 3);
133 GRANT SELECT (a) ON t1 TO foo;
134 SHOW GRANTS FOR foo;
135 Grants for foo@%
136 GRANT USAGE ON *.* TO `foo`@`%`
137 GRANT SELECT (`a`) ON `db`.`t1` TO `foo`@`%`
138 SELECT * FROM information_schema.check_constraints;
139 CONSTRAINT_CATALOG CONSTRAINT_SCHEMA TABLE_NAME CONSTRAINT_NAME LEVEL CHECK_CLAUSE
140 def db t1 CONSTRAINT_1 Table `b` > 0
141 def mysql global_priv Priv Column json_valid(`Priv`)
142 def mysql servers Options Column json_valid(`Options`)
143 CONNECT con1,localhost, foo,, db;
144 SELECT a FROM t1;
145 a
146 1
147 2
148 SELECT * FROM information_schema.check_constraints;
149 CONSTRAINT_CATALOG CONSTRAINT_SCHEMA TABLE_NAME CONSTRAINT_NAME LEVEL CHECK_CLAUSE
150 connection default;
151 DROP USER foo;
152 DROP DATABASE db;
153 #
154 # MDEV-24601: INFORMATION_SCHEMA doesn't differentiate between
155 # column and table-level CHECK constraints
156 #
157 use test;
158 create table t(check (t0>0),
159 t0 int,
160 t1 int check (t1<0),
161 t2 int check (t2<-1),
162 CONSTRAINT tc_1 check(t1 > 1),
163 CONSTRAINT t2 check(t2 > 1));
164 show create table t;
165 Table Create Table
166 t CREATE TABLE `t` (
167 `t0` int(11) DEFAULT NULL,
168 `t1` int(11) DEFAULT NULL CHECK (`t1` < 0),
169 `t2` int(11) DEFAULT NULL CHECK (`t2` < -1),
170 CONSTRAINT `CONSTRAINT_1` CHECK (`t0` > 0),
171 CONSTRAINT `tc_1` CHECK (`t1` > 1),
172 CONSTRAINT `t2` CHECK (`t2` > 1)
173 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
174 select * from information_schema.table_constraints where CONSTRAINT_TYPE='CHECK';
175 CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE
176 def mysql Options mysql servers CHECK
177 def mysql Priv mysql global_priv CHECK
178 def test CONSTRAINT_1 test t CHECK
179 def test t1 test t CHECK
180 def test t2 test t CHECK
181 def test t2 test t CHECK
182 def test tc_1 test t CHECK
183 select * from information_schema.check_constraints;
184 CONSTRAINT_CATALOG CONSTRAINT_SCHEMA TABLE_NAME CONSTRAINT_NAME LEVEL CHECK_CLAUSE
185 def mysql global_priv Priv Column json_valid(`Priv`)
186 def mysql servers Options Column json_valid(`Options`)
187 def test t CONSTRAINT_1 Table `t0` > 0
188 def test t t1 Column `t1` < 0
189 def test t t2 Column `t2` < -1
190 def test t t2 Table `t2` > 1
191 def test t tc_1 Table `t1` > 1
192 drop table t;