WL#9270: X Protocol Prepared Statement execution

Affects: Server-8.0   —   Status: Complete   —   Priority: Medium

Motivation
==========

Applications that repeatedly execute the same kind of statements with changed
values, currently have to go through same ``parse-execute`` cycle for every
execution.

In the case of the Document Store a large part of the queries are simple
``_id`` based lookups which can be accelerated by preparing the Crud once
and executing it often.

Goals
=====

Move SQL prepare functionality to X Protocol.
Give user a possibility to prepared and execute DevAPI CRUD (X Protocol CRUD).


Optional Goals
==============

Performance boost should be visible when the ``client-application`` is going
to switch from SQL/CRUD execution to prepared statement execution.


Notes
=====

Pipelining
----------

Prepared Statements allow to split off the ``parse`` phase from the ``execute``
into two steps and allow to repeatedly execute a statement with different
values without reparsing the statement all the time.

In the classic protocol this is solved by:

    C->S: COM_STMT_PREPARE(sql)
    C<-S: (stmt_id, arg_count)
    C->S: COM_STMT_EXECUTE(stmt_id, args, flags, ...)
    C<-S: (resultset)

As the EXECUTE has to wait for the response of the PREPARE to get access to the
server assigned ``statement-id`` using prepared-statements results in two
round-trips.

If prepared-statements are used as a performance feature the user has decide
upfront if round-trip time of PREPARE + EXECUTE is less than the time spent to
send just pipeline the statements and let the server parse them again and again.

As the xprotocol supports pipelining statements already, the prepared statements
MUST support pipelining too.
Design requirements
===================

DR1
:   user MUST be able to prepare and execute a SQL statement

DR2
:  user MUST be able to prepare and execute a CRUD statement

DR3
:  user MUST be able to pipeline a prepare and execute statement

DR4
:  user MUST be able to execute a earlier prepared statement

DR5
:  user MUST be able to close a prepared statement

DR6
:  resultset of ``execute`` MUST be the same as the resultset of a directly
   executed statement (Sql::StmtExecute, Crud::Find, ...)

DR7
:  user MUST be able to fetch a part of the resultset generated by
   prepared-statement execution

DR8
:  user MUST be able to cleanup the resourced allocated ``prepare``


Function requirements - Server
==============================

FR_S1
:  SQL accepted via the classic protocol for ``COM_STMT_PREPARE`` MUST be 
   accepted by the xprotocol implementation too

FR_S2
:  arguments specified in the ``execute`` MUST replace wild-cards specified
   the ``prepare``

FR_S3
:  ``execute`` MUST accept arguments

FR_S4
:  ``execute`` MUST return out-parameters of stored procedures

FR_S5
:  ``close`` MUST close a prepared statement and release its resources

FR_S6
:  closing a prepared-statement that has ``cursor`` assigned MUST close the
   ``cursor``

FR_S7
:  ``cursor`` MUST be able to open/execute a prepared statement

FR_S8
:  ``cursor`` MUST be able to fetch all resultsets referenced by a ``cursor``

FR_S9
:  ``cursor`` MUST be able to fetch a part of resultset/resultsets referenced
    by a ``cursor``

FR_S10
:  ``execute`` after a ``execute`` with a cursor MUST implicitly ``close``
   the cursor

FR_S11
:  preparing a statement with a statement-id already assigned MUST ``close``
   the old prepared statement

FR_S12
: closing X session should close all prepared-statements and ``cursors``

FR_S13
: reseting X session should close all prepared-statements and ``cursors``

FR_S14
: user should be able to use prepared-statements and ``cursors`` with
  expectations (for example open expect block with NO_ERROR)

error-cases
-----------

FR_SE1
:  executing a statement with a unassigned prepare-statement-id MUST fail

FR_SE2
:  closing a prepare-statement-id that's not in use MUST fail

FR_SE3
:  fetching from a ``cursor`` with unassigned cursor-id MUST fail

FR_SE4
:  closing a ``cursor`` with unassigned cursor-id MUST fail

FR_SE5
:  fetching from a ``cursor`` that reached end of data MUST fail

FR_SE6
:  preparing non SQL and non CRUD statement MUST fail


Function requirements - Client
==============================

FR_C1
:  libmysqlxclient MUST support ``prepare``, ``execute``, ``close`` prepare
   statement operations

FR_C2
:  libmysqlxclient MUST be able to ``fetch`` a subset of rows

FR_C3
:  libmysqlxclient MUST be able to operate on X Protocol messages directly

FR_C4
:  libmysqlxclient MUST be have API at session level to operate on prepared
   statements and cursors
Protocol
========

This worklog introduces two types of objects, prepared-statements and
cursors. Both are managed by X Protocol messages and referred by numerical IDs.
IDs are chosen by the client. Client must ensure that assigned IDs are reused
only when ID is not needed or deallocated explicitly before reuse.
Cursors and prepared-statement object in classic protocol are the same object,
still to make X Protocol more flexible it was separated.

Prepared-statements
-------------------

