MDEV-24598: duplicate CHECK constraint namebb-10.5-anel-MDEV-24602-check_constraints

- Problematic test case:
```
innodb.foreign_key
```

12 files changed, 150 insertions, 11 deletions

diff --git a/mysql-test/main/anel.result b/mysql-test/main/anel.result
new file mode 100644
index 00000000000..308d2e0be3a
--- /dev/null
+++ b/mysql-test/main/anel.result
@@ -0,0 +1,61 @@
+#
+# MDEV-24602: cannot specify a name for a column check constraint
+#
+create table t(check (t>0),
+t int, constraint check(t>1), constraint t_named check(t>2));
+show create table t;
+Table	Create Table
+t	CREATE TABLE `t` (
+  `t` int(11) DEFAULT NULL,
+  CONSTRAINT `CONSTRAINT_1` CHECK (`t` > 0),
+  CONSTRAINT `CONSTRAINT_2` CHECK (`t` > 1),
+  CONSTRAINT `t_named` CHECK (`t` > 2)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table t;
+create table t(t0 int,
+t1 int check (t1>0),
+t2 int constraint check(t2>0),
+t3 int constraint t_field check(t3>0));
+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` > 0),
+  `t3` int(11) DEFAULT NULL CHECK (`t3` > 0),
+  CONSTRAINT `t1` CHECK (`t1` > 0),
+  CONSTRAINT `t2` CHECK (`t2` > 0),
+  CONSTRAINT `t_field` CHECK (`t3` > 0)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table t;
+create table t(t0 int,
+t1 int check (t1>0),
+t2 int constraint check(t2>0),
+t3 int constraint t_field check(t3>0),
+t4 int constraint t_field check(t4>0));
+ERROR HY000: Duplicate CHECK constraint name 't_field'
+#
+# MDEV-24598: duplicate CHECK constraint name
+#
+create table t(constraint t check(t>0), t int check(t<0));
+ERROR HY000: Duplicate CHECK constraint name 't'
+create table t(check(t>0), t int constraint CONSTRAINT_1 check(t<0));
+show create table t;
+Table	Create Table
+t	CREATE TABLE `t` (
+  `t` int(11) DEFAULT NULL CHECK (`t` < 0),
+  CONSTRAINT `CONSTRAINT_2` CHECK (`t` > 0),
+  CONSTRAINT `CONSTRAINT_1` CHECK (`t` < 0)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table t;
+create table t(check(t>0), t int constraint CONSTRAINT_2 check(t<0));
+show create table t;
+Table	Create Table
+t	CREATE TABLE `t` (
+  `t` int(11) DEFAULT NULL CHECK (`t` < 0),
+  CONSTRAINT `CONSTRAINT_1` CHECK (`t` > 0),
+  CONSTRAINT `CONSTRAINT_2` CHECK (`t` < 0)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table t;
+create table t (a int check (a>2), constraint a check (a < 5));
+ERROR HY000: Duplicate CHECK constraint name 'a'
diff --git a/mysql-test/main/anel.test b/mysql-test/main/anel.test
new file mode 100644
index 00000000000..b1627888687
--- /dev/null
+++ b/mysql-test/main/anel.test
@@ -0,0 +1,42 @@
+--echo #
+--echo # MDEV-24602: cannot specify a name for a column check constraint
+--echo #
+
+# Table constraints (unnamed and named)
+create table t(check (t>0),
+               t int, constraint check(t>1), constraint t_named check(t>2));
+show create table t;
+drop table t;
+# Field constraints (with and without the names)
+create table t(t0 int,
+               t1 int check (t1>0),
+               t2 int constraint check(t2>0),
+               t3 int constraint t_field check(t3>0));
+show create table t;
+drop table t;
+--error ER_DUP_CONSTRAINT_NAME
+create table t(t0 int,
+               t1 int check (t1>0),
+               t2 int constraint check(t2>0),
+               t3 int constraint t_field check(t3>0),
+               t4 int constraint t_field check(t4>0));
+
+--echo #
+--echo # MDEV-24598: duplicate CHECK constraint name
+--echo #
+
+# Check table/field constraints with the same names
+--error ER_DUP_CONSTRAINT_NAME
+create table t(constraint t check(t>0), t int check(t<0));
+# We know that table check constraints is called `CONSTRAINT_1`, let's add the 
+# same for the field constraint.
+create table t(check(t>0), t int constraint CONSTRAINT_1 check(t<0));
+show create table t;
+drop table t;
+# Now vice versa
+create table t(check(t>0), t int constraint CONSTRAINT_2 check(t<0));
+show create table t;
+drop table t;
+
+--error ER_DUP_CONSTRAINT_NAME
+create table t (a int check (a>2), constraint a check (a < 5));
diff --git a/mysql-test/main/create_select_tmp.result b/mysql-test/main/create_select_tmp.result
index 2842ab26c42..296eeece7f9 100644
--- a/mysql-test/main/create_select_tmp.result
+++ b/mysql-test/main/create_select_tmp.result
@@ -35,7 +35,8 @@ t1	CREATE TABLE `t1` (
   `f2` int(11) DEFAULT NULL COMMENT 'a comment',
   `f3` int(11) DEFAULT NULL `foo`='bar',
   `f4` int(11) DEFAULT NULL CHECK (`f4` < 10),
-  `f5` int(11) DEFAULT NULL WITHOUT SYSTEM VERSIONING
+  `f5` int(11) DEFAULT NULL WITHOUT SYSTEM VERSIONING,
+  CONSTRAINT `f4` CHECK (`f4` < 10)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
 drop table t1;
 set sql_mode=default;
diff --git a/mysql-test/main/information_schema.result b/mysql-test/main/information_schema.result
index fcaa0a921c9..6d82bc0bb94 100644
--- a/mysql-test/main/information_schema.result
+++ b/mysql-test/main/information_schema.result
@@ -2258,6 +2258,9 @@ using (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, TABLE_NAME, CONSTRAINT_NAME)
 ;
 TABLE_SCHEMA	TABLE_NAME	CONSTRAINT_NAME	CHECK_CLAUSE
 mysql	global_priv	Priv	json_valid(`Priv`)
+mysql	global_priv	Priv	json_valid(`Priv`)
+mysql	global_priv	Priv	json_valid(`Priv`)
+mysql	global_priv	Priv	json_valid(`Priv`)
 test	t	a	`i` > 0
 select
 tc.TABLE_SCHEMA,
@@ -2270,6 +2273,9 @@ using (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, TABLE_NAME, CONSTRAINT_NAME)
 ;
 TABLE_SCHEMA	TABLE_NAME	CONSTRAINT_NAME	CHECK_CLAUSE
 mysql	global_priv	Priv	json_valid(`Priv`)
+mysql	global_priv	Priv	json_valid(`Priv`)
+mysql	global_priv	Priv	json_valid(`Priv`)
+mysql	global_priv	Priv	json_valid(`Priv`)
 test	t	a	`i` > 0
 select
 tc.TABLE_SCHEMA,
@@ -2281,6 +2287,9 @@ NATURAL join information_schema.CHECK_CONSTRAINTS cc
 ;
 TABLE_SCHEMA	TABLE_NAME	CONSTRAINT_NAME	CHECK_CLAUSE
 mysql	global_priv	Priv	json_valid(`Priv`)
+mysql	global_priv	Priv	json_valid(`Priv`)
+mysql	global_priv	Priv	json_valid(`Priv`)
+mysql	global_priv	Priv	json_valid(`Priv`)
 test	t	a	`i` > 0
 select
 tc.TABLE_SCHEMA,
@@ -2292,6 +2301,9 @@ NATURAL join information_schema.TABLE_CONSTRAINTS tc
 ;
 TABLE_SCHEMA	TABLE_NAME	CONSTRAINT_NAME	CHECK_CLAUSE
 mysql	global_priv	Priv	json_valid(`Priv`)
+mysql	global_priv	Priv	json_valid(`Priv`)
+mysql	global_priv	Priv	json_valid(`Priv`)
+mysql	global_priv	Priv	json_valid(`Priv`)
 test	t	a	`i` > 0
 select
 tc.TABLE_SCHEMA,
@@ -2306,6 +2318,9 @@ using (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, TABLE_NAME, CONSTRAINT_NAME)
 ;
 TABLE_SCHEMA	TABLE_NAME	CONSTRAINT_NAME	CHECK_CLAUSE	CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA
 mysql	global_priv	Priv	json_valid(`Priv`)	def	mysql
+mysql	global_priv	Priv	json_valid(`Priv`)	def	mysql
+mysql	global_priv	Priv	json_valid(`Priv`)	def	mysql
+mysql	global_priv	Priv	json_valid(`Priv`)	def	mysql
 test	t	a	`i` > 0	def	test
 drop table t;
 #
diff --git a/mysql-test/main/system_mysql_db_fix40123.result b/mysql-test/main/system_mysql_db_fix40123.result
index a876e71bdff..ce65314b9ab 100644
--- a/mysql-test/main/system_mysql_db_fix40123.result
+++ b/mysql-test/main/system_mysql_db_fix40123.result
@@ -116,7 +116,8 @@ global_priv	CREATE TABLE `global_priv` (
   `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
   `User` char(80) COLLATE utf8_bin NOT NULL DEFAULT '',
   `Priv` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '{}' CHECK (json_valid(`Priv`)),
-  PRIMARY KEY (`Host`,`User`)
+  PRIMARY KEY (`Host`,`User`),
+  CONSTRAINT `Priv` CHECK (json_valid(`Priv`))
 ) ENGINE=Aria DEFAULT CHARSET=utf8 COLLATE=utf8_bin PAGE_CHECKSUM=1 TRANSACTIONAL=1 COMMENT='Users and global privileges'
 show create table tables_priv;
 Table	Create Table
