#
# 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	LEVEL	CHECK_CLAUSE
def	foo	t0	CONSTRAINT_1	Table	`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	LEVEL	CHECK_CLAUSE
def	foo	t0	CHK_t0_t	Table	`t` < 100
def	foo	t0	CONSTRAINT_1	Table	`t` > 32
ALTER TABLE t0
DROP CONSTRAINT CHK_t0_t;
SELECT * from information_schema.check_constraints;
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	TABLE_NAME	CONSTRAINT_NAME	LEVEL	CHECK_CLAUSE
def	foo	t0	CONSTRAINT_1	Table	`t` > 32
ALTER TABLE t0
ADD CONSTRAINT CHECK(t<50);
SELECT * from information_schema.check_constraints;
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	TABLE_NAME	CONSTRAINT_NAME	LEVEL	CHECK_CLAUSE
def	foo	t0	CONSTRAINT_1	Table	`t` > 32
def	foo	t0	CONSTRAINT_2	Table	`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	LEVEL	CHECK_CLAUSE
def	foo	t0	CONSTRAINT_1	Table	`t` > 32
def	foo	t0	CONSTRAINT_2	Table	`t` < 50
def	foo	t1	CHK_tt	Table	`tt` < 100
def	foo	t1	CONSTRAINT_1	Table	`tt` > 32
def	foo	t1	CONSTRAINT_2	Table	`tt` < 50
def	foo	t1	t	Column	`t` > 2
ALTER TABLE t1
DROP CONSTRAINT CHK_tt;
SELECT * from information_schema.check_constraints;
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	TABLE_NAME	CONSTRAINT_NAME	LEVEL	CHECK_CLAUSE
def	foo	t0	CONSTRAINT_1	Table	`t` > 32
def	foo	t0	CONSTRAINT_2	Table	`t` < 50
def	foo	t1	CONSTRAINT_1	Table	`tt` > 32
def	foo	t1	CONSTRAINT_2	Table	`tt` < 50
def	foo	t1	t	Column	`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	LEVEL	CHECK_CLAUSE
def	foo	t0	CONSTRAINT_1	Table	`t` > 32
def	foo	t0	CONSTRAINT_2	Table	`t` < 50
def	foo	t1	CONSTRAINT_1	Table	`tt` > 32
def	foo	t1	CONSTRAINT_2	Table	`tt` < 50
def	foo	t1	t	Column	`t` > 2
def	foo	t2	CHK_dates	Table	`start_date` is null
def	foo	t2	name	Column	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	LEVEL	CHECK_CLAUSE
def	foo	t0	CONSTRAINT_1	Table	`t` > 32
def	foo	t0	CONSTRAINT_2	Table	`t` < 50
def	foo	t1	CHK_new_	Table	`t` > `tt`
def	foo	t1	CONSTRAINT_1	Table	`tt` > 32
def	foo	t1	CONSTRAINT_2	Table	`tt` < 50
def	foo	t1	t	Column	`t` > 2
def	foo	t2	CHK_dates	Table	`start_date` is null
def	foo	t2	name	Column	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
# `CHECK_CLAUSE` should allow more then `var(64)` constraints
CONSTRAINT b1 check (b<123456789012345678901234567890123456789012345678901234567890123456789)
) ENGINE=InnoDB;
SELECT * from information_schema.check_constraints;
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	TABLE_NAME	CONSTRAINT_NAME	LEVEL	CHECK_CLAUSE
def	foo	t0	CONSTRAINT_1	Table	`t` > 32
def	foo	t0	CONSTRAINT_2	Table	`t` < 50
def	foo	t1	CHK_new_	Table	`t` > `tt`
def	foo	t1	CONSTRAINT_1	Table	`tt` > 32
def	foo	t1	CONSTRAINT_2	Table	`tt` < 50
def	foo	t1	t	Column	`t` > 2
def	foo	t2	CHK_dates	Table	`start_date` is null
def	foo	t2	name	Column	char_length(`name`) > 2
def	foo	t3	b	Column	`b` > 0
def	foo	t3	b	Table	`b` > 10
def	foo	t3	b1	Table	`b` < 123456789012345678901234567890123456789012345678901234567890123456789
disconnect con1;
CONNECT con2, localhost, boo2,, test;
SELECT * from information_schema.check_constraints;
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	TABLE_NAME	CONSTRAINT_NAME	LEVEL	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	LEVEL	CHECK_CLAUSE
def	db	t1	CONSTRAINT_1	Table	`b` > 0
def	mysql	global_priv	Priv	Column	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	LEVEL	CHECK_CLAUSE
connection default;
DROP USER foo;
DROP DATABASE db;
#
# MDEV-24601: INFORMATION_SCHEMA doesn't differentiate between
#             column and table-level CHECK constraints
#
use test;
create table t(check (t0>0),
t0 int,
t1 int check (t1<0),
t2 int check (t2<-1),
CONSTRAINT tc_1 check(t1 > 1),
CONSTRAINT t2 check(t2 > 1));
show create table t;
Table	Create Table
t	CREATE TABLE `t` (
  `t0` int(11) DEFAULT NULL,
  `t1` int(11) DEFAULT NULL CHECK (`t1` < 0),
  `t2` int(11) DEFAULT NULL CHECK (`t2` < -1),
  CONSTRAINT `CONSTRAINT_1` CHECK (`t0` > 0),
  CONSTRAINT `tc_1` CHECK (`t1` > 1),
  CONSTRAINT `t2` CHECK (`t2` > 1)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select * from information_schema.table_constraints where CONSTRAINT_TYPE='CHECK';
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_SCHEMA	TABLE_NAME	CONSTRAINT_TYPE
def	mysql	Priv	mysql	global_priv	CHECK
def	test	CONSTRAINT_1	test	t	CHECK
def	test	t1	test	t	CHECK
def	test	t2	test	t	CHECK
def	test	t2	test	t	CHECK
def	test	tc_1	test	t	CHECK
select * from information_schema.check_constraints;
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	TABLE_NAME	CONSTRAINT_NAME	LEVEL	CHECK_CLAUSE
def	mysql	global_priv	Priv	Column	json_valid(`Priv`)
def	test	t	CONSTRAINT_1	Table	`t0` > 0
def	test	t	t1	Column	`t1` < 0
def	test	t	t2	Column	`t2` < -1
def	test	t	t2	Table	`t2` > 1
def	test	t	tc_1	Table	`t1` > 1
drop table t;