Merge 10.3 into 10.4

1 files changed, 41 insertions, 0 deletions

diff --git a/mysql-test/main/derived_split_innodb.result b/mysql-test/main/derived_split_innodb.result
index 5073aa84c13..e8f9df5f80d 100644
--- a/mysql-test/main/derived_split_innodb.result
+++ b/mysql-test/main/derived_split_innodb.result
@@ -100,3 +100,44 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 2	LATERAL DERIVED	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.id	1	
 set join_cache_level=default;
 DROP TABLE t1,t2;
+#
+# Bug mdev-18467: join of grouping view and a base table as inner operand
+#                 of left join with on condition containing impossible range
+#
+create table t1 (f1 int, f2 int, key(f2)) engine=InnoDB;
+insert into t1 values (3,33), (7,77), (1,11);
+create table t2 (f1 int, f2 int, primary key (f1)) engine=InnoDB;
+insert into t2 values (3,33), (9,99), (1,11);
+create view v1 as
+select f1, max(f2) as f2 from t2 group by f1;
+select t.f2
+from t1
+left join
+(v1 join t1 as t on v1.f1=t.f1 and t.f2 = null)
+on t1.f1=t.f1;
+f2
+NULL
+NULL
+NULL
+explain select t.f2
+from t1
+left join
+(v1 join t1 as t on v1.f1=t.f1 and t.f2 = null)
+on t1.f1=t.f1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t	const	f2	NULL	NULL	NULL	1	Impossible ON condition
+1	PRIMARY	<derived2>	const	key1	NULL	NULL	NULL	1	Impossible ON condition
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
+2	DERIVED	t2	ALL	PRIMARY	NULL	NULL	NULL	3	Using temporary; Using filesort
+set statement optimizer_switch='split_materialized=off' for explain select t.f2
+from t1
+left join
+(v1 join t1 as t on v1.f1=t.f1 and t.f2 = null)
+on t1.f1=t.f1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t	const	f2	NULL	NULL	NULL	1	Impossible ON condition
+1	PRIMARY	<derived3>	const	key1	NULL	NULL	NULL	1	Impossible ON condition
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
+3	DERIVED	t2	index	NULL	PRIMARY	4	NULL	3	
+drop view v1;
+drop table t1,t2;