Fixed the bug mdev-13107 and some similar unreported bugs.

The problems were in the code of sql_show.cc. There the tables
could be opened in such a way that mysql_derived_init() never
worked for CTE tables. As a result they were not marked as
derived and mysql_handle_derived() were not called for derived
tables used in their specifications.

3 files changed, 69 insertions, 3 deletions

diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result
index de681edf683..5a5cd0bdc8e 100644
--- a/mysql-test/r/cte_nonrecursive.result
+++ b/mysql-test/r/cte_nonrecursive.result
@@ -1002,3 +1002,36 @@ f
 2
 DROP TABLE cte;
 DROP TABLE t;
+#
+# MDEV-13107: SHOW TABLE STATUS, SHOW CREATE VIEW 
+#             for CTEs that use derived tables
+#
+create table t1(a int) engine=myisam;
+insert into t1 values (3), (1), (2);
+create table t2 (b int) engine=myisam;
+insert into t2 values (2), (10);
+create view v1 as
+with t as (select s.a from (select t1.a from t1) s),
+r as(select t.a from t2, t where t2.b=t.a)
+select a from r;
+create view v2 as
+with t as (select s.a from (select t1.a from t1) s),
+r as(select t.a from t2, t where t2.b=t.a)
+select a from t1;
+show table status;
+show create view v1;
+View	Create View	character_set_client	collation_connection
+v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `s`.`a` AS `a` from (select `test`.`t1`.`a` AS `a` from `test`.`t1`) `s`), r as (select `t`.`a` AS `a` from (`test`.`t2` join `t`) where `test`.`t2`.`b` = `t`.`a`)select `r`.`a` AS `a` from `r`	latin1	latin1_swedish_ci
+show create view v2;
+View	Create View	character_set_client	collation_connection
+v2	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS with t as (select `s`.`a` AS `a` from (select `test`.`t1`.`a` AS `a` from `test`.`t1`) `s`), r as (select `t`.`a` AS `a` from (`test`.`t2` join `t`) where `test`.`t2`.`b` = `t`.`a`)select `test`.`t1`.`a` AS `a` from `test`.`t1`	latin1	latin1_swedish_ci
+select * from v1;
+a
+2
+select * from v2;
+a
+3
+1
+2
+drop view v1,v2;
+drop table t1,t2;
diff --git a/mysql-test/t/cte_nonrecursive.test b/mysql-test/t/cte_nonrecursive.test
index 0cc63104fa5..361ab8de28e 100644
--- a/mysql-test/t/cte_nonrecursive.test
+++ b/mysql-test/t/cte_nonrecursive.test
@@ -681,3 +681,36 @@ SELECT * FROM cte;
 
 DROP TABLE cte;
 DROP TABLE t;
+
+--echo #
+--echo # MDEV-13107: SHOW TABLE STATUS, SHOW CREATE VIEW 
+--echo #             for CTEs that use derived tables
+--echo #
+
+create table t1(a int) engine=myisam;
+insert into t1 values (3), (1), (2);
+create table t2 (b int) engine=myisam;
+insert into t2 values (2), (10);
+
+create view v1 as
+with t as (select s.a from (select t1.a from t1) s),
+     r as(select t.a from t2, t where t2.b=t.a)
+ select a from r;
+
+create view v2 as
+with t as (select s.a from (select t1.a from t1) s),
+     r as(select t.a from t2, t where t2.b=t.a)
+ select a from t1;
+
+--disable_result_log
+show table status;
+--enable_result_log
+
+show create view v1;
+show create view v2;
+
+select * from v1;
+select * from v2;
+
+drop view v1,v2;
+drop table t1,t2;
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index bfeac981a93..1bc2321c1ca 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -1163,7 +1163,7 @@ mysqld_show_create_get_fields(THD *thd, TABLE_LIST *table_list,
     bool open_error=
       open_tables(thd, &table_list, &counter,
                   MYSQL_OPEN_FORCE_SHARED_HIGH_PRIO_MDL) ||
-                  mysql_handle_derived(thd->lex, DT_PREPARE);
+      mysql_handle_derived(thd->lex, DT_INIT | DT_PREPARE);
     thd->pop_internal_handler();
     if (open_error && (thd->killed || thd->is_error()))
       goto exit;
@@ -1416,7 +1416,7 @@ mysqld_list_fields(THD *thd, TABLE_LIST *table_list, const char *wild)
 
   if (open_normal_and_derived_tables(thd, table_list,
                                      MYSQL_OPEN_FORCE_SHARED_HIGH_PRIO_MDL,
-                                     DT_PREPARE | DT_CREATE))
+                                     DT_INIT | DT_PREPARE | DT_CREATE))
     DBUG_VOID_RETURN;
   table= table_list->table;
 
@@ -4274,7 +4274,7 @@ fill_schema_table_by_open(THD *thd, bool is_show_fields_or_keys,
                                            MYSQL_OPEN_FORCE_SHARED_HIGH_PRIO_MDL |
                                            (can_deadlock ?
                                             MYSQL_OPEN_FAIL_ON_MDL_CONFLICT : 0)),
-                                          DT_PREPARE | DT_CREATE));
+					  DT_INIT | DT_PREPARE | DT_CREATE));
   /*
     Restore old value of sql_command back as it is being looked at in
     process_table() function.