diff --git a/mysql-test/main/system_mysql_db_fix50030.result b/mysql-test/main/system_mysql_db_fix50030.result
index 9b21a54e86d..fc12817c624 100644
--- a/mysql-test/main/system_mysql_db_fix50030.result
+++ b/mysql-test/main/system_mysql_db_fix50030.result
@@ -120,7 +120,8 @@ global_priv	CREATE TABLE `global_priv` (
   `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
   `User` char(80) COLLATE utf8_bin NOT NULL DEFAULT '',
   `Priv` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '{}' CHECK (json_valid(`Priv`)),
-  PRIMARY KEY (`Host`,`User`)
+  PRIMARY KEY (`Host`,`User`),
+  CONSTRAINT `Priv` CHECK (json_valid(`Priv`))
 ) ENGINE=Aria DEFAULT CHARSET=utf8 COLLATE=utf8_bin PAGE_CHECKSUM=1 TRANSACTIONAL=1 COMMENT='Users and global privileges'
 show create table tables_priv;
 Table	Create Table
diff --git a/mysql-test/main/system_mysql_db_fix50117.result b/mysql-test/main/system_mysql_db_fix50117.result
index 07119cda6c6..1679670977a 100644
--- a/mysql-test/main/system_mysql_db_fix50117.result
+++ b/mysql-test/main/system_mysql_db_fix50117.result
@@ -100,7 +100,8 @@ global_priv	CREATE TABLE `global_priv` (
   `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
   `User` char(80) COLLATE utf8_bin NOT NULL DEFAULT '',
   `Priv` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '{}' CHECK (json_valid(`Priv`)),
-  PRIMARY KEY (`Host`,`User`)
+  PRIMARY KEY (`Host`,`User`),
+  CONSTRAINT `Priv` CHECK (json_valid(`Priv`))
 ) ENGINE=Aria DEFAULT CHARSET=utf8 COLLATE=utf8_bin PAGE_CHECKSUM=1 TRANSACTIONAL=1 COMMENT='Users and global privileges'
 show create table tables_priv;
 Table	Create Table
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 2436be8142f..fd051b0f6f7 100644
--- a/mysql-test/suite/funcs_1/r/is_check_constraints.result
+++ b/mysql-test/suite/funcs_1/r/is_check_constraints.result
@@ -49,6 +49,7 @@ 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
+def	foo	t1	t	`t` > 2
 ALTER TABLE t1
 DROP CONSTRAINT CHK_tt;
 SELECT * from information_schema.check_constraints;
