WL#4179: Stored programs: validation of stored program statements

Currently there is a problem in handling meta-data changes of the objects
(tables, views, ...) which are used in a stored program. The meta-data
information of such objects is fixed at the compilation time, and is not
updated when the objects are changed (by DDL statements). Next stored
program executions use outdated meta-data information, which leads to
wrong-data-errors or even a crash.

This WL task is about fixing that problem following the approach, previously
implemented for Prepared Statements (WL#4166). The idea is to remember
the original query for SQL-statement within a Stored Programs and re-parse
it when meta-data has changed.

There are a number of P1/P2 bugs which will be fixed by this WL task,
in particular:

  - Bug#11747537 - 32868: STORED ROUTINES DO NOT DETECT CHANGES IN
    META-DATA.

  - Bug#11747580 - 33082: STORED PROCEDURE: CRASH IF TABLE REPLACED WITH A
    VIEW IN A LOOP

  - Bug#11747581 - 33083: STORED FUNCTION: ERROR AND CRASH IF A TEMPORARY
    TABLE IS DROPPED IN A LOO

  - Bug#11747566 - 33000: TRIGGERS DO NOT DETECT CHANGES IN META-DATA.

  - Bug#11747626 - 33289: STORED PROCEDURE: BAD DATA IF VIEW IS REPLACED
    WITHIN A ITERATION

  - Bug#11745236 - 12257: SELECT * INSIDE PROCEDURE GIVES "UNKNOWN COLUMN"
    ON SECOND LOOP IF TBL C

  - Bug#12652835 - 61434: PREPARED STATEMENTS AND TEMPORARY TABLES IN
    STORED PROCEDURES

  - Bug#11747619 - 33255: TRIGGER USING VIEWS AND VIEW DDL : CORRUPTED
    TRIGGERS

All bugs fixed by this WL task are tagged with "WL#4179" in the Oracle BugDB.

In order to get the complete list of the bugs fixed by this WL task,
one can use 'Bugs_4179' saved search
(on http://clustra.no.oracle.com/orabugs/save-search.php).

This WL task is a pre-requisite for WL#4299 (Stored Routines: use the new
metadata cache).

Bug#11748352 (36002: PREPARED STATEMENTS: IF A VIEW USED IN A STATEMENT IS
REPLACED, BAD DATA) seems to be related, but actually a different issue.
A bugfix for Bug#11748352/36002 is needed to fix bugs from the list above.

Overview
========

Traditional SP-execution
------------------------

Prior to WL#4179 SP-instruction execution was as follows:

  - During first execution, a Stored Program is parsed, meaning every
    SQL-statement with the Stored Program is translated into the binary
    representation (LEX-object).

  - Binary binary representation has expanded wild-characters and
    established references to the data-objects (tables, views, ...).

  - During subsequent executions, the server just executes that binary
    representation (calls mysql_execute_command() against that LEX-object).

Overview of Prepared Statement invalidation
-------------------------------------------

WL#4166 introduced automatic re-preparation of a prepared statement
when the meta-data used by the prepared statement is changed.

For example:

  if we have a prepared statement like the following

    PREPARE s1 FROM 'SELECT * FROM table1';

  the statement 's1; will be automatically re-prepared when meta-data
  of the 'table1' table is changed (e.g. a column is added or dropped,
  or its definition is changed).

When a prepared statement is re-prepared, it starts to use up-to-date
meta-data information of the underlying objects. Thus, the user-visible
effect of automatic re-preparation is that the prepared statement produces
correct results even after the meta-data of underlying objects is changed.