Multiple executions of same SQL statement is one of the expensive operations
that can be optimized in classic-protocol using prepared statements.
Adding prepared-statements to X Protocol gives additional gain in case
of CRUD operations. When a CRUD operation is execute, X Plugin generates
SQL from protobuf messages and the SQL is parsed by the server, thus the query
is created and parsed each time before execution.

Mysqlx.Prepare.Prepare
----------------------

Instead of generating the query and parsing it on the server side multiple
times, user can do it just once and cache the result. Later on the cached query
can be executed just by referring to its ID.
SQL or CRUD statement can be prepared on X Protocol using following message:


    message Prepare {
      required uint32 stmt_id = 1;
    
      // Determine which of optional fields was set by the client
      // (Workaround for missing "oneof" keyword in pb2.5)
      enum OneOfMessageType {
        FIND = 0;
        INSERT = 1;
        UPDATE = 2;
        DELETE = 4;
        STMT = 5;
      }
    
      message OneOfMessage {
        required OneOfMessageType type = 1 /* OneOfMessageType */;
    
        optional Mysqlx.Crud.Find find = 2;
        optional Mysqlx.Crud.Insert insert = 3;
        optional Mysqlx.Crud.Update update = 4;
        optional Mysqlx.Crud.Delete delete = 5;
        optional Mysqlx.Sql.StmtExecute stmt_execute = 6;
      }
    
      required OneOfMessage stmt = 2;
    
      option (client_message_id) = PREPARE_PREPARE; // comment_out_if PROTOBUF_LITE
    }


* ``stmt_id`` - ID which is going to represent the result of preparation,
                its value is chosen by client and its his responsibility
                to guarantee its uniqueness in current session. Using same
                value on already prepared statement deallocates old one.
* ``stmt`` - Statement which should be prepared, currently client can prepare
             SQL, Crud.Insert, Crud.Update, Crud.Find, Crud.Delete.
* ``stmt.type`` - Value defines which filed from OneOfMessage message (stmt
                  field) is valid. Possible values are define by
                  ``Prepare.OneOfMessageType``.

Flow with this message looks following:

    client->server: Mysqlx.Prepare.Prepare(stmt_id=1, stmt=...)
    alt Success
      client<-server: Mysqlx.Ok
    else
      client<-server: Mysqlx.Error(
        Error codes generated by the server|
        Error codes generated by X Plugin query generator)
    end


When preparing SQL, the rules are the same as in classic protocol, question
mark is a placeholder, which is going to be replaced by argument supplied in
``args`` of ``Mysqlx.Prepare.Execute`` message. Example of placeholders:

    SELECT SLEEP(?);

X Plugin generates SQL from CRUD messages, this means that user can't use
question marks like with SQL execution (StmtExecute). X Protocol already
supports placeholder inside CRUD. To use those placeholders, user must specify
and expression of type ``Mysqlx.Expr.Expr.PLACEHOLDER``, with index into
the ``args`` field of:

* Mysqlx.Crud.Find, args
* Mysqlx.Crud.Insert, args
* Mysqlx.Crud.Update, args
* Mysqlx.Crud.Delete, args

When preparing one of those messages, placeholders with indexes that reach
outside the ``args`` field, must be translated question mark (SQL placeholder).

Those not resolved placeholder indexes must be translated in a way, that they
point to array elements supplied by user in ``args`` field of
``Mysqlx.Prepare.Execute`` message.

For example, lets assume that user prepared following:

    Mysqlx.Prepare.Prepare {
      stmt_id:1
      stmt {
        type: FIND
        find {
          collection {name:"coll"}

          projection { type: PLACEHOLDER postion: 3 }
          projection { type: PLACEHOLDER postion: 1 }
          projection { type: PLACEHOLDER postion: 1 }
          projection { type: PLACEHOLDER postion: 2 }
          projection { type: PLACEHOLDER postion: 0 }
          projection { type: PLACEHOLDER postion: 3 }
          
          args {string:"A"}
          args {string:"B"}
        }
      }
    }

Which is going to generate following SQL:

    SELECT ?, "B", "B", ?, "A", ? FROM coll;

X Plugin must remember the indexes that user specified for those question
marks:

    SELECT ?, /* placeholder_index:3 */,
          "B",
          "B",
          ?, /* placeholder_index:2 */,
          "A",
          ?, /* placeholder_index:3 */,
          FROM coll;

Missing data will be filled using binded data from field ``args`` in
``Mysqlx.Prepare.Execute`` message. X Plugin will assume that data from
Execute/args are appedned to the end of Find/args. This can be interpreted
other way, by referencing Execute/args directly. To do so each index must
be substracted by number of elements in ``Find.args`` field:

    SELECT ?, /* placeholder_index:1 */,
          "B",
          "B",
          ?, /* placeholder_index:0 */,
          "A",
          ?, /* placeholder_index:1 */,
          FROM coll;

When user will execute the prepared Find (SQL above), using following message:

    message Execute {
      stmt_id: 1;
    
      args { string:"X"}
      args { string:"Y"}
    }

