summaryrefslogtreecommitdiff
path: root/mysql-test/suite/funcs_1/r/is_check_constraints.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/suite/funcs_1/r/is_check_constraints.result')
-rw-r--r--mysql-test/suite/funcs_1/r/is_check_constraints.result152
1 files changed, 95 insertions, 57 deletions
diff --git a/mysql-test/suite/funcs_1/r/is_check_constraints.result b/mysql-test/suite/funcs_1/r/is_check_constraints.result
index b7b70635de9..3a8fbac9170 100644
--- a/mysql-test/suite/funcs_1/r/is_check_constraints.result
+++ b/mysql-test/suite/funcs_1/r/is_check_constraints.result
@@ -16,25 +16,25 @@ 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
+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 CHECK_CLAUSE
-def foo t0 CHK_t0_t `t` < 100
-def foo t0 CONSTRAINT_1 `t` > 32
+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 CHECK_CLAUSE
-def foo t0 CONSTRAINT_1 `t` > 32
+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 CHECK_CLAUSE
-def foo t0 CONSTRAINT_1 `t` > 32
-def foo t0 CONSTRAINT_2 `t` < 50
+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,
@@ -42,22 +42,22 @@ CONSTRAINT CHECK (tt > 32), CONSTRAINT CHECK (tt <50),# autogenerated names tabl
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
+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 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
+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
@@ -66,26 +66,26 @@ 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
+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 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
+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,
@@ -95,22 +95,22 @@ CONSTRAINT b check (b>10), # table constraint
CONSTRAINT b1 check (b<123456789012345678901234567890123456789012345678901234567890123456789)
) 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
-def foo t3 b1 `b` < 123456789012345678901234567890123456789012345678901234567890123456789
+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 CHECK_CLAUSE
+CONSTRAINT_CATALOG CONSTRAINT_SCHEMA TABLE_NAME CONSTRAINT_NAME LEVEL CHECK_CLAUSE
disconnect con2;
CONNECT con1, localhost, boo1,,foo;
DROP TABLE t0;
@@ -136,16 +136,54 @@ 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`)
+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 CHECK_CLAUSE
+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 COLLATE=latin1_swedish_ci
+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;
View Lorry Controller Status