WL#8413: Exposing PS execution to plugins

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

WL#7126 introduces an API to execute server commands, like COM_QUERY, by
decoupling the commands from their input. The class Protocol takes the burden
of parsing the incoming network packets, parses them into structs, which are
specific for every command, and passes the fields of the struct to the commands
that have been refactored to use direct parameters instead of network buffer.
Due to lack of time, WL#7126 included support only for basic commands.
The COM_STMT_EXECUTE command was not converted and thus prepared statements
(PS) can't be used via protocol plugins.

Goal of this WL is to refactor PS implementation to allow protocol plugins to
use them. 

A stretch goal is to allow plugins to use PS's materialized cursors
implemented in server. Cursors aren't available via SQL layer, only via
protocol API and should be fairly easy to use. However, would there any
significant issues occur, this goal would be dropped.

Executing prepared statements also means switching between the text protocol and 
the binary protocol(and back). Running commands through the sql service api also 
involves switching back and forth between the plugin protocol, error protocol and 
one of the classic protocols. So to make things easier a stack-like mechanism was 
added. This way the current protocol will remember the previous one and will be 
able to restore it back to the original protocol on exit.

This is purely a refactoring work, it doesn't add any new functionality to the
server.  However, after this WL protocol plugins should be able to use PS.

User Documentation

No user-visible changes. No user documentation required.
F-1: All clients should work as before

F-2: There should be no test failures

Non-Functional requirements:

NF-1: There should be no performance impact from the changes made in this worklog
Currently, PS code parses raw packet to extract PS's arguments. In this WL it
should be changed in order to accept arguments provided by the protocol.
They should be stored in the extended COM_STMT_EXECUTE_DATA structure.
Along with the stmt id open_cursor and has_new_types flags,
there should be a new array of parameters which should contain: (is)null_bit, 
type, (is)unsigned_type, value and length.. This structure would be passed down 
the call path and following
functions should be changes to read data from it, rather than parsing raw
- mysqld_stmt_execute()
- Prepared_statement::execute_loop()
- Prepared_statement::set_parameters()
- setup_conversion_functions()
- Prepared_statement::insert_params()

Pre-checking arguments will be done in mysql_stmt_precheck. The number of 
arguments and stmt id will be checked to be valid.

The cursor support should start working after the refactoring is done. The
protocol should provide the CURSOR_TYPE_READ_ONLY to the COM_STMT_EXECUTE
command in order to create cursor and allow COM_STMT_FETCH command.

Add stack-like mechanism for switching between protocols:
When switching to a new one: call the push method on THD with the new protocol as 
argument. THD will set this new protocol as the current protocol and store a 
pointer to the old protocol on the current protocol.
When going back to the previous protocol: call pop from THD which will set the 
current protocol back to the previous one.
First of all the struct COM_STMT_EXECUTE_DATA must be extended and it will 

struct st_com_stmt_execute_data
  unsigned long stmt_id;
  unsigned long open_cursor; // if the server side cursor should be opened 
instead of sending the results
  PS_PARAM *parameters; // array containing the parameters used to execute the 
  unsigned long parameter_count; // number of parameters
  unsigned char has_new_types; // if the PS_PARAM struct also defines the data 
type for the parameters(this should be always true for the first run)

PS_PARAM is defined as:

struct st_ps_param
  unsigned char null_bit;    // flag that stores if parameter is equal to null
  enum enum_field_types type; // parameter data type
  unsigned char unsigned_type; // signed/unsigned
  const unsigned char *value; // contains the value stored as char array
  size_t length; // length of the char array containing the value

All the functions related to prepared statement which are called from 
dispatch_command will change signature and will have the package and the 
statement id replaced with a parsed command and the pointer to the actualy 
Prepared statement. The responsibility of creating the command will be delegated 
to each protocol.

1. The signature of mysqld_stmt_execute() will be changed to:
void mysqld_stmt_execute(THD *thd,
                         Prepared_statement *stmt,
                         bool has_new_types,
                         ulong execute_flags,
                         PS_PARAM *parameters);

The code that validates the prepared statemnt id will be moved to 
mysql_stmt_precheck. This way mysqld_stmt_execute() will get the right object to 
work on or won't be executed at all.

2. These three functions:

void mysqld_stmt_fetch(THD *thd, Prepared_statement *stmt, ulong num_rows);
void mysqld_stmt_reset(THD *thd, Prepared_statement *stmt);
void mysql_stmt_get_longdata(THD *thd, Prepared_statement *stmt,
                             uint param_number, uchar *longdata, ulong length);

will get a pointer to the Prepared_statement instead of just the id of the 
prepared statement and these function will only be called after 
mysql_stmt_precheck will check that the statement id really exists.

In Prepared_statement the following changes will occur:

