Merge 10.3 into 10.4

7 files changed, 425 insertions, 0 deletions

diff --git a/mysql-test/main/join_outer.result b/mysql-test/main/join_outer.result
index b7fcb55e4fe..8048df5f1f5 100644
--- a/mysql-test/main/join_outer.result
+++ b/mysql-test/main/join_outer.result
@@ -1,4 +1,5 @@
 drop table if exists t0,t1,t2,t3,t4,t5;
+drop view if exists v0,v1,v2,v3;
 SET @org_optimizer_switch=@@optimizer_switch;
 SET optimizer_switch=ifnull(@optimizer_switch_for_join_outer_test,'outer_join_with_cache=off');
 set join_cache_level=1;
@@ -2825,5 +2826,89 @@ WHERE t3.pk IN (2);
 1
 drop view v4;
 drop table t1,t2,t3,t4;
+#
+# MDEV-28602 Wrong result with outer join, merged derived table and view
+#
+create table t1 (
+Election int(10) unsigned NOT NULL
+);
+insert into t1 (Election) values (1);
+create table t2 (
+VoteID int(10),
+ElectionID int(10),
+UserID int(10)
+);
+insert into t2 (ElectionID, UserID) values (2,  30), (3, 30);
+create view v1 as select * from t1
+left join ( select 'Y' AS Voted, ElectionID from t2 ) AS T 
+on T.ElectionID = t1.Election
+limit 9;
+select * from v1;
+Election	Voted	ElectionID
+1	NULL	NULL
+drop table t1, t2;
+drop view v1;
+#
+# and another contrived example showing a bit of heirarchy
+#
+create table t10 (a int);
+create table t20 (b int);
+insert into t10 values (1),(2);
+insert into t20 values (1),(3);
+create view v10 as select *, 'U' as u from t10 left join (select 'Y' as y, t20.b from t20) dt1 on t10.a= dt1.b limit 3;
+create table t30 (c int);
+insert into t30 values (1),(3);
+create view v20 as select * from t30 left join (select 'X' as x, v10.u, v10.y, v10.b from v10) dt2 on t30.c=dt2.b limit 6;
+select * from v20 limit 9;
+c	x	u	y	b
+1	X	U	Y	1
+3	NULL	NULL	NULL	NULL
+drop view v10, v20;
+drop table t10, t20, t30;
+#
+# More complex testcase
+#
+create table t2 (b int);
+insert into t2 values (3),(7),(1);
+create table t3 (c int);
+insert into t3 values (3),(1);
+create table t1 (a int);
+insert into t1 values (1),(2),(7),(1);
+select * from
+(
+select * from
+(select 'Z' as z, t1.a from t1) dt1
+left join 
+(select 'Y' as y, t2.b from t2) dt2
+left join
+(select 'X' as x, t3.c from t3) dt3
+on dt2.b=dt3.c
+on dt1.a=dt2.b
+limit 9
+) dt;
+z	a	y	b	x	c
+Z	1	Y	1	X	1
+Z	2	NULL	NULL	NULL	NULL
+Z	7	Y	7	NULL	NULL
+Z	1	Y	1	X	1
+create view v3(x,c) as select * from (select 'X' as x, t3.c from t3) dt3;
+create view v2(y,b) as select * from (select 'Y' as y, t2.b from t2) dt2;
+create view v0(y,b,x,c) as select * from v2 left join v3 on v2.b=v3.c;
+create view v1 as  select 'Z' as z, t1.a, v0.*  from t1 left join v0 on t1.a=v0.b limit 9;
+select * from v1;
+z	a	y	b	x	c
+Z	1	Y	1	X	1
+Z	2	NULL	NULL	NULL	NULL
+Z	7	Y	7	NULL	NULL
+Z	1	Y	1	X	1
+set statement join_cache_level=0 for
+select * from v1;
+z	a	y	b	x	c
+Z	1	Y	1	X	1
+Z	2	NULL	NULL	NULL	NULL
+Z	7	Y	7	NULL	NULL
+Z	1	Y	1	X	1
+drop view v0, v1, v2, v3;
+drop table t1, t2, t3;
 # end of 10.3 tests
 SET optimizer_switch=@org_optimizer_switch;
diff --git a/mysql-test/main/join_outer.test b/mysql-test/main/join_outer.test
index ff74e5280e7..5e1e83e4049 100644
--- a/mysql-test/main/join_outer.test
+++ b/mysql-test/main/join_outer.test
@@ -6,6 +6,7 @@
 
 --disable_warnings
 drop table if exists t0,t1,t2,t3,t4,t5;