X Plugin must build following array of binded arguments from the prepared
statement:

    ["Y", "X", "Y"]

SQL allows to use placeholders inside the LIMIT statement, still X Protocol
CRUD messages use following definition which doesn't allow to do that:

    message Limit {
      required uint64 row_count = 1;
      optional uint64 offset = 2;
    }


To workaround the problem, another message must be added as `limit_expr` field
to following message: Mysqlx.Crud.Find, Mysqlx.Crud.Delete, Mysqlx.Crud.Update:

    message LimitExpr {
      required Mysqlx.Expr.Expr row_count = 1;
      optional Mysqlx.Expr.Expr offset = 2;
    }

The use of `limit` and `limit_expr` has following constraints:

* only of those fields can be set, in case when X Plugin received two field set
  it must return ER_X_BAD_MESSAGE.

* the `Expr` type, can describe a lot of different values. In case of
  `limit_expr`, X Plugin must only allow a Scalar/UINT value and
  Expr/PLACEHOLDER.


Mysqlx.Prepare.Execute
----------------------

Following message is responsible for execution of already prepared SQL or CRUD
statement:

    message Execute {
      required uint32 stmt_id = 1;
    
      repeated Mysqlx.Datatypes.Any args = 2;
      optional bool compact_metadata = 3 [ default = false ];
    
      option (client_message_id) = PREPARE_EXECUTE; // comment_out_if PROTOBUF_LITE
    }

* ``stmt_id`` - ID representing statement prepared with
                ``Mysqlx.Prepare.Prepare``.
* ``args`` - Arguments to insert in place of placeholders defined at
             preparation. X Plugin must support only scalar arguments. Not all
             arguments must be used when executing, still all dependencies from
             Mysqlx.Expr.Expr.PLACEHOLDER must be satisfied.
* ``compact_metadata`` - Defines if that only type information should be send
                         in `Mysqlx.Resultset::ColumnMetadata`, skipping names.

Flow with this message looks following:

    client->server: Mysqlx.Prepare.Execute(stmt_id=1, args=...)
    alt Success
      client<-server: ...Resultset...
      client<-server: Mysqlx.Sql.StmtExecuteOk
    else
      alt
        client<-server: ...Part of Resultset...
      end
      client<-server: Mysqlx.Error(
        ER_X_BAD_STATEMENT_ID|
        ER_X_PREPARED_EXECUTE_ARGUMENT_NOT_SUPPORTED|
        ER_X_PREPARED_EXECUTE_ARGUMENT_CONSISTENCY|
        Error codes generated by the server)
    end

Error cases:

* return ER_X_BAD_STATEMENT_ID when``stmt_id`` is not assigned to
  prepared-statement in context of current session.
* return ER_X_PREPARED_EXECUTE_ARGUMENT_NOT_SUPPORTED when value in ``args``
  field is not supported by X Plugin, only Scalar types must be supported.
* return ER_X_PREPARED_EXECUTE_ARGUMENT_CONSISTENCY when placeholder index
  points outside ``args`` field boundaries.

In case when user executes a prepared ``Mysqlx.Curd.Insert``, then
all generated document ID must be returned to the client in notices.

Executing a prepared-statement that is in use by a cursor, must close the
cursor:

    client->server: Cursor.Open(cursor_id=1, stmt_id=10)
    client<-server: Ok
    client->server: Prepared.Execute(stmt_id=10)
    .. resultsets ..
    client<-server: StmtExecuteOk
    client->server: Cursor.Fetch(cursor_id=1)
    client<-server: Error (ER_X_BAD_CURSOR_ID)

Mysqlx.Prepare.Deallocate
-------------------------

Following message is responsible for deleting prepared statement and it
marks the ``stmt_id`` as free:

    message Deallocate {
      required uint32 stmt_id = 1;
    
      option (client_message_id) = PREPARE_DEALLOCATE; // comment_out_if PROTOBUF_LITE
    }

* ``stmt_id`` - ID representing statement prepared with
                ``Mysqlx.Prepare.Prepare``.

Flow with this message look following:

    client->server: Mysqlx.Prepare.Deallocate(stmt_id=1)
    alt Success
      client<-server: Mysqlx.Ok
    else
      client<-server: Mysqlx.Error(
        ER_X_BAD_STATEMENT_ID|
        Error codes generated by the server)
    end

Error cases:

* return ER_X_BAD_STATEMENT_ID when``stmt_id`` is not assigned to
  prepared-statement in context of current session.

Deallocating a prepared-statement that is in use by a cursor, must close then
cursor:

    client->server: Cursor.Open(cursor_id=1, stmt_id=10)
    client<-server: Ok
    client->server: Prepared.Deallocate(stmt_id=10)
    client<-server: Ok
    client->server: Cursor.Fetch(cursor_id=1)
    client<-server: Error (ER_X_BAD_CURSOR_ID)

Cursors
-------

Cursors were added to X Protocol, to give user possibility to fetch resultsets
in small chunks using iterator like behavior.

Client application can manage cursors using:

* create/open cursor by sending ``Mysqlx.Cursor.Open``
* fetch data from cursor by sending ``Mysqlx.Cursor.Fetch``
* close cursor by sending ``Mysqlx.Cursor.Close``

Cursors divide the flow of resultsets on multiple parts, each part is
transfered after execution of ``open``, ``fetch``. Information that
the resultsets were broken on parts can be determined by looking at
the last message of the result, which can be either ``FetchSuspended``
or ``FetchDone``. The first fetched resultset (when opening) must
contain meta-data.

Not all statements may be fetched using cursors, an example of this case
is a call to "dynamic SQL" (stored procedure call). ``open`` instead of
returning meta-data, it is going to return full resultset/resultsets.
Further attempts to fetch such cursor are going to end with error pointing
that there are no more data inside the cursor.

Mysqlx.Cursor.Open
------------------

Following message is responsible for opening a cursor for a given statement.
The cursor executes the statement and instead of streaming the result to client
it suspends it until client asks for more data.
Currently cursor can execute only prepared statements, still the message is
open for future changes.
Client must provide an ID (``cursor_id``) which the X Plugin is going to use
as cursor identification:

    message Open {
      required uint32 cursor_id = 1;
    
      message OneOfMessage {
        enum Type {
          PREPARE_EXECUTE = 0;
        }
        required Type type = 1 /* OneOfMessageType */;
    
        optional Mysqlx.Prepare.Execute prepare_execute = 2;
      }
    
      required OneOfMessage stmt = 4;
      optional uint64 fetch_rows = 5;
    
      option (client_message_id) = CURSOR_OPEN; // comment_out_if PROTOBUF_LITE
    }

* ``cursor_id`` - ID which is going to represent the newly created cursor,
                  its value is chosen by client and its his responsibility
                  to guarantee its uniqueness in current session. If client
                  specified already assigned ID then old cursor is going to be
                  closed.
* ``stmt`` - Statement which is going to be executed and its resultset is going
             to be streamed to client application using cursor. In case when
             ``stmt_id`` is already opened by other cursor, it is going to be
             closed and reopened in new cursor.
* ``fetch_rows`` - defines how many rows of the resultset should be send to
                   the client. For dynamic SQL this argument is going to be
                   ignored and whole resultset/resultsets are going to be
                   returned. In case when client didn't set it, its equivalent
                   to setting it to zero (fetch nothing).

Flow with this message looks following:

    client->server: Mysqlx.Cursor.Open(cursor_id=1, stmt={stmt_id=1})
    alt
      client<-server: Resultsets
    end
    alt Success
      client<-server: StmtExecuteOk
    else
      client<-server: Mysqlx.Error(
        ER_X_BAD_STATEMENT_ID|
        ER_X_PREPARED_EXECUTE_ARGUMENT_NOT_SUPPORTED|
        ER_X_PREPARED_EXECUTE_ARGUMENT_CONSISTENCY|
        Error codes generated by the server)
    end

Error cases:

* return ER_X_BAD_STATEMENT_ID when stmt_id does not refer to an existing
  prepared statement.
* return ER_X_PREPARED_EXECUTE_ARGUMENT_NOT_SUPPORTED when value in
  ``Cursor.stmt.prepare_execute.args`` field is not supported by X Plugin,
  only Scalar types must be supported.

The flow for opening a cursor is quite generic, there are several different
scenarios that can occur:

Case 1: Cursor was created and user didn't request any data to be fetched

     client->server: Mysqlx.Cursor.Open(cursor_id=1, stmt={stmt_id=1})
     client<-server: Mysqlx.Resultset.ColumnMetaData...
     client<-server: Mysqlx.Resultset.FetchSuspended
     client<-server: Mysqlx.Sql.StmtExecuteOk

  User can interpret ``FetchSuspended`` messages as information that cursor is
  open and valid.

Case 2: Cursor was created and user requested more data than the resultset has:

    client->server: Mysqlx.Cursor.Open(cursor_id=1, stmt={stmt_id=1}, fetch_row=10)
    client<-server: Mysqlx.Resultset.ColumnMetaData...
    client<-server: Mysqlx.Resultset.Row
    client<-server: Mysqlx.Resultset.Row
    client<-server: Mysqlx.Resultset.FetchDone
    client<-server: Mysqlx.Sql.StmtExecuteOk

  User can interpret ``FetchDone`` messages as information that cursor is
  open still there are no more data in it.

Case 3: Cursor was created and user requested less data than the resultset has:

    client->server: Mysqlx.Cursor.Open(cursor_id=1, stmt={stmt_id=1}, fetch_row=1)
    client<-server: Mysqlx.Resultset.ColumnMetaData...
    client<-server: Mysqlx.Resultset.Row
    client<-server: Mysqlx.Resultset.FetchSuspended
    client<-server: Mysqlx.Sql.StmtExecuteOk

Case 4: Cursor was created, still its doesn't contains any data. Stmt was executed right away and doesn't have any resultset:

    client->server: Mysqlx.Cursor.Open(cursor_id=1, stmt={stmt_id=1})
    client<-server: Mysqlx.Resultset.FetchDone
    client<-server: Mysqlx.Sql.StmtExecuteOk

Case 5: Cursor was created, still fetching is not possible because stmt executed right away (probably because its was dynamic SQL):

    client->server: Mysqlx.Cursor.Open(cursor_id=1, stmt={stmt_id=1})
    client<-server: Mysqlx.Resultset.ColumnMetaData...
    client<-server: Mysqlx.Resultset.Row
    client<-server: Mysqlx.Resultset.Row
    client<-server: Mysqlx.Resultset.FetchDone
    client<-server: Mysqlx.Sql.StmtExecuteOk

There are even more combinations possible, still all are in align with resultset flow.

Mysqlx.Cursor.Fetch
-------------------

Following message is responsible for fetching a part or ``resultset`` from
a cursor:

    message Fetch {
      required uint32 cursor_id = 1;
      optional uint64 fetch_rows = 2;
    
      option (client_message_id) = CURSOR_FETCH; // comment_out_if PROTOBUF_LITE
    }

* ``cursor_id`` - ID which represents opened cursor
* ``fetch_rows`` - number of rows which should be fetched from the resultset,
                   in case when it was not set then rest of resultsets is going
                   to be returned.

Flow with this message looks following:

    client -> server: Fetch
    alt Success
      ... none or partial Resultsets or full Resultsets ...
      client <- server: StmtExecuteOk
    else
      client <- server: Error(ER_X_BAD_CURSOR_ID|..Error generated by server..)
    end

User can check if only a part of resultset/resultsets was transfered by looking
at message before StmtExecuteOk. In case when there is no data to be transfered
in the cursor then the message is going to be ``Mysqlx.Resultset.FetchDone``
or ``Mysqlx.Resultset.FetchDoneMoreResultsets`` (old behavior), in other case
the message is going to be ``Mysqlx.Resultset.FetchSuspended``.
Flow for transferring resultsets, looks following:

    loop has more resultsets or not at end of fetch
      group resultset
        loop has more columns
          server --> client: ColumnMetaData
        end
        loop has more rows
          server --> client: Row
        end
      end
      alt has more resultsets
        server --> client: FetchDoneMoreResultsets
      end
    end
    loop has more OUT-paramsets or not at end of fetch
      server --> client: FetchDoneMoreOutParams
      group resultset
        loop has more columns
          server --> client: ColumnMetaData
        end
        loop has more rows
          server --> client: Row
        end
      end
    end
    alt at end of all resultsets
      server --> client: FetchDone
    else cursor is opened
      server --> client: FetchSuspended
    end

After fetching whole resultset (reception of FetchDone), a cursor should be marked
that it has not data. Thus next ``Mysqlx.Cursor.Fetch`` must fail with an error.

Error cases:

* return ER_X_BAD_CURSOR_ID when client used cursor-id that was not opened.
* return ER_X_CURSOR_REACHED_EOF when all data were already fetched from
  cursor represented by cursor-id (server send FetchDone to mark the EOF).

Mysqlx.Cursor.Close
-------------------

Following message is responsible for closing the cursor, which deallocates
all data associated with the cursor:

    message Close {
      required uint32 cursor_id = 1;
    
      option (client_message_id) = CURSOR_CLOSE; // comment_out_if PROTOBUF_LITE
    }

* ``cursor_id`` - ID which represents opened cursor

Flow with this message look following:

    client -> server: Close
    alt Success
      client <- server: Ok
    else
      client <- server: Error(ER_X_BAD_CURSOR_ID|..Error generated by server..)
    end

Error cases:

* return ER_X_BAD_CURSOR_ID when client used cursor-id that was not opened.

Stored procedure output parameters
----------------------------------

X Protocol already defines a way of transferring stored procedures OUT and
INTOUT parameters in following description:

    loop has more resultsets
      group resultset
        loop has more columns
          server --> client: ColumnMetaData
        end
        loop has more rows
          server --> client: Row
        end
      end
      alt has more resultsets
        server --> client: FetchDoneMoreResultsets
      end
    end
    loop has more OUT-paramsets
      server --> client: FetchDoneMoreOutParams
      group resultset
        loop has more columns
          server --> client: ColumnMetaData
        end
        loop has more rows
          server --> client: Row
        end
      end
    end
    alt at end of all resultsets
      server --> client: FetchDone
    else cursor is opened
      server --> client: FetchSuspended
    end

Where the output parameters are transfered as a last resultset with single row
defining the values. The 'out-param' resultset begins after reception of
`FetchDoneMoreOutParams` followed by `ColumnMetaData `which defines types for
the row.

Assuming that user defined following procedure:

    CREATE PROCEDURE my_inc(INOUT inout_number INT)
    BEGIN
        SET inout_number=inout_number + 1;
    END$$

After preparing following statement:

    CALL my_inc(?)

and executing it with argument set to "1", it will generate following resultset:

    client->server: Mysqlx.Prepare.Execute(stmt_id=1, args=1)
    server->client: Mysqlx.Resultset.FetchDoneMoreOutParams
    server->client: Mysqlx.Resultset.ColumnMetaData(type=UINT)
    server->client: Mysqlx.Resultset.Row(field:2)
    server->client: Mysqlx.Resultset.FetchDone

Same behavior must be implemented in aspect of:

* Mysqlx.Prepare.Execute
* Mysqlx.Sql.StmtExecute
* Mysqlx.Cursor.Fetch

Pipelining
----------

Current protocol design doesn't allow to open a cursor or execute a prepared
statement from ``Mysqlx.Prepare.Prepare`` message. Still user can achieve the
same effect by pipelining two message, for example:

* ``Mysqlx.Prepare.Prepare`` with ``Mysqlx.Prepare.Execute``
* ``Mysqlx.Prepare.Prepare`` with ``Mysqlx.Cursor.Open``

This keeps the new protocol messages clean, by applying the "single
responsibility" principle and paying small price of around additional 7 bytes
(X header + X Message type + stmt_id) of network transfer.

Additionally user pipeline more X Protocol messages to block execution of
``Prepare.Execute`` message when "prepare fails", for example:

    client->server: Mysqlx.Expect.Open(no_error=true)
    client->server: Mysqlx.Prepare.Prepare(stmt_id=1, ...)
    client->server: Mysqlx.Prepare.Execute(stmt_id=1, ...)
    client->server: Mysqlx.Expect.Close()
    
    alt successful scenraio
      server->client: Mysqlx.Ok
      server->client: ... ResultSet ...
      server->client: StmtExecuteOk
      server->client: Mysqlx.Ok
      server->client: Mysqlx.Ok
    else error scenraio
      server->client: Mysqlx.Ok
      server->client: Mysqlx.Error(*prepare failed*)
      server->client: Mysqlx.Error(no-executed)
      server->client: Mysqlx.Error(no-executed)
    end


Reseting
========

Cursors and prepared-statements are part of the session, in case when client is
going to reset the session (by using ``Mysqlx.Session.Reset``) those objects
must be released.

Same applies to release of the session in case of connection closes, session is
responsible for releasing cursors and prepared-statements.


Instrumentation
===============

Status variables
----------------

* mysqlx_prep_prepare - number of prepared-statement messages received

|Property      |Value                              |
|--------------|-----------------------------------|
|Variable Name |mysqlx_prep_prepare                |
|Type          |LONGLONG                           |
|Scope         |GLOBAL/SESSION                     |
|Default       |0                                  |


* mysqlx_prep_execute - number of prepared-statement-execute messages received

|Property      |Value                              |
|--------------|-----------------------------------|
|Variable Name |mysqlx_prep_execute                |
|Type          |LONGLONG                           |
|Scope         |GLOBAL/SESSION                     |
|Default       |0                                  |

* mysqlx_prep_deallocate - number of prepared-statement-deallocate messages received

|Property      |Value                              |
|--------------|-----------------------------------|
|Variable Name |mysqlx_prep_deallocate             |
|Type          |LONGLONG                           |
|Scope         |GLOBAL/SESSION                     |
|Default       |0                                  |

* mysqlx_cursor_open - number of cursor-open messages received

|Property      |Value                              |
|--------------|-----------------------------------|
|Variable Name |mysqlx_cursor_open                 |
|Type          |LONGLONG                           |
|Scope         |GLOBAL/SESSION                     |
|Default       |0                                  |

* mysqlx_cursor_close - number of cursor-close messages received

|Property      |Value                              |
|--------------|-----------------------------------|
|Variable Name |mysqlx_cursor_close                |
|Type          |LONGLONG                           |
|Scope         |GLOBAL/SESSION                     |
|Default       |0                                  |

* mysqlx_cursor_fetch - number of cursor-fetch messages received

|Property      |Value                              |
|--------------|-----------------------------------|
|Variable Name |mysqlx_cursor_fetch                |
|Type          |LONGLONG                           |
|Scope         |GLOBAL/SESSION                     |
|Default       |0                                  |


Performance schema
------------------

Prepared statement handling must be observable through already existing infrastructure:
https://dev.mysql.com/doc/refman/8.0/en/prepared-statements-instances-table.html

This basically means that all prepared statements must have an entry in
"preapared_statements_instances" table and must be updated on every successful
"Prepare", "PrepareExecute", "PrepareDeallocate".

X Plugin session holds an association between prepared statement client side id
(``stmt_id``) and server side id. The problem is that performance schema
presents server ids. To be able to localize ``stmt_id`` in PS, user can
create a query that uses ``mysql_get_prepared_statment_id`` UDF for translating
the identifiers:

    SELECT * FROM performance_schema.prepared_statments_instances
      WHERE stmt_id=mysql_get_prepared_statment_id(1 /* client side id */);


Error and Warnings
==================

|Property   |Value                                                         |
|-----------|--------------------------------------------------------------|
|Name       |ER_X_BAD_STATEMENT_ID                                         |
|Error code |5110                                                          |
|Text       |Statement with ID=%i was not prepared.                        |
|Example    |Statement with ID=12 was not prepared.                        |


|Property   |Value                                                         |
|-----------|--------------------------------------------------------------|
|Name       |ER_X_BAD_CURSOR_ID                                            |
|Error code |5111                                                          |
|Text       |Cursor with ID=%i was not opened.                             |
|Example    |Cursor with ID=22 was not opened.                             |


|Property   |Value                                                         |
|-----------|--------------------------------------------------------------|
|Name       |ER_X_PREPARED_EXECUTE_ARGUMENT_NOT_SUPPORTED                  |
|Error code |TODO                                                          |
|Text       |Argument at index '%i' and of type '%s' is not supported      |
|           |for binding to prepared statement                             |


|Property   |Value                                                         |
|-----------|--------------------------------------------------------------|
|Name       |ER_X_PREPARED_EXECUTE_ARGUMENT_CONSISTENCY                    |
|Error code |TODO                                                          |
|Text       |There is no argument for statement placeholder at position: %i|


|Property   |Value                                                         |
|-----------|--------------------------------------------------------------|
|Name       |ER_X_CURSOR_REACHED_EOF                                       |
|Error code |TODO                                                          |
|Text       |No more data in cursor (cursor id:'%i')                       |


User defined function
=====================

X Protocol introduces its own IDs for prepared-statements, to make it work
with current performance-schema infrastructure, client must be able to map
the ID used by X Plugin with ID shown in performance schema, using following
UDF:

* mysqlx_get_prepared_statment_id(client_side_prepared_stmt_id) -
  returns server side prep-stmt-id, allocated by current session or NULL when
  conversion is not possible.

To make server side ID generation work correctly with prepared statements
the plugin must provide following function to embed it into generated
SQL queries:

* mysqlx_generate_document_id([offset INT, [increment INT,
  [generate_key BOOLEAN]]]) -
  generate unique document ``ID`` that can be used when user did provide
  a document without an ``ID``. In case when offset or increment were not
  provided then function is going to use "1" as default value. Last argument
  can be used to point if the function should generate the id or not, it is
  useful in case when on SQL level the decision is going to be made about
  inserting the ``ID``.


Mysqlx.Crud.Insert
==================

When user put a placeholder in place of the whole document, X Plugin can't
determine if document contains ID. This means X Plugin doesn't know if the ID
should be inserted or not.

The decision about inserting the ID was moved to SQL level which requires that
all those operations must translate the X Protocol message:

    client->server: Mysqlx.Prepare.Prepare(stmt_id:1 insert{projection{placeholder:0}})

