- commits - lists.mariadb.org

[Commits] 37579f9: MDEV-18755 Assertion `inited==INDEX' failed in handler::ha_index_read_map
by IgorBabaev 28 Feb '19

28 Feb '19

revision-id: 37579f943fed52842bb04467707750c7f6e67e9b (mariadb-10.4.3-14-g37579f9) parent(s): 09d29dfc766613ffd6ac6b8a0bc1c3e6f9388eb2 author: Igor Babaev committer: Igor Babaev timestamp: 2019-02-28 14:44:38 -0800 message: MDEV-18755 Assertion `inited==INDEX' failed in handler::ha_index_read_map When the chosen execution plan accesses a join table employing a range rowid filter a quick select to scan this range has to be built. This quick select is built by a call of SQL_SELECT::test_quick_select(). At this call the function should allow to evaluate only single index range scans. In order to be able to do this a new parameter was added to this function. --- mysql-test/main/rowid_filter.result | 1 + mysql-test/main/rowid_filter.test | 2 + mysql-test/main/rowid_filter_innodb.result | 109 +++++++++++++++++++++++++++++ mysql-test/main/rowid_filter_innodb.test | 40 +++++++++++ sql/opt_range.cc | 19 +++-- sql/opt_range.h | 6 +- sql/sql_select.cc | 20 +++--- 7 files changed, 180 insertions(+), 17 deletions(-) diff --git a/mysql-test/main/rowid_filter.result b/mysql-test/main/rowid_filter.result index 34df303..ea7de8c 100644 --- a/mysql-test/main/rowid_filter.result +++ b/mysql-test/main/rowid_filter.result @@ -1940,4 +1940,5 @@ ALTER TABLE orders DROP CONSTRAINT o_price; ALTER TABLE orders DROP COLUMN o_totaldiscount; DROP VIEW v1; DROP DATABASE dbt3_s001; +use test; set @@use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/main/rowid_filter.test b/mysql-test/main/rowid_filter.test index 0e8a3de..a674021 100644 --- a/mysql-test/main/rowid_filter.test +++ b/mysql-test/main/rowid_filter.test @@ -228,4 +228,6 @@ DROP VIEW v1; DROP DATABASE dbt3_s001; +use test; + set @@use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result index 65f6896..cd09f1d 100644 --- a/mysql-test/main/rowid_filter_innodb.result +++ b/mysql-test/main/rowid_filter_innodb.result @@ -1869,5 +1869,114 @@ ALTER TABLE orders DROP CONSTRAINT o_price; ALTER TABLE orders DROP COLUMN o_totaldiscount; DROP VIEW v1; DROP DATABASE dbt3_s001; +use test; set @@use_stat_tables=@save_use_stat_tables; +# +# MDEV-18755: possible RORI-plan and possible plan with range filter +# +create table t1 ( +pk int not null primary key, f1 varchar(10), f2 varchar(30), a int(10), +key (f1), key (f2) +) engine=innodb; +insert into t1 values +(2,'a','a',2),(3,'a','a',null),(4,'a','a',55),(5,'a','a',4),(6,'a','a',0), +(7,'a','a',1),(8,'a','a',4),(9,'a','a',null),(10,'a','a',0),(11,'a','a',0), +(12,'a','a',null),(13,'a','a',49778),(14,'a','a',6),(15,'a','a',3), +(16,'a','a',233),(17,'a','a',-1),(18,'a','a',5),(19,'a','a',-1), +(20,'a','a',null),(21,'a','a',0),(22,'a','a',null),(23,'a','a',53840), +(24,'a','a',null),(25,'a','a',null),(26,'a','a',5),(27,'a','a',43454), +(28,'a','a',0),(29,'a','a',0),(30,'a','a',null),(59,'a','a',null), +(60,'a','a',null),(61,'a','a',-1),(62,'a','a',null),(63,'a','a',0), +(64,'a','a',14468),(65,'a','a',0),(66,'a','a',28),(67,'a','a',null), +(68,'a','a',14983),(69,'a','a',null),(70,'a','a',3),(71,'a','a',null), +(72,'a','a',null),(73,'a','a',237),(74,'a','a',2),(75,'a','a',0), +(76,'a','a',6),(77,'a','a',5),(78,'a','a',0),(79,'a','a',1),(80,'a','a',-1), +(81,'a','a',20),(82,'a','a',0),(83,'a','a',0),(84,'a','a',null), +(85,'a','a',-1),(86,'a','a',5),(87,'a','a',null),(88,'a','a',160), +(89,null,null,null),(90,'a','a',14785),(91,'a','a',0),(92,'a','a',null); +( select * from t1 +where (f1 is null and f2 is null) and (f2 between 'a' and 'z' or f1 in ('a'))) +union +( select * from t1 +where (f1 is null and f2 is null) and (f2 between 'a' and 'z' or f1 in ('a'))); +pk f1 f2 a +explain ( select * from t1 +where (f1 is null and f2 is null) and (f2 between 'a' and 'z' or f1 in ('a'))) +union +( select * from t1 +where (f1 is null and f2 is null) and (f2 between 'a' and 'z' or f1 in ('a'))); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ref|filter f1,f2 f2|f1 33|13 const 1 (2%) Using index condition; Using where; Using rowid filter +2 UNION t1 ref|filter f1,f2 f2|f1 33|13 const 1 (2%) Using index condition; Using where; Using rowid filter +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL +explain format=json ( select * from t1 +where (f1 is null and f2 is null) and (f2 between 'a' and 'z' or f1 in ('a'))) +union +( select * from t1 +where (f1 is null and f2 is null) and (f2 between 'a' and 'z' or f1 in ('a'))); +EXPLAIN +{ + "query_block": { + "union_result": { + "table_name": "<union1,2>", + "access_type": "ALL", + "query_specifications": [ + { + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ref", + "possible_keys": ["f1", "f2"], + "key": "f2", + "key_length": "33", + "used_key_parts": ["f2"], + "ref": ["const"], + "rowid_filter": { + "range": { + "key": "f1", + "used_key_parts": ["f1"] + }, + "rows": 1, + "selectivity_pct": 1.5873 + }, + "rows": 1, + "filtered": 100, + "index_condition": "t1.f2 is null", + "attached_condition": "t1.f1 is null and (t1.f2 between 'a' and 'z' or t1.f1 = 'a')" + } + } + }, + { + "query_block": { + "select_id": 2, + "operation": "UNION", + "table": { + "table_name": "t1", + "access_type": "ref", + "possible_keys": ["f1", "f2"], + "key": "f2", + "key_length": "33", + "used_key_parts": ["f2"], + "ref": ["const"], + "rowid_filter": { + "range": { + "key": "f1", + "used_key_parts": ["f1"] + }, + "rows": 1, + "selectivity_pct": 1.5873 + }, + "rows": 1, + "filtered": 100, + "index_condition": "t1.f2 is null", + "attached_condition": "t1.f1 is null and (t1.f2 between 'a' and 'z' or t1.f1 = 'a')" + } + } + } + ] + } + } +} +drop table t1; SET SESSION STORAGE_ENGINE=DEFAULT; diff --git a/mysql-test/main/rowid_filter_innodb.test b/mysql-test/main/rowid_filter_innodb.test index 5408f04..803f284 100644 --- a/mysql-test/main/rowid_filter_innodb.test +++ b/mysql-test/main/rowid_filter_innodb.test @@ -4,4 +4,44 @@ SET SESSION STORAGE_ENGINE='InnoDB'; --source rowid_filter.test +--echo # +--echo # MDEV-18755: possible RORI-plan and possible plan with range filter +--echo # + +create table t1 ( + pk int not null primary key, f1 varchar(10), f2 varchar(30), a int(10), + key (f1), key (f2) +) engine=innodb; + +insert into t1 values + (2,'a','a',2),(3,'a','a',null),(4,'a','a',55),(5,'a','a',4),(6,'a','a',0), + (7,'a','a',1),(8,'a','a',4),(9,'a','a',null),(10,'a','a',0),(11,'a','a',0), + (12,'a','a',null),(13,'a','a',49778),(14,'a','a',6),(15,'a','a',3), + (16,'a','a',233),(17,'a','a',-1),(18,'a','a',5),(19,'a','a',-1), + (20,'a','a',null),(21,'a','a',0),(22,'a','a',null),(23,'a','a',53840), + (24,'a','a',null),(25,'a','a',null),(26,'a','a',5),(27,'a','a',43454), + (28,'a','a',0),(29,'a','a',0),(30,'a','a',null),(59,'a','a',null), + (60,'a','a',null),(61,'a','a',-1),(62,'a','a',null),(63,'a','a',0), + (64,'a','a',14468),(65,'a','a',0),(66,'a','a',28),(67,'a','a',null), + (68,'a','a',14983),(69,'a','a',null),(70,'a','a',3),(71,'a','a',null), + (72,'a','a',null),(73,'a','a',237),(74,'a','a',2),(75,'a','a',0), + (76,'a','a',6),(77,'a','a',5),(78,'a','a',0),(79,'a','a',1),(80,'a','a',-1), + (81,'a','a',20),(82,'a','a',0),(83,'a','a',0),(84,'a','a',null), + (85,'a','a',-1),(86,'a','a',5),(87,'a','a',null),(88,'a','a',160), + (89,null,null,null),(90,'a','a',14785),(91,'a','a',0),(92,'a','a',null); + +let $q= +( select * from t1 + where (f1 is null and f2 is null) and (f2 between 'a' and 'z' or f1 in ('a'))) + union +( select * from t1 + where (f1 is null and f2 is null) and (f2 between 'a' and 'z' or f1 in ('a'))); + +eval $q; +eval explain $q; +eval explain format=json $q; + +drop table t1; + + SET SESSION STORAGE_ENGINE=DEFAULT; diff --git a/sql/opt_range.cc b/sql/opt_range.cc index fcf0d22..1e60bb9 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -2586,6 +2586,9 @@ static int fill_used_fields_bitmap(PARAM *param) limit Query limit force_quick_range Prefer to use range (instead of full table scan) even if it is more expensive. + remove_false_parts_of_where Remove parts of OR-clauses for which range + analysis produced SEL_TREE(IMPOSSIBLE) + only_single_index_range_scan Evaluate only single index range scans NOTES Updates the following in the select parameter: @@ -2644,7 +2647,8 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, table_map prev_tables, ha_rows limit, bool force_quick_range, bool ordered_output, - bool remove_false_parts_of_where) + bool remove_false_parts_of_where, + bool only_single_index_range_scan) { uint idx; double scan_time; @@ -2824,7 +2828,7 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, } TABLE_READ_PLAN *best_trp= NULL; - TRP_GROUP_MIN_MAX *group_trp; + TRP_GROUP_MIN_MAX *group_trp= NULL; double best_read_time= read_time; if (cond) @@ -2859,7 +2863,8 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, Try to construct a QUICK_GROUP_MIN_MAX_SELECT. Notice that it can be constructed no matter if there is a range tree. */ - group_trp= get_best_group_min_max(&param, tree, best_read_time); + if (!only_single_index_range_scan) + group_trp= get_best_group_min_max(&param, tree, best_read_time); if (group_trp) { param.table->quick_condition_rows= MY_MIN(group_trp->records, @@ -2907,7 +2912,8 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, table deletes. */ if ((thd->lex->sql_command != SQLCOM_DELETE) && - optimizer_flag(thd, OPTIMIZER_SWITCH_INDEX_MERGE)) + optimizer_flag(thd, OPTIMIZER_SWITCH_INDEX_MERGE) && + !only_single_index_range_scan) { /* Get best non-covering ROR-intersection plan and prepare data for @@ -2935,7 +2941,8 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, */ if (param.table->covering_keys.is_clear_all() && optimizer_flag(thd, OPTIMIZER_SWITCH_INDEX_MERGE) && - optimizer_flag(thd, OPTIMIZER_SWITCH_INDEX_MERGE_SORT_INTERSECT)) + optimizer_flag(thd, OPTIMIZER_SWITCH_INDEX_MERGE_SORT_INTERSECT) && + !only_single_index_range_scan) { if ((intersect_trp= get_best_index_intersect(&param, tree, best_read_time))) @@ -2948,7 +2955,7 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, } if (optimizer_flag(thd, OPTIMIZER_SWITCH_INDEX_MERGE) && - head->stat_records() != 0) + head->stat_records() != 0 && !only_single_index_range_scan) { /* Try creating index_merge/ROR-union scan. */ SEL_IMERGE *imerge; diff --git a/sql/opt_range.h b/sql/opt_range.h index 7e92e1f..2dab90b 100644 --- a/sql/opt_range.h +++ b/sql/opt_range.h @@ -1645,7 +1645,8 @@ class SQL_SELECT :public Sql_alloc { { key_map tmp; tmp.set_all(); - return test_quick_select(thd, tmp, 0, limit, force_quick_range, FALSE, FALSE) < 0; + return test_quick_select(thd, tmp, 0, limit, force_quick_range, + FALSE, FALSE, FALSE) < 0; } /* RETURN @@ -1662,7 +1663,8 @@ class SQL_SELECT :public Sql_alloc { } int test_quick_select(THD *thd, key_map keys, table_map prev_tables, ha_rows limit, bool force_quick_range, - bool ordered_output, bool remove_false_parts_of_where); + bool ordered_output, bool remove_false_parts_of_where, + bool only_single_index_range_scan); }; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 4f98153..211898f 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1606,7 +1606,7 @@ bool JOIN::make_range_rowid_filters() tab->table->force_index= true; (void) sel->test_quick_select(thd, filter_map, (table_map) 0, (ha_rows) HA_POS_ERROR, - true, false, true); + true, false, true, true); tab->table->force_index= force_index_save; if (thd->is_error()) goto no_filter; @@ -4623,7 +4623,8 @@ static ha_rows get_quick_record_count(THD *thd, SQL_SELECT *select, select->test_quick_select(thd, *(key_map *)keys, (table_map) 0, limit, 0, FALSE, - TRUE /* remove_where_parts*/)) == + TRUE, /* remove_where_parts*/ + FALSE)) == 1)) DBUG_RETURN(select->quick->records); if (unlikely(error == -1)) @@ -11356,7 +11357,7 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) OPTION_FOUND_ROWS ? HA_POS_ERROR : join->unit->select_limit_cnt), 0, - FALSE, FALSE) < 0) + FALSE, FALSE, FALSE) < 0) { /* Before reporting "Impossible WHERE" for the whole query @@ -11370,7 +11371,7 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) OPTION_FOUND_ROWS ? HA_POS_ERROR : join->unit->select_limit_cnt),0, - FALSE, FALSE) < 0) + FALSE, FALSE, FALSE) < 0) DBUG_RETURN(1); // Impossible WHERE } else @@ -20899,7 +20900,8 @@ test_if_quick_select(JOIN_TAB *tab) int res= tab->select->test_quick_select(tab->join->thd, tab->keys, (table_map) 0, HA_POS_ERROR, 0, - FALSE, /*remove where parts*/FALSE); + FALSE, /*remove where parts*/FALSE, + FALSE); if (tab->explain_plan && tab->explain_plan->range_checked_fer) tab->explain_plan->range_checked_fer->collect_data(tab->select->quick); @@ -22763,9 +22765,9 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, res= select->test_quick_select(tab->join->thd, new_ref_key_map, 0, (tab->join->select_options & OPTION_FOUND_ROWS) ? - HA_POS_ERROR : - tab->join->unit->select_limit_cnt,TRUE, - TRUE, FALSE) <= 0; + HA_POS_ERROR : + tab->join->unit->select_limit_cnt,TRUE, + TRUE, FALSE, FALSE) <= 0; if (res) { select->cond= save_cond; @@ -22867,7 +22869,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, join->select_options & OPTION_FOUND_ROWS ? HA_POS_ERROR : join->unit->select_limit_cnt, - TRUE, FALSE, FALSE); + TRUE, FALSE, FALSE, FALSE); if (cond_saved) select->cond= saved_cond;

