revision-id: 7b5c63856b324281a3b0533b7b90fb6c130fcc66 (mariadb-10.0.38-2-g7b5c63856b3) parent(s): d9d83f1d92b696ef56f4944df036b8a78364ebb4 cb11b3fbe9d4dde776cb8f2c0d6f83a569655efc author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2019-02-28 21:50:00 +0100 message: Merge branch '5.5' into 10.0 VERSION | 2 +- mysql-test/r/loaddata.result | 50 +++++++++++++++++++++++++++++++++++++++ mysql-test/r/sp.result | 33 ++++++++++++++++++++++++++ mysql-test/t/loaddata.test | 45 +++++++++++++++++++++++++++++++++++ mysql-test/t/sp.test | 41 ++++++++++++++++++++++++++++++++ sql/sql_delete.cc | 6 ++--- sql/sql_derived.cc | 31 +----------------------- sql/sql_derived.h | 1 - sql/sql_insert.cc | 3 +-- sql/sql_lex.cc | 16 ++++--------- sql/sql_lex.h | 32 ++++++++++++++++++++++++- sql/sql_load.cc | 10 ++++++-- sql/sql_prepare.cc | 21 +++++++++++----- sql/sql_union.cc | 5 ++-- win/packaging/create_msi.cmake.in | 5 ++-- 15 files changed, 240 insertions(+), 61 deletions(-) diff --cc VERSION index 0924461861a,1fb4ae866ce..163137684a2 --- a/VERSION +++ b/VERSION @@@ -1,3 -1,4 +1,3 @@@ -MYSQL_VERSION_MAJOR=5 -MYSQL_VERSION_MINOR=5 -MYSQL_VERSION_PATCH=64 -MYSQL_VERSION_EXTRA= +MYSQL_VERSION_MAJOR=10 +MYSQL_VERSION_MINOR=0 - MYSQL_VERSION_PATCH=39 ++MYSQL_VERSION_PATCH=38 diff --cc mysql-test/r/sp.result index 058ef69c548,52b52fbbd24..7cff6492fad --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@@ -7915,95 -8072,37 +7915,128 @@@ CALL sp c a b a b DROP PROCEDURE sp; DROP TABLE t1; + # + # MDEV-17055: Server crashes in find_order_in_list upon + # 2nd (3rd) execution of SP with UPDATE + # + CREATE TABLE t1 (a INT); + CREATE VIEW v1 AS SELECT * FROM t1; + CREATE TABLE t2 (c INT); + CREATE PROCEDURE sp() UPDATE v1 SET a = 1 ORDER BY a, b LIMIT 1; + LOCK TABLE t2 READ; + CALL sp; + ERROR HY000: Table 'v1' was not locked with LOCK TABLES + UNLOCK TABLES; + CALL sp; + ERROR 42S22: Unknown column 'b' in 'order clause' + CALL sp; + ERROR 42S22: Unknown column 'b' in 'order clause' + CALL sp; + ERROR 42S22: Unknown column 'b' in 'order clause' + DROP PROCEDURE sp; + CREATE PROCEDURE sp() UPDATE v1 SET a = 1 WHERE a=1 and b=2; + LOCK TABLE t2 READ; + CALL sp; + ERROR HY000: Table 'v1' was not locked with LOCK TABLES + UNLOCK TABLES; + CALL sp; + ERROR 42S22: Unknown column 'b' in 'where clause' + CALL sp; + ERROR 42S22: Unknown column 'b' in 'where clause' + CALL sp; + ERROR 42S22: Unknown column 'b' in 'where clause' + DROP PROCEDURE sp; + DROP VIEW v1; + DROP TABLE t1, t2; # End of 5.5 test +# +# MDEV-7040: Crash in field_conv, memcpy_field_possible, part#2 +# +create table t1 ( +col1 bigint(20), +col2 char(1), +col3 char(2) +); +insert into t1 values (1,'a','a'), (2,'b','b'); +create table t2 as select * from t1; +create table t3 as select * from t1; +create table t4 as select * from t1; +create table t5 as select * from t1; +create table t6 as select * from t1; +flush tables; +CREATE PROCEDURE p1() +begin +DECLARE _var1 bigint(20) UNSIGNED; +DECLARE _var2 CHAR(1) DEFAULT NULL; +DECLARE _var3 CHAR(1) DEFAULT NULL; +DECLARE _done BOOLEAN DEFAULT 0; +declare cur1 cursor for +select col1, col2, col3 +from t1 +where +col1 in (select t2.col1 from t2 where t2.col2=t1.col2) or +col2 in (select t3.col3 from t3 where t3.col3=t1.col2) ; +DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = 1; +OPEN cur1; +set _var1 = (select _var1 from t4 limit 1); +set _var1 = (select _var1 from t5 limit 1); +set _var1 = (select _var1 from t6 limit 1); +label1: +LOOP +SET _done = 0; +FETCH cur1 INTO _var1, _var2, _var3; +IF _done THEN +LEAVE label1; +END IF; +END LOOP label1; +CLOSE cur1; +end| +set @tmp_toc= @@table_open_cache; +set @tmp_tdc= @@table_definition_cache; +set global table_open_cache=1; +set global table_definition_cache=1; +Warnings: +Warning 1292 Truncated incorrect table_definition_cache value: '1' +call p1(); +set global table_open_cache= @tmp_toc; +set global table_definition_cache= @tmp_tdc; +drop procedure p1; +drop table t1,t2,t3,t4,t5,t6; +# +# MDEV-11935: Queries in stored procedures with and +# EXISTS(SELECT * FROM VIEW) crashes and closes hte conneciton. +# +CREATE TABLE ANY_TABLE ( +ENTITY_UID BIGINT NOT NULL +); +CREATE TABLE SECURITY_PATH( +origid BIGINT UNSIGNED NOT NULL, +destid BIGINT UNSIGNED NOT NULL, +KEY (destid) +); +CREATE VIEW ENTITY_ACCESS ( +ENTITY_UID, +OWNER_UID +) AS +SELECT SP1.origid, +SP2.destid +FROM SECURITY_PATH SP1 +JOIN SECURITY_PATH SP2 ON SP1.destid = SP2.origid +; +CREATE PROCEDURE SP_EXAMPLE_SELECT () +BEGIN +SELECT * +FROM ANY_TABLE AT1 +WHERE EXISTS ( SELECT * +FROM ENTITY_ACCESS EA +WHERE AT1.ENTITY_UID = EA.ENTITY_UID +AND EA.OWNER_UID IS NULL ); +END +// +CALL SP_EXAMPLE_SELECT (); +ENTITY_UID +CALL SP_EXAMPLE_SELECT (); +ENTITY_UID +drop procedure SP_EXAMPLE_SELECT; +drop view ENTITY_ACCESS; +drop table ANY_TABLE, SECURITY_PATH; diff --cc mysql-test/t/sp.test index 1b765f0eb2d,fb9da936fdb..dc1a244e716 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@@ -9373,113 -9373,46 +9373,154 @@@ CALL sp DROP PROCEDURE sp; DROP TABLE t1; + --echo # + --echo # MDEV-17055: Server crashes in find_order_in_list upon + --echo # 2nd (3rd) execution of SP with UPDATE + --echo # + + CREATE TABLE t1 (a INT); + CREATE VIEW v1 AS SELECT * FROM t1; + CREATE TABLE t2 (c INT); + + CREATE PROCEDURE sp() UPDATE v1 SET a = 1 ORDER BY a, b LIMIT 1; + LOCK TABLE t2 READ; + --error ER_TABLE_NOT_LOCKED + CALL sp; + UNLOCK TABLES; + --error ER_BAD_FIELD_ERROR + CALL sp; + --error ER_BAD_FIELD_ERROR + CALL sp; + --error ER_BAD_FIELD_ERROR + CALL sp; + + # Cleanup + DROP PROCEDURE sp; + + CREATE PROCEDURE sp() UPDATE v1 SET a = 1 WHERE a=1 and b=2; + LOCK TABLE t2 READ; + --error ER_TABLE_NOT_LOCKED + CALL sp; + UNLOCK TABLES; + --error ER_BAD_FIELD_ERROR + CALL sp; + --error ER_BAD_FIELD_ERROR + CALL sp; + --error ER_BAD_FIELD_ERROR + CALL sp; + + # Cleanup + DROP PROCEDURE sp; + + DROP VIEW v1; + DROP TABLE t1, t2; --echo # End of 5.5 test + +--echo # +--echo # MDEV-7040: Crash in field_conv, memcpy_field_possible, part#2 +--echo # +create table t1 ( + col1 bigint(20), + col2 char(1), + col3 char(2) +); +insert into t1 values (1,'a','a'), (2,'b','b'); + +create table t2 as select * from t1; +create table t3 as select * from t1; +create table t4 as select * from t1; +create table t5 as select * from t1; +create table t6 as select * from t1; + +flush tables; + +DELIMITER |; + +CREATE PROCEDURE p1() +begin + DECLARE _var1 bigint(20) UNSIGNED; + DECLARE _var2 CHAR(1) DEFAULT NULL; + DECLARE _var3 CHAR(1) DEFAULT NULL; + + DECLARE _done BOOLEAN DEFAULT 0; + + declare cur1 cursor for + select col1, col2, col3 + from t1 + where + col1 in (select t2.col1 from t2 where t2.col2=t1.col2) or + col2 in (select t3.col3 from t3 where t3.col3=t1.col2) ; + + DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = 1; + + OPEN cur1; + + set _var1 = (select _var1 from t4 limit 1); + set _var1 = (select _var1 from t5 limit 1); + set _var1 = (select _var1 from t6 limit 1); +label1: + LOOP + SET _done = 0; + FETCH cur1 INTO _var1, _var2, _var3; + IF _done THEN + LEAVE label1; + END IF; + END LOOP label1; + CLOSE cur1; +end| +DELIMITER ;| + +set @tmp_toc= @@table_open_cache; +set @tmp_tdc= @@table_definition_cache; + +set global table_open_cache=1; +set global table_definition_cache=1; +call p1(); + +set global table_open_cache= @tmp_toc; +set global table_definition_cache= @tmp_tdc; +drop procedure p1; + +drop table t1,t2,t3,t4,t5,t6; + +--echo # +--echo # MDEV-11935: Queries in stored procedures with and +--echo # EXISTS(SELECT * FROM VIEW) crashes and closes hte conneciton. +--echo # + +CREATE TABLE ANY_TABLE ( + ENTITY_UID BIGINT NOT NULL +); +CREATE TABLE SECURITY_PATH( +origid BIGINT UNSIGNED NOT NULL, +destid BIGINT UNSIGNED NOT NULL, +KEY (destid) +); +CREATE VIEW ENTITY_ACCESS ( +ENTITY_UID, +OWNER_UID +) AS +SELECT SP1.origid, + SP2.destid +FROM SECURITY_PATH SP1 +JOIN SECURITY_PATH SP2 ON SP1.destid = SP2.origid +; +--delimiter // +CREATE PROCEDURE SP_EXAMPLE_SELECT () +BEGIN + SELECT * + FROM ANY_TABLE AT1 + WHERE EXISTS ( SELECT * + FROM ENTITY_ACCESS EA + WHERE AT1.ENTITY_UID = EA.ENTITY_UID + AND EA.OWNER_UID IS NULL ); +END +// +--delimiter ; +CALL SP_EXAMPLE_SELECT (); +CALL SP_EXAMPLE_SELECT (); + +drop procedure SP_EXAMPLE_SELECT; +drop view ENTITY_ACCESS; +drop table ANY_TABLE, SECURITY_PATH; diff --cc sql/sql_delete.cc index bbea9815547,ab573df7992..46415566bc2 --- a/sql/sql_delete.cc +++ b/sql/sql_delete.cc @@@ -37,166 -35,10 +37,166 @@@ #include "sql_select.h" #include "sp_head.h" #include "sql_trigger.h" +#include "sql_statistics.h" #include "transaction.h" #include "records.h" // init_read_record, - #include "sql_derived.h" // mysql_handle_list_of_derived + #include "sql_derived.h" // mysql_handle_derived // end_read_record +#include "sql_partition.h" // make_used_partitions_str + +/* + @brief + Print query plan of a single-table DELETE command + + @detail + This function is used by EXPLAIN DELETE and by SHOW EXPLAIN when it is + invoked on a running DELETE statement. +*/ + +void Delete_plan::save_explain_data(Explain_query *query) +{ + Explain_delete* explain= new Explain_delete; + + if (deleting_all_rows) + { + explain->deleting_all_rows= true; + explain->select_type= "SIMPLE"; + explain->rows= scanned_rows; + } + else + { + explain->deleting_all_rows= false; + Update_plan::save_explain_data_intern(query, explain); + } + + query->add_upd_del_plan(explain); +} + + +void Update_plan::save_explain_data(Explain_query *query) +{ + Explain_update* explain= new Explain_update; + save_explain_data_intern(query, explain); + query->add_upd_del_plan(explain); +} + + +void Update_plan::save_explain_data_intern(Explain_query *query, + Explain_update *explain) +{ + explain->select_type= "SIMPLE"; + explain->table_name.append(table->pos_in_table_list->alias); + + explain->impossible_where= false; + explain->no_partitions= false; + + if (impossible_where) + { + explain->impossible_where= true; + return; + } + + if (no_partitions) + { + explain->no_partitions= true; + return; + } + + select_lex->set_explain_type(TRUE); + explain->select_type= select_lex->type; + /* Partitions */ + { +#ifdef WITH_PARTITION_STORAGE_ENGINE + partition_info *part_info; + if ((part_info= table->part_info)) + { + make_used_partitions_str(part_info, &explain->used_partitions); + explain->used_partitions_set= true; + } + else + explain->used_partitions_set= false; +#else + /* just produce empty column if partitioning is not compiled in */ + explain->used_partitions_set= false; +#endif + } + + + /* Set jtype */ + if (select && select->quick) + { + int quick_type= select->quick->get_type(); + if ((quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE) || + (quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_INTERSECT) || + (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT) || + (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION)) + explain->jtype= JT_INDEX_MERGE; + else + explain->jtype= JT_RANGE; + } + else + { + if (index == MAX_KEY) + explain->jtype= JT_ALL; + else + explain->jtype= JT_NEXT; + } + + explain->using_where= MY_TEST(select && select->cond); + explain->using_filesort= using_filesort; + explain->using_io_buffer= using_io_buffer; + + make_possible_keys_line(table, possible_keys, &explain->possible_keys_line); + + explain->quick_info= NULL; + + /* Calculate key_len */ + if (select && select->quick) + { + explain->quick_info= select->quick->get_explain(mem_root); + } + else + { + if (index != MAX_KEY) + { + explain->key_str.append(table->key_info[index].name); + char buf[64]; + size_t length; + length= longlong10_to_str(table->key_info[index].key_length, buf, 10) - buf; + explain->key_len_str.append(buf, length); + } + } + explain->rows= scanned_rows; + + if (select && select->quick && + select->quick->get_type() == QUICK_SELECT_I::QS_TYPE_RANGE) + { + explain_append_mrr_info((QUICK_RANGE_SELECT*)select->quick, + &explain->mrr_type); + } + + bool skip= updating_a_view; + + /* Save subquery children */ + for (SELECT_LEX_UNIT *unit= select_lex->first_inner_unit(); + unit; + unit= unit->next_unit()) + { + if (skip) + { + skip= false; + continue; + } + /* + Display subqueries only if they are not parts of eliminated WHERE/ON + clauses. + */ + if (!(unit->item && unit->item->eliminated)) + explain->add_child(unit->first_select()->select_number); + } +} + + /** Implement DELETE SQL word. diff --cc sql/sql_derived.h index 1dffef7235b,29b898fc65a..301ae31b016 --- a/sql/sql_derived.h +++ b/sql/sql_derived.h @@@ -22,8 -22,6 +22,7 @@@ struct LEX bool mysql_handle_derived(LEX *lex, uint phases); bool mysql_handle_single_derived(LEX *lex, TABLE_LIST *derived, uint phases); - bool mysql_handle_list_of_derived(LEX *lex, TABLE_LIST *dt_list, uint phases); +bool mysql_derived_reinit(THD *thd, LEX *lex, TABLE_LIST *derived); /** Cleans up the SELECT_LEX_UNIT for the derived table (if any). diff --cc sql/sql_lex.h index d58be8b336a,5b589714e1a..6483aa0e44d --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@@ -2770,8 -2791,30 +2775,33 @@@ struct LEX: public Query_tables_lis bool save_prep_leaf_tables(); + int print_explain(select_result_sink *output, uint8 explain_flags, + bool *printed_anything); ++ + /* + Run specified phases for derived tables/views in the given list + + @param table_list - list of derived tables/view to handle + @param phase - phases to process tables/views through + + @details + This method runs phases specified by the 'phases' on derived + tables/views found in the 'table_list' with help of the + TABLE_LIST::handle_derived function. + 'this' is passed as an argument to the TABLE_LIST::handle_derived. + + @return false - ok + @return true - error + */ + bool handle_list_of_derived(TABLE_LIST *table_list, uint phases) + { + for (TABLE_LIST *tl= table_list; tl; tl= tl->next_local) + { + if (tl->is_view_or_derived() && tl->handle_derived(this, phases)) + return true; + } + return false; + } };