revision-id: d816a2485dc9ee17c51f9b14d356d9f8445f098d (mariadb-10.3.7-47-gd816a24) parent(s): b52bb6eb82db8f3bff88efbf0876d364a3826cc1 author: Igor Babaev committer: Igor Babaev timestamp: 2018-06-14 14:30:38 -0700 message: 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. --- mysql-test/main/derived_view.result | 40 +++++++++++++++++++++++++++++++++++++ mysql-test/main/derived_view.test | 24 ++++++++++++++++++++++ sql/sql_select.cc | 3 +++ 3 files changed, 67 insertions(+) diff --git a/mysql-test/main/derived_view.result b/mysql-test/main/derived_view.result index 6c4b331..86dd73f 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 9b0cf9d..68f334e 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; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 52041cc..0bc19ad 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -16811,7 +16811,10 @@ Field *create_tmp_field(THD *thd, TABLE *table,Item *item, Item::Type type, if (result && modify_item) field->result_field= result; if (orig_item) + { item->maybe_null= save_maybe_null; + result->field_name= orig_item->name; + } } else if (table_cant_handle_bit_fields && field->field->type() == MYSQL_TYPE_BIT)