+drop view if exists v0,v1,v2,v3;
 --enable_warnings
 
 SET @org_optimizer_switch=@@optimizer_switch;
@@ -2341,6 +2342,90 @@ WHERE t3.pk IN (2);
 drop view v4;
 drop table t1,t2,t3,t4;
 
+--echo #
+--echo # MDEV-28602 Wrong result with outer join, merged derived table and view
+--echo #
+
+create table t1 (
+  Election int(10) unsigned NOT NULL
+);
+
+insert into t1 (Election) values (1);
+ 
+create table t2 (
+  VoteID int(10),
+  ElectionID int(10),
+  UserID int(10)
+);
+
+insert into t2 (ElectionID, UserID) values (2,  30), (3, 30);
+create view v1 as select * from t1
+  left join ( select 'Y' AS Voted, ElectionID from t2 ) AS T 
+    on T.ElectionID = t1.Election
+limit 9;
+# limit X causes merge algorithm select as opposed to temp table
+select * from v1;
+drop table t1, t2;
+drop view v1;
+
+--echo #
+--echo # and another contrived example showing a bit of heirarchy
+--echo #
+create table t10 (a int);
+create table t20 (b int);
+insert into t10 values (1),(2);
+insert into t20 values (1),(3);
+create view v10 as select *, 'U' as u from t10 left join (select 'Y' as y, t20.b from t20) dt1 on t10.a= dt1.b limit 3;
+create table t30 (c int);
+insert into t30 values (1),(3);
+create view v20 as select * from t30 left join (select 'X' as x, v10.u, v10.y, v10.b from v10) dt2 on t30.c=dt2.b limit 6;
+select * from v20 limit 9;
+drop view v10, v20;
+drop table t10, t20, t30;
+
+--echo #
+--echo # More complex testcase
+--echo #
+create table t2 (b int);
+insert into t2 values (3),(7),(1);
+create table t3 (c int);
+insert into t3 values (3),(1);
+create table t1 (a int);
+insert into t1 values (1),(2),(7),(1);
+ 
+select * from
+(
+  select * from
+    (select 'Z' as z, t1.a from t1) dt1
+    left join 
+      (select 'Y' as y, t2.b from t2) dt2
+      left join
+      (select 'X' as x, t3.c from t3) dt3
+      on dt2.b=dt3.c
+    on dt1.a=dt2.b
+  limit 9
+) dt;
+
+## Same as dt3 above
+create view v3(x,c) as select * from (select 'X' as x, t3.c from t3) dt3;
+
+## Same as dt2 above
+create view v2(y,b) as select * from (select 'Y' as y, t2.b from t2) dt2;
+
+## Same as  (...) in the "... dt1 left join (...)" above
+create view v0(y,b,x,c) as select * from v2 left join v3 on v2.b=v3.c;
+
+# Same as above select statement
+create view v1 as  select 'Z' as z, t1.a, v0.*  from t1 left join v0 on t1.a=v0.b limit 9;
+
+select * from v1;
+
+set statement join_cache_level=0 for
+select * from v1;
+
+drop view v0, v1, v2, v3;
+drop table t1, t2, t3;
+
 --echo # end of 10.3 tests
 
 SET optimizer_switch=@org_optimizer_switch;
diff --git a/mysql-test/main/join_outer_jcl6.result b/mysql-test/main/join_outer_jcl6.result
index 3cb846426fe..f483f5bb634 100644
--- a/mysql-test/main/join_outer_jcl6.result
+++ b/mysql-test/main/join_outer_jcl6.result
@@ -6,6 +6,7 @@ set @@join_cache_level=6;
 set @optimizer_switch_for_join_outer_test=@@optimizer_switch;
 set @join_cache_level_for_join_outer_test=@@join_cache_level;
 drop table if exists t0,t1,t2,t3,t4,t5;
+drop view if exists v0,v1,v2,v3;
 SET @org_optimizer_switch=@@optimizer_switch;
 SET optimizer_switch=ifnull(@optimizer_switch_for_join_outer_test,'outer_join_with_cache=off');
 set join_cache_level=@join_cache_level_for_join_outer_test;
@@ -2832,5 +2833,89 @@ WHERE t3.pk IN (2);
 1
 drop view v4;
 drop table t1,t2,t3,t4;