@@ -58,6 +59,7 @@ 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	t1	t	`t` > 2
 CREATE TABLE t2
 (
 name VARCHAR(30) CHECK(CHAR_LENGTH(name)>2), #field constraint
@@ -72,8 +74,10 @@ 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	t1	t	`t` > 2
 def	foo	t2	CHK_dates	`start_date` is null
 def	foo	t2	name	char_length(`name`) > 2
+def	foo	t2	name	char_length(`name`) > 2
 ALTER TABLE t1
 ADD CONSTRAINT CHK_new_ CHECK(t>tt);
 SELECT * from information_schema.check_constraints;
@@ -84,13 +88,15 @@ 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	t1	t	`t` > 2
 def	foo	t2	CHK_dates	`start_date` is null
 def	foo	t2	name	char_length(`name`) > 2
+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
+CONSTRAINT bb check (b>10), # table constraint
 # `CHECK_CLAUSE` should allow more then `var(64)` constraints
 CONSTRAINT b1 check (b<123456789012345678901234567890123456789012345678901234567890123456789)
 ) ENGINE=InnoDB;
@@ -102,11 +108,14 @@ 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	t1	t	`t` > 2
 def	foo	t2	CHK_dates	`start_date` is null
 def	foo	t2	name	char_length(`name`) > 2
+def	foo	t2	name	char_length(`name`) > 2
+def	foo	t3	b	`b` > 0
 def	foo	t3	b	`b` > 0
-def	foo	t3	b	`b` > 10
 def	foo	t3	b1	`b` < 123456789012345678901234567890123456789012345678901234567890123456789
