summaryrefslogtreecommitdiff
path: root/mysql-test/suite/funcs_1/r/is_check_constraints.result
blob: 307c14792a9e7ea4eede8df414603574293260d9 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
#
# MDEV-14474: Create INFORMATION_SCHEMA.CHECK_CONSTRAINTS
#
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	TABLE_NAME	CONSTRAINT_NAME	CHECK_CLAUSE
def	foo	t0	CONSTRAINT_1	`t` > 32
ALTER TABLE t0
ADD CONSTRAINT CHK_t0_t CHECK(t<100);
SELECT * from information_schema.check_constraints;
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	TABLE_NAME	CONSTRAINT_NAME	CHECK_CLAUSE
def	foo	t0	CHK_t0_t	`t` < 100
def	foo	t0	CONSTRAINT_1	`t` > 32
ALTER TABLE t0
DROP CONSTRAINT CHK_t0_t;
SELECT * from information_schema.check_constraints;
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	TABLE_NAME	CONSTRAINT_NAME	CHECK_CLAUSE
def	foo	t0	CONSTRAINT_1	`t` > 32
ALTER TABLE t0
ADD CONSTRAINT CHECK(t<50);
SELECT * from information_schema.check_constraints;
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	TABLE_NAME	CONSTRAINT_NAME	CHECK_CLAUSE
def	foo	t0	CONSTRAINT_1	`t` > 32
def	foo	t0	CONSTRAINT_2	`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	TABLE_NAME	CONSTRAINT_NAME	CHECK_CLAUSE
def	foo	t0	CONSTRAINT_1	`t` > 32
def	foo	t0	CONSTRAINT_2	`t` < 50
def	foo	t1	CHK_tt	`tt` < 100
def	foo	t1	CONSTRAINT_1	`tt` > 32
def	foo	t1	CONSTRAINT_2	`tt` < 50
def	foo	t1	t	`t` > 2
ALTER TABLE t1
DROP CONSTRAINT CHK_tt;
SELECT * from information_schema.check_constraints;
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	TABLE_NAME	CONSTRAINT_NAME	CHECK_CLAUSE
def	foo	t0	CONSTRAINT_1	`t` > 32
def	foo	t0	CONSTRAINT_2	`t` < 50
def	foo	t1	CONSTRAINT_1	`tt` > 32
def	foo	t1	CONSTRAINT_2	`tt` < 50
def	foo	t1	t	`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	TABLE_NAME	CONSTRAINT_NAME	CHECK_CLAUSE
def	foo	t0	CONSTRAINT_1	`t` > 32
def	foo	t0	CONSTRAINT_2	`t` < 50
def	foo	t1	CONSTRAINT_1	`tt` > 32
def	foo	t1	CONSTRAINT_2	`tt` < 50
def	foo	t1	t	`t` > 2
def	foo	t2	CHK_dates	`start_date` is null
def	foo	t2	name	char_length(`name`) > 2
ALTER TABLE t1
ADD CONSTRAINT CHK_new_ CHECK(t>tt);
SELECT * from information_schema.check_constraints;
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	TABLE_NAME	CONSTRAINT_NAME	CHECK_CLAUSE
def	foo	t0	CONSTRAINT_1	`t` > 32
def	foo	t0	CONSTRAINT_2	`t` < 50
def	foo	t1	CHK_new_	`t` > `tt`
def	foo	t1	CONSTRAINT_1	`tt` > 32
def	foo	t1	CONSTRAINT_2	`tt` < 50
def	foo	t1	t	`t` > 2
def	foo	t2	CHK_dates	`start_date` is null
def	foo	t2	name	char_length(`name`) > 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	TABLE_NAME	CONSTRAINT_NAME	CHECK_CLAUSE
def	foo	t0	CONSTRAINT_1	`t` > 32
def	foo	t0	CONSTRAINT_2	`t` < 50
def	foo	t1	CHK_new_	`t` > `tt`
def	foo	t1	CONSTRAINT_1	`tt` > 32
def	foo	t1	CONSTRAINT_2	`tt` < 50
def	foo	t1	t	`t` > 2
def	foo	t2	CHK_dates	`start_date` is null
def	foo	t2	name	char_length(`name`) > 2
def	foo	t3	b	`b` > 0
def	foo	t3	b	`b` > 10
disconnect con1;
CONNECT con2, localhost, boo2,, test;
SELECT * from information_schema.check_constraints;
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	TABLE_NAME	CONSTRAINT_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;
#
# MDEV-18440: Information_schema.check_constraints possible data leak
#
CREATE USER foo;
CREATE DATABASE db;
USE db;
CREATE TABLE t1 (a int, b int, CONSTRAINT CHECK (b > 0));
INSERT INTO t1 VALUES (1, 2), (2, 3);
GRANT SELECT (a) ON t1 TO foo;
SHOW GRANTS FOR foo;
Grants for foo@%
GRANT USAGE ON *.* TO 'foo'@'%'
GRANT SELECT (a) ON `db`.`t1` TO 'foo'@'%'
SELECT * FROM information_schema.check_constraints;
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	TABLE_NAME	CONSTRAINT_NAME	CHECK_CLAUSE
def	db	t1	CONSTRAINT_1	`b` > 0
def	mysql	global_priv	Priv	json_valid(`Priv`)
CONNECT con1,localhost, foo,, db;
SELECT a FROM t1;
a
1
2
SELECT * FROM information_schema.check_constraints;
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	TABLE_NAME	CONSTRAINT_NAME	CHECK_CLAUSE
connection default;
DROP USER foo;
DROP DATABASE db;