+#
+# MDEV-28602 Wrong result with outer join, merged derived table and view
+#
+create table t1 (
+Election int(10) unsigned NOT NULL
+);
+insert into t1 (Election) values (1);
+create table t2 (
+VoteID int(10),
+ElectionID int(10),
+UserID int(10)
+);
+insert into t2 (ElectionID, UserID) values (2,  30), (3, 30);
+create view v1 as select * from t1
+left join ( select 'Y' AS Voted, ElectionID from t2 ) AS T 
+on T.ElectionID = t1.Election
+limit 9;
+select * from v1;
+Election	Voted	ElectionID
+1	NULL	NULL
+drop table t1, t2;
+drop view v1;
+#
+# and another contrived example showing a bit of heirarchy
+#
+create table t10 (a int);
+create table t20 (b int);
+insert into t10 values (1),(2);
+insert into t20 values (1),(3);
+create view v10 as select *, 'U' as u from t10 left join (select 'Y' as y, t20.b from t20) dt1 on t10.a= dt1.b limit 3;
+create table t30 (c int);
+insert into t30 values (1),(3);
+create view v20 as select * from t30 left join (select 'X' as x, v10.u, v10.y, v10.b from v10) dt2 on t30.c=dt2.b limit 6;
+select * from v20 limit 9;
+c	x	u	y	b
+1	X	U	Y	1
+3	NULL	NULL	NULL	NULL
+drop view v10, v20;
+drop table t10, t20, t30;
+#
+# More complex testcase
+#
+create table t2 (b int);
+insert into t2 values (3),(7),(1);
+create table t3 (c int);
+insert into t3 values (3),(1);
+create table t1 (a int);
+insert into t1 values (1),(2),(7),(1);
+select * from
+(
+select * from
+(select 'Z' as z, t1.a from t1) dt1
+left join 
+(select 'Y' as y, t2.b from t2) dt2
+left join
+(select 'X' as x, t3.c from t3) dt3
+on dt2.b=dt3.c
+on dt1.a=dt2.b
+limit 9
+) dt;
+z	a	y	b	x	c
+Z	1	Y	1	X	1
+Z	1	Y	1	X	1
+Z	7	Y	7	NULL	NULL
+Z	2	NULL	NULL	NULL	NULL
+create view v3(x,c) as select * from (select 'X' as x, t3.c from t3) dt3;
+create view v2(y,b) as select * from (select 'Y' as y, t2.b from t2) dt2;
+create view v0(y,b,x,c) as select * from v2 left join v3 on v2.b=v3.c;
+create view v1 as  select 'Z' as z, t1.a, v0.*  from t1 left join v0 on t1.a=v0.b limit 9;
+select * from v1;
+z	a	y	b	x	c
+Z	1	Y	1	X	1
+Z	1	Y	1	X	1
+Z	7	Y	7	NULL	NULL
+Z	2	NULL	NULL	NULL	NULL
+set statement join_cache_level=0 for
+select * from v1;
+z	a	y	b	x	c
+Z	1	Y	1	X	1
+Z	2	NULL	NULL	NULL	NULL
+Z	7	Y	7	NULL	NULL
+Z	1	Y	1	X	1
+drop view v0, v1, v2, v3;
+drop table t1, t2, t3;
 # end of 10.3 tests
 SET optimizer_switch=@org_optimizer_switch;
