revision-id: 87e97b6c74359e3d2fe9094f1198d37b6e68792e (mariadb-10.3.7-48-g87e97b6) parent(s): 4787913db0b49b4a48e337e6b76b8b6c8233a941 author: Igor Babaev committer: Igor Babaev timestamp: 2018-06-14 15:48:57 -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. 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). --- 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)