Technically, this is achieved by the the following technique:

  - an opened table (or a view) has the current meta-data version;

  - at first execution of prepared statement, meta-data-versions
    of the underlying objects (tables, views) are remembered in the
    statement's context;

  - meta-data-version is changed (increased) when meta-data is changed;

  - each time a prepared statement is executed, the meta-data-versions
    remembered in the statement are compared against the current
    meta-data-versions from the object definitions.

  - if the current meta-data-version is newer than the saved one,
    an internal SQL-error is raised, thus, the prepared statement execution
    aborts;

  - that SQL-error is internally handled by the prepared statement execution
    layer up the calling stack in the following way:

    - the prepared statement is de-allocated (destroyed);

    - the prepared statement is re-prepared from the original query string;

    - execution is re-started.

  - The prepared statement execution layer makes three attempts to execute
    the prepared statement. If the execution fails three times
    due to differences in meta-data versions, a user-visible SQL-condition
    is raised (automatic re-preparation failed).

Comparing SP-execution and PS-execution
---------------------------------------

Here is more or less technical overview of differences between executing
a Stored Program statement, and a Prepared Statement.

Background:

  - There is Reprepare_observer interface, which provides report_error();

  - THD class has THD::m_reprepare_observer member, which is a pointer to
    an object implementing Reprepare_observer interface;

  - THD::m_reprepare_observer can be NULL.
  
    If it is NULL, meta-data change tracking is disabled, the queries are
    executed "as usual".

    Every query execution starts with the opening of the objects (tables,
    views, ...) used by that query. If THD::m_reprepare_observer points
    to a valid object, the table-opening function will call
    Reprepare_observer::report_error() method if meta-data of any object
    has been changed. report_error() throws ER_REPREPARE SQL-condition
    (this is an internal error), thus the table-opening function fails.

