When we set merge_underlying_list. derived→table is set to t1 in case of 'update (select a from t1) d set a=0'. But what till now happened was that table was set to 'd' instead of 't1' and at the end of execution of query those tmp tables were dropped. If we allow this drop to happen, it will drop t1 instead of a tmp table. Check close_thread_tables()

Lets see how views handle this. [mysql_create_view]

My thoughts: If a derived table is merged, we should not even create a temp table. So if we have merge underlying tables then we should just prepare the unit like mysql_create view.

Proof of Concept

Above changes also fixes

// This is check will be added in the main branch in a more natural way
// but right now this is only a proof of concept inside mysql_derived_prepare:
if (!unit->prepared && derived->merge_underlying_list)
    {
      lex->context_analysis_only|= CONTEXT_ANALYSIS_ONLY_DERIVED;
      if (unit->prepare(derived,0,0))
        goto exit;
      lex->context_analysis_only&= ~CONTEXT_ANALYSIS_ONLY_DERIVED;

      // Create field translations
      if (derived->init_derived(thd, FALSE))
        goto exit;
      
    }

NEXT ISSUE:

// File: table.cc
const char *Field_iterator_table_ref::get_db_name()
6796 {
6797   if (table_ref->view)
6798     return table_ref->view_db.str;
6799   else if (table_ref->is_natural_join)
6800     return natural_join_it.column_ref()->safe_db_name();
6801 
6802   /*
6803     Test that TABLE_LIST::db is the same as TABLE_SHARE::db to
6804     ensure consistency. An exception are I_S schema tables, which
6805     are inconsistent in this respect.
6806   */
6807   DBUG_ASSERT(!cmp(&table_ref->db, &table_ref->table->s->db) ||
6808               (table_ref->schema_table &&
6809                is_infoschema_db(&table_ref->table->s->db)));
6810 
6811   return table_ref->db.str;
6812 }

Example used: 'UPDATE (select * from (select * from t1) x) d set a=0'

Assertion fails because table_ref→db contains an empty string. This happens due to the fact that db field is never for set for derived tables at the parsing phase. Before making any further changes, this needs more investigation.

Follow this tomorrow from mysql_create_view especially 'prepare' part:

create view v4 as (select * from (select * from t1) x);

Then follow update one from mysql_derived_prepare.

TODO:

→ Making is_mergeable() property of a unit instead of a select_lex

→ Change updatability check inside mysql_derived_init to comply with complex cases. [Refer 11 June]

→ Update check_fields to check fields for derived tables. [Refer 11 June]

→ Make changes such that merge_underlying_list can take complex cases. [Refer 12 June]