+def	foo	t3	bb	`b` > 10
 disconnect con1;
 CONNECT con2, localhost, boo2,, test;
 SELECT * from information_schema.check_constraints;
@@ -139,6 +148,7 @@ 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`)
+def	mysql	global_priv	Priv	json_valid(`Priv`)
 CONNECT con1,localhost, foo,, db;
 SELECT a FROM t1;
 a
diff --git a/mysql-test/suite/funcs_1/r/is_table_constraints.result b/mysql-test/suite/funcs_1/r/is_table_constraints.result
index e0b861c8589..632c725591b 100644
--- a/mysql-test/suite/funcs_1/r/is_table_constraints.result
+++ b/mysql-test/suite/funcs_1/r/is_table_constraints.result
@@ -65,6 +65,7 @@ def	mysql	PRIMARY	mysql	event
 def	mysql	PRIMARY	mysql	func
 def	mysql	PRIMARY	mysql	global_priv
 def	mysql	Priv	mysql	global_priv
+def	mysql	Priv	mysql	global_priv
 def	mysql	PRIMARY	mysql	gtid_slave_pos
 def	mysql	name	mysql	help_category
 def	mysql	PRIMARY	mysql	help_category
diff --git a/mysql-test/suite/funcs_1/r/is_table_constraints_mysql.result b/mysql-test/suite/funcs_1/r/is_table_constraints_mysql.result
index d5da807388b..c3b18725cb7 100644
--- a/mysql-test/suite/funcs_1/r/is_table_constraints_mysql.result
+++ b/mysql-test/suite/funcs_1/r/is_table_constraints_mysql.result
@@ -14,6 +14,7 @@ def	mysql	PRIMARY	mysql	event	PRIMARY KEY
 def	mysql	PRIMARY	mysql	func	PRIMARY KEY
 def	mysql	PRIMARY	mysql	global_priv	PRIMARY KEY
 def	mysql	Priv	mysql	global_priv	CHECK
+def	mysql	Priv	mysql	global_priv	CHECK
 def	mysql	PRIMARY	mysql	gtid_slave_pos	PRIMARY KEY
 def	mysql	name	mysql	help_category	UNIQUE
 def	mysql	PRIMARY	mysql	help_category	PRIMARY KEY
diff --git a/mysql-test/suite/funcs_1/t/is_check_constraints.test b/mysql-test/suite/funcs_1/t/is_check_constraints.test
index dbd286e6239..2b0a9ac3752 100644
--- a/mysql-test/suite/funcs_1/t/is_check_constraints.test
+++ b/mysql-test/suite/funcs_1/t/is_check_constraints.test
@@ -69,7 +69,7 @@ CREATE TABLE t3
 (
 a int,
 b int check (b>0), # field constraint named 'b'
-CONSTRAINT b check (b>10), # table constraint
+CONSTRAINT bb check (b>10), # table constraint
 # `CHECK_CLAUSE` should allow more then `var(64)` constraints
 CONSTRAINT b1 check (b<123456789012345678901234567890123456789012345678901234567890123456789)
 ) ENGINE=InnoDB;
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 1a045c5416f..d835d441cb8 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -333,7 +333,7 @@ static_assert(sizeof(YYSTYPE) == sizeof(void*)*2+8, "%union size check");
 bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize);
 %}
 
-%pure-parser                                    /* We have threads */
+%define api.pure                                    /* We have threads */
 %parse-param { THD *thd }
 %lex-param { THD *thd }
 /*
@@ -1244,7 +1244,7 @@ End SQL_MODE_ORACLE_SPECIFIC */
    and until NEXT_SYM / PREVIOUS_SYM.
 */
 %left   PREC_BELOW_IDENTIFIER_OPT_SPECIAL_CASE
-%left   TRANSACTION_SYM TIMESTAMP PERIOD_SYM SYSTEM USER COMMENT_SYM
+%left   TRANSACTION_SYM TIMESTAMP PERIOD_SYM SYSTEM USER COMMENT_SYM CONSTRAINT
 
 
 /*
@@ -5943,9 +5943,14 @@ period_for_application_time:
           }
         ;
 
+
 opt_check_constraint:
-          /* empty */      { $$= (Virtual_column_info*) 0; }
-        | check_constraint { $$= $1;}
+          /* empty */      { $$= (Virtual_column_info*) 0; } %prec PREC_BELOW_IDENTIFIER_OPT_SPECIAL_CASE
+        | opt_constraint check_constraint
+          {
+            $$= $2;
+            Lex->add_constraint($1, $2, FALSE);
+          }
         ;
 
 check_constraint: