Merge 10.5 into 10.6

4 files changed, 83 insertions, 0 deletions

diff --git a/mysql-test/main/constraints.result b/mysql-test/main/constraints.result
index ca4b762fa5c..143c22321ab 100644
--- a/mysql-test/main/constraints.result
+++ b/mysql-test/main/constraints.result
@@ -183,7 +183,9 @@ t1	CREATE TABLE `t1` (
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
 DROP PROCEDURE sp;
 DROP TABLE t1;
+#
 # End of 10.2 tests
+#
 create table t1 (a int check (a>10)) select 100 as 'a';
 show create table t1;
 Table	Create Table
@@ -201,3 +203,35 @@ a
 19
 ccc
 drop table t1;
+create table t1 (a int, b int);
+create procedure sp() alter table t1 add constraint if not exists foo check (b > 0);
+call sp;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` int(11) DEFAULT NULL,
+  CONSTRAINT `foo` CHECK (`b` > 0)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+call sp;
+Warnings:
+Note	1826	Duplicate CHECK constraint name 'foo'
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` int(11) DEFAULT NULL,
+  CONSTRAINT `foo` CHECK (`b` > 0)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+call sp;
+Warnings:
+Note	1826	Duplicate CHECK constraint name 'foo'
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` int(11) DEFAULT NULL,
+  CONSTRAINT `foo` CHECK (`b` > 0)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+drop procedure sp;
+drop table t1;
diff --git a/mysql-test/main/constraints.test b/mysql-test/main/constraints.test
index 2f4dadcee9d..5c673f9be81 100644
--- a/mysql-test/main/constraints.test
+++ b/mysql-test/main/constraints.test
@@ -151,7 +151,9 @@ show create table t1;
 DROP PROCEDURE sp;
 DROP TABLE t1;
 
+--echo #
 --echo # End of 10.2 tests
+--echo #
 
 #
 # Check that we don't lose constraints as part of CREATE ... SELECT
@@ -172,3 +174,18 @@ insert into t1 values ("ccc");
 insert into t1 values ("");
 select * from t1;
 drop table t1;
+
+#
+# add if not exists in SP
+#
+
+create table t1 (a int, b int);
+create procedure sp() alter table t1 add constraint if not exists foo check (b > 0);
+call sp;
+show create table t1;
+call sp;
+show create table t1;
+call sp;
+show create table t1;
+drop procedure sp;
+drop table t1;
diff --git a/mysql-test/main/join.result b/mysql-test/main/join.result
index 11b7ecad3ee..942ee96fc32 100644
--- a/mysql-test/main/join.result
+++ b/mysql-test/main/join.result
@@ -3407,3 +3407,20 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 drop table t1,t2,t3;
 drop table t1000,t10,t03;
 # End of 10.3 tests
+#
+# MDEV-30080 Wrong result with LEFT JOINs involving constant tables
+#
+CREATE TABLE t1 (a INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1);
+CREATE TABLE t2 (b INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t3 (c INT PRIMARY KEY) ENGINE=MyISAM;
+SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b = t3.c) ON t1.a = t2.b;
+a	b	c
+1	1	NULL
+1	1	NULL
+SELECT COUNT(*) FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b = t3.c) ON t1.a = t2.b;
+COUNT(*)
+2
+DROP TABLE t1, t2, t3;
+# End of 10.5 tests
diff --git a/mysql-test/main/join.test b/mysql-test/main/join.test
index b99f05f7c88..c8bd2886b30 100644
--- a/mysql-test/main/join.test
+++ b/mysql-test/main/join.test
@@ -1820,3 +1820,18 @@ drop table t1,t2,t3;
 drop table t1000,t10,t03;
 
 --echo # End of 10.3 tests
+
+--echo #
+--echo # MDEV-30080 Wrong result with LEFT JOINs involving constant tables
+--echo #
+
+CREATE TABLE t1 (a INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1);
+CREATE TABLE t2 (b INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t3 (c INT PRIMARY KEY) ENGINE=MyISAM;
+SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b = t3.c) ON t1.a = t2.b;
+SELECT COUNT(*) FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b = t3.c) ON t1.a = t2.b;
+DROP TABLE t1, t2, t3;
+
+--echo # End of 10.5 tests