WL#2793: Dynamic SQL: support EXECUTE IMMEDIATE

Affects: Server-6.x   —   Status: In-Design   —   Priority: Medium

Introduce a convenient syntax for Dynamic SQL in stored procedures:

EXECUTE IMMEDIATE <expr>

<expr> can contain any expression that doesn't refer to stored functions and
doesn't use tables (including temporary tables, schema tables, or views).

The expression is evaluated, cast to a string, and then parsed as an SQL
statement and executed.

Example:

EXECUTE IMMEDIATE concat("select * from ", @table_name, " where a=", @val);

The syntax is available as a standalone SQL (for testing purposes) and inside a
stored procedure.

Any SQL statement is supported in argument of EXECUTE IMMEDIATE. In particular,
DROP PROCEDURE, ALTER PROCEDURE, FLUSH TABLES, KILL are all allowed.

Stored procedure variables can be part of expression.

General log, binary log and slow log are supported. Instead of 
logging EXECUTE IMMEDIATE text, the statement that has actually been executed
is logged. 

Execution of the statement increments all respective statistics counters, as
well as a new one, Com_stmt_execute_direct.

Query cache is supported.

Query profiling (community feature) is supported as well.

EXECUTE IMMEDIATE "EXECUTE IMMEDIATE ...", EXECUTE IMMEDIATE "PREPARE ..."
and EXECUTE IMMEDIATE "EXECUTE ..." are prohibited to avoid meaningless 
recursion.

EXECUTE IMMEDIATE is a statement that can commit transaction or return multiple
result sets, and is therefore not supported inside stored functions and triggers.

It is available in Events.


Sample application
-------------------
For taking backups:

CREATE EVENT backup_weekly
ON SCHEDULE ...
DO BEGIN
 EXECUTE IMMEDIATE "BACKUP DATABASE .."
END|

To kill all queries of a certain user:

CREATE PROCEDURE kill_all_for_user(user_name varchar(255))
BEGIN
  DECLARE CURSOR FOR SELECT ... FROM INFORMATION_SCHEMA.PROCESSLIST WHERE ...;
  ...
  REPEAT
    EXECUTE IMMEDIATE concat("KILL ", user_connection_id);
    ..
  END REPEAT
END|


New parser keyword: IMMEDIATE (reserved word according to SQL:92) is added.

A new SQL word is supported:
EXECUTE IMMEDIATE <expr>.

<expr> can contain any expression that doesn't refer to stored functions and
doesn't use tables (including temporary tables, schema tables, or views).

Standard compliance
-------------------
EXECUTE IMMEDIATE is part of SQL standard (part 4, SQL/PSM, Embedded SQL) 
Add a new prepared statement method:

bool
Prepared_statement::execute_immediate(const char *query, uint length)

The method is responsible for parsing and execution of the argument without
affecting the current thread state (so that it can be used in nesting environments).

Add

/**
  SQLCOM_EXECUTE_IMMDEDIATE implementation.

  @param thd                thread handle

  @return
    none: in case of success, OK (or result set) packet is sent to the
    client, otherwise an error is set in THD
*/

void mysql_sql_stmt_execute_immediate(THD *thd)
- a function that handles a new SQLCOM, SQLCOM_EXECUTE_IMMEDIATE.

A narrative of the contents of this function:
 - evaluate the expression argument of EXECUTE_IMMEDIATE
 - create a prepared statement
 - call Prepared_statement::execute_immediate() with expression text
 - cleanup.