igor, can you please review bb-10.4-MDEV-31361-v2?

igor's objections against the solution proposed at bb-10.4-MDEV-31361-v2:
1. Renaming TABLE_LIST::init_derived() to TABLE_LIST::setup_derived(). Although the method is called multiple times, it is wrong and will be fixed some day, so the word "initialize" will not confuse a reader as it does now.
2. Default parameter in a newly introduced function void set_pushed_down(bool is_pushed_down= true).
3. Choosing derived->dt_handler via find_derived_handler() must be the last step in mysql_derived_prepare(), because an external engine has to see a completely prepared DT to evaluate its ability to handle it. However, my proposed solution exploited the determined dt_handler to make the decision for materialization instead of merging of the DT at the 2nd call to TABLE_LIST::init_derived(), so find_derived_handler() stopped being the last step of mysql_derived_prepare().

I can agree with 1 and 2 but not with 3, for the following reasons:
1. The DT is not completely prepared at the end of mysql_derived_prepare(), the changes might be introduced later, at mysql_derived_prepare() after the "exit:" label, mysql_derived_merge() or mysql_derived_optimize(). The external engine is already given wrong information that the DT has "mergeable" flag, so the context is not final anyway.
2. The current code of FederatedX (create_federatedx_derived_handler()) and ColumnStore (create_columnstore_derived_handler()) doesn't seem to be affected by the change, but some additional testing, of course, should be done with ColumnStore.

If I understood correctly, igor suggested implementing something like bb-10.4-MDEV-31361-v3 (see the commit). However, not only it doesn't fix the current issue but also breaks existing tests at federated.federatedx_create_handlers. Having spent more than one day trying to make that proof-of-concept work, I have no more ideas.

As psergei complained about not having of an analysis provided, here's my version (igor can correct or complement).
Let's look at the query:

SELECT * FROM

  (SELECT * FROM

    (SELECT * FROM

        (SELECT * FROM t1 where id>3) dt3

      WHERE id>3) dt2

  ) dt

Derived table dt3 is mergeable into dt2, but at the same time it is pushable, which implies it must be materialized (since an external engine is executing the SQL and fills the result table).
After execution of mysql_derived_init() and mysql_derived_prepare() dt3 is still mergeable and it has field_translation table set up. Merge/materialize flag is set at TABLE_LIST::init_derived() call.
However, at the last step of mysql_derived_prepare() a derived handler is chosen, so dt3->dt_handler is not NULL.
Switch to materialization of dt3 is performed at mysql_derived_merge():

  if (derived->dt_handler)

  {

    derived->change_refs_to_fields();

    derived->set_materialized_derived();

    DBUG_RETURN(FALSE);

  }

derived->change_refs_to_fields() effectively unsets the field translation previously made at TABLE_LIST::init_derived(), so the regular statement and first execution of the prepared statement completes successfully.
But mysql_derived_merge() is called only for the first execution:

  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);  

  }

So, during second and further executions there is no call to derived->change_refs_to_fields(), and the field translation table (set at TABLE_LIST::init_derived()) remains effective. This leads to reading incorrect results from wrong fields.

My solution proposed at bb-10.4-MDEV-31361-v2 is to move the call of TABLE_LIST::init_derived() at mysql_derived_prepare() down the code placing it after the selection of a derived handler. At the same time, it adjusts the logic of TABLE_LIST::init_derived() to take into account dt_handler and force setting the materialization flag for the derived table if dt_handler is not NULL. In this case the server does not construct the field translation table, and there is no need to roll it back later. So the second and further executions of the prepared statement work correctly.

Assigned it to myself

OK to push a8217d7213a0d8c6faebfb50bdc839a53c7cf21b

A fix for this bug was pushed into 10.5. It should be merged upstream as it is.