"select dt.b AS b from (select v2.b + 200 AS b from (((((((v1 s1 left join v1 s2 on(s2.a = s1.a)) left join v1 s3 on(s3.a = s2.a)) left join v1 s4 on(s4.a = s3.a)) left join v1 s5 on(s5.a = s4.a)) left join v1 s6 on(s6.a = s5.a)) left join v1 s7 on(s7.a = s6.a)) left join v2 on(v2.a = s7.a)) where s1.a > 1) dt"

"select t9.b + 100 + 200 AS b 

from (select t9.b + 100 + 200 AS b 

      from (((((((v1 s1 left join v1 s2 on(t8.a = t8.a)) left join v1 s3 on(t8.a = t8.a)) left join v1 s4 on(t8.a = t8.a)) left join v1 s5 on(t8.a = t8.a)) left join v1 s6 on(t8.a = t8.a)) left join v1 s7 on(t8.a = t8.a)) left join v2 on(t8.a" = t8.a)) where t8.a > 1) dt"

    /*

      Check whether there is enough free bits in table map to merge subquery.

      If not - materialize it. This check isn't cached so when there is a big

      and small subqueries, and the bigger one can't be merged it wouldn't

      block the smaller one.

    */

    if (parent_lex->get_free_table_map(&map, &tablenr) ||

       dt_select->leaf_tables.elements + tablenr > MAX_TABLES)

    {

      /* There is no enough table bits, fall back to materialization. */

      cause= "Not enough table bits to merge subquery";

      goto unconditional_materialization;

    }

unconditional_materialization:

  derived->change_refs_to_fields();

  derived->set_materialized_derived();

  if (!derived->table || !derived->table->is_created())

    res= mysql_derived_create(thd, lex, derived);

  goto exit_merge;

"select v2.b + 200 AS b from (select v2.b + 200 AS b from (((((((((((((((t1 left join t2 on(t2.a = t1.a)) left join t3 on(t3.a = t2.a)) left join t4 on(t4.a = t3.a)) left join t5 on(t5.a = t4.a)) left join t6 on(t6.a = t5.a)) left join t7 on(t7.a = t6.a)) left join t8 on(t8.a = t7.a))) left join (((("

  if (select_lex->first_cond_optimization)

  {

    //Do it only for the first execution

    /* Merge all mergeable derived tables/views in this SELECT. */

    if (select_lex->handle_derived(thd->lex, DT_MERGE))

      DBUG_RETURN(TRUE);  

  }

"select t9.b + 100 + 200 AS b 

from (select t9.b + 100 + 200 AS b 

      from (((((((v1 s1 left join v1 s2 on(t8.a = t8.a)) left join v1 s3 on(t8.a = t8.a)) left join v1 s4 on(t8.a = t8.a)) left join v1 s5 on(t8.a = t8.a)) left join v1 s6 on(t8.a = t8.a)) left join v1 s7 on(t8.a = t8.a)) left join v2 on(t8.a" = t8.a)) where t8.a > 1) dt"

commit 69bd5437c45490a53f075f8ac17744a3d9b31ccd (HEAD -> bb-10.4-MDEV-33081)

Author: Oleg Smirnov <olernov@gmail.com>

Date:   Sat Jan 13 17:32:16 2024 +0700

    MDEV-33081 Crash on second execution of PS for query over many views

    On the preparation phase a view or derived table can be marked as mergeable,

    and the appropriate field_translation table will be set up for it.

    But the merging itself may fail due to lack of free bits in table map

    (see mysql_derived_merge()). In such a case the merging is cancelled,

    and the view/DT falls back to materialization. But the field_translation

    table persists, and at 2nd execution of a prepared statement the name

    resolution procedure resolves names incorrectly using the outdated

    field_translation table.

    The solution is to reset field_translation to NULL if the merging

    of a DT/view fails. However, there are two exceptions

    when field_translation is used later at the execution phase:

     - pushdown of a DT to an external storage engine

     - statements looking like

       INSERT INTO t1 (SELECT tmp.a FROM (select * FROM t1) as tmp)

    In such cases field_translation must not be reset

# <...  this part is unchanged ...>

CREATE VIEW v2 AS

SELECT t8.a as b, t9.b as a

FROM t1

     LEFT JOIN t2 ON t2.a = t1.a

     LEFT JOIN t3 ON t3.a = t2.a

     LEFT JOIN t8 ON t8.a = t3.a

     LEFT JOIN t9 ON t9.a = t8.a;

SET optimizer_switch = 'table_elimination=off';

let $q=

SELECT dt.b FROM

(

SELECT v2.b + 200 as b

FROM v1 AS s1

     LEFT JOIN v1 AS s2 ON s2.a = s1.a

     LEFT JOIN v1 AS s3 ON s3.a = s2.a

     LEFT JOIN v1 AS s4 ON s4.a = s3.a

     LEFT JOIN v1 AS s5 ON s5.a = s4.a

     LEFT JOIN v1 AS s6 ON s6.a = s5.a

     LEFT JOIN v1 AS s7 ON s7.a = s6.a

     LEFT JOIN v2 ON v2.a = s7.a

WHERE s1.a > 1

) dt;

--echo # Sample result (regular statement)

eval $q;

eval PREPARE stmt FROM "$q";

EXECUTE stmt;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

static void mark_as_eliminated(JOIN *join, TABLE_LIST *tbl,  Json_writer_array* trace_eliminate_tables)

bool Item_subselect::mark_as_eliminated_processor(void *arg)