in following way:

    INSERT INTO coll(doc) VALUES((SELECT JSON_INSERT(TMP1.doc, '$._id', CONVERT(
             mysqlx_generate_document_id(@@auto_increment_offset,
               @@auto_increment_increment, JSON_CONTAINS_PATH(TMP1.doc, 'one',
                 '$._id')) USING utf8mb4 FROM (SELECT ? as doc) as TMP1)))

optionally it can expand to:

    INSERT INTO coll(doc) VALUES(JSON_INSERT(?, '$._id', CONVERT(
             mysqlx_generate_document_id(@@auto_increment_offset, @@auto_increment_increment,
               JSON_CONTAINS_PATH(?, 'one', '$._id')) USING utf8mb4)))

where X Plugin must pass same bound argument multiple times to server.


Examples
========

Execute prepared statement

Statement ID can be allocated to one stmt, old one is deallocated
-----------------------------------------------------------------

    client->server: Mysqlx.Prepare.Prepare(stmt_id=1, stmt=%VALID%)
    client<-server: Mysqlx.Ok
    client->server: Mysqlx.Prepare.Prepare(stmt_id=1, stmt=%VALID%)
    client<-server: Mysqlx.Ok

Statement ID can be reused after deleting old prep-stmt
-------------------------------------------------------

    client->server: Mysqlx.Prepare.Prepare(stmt_id=1, stmt=%VALID%)
    client<-server: Mysqlx.Ok
    client->server: Mysqlx.Prepare.Deallocate(stmt_id=1)
    client<-server: Mysqlx.Ok
    client->server: Mysqlx.Prepare.Prepare(stmt_id=1, stmt=%VALID%)
    client<-server: Mysqlx.Ok

Preparing and executing simple SQL
----------------------------------

    client->server: Mysqlx.Prepare.Prepare(stmt_id=1, stmt=StmtExecute(stmt="SELECT NOW();"))
    client<-server: Mysqlx.Ok
    client->server: Mysqlx.Prepare.Execute(stmt_id=1)
    client<-server: ...Resultsets...
    client<-server: Mysqlx.Sql.StmtExecuteOk
    client->server: Mysqlx.Prepare.Deallocate(stmt_id=1)
    client<-server: Mysqlx.Ok

Opening same ID twice, implicitly closes old one
------------------------------------------------

    client->server: Mysqlx.Cursor.Open(cursor_id=1, stmt={stmt_id=1})
    client<-server: Mysqlx.Resultset.ColumnMetaData(...)
    client<-server: Mysqlx.Resultset.FetchSuspended()
    client<-server: Mysqlx.Sql.StmtExecuteOk
    
    client->server: Mysqlx.Cursor.Open(cursor_id=1, stmt={stmt_id=2})
    client<-server: Mysqlx.Resultset.ColumnMetaData(...)
    client<-server: Mysqlx.Resultset.FetchSuspended()
    client<-server: Mysqlx.Sql.StmtExecuteOk
    

Cursor ID can be used after closing old one
-------------------------------------------

    client->server: Mysqlx.Cursor.Open(cursor_id=1, stmt={stmt_id=1})
    client<-server: Mysqlx.Resultset.ColumnMetaData(...)
    client<-server: Mysqlx.Resultset.FetchSuspended()
    client<-server: Mysqlx.Sql.StmtExecuteOk
    client->server: Mysqlx.Cursor.Close(cursor_id=1)
    client<-server: Mysqlx.Ok
    client->server: Mysqlx.Cursor.Open(cursor_id=1, stmt={stmt_id=2})
    client<-server: Mysqlx.Resultset.ColumnMetaData(...)
    client<-server: Mysqlx.Resultset.FetchSuspended()
    client<-server: Mysqlx.Sql.StmtExecuteOk
    

Fetch '0' rows from cursor
--------------------------

Assuming that ``resultset`` has two columns:

    client->server: Mysqlx.Cursor.Open(cursor_id=1, stmt={stmt_id=1})
    client<-server: Mysqlx.Resultset.ColumnMetaData(...)
    client<-server: Mysqlx.Resultset.ColumnMetaData(...)
    client<-server: Mysqlx.Resultset.FetchSuspended()
    client<-server: Mysqlx.Sql.StmtExecuteOk
    
    client->server: Mysqlx.Cursor.Fetch(cursor_id=1, fetch_rows=0)
    client<-server: Mysqlx.Resultset.FetchSuspended()
    client<-server: Mysqlx.Sql.StmtExecuteOk

Fetch all rows from cursor
--------------------------

Assuming  that ``resultset`` has two columns:

    client->server: Mysqlx.Cursor.Open(cursor_id=1, stmt={stmt_id=1})
    client<-server: Mysqlx.Resultset.ColumnMetaData(...)
    client<-server: Mysqlx.Resultset.ColumnMetaData(...)
    client<-server: Mysqlx.Resultset.FetchSuspended()
    client<-server: Mysqlx.Sql.StmtExecuteOk
    
    client->server: Mysqlx.Cursor.Fetch(cursor_id=1)
    client<-server: ...Resultset rows...
    client<-server: Mysqlx.Resultset.FetchDone()
    client<-server: Mysqlx.Sql.StmtExecuteOk


Fetch same number of rows as there are in resultset
---------------------------------------------------

Assuming  that ``resultset`` has two columns and two rows:

    client->server: Mysqlx.Cursor.Open(cursor_id=1, stmt={stmt_id=1})
    client<-server: Mysqlx.Resultset.ColumnMetaData(...)
    client<-server: Mysqlx.Resultset.ColumnMetaData(...)
    client<-server: Mysqlx.Resultset.FetchSuspended()
    client<-server: Mysqlx.Sql.StmtExecuteOk
    
    client->server: Mysqlx.Cursor.Fetch(cursor_id=1, fetch_rows=2)
    client<-server: Mysqlx.Resultset.Row(...)
    client<-server: Mysqlx.Resultset.Row(...)
    client<-server: Mysqlx.Resultset.FetchSuspended()
    client<-server: Mysqlx.Sql.StmtExecuteOk

    client->server: Mysqlx.Cursor.Fetch(cursor_id=1, fetch_rows=1)
    client<-server: Mysqlx.Resultset.FetchDone()
    client<-server: Mysqlx.Sql.StmtExecuteOk
Srv_service requirements
========================

* To enable out-params on Srv_service implementation needs to set following
  flag: CLIENT_PS_MULTI_RESULTS.

* Opening a cursor for every query is not possible. In case when opening
  a cursor is not possible then the server streams the whole resultset to
  the user. X Protocols st_command_service_cbs must fail the streaming in
  such case.

* On the server side relation between cursor and prepared statement is
  one-to-one, still X Protocol allows multiple-to-one. The limitation
  must be handled on X Plugin side when the client tries to open
  a second cursor for the prepared statement.

* ``st_command_service_cbs`` gives the hint about position inside the cursor
  using following flag: SERVER_STATUS_CURSOR_EXISTS, returned through
  ``call_handle_ok``. It must be used to determine if cursor is at EOR
  (last record was fetched) or not.

* Server converts the Mysqlx.Prepare.Execute arguments to types expected
  by placeholders. No additional work needs to be done.


UDF requirements
================

* UDF API doesn't pass to plugin/component current THD, thus X Plugin to
  identify ``xpl::Session`` must fetch THD using "current_thd" macro.