1 0

[Commits] 2d34713294d: Increase the version
by Oleksandr Byelkin 28 Feb '19

28 Feb '19

revision-id: 2d34713294d5339d1459911a46fa20395443b3c7 (mariadb-10.0.38-3-g2d34713294d) parent(s): 7b5c63856b324281a3b0533b7b90fb6c130fcc66 author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2019-02-28 23:40:49 +0100 message: Increase the version --- VERSION | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/VERSION b/VERSION index 163137684a2..0924461861a 100644 --- a/VERSION +++ b/VERSION @@ -1,3 +1,3 @@ MYSQL_VERSION_MAJOR=10 MYSQL_VERSION_MINOR=0 -MYSQL_VERSION_PATCH=38 +MYSQL_VERSION_PATCH=39

1 0

[Commits] 7b5c63856b3: Merge branch '5.5' into 10.0
by Oleksandr Byelkin 28 Feb '19

28 Feb '19

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; + } };

1 0

[Commits] c8d8839fbe4: c
by Oleksandr Byelkin 28 Feb '19

28 Feb '19

revision-id: c8d8839fbe41244408823a34855d4df02525a5b8 (mariadb-5.5.63-8-gc8d8839fbe4) parent(s): a352dc57c8582be60c5c0b89f19c6f0cde78bd50 author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2019-02-28 18:14:41 +0100 message: c --- sql/sql_lex.h | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 2bde94ee5c2..5b589714e1a 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -730,8 +730,8 @@ class st_select_lex_unit: public st_select_lex_node { typedef class st_select_lex_unit SELECT_LEX_UNIT; -#define TOUCHED_SEL_COND 1/* WHERE/HAVING/ON should be reinited before use */ -#define TOUCHED_SEL_DERIVED 2/* derived should be reinited before use */ +#define TOUCHED_SEL_COND 1/* WHERE/HAVING/ON should be reinited before use */ +#define TOUCHED_SEL_DERIVED (1<<1)/* derived should be reinited before use */ /* SELECT_LEX - store information of parsed SELECT statment

1 0

[Commits] a352dc57c85: MDEV-17055: Server crashes in find_order_in_list upon 2nd (3rd) execution of SP with UPDATE
by Oleksandr Byelkin 28 Feb '19

28 Feb '19

revision-id: a352dc57c8582be60c5c0b89f19c6f0cde78bd50 (mariadb-5.5.63-7-ga352dc57c85) parent(s): 0ad598a00b17008b0c0702db40948b14d7eee0d5 author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2019-02-28 17:21:43 +0100 message: MDEV-17055: Server crashes in find_order_in_list upon 2nd (3rd) execution of SP with UPDATE 1. Always drop merged_for_insert flag on cleanup (there could be errors which prevent TABLE to be assigned) 2. Make more precise cleanup of select parts which was touched --- mysql-test/r/sp.result | 33 +++++++++++++++++++++++++++++++++ mysql-test/t/sp.test | 41 +++++++++++++++++++++++++++++++++++++++++ sql/sql_derived.cc | 4 ++-- sql/sql_insert.cc | 1 - sql/sql_lex.cc | 7 ++++--- sql/sql_lex.h | 7 ++++++- sql/sql_prepare.cc | 21 +++++++++++++++------ sql/sql_union.cc | 5 +++-- 8 files changed, 104 insertions(+), 15 deletions(-) diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index 4535056242a..52b52fbbd24 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -8072,4 +8072,37 @@ 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 diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index cb93cd31442..fb9da936fdb 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -9373,5 +9373,46 @@ 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 diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index e990eba8e54..1709669d8a2 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -90,6 +90,7 @@ mysql_handle_derived(LEX *lex, uint phases) sl= sl->next_select_in_list()) { TABLE_LIST *cursor= sl->get_table_list(); + sl->changed_elements|= TOUCHED_SEL_DERIVED; /* DT_MERGE_FOR_INSERT is not needed for views/derived tables inside subqueries. Views and derived tables of subqueries should be @@ -1002,8 +1003,7 @@ bool mysql_derived_reinit(THD *thd, LEX *lex, TABLE_LIST *derived) derived->get_unit())); st_select_lex_unit *unit= derived->get_unit(); - if (derived->table) - derived->merged_for_insert= FALSE; + derived->merged_for_insert= FALSE; unit->unclean(); unit->types.empty(); /* for derived tables & PS (which can't be reset by Item_subquery) */ diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index ba8134bfaf1..cc040508842 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -1510,7 +1510,6 @@ bool mysql_prepare_insert(THD *thd, TABLE_LIST *table_list, DBUG_RETURN(TRUE); } select_lex->fix_prepare_information(thd, &fake_conds, &fake_conds); - select_lex->first_execution= 0; } /* Only call prepare_for_posistion() if we are not performing a DELAYED diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 3c0d3049ae4..7d694f90aa2 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -1904,7 +1904,7 @@ void st_select_lex::init_query() n_child_sum_items= 0; subquery_in_having= explicit_limit= 0; is_item_list_lookup= 0; - first_execution= 1; + changed_elements= 0; first_natural_join_processing= 1; first_cond_optimization= 1; parsing_place= NO_MATTER; @@ -3367,9 +3367,10 @@ void st_select_lex::fix_prepare_information(THD *thd, Item **conds, Item **having_conds) { DBUG_ENTER("st_select_lex::fix_prepare_information"); - if (!thd->stmt_arena->is_conventional() && first_execution) + if (!thd->stmt_arena->is_conventional() && + !(changed_elements & TOUCHED_SEL_COND)) { - first_execution= 0; + changed_elements|= TOUCHED_SEL_COND; if (group_list.first) { if (!group_list_ptrs) diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 8a31560d2bf..2bde94ee5c2 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -729,6 +729,10 @@ class st_select_lex_unit: public st_select_lex_node { typedef class st_select_lex_unit SELECT_LEX_UNIT; + +#define TOUCHED_SEL_COND 1/* WHERE/HAVING/ON should be reinited before use */ +#define TOUCHED_SEL_DERIVED 2/* derived should be reinited before use */ + /* SELECT_LEX - store information of parsed SELECT statment */ @@ -876,7 +880,8 @@ class st_select_lex: public st_select_lex_node subquery. Prepared statements work OK in that regard, as in case of an error during prepare the PS is not created. */ - bool first_execution; + uint8 changed_elements; // see TOUCHED_SEL_* + /* TODO: add foloowing first_* to bitmap above */ bool first_natural_join_processing; bool first_cond_optimization; /* do not wrap view fields with Item_ref */ diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc index 6aa6aacc504..16923ee5a21 100644 --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -2496,7 +2496,7 @@ void reinit_stmt_before_use(THD *thd, LEX *lex) } for (; sl; sl= sl->next_select_in_list()) { - if (!sl->first_execution) + if (sl->changed_elements & TOUCHED_SEL_COND) { /* remove option which was put by mysql_explain_union() */ sl->options&= ~SELECT_DESCRIBE; @@ -2543,19 +2543,28 @@ void reinit_stmt_before_use(THD *thd, LEX *lex) order->next= sl->group_list_ptrs->at(ix+1); } } + } + { // no harm to do it (item_ptr set on parsing) + ORDER *order; for (order= sl->group_list.first; order; order= order->next) + { order->item= &order->item_ptr; + } /* Fix ORDER list */ for (order= sl->order_list.first; order; order= order->next) - order->item= &order->item_ptr; { + order->item= &order->item_ptr; + } + } + if (sl->changed_elements & TOUCHED_SEL_DERIVED) + { #ifndef DBUG_OFF - bool res= + bool res= #endif - sl->handle_derived(lex, DT_REINIT); - DBUG_ASSERT(res == 0); - } + sl->handle_derived(lex, DT_REINIT); + DBUG_ASSERT(res == 0); } + { SELECT_LEX_UNIT *unit= sl->master_unit(); unit->unclean(); diff --git a/sql/sql_union.cc b/sql/sql_union.cc index bbb4133417e..e855b64e600 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -215,8 +215,9 @@ st_select_lex_unit::init_prepare_fake_select_lex(THD *thd_arg, called at the first execution of the statement, while first_execution shows whether this is called at the first execution of the union that may form just a subselect. - */ - if (!fake_select_lex->first_execution && first_execution) + */ + if ((fake_select_lex->changed_elements & TOUCHED_SEL_COND) && + first_execution) { for (ORDER *order= global_parameters->order_list.first; order;

