revision-id: 841e8877ccb8ef5d692a22b4447383a360557326 (mariadb-10.4.27-107-g841e887) parent(s): 839c7fcf38ef17f9627f63ebec3e82d814912973 author: Igor Babaev committer: Igor Babaev timestamp: 2023-02-27 10:51:22 -0800 message: MDEV-28603 Invalid view when its definition uses TVC as single-value subquery Subselect_single_value_engine cannot handle table value constructor used as subquery. That's why any table value constructor TVC used as subquery is converted into a select over derived table whose specification is TVC. Currently the names of the columns of the derived table DT are taken from the first element of TVC and if the k-th component of the element happens to be a subquery the text representation of this subquery serves as the name of the k-th column of the derived table. References of all columns of the derived table DT compose the select list of the result of the conversion. If a definition of a view contained a table value constructor used as a subquery and the view was registered after this conversion had been applied we could register an invalid view definition if the first element of TVC contained a subquery as its component: the name of this component was taken from the original subquery, while the name of the corresponding column of the derived table was taken from the text representation of the subquery produced by the function SELECT_LEX::print() and these names were usually differ from each other. To avoid registration of such invalid views the function SELECT_LEX::print() now prints the original TVC instead of the select in which this TVC has been wrapped. Now the specification of registered view looks like as if no conversions from TVC to selects were done. Approved by Oleksandr Byelkin <sanja@mariadb.com> --- mysql-test/main/table_value_constr.result | 119 ++++++++++++++++++++++++++++++ mysql-test/main/table_value_constr.test | 74 +++++++++++++++++++ sql/mysqld.h | 2 + sql/sql_lex.cc | 1 + sql/sql_lex.h | 3 +- sql/sql_select.cc | 6 ++ sql/sql_show.cc | 3 +- sql/sql_tvc.cc | 1 + sql/sql_view.cc | 6 +- 9 files changed, 211 insertions(+), 4 deletions(-) diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result index c8e6363..bd0aac5 100644 --- a/mysql-test/main/table_value_constr.result +++ b/mysql-test/main/table_value_constr.result @@ -3133,5 +3133,124 @@ INSERT INTO t1 (VALUES (IGNORE) UNION VALUES (IGNORE)); ERROR HY000: 'ignore' is not allowed in this context DROP TABLE t1; # +# MDEV-28603: VIEW with table value constructor used as single-value +# subquery contains subquery as its first element +# +create table t1 (a int); +insert into t1 values (3), (7), (1); +create table t2 (b int); +insert into t2 values (1), (2); +create view v as select (values ((select * from t1 where a > 5))) as m from t2; +select (values ((select * from t1 where a > 5))) as m from t2; +m +7 +7 +select * from v; +m +7 +7 +with cte as ( select (values ((select * from t1 where a > 5))) as m from t2 ) select * from cte; +m +7 +7 +explain select (values ((select * from t1 where a > 5))) as m from t2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +4 SUBQUERY <derived2> ALL NULL NULL NULL NULL 2 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +3 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where +explain select * from v; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +5 SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 +3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +4 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where +explain with cte as ( select (values ((select * from t1 where a > 5))) as m from t2 ) select * from cte; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +5 SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 +3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +4 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where +prepare stmt from "select (values ((select * from t1 where a > 5))) as m from t2"; +execute stmt; +m +7 +7 +execute stmt; +m +7 +7 +deallocate prepare stmt; +prepare stmt from "select * from v"; +execute stmt; +m +7 +7 +execute stmt; +m +7 +7 +deallocate prepare stmt; +prepare stmt from "with cte as ( select (values ((select * from t1 where a > 5))) as m from t2 ) select * from cte"; +execute stmt; +m +7 +7 +execute stmt; +m +7 +7 +deallocate prepare stmt; +show create view v; +View Create View character_set_client collation_connection +v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select (values ((select `t1`.`a` from `t1` where `t1`.`a` > 5))) AS `m` from `t2` latin1 latin1_swedish_ci +drop view v; +prepare stmt from "create view v as select (values ((select * from t1 where a > 5))) as m from t2"; +execute stmt; +show create view v; +View Create View character_set_client collation_connection +v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select (values ((select `t1`.`a` from `t1` where `t1`.`a` > 5))) AS `m` from `t2` latin1 latin1_swedish_ci +select * from v; +m +7 +7 +drop view v; +execute stmt; +show create view v; +View Create View character_set_client collation_connection +v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select (values ((select `t1`.`a` from `t1` where `t1`.`a` > 5))) AS `m` from `t2` latin1 latin1_swedish_ci +select * from v; +m +7 +7 +deallocate prepare stmt; +prepare stmt from "show create view v"; +execute stmt; +View Create View character_set_client collation_connection +v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select (values ((select `t1`.`a` from `t1` where `t1`.`a` > 5))) AS `m` from `t2` latin1 latin1_swedish_ci +execute stmt; +View Create View character_set_client collation_connection +v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select (values ((select `t1`.`a` from `t1` where `t1`.`a` > 5))) AS `m` from `t2` latin1 latin1_swedish_ci +deallocate prepare stmt; +drop view v; +create view v as select (values ((select * from t1 where a > 5 +union +select * from t1 where a > 7))) as m from t2; +select (values ((select * from t1 where a > 5 +union +select * from t1 where a > 7))) as m from t2; +m +7 +7 +select * from v; +m +7 +7 +show create view v; +View Create View character_set_client collation_connection +v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select (values ((select `t1`.`a` from `t1` where `t1`.`a` > 5 union select `t1`.`a` from `t1` where `t1`.`a` > 7))) AS `m` from `t2` latin1 latin1_swedish_ci +drop view v; +drop table t1,t2; +# # End of 10.4 tests # diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test index 19af93e..1d58cd0 100644 --- a/mysql-test/main/table_value_constr.test +++ b/mysql-test/main/table_value_constr.test @@ -1737,5 +1737,79 @@ INSERT INTO t1 (VALUES (IGNORE) UNION VALUES (IGNORE)); DROP TABLE t1; --echo # +--echo # MDEV-28603: VIEW with table value constructor used as single-value +--echo # subquery contains subquery as its first element +--echo # + +create table t1 (a int); +insert into t1 values (3), (7), (1); +create table t2 (b int); +insert into t2 values (1), (2); + +let $q= +select (values ((select * from t1 where a > 5))) as m from t2; + +eval create view v as $q; + +eval $q; +eval select * from v; +eval with cte as ( $q ) select * from cte; + +eval explain $q; +eval explain select * from v; +eval explain with cte as ( $q ) select * from cte; + +eval prepare stmt from "$q"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +eval prepare stmt from "select * from v"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +eval prepare stmt from "with cte as ( $q ) select * from cte"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +show create view v; + +drop view v; + +eval prepare stmt from "create view v as $q"; +execute stmt; +show create view v; +select * from v; +drop view v; +execute stmt; +show create view v; +select * from v; +deallocate prepare stmt; + +prepare stmt from "show create view v"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +drop view v; + +let $q= +select (values ((select * from t1 where a > 5 + union + select * from t1 where a > 7))) as m from t2; + +eval create view v as $q; + +eval $q; +eval select * from v; + +show create view v; + +drop view v; +drop table t1,t2; + +--echo # --echo # End of 10.4 tests --echo # diff --git a/sql/mysqld.h b/sql/mysqld.h index 60afb0b..f521ea2 100644 --- a/sql/mysqld.h +++ b/sql/mysqld.h @@ -755,6 +755,8 @@ enum enum_query_type // it evaluates to. Should be used for error messages, so that they // don't reveal values. QT_NO_DATA_EXPANSION= (1 << 9), + // Remove wrappers added for TVC when creating or showing view + QT_NO_WRAPPERS_FOR_TVC_IN_VIEW= (1 << 11), }; diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 24958b5..62c7556 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -2487,6 +2487,7 @@ void st_select_lex::init_select() curr_tvc_name= 0; in_tvc= false; versioned_tables= 0; + is_tvc_wrapper= false; } /* diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 3545a4e..8f25426 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -1132,7 +1132,8 @@ class st_select_lex: public st_select_lex_node st_select_lex. */ uint curr_tvc_name; - + /* true <=> select has been created a TVC wrapper */ + bool is_tvc_wrapper; /* Needed to correctly generate 'PRIMARY' or 'SIMPLE' for select_type column of EXPLAIN diff --git a/sql/sql_select.cc b/sql/sql_select.cc index f83bf32..2c12d1c 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -27992,6 +27992,12 @@ void st_select_lex::print(THD *thd, String *str, enum_query_type query_type) return; } + if (is_tvc_wrapper && (query_type & QT_NO_WRAPPERS_FOR_TVC_IN_VIEW)) + { + first_inner_unit()->first_select()->print(thd, str, query_type); + return; + } + if ((query_type & QT_SHOW_SELECT_NUMBER) && thd->lex->all_selects_list && thd->lex->all_selects_list->link_next && diff --git a/sql/sql_show.cc b/sql/sql_show.cc index 9fee8ce..1ad20ac 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -2707,7 +2707,8 @@ static int show_create_view(THD *thd, TABLE_LIST *table, String *buff) a different syntax, like when ANSI_QUOTES is defined. */ table->view->unit.print(buff, enum_query_type(QT_VIEW_INTERNAL | - QT_ITEM_ORIGINAL_FUNC_NULLIF)); + QT_ITEM_ORIGINAL_FUNC_NULLIF | + QT_NO_WRAPPERS_FOR_TVC_IN_VIEW)); if (table->with_check != VIEW_CHECK_NONE) { diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc index d8e6770..71377d3 100644 --- a/sql/sql_tvc.cc +++ b/sql/sql_tvc.cc @@ -673,6 +673,7 @@ st_select_lex *wrap_tvc(THD *thd, st_select_lex *tvc_sl, wrapper_sl->parent_lex= lex; /* Used in init_query. */ wrapper_sl->init_query(); wrapper_sl->init_select(); + wrapper_sl->is_tvc_wrapper= true; wrapper_sl->nest_level= tvc_sl->nest_level; wrapper_sl->parsing_place= tvc_sl->parsing_place; diff --git a/sql/sql_view.cc b/sql/sql_view.cc index 1efbc68..54debf9 100644 --- a/sql/sql_view.cc +++ b/sql/sql_view.cc @@ -956,10 +956,12 @@ static int mysql_register_view(THD *thd, TABLE_LIST *view, thd->variables.sql_mode&= ~MODE_ANSI_QUOTES; lex->unit.print(&view_query, enum_query_type(QT_VIEW_INTERNAL | - QT_ITEM_ORIGINAL_FUNC_NULLIF)); + QT_ITEM_ORIGINAL_FUNC_NULLIF | + QT_NO_WRAPPERS_FOR_TVC_IN_VIEW)); lex->unit.print(&is_query, enum_query_type(QT_TO_SYSTEM_CHARSET | QT_WITHOUT_INTRODUCERS | - QT_ITEM_ORIGINAL_FUNC_NULLIF)); + QT_ITEM_ORIGINAL_FUNC_NULLIF | + QT_NO_WRAPPERS_FOR_TVC_IN_VIEW)); thd->variables.sql_mode|= sql_mode; }