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.

3. Implementation

3.1 Implementation - user view

A basic example. Setup the data on the backend:

create database j1;

use j1;

create table t1(

  pk int primary key,

  a int

);

insert into t1 select seq,seq from seq_1_to_10;

create table t2(

  pk int primary key,

  a int

);

insert into t2 select seq,seq from seq_1_to_10;

Then, set up the frontend:

create database j1;

CREATE TABLE t1 (

  pk int primary key,

  a int

) ENGINE="FEDERATED" CONNECTION='mysql://root@127.0.0.1:3320/j1/t1';

CREATE TABLE t2 (

  pk int primary key,

  a int

) ENGINE="FEDERATED" CONNECTION='mysql://root@127.0.0.1:3320/j1/t2';

Run EXPLAIN UPDATE to see how UPDATE would run:

MariaDB [j1]> set global federated_pushdown=ON; 

Query OK, 0 rows affected (0.001 sec)

MariaDB [j1]> explain update t1,t2 set t1.a=t1.a+1 where t1.pk=t2.pk;

+------+---------------+-------+------+---------------+------+---------+------+------+-------+

| id   | select_type   | table | type | possible_keys | key  | key_len | ref  | rows | Extra |

+------+---------------+-------+------+---------------+------+---------+------+------+-------+

|    1 | PUSHED UPDATE | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | NULL  |

+------+---------------+-------+------+---------------+------+---------+------+------+-------+

1 row in set (38.455 sec)

Yes, everything will be pushed down.
Run the UPDATE itself and note the matched/changed row counts (received from the backend):

MariaDB [j1]>  update t1,t2 set t1.a=t1.a+1 where t1.pk=t2.pk;

Query OK, 10 rows affected (9 min 51.329 sec)

Rows matched: 10  Changed: 10  Warnings: 0

3.2 Implementation - under the hood

Applicability check

class handlerton has a new member:

  /*

    Create and return a multi_upddel_handler that performs a whole

    multi-table UPDATE/DELETE. If the storage engine cannot execute the

    statement, return NULL

  */

  multi_upddel_handler *(*create_multi_upddel)(THD *thd,

                                               SELECT_LEX *select_lex);

Here, the server should check applicability criteria and if the whole statement can be executed on the backend,
return an object that inherits from class multi_upddel_handler.
The object must implements one method to perform the updates/deletes:

class multi_upddel_handler ...

  ...

  /*

    Perform the whole statement and report how many rows matched the WHERE

    clause (*found_rows) and how many rows were really changed or deleted

    (*affected_rows).

    This is called by execute().

    @retval 0        ok

    @retval != 0     error code

  */

  virtual int batch_update_delete(ha_rows *found_rows,

                                  ha_rows *affected_rows) = 0;

};

Then, during statement execution, the server will call

$RETURNED_HANDLER->batch_update_delete(&found_rows, &updated_rows);

to actually do the updates or deletes.