1. The original request

Currently direct_update_rows() / direct_delete_rows() handler calls are limited to single-table statements. Multi-table UPDATE/DELETE bypasses this path and is fully executed by the SQL layer, preventing storage engines from optimizing execution.
Extend the API to allow engines to handle multi-table DML pushdown.
Example:

UPDATE orders o

  JOIN customers c ON o.customer_id = c.id

SET o.status = 'active'

WHERE c.region = 'EU';

2. What is already available

Currently available APIs

Single-table direct UPDATE

The API calls made are:

  h->cond_push(select->cond);

  h->info_push(INFO_KIND_UPDATE_FIELDS, fields);

  h->info_push(INFO_KIND_UPDATE_VALUES, values);

  h->direct_update_rows_init(fields);

  h->ha_direct_update_rows(&update_rows, &found_rows);

The SQL layer also makes multiple checks about whether Direct UPDATE can be used for the statement:

• no RBR
• virtual columns are not used
• this is not UPDATE IGNORE
• etc.

Single-table direct DELETE

The API calls made are:

  h->cond_push(select->cond);

  h->direct_delete_rows_init();

  h->ha_direct_delete_rows(&deleted);

There are also many limitations about when direct DELETE can be used. They are checked at the SQL layer.

The Select handler

This is for offloading the entire query (or portions of it if the top-level operation is a UNION).

Store Engine's handlerton has methods to create a select_handler:

select_handler *(*create_select)(THD*, SELECT_LEX*, SELECT_LEX_UNIT*);

select_handler *(*create_unit)(THD*, SELECT_LEX_UNIT*);

The select_handler has methods which produce the rows:

class select_handler

{

public:

  virtual int init_scan() = 0;

  virtual int next_row() = 0;

  virtual int end_scan() = 0;

}

Search for select_handler is done right before JOIN::optimize:
handle_select() calls mysql_select() which does this:

    ...

    (err= join->prepare(tables, conds, og_num, order, false, group, having,

                        proc_param, select_lex, unit)))

   ...

  /* Look for a table owned by an engine with the select_handler interface */

  select_lex->pushdown_select= find_single_select_handler(thd, select_lex);

  if ((err= join->optimize()))

Note that multi-table UPDATE/DELETE does search for select handler now:

bool Sql_cmd_dml::execute_inner(THD *thd)

{

  SELECT_LEX_UNIT *unit = &lex->unit;

  SELECT_LEX *select_lex= unit->first_select();

  JOIN *join= select_lex->join;

  // look for select_handler provided by engines

  select_lex->pushdown_select= find_single_select_handler(thd, select_lex);

Execution of select_handler

JOIN::exec_inner() has this:

  else if (select_lex->pushdown_select)

  {

    /* Execute the query pushed into a foreign engine */

    error= select_lex->pushdown_select->execute();

    DBUG_RETURN(error);

  }

select_handler::execute() is a non-virtual function which has a row read loop:

  init_scan();

  ...

  send_result_set_metadata();

 ...

  while (!(err= next_row()))

  {

    ...

  }

  end_scan();

How Multi-table DELETE works currently.

It runs as a SELECT, but JOIN::result points to a multi_delete object which which buffers and/or applies the changes.
The operation is finalized in multi_delete::send_eof() which does things like:

  if (thd->transaction->stmt.modified_non_trans_table)

    thd->transaction->all.modified_non_trans_table= true;

  thd->transaction->all.m_unsafe_rollback_flags|=

    (thd->transaction->stmt.m_unsafe_rollback_flags & THD_TRANS::DID_WAIT);

  ...

    query_cache_invalidate3(thd, delete_tables, 1);

  ...

And eventually calls this to return result to the client:

  ::my_ok(thd, deleted);

3. Extending to handle multi-table UPDATE/DELETE

In multi-table UPDATE/DELETE, there is no "primary" ha_something object, so we can't call direct_update_rows_init or direct_delete_rows_init for it.

It looks like it's better to extend select_handler to also handle Multi-table UPDATEs/DELETEs.

Let the select handler object support two types (TODO: is there any point in keeping this in one object? Just so that there's only one handle for JOIN::exec_inner() to call?)

A. SELECT-TYPE. This is what it currently does.
B. UPDATE_DELETE-type.

For UPDATE_DELETE type, one should not call init_scan(), next_row(), end_scan().
Instead we will introduce a batch_update_delete function:

class select_handler {

...

  virtual int batch_update_delete(ha_rows *found_rows,  ha_rows *affected_rows) = 0;

...

SQL layer will call it (in select_handler::exec(), instead of the row-retrieval loop). The storage engine will implement it.

Pushdown capability detection.

The create_select function that storage engine publishes in handlerton will examine the LEX data structure.
It will check if the statement is a multi-table UPDATE/DELETE. If yes, it will collect all the required information (e.g. columns to UPDATE and what to UPDATE to, WHERE clause, tables, etc) and return an UPDATE_DELETE-type handler.

Execution.

select_handler::exec() will check if it is an UPDATE_DELETE-type select_handler object.
If yes it will call {{ this->batch_update_delete();}} which will perform the required operation.
Then, it will make appropriate calls that what multi_delete::send_eof() currently does: invalidate query cache, binlog the query, etc.

Testing

Implement this in ha_federatedx.
We can also use Optimizer Trace to show how one can enumerate/print needed details:

• Check which kind of statement this is
• for UPDATE, print which columns are updated to what expressions.

We need the above, because as far as I remember, federatedx just passes the query text to the backend. We need to demonstrate that a storage engine that actually analyzes the statement will be able to do so.