Additional coverage for WL#4179 attached.

It involves a lot of SP code refactoring so moved to 10.1.

We have several bugs which depends on this, so I have risen priority and put version here.

f832c661e637b6725e4af3f20c5a458757d1ca3b
f7bbb16a65abdbd03d50b1147c07e64249b80cfe
f3163a0b6e6b6d82f1b31ac62e835d3d1b5cd38c
7535096a79e68be1860fc7a3ddc3e2210bd616f4
010a900fcbb733b0b79e16710b979b4bce0e041d

a1b500f3b1b5f945f41b457301fe12ec4e68a131

2007d5060786552e191abe0933a22ef978d2b91a
a03792d4ea64af8cac286aa07637a75d59640d34
4478f48e46b4356c1295b1f62ac538362cc8f4d6
ffe63d62b45040194ec9c5e6dca325c26eb3300e
9d035190ea869337a42eada305161c3097664ad5
5f96951d37c5ee578d4957d59340bb4929ab6324

381346992345c6ea7bc94864b8f5b8a16265489d
5c436cf54805d91579ff838bfd52ed538ed6b9b8

-078166b94c1ca3f0f00df5f3724cf211c6e3ed58
-a4dd7f95c8a355009f317cff214568f220f0b6db

it looks like we need:
1) reorder sp_instr to have 1 class for all statements using expression (and so lex)

1.1) the class should contain:

• copy of statement or expression to parse
• MEM_ROOT and be able reparse content
• list of connected sp (triggers) and tables

1.2) how to make it parsed in the instruction memory? (probably impossible)
2) lex_keeper probably should go tho above sp_instr

above needs discussion

Parametric cursors already has its own arena.

Probably it is possible to switch arenas for each instruction as lex switched

Current status of the task:

• created the branch 10.9-MDEV-5816 containing patches with refactoring required for implementation of the core functional
• the branch is named intentionally without the prefix 'bb-' in order to avoid running tests on buildbot since too many tests fail.
• tests failures caused by the last commit (868f77e4182224329ffbb0c34c6fa5026ae015f2) in the branch; All failing tests are finished successfully on the last but one commit (6d9eaaacae966222a87bd33bc48055300cd14d3b).
• crash dumps produced by failing tests show that the possible reason for failures could be working with wrong mem_root (runtime instead of instruction memroot, as one of hypothesis).
• there is an issue with remembering right hand side expression of the SET statement, like

  SET var=f();

  For such expression the string '=f()' is stored inside a stored routine's instruction for statement re-parsing on failure during SP instruction execution. That is obviously incorrect. Right statement string for storing is 'f()'.

• query run inside stored routine now survive adding/removing columns to/from a query's table, e.g.

  MariaDB [test]> CREATE TABLE t1(a INT);

  Query OK, 0 rows affected (0,130 sec)

  MariaDB [test]> INSERT INTO t1 VALUES (1);

  Query OK, 1 row affected (0,003 sec)

  MariaDB [test]> CREATE PROCEDURE p1() SELECT * FROM t1;

  Query OK, 0 rows affected (0,065 sec)

  MariaDB [test]> CALL p1();

  +------+

  | a |

  +------+

  | 1 |

  +------+

  1 row in set (0,001 sec)

  Query OK, 0 rows affected (0,001 sec)

  MariaDB [test]> ALTER TABLE t1 ADD COLUMN b INT DEFAULT 3;

  Query OK, 0 rows affected (0,318 sec)

  Records: 0 Duplicates: 0 Warnings: 0

  MariaDB [test]> CALL p1();

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

  | a | b |

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

  | 1 | 3 |

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

  1 row in set (0,001 sec)

  Query OK, 0 rows affected (0,001 sec)

  MariaDB [test]> ALTER TABLE t1 DROP COLUMN a;

  Query OK, 0 rows affected (0,234 sec)

  Records: 0 Duplicates: 0 Warnings: 0

  MariaDB [test]> CALL p1();

  +------+

  | b |

  +------+

  | 3 |

  +------+

  1 row in set (0,001 sec)

  Query OK, 0 rows affected (0,001 sec)

Updated status of the task:

• fixed issues regarding storing a text of a statement corresponding to a SP instruction that used for re-parsing in case execution of SP instruction results in error. To be more precise,

• modified the grammar rules to fix issues with storing a text of statements
  SET var:=expression
  RETURN (SELECT ...)
  For the 'SET var:=expression' statement the string containing a text of the statement to store inside the SP is started from ':=' that's obviously incorrect.
• use a separate free_list for every SP instruction on parsing its statement string. The reason a separate free_list is used for every SP instruction is that the function subst_spvars() iterates along SP instruction's free_list before execution of the instruction.
• remember the original value of the data member sp->m_tmp_query and reset it to point to the beginning of SQL statement to be re-parsed. Restore the original value of sp->m_tmp_query after parsing of the statement finished. Typically, the data member sp->m_tmp_query is set by parser on parsing the content of a stored routine. But since we re-parse the stored routine's statement outside context of the whole stored routine, this data memebr should be set manually before start of parsing

• fixed issues regarding with re-parsing of a trigger caused by metadata changes.
• created the separate branch bb-10.9-MDEV-5816-1 containing refactoring patches that are prerequisites for the core implementation of the task. This branch is waiting for approving by Sanja.
• currently, there are only 5 failing tests in the main suite.

Updated status of the task:

• The new branch 10.11-MDEV-5816-1 contains current implementation of the task.
• There are few failing tests: sys_vars.sysvars_star versioning.partition main.ps_ddl sysschema.pr_create_synonym_db innodb.autoinc_persist atomic.rename_trigger
• There are several memory leaks on recompilation of statements for failing SP instructions that will be fixed on the next development cycle
• There is the critical issue regarding recompilation of statements inside triggers.
  • Before the failed statement of trigger is re-parsed, all items storing in the intrusive list sp_lex_instr::free_list are deleted. For triggers such items include Item_trigger_field. On the other hand, pointers to deleted objects of the class Item_trigger_field are stored at objects of the class Trigger. After re-parsing of the current statement for the failed SP instruction, Trigger objects still points to objects just destroyed that results in crash.
• First 8 test suites in the file sp_validation.test (borrowed from MySQL) passed ok. Total number of test suites is 11. First test that fails is regarding to running a stored routine that
  has an meta-data dependent expression inside IF instruction.

Branch for review is bb-11.0-MDEV-5816

see my e-main with only 2 issues

The branch bb-11.2-MDEV-5816 is ready for review and QA.

okay to push