1. The signature of Prepared_statement::execute_loop() will be changed to this:
bool execute_loop(String *expanded_query,
                  bool open_cursor,
                  PS_PARAM *parameters);
So the network package will be replaced by an array of parameter. 
mysql_sql_stmt_execute() will call execute_loop() with false and nullptr for the 
2 new parameters(parameters are coming from user variables).

2. The signature of Prepared_statement::set_parameters() will be overridden to 
eliminate the if's inside the function since the logic is anyway split in two(if 
the parameters come from user variables or from the COM_STMT_EXECUTE packet) and 
it will become:

bool Prepared_statement::set_parameters(String *expanded_query,
                                        bool has_new_types,
                                        PS_PARAM *parameters);
bool Prepared_statement::set_parameters(String *expanded_query);

Also the methods will become public and will be called outside execute_loop.

3. The signature of Prepared_statement::insert_params() will be changed to:
bool Prepared_statement::insert_params(String *query, PS_PARAM *parameters)

4. Query_fetch_protocol_binary result member in Prepared_statement will be 
replaced  by a pointer to Query_result_send and will be initialized to either 
Query_fetch_protocol_binary or Query_result_send when plugin protocols are used 
and deallocated in close_cursor, destructor. This way the Query result can be 
also sent to 

5. The helper function setup_conversion_functions() used by set_parameters will 
also change signature and instead of having access to the package it will get 
access to the PS_PARAM array:

static bool setup_conversion_functions(Prepared_statement *stmt,
                                       PS_PARAM *parameters)
The function will not be called if new types are NOT provided(now the function 
is called but returns after checking the 'types supplied' byte)

Since the network package needs to be converted to a command by each protocol 
some changes needed to be made, so new function will be introduced:

ulong get_ps_param_len(enum enum_field_types type, uchar *packet,
                       ulong packet_len, ulong *header_len, bool *err);

This function will return the length of the the header length(bytes containing 
the parameter length at the beginning of the packet), the current param's actual 
length, or in case of error it will return 0 and set parameter error to true. 
The returned value will be used to chop the amount of bytes needed for 
constructing the PS_PARAM *parameters. PS_PARAM *parameters' value will serve 
just as a pointer to the data inside the network buffer (in case of the classic 
protocol). This will eliminate duplication of data and also data copy.

get_ps_param_len() will have a switch, which depending on the type either
returns a fixed value (as for byte, float, double, or uses get_param_length()
for run length encoded values. get_ps_param_len() will not jump over
the data. This will be a responsibility of the caller.

Protocol_classic's send_out_parameters(List<Item_param> *sp_params) was added to 
the Protocol interface, and updated to:
bool send_parameters(List<Item_param> *parameters, bool is_sql_prepare);

since Prepared_statement::execute was actually sending the OUT-parameters as 
user variables when the protocol used was text or it was a sql_prepare statement 
and it was writing the Items to the protocol otherwise. The functions' logic was 
not changed.

Protocol's send_ok function got a new parameter: bool eof_identifier in order to 
remove the need to check if the protocol is classic when sending the response in  

In send_prep_stmt added code to be able to send the statement id and 
metadata(number of columns, parameters and warnings) to the plugin protocols.

get_param_length no longer changes the packet, neither the 
set_param_tiny/short/long... (except for EMBEDDED protocol).

mysql_test_select and check_prepared_statement were adapted to work with plugin 

Other changes:
packet_length -> input_packet_length
raw_packet -> input_raw_packet
removed storage_packet()
removed bool flush_net(); and updated bool flush(); to: bool flush(bool 
force_flush); and added it to Protocol API
set_pkt_nr -> set_output_pkt_nr
get_pkt_nr -> get_output_pkt_nr
get_packet -> get_output_packet

removed checks for protocol type in do_command as the method is only executed by 
the classic protocols
the switch to binary protocol in mysqld_stmt_prepare, mysqld_stmt_execute only 
happens for the text protocol.

Stack like methods for switching protocols:

Protocol class will work as a node storing a reference to the previous protocol 
and THD will hold the HEAD of the stack(current used protocol).

Protocol will have an additional member called m_previous_protocol which will 
store a pointer to the previous protocol in the stack or nullptr if there's no 
other protocol. Also push_protocol method will be added to Protocol so that THD 
will be able to  chain the current protocol to the new protocol before setting 
it as the HEAD(current protocol). pop_protocol method will added so that THD 
will be able to retrieve previous protocol from the current protocol(go back one 
step on the stack).

THD already has a pointer to the current protocol using m_protocol, so a 
push_protocol method will be added to be able to set the new protocol as the 
current one (which will hold the pointer to the old one), and pop_protocol to 
remove the top of the stack and set the current protocol(in m_protocol) to the 
previous one.