MDEV-28883 Re-design the upper level of handling UPDATE and DELETE statements

This patch introduces a new way of handling UPDATE and DELETE commands at
the top level after the parsing phase. This new way of processing update
and delete statements can be seen in the implementation of the  prepare()
and execute() methods from the new Sql_cmd_dml class. This class derived
from the Sql_cmd class can be considered as an interface class for processing
such commands as SELECT, INSERT, UPDATE, DELETE and other comands
manipulating data in tables.
With this patch processing of update and delete statements after parsing
proceeds by the following schema:
  - precheck of the access rights is performed for the used tables
  - the used tables are opened
  - context analysis phase is performed for the statement
  - the used tables are locked
  - the statement is optimized and executed
  - clean-up is performed for the statement
The implementation of the method Sql_cmd_dml::execute() adheres this schema.
The virtual functions of the class Sql_cmd_dml used for precheck of the
access rights, context analysis, optimization and execution allow to adjust
this schema for processing data manipulation statements of any types.

This schema of processing data manipulation statements is taken from the
current MySQL code. Moreover the definition the class Sql_cmd_dml introduced
in this patch is almost a full replica of such class in the existing MySQL.
However the implementation of the derived classes for update and delete
statements is quite different. This implementation employs the JOIN class
for all kinds of update and delete statements. It allows to perform main
bulk of context analysis actions by the function JOIN::prepare(). This
guarantees that characteristics and properties of the statement tree
discovered for optimization phase when doing context analysis are the same
for single-table and multi-table updates and deletes.

With this patch the following functions are gone:
  mysql_prepare_update(), mysql_multi_update_prepare(),
  mysql_update(), mysql_multi_update(),
  mysql_prepare_delete(), mysql_multi_delete_prepare(), mysql_delete().
The code within these functions have been used as much as possible though.
The functions mysql_test_update() and mysql_test_delete() are also not
needed anymore. The method Sql_cmd_dml::prepare() serves processing
  - update/delete statement
  - PREPARE stmt FROM "<update/delete statement>"
  - EXECUTE stmt when stmt is prepared from update/delete statement.

Approved by Oleksandr Byelkin <sanja@mariadb.com>

4 files changed, 4 insertions, 4 deletions

diff --git a/mysql-test/suite/funcs_1/r/is_collations.result b/mysql-test/suite/funcs_1/r/is_collations.result
index 979b477b8bc..6007742a2a1 100644
--- a/mysql-test/suite/funcs_1/r/is_collations.result
+++ b/mysql-test/suite/funcs_1/r/is_collations.result
@@ -66,7 +66,7 @@ INSERT INTO information_schema.collations
 (collation_name,character_set_name,id,is_default,is_compiled,sortlen)
 VALUES (  'cp1251_bin',          'cp1251',50,        '',         '',0);
 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
-UPDATE information_schema.collations SET description = 'just updated';
+UPDATE information_schema.collations SET collation_name = 'just updated';
 Got one of the listed errors
 DELETE FROM information_schema.collations WHERE table_name = 't1';
 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
diff --git a/mysql-test/suite/funcs_1/t/is_collations.test b/mysql-test/suite/funcs_1/t/is_collations.test
index db34a7b77b1..aa199b512bc 100644
--- a/mysql-test/suite/funcs_1/t/is_collations.test
+++ b/mysql-test/suite/funcs_1/t/is_collations.test
@@ -83,7 +83,7 @@ INSERT INTO information_schema.collations
 VALUES (  'cp1251_bin',          'cp1251',50,        '',         '',0);
 
 --error ER_DBACCESS_DENIED_ERROR,ER_NON_UPDATABLE_TABLE
-UPDATE information_schema.collations SET description = 'just updated';
+UPDATE information_schema.collations SET collation_name = 'just updated';
 
 --error ER_DBACCESS_DENIED_ERROR
 DELETE FROM information_schema.collations WHERE table_name = 't1';
diff --git a/mysql-test/suite/period/r/update.result b/mysql-test/suite/period/r/update.result
index f726b4c07cf..004b9976f51 100644
--- a/mysql-test/suite/period/r/update.result
+++ b/mysql-test/suite/period/r/update.result
@@ -229,8 +229,8 @@ update t for portion of apptime from @s to g() set t.id= t.id + 5;
 ERROR HY000: Expression in FOR PORTION OF must be constant
 # success
 update t for portion of apptime from @s to h() set t.id= t.id + 5;
-# select value is cached
 update t for portion of apptime from (select s from t2 limit 1) to h() set t.id= t.id + 5;
+ERROR HY000: Expression in FOR PORTION OF must be constant
 # auto_inrement field is updated
 create or replace table t (id int primary key auto_increment, x int,
 s date, e date, period for apptime(s, e));
diff --git a/mysql-test/suite/period/t/update.test b/mysql-test/suite/period/t/update.test
index 3f4dd2bdc68..fd67dc328c1 100644
--- a/mysql-test/suite/period/t/update.test
+++ b/mysql-test/suite/period/t/update.test
@@ -123,7 +123,7 @@ update t for portion of apptime from @s to g() set t.id= t.id + 5;
 
 --echo # success
 update t for portion of apptime from @s to h() set t.id= t.id + 5;
---echo # select value is cached
+--error ER_NOT_CONSTANT_EXPRESSION
 update t for portion of apptime from (select s from t2 limit 1) to h() set t.id= t.id + 5;
 
 --echo # auto_inrement field is updated