revision-id: 086a212d96b7693d1bacf67e3ad14627fb802269 (mariadb-10.6.1-49-g086a212) parent(s): 593885f785440358028999cb8d2c47d4b0a1e917 author: Igor Babaev committer: Igor Babaev timestamp: 2021-12-02 21:51:14 -0800 message: MDEV-27159 Re-design the upper level of handling DML commands This is the first commit for the task. This patch allows to execute only single-table and multi-table UPDATE statements using the method Sql_cmd_dml::execute(). The code that handles DELETE and INSERT statements has not been touched. Moreover, these are not the final changes to handle UPDATE statements. All tests from the main suite passed. With --ps-protocol one test from opt_trace_security returns not the same result. This will be fixed soon. --- mysql-test/main/analyze_stmt_privileges2.result | 9 +- mysql-test/main/analyze_stmt_privileges2.test | 15 +- .../main/myisam_explain_non_select_all.result | 86 +---- mysql-test/main/mysqlbinlog_row_minimal.test | 1 - mysql-test/main/opt_trace.test | 1 - mysql-test/main/order_by.result | 8 +- mysql-test/main/partition_explicit_prune.result | 1 - mysql-test/main/sp.result | 2 +- mysql-test/main/update.result | 2 +- mysql-test/main/update_use_source.result | 7 +- mysql-test/main/update_use_source.test | 2 +- mysql-test/main/view_grant.result | 1 + mysql-test/main/view_grant.test | 1 + sql/opt_subselect.cc | 11 + sql/opt_trace.cc | 3 +- sql/sql_base.cc | 26 +- sql/sql_base.h | 15 + sql/sql_class.h | 1 + sql/sql_cmd.h | 151 ++++++++- sql/sql_delete.h | 24 ++ sql/sql_lex.cc | 9 + sql/sql_lex.h | 26 ++ sql/sql_parse.cc | 128 +------- sql/sql_prepare.cc | 143 +-------- sql/sql_select.cc | 138 +++++++- sql/sql_update.cc | 352 ++++++++++++--------- sql/sql_update.h | 40 +++ sql/sql_yacc.yy | 9 + 28 files changed, 698 insertions(+), 514 deletions(-) diff --git a/mysql-test/main/analyze_stmt_privileges2.result b/mysql-test/main/analyze_stmt_privileges2.result index f269aaf..d40dd63 100644 --- a/mysql-test/main/analyze_stmt_privileges2.result +++ b/mysql-test/main/analyze_stmt_privileges2.result @@ -3034,6 +3034,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; @@ -4767,6 +4768,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; @@ -4865,12 +4867,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 a0f1f49..8b011c2 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 2ff966f..36231c3 100644 --- a/mysql-test/main/myisam_explain_non_select_all.result +++ b/mysql-test/main/myisam_explain_non_select_all.result @@ -19,7 +19,6 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where # Status of EXPLAIN EXTENDED query Variable_name Value -Handler_read_key 2 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE a < 10; @@ -38,7 +37,6 @@ Handler_read_key 2 Handler_read_rnd_next 4 # Status of testing query execution: Variable_name Value -Handler_read_key 2 Handler_read_rnd_next 4 Handler_update 3 @@ -152,7 +150,6 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 # Status of EXPLAIN EXTENDED query Variable_name Value -Handler_read_key 4 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1, t2 WHERE t1.a = 1; @@ -172,7 +169,6 @@ Handler_read_key 4 Handler_read_rnd_next 8 # Status of testing query execution: Variable_name Value -Handler_read_key 4 Handler_read_rnd_next 8 Handler_update 1 @@ -202,7 +198,6 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 # Status of EXPLAIN EXTENDED query Variable_name Value -Handler_read_key 4 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 t11, (SELECT * FROM t2) t12 WHERE t11.a = 1; @@ -222,7 +217,6 @@ Handler_read_key 4 Handler_read_rnd_next 8 # Status of testing query execution: Variable_name Value -Handler_read_key 4 Handler_read_rnd_next 12 Handler_update 1 @@ -240,18 +234,16 @@ Warnings: Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead 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); @@ -272,7 +264,6 @@ Handler_read_key 5 Handler_read_rnd_next 8 # Status of testing query execution: Variable_name Value -Handler_read_key 4 Handler_read_rnd_next 5 Handler_update 3 @@ -302,7 +293,6 @@ Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 # Status of EXPLAIN EXTENDED query Variable_name Value -Handler_read_key 4 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (SELECT b FROM t2 WHERE t1.a < 3); @@ -323,7 +313,6 @@ Handler_read_key 4 Handler_read_rnd_next 8 # Status of testing query execution: Variable_name Value -Handler_read_key 4 Handler_read_rnd_next 7 Handler_update 2 @@ -355,7 +344,6 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where # Status of EXPLAIN EXTENDED query Variable_name Value -Handler_read_key 4 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1, t2 WHERE a IN (SELECT b FROM t2 WHERE t2.b < 3); @@ -377,7 +365,7 @@ Handler_read_key 7 Handler_read_rnd_next 12 # Status of testing query execution: Variable_name Value -Handler_read_key 7 +Handler_read_key 3 Handler_read_rnd_next 16 Handler_update 2 @@ -407,7 +395,6 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 # Status of EXPLAIN EXTENDED query Variable_name Value -Handler_read_key 4 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 t11, (SELECT * FROM t2) t12; @@ -427,7 +414,6 @@ Handler_read_key 4 Handler_read_rnd_next 8 # Status of testing query execution: Variable_name Value -Handler_read_key 4 Handler_read_rnd 3 Handler_read_rnd_deleted 1 Handler_read_rnd_next 24 @@ -459,7 +445,6 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used # Status of EXPLAIN EXTENDED query Variable_name Value -Handler_read_key 2 Handler_read_rnd_next 1 FLUSH STATUS; FLUSH TABLES; @@ -482,7 +467,6 @@ Handler_read_key 2 Handler_read_rnd_next 5 # Status of testing query execution: Variable_name Value -Handler_read_key 2 Handler_read_rnd 3 Handler_read_rnd_next 9 Handler_update 3 @@ -513,7 +497,6 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 # Status of EXPLAIN EXTENDED query Variable_name Value -Handler_read_key 4 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 t11, (SELECT * FROM t2) t12 WHERE t11.a > 1; @@ -533,7 +516,6 @@ Handler_read_key 4 Handler_read_rnd_next 8 # Status of testing query execution: Variable_name Value -Handler_read_key 4 Handler_read_rnd_next 16 Handler_update 2 @@ -995,7 +977,6 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00 Using where # Status of EXPLAIN EXTENDED query Variable_name Value -Handler_read_key 4 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (SELECT a FROM t2); @@ -1016,7 +997,6 @@ Handler_read_key 7 Handler_read_rnd_next 8 # Status of testing query execution: Variable_name Value -Handler_read_key 4 Handler_read_rnd_next 10 Handler_update 3 @@ -1134,7 +1114,6 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 # Status of EXPLAIN EXTENDED query Variable_name Value -Handler_read_key 3 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1; @@ -1153,7 +1132,6 @@ Handler_read_key 3 Handler_read_rnd_next 6 # Status of testing query execution: Variable_name Value -Handler_read_key 3 Handler_read_rnd_next 6 Handler_update 5 @@ -1917,7 +1895,6 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 5 100.00 Using where; Using buffer # Status of EXPLAIN EXTENDED query Variable_name Value -Handler_read_key 4 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5; @@ -1936,7 +1913,7 @@ Handler_read_key 5 Handler_read_next 4 # Status of testing query execution: Variable_name Value -Handler_read_key 5 +Handler_read_key 1 Handler_read_next 4 Handler_read_rnd 5 Handler_update 5 @@ -1965,7 +1942,6 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using where; Using filesort # Status of EXPLAIN EXTENDED query Variable_name Value -Handler_read_key 4 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5; @@ -1987,7 +1963,6 @@ Sort_rows 5 Sort_scan 1 # Status of testing query execution: Variable_name Value -Handler_read_key 4 Handler_read_rnd 5 Handler_read_rnd_next 27 Handler_update 5 @@ -2019,7 +1994,6 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using where; Using filesort # Status of EXPLAIN EXTENDED query Variable_name Value -Handler_read_key 8 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; @@ -2041,7 +2015,6 @@ Sort_rows 1 Sort_scan 1 # Status of testing query execution: Variable_name Value -Handler_read_key 8 Handler_read_rnd 1 Handler_read_rnd_next 27 Handler_update 1 @@ -2074,7 +2047,6 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 index NULL a 15 NULL 5 100.00 Using where; Using buffer # Status of EXPLAIN EXTENDED query Variable_name Value -Handler_read_key 8 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; @@ -2095,7 +2067,6 @@ Handler_read_next 4 # Status of testing query execution: Variable_name Value Handler_read_first 1 -Handler_read_key 8 Handler_read_next 4 Handler_read_rnd 5 Handler_update 5 @@ -2124,7 +2095,6 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using where; Using filesort # Status of EXPLAIN EXTENDED query Variable_name Value -Handler_read_key 8 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; @@ -2146,7 +2116,6 @@ Sort_rows 1 Sort_scan 1 # Status of testing query execution: Variable_name Value -Handler_read_key 8 Handler_read_rnd 1 Handler_read_rnd_next 27 Sort_priority_queue_sorts 1 @@ -2178,7 +2147,6 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using where; Using filesort # Status of EXPLAIN EXTENDED query Variable_name Value -Handler_read_key 8 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; @@ -2201,7 +2169,6 @@ Sort_rows 1 Sort_scan 1 # Status of testing query execution: Variable_name Value -Handler_read_key 8 Handler_read_rnd 1 Handler_read_rnd_next 27 Sort_priority_queue_sorts 1 @@ -2233,7 +2200,6 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 index_merge key1,key2 key1,key2 5,5 NULL 7 100.00 Using sort_union(key1,key2); Using where; Using filesort # Status of EXPLAIN EXTENDED query Variable_name Value -Handler_read_key 6 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1; @@ -2255,7 +2221,7 @@ Sort_range 1 Sort_rows 4 # Status of testing query execution: Variable_name Value -Handler_read_key 8 +Handler_read_key 2 Handler_read_next 7 Handler_read_rnd 8 Handler_update 4 @@ -2286,7 +2252,6 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 5 100.00 Using where; Using buffer # Status of EXPLAIN EXTENDED query Variable_name Value -Handler_read_key 4 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5; @@ -2305,7 +2270,7 @@ Handler_read_key 5 Handler_read_prev 4 # Status of testing query execution: Variable_name Value -Handler_read_key 5 +Handler_read_key 1 Handler_read_prev 4 Handler_read_rnd 5 Handler_update 5 @@ -2334,7 +2299,6 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using filesort # Status of EXPLAIN EXTENDED query Variable_name Value -Handler_read_key 6 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t2 ORDER BY a, b DESC LIMIT 5; @@ -2356,7 +2320,6 @@ Sort_rows 5 Sort_scan 1 # Status of testing query execution: Variable_name Value -Handler_read_key 6 Handler_read_rnd 5 Handler_read_rnd_next 27 Handler_update 4 @@ -2389,7 +2352,6 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 index NULL a 6 NULL 5 100.00 Using buffer # Status of EXPLAIN EXTENDED query Variable_name Value -Handler_read_key 6 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t2 ORDER BY a DESC, b DESC LIMIT 5; @@ -2409,7 +2371,6 @@ Handler_read_last 1 Handler_read_prev 4 # Status of testing query execution: Variable_name Value -Handler_read_key 6 Handler_read_last 1 Handler_read_prev 4 Handler_read_rnd 5 @@ -2441,7 +2402,6 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range c1_idx c1_idx 2 NULL 2 100.00 Using where; Using filesort # Status of EXPLAIN EXTENDED query Variable_name Value -Handler_read_key 6 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2; @@ -2463,7 +2423,7 @@ Sort_range 1 Sort_rows 2 # Status of testing query execution: Variable_name Value -Handler_read_key 7 +Handler_read_key 1 Handler_read_next 2 Handler_read_rnd 2 Handler_update 2 @@ -2536,7 +2496,6 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 100.00 Using where; Using buffer # Status of EXPLAIN EXTENDED query Variable_name Value -Handler_read_key 3 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE a > 34; @@ -2555,7 +2514,7 @@ Handler_read_key 4 Handler_read_next 2 # Status of testing query execution: Variable_name Value -Handler_read_key 4 +Handler_read_key 1 Handler_read_next 2 Handler_read_rnd 2 Handler_update 2 @@ -2583,7 +2542,6 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 # Status of EXPLAIN EXTENDED query Variable_name Value -Handler_read_key 7 Handler_read_rnd_next 1 FLUSH STATUS; FLUSH TABLES; @@ -2605,7 +2563,6 @@ Handler_read_key 7 Handler_read_rnd_next 4 # Status of testing query execution: Variable_name Value -Handler_read_key 7 Handler_read_rnd_next 4 # @@ -2626,7 +2583,6 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where # Status of EXPLAIN EXTENDED query Variable_name Value -Handler_read_key 7 Handler_read_rnd_next 1 FLUSH STATUS; FLUSH TABLES; @@ -2648,7 +2604,6 @@ Handler_read_key 7 Handler_read_rnd_next 4 # Status of testing query execution: Variable_name Value -Handler_read_key 7 Handler_read_rnd_next 4 DROP TABLE t1, t2; @@ -2678,7 +2633,6 @@ Warnings: Note 1276 Field or reference 'test.t1.f1' of SELECT #2 was resolved in SELECT #1 # Status of EXPLAIN EXTENDED query Variable_name Value -Handler_read_key 7 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT (SELECT MAX(t2.f4) FROM t2 WHERE t2.f3=t1.f1) FROM t1; @@ -2699,7 +2653,6 @@ Handler_read_key 9 Handler_read_rnd_next 9 # Status of testing query execution: Variable_name Value -Handler_read_key 7 Handler_read_rnd_next 9 Handler_update 2 @@ -2723,9 +2676,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); @@ -2749,7 +2702,6 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t12 ALL NULL NULL NULL NULL 2 100.00 # Status of EXPLAIN EXTENDED query Variable_name Value -Handler_read_key 2 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM v1 WHERE a > 0; @@ -2769,7 +2721,6 @@ Handler_read_key 2 Handler_read_rnd_next 6 # Status of testing query execution: Variable_name Value -Handler_read_key 2 Handler_read_rnd 1 Handler_read_rnd_deleted 1 Handler_read_rnd_next 8 @@ -2794,7 +2745,6 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t12 ALL NULL NULL NULL NULL 2 100.00 # Status of EXPLAIN EXTENDED query Variable_name Value -Handler_read_key 2 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1, v1 WHERE t1.a = v1.a; @@ -2815,7 +2765,6 @@ Handler_read_key 2 Handler_read_rnd_next 9 # Status of testing query execution: Variable_name Value -Handler_read_key 2 Handler_read_rnd 2 Handler_read_rnd_deleted 1 Handler_read_rnd_next 18 @@ -3086,7 +3035,6 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using filesort # Status of EXPLAIN EXTENDED query Variable_name Value -Handler_read_key 4 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x); @@ -3110,7 +3058,7 @@ Sort_rows 3 Sort_scan 1 # Status of testing query execution: Variable_name Value -Handler_read_key 7 +Handler_read_key 3 Handler_read_rnd_next 8 Handler_update 1 Sort_priority_queue_sorts 1 @@ -3139,7 +3087,6 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using filesort # Status of EXPLAIN EXTENDED query Variable_name Value -Handler_read_key 4 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1, t2 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x); @@ -3164,7 +3111,7 @@ Sort_rows 3 Sort_scan 1 # Status of testing query execution: Variable_name Value -Handler_read_key 7 +Handler_read_key 3 Handler_read_rnd_next 8 Sort_priority_queue_sorts 1 Sort_rows 3 @@ -3194,7 +3141,6 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 # Status of EXPLAIN EXTENDED query Variable_name Value -Handler_read_key 4 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1, (SELECT * FROM t2) y WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x); @@ -3219,7 +3165,7 @@ Sort_rows 3 Sort_scan 1 # Status of testing query execution: Variable_name Value -Handler_read_key 7 +Handler_read_key 3 Handler_read_rnd_next 8 Sort_priority_queue_sorts 1 Sort_rows 3 @@ -3271,7 +3217,6 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 100.00 Using where; Using buffer # Status of EXPLAIN EXTENDED query Variable_name Value -Handler_read_key 3 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT a t1 FROM t1 WHERE a>10; @@ -3289,7 +3234,7 @@ Variable_name Value Handler_read_key 4 # Status of testing query execution: Variable_name Value -Handler_read_key 4 +Handler_read_key 1 # used key is modified & Using filesort # @@ -3308,7 +3253,6 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 100.00 Using where; Using filesort # Status of EXPLAIN EXTENDED query Variable_name Value -Handler_read_key 3 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT a t1 FROM t1 WHERE a>10 ORDER BY a+20; @@ -3327,7 +3271,7 @@ Handler_read_key 4 Sort_range 1 # Status of testing query execution: Variable_name Value -Handler_read_key 4 +Handler_read_key 1 Sort_range 1 DROP TABLE t1; diff --git a/mysql-test/main/mysqlbinlog_row_minimal.test b/mysql-test/main/mysqlbinlog_row_minimal.test index 67fa7b9..0f64bba 100644 --- a/mysql-test/main/mysqlbinlog_row_minimal.test +++ b/mysql-test/main/mysqlbinlog_row_minimal.test @@ -99,4 +99,3 @@ FLUSH BINARY LOGS; --exec $MYSQL_BINLOG --verbose --verbose --base64-output=DECODE-ROWS $datadir/$binlog --start-position=$binlog_pos --stop-position=$binlog_end DROP TABLE t1,t2; - diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test index e0c6572..a73988c 100644 --- a/mysql-test/main/opt_trace.test +++ b/mysql-test/main/opt_trace.test @@ -1,6 +1,5 @@ --source include/not_embedded.inc --source include/have_sequence.inc ---source include/protocol.inc SELECT table_name, column_name FROM information_schema.columns where table_name="OPTIMIZER_TRACE"; show variables like 'optimizer_trace'; set optimizer_trace="enabled=on"; diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result index e9f4b1d..c383eb1 100644 --- a/mysql-test/main/order_by.result +++ b/mysql-test/main/order_by.result @@ -982,13 +982,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/partition_explicit_prune.result b/mysql-test/main/partition_explicit_prune.result index 5b3049c..a0b7db8 100644 --- a/mysql-test/main/partition_explicit_prune.result +++ b/mysql-test/main/partition_explicit_prune.result @@ -777,7 +777,6 @@ SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 1 -HANDLER_READ_KEY 8 HANDLER_READ_RND_NEXT 2 HANDLER_TMP_WRITE 24 HANDLER_UPDATE 2 diff --git a/mysql-test/main/sp.result b/mysql-test/main/sp.result index c460eca..fcb7e3e 100644 --- a/mysql-test/main/sp.result +++ b/mysql-test/main/sp.result @@ -7731,7 +7731,7 @@ UPDATE t1 SET a = '+' WHERE daynum=tdn(); SHOW STATUS LIKE '%Handler_read%'; Variable_name Value Handler_read_first 0 -Handler_read_key 9 +Handler_read_key 2 Handler_read_last 0 Handler_read_next 4097 Handler_read_prev 0 diff --git a/mysql-test/main/update.result b/mysql-test/main/update.result index f5edf1c..15efd7e 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 9e43b54..c70c975 100644 --- a/mysql-test/main/update_use_source.result +++ b/mysql-test/main/update_use_source.result @@ -316,7 +316,7 @@ rollback; # explain update t1 set c1=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 > 3; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range t1_c2 t1_c2 5 NULL 2 Using where +1 PRIMARY t1 range t1_c2 t1_c2 5 NULL 2 Using index condition; Using where 2 DEPENDENT SUBQUERY a ref t1_c2 t1_c2 5 test.t1.c2 4 Using index start transaction; update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3; @@ -557,7 +557,7 @@ rollback; # explain update t1 set c1=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 > 3; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range t1_c2 t1_c2 5 NULL 2 Using where +1 PRIMARY t1 range t1_c2 t1_c2 5 NULL 2 Using index condition; Using where 2 DEPENDENT SUBQUERY a ref t1_c2 t1_c2 5 test.t1.c2 1 Using index start transaction; update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3; @@ -799,7 +799,7 @@ rollback; # explain update t1 set c1=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 > 3; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range t1_c2 t1_c2 5 NULL 2 Using where +1 PRIMARY t1 range t1_c2 t1_c2 5 NULL 2 Using index condition; Using where 2 DEPENDENT SUBQUERY a ref t1_c2 t1_c2 5 test.t1.c2 1 Using index start transaction; update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3; @@ -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 7ed5f95..832c03d 100644 --- a/mysql-test/main/update_use_source.test +++ b/mysql-test/main/update_use_source.test @@ -237,7 +237,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 +# --error ER_BAD_FIELD_ERROR 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 c31ba88..6167c1f 100644 --- a/mysql-test/main/view_grant.result +++ b/mysql-test/main/view_grant.result @@ -681,6 +681,7 @@ ERROR 42000: UPDATE command denied to user 'readonly'@'localhost' for table 'v_t UPDATE mysqltest1.v_ts SET x= 200; ERROR 42000: UPDATE command denied to user 'readonly'@'localhost' for table '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 83bbeb3..538342c 100644 --- a/mysql-test/main/view_grant.test +++ b/mysql-test/main/view_grant.test @@ -810,6 +810,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; diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index 859ee5f..09a2bd2 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -693,6 +693,8 @@ int check_and_do_in_subquery_rewrites(JOIN *join) !join->having && !select_lex->with_sum_func && // 4 in_subs->emb_on_expr_nest && // 5 select_lex->outer_select()->join && // 6 + (!thd->lex->m_sql_cmd || + thd->lex->m_sql_cmd->sql_command_code() == SQLCOM_UPDATE_MULTI) && parent_unit->first_select()->leaf_tables.elements && // 7 !in_subs->has_strategy() && // 8 select_lex->outer_select()->table_list.first && // 9 @@ -717,6 +719,15 @@ int check_and_do_in_subquery_rewrites(JOIN *join) if (arena) thd->restore_active_arena(arena, &backup); in_subs->is_registered_semijoin= TRUE; + } + + /* + Print the transformation into trace. Do it when we've just set + is_registered_semijoin=TRUE above, and also do it when we've already + had it set. + */ + if (in_subs->is_registered_semijoin) + { OPT_TRACE_TRANSFORM(thd, trace_wrapper, trace_transform, select_lex->select_number, "IN (SELECT)", "semijoin"); diff --git a/sql/opt_trace.cc b/sql/opt_trace.cc index ba9220c..80c51eb 100644 --- a/sql/opt_trace.cc +++ b/sql/opt_trace.cc @@ -489,7 +489,8 @@ Opt_trace_start::Opt_trace_start(THD *thd, TABLE_LIST *tbl, !list_has_optimizer_trace_table(tbl) && !sets_var_optimizer_trace(sql_command, set_vars) && !thd->system_thread && - !ctx->disable_tracing_if_required()) + !ctx->disable_tracing_if_required() && + !(thd->lex->context_analysis_only & CONTEXT_ANALYSIS_ONLY_PREPARE)) { ctx->start(thd, tbl, sql_command, query, query_length, query_charset, thd->variables.optimizer_trace_max_mem_size); diff --git a/sql/sql_base.cc b/sql/sql_base.cc index ac22a63..65b1b92 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -1067,7 +1067,9 @@ TABLE_LIST* find_dup_table(THD *thd, TABLE_LIST *table, TABLE_LIST *table_list, (table->table equal to 0) and right names is in current TABLE_LIST object. */ - if (table->table) + if (table->table && + thd->lex->sql_command != SQLCOM_UPDATE && + thd->lex->sql_command != SQLCOM_UPDATE_MULTI) { /* All MyISAMMRG children are plain MyISAM tables. */ DBUG_ASSERT(table->table->file->ht->db_type != DB_TYPE_MRG_MYISAM); @@ -5380,6 +5382,28 @@ bool open_tables_only_view_structure(THD *thd, TABLE_LIST *table_list, } +bool open_tables_for_query(THD *thd, TABLE_LIST *tables, + uint *table_count, uint flags, + DML_prelocking_strategy *prelocking_strategy) +{ + MDL_savepoint mdl_savepoint = thd->mdl_context.mdl_savepoint(); + + DBUG_ASSERT(tables == thd->lex->query_tables); + + if (open_tables(thd, &tables, table_count, + thd->stmt_arena->is_stmt_prepare() ? MYSQL_OPEN_FORCE_SHARED_MDL : 0, + prelocking_strategy)) + { + close_thread_tables(thd); + /* Don't keep locks for a failed statement. */ + thd->mdl_context.rollback_to_savepoint(mdl_savepoint); + return true; + } + + return false; +} + + /* Mark all real tables in the list as free for reuse. diff --git a/sql/sql_base.h b/sql/sql_base.h index cafb596..ae3fb62 100644 --- a/sql/sql_base.h +++ b/sql/sql_base.h @@ -28,6 +28,7 @@ struct Name_resolution_context; class Open_table_context; class Open_tables_state; class Prelocking_strategy; +class DML_prelocking_strategy; struct TABLE_LIST; class THD; struct handlerton; @@ -287,6 +288,9 @@ bool open_normal_and_derived_tables(THD *thd, TABLE_LIST *tables, uint flags, bool open_tables_only_view_structure(THD *thd, TABLE_LIST *tables, bool can_deadlock); bool open_and_lock_internal_tables(TABLE *table, bool lock); +bool open_tables_for_query(THD *thd, TABLE_LIST *tables, + uint *table_count, uint flags, + DML_prelocking_strategy *prelocking_strategy); bool lock_tables(THD *thd, TABLE_LIST *tables, uint counter, uint flags); int decide_logging_format(THD *thd, TABLE_LIST *tables); void close_thread_table(THD *thd, TABLE **table_ptr); @@ -429,6 +433,17 @@ class DML_prelocking_strategy : public Prelocking_strategy }; + +class Multiupdate_prelocking_strategy : public DML_prelocking_strategy +{ + bool done; + bool has_prelocking_list; +public: + void reset(THD *thd); + bool handle_end(THD *thd); +}; + + /** A strategy for prelocking algorithm to be used for LOCK TABLES statement. diff --git a/sql/sql_class.h b/sql/sql_class.h index bc9bb82..2dd584e 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -7000,6 +7000,7 @@ class multi_update :public select_result_interceptor enum_duplicates handle_duplicates, bool ignore); ~multi_update(); bool init(THD *thd); + bool init_for_single_table(THD *thd); int prepare(List<Item> &list, SELECT_LEX_UNIT *u); int send_data(List<Item> &items); bool initialize_tables (JOIN *join); diff --git a/sql/sql_cmd.h b/sql/sql_cmd.h index ce34852..a5557c6 100644 --- a/sql/sql_cmd.h +++ b/sql/sql_cmd.h @@ -118,6 +118,7 @@ enum enum_sql_command { SQLCOM_END }; +class TABLE_LIST; class Storage_engine_name { @@ -144,6 +145,8 @@ class Storage_engine_name }; +class Prepared_statement; + /** @class Sql_cmd - Representation of an SQL command. @@ -179,6 +182,25 @@ class Sql_cmd : public Sql_alloc */ virtual enum_sql_command sql_command_code() const = 0; + /// @return true if this statement is prepared + bool is_prepared() const { return m_prepared; } + + /** + Prepare this SQL statement. + @param thd the current thread + @returns false if success, true if error + @retval false on success. + @retval true on error + */ + + virtual bool prepare(THD *thd) + { + /* Default behavior for a statement is to have no preparation code. */ + DBUG_ASSERT(!is_prepared()); + set_prepared(); + return false; + } + /** Execute this SQL statement. @param thd the current thread. @@ -192,8 +214,28 @@ class Sql_cmd : public Sql_alloc return NULL; } + /// Set the owning prepared statement + void set_owner(Prepared_statement *stmt) { m_owner = stmt; } + + /// Get the owning prepared statement + Prepared_statement *get_owner() { return m_owner; } + + /// @return true if SQL command is a DML statement + virtual bool is_dml() const { return false; } + + /** + Temporary function used to "unprepare" a prepared statement after + preparation, so that a subsequent execute statement will reprepare it. + This is done because UNIT::cleanup() will un-resolve all resolved QBs. + */ + virtual void unprepare(THD *thd) + { + DBUG_ASSERT(is_prepared()); + m_prepared = false; + } + protected: - Sql_cmd() + Sql_cmd() : m_prepared(false) {} virtual ~Sql_cmd() @@ -206,8 +248,115 @@ class Sql_cmd : public Sql_alloc */ DBUG_ASSERT(FALSE); } + + /// Set this statement as prepared + void set_prepared() { m_prepared = true; } + + private: + Prepared_statement + *m_owner; /// Owning prepared statement, nullptr if non-prep. + bool m_prepared; /// True when statement has been prepared + +}; + +class LEX; +class select_result; +class Prelocking_strategy; +class DML_prelocking_strategy; + +class Sql_cmd_dml : public Sql_cmd +{ +public: + /// @return true if data change statement, false if not (SELECT statement) + virtual bool is_data_change_stmt() const { return true; } + + /** + Command-specific resolving (doesn't include LEX::prepare()) + + @param thd Current THD. + @returns false on success, true on error + */ + virtual bool prepare(THD *thd); + + /** + Execute this query once + + @param thd Thread handler + @returns false on success, true on error + */ + virtual bool execute(THD *thd); + + virtual bool is_dml() const { return true; } + +protected: + Sql_cmd_dml() + : Sql_cmd(), lex(nullptr), result(nullptr), m_empty_query(false) {} + + /// @return true if query is guaranteed to return no data + /** + @todo Also check this for the following cases: + - Empty source for multi-table UPDATE and DELETE. + - Check empty query expression for INSERT + */ + bool is_empty_query() const + { + DBUG_ASSERT(is_prepared()); + return m_empty_query; + } + + /// Set statement as returning no data + void set_empty_query() { m_empty_query = true; } + + /** + Perform a precheck of table privileges for the specific operation. + + @details + Check that user has some relevant privileges for all tables involved in + the statement, e.g. SELECT privileges for tables selected from, INSERT + privileges for tables inserted into, etc. This function will also populate + TABLE_LIST::grant with all privileges the user has for each table, which + is later used during checking of column privileges. + Note that at preparation time, views are not expanded yet. Privilege + checking is thus rudimentary and must be complemented with later calls to + SELECT_LEX::check_view_privileges(). + The reason to call this function at such an early stage is to be able to + quickly reject statements for which the user obviously has insufficient + privileges. + + @param thd thread handler + @returns false if success, true if false + */ + virtual bool precheck(THD *thd) = 0; + + /** + Perform the command-specific parts of DML command preparation, + to be called from prepare() + + @param thd the current thread + @returns false if success, true if error + */ + virtual bool prepare_inner(THD *thd) = 0; + + /** + The inner parts of query optimization and execution. + Single-table DML operations needs to reimplement this. + + @param thd Thread handler + @returns false on success, true on error + */ + virtual bool execute_inner(THD *thd); + + virtual DML_prelocking_strategy *get_dml_prelocking_strategy() = 0; + + uint table_count; + + protected: + LEX *lex; ///< Pointer to LEX for this statement + select_result *result; ///< Pointer to object for handling of the result + bool m_empty_query; ///< True if query will produce no rows }; + class Sql_cmd_show_slave_status: public Sql_cmd { protected: diff --git a/sql/sql_delete.h b/sql/sql_delete.h index 520524c..dabcafb 100644 --- a/sql/sql_delete.h +++ b/sql/sql_delete.h @@ -32,4 +32,28 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds, SQL_I_List<ORDER> *order, ha_rows rows, ulonglong options, select_result *result); +class Sql_cmd_delete final : public Sql_cmd_dml +{ +public: + Sql_cmd_delete(bool multitable_arg) + : multitable(multitable_arg) {} + + enum_sql_command sql_command_code() const override + { + return multitable ? SQLCOM_DELETE_MULTI : SQLCOM_DELETE; + } + +protected: + bool precheck(THD *thd) override; + + bool prepare_inner(THD *thd) override; + + bool execute_inner(THD *thd) override; + + private: + bool delete_from_single_table(THD *thd); + + bool multitable; + +}; #endif /* SQL_DELETE_INCLUDED */ diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index c3f97fb..47d9479 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -1321,6 +1321,8 @@ void LEX::start(THD *thd_arg) wild= 0; exchange= 0; + table_count= 0; + DBUG_VOID_RETURN; } @@ -3051,6 +3053,7 @@ void st_select_lex::init_select() curr_tvc_name= 0; versioned_tables= 0; nest_flags= 0; + item_list_usage= MARK_COLUMNS_READ; } /* @@ -4115,6 +4118,12 @@ bool LEX::can_not_use_merged(bool no_update_or_delete) return TRUE; /* Fall through */ + case SQLCOM_UPDATE: + if (no_update_or_delete && m_sql_cmd && + (m_sql_cmd->sql_command_code() == SQLCOM_UPDATE_MULTI || + query_tables->is_multitable())) + return TRUE; + default: return FALSE; } diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 0d82519..8f46e9f 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -36,6 +36,7 @@ #include "sql_limit.h" // Select_limit_counters #include "json_table.h" // Json_table_column #include "sql_schema.h" +#include "sql_class.h" // enum enum_column_usage /* Used for flags of nesting constructs */ #define SELECT_NESTING_MAP_SIZE 64 @@ -874,6 +875,8 @@ class st_select_lex_unit: public st_select_lex_node { { } + void set_query_result(select_result *res) { result= res; } + TABLE *table; /* temporary table using for appending UNION results */ select_result *result; st_select_lex *pre_last_parse; @@ -1006,6 +1009,7 @@ class st_select_lex_unit: public st_select_lex_node { bool add_fake_select_lex(THD *thd); void init_prepare_fake_select_lex(THD *thd, bool first_execution); + void set_prepared() { prepared = true; } inline bool is_prepared() { return prepared; } bool change_result(select_result_interceptor *result, select_result_interceptor *old_result); @@ -1121,6 +1125,7 @@ class st_select_lex: public st_select_lex_node Item *prep_having;/* saved HAVING clause for prepared statement processing */ Item *cond_pushed_into_where; /* condition pushed into WHERE */ Item *cond_pushed_into_having; /* condition pushed into HAVING */ + Item *where_cond_after_prepare; /* nest_levels are local to the query or VIEW, @@ -1229,6 +1234,7 @@ class st_select_lex: public st_select_lex_node List<List_item> save_many_values; List<Item> *save_insert_list; + enum_column_usage item_list_usage; bool is_item_list_lookup:1; /* Needed to correctly generate 'PRIMARY' or 'SIMPLE' for select_type column @@ -1759,6 +1765,25 @@ class Query_tables_list uint sroutines_list_own_elements; /** + Locking state of tables in this particular statement. + + If we under LOCK TABLES or in prelocked mode we consider tables + for the statement to be "locked" if there was a call to lock_tables() + (which called handler::start_stmt()) for tables of this statement + and there was no matching close_thread_tables() call. + + As result this state may differ significantly from one represented + by Open_tables_state::lock/locked_tables_mode more, which are always + "on" under LOCK TABLES or in prelocked mode. + */ + enum enum_lock_tables_state { LTS_NOT_LOCKED = 0, LTS_LOCKED }; + enum_lock_tables_state lock_tables_state; + bool is_query_tables_locked() const + { + return (lock_tables_state == LTS_LOCKED); + } + + /** Number of tables which were open by open_tables() and to be locked by lock_tables(). Note that we set this member only in some cases, when this value @@ -3398,6 +3423,7 @@ struct LEX: public Query_tables_list bool default_used:1; /* using default() function */ bool with_rownum:1; /* Using rownum() function */ bool is_lex_started:1; /* If lex_start() did run. For debugging. */ + /* This variable is used in post-parse stage to declare that sum-functions, or functions which have sense only if GROUP BY is present, are allowed. diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 2c6df6d..47d42f9 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -3445,7 +3445,6 @@ int mysql_execute_command(THD *thd, bool is_called_from_prepared_stmt) { int res= 0; - int up_result= 0; LEX *lex= thd->lex; /* first SELECT_LEX (have special meaning for many of non-SELECTcommands) */ SELECT_LEX *select_lex= lex->first_select_lex(); @@ -3457,7 +3456,6 @@ mysql_execute_command(THD *thd, bool is_called_from_prepared_stmt) SELECT_LEX_UNIT *unit= &lex->unit; #ifdef HAVE_REPLICATION /* have table map for update for multi-update statement (BUG#37051) */ - bool have_table_map_for_update= FALSE; /* */ Rpl_filter *rpl_filter; #endif @@ -3579,7 +3577,6 @@ mysql_execute_command(THD *thd, bool is_called_from_prepared_stmt) if (lex->sql_command == SQLCOM_UPDATE_MULTI && thd->table_map_for_update) { - have_table_map_for_update= TRUE; table_map table_map_for_update= thd->table_map_for_update; uint nr= 0; TABLE_LIST *table; @@ -4385,130 +4382,13 @@ mysql_execute_command(THD *thd, bool is_called_from_prepared_stmt) break; } case SQLCOM_UPDATE: - { - WSREP_SYNC_WAIT(thd, WSREP_SYNC_WAIT_BEFORE_UPDATE_DELETE); - ha_rows found= 0, updated= 0; - DBUG_ASSERT(first_table == all_tables && first_table != 0); - WSREP_SYNC_WAIT(thd, WSREP_SYNC_WAIT_BEFORE_UPDATE_DELETE); - - if (update_precheck(thd, all_tables)) - break; - - /* - UPDATE IGNORE can be unsafe. We therefore use row based - logging if mixed or row based logging is available. - TODO: Check if the order of the output of the select statement is - deterministic. Waiting for BUG#42415 - */ - if (lex->ignore) - lex->set_stmt_unsafe(LEX::BINLOG_STMT_UNSAFE_UPDATE_IGNORE); - - DBUG_ASSERT(select_lex->limit_params.offset_limit == 0); - unit->set_limit(select_lex); - MYSQL_UPDATE_START(thd->query()); - res= up_result= mysql_update(thd, all_tables, - select_lex->item_list, - lex->value_list, - select_lex->where, - select_lex->order_list.elements, - select_lex->order_list.first, - unit->lim.get_select_limit(), - lex->ignore, &found, &updated); - MYSQL_UPDATE_DONE(res, found, updated); - /* mysql_update return 2 if we need to switch to multi-update */ - if (up_result != 2) - break; - if (thd->lex->period_conditions.is_set()) - { - DBUG_ASSERT(0); // Should never happen - goto error; - } - } - /* fall through */ case SQLCOM_UPDATE_MULTI: { DBUG_ASSERT(first_table == all_tables && first_table != 0); - /* if we switched from normal update, rights are checked */ - if (up_result != 2) - { - WSREP_SYNC_WAIT(thd, WSREP_SYNC_WAIT_BEFORE_UPDATE_DELETE); - if ((res= multi_update_precheck(thd, all_tables))) - break; - } - else - res= 0; - - unit->set_limit(select_lex); - /* - We can not use mysql_explain_union() because of parameters of - mysql_select in mysql_multi_update so just set the option if needed - */ - if (thd->lex->describe) - { - select_lex->set_explain_type(FALSE); - select_lex->options|= SELECT_DESCRIBE; - } - - res= mysql_multi_update_prepare(thd); - -#ifdef HAVE_REPLICATION - /* Check slave filtering rules */ - if (unlikely(thd->slave_thread && !have_table_map_for_update)) - { - if (all_tables_not_ok(thd, all_tables)) - { - if (res!= 0) - { - res= 0; /* don't care of prev failure */ - thd->clear_error(); /* filters are of highest prior */ - } - /* we warn the slave SQL thread */ - my_error(ER_SLAVE_IGNORED_TABLE, MYF(0)); - break; - } - if (res) - break; - } - else - { -#endif /* HAVE_REPLICATION */ - if (res) - break; - if (opt_readonly && - !(thd->security_ctx->master_access & PRIV_IGNORE_READ_ONLY) && - some_non_temp_table_to_be_updated(thd, all_tables)) - { - my_error(ER_OPTION_PREVENTS_STATEMENT, MYF(0), "--read-only"); - break; - } -#ifdef HAVE_REPLICATION - } /* unlikely */ -#endif - { - multi_update *result_obj; - MYSQL_MULTI_UPDATE_START(thd->query()); - res= mysql_multi_update(thd, all_tables, - &select_lex->item_list, - &lex->value_list, - select_lex->where, - select_lex->options, - lex->duplicates, - lex->ignore, - unit, - select_lex, - &result_obj); - if (result_obj) - { - MYSQL_MULTI_UPDATE_DONE(res, result_obj->num_found(), - result_obj->num_updated()); - res= FALSE; /* Ignore errors here */ - delete result_obj; - } - else - { - MYSQL_MULTI_UPDATE_DONE(1, 0, 0); - } - } + DBUG_ASSERT(lex->m_sql_cmd != NULL); + thd->abort_on_warning= !thd->lex->ignore && thd->is_strict_mode(); + res = lex->m_sql_cmd->execute(thd); + thd->abort_on_warning= 0; break; } case SQLCOM_REPLACE: diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc index e7d02aa..87ee207 100644 --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -98,7 +98,6 @@ When one supplies long data for a placeholder: #include "sql_delete.h" // mysql_prepare_delete #include "sql_select.h" // for JOIN #include "sql_insert.h" // upgrade_lock_type_for_insert, mysql_prepare_insert -#include "sql_update.h" // mysql_prepare_update #include "sql_db.h" // mysql_opt_change_db, mysql_change_db #include "sql_derived.h" // mysql_derived_prepare, // mysql_handle_derived @@ -1398,110 +1397,6 @@ static bool mysql_test_insert(Prepared_statement *stmt, /** - Validate UPDATE statement. - - @param stmt prepared statement - @param tables list of tables used in this query - - @todo - - here we should send types of placeholders to the client. - - @retval - 0 success - @retval - 1 error, error message is set in THD - @retval - 2 convert to multi_update -*/ - -static int mysql_test_update(Prepared_statement *stmt, - TABLE_LIST *table_list) -{ - int res; - THD *thd= stmt->thd; - uint table_count= 0; - TABLE_LIST *update_source_table; - SELECT_LEX *select= stmt->lex->first_select_lex(); -#ifndef NO_EMBEDDED_ACCESS_CHECKS - privilege_t want_privilege(NO_ACL); -#endif - DBUG_ENTER("mysql_test_update"); - - if (update_precheck(thd, table_list) || - open_tables(thd, &table_list, &table_count, MYSQL_OPEN_FORCE_SHARED_MDL)) - goto error; - - if (mysql_handle_derived(thd->lex, DT_INIT)) - goto error; - - if (((update_source_table= unique_table(thd, table_list, - table_list->next_global, 0)) || - table_list->is_multitable())) - { - DBUG_ASSERT(update_source_table || table_list->view != 0); - DBUG_PRINT("info", ("Switch to multi-update")); - /* pass counter value */ - thd->lex->table_count= table_count; - /* convert to multiupdate */ - DBUG_RETURN(2); - } - - /* - thd->fill_derived_tables() is false here for sure (because it is - preparation of PS, so we even do not check it). - */ - if (table_list->handle_derived(thd->lex, DT_MERGE_FOR_INSERT)) - goto error; - if (table_list->handle_derived(thd->lex, DT_PREPARE)) - goto error; - - if (!table_list->single_table_updatable()) - { - my_error(ER_NON_UPDATABLE_TABLE, MYF(0), table_list->alias.str, "UPDATE"); - goto error; - } - -#ifndef NO_EMBEDDED_ACCESS_CHECKS - /* Force privilege re-checking for views after they have been opened. */ - want_privilege= (table_list->view ? UPDATE_ACL : - table_list->grant.want_privilege); -#endif - - if (mysql_prepare_update(thd, table_list, &select->where, - select->order_list.elements, - select->order_list.first)) - goto error; - -#ifndef NO_EMBEDDED_ACCESS_CHECKS - table_list->grant.want_privilege= want_privilege; - table_list->table->grant.want_privilege= want_privilege; - table_list->register_want_access(want_privilege); -#endif - thd->lex->first_select_lex()->no_wrap_view_item= TRUE; - res= setup_fields(thd, Ref_ptr_array(), - select->item_list, MARK_COLUMNS_READ, 0, NULL, 0); - thd->lex->first_select_lex()->no_wrap_view_item= FALSE; - if (res) - goto error; -#ifndef NO_EMBEDDED_ACCESS_CHECKS - /* Check values */ - table_list->grant.want_privilege= - table_list->table->grant.want_privilege= - (SELECT_ACL & ~table_list->table->grant.privilege); - table_list->register_want_access(SELECT_ACL); -#endif - if (setup_fields(thd, Ref_ptr_array(), - stmt->lex->value_list, COLUMNS_READ, 0, NULL, 0) || - check_unique_table(thd, table_list)) - goto error; - /* TODO: here we should send types of placeholders to the client. */ - DBUG_RETURN(0); -error: - DBUG_RETURN(1); -} - - -/** Validate DELETE statement. @param stmt prepared statement @@ -2128,32 +2023,6 @@ static bool mysql_test_create_view(Prepared_statement *stmt) } -/* - Validate and prepare for execution a multi update statement. - - @param stmt prepared statement - @param tables list of tables used in this query - @param converted converted to multi-update from usual update - - @retval - FALSE success - @retval - TRUE error, error message is set in THD -*/ - -static bool mysql_test_multiupdate(Prepared_statement *stmt, - TABLE_LIST *tables, - bool converted) -{ - /* if we switched from normal update, rights are checked */ - if (!converted && multi_update_precheck(stmt->thd, tables)) - return TRUE; - - return select_like_stmt_test(stmt, &mysql_multi_update_prepare, - OPTION_SETUP_TABLES_DONE); -} - - /** Validate and prepare for execution a multi delete statement. @@ -2473,13 +2342,10 @@ static bool check_prepared_statement(Prepared_statement *stmt) break; case SQLCOM_UPDATE: - res= mysql_test_update(stmt, tables); - /* mysql_test_update returns 2 if we need to switch to multi-update */ - if (res != 2) - break; - /* fall through */ case SQLCOM_UPDATE_MULTI: - res= mysql_test_multiupdate(stmt, tables, res == 2); + res = lex->m_sql_cmd->prepare(thd); + if (!res) + lex->m_sql_cmd->unprepare(thd); break; case SQLCOM_DELETE: @@ -4347,6 +4213,9 @@ bool Prepared_statement::prepare(const char *packet, uint packet_len) thd->is_error() || init_param_array(this)); + if (lex->m_sql_cmd) + lex->m_sql_cmd->set_owner(this); + if (thd->security_ctx->password_expired && lex->sql_command != SQLCOM_SET_OPTION && lex->sql_command != SQLCOM_PREPARE && diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 80d9cafe..47ac7c8 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1393,7 +1393,7 @@ JOIN::prepare(TABLE_LIST *tables_init, COND *conds_init, uint og_num, } } - if (setup_fields(thd, ref_ptrs, fields_list, MARK_COLUMNS_READ, + if (setup_fields(thd, ref_ptrs, fields_list, select_lex->item_list_usage, &all_fields, &select_lex->pre_fix, 1)) DBUG_RETURN(-1); thd->lex->current_select->context_analysis_place= save_place; @@ -1683,6 +1683,8 @@ JOIN::prepare(TABLE_LIST *tables_init, COND *conds_init, uint og_num, if (!procedure && result && result->prepare(fields_list, unit_arg)) goto err; /* purecov: inspected */ + select_lex->where_cond_after_prepare= conds; + unit= unit_arg; if (prepare_stage2()) goto err; @@ -30152,6 +30154,140 @@ static bool process_direct_rownum_comparison(THD *thd, SELECT_LEX_UNIT *unit, } +bool Sql_cmd_dml::prepare(THD *thd) +{ + lex= thd->lex; + SELECT_LEX_UNIT *unit= &lex->unit; + + DBUG_ASSERT(!is_prepared()); + + // Perform a coarse statement-specific privilege check. + if (precheck(thd)) + goto err; + + lex->context_analysis_only|= CONTEXT_ANALYSIS_ONLY_DERIVED; + + if (open_tables_for_query(thd, lex->query_tables, &table_count, 0, + get_dml_prelocking_strategy())) + { + if (thd->is_error()) + goto err; + (void)unit->cleanup(); + return true; + } + + if (prepare_inner(thd)) + goto err; + + lex->context_analysis_only&= ~CONTEXT_ANALYSIS_ONLY_DERIVED; + + set_prepared(); + unit->set_prepared(); + + return false; + +err: + DBUG_ASSERT(thd->is_error()); + DBUG_PRINT("info", ("report_error: %d", thd->is_error())); + + (void)unit->cleanup(); + + return true; +} + +bool Sql_cmd_dml::execute(THD *thd) +{ + lex = thd->lex; + bool res; + + SELECT_LEX_UNIT *unit = &lex->unit; + SELECT_LEX *select_lex= lex->first_select_lex(); + + if (!is_prepared()) + { + if (prepare(thd)) + goto err; + } + else + { + if (precheck(thd)) + goto err; + if (open_tables_for_query(thd, lex->query_tables, &table_count, 0, + get_dml_prelocking_strategy())) + goto err; + } + + THD_STAGE_INFO(thd, stage_init); + + DBUG_ASSERT(!lex->is_query_tables_locked()); + /* + Locking of tables is done after preparation but before optimization. + This allows to do better partition pruning and avoid locking unused + partitions. As a consequence, in such a case, prepare stage can rely only + on metadata about tables used and not data from them. + */ + if (!is_empty_query()) + { + if (lock_tables(thd, lex->query_tables, table_count, 0)) + goto err; + } + + unit->set_limit(select_lex); + + // Perform statement-specific execution + res = execute_inner(thd); + + if (res) + goto err; + + res= unit->cleanup(); + + // "unprepare" this object since unit->cleanup actually unprepares + unprepare(thd); + + THD_STAGE_INFO(thd, stage_end); + + return res; + +err: + DBUG_ASSERT(thd->is_error() || thd->killed); + THD_STAGE_INFO(thd, stage_end); + (void)unit->cleanup(); + + return thd->is_error(); +} + + +bool Sql_cmd_dml::execute_inner(THD *thd) +{ + SELECT_LEX_UNIT *unit = &lex->unit; + SELECT_LEX *select_lex= unit->first_select(); + JOIN *join= select_lex->join; + + if (join->optimize()) + goto err; + + if (thd->lex->describe & DESCRIBE_EXTENDED) + { + join->conds_history= join->conds; + join->having_history= (join->having?join->having:join->tmp_having); + } + + if (unlikely(thd->is_error())) + goto err; + + join->exec(); + + if (thd->lex->describe & DESCRIBE_EXTENDED) + { + select_lex->where= join->conds_history; + select_lex->having= join->having_history; + } + +err: + return join->error; +} + /** @} (end of group Query_Optimizer) diff --git a/sql/sql_update.cc b/sql/sql_update.cc index 0717148..1877194 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -339,36 +339,19 @@ int cut_fields_for_portion_of_time(THD *thd, TABLE *table, return res; } -/* - Process usual UPDATE - - SYNOPSIS - mysql_update() - thd thread handler - fields fields for update - values values of fields for update - conds WHERE clause expression - order_num number of elemen in ORDER BY clause - order ORDER BY clause list - limit limit clause - - RETURN - 0 - OK - 2 - privilege check and openning table passed, but we need to convert to - multi-update because of view substitution - 1 - error -*/ -int mysql_update(THD *thd, - TABLE_LIST *table_list, - List<Item> &fields, - List<Item> &values, - COND *conds, - uint order_num, ORDER *order, - ha_rows limit, - bool ignore, - ha_rows *found_return, ha_rows *updated_return) +bool Sql_cmd_update::update_single_table(THD *thd) { + SELECT_LEX_UNIT *unit = &lex->unit; + SELECT_LEX *select_lex= unit->first_select(); + TABLE_LIST *const table_list = select_lex->get_table_list(); + List<Item> *fields= &select_lex->item_list; + List<Item> *values= &lex->value_list; + COND *conds= select_lex->where_cond_after_prepare; + ORDER *order= select_lex->order_list.first; + ha_rows limit= unit->lim.get_select_limit(); + bool ignore= lex->ignore; + bool using_limit= limit != HA_POS_ERROR; bool safe_update= thd->variables.option_bits & OPTION_SAFE_UPDATES; bool used_key_is_modified= FALSE, transactional_table; @@ -379,76 +362,39 @@ int mysql_update(THD *thd, ha_rows dup_key_found; bool need_sort= TRUE; bool reverse= FALSE; -#ifndef NO_EMBEDDED_ACCESS_CHECKS - privilege_t want_privilege(NO_ACL); -#endif - uint table_count= 0; ha_rows updated, updated_or_same, found; key_map old_covering_keys; - TABLE *table; + TABLE *table; SQL_SELECT *select= NULL; SORT_INFO *file_sort= 0; READ_RECORD info; - SELECT_LEX *select_lex= thd->lex->first_select_lex(); ulonglong id; List<Item> all_fields; killed_state killed_status= NOT_KILLED; bool has_triggers, binlog_is_row, do_direct_update= FALSE; Update_plan query_plan(thd->mem_root); Explain_update *explain; - TABLE_LIST *update_source_table; query_plan.index= MAX_KEY; query_plan.using_filesort= FALSE; // For System Versioning (may need to insert new fields to a table). ha_rows rows_inserted= 0; - DBUG_ENTER("mysql_update"); + DBUG_ENTER("Sql_cmd_update::update_single_table"); + THD_STAGE_INFO(thd, stage_init_update); create_explain_query(thd->lex, thd->mem_root); - if (open_tables(thd, &table_list, &table_count, 0)) - DBUG_RETURN(1); - /* Prepare views so they are handled correctly */ - if (mysql_handle_derived(thd->lex, DT_INIT)) - DBUG_RETURN(1); - - if (table_list->has_period() && table_list->is_view_or_derived()) - { - my_error(ER_IT_IS_A_VIEW, MYF(0), table_list->table_name.str); - DBUG_RETURN(TRUE); - } + thd->table_map_for_update= 0; - if (((update_source_table=unique_table(thd, table_list, - table_list->next_global, 0)) || - table_list->is_multitable())) - { - DBUG_ASSERT(update_source_table || table_list->view != 0); - DBUG_PRINT("info", ("Switch to multi-update")); - /* pass counter value */ - thd->lex->table_count= table_count; - if (thd->lex->period_conditions.is_set()) - { - my_error(ER_NOT_SUPPORTED_YET, MYF(0), - "updating and querying the same temporal periods table"); - - DBUG_RETURN(1); - } - - /* convert to multiupdate */ - DBUG_RETURN(2); - } - if (lock_tables(thd, table_list, table_count, 0)) - DBUG_RETURN(1); - - (void) read_statistics_for_tables_if_needed(thd, table_list); - - THD_STAGE_INFO(thd, stage_init_update); if (table_list->handle_derived(thd->lex, DT_MERGE_FOR_INSERT)) DBUG_RETURN(1); if (table_list->handle_derived(thd->lex, DT_PREPARE)) DBUG_RETURN(1); + if (setup_ftfuncs(select_lex)) + DBUG_RETURN(-1); + table= table_list->table; if (!table_list->single_table_updatable()) @@ -457,85 +403,26 @@ int mysql_update(THD *thd, DBUG_RETURN(1); } - /* Calculate "table->covering_keys" based on the WHERE */ - table->covering_keys= table->s->keys_in_use; table->opt_range_keys.clear_all(); query_plan.select_lex= thd->lex->first_select_lex(); query_plan.table= table; -#ifndef NO_EMBEDDED_ACCESS_CHECKS - /* Force privilege re-checking for views after they have been opened. */ - want_privilege= (table_list->view ? UPDATE_ACL : - table_list->grant.want_privilege); -#endif thd->lex->promote_select_describe_flag_if_needed(); - if (mysql_prepare_update(thd, table_list, &conds, order_num, order)) - DBUG_RETURN(1); - - if (table_list->has_period()) - { - if (!table_list->period_conditions.start.item->const_item() - || !table_list->period_conditions.end.item->const_item()) - { - my_error(ER_NOT_CONSTANT_EXPRESSION, MYF(0), "FOR PORTION OF"); - DBUG_RETURN(true); - } - table->no_cache= true; - } - old_covering_keys= table->covering_keys; // Keys used in WHERE - /* Check the fields we are going to modify */ -#ifndef NO_EMBEDDED_ACCESS_CHECKS - table_list->grant.want_privilege= table->grant.want_privilege= want_privilege; - table_list->register_want_access(want_privilege); -#endif - /* 'Unfix' fields to allow correct marking by the setup_fields function. */ - if (table_list->is_view()) - unfix_fields(fields); - if (setup_fields_with_no_wrap(thd, Ref_ptr_array(), - fields, MARK_COLUMNS_WRITE, 0, 0)) - DBUG_RETURN(1); /* purecov: inspected */ - if (check_fields(thd, table_list, fields, table_list->view)) - { - DBUG_RETURN(1); - } - bool has_vers_fields= table->vers_check_update(fields); - if (check_key_in_view(thd, table_list)) - { - my_error(ER_NON_UPDATABLE_TABLE, MYF(0), table_list->alias.str, "UPDATE"); - DBUG_RETURN(1); - } + bool has_vers_fields= table->vers_check_update(*fields); if (table->default_field) table->mark_default_fields_for_write(false); -#ifndef NO_EMBEDDED_ACCESS_CHECKS - /* Check values */ - table_list->grant.want_privilege= table->grant.want_privilege= - (SELECT_ACL & ~table->grant.privilege); -#endif - if (setup_fields(thd, Ref_ptr_array(), values, MARK_COLUMNS_READ, 0, NULL, 0)) - { - free_underlaid_joins(thd, select_lex); - DBUG_RETURN(1); /* purecov: inspected */ - } - - if (check_unique_table(thd, table_list)) - DBUG_RETURN(TRUE); - - switch_to_nullable_trigger_fields(fields, table); - switch_to_nullable_trigger_fields(values, table); + switch_to_nullable_trigger_fields(*fields, table); + switch_to_nullable_trigger_fields(*values, table); /* Apply the IN=>EXISTS transformation to all subqueries and optimize them */ if (select_lex->optimize_unflattened_subqueries(false)) DBUG_RETURN(TRUE); - if (select_lex->inner_refs_list.elements && - fix_inner_refs(thd, all_fields, select_lex, select_lex->ref_pointer_array)) - DBUG_RETURN(1); - if (conds) { Item::cond_result cond_value; @@ -764,7 +651,7 @@ int mysql_update(THD *thd, if (!table->file->info_push(INFO_KIND_UPDATE_FIELDS, &fields) && !table->file->info_push(INFO_KIND_UPDATE_VALUES, &values) && - !table->file->direct_update_rows_init(&fields)) + !table->file->direct_update_rows_init(fields)) { do_direct_update= TRUE; @@ -1013,7 +900,7 @@ int mysql_update(THD *thd, cut_fields_for_portion_of_time(thd, table, table_list->period_conditions); - if (fill_record_n_invoke_before_triggers(thd, table, fields, values, 0, + if (fill_record_n_invoke_before_triggers(thd, table, *fields, *values, 0, TRG_EVENT_UPDATE)) break; /* purecov: inspected */ @@ -1346,9 +1233,6 @@ int mysql_update(THD *thd, thd->lex->current_select->save_leaf_tables(thd); thd->lex->current_select->first_cond_optimization= 0; } - *found_return= found; - *updated_return= updated; - if (unlikely(thd->lex->analyze_stmt)) goto emit_explain_and_leave; @@ -1676,15 +1560,6 @@ static bool multi_update_check_table_access(THD *thd, TABLE_LIST *table, } -class Multiupdate_prelocking_strategy : public DML_prelocking_strategy -{ - bool done; - bool has_prelocking_list; -public: - void reset(THD *thd); - bool handle_end(THD *thd); -}; - void Multiupdate_prelocking_strategy::reset(THD *thd) { done= false; @@ -1921,7 +1796,7 @@ int mysql_multi_update_prepare(THD *thd) if (lex->save_prep_leaf_tables()) DBUG_RETURN(TRUE); - + DBUG_RETURN (FALSE); } @@ -2015,6 +1890,19 @@ bool multi_update::init(THD *thd) } +bool multi_update::init_for_single_table(THD *thd) +{ + List_iterator_fast<TABLE_LIST> li(*leaves); + TABLE_LIST *tbl; + while ((tbl =li++)) + { + if (updated_leaves.push_back(tbl, thd->mem_root)) + return true; + } + return false; +} + + /* Connect fields with tables and create list of tables that are updated */ @@ -2088,7 +1976,8 @@ int multi_update::prepare(List<Item> ¬_used_values, { table->read_set= &table->def_read_set; bitmap_union(table->read_set, &table->tmp_set); - table->file->prepare_for_insert(1); + if (!(thd->lex->context_analysis_only & CONTEXT_ANALYSIS_ONLY_PREPARE)) + table->file->prepare_for_insert(1); } } if (unlikely(error)) @@ -3106,3 +2995,166 @@ bool multi_update::send_eof() } DBUG_RETURN(FALSE); } + + +bool Sql_cmd_update::precheck(THD *thd) +{ + if (!multitable) + { + if (update_precheck(thd, lex->query_tables)) + return true; + } + else + { + if (multi_update_precheck(thd, lex->query_tables)) + return true; + } + return false; +} + + +bool Sql_cmd_update::prepare_inner(THD *thd) +{ + JOIN *join; + int err= 0; + // uint table_cnt= 0; + SELECT_LEX *const select_lex = thd->lex->first_select_lex(); + TABLE_LIST *const table_list = select_lex->get_table_list(); + ulonglong select_options= select_lex->options; + bool free_join= 1; + // bool orig_multitable= multitable; + DBUG_ENTER("Sql_cmd_update::prepare_inner"); + + if (!multitable) + { + TABLE_LIST *update_source_table= 0; + + if (mysql_handle_derived(lex, DT_INIT)) + DBUG_RETURN(TRUE); + + if (table_list->has_period() && table_list->is_view_or_derived()) + { + my_error(ER_IT_IS_A_VIEW, MYF(0), table_list->table_name.str); + DBUG_RETURN(TRUE); + } + + if (((update_source_table=unique_table(thd, table_list, + table_list->next_global, 0)) || + table_list->is_multitable())) + { + DBUG_ASSERT(update_source_table || table_list->view != 0); + if (thd->lex->period_conditions.is_set()) + { + my_error(ER_NOT_SUPPORTED_YET, MYF(0), + "updating and querying the same temporal periods table"); + DBUG_RETURN(TRUE); + } + multitable= true; + } + } + + if(!multitable) + { + if (table_list->is_view_or_derived() && + select_lex->leaf_tables.elements > 1) + multitable = true; + } + + if (!multitable) + { + if (lex->ignore) + lex->set_stmt_unsafe(LEX::BINLOG_STMT_UNSAFE_UPDATE_IGNORE); + } + + if (!(result= new (thd->mem_root) multi_update(thd, table_list, + &select_lex->leaf_tables, + &select_lex->item_list, + &lex->value_list, + lex->duplicates, + lex->ignore))) + { + DBUG_RETURN(TRUE); + } + + if (((multi_update *)result)->init(thd)) + DBUG_RETURN(TRUE); + + if (setup_tables(thd, &select_lex->context, &select_lex->top_join_list, + table_list, select_lex->leaf_tables, false, false)) + DBUG_RETURN(TRUE); + + if (select_lex->vers_setup_conds(thd, table_list)) + DBUG_RETURN(TRUE); + + { + if (thd->lex->describe) + select_options|= SELECT_DESCRIBE; + + /* + When in EXPLAIN, delay deleting the joins so that they are still + available when we're producing EXPLAIN EXTENDED warning text. + */ + if (select_options & SELECT_DESCRIBE) + free_join= 0; + + select_options|= + SELECT_NO_JOIN_CACHE | SELECT_NO_UNLOCK | OPTION_SETUP_TABLES_DONE; + + if (!(join= new (thd->mem_root) JOIN(thd, select_lex->item_list, + select_options, result))) + DBUG_RETURN(TRUE); + THD_STAGE_INFO(thd, stage_init); + select_lex->join= join; + thd->lex->used_tables=0; + select_lex->item_list_usage= MARK_COLUMNS_WRITE; + if ((err= join->prepare(table_list, select_lex->where, + select_lex->order_list.elements, + select_lex->order_list.first, + false, NULL, NULL, NULL, + select_lex, &lex->unit))) + { + goto err; + } + + } + + free_join= false; + +err: + + if (free_join) + { + THD_STAGE_INFO(thd, stage_end); + err|= (int)(select_lex->cleanup()); + DBUG_RETURN(err || thd->is_error()); + } + DBUG_RETURN(err); + +} + + +bool Sql_cmd_update::execute_inner(THD *thd) +{ + bool res= multitable ? Sql_cmd_dml::execute_inner(thd) + : update_single_table(thd); + + res|= thd->is_error(); + if (multitable) + { + if (unlikely(res)) + result->abort_result_set(); + else + { + if (thd->lex->describe || thd->lex->analyze_stmt) + res= thd->lex->explain->send_explain(thd); + } + } + + if (result) + { + res= false; + delete result; + } + + return res; +} diff --git a/sql/sql_update.h b/sql/sql_update.h index 65e44d1..e52d3cd 100644 --- a/sql/sql_update.h +++ b/sql/sql_update.h @@ -17,6 +17,8 @@ #define SQL_UPDATE_INCLUDED #include "sql_class.h" /* enum_duplicates */ +#include "sql_cmd.h" // Sql_cmd_dml +#include "sql_base.h" class Item; struct TABLE_LIST; @@ -41,4 +43,42 @@ bool mysql_multi_update(THD *thd, TABLE_LIST *table_list, bool records_are_comparable(const TABLE *table); bool compare_record(const TABLE *table); + +class Sql_cmd_update final : public Sql_cmd_dml +{ +public: + Sql_cmd_update(bool multitable_arg) + : multitable(multitable_arg) + { } + + enum_sql_command sql_command_code() const override + { + return multitable ? SQLCOM_UPDATE_MULTI : SQLCOM_UPDATE; + } + + DML_prelocking_strategy *get_dml_prelocking_strategy() + { + return &multiupdate_prelocking_strategy; + } + +protected: + bool precheck(THD *thd) override; + + bool prepare_inner(THD *thd) override; + + bool execute_inner(THD *thd) override; + +private: + bool update_single_table(THD *thd); + + bool multitable; + + DML_prelocking_strategy dml_prelocking_strategy; + Multiupdate_prelocking_strategy multiupdate_prelocking_strategy; + + public: + List<Item> *update_value_list; + +}; + #endif /* SQL_UPDATE_INCLUDED */ diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index dc2eb44..9d112c9 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -69,6 +69,7 @@ #include "my_base.h" #include "sql_type_json.h" #include "json_table.h" +#include "sql_update.h" /* this is to get the bison compilation windows warnings out */ #ifdef _MSC_VER @@ -13481,9 +13482,14 @@ update: opt_low_priority opt_ignore update_table_list SET update_list { + bool is_multiupdate= false; + LEX *lex= Lex; SELECT_LEX *slex= Lex->first_select_lex(); if (slex->table_list.elements > 1) + { Lex->sql_command= SQLCOM_UPDATE_MULTI; + is_multiupdate= true; + } else if (slex->get_table_list()->derived) { /* it is single table update and it is update of derived table */ @@ -13491,6 +13497,9 @@ update: slex->get_table_list()->alias.str, "UPDATE"); MYSQL_YYABORT; } + if (!(lex->m_sql_cmd= + new (thd->mem_root) Sql_cmd_update(is_multiupdate))) + MYSQL_YYABORT; /* In case of multi-update setting write lock for all tables may be too pessimistic. We will decrease lock level if possible in