MDEV-16420 View stop working after upgrade from 10.1.15 to 10.3.7

This bug happened for queries that used a materialized view that
renamed columns of the specifying query in an inner table of
an outer join. For such a query name resolution for a column
belonging the view could fail if the underlying column was
non-nullable.
When creating the defintion of the the temporary table for
the materialized view used in the inner part of an outer join
the definition of the non-nullable columns are created by the
function create_tmp_field_from_item() that names the columns
according to the names of the underlying columns. So these names
should be changed for the view column names.

This bug cannot be reproduced in 10.2 because there setup_fields()
called when preparing joins in the view specification effectively
renames the underlying columns in the function find_field_in_view().
In 10.3 this renaming was removed as improper
(see Monty's commit b478276b04d4dd122e2ed4d4e2cd7eb69c0fb2d2).

2 files changed, 64 insertions, 0 deletions

diff --git a/mysql-test/main/derived_view.result b/mysql-test/main/derived_view.result
index 6c4b3310e11..86dd73f5733 100644
--- a/mysql-test/main/derived_view.result
+++ b/mysql-test/main/derived_view.result
@@ -2977,5 +2977,45 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 Warnings:
 Note	1003	/* select#1 */ select straight_join `test`.`t1`.`c1` AS `c1` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`c1`,<exists>(/* select#3 */ select `test`.`t2`.`c2` from `test`.`t2` where <cache>(`test`.`t1`.`c1`) = `test`.`t2`.`c2`))
 DROP TABLE t1, t2;
+#
+# Bug mdev-16420: materialized view that renames columns
+#                 in inner part of outer join
+#
+CREATE TABLE t1 (id int, PRIMARY KEY (id));
+INSERT INTO t1 VALUES (2), (3), (7), (1);
+CREATE VIEW v1 AS SELECT * FROM t1;
+CREATE  VIEW v2 AS SELECT v1.id AS order_pk FROM v1 GROUP BY v1.id;
+CREATE  VIEW v3 AS
+SELECT t.id AS order_pk FROM (SELECT * FROM t1) AS t GROUP BY t.id;
+SELECT * FROM t1  LEFT JOIN v2  ON t1.id=v2.order_pk;
+id	order_pk
+1	1
+2	2
+3	3
+7	7
+EXPLAIN EXTENDED
+SELECT * FROM t1  LEFT JOIN v2  ON t1.id=v2.order_pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	index	NULL	PRIMARY	4	NULL	4	100.00	Using index
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.id	2	100.00	
+2	DERIVED	t1	index	PRIMARY	PRIMARY	4	NULL	4	100.00	Using index; Using filesort
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`id` AS `id`,`v2`.`order_pk` AS `order_pk` from `test`.`t1` left join `test`.`v2` on(`v2`.`order_pk` = `test`.`t1`.`id`) where 1
+SELECT * FROM t1  LEFT JOIN v3  ON t1.id=v3.order_pk;
+id	order_pk
+1	1
+2	2
+3	3
+7	7
+EXPLAIN EXTENDED
+SELECT * FROM t1  LEFT JOIN v3  ON t1.id=v3.order_pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	index	NULL	PRIMARY	4	NULL	4	100.00	Using index
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.id	2	100.00	
+2	DERIVED	t1	index	PRIMARY	PRIMARY	4	NULL	4	100.00	Using index; Using filesort
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`id` AS `id`,`v3`.`order_pk` AS `order_pk` from `test`.`t1` left join `test`.`v3` on(`v3`.`order_pk` = `test`.`t1`.`id`) where 1
+DROP VIEW v1,v2,v3;
+DROP TABLE t1;
 set optimizer_switch=@exit_optimizer_switch;
 set join_cache_level=@exit_join_cache_level;
diff --git a/mysql-test/main/derived_view.test b/mysql-test/main/derived_view.test
index 9b0cf9dca7d..68f334e1ac4 100644
--- a/mysql-test/main/derived_view.test
+++ b/mysql-test/main/derived_view.test
@@ -1949,6 +1949,30 @@ eval EXPLAIN EXTENDED $q;
 
 DROP TABLE t1, t2;
 
+--echo #
+--echo # Bug mdev-16420: materialized view that renames columns
+--echo #                 in inner part of outer join
+--echo #
+
+CREATE TABLE t1 (id int, PRIMARY KEY (id));
+INSERT INTO t1 VALUES (2), (3), (7), (1);
+
+CREATE VIEW v1 AS SELECT * FROM t1;
+CREATE  VIEW v2 AS SELECT v1.id AS order_pk FROM v1 GROUP BY v1.id;
+CREATE  VIEW v3 AS
+SELECT t.id AS order_pk FROM (SELECT * FROM t1) AS t GROUP BY t.id;
+
+SELECT * FROM t1  LEFT JOIN v2  ON t1.id=v2.order_pk;
+EXPLAIN EXTENDED
+SELECT * FROM t1  LEFT JOIN v2  ON t1.id=v2.order_pk;
+
+SELECT * FROM t1  LEFT JOIN v3  ON t1.id=v3.order_pk;
+EXPLAIN EXTENDED
+SELECT * FROM t1  LEFT JOIN v3  ON t1.id=v3.order_pk;
+
+DROP VIEW v1,v2,v3;
+DROP TABLE t1;
+
 # The following command must be the last one the file 
 set optimizer_switch=@exit_optimizer_switch;
 set join_cache_level=@exit_join_cache_level;