diff --git a/mysql-test/suite/binlog/r/binlog_verbose_compressed_fields.result b/mysql-test/suite/binlog/r/binlog_verbose_compressed_fields.result
new file mode 100644
index 00000000000..6ee11754935
--- /dev/null
+++ b/mysql-test/suite/binlog/r/binlog_verbose_compressed_fields.result
@@ -0,0 +1,15 @@
+CREATE TABLE t1 (a TEXT, ac TEXT COMPRESSED, b TINYTEXT, bc TINYTEXT COMPRESSED, c MEDIUMTEXT, cc MEDIUMTEXT COMPRESSED, d LONGTEXT, dc LONGTEXT COMPRESSED, e VARCHAR(10), ec VARCHAR(10) COMPRESSED);
+# Isolate row event into its own binary log
+FLUSH BINARY LOGS;
+INSERT INTO t1 VALUES ('mya', 'myac', 'myb', 'mybc', 'myc', 'mycc', 'myd', 'mydc', 'mye', 'myec');
+FLUSH BINARY LOGS;
+# MYSQLBINLOG --base64-output=decode-rows -vv datadir/binlog_file --result-file=result_binlog
+include/assert_grep.inc [Ensure compressed TEXT fields are annotated correctly]
+include/assert_grep.inc [Ensure compressed TINYTEXT fields are annotated correctly]
+include/assert_grep.inc [Ensure compressed MEDIUMTEXT fields are annotated correctly]
+include/assert_grep.inc [Ensure compressed LONGTEXT fields are annotated correctly]
+include/assert_grep.inc [Ensure compressed VARSTRING fields are annotated correctly]
+include/assert_grep.inc [Ensure COMPRESSED only shows up for corresponding fields]
+include/assert_grep.inc [Ensure non-compressed TEXT fields are annotated correctly]
+include/assert_grep.inc [Ensure non-compressed VARSTRING fields are annotated correctly]
+DROP TABLE t1;
diff --git a/mysql-test/suite/binlog/t/binlog_verbose_compressed_fields.test b/mysql-test/suite/binlog/t/binlog_verbose_compressed_fields.test
new file mode 100644
index 00000000000..8cbcdbef601
--- /dev/null
+++ b/mysql-test/suite/binlog/t/binlog_verbose_compressed_fields.test
@@ -0,0 +1,70 @@
+#
+# Purpose:
+#   This test validates that mysqlbinlog is able to annotate compressed column
+# types with two levels of verbosity.
+#
+# Methodology:
+#   Validate that the output from mysqlbinlog -vv after creating and inserting
+# into a table with compressed and uncompressed fields correctly annotates
+# which columns are compressed
+#
+# References:
+#   MDEV-25277: mysqlbinlog --verbose cannot read row events with compressed
+#               columns: Don't know how to handle column type: 140
+#
+--source include/have_binlog_format_row.inc
+
+CREATE TABLE t1 (a TEXT, ac TEXT COMPRESSED, b TINYTEXT, bc TINYTEXT COMPRESSED, c MEDIUMTEXT, cc MEDIUMTEXT COMPRESSED, d LONGTEXT, dc LONGTEXT COMPRESSED, e VARCHAR(10), ec VARCHAR(10) COMPRESSED);
+
+--echo # Isolate row event into its own binary log
+FLUSH BINARY LOGS;
+INSERT INTO t1 VALUES ('mya', 'myac', 'myb', 'mybc', 'myc', 'mycc', 'myd', 'mydc', 'mye', 'myec');
+FLUSH BINARY LOGS;
+
+--let $binlog_file= query_get_value(SHOW BINARY LOGS, Log_name, 2)
+--let $datadir= `SELECT @@datadir`
+--let $result_binlog= $MYSQLTEST_VARDIR/tmp/$binlog_file
+
+--echo # MYSQLBINLOG --base64-output=decode-rows -vv datadir/binlog_file --result-file=result_binlog
+--exec $MYSQL_BINLOG --base64-output=decode-rows -vv $datadir/$binlog_file --result-file=$result_binlog
+
+--let $assert_file= $result_binlog
+--let $assert_count= 1
+
+--let $assert_text= Ensure compressed TEXT fields are annotated correctly
+--let $assert_select=\WTEXT COMPRESSED
+--source include/assert_grep.inc
+
+--let $assert_text= Ensure compressed TINYTEXT fields are annotated correctly
+--let $assert_select=\WTINYTEXT COMPRESSED
+--source include/assert_grep.inc
+
+--let $assert_text= Ensure compressed MEDIUMTEXT fields are annotated correctly
+--let $assert_select=\WMEDIUMTEXT COMPRESSED
+--source include/assert_grep.inc
+
+--let $assert_text= Ensure compressed LONGTEXT fields are annotated correctly
+--let $assert_select=\WLONGTEXT COMPRESSED
+--source include/assert_grep.inc
+
+--let $assert_text= Ensure compressed VARSTRING fields are annotated correctly
+--let $assert_select=\WVARSTRING\(\d+\) COMPRESSED
+--source include/assert_grep.inc
+
+--let $assert_text= Ensure COMPRESSED only shows up for corresponding fields
+--let $assert_count= 5
+--let $assert_select= COMPRESSED
+--source include/assert_grep.inc
+
+--let $assert_text= Ensure non-compressed TEXT fields are annotated correctly
+--let $assert_count= 8
+--let $assert_select=/*.*TEXT
+--source include/assert_grep.inc
+
+--let $assert_text= Ensure non-compressed VARSTRING fields are annotated correctly
+--let $assert_count= 2
+--let $assert_select=/*.*VARSTRING
+--source include/assert_grep.inc
+
+# Cleanup
+DROP TABLE t1;
diff --git a/mysql-test/suite/versioning/r/foreign.result b/mysql-test/suite/versioning/r/foreign.result
index ae53511d7e0..51d793f0cf8 100644
--- a/mysql-test/suite/versioning/r/foreign.result
+++ b/mysql-test/suite/versioning/r/foreign.result
@@ -495,3 +495,42 @@ set foreign_key_checks= on;
 delete history from t1;
 delete from t1;
 drop table t1;
+#
+# MDEV-30378 Versioned REPLACE succeeds with ON DELETE RESTRICT
+# constraint
+#
+create table t0 (pk integer primary key) with system versioning engine=innodb;
+create table t1 (pk integer primary key,
+foreign key(pk) references t0(pk)
+on delete restrict on update cascade) engine=innodb;
+create table t2 (pk integer);
+insert into t0 (pk) values (1);
+insert into t1 (pk) values (1);
+insert into t2 (pk) values (1);
+delete from t0;
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`pk`) REFERENCES `t0` (`pk`) ON UPDATE CASCADE)
+replace t0 values (1);
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`pk`) REFERENCES `t0` (`pk`) ON UPDATE CASCADE)
+select * into outfile 'load_t0' from t0 ;
+load data infile 'load_t0' replace into table t0;
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`pk`) REFERENCES `t0` (`pk`) ON UPDATE CASCADE)
+delete t0, t2 from t0 join t2;
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`pk`) REFERENCES `t0` (`pk`) ON UPDATE CASCADE)
+select pk from t0;
+pk
+1
+# Cleanup
+drop table t1, t0, t2;
+# create_select for a temporary table didn't set up pos_in_locked_tables.
+create table t (a int unique) engine=innodb
+replace select 1 as a, 2 as b union select 1 as a, 3 as c;
+select * from t;
+a	b
+1	3
+drop table t;
+create temporary table t (a int unique) engine=innodb
+replace select 1 as a, 2 as b union select 1 as a, 3 as c;
+select * from t;
+a	b
+1	3
+drop table t;
diff --git a/mysql-test/suite/versioning/t/foreign.test b/mysql-test/suite/versioning/t/foreign.test
index 3548a9b301b..532086b791d 100644
--- a/mysql-test/suite/versioning/t/foreign.test
+++ b/mysql-test/suite/versioning/t/foreign.test
@@ -527,4 +527,50 @@ delete from t1;
 # cleanup
 drop table t1;
 
+--echo #
+--echo # MDEV-30378 Versioned REPLACE succeeds with ON DELETE RESTRICT
+--echo # constraint
+--echo #
+create table t0 (pk integer primary key) with system versioning engine=innodb;
+create table t1 (pk integer primary key,
+                 foreign key(pk) references t0(pk)
+                            on delete restrict on update cascade) engine=innodb;
+create table t2 (pk integer);
+
+insert into t0 (pk) values (1);
+insert into t1 (pk) values (1);
+insert into t2 (pk) values (1);
+
+--error ER_ROW_IS_REFERENCED_2
+delete from t0;
+
+--error ER_ROW_IS_REFERENCED_2
+replace t0 values (1);
+
+select * into outfile 'load_t0' from t0 ;
+--error ER_ROW_IS_REFERENCED_2
+load data infile 'load_t0' replace into table t0;
+
+--error ER_ROW_IS_REFERENCED_2
+delete t0, t2 from t0 join t2;
+
+select pk from t0;
+
+--echo # Cleanup
+drop table t1, t0, t2;
+--let $datadir= `select @@datadir`
+--remove_file $datadir/test/load_t0
+
+
+--echo # create_select for a temporary table didn't set up pos_in_locked_tables.
+create table t (a int unique) engine=innodb
+    replace select 1 as a, 2 as b union select 1 as a, 3 as c;
+select * from t;
+drop table t;
+
+create temporary table t (a int unique) engine=innodb
+    replace select 1 as a, 2 as b union select 1 as a, 3 as c;
+select * from t;
+drop table t;
+
 --source suite/versioning/common_finish.inc