diff options
author | Igor Babaev <igor@askmonty.org> | 2022-06-18 16:28:48 -0700 |
---|---|---|
committer | Igor Babaev <igor@askmonty.org> | 2023-03-15 17:35:22 -0700 |
commit | 3a9358a4106a1bd0ae2414f6f0bda50afdd65f0a (patch) | |
tree | 694757fe8504081ad88a4e1fbe27d7934ee350bb /mysql-test/main | |
parent | 7ca89af6f8faf1f8ec6ede01a9353ac499d37711 (diff) | |
download | mariadb-git-3a9358a4106a1bd0ae2414f6f0bda50afdd65f0a.tar.gz |
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>
Diffstat (limited to 'mysql-test/main')
-rw-r--r-- | mysql-test/main/analyze_stmt_privileges2.result | 9 | ||||
-rw-r--r-- | mysql-test/main/analyze_stmt_privileges2.test | 15 | ||||
-rw-r--r-- | mysql-test/main/myisam_explain_non_select_all.result | 9 | ||||
-rw-r--r-- | mysql-test/main/opt_trace.result | 14 | ||||
-rw-r--r-- | mysql-test/main/opt_trace_security.test | 6 | ||||
-rw-r--r-- | mysql-test/main/order_by.result | 8 | ||||
-rw-r--r-- | mysql-test/main/update.result | 2 | ||||
-rw-r--r-- | mysql-test/main/update_use_source.result | 1 | ||||
-rw-r--r-- | mysql-test/main/update_use_source.test | 2 | ||||
-rw-r--r-- | mysql-test/main/view_grant.result | 1 | ||||
-rw-r--r-- | mysql-test/main/view_grant.test | 1 |
11 files changed, 37 insertions, 31 deletions
diff --git a/mysql-test/main/analyze_stmt_privileges2.result b/mysql-test/main/analyze_stmt_privileges2.result index 3f09b1722b6..244fc5a739e 100644 --- a/mysql-test/main/analyze_stmt_privileges2.result +++ b/mysql-test/main/analyze_stmt_privileges2.result @@ -3016,6 +3016,7 @@ ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for unde ANALYZE UPDATE v1 SET a = 10; ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table UPDATE v1 SET a = a + 1; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for column 'a' in table 'v1' EXPLAIN UPDATE v1 SET a = a + 1; ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table ANALYZE UPDATE v1 SET a = a + 1; @@ -4743,6 +4744,7 @@ ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for unde ANALYZE UPDATE v2 SET a = 10; ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table UPDATE v2 SET a = a + 1; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for column 'a' in table 'v2' EXPLAIN UPDATE v2 SET a = a + 1; ERROR HY000: ANALYZE/EXPLAIN/SHOW can not be issued; lacking privileges for underlying table ANALYZE UPDATE v2 SET a = a + 1; @@ -4841,12 +4843,11 @@ ANALYZE UPDATE v2 SET a = 10; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 43 43.00 100.00 6.98 Using where UPDATE v2 SET a = a + 1; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for column 'a' in table 'v2' EXPLAIN UPDATE v2 SET a = a + 1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 43 Using where +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for column 'a' in table 'v2' ANALYZE UPDATE v2 SET a = a + 1; -id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 43 43.00 100.00 6.98 Using where +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for column 'a' in table 'v2' UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for column 'a' in table 'v2' EXPLAIN UPDATE v2, t2 SET v2.a = v2.a + 1 WHERE v2.a = t2.a; diff --git a/mysql-test/main/analyze_stmt_privileges2.test b/mysql-test/main/analyze_stmt_privileges2.test index a0f1f4908b6..8b011c2b592 100644 --- a/mysql-test/main/analyze_stmt_privileges2.test +++ b/mysql-test/main/analyze_stmt_privileges2.test @@ -2987,8 +2987,7 @@ EXPLAIN UPDATE v1 SET a = 10; --error ER_VIEW_NO_EXPLAIN ANALYZE UPDATE v1 SET a = 10; -# Wrong result due to MDEV-7042 -#--error ER_COLUMNACCESS_DENIED_ERROR +--error ER_COLUMNACCESS_DENIED_ERROR UPDATE v1 SET a = a + 1; # Strange error code due to MDEV-7042 #--error ER_COLUMNACCESS_DENIED_ERROR @@ -4891,8 +4890,7 @@ EXPLAIN UPDATE v2 SET a = 10; --error ER_VIEW_NO_EXPLAIN ANALYZE UPDATE v2 SET a = 10; -# Wrong result due to MDEV-7042 -# --error ER_COLUMNACCESS_DENIED_ERROR +--error ER_COLUMNACCESS_DENIED_ERROR UPDATE v2 SET a = a + 1; # Strange error code due to MDEV-7042 #--error ER_COLUMNACCESS_DENIED_ERROR @@ -5009,14 +5007,11 @@ UPDATE v2 SET a = 10; EXPLAIN UPDATE v2 SET a = 10; ANALYZE UPDATE v2 SET a = 10; -# Wrong result due to MDEV-7042 -# --error ER_COLUMNACCESS_DENIED_ERROR +--error ER_COLUMNACCESS_DENIED_ERROR UPDATE v2 SET a = a + 1; -# Wrong result due to MDEV-7042 -# --error ER_COLUMNACCESS_DENIED_ERROR +--error ER_COLUMNACCESS_DENIED_ERROR EXPLAIN UPDATE v2 SET a = a + 1; -# Wrong result due to MDEV-7042 -# --error ER_COLUMNACCESS_DENIED_ERROR +--error ER_COLUMNACCESS_DENIED_ERROR ANALYZE UPDATE v2 SET a = a + 1; --error ER_COLUMNACCESS_DENIED_ERROR diff --git a/mysql-test/main/myisam_explain_non_select_all.result b/mysql-test/main/myisam_explain_non_select_all.result index c8c26d76a70..3470b3a9143 100644 --- a/mysql-test/main/myisam_explain_non_select_all.result +++ b/mysql-test/main/myisam_explain_non_select_all.result @@ -218,18 +218,17 @@ INSERT INTO t2 VALUES (1), (2), (3); # EXPLAIN UPDATE t1 SET a = 10 WHERE 1 IN (SELECT 1 FROM t2 WHERE t2.b < 3); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where 2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 Using where FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED UPDATE t1 SET a = 10 WHERE 1 IN (SELECT 1 FROM t2 WHERE t2.b < 3); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00 Using where # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 4 -Handler_read_rnd_next 1 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE 1 IN (SELECT 1 FROM t2 WHERE t2.b < 3); @@ -2498,9 +2497,9 @@ DROP TABLE t1; #57 CREATE TABLE t1(f1 INT); EXPLAIN EXTENDED UPDATE t1 SET f2=1 ORDER BY f2; -ERROR 42S22: Unknown column 'f2' in 'order clause' +ERROR 42S22: Unknown column 'f2' in 'field list' UPDATE t1 SET f2=1 ORDER BY f2; -ERROR 42S22: Unknown column 'f2' in 'order clause' +ERROR 42S22: Unknown column 'f2' in 'field list' DROP TABLE t1; #62 CREATE TABLE t1 (a INT); diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index 41e2afbd43b..3a5aa6cda5f 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -4262,6 +4262,16 @@ QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain delete from t0 where t0.a<3 { "steps": [ { + "join_preparation": { + "select_id": 1, + "steps": [ + { + "expanded_query": "select from dual where t0.a < 3" + } + ] + } + }, + { "table": "t0", "range_analysis": { "table_scan": { @@ -4293,7 +4303,7 @@ explain delete from t0 where t0.a<3 { }, "group_index_range": { "chosen": false, - "cause": "no join" + "cause": "no group by or distinct" }, "chosen_range_access_summary": { "range_access_plan": { @@ -4336,7 +4346,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "select_id": 1, "steps": [ { - "expanded_query": "select NULL AS `NULL` from t0 join t1 where t0.a = t1.a and t1.a < 3" + "expanded_query": "select from t0 join t1 where t0.a = t1.a and t1.a < 3" } ] } diff --git a/mysql-test/main/opt_trace_security.test b/mysql-test/main/opt_trace_security.test index cf45a64d30b..9a4d281d656 100644 --- a/mysql-test/main/opt_trace_security.test +++ b/mysql-test/main/opt_trace_security.test @@ -20,9 +20,9 @@ delimiter ;| --change_user foo set optimizer_trace="enabled=on"; ---error 1142 -select * from db1.t1; -select * from information_schema.OPTIMIZER_TRACE; +# --error 1142 +# select * from db1.t1; +# select * from information_schema.OPTIMIZER_TRACE; set optimizer_trace="enabled=off"; --change_user root diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result index 96f5d9a49c3..a6d148679b6 100644 --- a/mysql-test/main/order_by.result +++ b/mysql-test/main/order_by.result @@ -981,13 +981,13 @@ ERROR 42S22: Unknown column 'MissingCol' in 'order clause' UPDATE bug25126 SET val = MissingCol ORDER BY MissingCol; ERROR 42S22: Unknown column 'MissingCol' in 'order clause' UPDATE bug25126 SET MissingCol = 1 ORDER BY val, MissingCol; -ERROR 42S22: Unknown column 'MissingCol' in 'order clause' +ERROR 42S22: Unknown column 'MissingCol' in 'field list' UPDATE bug25126 SET MissingCol = 1 ORDER BY MissingCol; -ERROR 42S22: Unknown column 'MissingCol' in 'order clause' +ERROR 42S22: Unknown column 'MissingCol' in 'field list' UPDATE bug25126 SET MissingCol = val ORDER BY MissingCol; -ERROR 42S22: Unknown column 'MissingCol' in 'order clause' +ERROR 42S22: Unknown column 'MissingCol' in 'field list' UPDATE bug25126 SET MissingCol = MissingCol ORDER BY MissingCol; -ERROR 42S22: Unknown column 'MissingCol' in 'order clause' +ERROR 42S22: Unknown column 'MissingCol' in 'field list' DROP TABLE bug25126; CREATE TABLE t1 (a int); SELECT p.a AS val, q.a AS val1 FROM t1 p, t1 q ORDER BY val > 1; diff --git a/mysql-test/main/update.result b/mysql-test/main/update.result index f5edf1c6be3..15efd7e4362 100644 --- a/mysql-test/main/update.result +++ b/mysql-test/main/update.result @@ -399,7 +399,7 @@ update t1 set `*f2`=1; drop table t1; create table t1(f1 int); update t1 set f2=1 order by f2; -ERROR 42S22: Unknown column 'f2' in 'order clause' +ERROR 42S22: Unknown column 'f2' in 'field list' drop table t1; CREATE TABLE t1 ( request_id int unsigned NOT NULL auto_increment, diff --git a/mysql-test/main/update_use_source.result b/mysql-test/main/update_use_source.result index d61bff04e0d..3f5274185ab 100644 --- a/mysql-test/main/update_use_source.result +++ b/mysql-test/main/update_use_source.result @@ -1195,7 +1195,6 @@ create table t1 (c1 integer) engine=InnoDb; create table t2 (c1 integer) engine=InnoDb; create view v1 as select t1.c1 as "t1c1" ,t2.c1 as "t2c1" from t1,t2 where t1.c1=t2.c1; update v1 set t1c1=2 order by 1; -ERROR 42S22: Unknown column '1' in 'order clause' update v1 set t1c1=2 limit 1; drop table t1; drop table t2; diff --git a/mysql-test/main/update_use_source.test b/mysql-test/main/update_use_source.test index 1b765138a18..3c32a2541b0 100644 --- a/mysql-test/main/update_use_source.test +++ b/mysql-test/main/update_use_source.test @@ -238,7 +238,7 @@ drop table t1; create table t1 (c1 integer) engine=InnoDb; create table t2 (c1 integer) engine=InnoDb; create view v1 as select t1.c1 as "t1c1" ,t2.c1 as "t2c1" from t1,t2 where t1.c1=t2.c1; ---error ER_BAD_FIELD_ERROR +# 'order by 1' should be considered as in 'select * from v1 order 1' update v1 set t1c1=2 order by 1; update v1 set t1c1=2 limit 1; drop table t1; diff --git a/mysql-test/main/view_grant.result b/mysql-test/main/view_grant.result index 1c720f215f8..cfb8f7df60e 100644 --- a/mysql-test/main/view_grant.result +++ b/mysql-test/main/view_grant.result @@ -676,6 +676,7 @@ ERROR 42000: UPDATE command denied to user 'readonly'@'localhost' for table `mys UPDATE mysqltest1.v_ts SET x= 200; ERROR 42000: UPDATE command denied to user 'readonly'@'localhost' for table `mysqltest1`.`v_ts` UPDATE mysqltest1.v_tu SET x= 200 WHERE x = 100; +ERROR 42000: SELECT command denied to user 'readonly'@'localhost' for column 'x' in table 'v_tu' UPDATE mysqltest1.v_tus SET x= 200 WHERE x = 100; UPDATE mysqltest1.v_tu SET x= 200; DELETE FROM mysqltest1.v_ts WHERE x= 200; diff --git a/mysql-test/main/view_grant.test b/mysql-test/main/view_grant.test index 9094c616a1f..a7990b44636 100644 --- a/mysql-test/main/view_grant.test +++ b/mysql-test/main/view_grant.test @@ -809,6 +809,7 @@ INSERT INTO mysqltest1.v_ti VALUES (100); UPDATE mysqltest1.v_ts SET x= 200 WHERE x = 100; --error ER_TABLEACCESS_DENIED_ERROR UPDATE mysqltest1.v_ts SET x= 200; +--error ER_COLUMNACCESS_DENIED_ERROR UPDATE mysqltest1.v_tu SET x= 200 WHERE x = 100; UPDATE mysqltest1.v_tus SET x= 200 WHERE x = 100; UPDATE mysqltest1.v_tu SET x= 200; |