Exponential time of the second execution of PS/SP with nested derived tables or views

create table t1 (f1 int);

insert into t1 values (1),(2);

--disable_query_log

let $levels= 30;

let $q= select f1 as s1 from t1;

let $i= 1;

while ($i < $levels)

{

  let $j= $i;

  inc $j;

  let $q= select s$i as s$j from ($q) dt$i;

  inc $i;

}

--echo # direct execution: fast

eval $q;

eval prepare stmt from "$q";

--enable_query_log

--echo # first execution: fast

execute stmt;

--echo # second execution: exponential in the nesting depth

execute stmt;

deallocate prepare stmt;

--disable_query_log

eval create procedure p() $q;

--enable_query_log

call p();

--echo # second call: exponential as well

call p();

drop procedure p;

drop table t1;

create table t1 (f1 int);

insert into t1 values (1),(2);

prepare stmt from 'select s29 as s30 from (select s28 as s29 from (select s27 as s28 from (select s26 as s27 from (select s25 as s26 from (select s24 as s25 from (select s23 as s24 from (select s22 as s23 from (select s21 as s22 from (select s20 as s21 from (select s19 as s20 from (select s18 as s19 from (select s17 as s18 from (select s16 as s17 from (select s15 as s16 from (select s14 as s15 from (select s13 as s14 from (select s12 as s13 from (select s11 as s12 from (select s10 as s11 from (select s9 as s10 from (select s8 as s9 from (select s7 as s8 from (select s6 as s7 from (select s5 as s6 from (select s4 as s5 from (select s3 as s4 from (select s2 as s3 from (select s1 as s2 from (select f1 as s1 from t1) dt1) dt2) dt3) dt4) dt5) dt6) dt7) dt8) dt9) dt10) dt11) dt12) dt13) dt14) dt15) dt16) dt17) dt18) dt19) dt20) dt21) dt22) dt23) dt24) dt25) dt26) dt27) dt28) dt29';

execute stmt;  -- fast

execute stmt;  -- 6 s, doubles with each extra level

mysql_derived_prepare

mysql_handle_single_derived

TABLE_LIST::handle_derived

LEX::handle_list_of_derived

st_select_lex::handle_derived

mysql_derived_prepare

mysql_handle_single_derived

TABLE_LIST::handle_derived

... (repeats for every nesting level)

st_select_lex::handle_derived

JOIN::prepare

mysql_select

handle_select

execute_sqlcom_select

mysql_execute_command

Prepared_statement::execute