SP/PS execution steps:

  - Parsing phase (PREPARE for PS):

    - SP/PS: Parse SQL-statement;

    - SP/PS: Remember the LEX-object (generated for an SQL-statement) for
      future use;
    
    - SP/PS: Remember mem-root and free-item-list used during the parsing
      (they will form SP-instruction's arena);

  - Execution phase:

    - SP/PS: Switch active query arena to the SP-instruction arena

    - SP/PS: Re-initialise LEX-object before use

    - PS: Create an object implementing Reprepare_observer and make
      THD::m_reprepare_observer points to that object;

    - PS: Call mysql_execute_command() against the stored LEX-object;
      metadata changes (if any) will be noticed by the Reprepare_observer object,
      while opening tables.

      SP: Call mysql_execute_command() against the stored LEX-object;
      metadata changes will be missed, since the Reprepare Observer
      has not been set.

    - PS: Check if the Reprepare_observer-object noticed any meta-data
      change. If so, throw away current LEX-object and repeat the parsing
      stage.

WL#4179 overview
----------------

WL#4179 alters the SP-execution logic by following PS-execution path: setup
a Reprepare_observer object and tracks meta-data changes.

When a meta-data change is noticed, the corresponding SQL-statement should
be re-parsed (or re-prepared in case of Prepared Statements). The thing
is that Stored Programs support SQL-statements, which can not be executed
in the conventional mode (outside Stored Programs).

For example: RETURN, IF, DECLARE CURSOR, ...

The parser is not able to parse such an SQL-statement alone (without existing
SP-parsing-context). That means, that in order to re-parse such
an SQL-statement, the whole Stored Program has to be re-parsed. This is not
what we want.

This limitation is lifted as follows: such statements are split to
SQL-expressions, which might depend on the meta-data, and the static part,
which does not depend on the meta-data. Then, a new auxiliary SELECT-query
is constructed using the SQL-expression part.

For example:

  let's say a stored program contains the following SP-specific statement:

    RETURN (SELECT * FROM t1)

  It consists of the static part (just 'RETURN'), and the SQL-expression:
  '(SELECT * FROM t1)'. An auxiliary SELECT-statement would be:

    SELECT (SELECT * FROM t1)

  The idea is to (re-)parse that auxiliary SELECT-statement and take its
  first and only item to evaluate the RETURN-expression.

Handling SP-specific statements
===============================

As outlined in the 'WL#4179 overview', SP-statements are split into the
static part and SQL-expression part(s). The SQL-expression part is used
to construct an auxiliary SELECT-statement to re-parse the expression after
meta-data changes.

Below there is a list of SP-specific statements along with the notes of
handling every particular statement.

BEGIN...END
-----------

BEGIN and END designate the boundaries of a block. The statements themselves
do not have any SQL-expressions, thus no special action for them is needed.

SQL-statements in a BEGIN..END block, however, are handled as usual.

RETURN statement
----------------

Syntax (http://dev.mysql.com/doc/refman/5.6/en/return.html):

  RETURN expr

RETURN-statements are handled by the sp_instr_freturn instruction class.

sp_instr_freturn will be modified to store part of the original query,
corresponding to the 'expr'.

IF statement
------------

Syntax (http://dev.mysql.com/doc/refman/5.6/en/if-statement.html):

  IF search_condition THEN statement_list
      [ELSEIF search_condition THEN statement_list] ...
      [ELSE statement_list]
  END IF

Every branch in IF-statement (IF / ELSEIF) is handled by the
sp_instr_jump_if_not instruction class.

sp_instr_jump_if_not will be modified to store part of the original query,
corresponding to the 'search_condition'.

REPEAT statement 
----------------

Syntax (http://dev.mysql.com/doc/refman/5.6/en/repeat-statement.html):

  [begin_label:] REPEAT
      statement_list
  UNTIL search_condition
  END REPEAT [end_label]

REPEAT-statements are handled by the sp_instr_jump_if_not instruction
class.

sp_instr_jump_if_not should store part of the original query, corresponding
to the 'search_condition'.

WHILE statement
---------------

Syntax (http://dev.mysql.com/doc/refman/5.6/en/while-statement.html):

  [begin_label:] WHILE search_condition DO
      statement_list
  END WHILE [end_label]

WHILE-statements are handled by the sp_instr_jump_if_not instruction
class.

sp_instr_jump_if_not should store part of the original query, corresponding
to the 'search_condition'.

CASE statement
--------------

Syntax (http://dev.mysql.com/doc/refman/5.6/en/case-statement.html):

  CASE case_value
      WHEN when_value THEN statement_list
      [WHEN when_value THEN statement_list] ...
      [ELSE statement_list]
  END CASE

  or:

  CASE
      WHEN search_condition THEN statement_list
      [WHEN search_condition THEN statement_list] ...
      [ELSE statement_list]
  END CASE

As it is shown above, there are actually two different forms of
CASE-statements, which are handled differently from the technical point of
view.

Handling "simple CASE-statement"
++++++++++++++++++++++++++++++++

(that's how this CASE-variant is named internally).

  CASE case_value
      WHEN when_value THEN statement_list
      [WHEN when_value THEN statement_list] ...
      [ELSE statement_list]
  END CASE

There are the following instructions used to handle that statement:

  - sp_instr_set_case_expr -- it evaluates 'case_value' expression and
    stores the result into sp_rcontext internal table.

  - sp_instr_jump_if_not -- it creates auxillary Item-tree, corresponding
    to the following expression: 'case_value = when_value'

In order to be able to reprepare case_value and when_value expressions, the
following changes will be done:

  - sp_instr_set_case_expr will store part of the original query,
    corresponding to the 'case_value' expression;

  - a new instruction class will be introduced, with the following
    behaviour:

    - store part of the original query corresponding to the 'when_value'
      expression;

    - when meta-data change occurred:

      - re-parse 'when_value' expression when meta-data change occurred;

      - re-build 'case_value = when_value' internal expression;

      - use 'case_value = when_value' internal expression as the condition
        to jump

It must be guaranteed, that in case of meta-data change:

  - 'case_value' expression is evaluated once
  
  - 'when_value' expressions, which have already been evaluated and were
    false, are not evaluated again.

Handling "searched CASE-statement"
++++++++++++++++++++++++++++++++++

(that's how this CASE-variant is named internally)

  CASE
      WHEN search_condition THEN statement_list
      [WHEN search_condition THEN statement_list] ...
      [ELSE statement_list]
  END CASE

This form of the CASE-statement is more like traditional IF/ELSEIF
statement. It is handled by the sp_instr_jump_if_not instruction class.

sp_instr_jump_if_not should store part of the original query, corresponding
to the 'search_condition'.

It must be guaranteed, that in case of meta-data change:

  - 'when_value' expressions, which have already been evaluated and were
    false, are not evaluated again.

SET statement
-------------

Syntax (http://dev.mysql.com/doc/refman/5.6/en/set-statement.html):

  SET variable_assignment [, variable_assignment] ...

  variable_assignment:
        user_var_name = expr
      | [GLOBAL | SESSION] system_var_name = expr
      | [@@global. | @@session. | @@]system_var_name = expr

There are actually three forms of the SET-statement:

  - SET for user variables -- this is a regular statement, supported for
    conventional execution;

  - SET for SP-variables -- this is an SP-specific statement, handled by
    sp_instr_set instruction class;

  - SET for NEW/OLD trigger pseudo-rows -- this is an SP-specific
    statement, handled by sp_instr_set_trigger_field instruction class.

sp_instr_set and sp_instr_set_trigger_field should be modified to store
part of the original query, corresponding to the 'expr'.

DECLARE for SP-variables
------------------------

Syntax (http://dev.mysql.com/doc/refman/5.6/en/declare-local-variable.html):

  DECLARE var_name [, var_name] ... type [DEFAULT value]

The thing is that 'value' in the DEFAULT-clause might be an expression,
which depends on the meta-data.

Setting default values to stored program variables is handled by
the sp_instr_set instruction class. Thus, it will be done in the way
SET-statement does it for SP-variables.

Note, that MySQL supports the following non-documented feature:

  SET x = DEFAULT;

i.e. a stored program variable can be set to its default value somewhere
after the initial declaration.

The current implementation of this feature does not work well -- it crashes
the server under some circumstances (see Bug#13727199 - STORED PROGRAMS USING
SET AND DEFAULT IN SOME COMBINATION CRASH THE SERVER). In particular, if
DEFAULT expression contains a stored function reference, or a sub-query,
the server will crash.

It's been decided to not fix that in scope of WL#4179, as this is a side and
legacy problem. Thus, 'SET x = DEFAULT' will not detect changes in meta-data
of the objects, used in the DEFAULT expression.

For example, the following code will not detect meta-data changes
(now, it does not even execute -- it crashes the server):

  CREATE PROCEDURE p1()
  BEGIN
    DECLARE v INT DEFAULT (SELECT * FROM t1); # -- Here meta-data changes *will*
                                              # -- be noticed.
    ...
    SET v = DEFAULT;                          # -- Here meta-data changes will *not*
                                              # -- be noticed (the server
crashes now).
  END

Another note: DEFAULT-value-expression is re-evaluated every time,
the assignment happens.

DECLARE for handlers
--------------------

DECLARE keyword may be used to introduce an SQL-condition or a stored
program handler:
http://dev.mysql.com/doc/refman/5.6/en/declare-condition.html
http://dev.mysql.com/doc/refman/5.6/en/declare-handler.html

DECLARE-condition does not deal with data objects, nor can it contain an
expression referencing data objects.

DECLARE for cursors
-------------------

Syntax (http://dev.mysql.com/doc/refman/5.6/en/declare-cursor.html):

  DECLARE cursor_name CURSOR FOR select_statement

DECLARE CURSOR statements are handled by the sp_instr_cpush instruction class.

sp_instr_cpush will be modified to store part of the original query,
corresponding to the 'select_statement'.

Getting query string of statement
=================================

In order to make a prepared statement out of an SQL-statement in a stored
program, the statement's query string needs to be clearly identified.

It's proposed to use the current parser (sql_yacc.yy) to extract query string
for statements for now.

In the future, this could be implemented by means of the "Bison locations"
feature (WL#5967), which provides more straightforward way to get query
strings from the parser.

Re-execution
============

Like with the execution of the Prepared Statements, there are 3 attempts
to re-parse a SP-statememnt. If the parsing failed three times, an
SQL-condition (an error) is raised. It is a regular SQL-condition, which
can be handled according to the standard rules.

Other SQL-conditions are also preserved in the Diagnostics Area.

For example, let's say, there is a stored procedure with two statements:
  - one statement updates a table, and generates a warning/SQL-condition
    (type conversion, ..);
  - the other statement fails to re-parse.

  The 'CALL' statement will fail, Diagnostics Area will also contain the warning
  generated by the first statement.

If meta-data change is incompatible, re-parsing fails and Diagnostics Area
contains the corresponding error state.

For example:
  create table t(a int);

  create procedure P()
  begin
    ...
    select a from t;
    ...
  end;

  call P(); -- OK

  alter table t rename a to b;

  call P(); -- Error

  The second 'CALL' fails with the 'unknown column' error.

Performance impact
==================

Not more than 1% overhead with two different kinds of load:

Load 1: A CPU-intensive procedure that is not affected by meta-data changes.

Load 2: A stored procedure that executes a subquery and assigns the result to
a local variable. Overhead is measured without metadata changes.

Alternatives
============

Use internal Prepared Statements
--------------------------------

The first implementation of WL#4179 was using internal Prepared Statements:
every statement within a Stored Program is converted into a Prepared
Statement.

That approach worked more or less Ok with the traditional SQL-statements,
but it turned to be pretty ugly when things come to supporting SP-specific
statements (IF/CASE/..., DECLARE CURSOR, RETURN, ...).

The approach was to convert SP-specific statement to a pair:
  - an instruction with SET with user variable, which can be turn into an
    internal prepared statement
  - actual instruction for SP-specific statement.

For example,

  IF (SELECT * FROM t1) THEN ...

  would be converted into:

  PREPARE s1 FROM 'SET @__v1 = (SELECT * FROM t1)';
  EXECUTE s1;

  IF (@__v1) THEN ...

However, that didn't work for RETURN-statement, which must follow strict
type-conversion rules. Thus, for RETURN-statement, a new auxillary field in
the sp_rcontext-object was introduced, and RETURN-statement was converted
to something like:

  PREPARE s1 FROM 'SET __aux_return_field = (SELECT * FROM t1)';
  EXECUTE s1;

  RETURN __aux_return_field;

Those transformation were not obvious in some tricky cases and lead to
numerous errors.

Also, the performance impact was questionable.

Get an exhaustive set of the used objects
-----------------------------------------

We could try to make an exhaustive set of the objects a stored program is
using, lock those objects at the beginning of the stored program and detect
theirs meta-data changes.

Although this approach seems to be more natural and easier to implement,
there are the following conceptual problems, which make it hardly possible:

  - Stored functions and triggers are Ok, because they are executed in
    scope of outer transaction. Stored procedures and events are not.
    Thus, we're actually changing the locking scheme.

  - If all the objects are locked at the beginning of a stored procedure /
    event, the following problem appear: how to handle DDL statements and
    other statements that cause implicit commit?

    When commit happens, we need to re-take all the locks. It's not clear
    how to handle meta-data changes, happened in the middle of this
    process.

  - It's hard to support Dynamic SQL within stored procedures / events,
    because:

    - dynamic query string might eventually contain DDL-statement, or
      other statement causing implicit COMMIT (the previous problem).

    - it's not possible to determine full set of objects to pre-lock from
      the dynamic string.

  - Auto-commit mode. When auto-commit is on, stored procedure is executed
    statement by statement, and commit is issued after every statement.

Impact on replication
=====================

The current state (the state before WL#4179) is the following:

  - sp_instr_stmt substitutes all SP-variables in a statement by
    NAME_CONST()-expressions and writes the expanded query into the binary
    log;

  - a stored program call is logged only if it has modified the data
    (the stored program has a side effect):

    - stored procedure calls are logged per-statement (every statement in a
      stored procedure is logged);

    - stored function calls are logged as calls -- usually,
      by SELECT-statement;

WL#4179 does not introduce significant changes in replication.

Before executing prepared statement, the server should generate expanded
query (with SP-variables translated into NAME_CONST()-expressions) and
write it to the binary log.

Other notes
===========

Bug#11744945 (Stored procedure declared variable used instead of column)
------------------------------------------------------------------------

This bug most likely will not be fixed by this WL task.

The essence of this bug is resolving ambiguity when a stored-program-variable,
a stored-program-parameter and a table column have the same name.

Tentative specification from Peter
(though it's not clear yet if it is possible to achieve this behavior):

  Error for ambiguous column | parameter | variable name
  ------------------------------------------------------

  Suppose I have a column named X and I have a variable named X and
  a parameter named X (the same name for all three items).

  Suppose I refer to X in an SQL statement, e.g. "SELECT X FROM t".

  MySQL thinks I am referring to the variable. Some other DBMSs would
  think I am referring to the column. But according to Peter
  Gulutzan's email to Paul DuBois in 2005
  http://vilje01.no.oracle.com/mailarchive/mail.php?folder=51&mail=2207
  in standard SQL ambiguity should cause a syntax error.

  Therefore

  If the name is qualified, for example t.X, there is no possible
  ambiguity, X is a column name. This is correct until we implement
  WL#5975 (Qualified variable and parameter names).

  Otherwise, if the name can refer to any two of
  (a column, a parameter, a variable),
  Error. SQLSTATE = 42000.
  Message = Ambiguity. X is the name of both a
  (column|parameter|variable) name and a
  (column|parameter|variable) name.

  This error may happen during a routine invocation, rather than during
  a routine creation.

  This will fix BUG#5967 (Stored procedure declared variable used
  instead of column) although it will not do what BUG#5967 asks for.

  See also

    - MySQL Reference Manual 12.7.3.4. Scope and Resolution of Local Variables
      http://dev.mysql.com/doc/refman/5.6/en/local-variable-scope.html

    - dev-private email thread re BUG#5967
      Re: BUG#5967 Stored procedure declared variable used instead of column
      http://vilje01.no.oracle.com/mailarchive/mail.php?folder=4&mail=24912
  
    - dev-private email thread
      Limitation of name resolving of stored routines variables, starting
      http://vilje01.no.oracle.com/mailarchive/mail.php?folder=51&mail=2123

Functional requirements
=======================

Definitions
-----------

a. A stored program is either a stored procedure or a stored function, or an
event, or a trigger;

b. Successful execution means that the server returns OK status and the
result of stored program execution is as expected by the user.

Requirements
------------

1. Stored program must be re-executed successfully if the table that this
program is referenced to has been modified.

 1.1 Stored program that uses query like 'SELECT * FROM t' must be
 re-executed successfully if some columns were added into table 't'
 by ALTER TABLE;

 1.2 Stored program that uses query like 'SELECT * FROM t' must be
 re-executed successfully if some columns were removed from table 't'
 by ALTER TABLE;

 1.3 Stored program that uses query like 'SELECT * FROM t' must be
 re-executed successfully if a type of some table's columns were changed
 by ALTER TABLE;

 1.4 Stored program that uses query like 'SELECT * FROM t' must be
 re-executed successfully if the table 't' was dropped and created again
 with the same definition;

 1.5 Stored program that uses query like 'SELECT * FROM t' must be
 re-executed successfully if the table 't' was dropped and created again
 with different, but compatible definition.

2. Stored program must be successfully re-executed if the view that this
program is referenced to has been modified.

 2.1 Stored program that uses query like 'SELECT * FROM v' must be
 re-executed successfully if some columns were added into the view
 definition by ALTER VIEW;

 2.2 Stored program that uses query like 'SELECT * FROM v' must be
 re-executed successfully if some columns were removed from the view
 definition by ALTER VIEW;

 2.3 Stored program that uses query like 'SELECT * FROM v' must be
 re-executed successfully if a base table for the view being used was
 extended by new columns (by ALTER TABLE);

 2.4 Stored program that uses query like 'SELECT * FROM v' must be
 re-executed successfully if not used columns were removed from the
 base table of this view (by ALTER TABLE);

 2.5 Stored program that uses query like 'SELECT * FROM v' must be
 re-executed successfully if a type of some base table's columns were
 changed (by ALTER TABLE);

 2.6 Stored program that uses query like 'SELECT * FROM v' must be
 re-executed successfully if the view 'v' was dropped and created again
 with the same definition;

 2.7 Stored program that uses query like 'SELECT * FROM v' must be
 re-executed successfully if the view 'v' was dropped and created again
 with different, but compatible definition.

 2.8 Stored program that uses query like 'SELECT * FROM v' must be
 re-executed successfully if the view base tables have been re-created
 using the same or compatible definition.

3. Stored program must be re-executed successfully if a temporary table
that this program is referenced to has been modified.

 3.1 Stored program that uses query like 'SELECT * FROM t' must be
 re-executed successfully if some columns were added into temporary table
 table 't' (by ALTER TABLE);

 3.2 Stored program that uses query like 'SELECT * FROM t' must be
 re-executed successfully if some columns were removed from temporary
 table 't' (by ALTER TABLE);

 3.3 Stored program that uses query like 'SELECT * FROM t' must be
 re-executed successfully if a type of some temporary table's columns were
 changed (by ALTER TABLE);

 3.4 Stored program that uses query like 'SELECT * FROM t' must be
 re-executed successfully if the temporary table 't' was dropped and
 created again with the same definition;

 3.5 Stored program that uses query like 'SELECT * FROM t' must be
 re-executed successfully if the temporary table 't' was dropped and
 created again with different, but compatible definition.

4. Stored program must fail when it is re-executed after the definitions of
the objects being used have changed in an incompatible way or have
been dropped.

 4.1 Stored program must be fail when it is re-executed after a table's column
 that this program is referenced to has been removed;

 4.2 Stored program must be fail when it is re-executed after a temporary
 table's column that this program is referenced to has been removed;

 4.3 Stored program must be fail when it is re-executed after a view's
 column that this program is referenced to has been removed;

 4.4 Stored program must be fail when it is re-executed after a regular table
that this program referenced to was removed;

 4.5 Stored program must be fail when it is re-executed after a view that
this program referenced to was removed;

 4.6 Stored program must be fail when it is re-executed after a temporary
table that this program referenced to was removed;

 4.7 Stored program must be fail if the program executes some
SQL-statement and afterwards re-executes it again when some table 't'
referenced by the statement was dropped in the period between statement
execution;

5. A stored program must be successfully re-executed if it references
an object (a table, a temporary table, a view), which type has changed
between two executions of the function.

 5.1 Regular table -> View
 5.1 Regular table -> Temporary table
 5.1 View -> Regular table
 5.1 View -> Temporary table
 5.1 Temporary table -> View
 5.1 Temporary table -> Regular table

6. Triggers must handle changes in the base table definition
correctly when the base tables are accessed via NEW/OLD pseudo-variables.

 6.1 Trigger that uses column 'a' of table 't' via pseudo-variable NEW
 must be re-executed successfully if the table definition has been changed
 in a compatible way. "Compatible way" in this case is that if the table
 't' still has a column named 'a' and the column type is compatible with
 the operation that NEW.a takes part of.

 6.2 Trigger that uses column 'a' of table 't' via pseudo-variable OLD
 must be re-executed successfully if the table definition has been changed
 in a compatible way. "Compatible way" in this case is that if the table
 't' still has a column named 'a' and the column type is compatible with
 the operation that OLD.a takes part of.

 6.3 Re-execution of a trigger that uses column 'a' of table 't' via
 pseudo-variable NEW must fail if the table definition has been changed in
 the way that the column 'a' does not exist anymore.

 6.4 Re-execution of a trigger that uses column 'a' of table 't' via
 pseudo-variable OLD must fail if the table definition has been changed in
 the way that the column 'a' does not exist anymore.

7. Changes in definition of table/temporary table/view must be handled in a
correct way in case of dependency call of stored programs

 7.1 Setup:
   - stored program 'a', which alters regular table 't' in a compatible
     way;
   - stored program 'b', which calls 'a' and uses 't' before and after the
     call;
 Stored program 'b' must be executed successfully.

 7.2 Setup:
   - stored program 'a', which alters temporary table 't' in a compatible
     way;
   - stored program 'b', which calls 'a' and uses 't' before and after the
     call;
 Stored program 'b' must be executed successfully.

 7.3 Setup:
   - stored program 'a', which re-creates regular table 't' in a
     compatible way;
   - stored program 'b', which calls 'a' and uses 't' before and after the
     call;
 Stored program 'b' must be executed successfully.

 7.4 Setup:
   - stored program 'a', which re-creates temporary table 't' in a
     compatible way;
   - stored program 'b', which calls 'a' and uses 't' before and after the
     call;
 Stored program 'b' must be executed successfully.

 7.5 Setup:
   - stored program 'a', which re-creates view 'v' in a compatible way;
   - stored program 'b', which calls 'a' and uses 'v' before and after the
     call;
 Stored program 'b' must be executed successfully.

 7.6 Setup:
   - stored program 'a', which alters regular table 't' in an incompatible
     way;
   - stored program 'b', which calls 'a' and uses 't' before and after the
     call;
 Stored program 'b' must fail on access to the table after its
 modification.

 7.7 Setup:
   - stored program 'a', which alters temporary table 't' in an
     incompatible way;
   - stored program 'b', which calls 'a' and uses 't' before and after the
     call;
 Stored program 'b' must fail on access to the table after its
 modification.

 7.8 Setup:
   - stored program 'a', which re-creates regular table 't' in an
     incompatible way;
   - stored program 'b', which calls 'a' and uses 't' before and after the
     call;
 Stored program 'b' must fail on access to the table after its
 modification.

 7.9 Setup:
   - stored program 'a', which re-creates temporary table 't' in an
     incompatible way;
   - stored program 'b', which calls 'a' and uses 't' before and after the
     call;
 Stored program 'b' must fail on access to the table after its
 modification.

 7.10 Setup:
   - stored program 'a', which re-creates view 'v' in an incompatible way;
   - stored program 'b', which calls 'a' and uses 'v' before and after the
     call;
 Stored program 'b' must fail on access to the view after its
 modification.

8. Stored program must be executed successfully when:
 a. the program uses a table/view/temporary table that doesn't exist
    at the time of start program execution
 b. failed reference to the missed table/view/temporary table handled
    by stored program
 c. this table/view/temporary table is created as part of the
    program execution
 d. stored program gets access to newly created table/view/temporary
    table from some SQL-statement during subsequent stored program execution.

9. Stored program must be executed successfully when
   - the stored program has an expression in one of the following
     statements
     - RETURN
     - IF
     - CASE
     - WHILE
     - UNTIL
     - SET
   - the expression depends on the meta-data of some table/view/temporary table;
   - the meta-data of dependent object has changed in a compatible way.

   Note, that CASE-expression must be evaluated once even if (some)
   CASE-expressions need to be re-parsed.

10. Subsequent executions of a stored program must fail when
   - the stored program has an expression in one of the following
     statements
     - RETURN
     - IF
     - CASE
     - WHILE
     - UNTIL
     - SET
   - the expression depends on the meta-data of some table/view/temporary table;
   - the meta-data of dependent object has changed in a non-compatible way.

   Note, that CASE-expression must be evaluated once even if (some)
   CASE-expressions need to be re-parsed.

11. Metadata changes should be properly handled by SELECT-statement
 used for cursor declarations.

 11.1 If metadata of the objects (regular tables, temporary tables, views),
 used in SELECT-statement changed between DECLARE CURSOR and OPEN statements,
 the SELECT-statement should be re-parsed to use up-to-date metadata.

 11.2 If the metadata changed between OPEN and FETCH or CLOSE statements,
 those changes should not be noticed.

 11.3 Re-parsing of the SELECT-statement should be made correctly
 (in the correct parsing context) if the metadata changed between
 DECLARE CURSOR and OPEN statements, and those statements reside in different
 parsing contexts.