1 0

[Commits] d6a1ea33e21: c
by Oleksandr Byelkin 28 Feb '19

28 Feb '19

revision-id: d6a1ea33e217f0c6481747daca66a5a6fd2353bd (mariadb-5.5.63-8-gd6a1ea33e21) parent(s): dd9bd1db914b6166d3e220fd9945b5fcf89b06d0 author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2019-02-28 17:21:33 +0100 message: c --- sql/sql_lex.h | 1 + 1 file changed, 1 insertion(+) diff --git a/sql/sql_lex.h b/sql/sql_lex.h index abbfd901112..2bde94ee5c2 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -881,6 +881,7 @@ class st_select_lex: public st_select_lex_node case of an error during prepare the PS is not created. */ uint8 changed_elements; // see TOUCHED_SEL_* + /* TODO: add foloowing first_* to bitmap above */ bool first_natural_join_processing; bool first_cond_optimization; /* do not wrap view fields with Item_ref */

1 0

[Commits] b7cc17b2734: postreview
by Oleksandr Byelkin 28 Feb '19

28 Feb '19

revision-id: b7cc17b27340f1659165f2c9fbdf891355b98d33 (mariadb-5.5.63-8-gb7cc17b2734) parent(s): ae5bf0d6ada265d1158d108290555d9077e7580e author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2019-02-28 17:18:58 +0100 message: postreview --- sql/sql_delete.cc | 2 -- sql/sql_derived.cc | 1 + sql/sql_insert.cc | 2 -- sql/sql_lex.cc | 7 ++++--- sql/sql_lex.h | 13 +++++-------- sql/sql_load.cc | 2 -- sql/sql_prepare.cc | 20 ++++++++++---------- sql/sql_select.cc | 2 -- sql/sql_union.cc | 5 +++-- sql/sql_update.cc | 2 -- 10 files changed, 23 insertions(+), 33 deletions(-) diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc index 51300b27600..ab573df7992 100644 --- a/sql/sql_delete.cc +++ b/sql/sql_delete.cc @@ -474,8 +474,6 @@ int mysql_prepare_delete(THD *thd, TABLE_LIST *table_list, Item **conds) DBUG_ENTER("mysql_prepare_delete"); List<Item> all_fields; - select_lex->first_execution_attempt= FALSE; - thd->lex->allow_sum_func= 0; if (setup_tables_and_check_access(thd, &thd->lex->select_lex.context, &thd->lex->select_lex.top_join_list, diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index f5f6d52e793..1709669d8a2 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -90,6 +90,7 @@ mysql_handle_derived(LEX *lex, uint phases) sl= sl->next_select_in_list()) { TABLE_LIST *cursor= sl->get_table_list(); + sl->changed_elements|= TOUCHED_SEL_DERIVED; /* DT_MERGE_FOR_INSERT is not needed for views/derived tables inside subqueries. Views and derived tables of subqueries should be diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index 71eb434201c..cc040508842 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -1400,8 +1400,6 @@ bool mysql_prepare_insert(THD *thd, TABLE_LIST *table_list, /* INSERT should have a SELECT or VALUES clause */ DBUG_ASSERT (!select_insert || !values); - select_lex->first_execution_attempt= FALSE; - if (mysql_handle_derived(thd->lex, DT_INIT)) DBUG_RETURN(TRUE); if (table_list->handle_derived(thd->lex, DT_MERGE_FOR_INSERT)) diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index bf23a916056..7d694f90aa2 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -1904,7 +1904,7 @@ void st_select_lex::init_query() n_child_sum_items= 0; subquery_in_having= explicit_limit= 0; is_item_list_lookup= 0; - first_execution_attempt= first_execution= 1; + changed_elements= 0; first_natural_join_processing= 1; first_cond_optimization= 1; parsing_place= NO_MATTER; @@ -3367,9 +3367,10 @@ void st_select_lex::fix_prepare_information(THD *thd, Item **conds, Item **having_conds) { DBUG_ENTER("st_select_lex::fix_prepare_information"); - if (!thd->stmt_arena->is_conventional() && first_execution) + if (!thd->stmt_arena->is_conventional() && + !(changed_elements & TOUCHED_SEL_COND)) { - first_execution= 0; + changed_elements|= TOUCHED_SEL_COND; if (group_list.first) { if (!group_list_ptrs) diff --git a/sql/sql_lex.h b/sql/sql_lex.h index e1b96b86249..abbfd901112 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -729,6 +729,10 @@ class st_select_lex_unit: public st_select_lex_node { typedef class st_select_lex_unit SELECT_LEX_UNIT; + +#define TOUCHED_SEL_COND 1/* WHERE/HAVING/ON should be reinited before use */ +#define TOUCHED_SEL_DERIVED 2/* derived should be reinited before use */ + /* SELECT_LEX - store information of parsed SELECT statment */ @@ -875,15 +879,8 @@ class st_select_lex: public st_select_lex_node variable to inidicate the optimization/execution state of every subquery. Prepared statements work OK in that regard, as in case of an error during prepare the PS is not created. - - TODO: make a bitmap with execution stages done for nest bool flags - */ - bool first_execution; // where/having saved - /* - False if we have started execution and so there is something to cleanup - in tables/derived tables */ - bool first_execution_attempt; + uint8 changed_elements; // see TOUCHED_SEL_* bool first_natural_join_processing; bool first_cond_optimization; /* do not wrap view fields with Item_ref */ diff --git a/sql/sql_load.cc b/sql/sql_load.cc index befa7a33478..850360c1dba 100644 --- a/sql/sql_load.cc +++ b/sql/sql_load.cc @@ -202,8 +202,6 @@ int mysql_load(THD *thd,sql_exchange *ex,TABLE_LIST *table_list, bool transactional_table __attribute__((unused)); DBUG_ENTER("mysql_load"); - thd->lex->select_lex.first_execution_attempt= FALSE; - /* Bug #34283 mysqlbinlog leaves tmpfile after termination if binlog contains diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc index 935ff29f6df..16923ee5a21 100644 --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -2496,7 +2496,7 @@ void reinit_stmt_before_use(THD *thd, LEX *lex) } for (; sl; sl= sl->next_select_in_list()) { - if (!sl->first_execution) + if (sl->changed_elements & TOUCHED_SEL_COND) { /* remove option which was put by mysql_explain_union() */ sl->options&= ~SELECT_DESCRIBE; @@ -2533,9 +2533,6 @@ void reinit_stmt_before_use(THD *thd, LEX *lex) else sl->having= NULL; DBUG_ASSERT(sl->join == 0); - } - if (!sl->first_execution_attempt) - { ORDER *order; /* Fix GROUP list */ if (sl->group_list_ptrs && sl->group_list_ptrs->size() > 0) @@ -2546,6 +2543,9 @@ void reinit_stmt_before_use(THD *thd, LEX *lex) order->next= sl->group_list_ptrs->at(ix+1); } } + } + { // no harm to do it (item_ptr set on parsing) + ORDER *order; for (order= sl->group_list.first; order; order= order->next) { order->item= &order->item_ptr; @@ -2555,14 +2555,14 @@ void reinit_stmt_before_use(THD *thd, LEX *lex) { order->item= &order->item_ptr; } - - { + } + if (sl->changed_elements & TOUCHED_SEL_DERIVED) + { #ifndef DBUG_OFF - bool res= + bool res= #endif - sl->handle_derived(lex, DT_REINIT); - DBUG_ASSERT(res == 0); - } + sl->handle_derived(lex, DT_REINIT); + DBUG_ASSERT(res == 0); } { diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 489851dce46..35937f0536f 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -638,8 +638,6 @@ JOIN::prepare(Item ***rref_pointer_array, join_list= &select_lex->top_join_list; union_part= unit_arg->is_union(); - select_lex->first_execution_attempt= FALSE; - // simple check that we got usable conds dbug_print_item(conds); diff --git a/sql/sql_union.cc b/sql/sql_union.cc index bbb4133417e..e855b64e600 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -215,8 +215,9 @@ st_select_lex_unit::init_prepare_fake_select_lex(THD *thd_arg, called at the first execution of the statement, while first_execution shows whether this is called at the first execution of the union that may form just a subselect. - */ - if (!fake_select_lex->first_execution && first_execution) + */ + if ((fake_select_lex->changed_elements & TOUCHED_SEL_COND) && + first_execution) { for (ORDER *order= global_parameters->order_list.first; order; diff --git a/sql/sql_update.cc b/sql/sql_update.cc index ef51e2070a3..fe007d5823d 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -1010,8 +1010,6 @@ bool mysql_prepare_update(THD *thd, TABLE_LIST *table_list, SELECT_LEX *select_lex= &thd->lex->select_lex; DBUG_ENTER("mysql_prepare_update"); - select_lex->first_execution_attempt= FALSE; - #ifndef NO_EMBEDDED_ACCESS_CHECKS table_list->grant.want_privilege= table->grant.want_privilege= (SELECT_ACL & ~table->grant.privilege);

1 0

[Commits] d984607: Add .gitignore, edits
by Sergei Petrunia 28 Feb '19

28 Feb '19

revision-id: d98460741bf0bf8667786b53a7b161f7ca1f4717 () parent(s): 1752d7dfcdb9cb9c6d668ad9276b9feab8b06336 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2019-02-28 10:29:05 -0500 message: Add .gitignore, edits --- .gitignore | 15 +++++++++++++++ mariadb-tpcds-tooling2/01-generate-dataset-pg.sh | 2 +- 2 files changed, 16 insertions(+), 1 deletion(-) diff --git a/.gitignore b/.gitignore new file mode 100644 index 0000000..2681c87 --- /dev/null +++ b/.gitignore @@ -0,0 +1,15 @@ +# Dataset (not loaded) +data-for-pg +data-for-pg.txt +v2.8.0rc4-pg +queries-for-pg.txt +queries-for-pg + +# PostgreSQL itself +postgresql-11.2 +postgresql-11.2-inst +pgdata + +# logs +logs +pglogfile diff --git a/mariadb-tpcds-tooling2/01-generate-dataset-pg.sh b/mariadb-tpcds-tooling2/01-generate-dataset-pg.sh index b0e0c83..9fad821 100755 --- a/mariadb-tpcds-tooling2/01-generate-dataset-pg.sh +++ b/mariadb-tpcds-tooling2/01-generate-dataset-pg.sh @@ -35,7 +35,7 @@ fi du -sh ../../data-for-pg ) -cat > data-for-postgresql.txt <<END +cat > data-for-pg.txt <<END RNGSEED_DATASET=$RNGSEED_DATASET END

1 0

[Commits] 1752d7d: Fix permissions for 20-run-queries-pg.sh, better result print-out
by Sergei Petrunia 28 Feb '19

28 Feb '19

revision-id: 1752d7dfcdb9cb9c6d668ad9276b9feab8b06336 () parent(s): 4ffc3da2ff971ae6b99d150ee52514c89478a9c2 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2019-02-28 10:24:14 -0500 message: Fix permissions for 20-run-queries-pg.sh, better result print-out --- mariadb-tpcds-tooling2/20-run-queries-pg.sh | 4 +++- 1 file changed, 3 insertions(+), 1 deletion(-) diff --git a/mariadb-tpcds-tooling2/20-run-queries-pg.sh b/mariadb-tpcds-tooling2/20-run-queries-pg.sh old mode 100644 new mode 100755 index 86d959a..c1e21ff --- a/mariadb-tpcds-tooling2/20-run-queries-pg.sh +++ b/mariadb-tpcds-tooling2/20-run-queries-pg.sh @@ -9,7 +9,9 @@ done (./postgresql-11.2-inst/bin/psql tpcds | tee pg-result.txt) << END select - query_stream, count(*), sum(query_time_ms) / (60*1000) + query_stream, + count(*) as n_queries, + sum(query_time_ms) / (60*1000) as total_time_sec from my_tpcds_result group by

1 0

[Commits] 4ffc3da: Cleanup part#3
by Sergei Petrunia 28 Feb '19

28 Feb '19

revision-id: 4ffc3da2ff971ae6b99d150ee52514c89478a9c2 () parent(s): 014e2d16defac0de290de65148b8a425fa710be1 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2019-02-28 10:02:38 -0500 message: Cleanup part#3 --- mariadb-tpcds-tooling2/01-generate-dataset.sh | 2 ++ mariadb-tpcds-tooling2/20-run-queries-pg.sh | 3 +-- mariadb-tpcds-tooling2/run-queries-mysql8.sh | 2 ++ run-all-mariadb-10.4.sh | 12 ++++++++---- setup-server/setup-postgresql-11.sh | 0 5 files changed, 13 insertions(+), 6 deletions(-) diff --git a/mariadb-tpcds-tooling2/01-generate-dataset.sh b/mariadb-tpcds-tooling2/01-generate-dataset.sh index 3fb580a..0272c9c 100755 --- a/mariadb-tpcds-tooling2/01-generate-dataset.sh +++ b/mariadb-tpcds-tooling2/01-generate-dataset.sh @@ -16,6 +16,8 @@ fi if [ ! -d v2.8.0rc4 ] ; then + unzip tpc-ds-tool.zip + # Modify the toolkit to produce MySQL dialect and compile it ( cd v2.8.0rc4/tools diff --git a/mariadb-tpcds-tooling2/20-run-queries-pg.sh b/mariadb-tpcds-tooling2/20-run-queries-pg.sh index f00c119..86d959a 100644 --- a/mariadb-tpcds-tooling2/20-run-queries-pg.sh +++ b/mariadb-tpcds-tooling2/20-run-queries-pg.sh @@ -9,11 +9,10 @@ done (./postgresql-11.2-inst/bin/psql tpcds | tee pg-result.txt) << END select - query_stream, count(*), sum(query_time_ms) + query_stream, count(*), sum(query_time_ms) / (60*1000) from my_tpcds_result group by query_stream; END - diff --git a/mariadb-tpcds-tooling2/run-queries-mysql8.sh b/mariadb-tpcds-tooling2/run-queries-mysql8.sh index 1e071e8..64dc0df 100755 --- a/mariadb-tpcds-tooling2/run-queries-mysql8.sh +++ b/mariadb-tpcds-tooling2/run-queries-mysql8.sh @@ -1,5 +1,7 @@ #!/bin/bash +echo "THis script is out of date" +exit 1 # TODO: factor out the config soemwhere, and then this is the same as with MariaDB MYSQL="./mysql-8.0/runtime_output_directory/mysql" diff --git a/run-all-mariadb-10.4.sh b/run-all-mariadb-10.4.sh index 4fd69c0..2ba2fed 100755 --- a/run-all-mariadb-10.4.sh +++ b/run-all-mariadb-10.4.sh @@ -1,7 +1,11 @@ #!/bin/bash set -e -./setup-server/setup-mariadb-current.sh 10.2 | tee log-build +mkdir -p logs +./setup-server/setup-mariadb-current.sh 10.4 | tee logs/setup-mariadb + +./mariadb-tpcds-tooling2/01-generate-dataset.sh | tee logs/generate-dataset +./mariadb-tpcds-tooling2/10-prepare-queries.sh | tee logs/prepare-queries +./mariadb-tpcds-tooling2/02-load-dataset.sh | tee logs/load-dataset +./mariadb-tpcds-tooling2/20-run-queries.sh | tee logs/run-queries + -./mariadb-tpcds-tooling2/prepare-benchmark-and-data.sh | tee log-prepare -./mariadb-tpcds-tooling2/prepare-queries.sh | tee log-run -./mariadb-tpcds-tooling2/run-queries.sh | tee log-benchmark diff --git a/setup-server/setup-postgresql-11.sh b/setup-server/setup-postgresql-11.sh old mode 100644 new mode 100755

1 0