- commits - lists.mariadb.org

[Commits] 677643a: MDEV-27262 Unexpected index intersection with full index scan for an index
by IgorBabaev 20 Dec '21

20 Dec '21

revision-id: 677643a80986107491b7886441f2828384f0494b (mariadb-10.2.31-1286-g677643a) parent(s): 8bb55633699612279744c055e22eeca8d4058273 author: Igor Babaev committer: Igor Babaev timestamp: 2021-12-17 14:11:39 -0800 message: MDEV-27262 Unexpected index intersection with full index scan for an index If when extracting a range condition foran index from the WHERE condition Range Optimizer sees that the range condition covers the whole index then such condition should be discarded because cannot it be used in any range scan. In some cases Range Optimizer really does it, but there remained some conditions for which it was not done. As a result the optimizer could produce index merge plans with the full index scan for one of the indexes participating in the index merge. This could be observed in one of the test cases from index_merge1.inc where a plan with index_merge_sort_union was produced and in the test case reported for this bug where a plan with index_merge_sort_intersect was produced. In both cases one of two index scans participating in index merge ran over the whole index. The patch slightly changes the original above mentioned test case from index_merge1.inc to be able to produce an intended plan employing index_merge_sort_union. The original query was left to show that index merge is not used for it anymore. It should be noted that for the plan with index_merge_sort_intersect could be chosen for execution only due to a defect in the InnoDB code that returns wrong estimates for the cardinality of big ranges. This bug led to serious problems in 10.4+ where the optimization using Rowid filters is employed (see mdev-26446). Approved by Oleksandr Byelkin <sanja(a)mariadb.com> --- mysql-test/include/index_merge1.inc | 8 +++- mysql-test/r/index_merge_myisam.result | 12 +++-- mysql-test/r/range_innodb.result | 81 ++++++++++++++++++++++++++++++++++ mysql-test/t/range_innodb.test | 78 ++++++++++++++++++++++++++++++++ sql/opt_range.cc | 7 +++ 5 files changed, 181 insertions(+), 5 deletions(-) diff --git a/mysql-test/include/index_merge1.inc b/mysql-test/include/index_merge1.inc index b168a76..440f1f7 100644 --- a/mysql-test/include/index_merge1.inc +++ b/mysql-test/include/index_merge1.inc @@ -150,15 +150,19 @@ explain select * from t0 where (((key3 <7 and key7 < 6) or key5 < 2) and (key5 < 5 or key6 < 6)); explain select * from t0 where - ((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4)) + ((key3 < 4 or key5 < 4) and (key1 < 4 or key2 < 4)) or ((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6)); explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where - ((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4)) + ((key3 < 4 or key5 < 4) and (key1 < 4 or key2 < 4)) or ((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6)); +explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where + ((key3 < 5 or key5 < 4) and (key1 < 4 or key2 < 4)) + or + ((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6)); # 8. Verify that "order by" after index merge uses filesort select * from t0 where key1 < 5 or key8 < 4 order by key1; diff --git a/mysql-test/r/index_merge_myisam.result b/mysql-test/r/index_merge_myisam.result index 5a23092..a096c34 100644 --- a/mysql-test/r/index_merge_myisam.result +++ b/mysql-test/r/index_merge_myisam.result @@ -173,17 +173,23 @@ or id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t0 index_merge i1,i2,i3,i5,i6,i7 i3,i5 4,4 NULL 11 Using sort_union(i3,i5); Using where explain select * from t0 where -((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4)) +((key3 < 4 or key5 < 4) and (key1 < 4 or key2 < 4)) or ((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6)); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t0 ALL i1,i2,i3,i5,i6 NULL NULL NULL 1024 Using where explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where -((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4)) +((key3 < 4 or key5 < 4) and (key1 < 4 or key2 < 4)) or ((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6)); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 index_merge i1,i2,i3,i5,i6 i3,i5 0,4 NULL 1024 Using sort_union(i3,i5); Using where +1 SIMPLE t0 index_merge i1,i2,i3,i5,i6 i3,i5 4,4 NULL 1024 Using sort_union(i3,i5); Using where +explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where +((key3 < 5 or key5 < 4) and (key1 < 4 or key2 < 4)) +or +((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6)); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL i1,i2,i3,i5,i6 NULL NULL NULL 1024 Using where select * from t0 where key1 < 5 or key8 < 4 order by key1; key1 key2 key3 key4 key5 key6 key7 key8 1 1 1 1 1 1 1 1023 diff --git a/mysql-test/r/range_innodb.result b/mysql-test/r/range_innodb.result index f2349f2..ccb6da3 100644 --- a/mysql-test/r/range_innodb.result +++ b/mysql-test/r/range_innodb.result @@ -108,3 +108,84 @@ DROP TABLE t0,t1; SET @@GLOBAL.debug_dbug = @saved_dbug; set @@optimizer_switch= @optimizer_switch_save; # End of 10.1 tests +# +# MDEV-27262: Index intersection with full scan over an index +# +CREATE TABLE t1 ( +id int(10) unsigned NOT NULL AUTO_INCREMENT, +p char(32) DEFAULT NULL, +es tinyint(3) unsigned NOT NULL DEFAULT 0, +er tinyint(3) unsigned NOT NULL DEFAULT 0, +x mediumint(8) unsigned NOT NULL DEFAULT 0, +PRIMARY KEY (id), +INDEX es (es), +INDEX x (x), +INDEX er (er,x), +INDEX p (p) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +insert into t1(es,er) select 0, 1 from seq_1_to_45; +insert into t1(es,er) select 0, 2 from seq_1_to_49; +insert into t1(es,er) select 0, 3 from seq_1_to_951; +insert into t1(es,er) select 0, 3 from seq_1_to_1054; +insert into t1(es,er) select 0, 6 from seq_1_to_25; +insert into t1(es,er) select 0, 11 from seq_1_to_1; +insert into t1(es,er) select 1, 1 from seq_1_to_45; +insert into t1(es,er) select 1, 2 from seq_1_to_16; +insert into t1(es,er) select 1, 3 from seq_1_to_511; +insert into t1(es,er) select 1, 4 from seq_1_to_687; +insert into t1(es,er) select 1, 6 from seq_1_to_50; +insert into t1(es,er) select 1, 7 from seq_1_to_4; +insert into t1(es,er) select 1, 11 from seq_1_to_1; +insert into t1(es,er) select 2, 1 from seq_1_to_82; +insert into t1(es,er) select 2, 2 from seq_1_to_82; +insert into t1(es,er) select 2, 3 from seq_1_to_1626; +insert into t1(es,er) select 2, 4 from seq_1_to_977; +insert into t1(es,er) select 2, 6 from seq_1_to_33; +insert into t1(es,er) select 2, 11 from seq_1_to_1; +insert into t1(es,er) select 3, 1 from seq_1_to_245; +insert into t1(es,er) select 3, 2 from seq_1_to_81; +insert into t1(es,er) select 3, 3 from seq_1_to_852; +insert into t1(es,er) select 3, 4 from seq_1_to_2243; +insert into t1(es,er) select 3, 6 from seq_1_to_44; +insert into t1(es,er) select 3, 11 from seq_1_to_1; +insert into t1(es,er) select 4, 1 from seq_1_to_91; +insert into t1(es,er) select 4, 2 from seq_1_to_83; +insert into t1(es,er) select 4, 3 from seq_1_to_297; +insert into t1(es,er) select 4, 4 from seq_1_to_2456; +insert into t1(es,er) select 4, 6 from seq_1_to_19; +insert into t1(es,er) select 4, 11 from seq_1_to_1; +update t1 set p='foobar'; +update t1 set x=0; +set @save_isp=@@innodb_stats_persistent; +set global innodb_stats_persistent= 1; +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +set optimizer_switch='index_merge_sort_intersection=on'; +SELECT * FROM t1 +WHERE ((p = 'foo' AND er != 4) OR er = 4 ) AND (es >= 4) LIMIT 2; +id p es er x +14645 foobar 4 4 0 +14646 foobar 4 4 0 +EXPLAIN EXTENDED SELECT * FROM t1 +WHERE ((p = 'foo' AND er != 4) OR er = 4 ) AND (es >= 4) LIMIT 2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 range es,er,p es 1 NULL # 100.00 Using index condition; Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`p` AS `p`,`test`.`t1`.`es` AS `es`,`test`.`t1`.`er` AS `er`,`test`.`t1`.`x` AS `x` from `test`.`t1` where (`test`.`t1`.`p` = 'foo' and `test`.`t1`.`er` <> 4 or `test`.`t1`.`er` = 4) and `test`.`t1`.`es` >= 4 limit 2 +set optimizer_switch='index_merge_sort_intersection=off'; +SELECT * FROM t1 +WHERE ((p = 'foo' AND er != 4) OR er = 4 ) AND (es >= 4) LIMIT 2; +id p es er x +14645 foobar 4 4 0 +14646 foobar 4 4 0 +EXPLAIN EXTENDED SELECT * FROM t1 +WHERE ((p = 'foo' AND er != 4) OR er = 4 ) AND (es >= 4) LIMIT 2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 range es,er,p es 1 NULL # 100.00 Using index condition; Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`p` AS `p`,`test`.`t1`.`es` AS `es`,`test`.`t1`.`er` AS `er`,`test`.`t1`.`x` AS `x` from `test`.`t1` where (`test`.`t1`.`p` = 'foo' and `test`.`t1`.`er` <> 4 or `test`.`t1`.`er` = 4) and `test`.`t1`.`es` >= 4 limit 2 +set optimizer_switch='index_merge_sort_intersection=default'; +set global innodb_stats_persistent= @save_isp; +DROP TABLE t1; +# End of 10.2 tests diff --git a/mysql-test/t/range_innodb.test b/mysql-test/t/range_innodb.test index 428e5c2..7420e72 100644 --- a/mysql-test/t/range_innodb.test +++ b/mysql-test/t/range_innodb.test @@ -116,3 +116,81 @@ SET @@GLOBAL.debug_dbug = @saved_dbug; set @@optimizer_switch= @optimizer_switch_save; --echo # End of 10.1 tests + +--echo # +--echo # MDEV-27262: Index intersection with full scan over an index +--echo # + +--source include/have_sequence.inc + +CREATE TABLE t1 ( + id int(10) unsigned NOT NULL AUTO_INCREMENT, + p char(32) DEFAULT NULL, + es tinyint(3) unsigned NOT NULL DEFAULT 0, + er tinyint(3) unsigned NOT NULL DEFAULT 0, + x mediumint(8) unsigned NOT NULL DEFAULT 0, + PRIMARY KEY (id), + INDEX es (es), + INDEX x (x), + INDEX er (er,x), + INDEX p (p) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +insert into t1(es,er) select 0, 1 from seq_1_to_45; +insert into t1(es,er) select 0, 2 from seq_1_to_49; +insert into t1(es,er) select 0, 3 from seq_1_to_951; +insert into t1(es,er) select 0, 3 from seq_1_to_1054; +insert into t1(es,er) select 0, 6 from seq_1_to_25; +insert into t1(es,er) select 0, 11 from seq_1_to_1; +insert into t1(es,er) select 1, 1 from seq_1_to_45; +insert into t1(es,er) select 1, 2 from seq_1_to_16; +insert into t1(es,er) select 1, 3 from seq_1_to_511; +insert into t1(es,er) select 1, 4 from seq_1_to_687; +insert into t1(es,er) select 1, 6 from seq_1_to_50; +insert into t1(es,er) select 1, 7 from seq_1_to_4; +insert into t1(es,er) select 1, 11 from seq_1_to_1; +insert into t1(es,er) select 2, 1 from seq_1_to_82; +insert into t1(es,er) select 2, 2 from seq_1_to_82; +insert into t1(es,er) select 2, 3 from seq_1_to_1626; +insert into t1(es,er) select 2, 4 from seq_1_to_977; +insert into t1(es,er) select 2, 6 from seq_1_to_33; +insert into t1(es,er) select 2, 11 from seq_1_to_1; +insert into t1(es,er) select 3, 1 from seq_1_to_245; +insert into t1(es,er) select 3, 2 from seq_1_to_81; +insert into t1(es,er) select 3, 3 from seq_1_to_852; +insert into t1(es,er) select 3, 4 from seq_1_to_2243; +insert into t1(es,er) select 3, 6 from seq_1_to_44; +insert into t1(es,er) select 3, 11 from seq_1_to_1; +insert into t1(es,er) select 4, 1 from seq_1_to_91; +insert into t1(es,er) select 4, 2 from seq_1_to_83; +insert into t1(es,er) select 4, 3 from seq_1_to_297; +insert into t1(es,er) select 4, 4 from seq_1_to_2456; +insert into t1(es,er) select 4, 6 from seq_1_to_19; +insert into t1(es,er) select 4, 11 from seq_1_to_1; +update t1 set p='foobar'; +update t1 set x=0; +set @save_isp=@@innodb_stats_persistent; +set global innodb_stats_persistent= 1; +analyze table t1; + +let $q= +SELECT * FROM t1 + WHERE ((p = 'foo' AND er != 4) OR er = 4 ) AND (es >= 4) LIMIT 2; + +set optimizer_switch='index_merge_sort_intersection=on'; +eval $q; +--replace_column 9 # +eval EXPLAIN EXTENDED $q; + +set optimizer_switch='index_merge_sort_intersection=off'; +# execution of $q and explain for it led to an assertion failure in 10.4 +# (with the optimizer switch rowid_filter set to 'on') +eval $q; +--replace_column 9 # +eval EXPLAIN EXTENDED $q; +set optimizer_switch='index_merge_sort_intersection=default'; + +set global innodb_stats_persistent= @save_isp; +DROP TABLE t1; + +--echo # End of 10.2 tests diff --git a/sql/opt_range.cc b/sql/opt_range.cc index f3f1843..2e05b88 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -9413,6 +9413,13 @@ key_or(RANGE_OPT_PARAM *param, SEL_ARG *key1,SEL_ARG *key2) key2->copy_min(tmp); if (!(key1=key1->tree_delete(tmp))) { // Only one key in tree + if (key2->min_flag & NO_MIN_RANGE && + key2->max_flag & NO_MAX_RANGE) + { + if (key2->maybe_flag) + return new SEL_ARG(SEL_ARG::MAYBE_KEY); + return 0; // Always true OR + } key1=key2; key1->make_root(); key2=key2_next;

2 1

[Commits] 32692140e1a: MDEV-27306: SET STATEMENT optimizer_trace=1 Doesn't save the trace
by psergey 19 Dec '21

19 Dec '21

revision-id: 32692140e1a4f9aa87359f3ef6efe4a615119e71 (mariadb-10.6.5-39-g32692140e1a) parent(s): 946dafb260fc5683e1ec1410a801f2235ba2313a author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-12-19 17:19:02 +0300 message: MDEV-27306: SET STATEMENT optimizer_trace=1 Doesn't save the trace In mysql_execute_command(), move optimizer trace initialization to be after run_set_statement_if_requested() call. Unfortunately, mysql_execute_command() code uses "goto error" a lot, and this means optimizer trace code cannot use RAII objects. Work this around by: - Make Opt_trace_start a non-RAII object, add init() method. - Move the code that writes the top-level object and array into Opt_trace_start::init(). --- mysql-test/main/opt_trace.result | 17 +++++++++++++++++ mysql-test/main/opt_trace.test | 8 ++++++++ sql/opt_trace.cc | 20 ++++++++++++++------ sql/opt_trace.h | 18 +++++++++++------- sql/sp_head.cc | 7 +++---- sql/sql_parse.cc | 11 +++++------ sql/sql_prepare.cc | 6 +++--- 7 files changed, 61 insertions(+), 26 deletions(-) diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index b0c2a9ca4d9..477c1f31095 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -9248,5 +9248,22 @@ json_detailed(json_extract(trace, '$**.best_join_order')) ] ] DROP TABLE t1; +# +# MDEV-27306: SET STATEMENT optimizer_trace=1 Doesn't save the trace +# +set optimizer_trace=0; +set statement optimizer_trace=1 for select * from seq_1_to_10 where seq<2; +seq +1 +# The trace must not be empty: +select left(trace, 100) from information_schema.optimizer_trace; +left(trace, 100) +{ + "steps": [ + { + "join_preparation": { + "select_id": 1, + "steps": [ + # End of 10.6 tests set optimizer_trace='enabled=off'; diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test index 402caf2a165..9a7aa017cd4 100644 --- a/mysql-test/main/opt_trace.test +++ b/mysql-test/main/opt_trace.test @@ -865,5 +865,13 @@ select json_detailed(json_extract(trace, '$**.best_join_order')) from information_schema.OPTIMIZER_TRACE; DROP TABLE t1; +--echo # +--echo # MDEV-27306: SET STATEMENT optimizer_trace=1 Doesn't save the trace +--echo # +set optimizer_trace=0; +set statement optimizer_trace=1 for select * from seq_1_to_10 where seq<2; +--echo # The trace must not be empty: +select left(trace, 100) from information_schema.optimizer_trace; + --echo # End of 10.6 tests set optimizer_trace='enabled=off'; diff --git a/sql/opt_trace.cc b/sql/opt_trace.cc index ba9220cac44..4bc493940fb 100644 --- a/sql/opt_trace.cc +++ b/sql/opt_trace.cc @@ -471,12 +471,14 @@ void Opt_trace_context::end() current_trace= NULL; } -Opt_trace_start::Opt_trace_start(THD *thd, TABLE_LIST *tbl, - enum enum_sql_command sql_command, - List<set_var_base> *set_vars, - const char *query, - size_t query_length, - const CHARSET_INFO *query_charset):ctx(&thd->opt_trace) + +void Opt_trace_start::init(THD *thd, + TABLE_LIST *tbl, + enum enum_sql_command sql_command, + List<set_var_base> *set_vars, + const char *query, + size_t query_length, + const CHARSET_INFO *query_charset) { /* if optimizer trace is enabled and the statment we have is traceable, @@ -496,6 +498,9 @@ Opt_trace_start::Opt_trace_start(THD *thd, TABLE_LIST *tbl, ctx->set_query(query, query_length, query_charset); traceable= TRUE; opt_trace_disable_if_no_tables_access(thd, tbl); + Json_writer *w= ctx->get_current_json(); + w->start_object(); + w->add_member("steps").start_array(); } } @@ -503,6 +508,9 @@ Opt_trace_start::~Opt_trace_start() { if (traceable) { + Json_writer *w= ctx->get_current_json(); + w->end_array(); + w->end_object(); ctx->end(); traceable= FALSE; } diff --git a/sql/opt_trace.h b/sql/opt_trace.h index 101fb5f707e..1ee23a33591 100644 --- a/sql/opt_trace.h +++ b/sql/opt_trace.h @@ -72,14 +72,18 @@ struct Opt_trace_info */ -class Opt_trace_start { +class Opt_trace_start +{ public: - Opt_trace_start(THD *thd_arg, TABLE_LIST *tbl, - enum enum_sql_command sql_command, - List<set_var_base> *set_vars, - const char *query, - size_t query_length, - const CHARSET_INFO *query_charset); + Opt_trace_start(THD *thd_arg): ctx(&thd_arg->opt_trace), traceable(false) {} + + void init(THD *thd, TABLE_LIST *tbl, + enum enum_sql_command sql_command, + List<set_var_base> *set_vars, + const char *query, + size_t query_length, + const CHARSET_INFO *query_charset); + ~Opt_trace_start(); private: diff --git a/sql/sp_head.cc b/sql/sp_head.cc index 641c88de826..97905f6a5b4 100644 --- a/sql/sp_head.cc +++ b/sql/sp_head.cc @@ -3489,10 +3489,9 @@ sp_lex_keeper::reset_lex_and_exec_core(THD *thd, uint *nextp, thd->lex->safe_to_cache_query= 0; #endif - Opt_trace_start ots(thd, m_lex->query_tables, - SQLCOM_SELECT, &m_lex->var_list, - NULL, 0, - thd->variables.character_set_client); + Opt_trace_start ots(thd); + ots.init(thd, m_lex->query_tables, SQLCOM_SELECT, &m_lex->var_list, + NULL, 0, thd->variables.character_set_client); Json_writer_object trace_command(thd); Json_writer_array trace_command_steps(thd, "steps"); diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 2a48c8fb1ce..0886fc85151 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -3644,12 +3644,7 @@ mysql_execute_command(THD *thd, bool is_called_from_prepared_stmt) #ifdef HAVE_REPLICATION } /* endif unlikely slave */ #endif - Opt_trace_start ots(thd, all_tables, lex->sql_command, &lex->var_list, - thd->query(), thd->query_length(), - thd->variables.character_set_client); - - Json_writer_object trace_command(thd); - Json_writer_array trace_command_steps(thd, "steps"); + Opt_trace_start ots(thd); /* store old value of binlog format */ enum_binlog_format orig_binlog_format,orig_current_stmt_binlog_format; @@ -3715,6 +3710,10 @@ mysql_execute_command(THD *thd, bool is_called_from_prepared_stmt) if (run_set_statement_if_requested(thd, lex)) goto error; + /* After SET STATEMENT is done, we can initialize the Optimizer Trace: */ + ots.init(thd, all_tables, lex->sql_command, &lex->var_list, thd->query(), + thd->query_length(), thd->variables.character_set_client); + if (thd->lex->mi.connection_name.str == NULL) thd->lex->mi.connection_name= thd->variables.default_master_connection; diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc index 4ead77c225f..67032142591 100644 --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -2437,9 +2437,9 @@ static bool check_prepared_statement(Prepared_statement *stmt) For the optimizer trace, this is the symmetric, for statement preparation, of what is done at statement execution (in mysql_execute_command()). */ - Opt_trace_start ots(thd, tables, lex->sql_command, &lex->var_list, - thd->query(), thd->query_length(), - thd->variables.character_set_client); + Opt_trace_start ots(thd); + ots.init(thd, tables, lex->sql_command, &lex->var_list, thd->query(), + thd->query_length(), thd->variables.character_set_client); Json_writer_object trace_command(thd); Json_writer_array trace_command_steps(thd, "steps");

1 0

[Commits] c2feba8ecc8: MDEV-27188: Suppress optimizer output when executing prepare
by psergey 17 Dec '21

17 Dec '21

revision-id: c2feba8ecc8e8ab167efb4f4298469f345c359fb (mariadb-10.7.1-3-gc2feba8ecc8) parent(s): 06988bdcaa2d1af2c178c199b7f65dbafda45a2c author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-12-17 20:29:41 +0300 message: MDEV-27188: Suppress optimizer output when executing prepare - Do not write anything into Optimizer Trace at Prepare phase - When the query gets an error at Prepare phase, make sure there is no trace written, either. This is important as we need to produce the same trace for "mtr --ps-protocol" and regular mtr run. - For other kinds of errors, trace is still produced as it might be valuable. --- mysql-test/main/opt_trace,ps.rdiff | 92 --- mysql-test/main/opt_trace.result | 621 +++------------------ mysql-test/main/opt_trace_index_merge.result | 11 +- .../main/opt_trace_index_merge_innodb.result | 11 +- mysql-test/main/opt_trace_security.result | 38 +- sql/my_json_writer.h | 8 + sql/opt_subselect.cc | 13 +- sql/opt_trace.cc | 60 ++ sql/opt_trace.h | 37 +- sql/opt_trace_context.h | 6 + sql/sql_derived.cc | 30 +- sql/sql_parse.cc | 4 + sql/sql_prepare.cc | 11 - sql/sql_select.cc | 17 +- 14 files changed, 235 insertions(+), 724 deletions(-) diff --git a/mysql-test/main/opt_trace,ps.rdiff b/mysql-test/main/opt_trace,ps.rdiff deleted file mode 100644 index 3e2218de673..00000000000 --- a/mysql-test/main/opt_trace,ps.rdiff +++ /dev/null @@ -1,92 +0,0 @@ ---- /Users/shulga/projects/mariadb/server-10.6/mysql-test/main/opt_trace.result 2021-07-21 19:17:11.000000000 +0700 -+++ /Users/shulga/projects/mariadb/server-10.6/mysql-test/main/opt_trace.reject 2021-07-21 19:17:48.000000000 +0700 -@@ -2829,14 +2829,6 @@ - } - }, - { -- "transformation": { -- "select_id": 2, -- "from": "IN (SELECT)", -- "to": "semijoin", -- "chosen": true -- } -- }, -- { - "expanded_query": "/* select#2 */ select t10.pk from t10" - } - ] -@@ -4402,14 +4394,6 @@ - } - }, - { -- "transformation": { -- "select_id": 2, -- "from": "IN (SELECT)", -- "to": "semijoin", -- "chosen": true -- } -- }, -- { - "expanded_query": "/* select#2 */ select t_inner_1.a from t1 t_inner_1 join t1 t_inner_2" - } - ] -@@ -4852,14 +4836,6 @@ - } - }, - { -- "transformation": { -- "select_id": 2, -- "from": "IN (SELECT)", -- "to": "semijoin", -- "chosen": true -- } -- }, -- { - "expanded_query": "/* select#2 */ select t_inner_1.a from t2 t_inner_2 join t1 t_inner_1" - } - ] -@@ -4879,14 +4855,6 @@ - } - }, - { -- "transformation": { -- "select_id": 3, -- "from": "IN (SELECT)", -- "to": "semijoin", -- "chosen": true -- } -- }, -- { - "expanded_query": "/* select#3 */ select t_inner_3.a from t2 t_inner_3 join t1 t_inner_4" - } - ] -@@ -6432,14 +6400,6 @@ - } - }, - { -- "transformation": { -- "select_id": 2, -- "from": "IN (SELECT)", -- "to": "semijoin", -- "chosen": true -- } -- }, -- { - "expanded_query": "/* select#2 */ select t_inner_1.a from t2 t_inner_2 join t1 t_inner_1" - } - ] -@@ -6459,14 +6419,6 @@ - } - }, - { -- "transformation": { -- "select_id": 3, -- "from": "IN (SELECT)", -- "to": "semijoin", -- "chosen": true -- } -- }, -- { - "expanded_query": "/* select#3 */ select t_inner_3.a from t2 t_inner_3 join t1 t_inner_4" - } - ] diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index b0c2a9ca4d9..18ef904633b 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -38,36 +38,18 @@ QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES select * from v1 { "steps": [ { - "join_preparation": { + "join_optimization": { "select_id": 1, + "expanded_query": "/* select#1 */ select t1.a AS a,t1.b AS b from v1", "steps": [ { "view": { "table": "v1", "select_id": 2, - "algorithm": "merged" + "algorithm": "merged", + "expanded_query": "/* select#2 */ select t1.a AS a,t1.b AS b from t1 where t1.a = 1" } }, - { - "join_preparation": { - "select_id": 2, - "steps": [ - { - "expanded_query": "/* select#2 */ select t1.a AS a,t1.b AS b from t1 where t1.a = 1" - } - ] - } - }, - { - "expanded_query": "/* select#1 */ select t1.a AS a,t1.b AS b from v1" - } - ] - } - }, - { - "join_optimization": { - "select_id": 1, - "steps": [ { "condition_processing": { "condition": "WHERE", @@ -189,36 +171,18 @@ QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES select * from (select * from t1 where t1.a=1)q { "steps": [ { - "join_preparation": { + "join_optimization": { "select_id": 1, + "expanded_query": "/* select#1 */ select t1.a AS a,t1.b AS b from (/* select#2 */ select t1.a AS a,t1.b AS b from t1 where t1.a = 1) q", "steps": [ { "derived": { "table": "q", "select_id": 2, - "algorithm": "merged" - } - }, - { - "join_preparation": { - "select_id": 2, - "steps": [ - { - "expanded_query": "/* select#2 */ select t1.a AS a,t1.b AS b from t1 where t1.a = 1" - } - ] + "algorithm": "merged", + "expanded_query": "/* select#2 */ select t1.a AS a,t1.b AS b from t1 where t1.a = 1" } }, - { - "expanded_query": "/* select#1 */ select t1.a AS a,t1.b AS b from (/* select#2 */ select t1.a AS a,t1.b AS b from t1 where t1.a = 1) q" - } - ] - } - }, - { - "join_optimization": { - "select_id": 1, - "steps": [ { "condition_processing": { "condition": "WHERE", @@ -340,40 +304,15 @@ select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES select * from v2 { "steps": [ - { - "join_preparation": { - "select_id": 1, - "steps": [ - { - "view": { - "table": "v2", - "select_id": 2, - "algorithm": "materialized" - } - }, - { - "join_preparation": { - "select_id": 2, - "steps": [ - { - "expanded_query": "/* select#2 */ select t1.a AS a,t1.b AS b from t1 where t1.a = 1 group by t1.b" - } - ] - } - }, - { - "expanded_query": "/* select#1 */ select v2.a AS a,v2.b AS b from v2" - } - ] - } - }, { "join_optimization": { "select_id": 1, + "expanded_query": "/* select#1 */ select v2.a AS a,v2.b AS b from v2", "steps": [ { "join_optimization": { "select_id": 2, + "expanded_query": "/* select#2 */ select t1.a AS a,t1.b AS b from t1 where t1.a = 1 group by t1.b", "steps": [ { "condition_processing": { @@ -590,36 +529,18 @@ QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain select * from v2 { "steps": [ { - "join_preparation": { + "join_optimization": { "select_id": 1, + "expanded_query": "/* select#1 */ select t2.a AS a from v2", "steps": [ { "view": { "table": "v2", "select_id": 2, - "algorithm": "merged" + "algorithm": "merged", + "expanded_query": "/* select#2 */ select t2.a AS a from t2" } }, - { - "join_preparation": { - "select_id": 2, - "steps": [ - { - "expanded_query": "/* select#2 */ select t2.a AS a from t2" - } - ] - } - }, - { - "expanded_query": "/* select#1 */ select t2.a AS a from v2" - } - ] - } - }, - { - "join_optimization": { - "select_id": 1, - "steps": [ { "table_dependencies": [ { @@ -702,40 +623,15 @@ select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain select * from v1 { "steps": [ - { - "join_preparation": { - "select_id": 1, - "steps": [ - { - "view": { - "table": "v1", - "select_id": 2, - "algorithm": "materialized" - } - }, - { - "join_preparation": { - "select_id": 2, - "steps": [ - { - "expanded_query": "/* select#2 */ select t1.a AS a from t1 group by t1.b" - } - ] - } - }, - { - "expanded_query": "/* select#1 */ select v1.a AS a from v1" - } - ] - } - }, { "join_optimization": { "select_id": 1, + "expanded_query": "/* select#1 */ select v1.a AS a from v1", "steps": [ { "join_optimization": { "select_id": 2, + "expanded_query": "/* select#2 */ select t1.a AS a from t1 group by t1.b", "steps": [ { "table_dependencies": [ @@ -911,19 +807,10 @@ select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { "steps": [ - { - "join_preparation": { - "select_id": 1, - "steps": [ - { - "expanded_query": "select t1.a AS a,t1.b AS b,t1.c AS c,t2.a AS a,t2.b AS b,t2.c AS c from t1 join t2 where t1.a = t2.b + 2 and t2.a = t1.b" - } - ] - } - }, { "join_optimization": { "select_id": 1, + "expanded_query": "select t1.a AS a,t1.b AS b,t1.c AS c,t2.a AS a,t2.b AS b,t2.c AS c from t1 join t2 where t1.a = t2.b + 2 and t2.a = t1.b", "steps": [ { "condition_processing": { @@ -1162,19 +1049,10 @@ select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES EXPLAIN SELECT DISTINCT a FROM t1 { "steps": [ - { - "join_preparation": { - "select_id": 1, - "steps": [ - { - "expanded_query": "select distinct t1.a AS a from t1" - } - ] - } - }, { "join_optimization": { "select_id": 1, + "expanded_query": "select distinct t1.a AS a from t1", "steps": [ { "table_dependencies": [ @@ -1327,19 +1205,10 @@ select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { "steps": [ - { - "join_preparation": { - "select_id": 1, - "steps": [ - { - "expanded_query": "select min(t1.d) AS `MIN(d)` from t1 where t1.b = 2 and t1.c = 3 group by t1.a" - } - ] - } - }, { "join_optimization": { "select_id": 1, + "expanded_query": "select min(t1.d) AS `MIN(d)` from t1 where t1.b = 2 and t1.c = 3 group by t1.a", "steps": [ { "condition_processing": { @@ -1526,19 +1395,10 @@ select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id { "steps": [ - { - "join_preparation": { - "select_id": 1, - "steps": [ - { - "expanded_query": "select t1.`id` AS `id`,min(t1.a) AS `MIN(a)`,max(t1.a) AS `MAX(a)` from t1 where t1.a >= 20010104e0 group by t1.`id`" - } - ] - } - }, { "join_optimization": { "select_id": 1, + "expanded_query": "select t1.`id` AS `id`,min(t1.a) AS `MIN(a)`,max(t1.a) AS `MAX(a)` from t1 where t1.a >= 20010104e0 group by t1.`id`", "steps": [ { "condition_processing": { @@ -1714,19 +1574,10 @@ select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id { "steps": [ - { - "join_preparation": { - "select_id": 1, - "steps": [ - { - "expanded_query": "select t1.`id` AS `id`,t1.a AS a from t1 where t1.a = 20010104e0 group by t1.`id`" - } - ] - } - }, { "join_optimization": { "select_id": 1, + "expanded_query": "select t1.`id` AS `id`,t1.a AS a from t1 where t1.a = 20010104e0 group by t1.`id`", "steps": [ { "condition_processing": { @@ -1929,19 +1780,10 @@ select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain select * from t1 where a=1 and b=2 order by c limit 1 { "steps": [ - { - "join_preparation": { - "select_id": 1, - "steps": [ - { - "expanded_query": "select t1.pk AS pk,t1.a AS a,t1.b AS b,t1.c AS c,t1.filler AS filler from t1 where t1.a = 1 and t1.b = 2 order by t1.c limit 1" - } - ] - } - }, { "join_optimization": { "select_id": 1, + "expanded_query": "select t1.pk AS pk,t1.a AS a,t1.b AS b,t1.c AS c,t1.filler AS filler from t1 where t1.a = 1 and t1.b = 2 order by t1.c limit 1", "steps": [ { "condition_processing": { @@ -2305,19 +2147,10 @@ QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain select t1.a from t1 left join t2 on t1.a=t2.a { "steps": [ - { - "join_preparation": { - "select_id": 1, - "steps": [ - { - "expanded_query": "select t1.a AS a from (t1 left join t2 on(t1.a = t2.a))" - } - ] - } - }, { "join_optimization": { "select_id": 1, + "expanded_query": "select t1.a AS a from (t1 left join t2 on(t1.a = t2.a))", "steps": [ { "build_equal_items": { @@ -2442,19 +2275,10 @@ select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain select * from t1 left join t2 on t2.a=t1.a { "steps": [ - { - "join_preparation": { - "select_id": 1, - "steps": [ - { - "expanded_query": "select t1.a AS a,t2.a AS a,t2.b AS b from (t1 left join t2 on(t2.a = t1.a))" - } - ] - } - }, { "join_optimization": { "select_id": 1, + "expanded_query": "select t1.a AS a,t2.a AS a,t2.b AS b from (t1 left join t2 on(t2.a = t1.a))", "steps": [ { "build_equal_items": { @@ -2621,19 +2445,10 @@ select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain select t1.a from t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and t3.a=t1.a { "steps": [ - { - "join_preparation": { - "select_id": 1, - "steps": [ - { - "expanded_query": "select t1.a AS a from (t1 left join (t2 join t3 on(t2.b = t3.b)) on(t2.a = t1.a and t3.a = t1.a))" - } - ] - } - }, { "join_optimization": { "select_id": 1, + "expanded_query": "select t1.a AS a from (t1 left join (t2 join t3 on(t2.b = t3.b)) on(t2.a = t1.a and t3.a = t1.a))", "steps": [ { "build_equal_items": { @@ -2812,46 +2627,19 @@ QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain extended select * from t1 where a in (select pk from t10) { "steps": [ { - "join_preparation": { + "join_optimization": { "select_id": 1, + "expanded_query": "/* select#1 */ select t1.a AS a,t1.b AS b from t1 where t1.a in (/* select#2 */ select t10.pk from t10)", "steps": [ { - "join_preparation": { + "transformation": { "select_id": 2, - "steps": [ - { - "transformation": { - "select_id": 2, - "from": "IN (SELECT)", - "to": "materialization", - "sjm_scan_allowed": true, - "possible": true - } - }, - { - "transformation": { - "select_id": 2, - "from": "IN (SELECT)", - "to": "semijoin", - "chosen": true - } - }, - { - "expanded_query": "/* select#2 */ select t10.pk from t10" - } - ] + "from": "IN (SELECT)", + "to": "materialization", + "sjm_scan_allowed": true, + "possible": true } }, - { - "expanded_query": "/* select#1 */ select t1.a AS a,t1.b AS b from t1 where t1.a in (/* select#2 */ select t10.pk from t10)" - } - ] - } - }, - { - "join_optimization": { - "select_id": 1, - "steps": [ { "transformation": { "select_id": 2, @@ -3135,19 +2923,10 @@ select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain select * from t1 where pk = 2 and a=5 and b=1 { "steps": [ - { - "join_preparation": { - "select_id": 1, - "steps": [ - { - "expanded_query": "select t1.pk AS pk,t1.a AS a,t1.b AS b from t1 where t1.pk = 2 and t1.a = 5 and t1.b = 1" - } - ] - } - }, { "join_optimization": { "select_id": 1, + "expanded_query": "select t1.pk AS pk,t1.a AS a,t1.b AS b from t1 where t1.pk = 2 and t1.a = 5 and t1.b = 1", "steps": [ { "condition_processing": { @@ -3496,19 +3275,10 @@ select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES select f1(a) from t1 { "steps": [ - { - "join_preparation": { - "select_id": 1, - "steps": [ - { - "expanded_query": "select f1(t1.a) AS `f1(a)` from t1" - } - ] - } - }, { "join_optimization": { "select_id": 1, + "expanded_query": "select f1(t1.a) AS `f1(a)` from t1", "steps": [ { "table_dependencies": [ @@ -3593,19 +3363,10 @@ select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES select f2(a) from t1 { "steps": [ - { - "join_preparation": { - "select_id": 1, - "steps": [ - { - "expanded_query": "select f2(t1.a) AS `f2(a)` from t1" - } - ] - } - }, { "join_optimization": { "select_id": 1, + "expanded_query": "select f2(t1.a) AS `f2(a)` from t1", "steps": [ { "table_dependencies": [ @@ -3697,7 +3458,7 @@ a 2 select length(trace) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; length(trace) -2141 +2012 set optimizer_trace_max_mem_size=100; select * from t1; a @@ -3708,10 +3469,9 @@ QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES select * from t1 { "steps": [ { - "join_preparation": { + "join_optimization": { "select_id": 1, - "steps": [ - 2041 0 + "expanded_query": 1912 0 set optimizer_trace_max_mem_size=0; select * from t1; a @@ -3719,7 +3479,7 @@ a 2 select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES -select * from t1 2141 0 +select * from t1 2012 0 drop table t1; set optimizer_trace='enabled=off'; set @@optimizer_trace_max_mem_size= @save_optimizer_trace_max_mem_size; @@ -3809,19 +3569,10 @@ select * from information_schema.optimizer_trace; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "steps": [ - { - "join_preparation": { - "select_id": 1, - "steps": [ - { - "expanded_query": "select NULL AS `NULL` from t0 join t1 where t0.a = t1.a and t1.a < 3" - } - ] - } - }, { "join_optimization": { "select_id": 1, + "expanded_query": "select NULL AS `NULL` from t0 join t1 where t0.a = t1.a and t1.a < 3", "steps": [ { "condition_processing": { @@ -4175,36 +3926,10 @@ select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain select * from (select rand() from t1)q { "steps": [ - { - "join_preparation": { - "select_id": 1, - "steps": [ - { - "derived": { - "table": "q", - "select_id": 2, - "algorithm": "merged" - } - }, - { - "join_preparation": { - "select_id": 2, - "steps": [ - { - "expanded_query": "/* select#2 */ select rand() AS `rand()` from t1" - } - ] - } - }, - { - "expanded_query": "/* select#1 */ select rand() AS `rand()` from (/* select#2 */ select rand() AS `rand()` from t1) q" - } - ] - } - }, { "join_optimization": { "select_id": 1, + "expanded_query": "/* select#1 */ select rand() AS `rand()` from (/* select#2 */ select rand() AS `rand()` from t1) q", "steps": [ { "derived": { @@ -4217,6 +3942,7 @@ explain select * from (select rand() from t1)q { { "join_optimization": { "select_id": 2, + "expanded_query": "/* select#2 */ select rand() AS `rand()` from t1", "steps": [ { "table_dependencies": [ @@ -4385,46 +4111,19 @@ QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_inner_2) { "steps": [ { - "join_preparation": { + "join_optimization": { "select_id": 1, + "expanded_query": "/* select#1 */ select t1.a AS a from t1 where t1.a in (/* select#2 */ select t_inner_1.a from t1 t_inner_1 join t1 t_inner_2)", "steps": [ { - "join_preparation": { + "transformation": { "select_id": 2, - "steps": [ - { - "transformation": { - "select_id": 2, - "from": "IN (SELECT)", - "to": "materialization", - "sjm_scan_allowed": true, - "possible": true - } - }, - { - "transformation": { - "select_id": 2, - "from": "IN (SELECT)", - "to": "semijoin", - "chosen": true - } - }, - { - "expanded_query": "/* select#2 */ select t_inner_1.a from t1 t_inner_1 join t1 t_inner_2" - } - ] + "from": "IN (SELECT)", + "to": "materialization", + "sjm_scan_allowed": true, + "possible": true } }, - { - "expanded_query": "/* select#1 */ select t1.a AS a from t1 where t1.a in (/* select#2 */ select t_inner_1.a from t1 t_inner_1 join t1 t_inner_2)" - } - ] - } - }, - { - "join_optimization": { - "select_id": 1, - "steps": [ { "transformation": { "select_id": 2, @@ -4835,73 +4534,19 @@ explain select * from t1 t_outer_1,t2 t_outer_2 where t_outer_1.a in (select t_ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "steps": [ { - "join_preparation": { + "join_optimization": { "select_id": 1, + "expanded_query": "/* select#1 */ select t_outer_1.a AS a,t_outer_2.a AS a from t1 t_outer_1 join t2 t_outer_2 where t_outer_1.a in (/* select#2 */ select t_inner_1.a from t2 t_inner_2 join t1 t_inner_1) and t_outer_2.a in (/* select#3 */ select t_inner_3.a from t2 t_inner_3 join t1 t_inner_4)", "steps": [ { - "join_preparation": { + "transformation": { "select_id": 2, - "steps": [ - { - "transformation": { - "select_id": 2, - "from": "IN (SELECT)", - "to": "materialization", - "sjm_scan_allowed": true, - "possible": true - } - }, - { - "transformation": { - "select_id": 2, - "from": "IN (SELECT)", - "to": "semijoin", - "chosen": true - } - }, - { - "expanded_query": "/* select#2 */ select t_inner_1.a from t2 t_inner_2 join t1 t_inner_1" - } - ] - } - }, - { - "join_preparation": { - "select_id": 3, - "steps": [ - { - "transformation": { - "select_id": 3, - "from": "IN (SELECT)", - "to": "materialization", - "sjm_scan_allowed": true, - "possible": true - } - }, - { - "transformation": { - "select_id": 3, - "from": "IN (SELECT)", - "to": "semijoin", - "chosen": true - } - }, - { - "expanded_query": "/* select#3 */ select t_inner_3.a from t2 t_inner_3 join t1 t_inner_4" - } - ] + "from": "IN (SELECT)", + "to": "materialization", + "sjm_scan_allowed": true, + "possible": true } }, - { - "expanded_query": "/* select#1 */ select t_outer_1.a AS a,t_outer_2.a AS a from t1 t_outer_1 join t2 t_outer_2 where t_outer_1.a in (/* select#2 */ select t_inner_1.a from t2 t_inner_2 join t1 t_inner_1) and t_outer_2.a in (/* select#3 */ select t_inner_3.a from t2 t_inner_3 join t1 t_inner_4)" - } - ] - } - }, - { - "join_optimization": { - "select_id": 1, - "steps": [ { "transformation": { "select_id": 2, @@ -4910,6 +4555,15 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "converted_to_semi_join": true } }, + { + "transformation": { + "select_id": 3, + "from": "IN (SELECT)", + "to": "materialization", + "sjm_scan_allowed": true, + "possible": true + } + }, { "transformation": { "select_id": 3, @@ -6415,73 +6069,19 @@ explain select * from t1 t_outer_1,t2 t_outer_2 where t_outer_1.a in (select t_ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "steps": [ { - "join_preparation": { + "join_optimization": { "select_id": 1, + "expanded_query": "/* select#1 */ select t_outer_1.a AS a,t_outer_2.a AS a from t1 t_outer_1 join t2 t_outer_2 where t_outer_1.a in (/* select#2 */ select t_inner_1.a from t2 t_inner_2 join t1 t_inner_1) and t_outer_2.a in (/* select#3 */ select t_inner_3.a from t2 t_inner_3 join t1 t_inner_4)", "steps": [ { - "join_preparation": { + "transformation": { "select_id": 2, - "steps": [ - { - "transformation": { - "select_id": 2, - "from": "IN (SELECT)", - "to": "materialization", - "sjm_scan_allowed": true, - "possible": true - } - }, - { - "transformation": { - "select_id": 2, - "from": "IN (SELECT)", - "to": "semijoin", - "chosen": true - } - }, - { - "expanded_query": "/* select#2 */ select t_inner_1.a from t2 t_inner_2 join t1 t_inner_1" - } - ] - } - }, - { - "join_preparation": { - "select_id": 3, - "steps": [ - { - "transformation": { - "select_id": 3, - "from": "IN (SELECT)", - "to": "materialization", - "sjm_scan_allowed": true, - "possible": true - } - }, - { - "transformation": { - "select_id": 3, - "from": "IN (SELECT)", - "to": "semijoin", - "chosen": true - } - }, - { - "expanded_query": "/* select#3 */ select t_inner_3.a from t2 t_inner_3 join t1 t_inner_4" - } - ] + "from": "IN (SELECT)", + "to": "materialization", + "sjm_scan_allowed": true, + "possible": true } }, - { - "expanded_query": "/* select#1 */ select t_outer_1.a AS a,t_outer_2.a AS a from t1 t_outer_1 join t2 t_outer_2 where t_outer_1.a in (/* select#2 */ select t_inner_1.a from t2 t_inner_2 join t1 t_inner_1) and t_outer_2.a in (/* select#3 */ select t_inner_3.a from t2 t_inner_3 join t1 t_inner_4)" - } - ] - } - }, - { - "join_optimization": { - "select_id": 1, - "steps": [ { "transformation": { "select_id": 2, @@ -6490,6 +6090,15 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "converted_to_semi_join": true } }, + { + "transformation": { + "select_id": 3, + "from": "IN (SELECT)", + "to": "materialization", + "sjm_scan_allowed": true, + "possible": true + } + }, { "transformation": { "select_id": 3, @@ -8677,19 +8286,10 @@ SELECT query, trace FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; query trace SELECT 'a\0' LIMIT 0 { "steps": [ - { - "join_preparation": { - "select_id": 1, - "steps": [ - { - "expanded_query": "select 'a\0' AS `a\x00` limit 0" - } - ] - } - }, { "join_optimization": { "select_id": 1, + "expanded_query": "select 'a\0' AS `a\x00` limit 0", "steps": [] } }, @@ -8716,19 +8316,10 @@ select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES select count(*) from seq_1_to_10000000 { "steps": [ - { - "join_preparation": { - "select_id": 1, - "steps": [ - { - "expanded_query": "select count(0) AS `count(*)` from seq_1_to_10000000" - } - ] - } - }, { "join_optimization": { "select_id": 1, + "expanded_query": "select count(0) AS `count(*)` from seq_1_to_10000000", "steps": [ { "table_dependencies": [ @@ -8855,50 +8446,6 @@ json_detailed(json_extract(trace, '$**.in_to_subquery_conversion')) "item": "t0.a in (1,2,3,4,5,6)", "conversion": [ - - { - "join_preparation": - { - "select_id": 2, - "steps": - [ - - { - "derived": - { - "table": "tvc_0", - "select_id": 3, - "algorithm": "materialized" - } - }, - - { - "transformation": - { - "select_id": 2, - "from": "IN (SELECT)", - "to": "materialization", - "sjm_scan_allowed": true, - "possible": true - } - }, - - { - "transformation": - { - "select_id": 2, - "from": "IN (SELECT)", - "to": "semijoin", - "chosen": true - } - }, - - { - "expanded_query": "/* select#2 */ select tvc_0._col_1 from (values (1),(2),(3),(4),(5),(6)) tvc_0" - } - ] - } - } ] } ] @@ -8954,7 +8501,7 @@ set @path= (select json_search(@trace, 'one', 'no predicate for first keypart')) set @sub_path= substr(@path, 2, locate('.best_access_path', @path)-2); select @sub_path; @sub_path -$.steps[1].join_optimization.steps[4].considered_execution_plans[0].rest_of_plan[0] +$.steps[0].join_optimization.steps[4].considered_execution_plans[0].rest_of_plan[0] select json_detailed(json_extract( @trace, diff --git a/mysql-test/main/opt_trace_index_merge.result b/mysql-test/main/opt_trace_index_merge.result index f1e13586eda..b319639e9fa 100644 --- a/mysql-test/main/opt_trace_index_merge.result +++ b/mysql-test/main/opt_trace_index_merge.result @@ -19,19 +19,10 @@ select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain select * from t1 where a=1 or b=1 { "steps": [ - { - "join_preparation": { - "select_id": 1, - "steps": [ - { - "expanded_query": "select t1.a AS a,t1.b AS b,t1.c AS c,t1.filler AS filler from t1 where t1.a = 1 or t1.b = 1" - } - ] - } - }, { "join_optimization": { "select_id": 1, + "expanded_query": "select t1.a AS a,t1.b AS b,t1.c AS c,t1.filler AS filler from t1 where t1.a = 1 or t1.b = 1", "steps": [ { "condition_processing": { diff --git a/mysql-test/main/opt_trace_index_merge_innodb.result b/mysql-test/main/opt_trace_index_merge_innodb.result index 0ddaaeae89d..bc063015e6d 100644 --- a/mysql-test/main/opt_trace_index_merge_innodb.result +++ b/mysql-test/main/opt_trace_index_merge_innodb.result @@ -27,19 +27,10 @@ select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain select * from t1 where pk1 != 0 and key1 = 1 { "steps": [ - { - "join_preparation": { - "select_id": 1, - "steps": [ - { - "expanded_query": "select t1.pk1 AS pk1,t1.pk2 AS pk2,t1.key1 AS key1,t1.key2 AS key2 from t1 where t1.pk1 <> 0 and t1.key1 = 1" - } - ] - } - }, { "join_optimization": { "select_id": 1, + "expanded_query": "select t1.pk1 AS pk1,t1.pk2 AS pk2,t1.key1 AS key1,t1.key2 AS key2 from t1 where t1.pk1 <> 0 and t1.key1 = 1", "steps": [ { "condition_processing": { diff --git a/mysql-test/main/opt_trace_security.result b/mysql-test/main/opt_trace_security.result index e1937e744a4..9223f6c0a28 100644 --- a/mysql-test/main/opt_trace_security.result +++ b/mysql-test/main/opt_trace_security.result @@ -16,7 +16,6 @@ select * from db1.t1; ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1' select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES - 0 1 set optimizer_trace="enabled=off"; grant select(a) on db1.t1 to 'foo'@'%'; set optimizer_trace="enabled=on"; @@ -50,19 +49,10 @@ select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES select * from db1.t1 { "steps": [ - { - "join_preparation": { - "select_id": 1, - "steps": [ - { - "expanded_query": "select db1.t1.a AS a from t1" - } - ] - } - }, { "join_optimization": { "select_id": 1, + "expanded_query": "select db1.t1.a AS a from t1", "steps": [ { "table_dependencies": [ @@ -156,36 +146,18 @@ QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES select * from db1.v1 { "steps": [ { - "join_preparation": { + "join_optimization": { "select_id": 1, + "expanded_query": "/* select#1 */ select db1.t1.a AS a from v1", "steps": [ { "view": { "table": "v1", "select_id": 2, - "algorithm": "merged" - } - }, - { - "join_preparation": { - "select_id": 2, - "steps": [ - { - "expanded_query": "/* select#2 */ select db1.t1.a AS a from t1" - } - ] + "algorithm": "merged", + "expanded_query": "/* select#2 */ select db1.t1.a AS a from t1" } }, - { - "expanded_query": "/* select#1 */ select db1.t1.a AS a from v1" - } - ] - } - }, - { - "join_optimization": { - "select_id": 1, - "steps": [ { "table_dependencies": [ { diff --git a/sql/my_json_writer.h b/sql/my_json_writer.h index d82313f996f..20f479dc6d0 100644 --- a/sql/my_json_writer.h +++ b/sql/my_json_writer.h @@ -224,6 +224,14 @@ class Json_writer size_t get_truncated_bytes() { return output.get_truncated_bytes(); } + /* + Note: this may not return exact value due to pretty-printer doing + buffering + */ + size_t get_written_size() { + return output.length() + output.get_truncated_bytes(); + } + Json_writer() : indent_level(0), document_start(true), element_started(false), first_child(true) diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index d91557c5be2..953f2af0be8 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -549,6 +549,12 @@ bool is_materialization_applicable(THD *thd, Item_in_subselect *in_subs, and, depending on the rewrite, either do it, or record it to be done at a later phase. + NOTE + * This function called at Prepare phase. It should NOT do any rewrites. + It only collects information that's used for doing the rewrites at the + optimization phase. + * Optimizer trace is NOT yet enabled when this function is called. + RETURN 0 - OK Other - Some sort of query error @@ -703,8 +709,6 @@ int check_and_do_in_subquery_rewrites(JOIN *join) { DBUG_PRINT("info", ("Subquery is semi-join conversion candidate")); - (void)subquery_types_allow_materialization(thd, in_subs); - in_subs->is_flattenable_semijoin= TRUE; /* Register the subquery for further processing in flatten_subqueries() */ @@ -717,10 +721,6 @@ int check_and_do_in_subquery_rewrites(JOIN *join) if (arena) thd->restore_active_arena(arena, &backup); in_subs->is_registered_semijoin= TRUE; - OPT_TRACE_TRANSFORM(thd, trace_wrapper, trace_transform, - select_lex->select_number, - "IN (SELECT)", "semijoin"); - trace_transform.add("chosen", true); } } else @@ -1262,6 +1262,7 @@ bool convert_join_subqueries_to_semijoins(JOIN *join) while ((in_subq= li++)) { bool remove_item= TRUE; + (void)subquery_types_allow_materialization(thd, in_subq); /* Stop processing if we've reached a subquery that's attached to the ON clause */ if (in_subq->do_not_convert_to_sj) diff --git a/sql/opt_trace.cc b/sql/opt_trace.cc index ba9220cac44..2227519d991 100644 --- a/sql/opt_trace.cc +++ b/sql/opt_trace.cc @@ -106,6 +106,27 @@ inline bool sql_command_can_be_traced(enum enum_sql_command sql_command) sql_command == SQLCOM_UPDATE_MULTI; } + +void opt_trace_print_expanded_union(THD *thd, SELECT_LEX_UNIT *unit, + Json_writer_object *writer) +{ + DBUG_ASSERT(thd->trace_started()); + + StringBuffer<1024> str(system_charset_info); + ulonglong save_option_bits= thd->variables.option_bits; + thd->variables.option_bits &= ~OPTION_QUOTE_SHOW_CREATE; + unit->print(&str, enum_query_type(QT_TO_SYSTEM_CHARSET | + QT_SHOW_SELECT_NUMBER | + QT_ITEM_IDENT_SKIP_DB_NAMES | + QT_VIEW_INTERNAL)); + thd->variables.option_bits= save_option_bits; + /* + The output is not very pretty lots of back-ticks, the output + is as the one in explain extended , lets try to improved it here. + */ + writer->add("expanded_query", str.c_ptr_safe(), str.length()); +} + void opt_trace_print_expanded_query(THD *thd, SELECT_LEX *select_lex, Json_writer_object *writer) @@ -499,10 +520,49 @@ Opt_trace_start::Opt_trace_start(THD *thd, TABLE_LIST *tbl, } } + +/* + @brief + See "Handing Query Errors" section of comment for Opt_trace_start +*/ + +void Opt_trace_start::trace_heading_done() +{ + Json_writer *w; + if (traceable && (w= ctx->get_current_json())) + trace_heading_size= w->get_written_size(); + else + trace_heading_size= 0; +} + + +/* + @brief + See "Handing Query Errors" section of comment for Opt_trace_start + + @detail + We can't delete the trace right now, because some final writes (e.g. + the top-level closing '}' will still be made to it. Just set clean_me=true + so that it is deleted instead of saving it. +*/ + +void Opt_trace_start::clean_empty_trace() +{ + Json_writer *w; + if (traceable && (w= ctx->get_current_json())) + { + if (w->get_written_size() == trace_heading_size) + clean_me= true; + } +} + + Opt_trace_start::~Opt_trace_start() { if (traceable) { + if (clean_me) + ctx->abort_trace(); ctx->end(); traceable= FALSE; } diff --git a/sql/opt_trace.h b/sql/opt_trace.h index 101fb5f707e..180ecd7bdd3 100644 --- a/sql/opt_trace.h +++ b/sql/opt_trace.h @@ -69,10 +69,33 @@ struct Opt_trace_info @param query query @param length query's length @param charset charset which was used to encode this query + + @detail + == Lifecycle == + The trace is created before the Name Resolution phase. Reasons: + 1. This way, we can have one place where we start the trace for all kinds of + queries. If we tried to start tracing right before query optimization + starts, we would have to construct Opt_trace_start object in many + places: one for SELECT, for UPDATE, for DELETE, etc. + + 2. Privilege checking code may notify the trace (which must exist already) + that the user doesn't have enough permissions to perform tracing. See + missing_privilege() and the opt_trace_disable_if_*** functions below. + + == Handling Query Errors == + The trace is kept when query error occurs, except for the case when + nothing [meaningful] was traced. The second part is necessary for mtr to + produce the same output with and without --ps-protocol. If there is an + error on prepare phase, then: + - In --ps-protocol: PREPARE command produces no trace. The EXECUTE + command is not run. The trace is not generated at all. + - Regular SQL query: should also NOT produce any trace to match the above. + This is handled by trace_heading_done() and clean_empty_trace(). */ -class Opt_trace_start { +class Opt_trace_start +{ public: Opt_trace_start(THD *thd_arg, TABLE_LIST *tbl, enum enum_sql_command sql_command, @@ -82,8 +105,17 @@ class Opt_trace_start { const CHARSET_INFO *query_charset); ~Opt_trace_start(); + void trace_heading_done(); + void clean_empty_trace(); private: Opt_trace_context *const ctx; + + /* Number of bytes written to the trace after the heading was written/ */ + size_t trace_heading_size; + + /* If true, trace should be removed (See Handling Query Errors above) */ + bool clean_me= false; + /* True: the query will be traced False: otherwise @@ -102,6 +134,9 @@ class Opt_trace_start { void opt_trace_print_expanded_query(THD *thd, SELECT_LEX *select_lex, Json_writer_object *trace_object); +void opt_trace_print_expanded_union(THD *thd, SELECT_LEX_UNIT *unit, + Json_writer_object *writer); + void add_table_scan_values_to_trace(THD *thd, JOIN_TAB *tab); void trace_plan_prefix(JOIN *join, uint idx, table_map join_tables); void print_final_join_order(JOIN *join); diff --git a/sql/opt_trace_context.h b/sql/opt_trace_context.h index f578a0c67ec..ae77c94fdc2 100644 --- a/sql/opt_trace_context.h +++ b/sql/opt_trace_context.h @@ -114,6 +114,12 @@ class Opt_trace_context bool is_enabled(); + void abort_trace() + { + delete current_trace; + current_trace= NULL; + } + void missing_privilege(); static const char *flag_names[]; diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index 579ea34b8e4..cf878ad29c0 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -420,6 +420,24 @@ bool mysql_derived_merge(THD *thd, LEX *lex, TABLE_LIST *derived) goto exit_merge; } + if (unlikely(thd->trace_started())) + { + /* + Add to optimizer trace whether a derived table/view + is merged into the parent select or not. + */ + OPT_TRACE_VIEWS_TRANSFORM(thd, trace_wrapper, trace_derived, + derived->is_derived() ? "derived" : "view", + derived->alias.str ? derived->alias.str : "<NULL>", + derived->get_unit()->first_select()->select_number, + derived->is_merged_derived() ? "merged" : "materialized"); + if (derived->is_merged_derived()) + { + opt_trace_print_expanded_union(thd, derived->get_unit(), + &trace_derived); + } + } + /* exclude select lex so it doesn't show up in explain. do this only for derived table as for views this is already done. @@ -822,18 +840,6 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived) } } - if (unlikely(thd->trace_started())) - { - /* - Add to optimizer trace whether a derived table/view - is merged into the parent select or not. - */ - OPT_TRACE_VIEWS_TRANSFORM(thd, trace_wrapper, trace_derived, - derived->is_derived() ? "derived" : "view", - derived->alias.str ? derived->alias.str : "<NULL>", - derived->get_unit()->first_select()->select_number, - derived->is_merged_derived() ? "merged" : "materialized"); - } /* Above cascade call of prepare is important for PS protocol, but after it is called we can check if we really need prepare for this derived diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index b9d3eec5a60..47cf801d74c 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -3648,6 +3648,7 @@ mysql_execute_command(THD *thd, bool is_called_from_prepared_stmt) Json_writer_object trace_command(thd); Json_writer_array trace_command_steps(thd, "steps"); + ots.trace_heading_done(); /* store old value of binlog format */ enum_binlog_format orig_binlog_format,orig_current_stmt_binlog_format; @@ -6150,6 +6151,9 @@ mysql_execute_command(THD *thd, bool is_called_from_prepared_stmt) wsrep_commit_empty(thd, true); } + if (res || thd->is_error()) + ots.clean_empty_trace(); + /* assume PA safety for next transaction */ thd->wsrep_PA_safe= true; #endif /* WITH_WSREP */ diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc index cc6f572ea64..b6bc9eff50e 100644 --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -2435,17 +2435,6 @@ static bool check_prepared_statement(Prepared_statement *stmt) lex->first_select_lex()->context.resolve_in_table_list_only(select_lex-> get_table_list()); - /* - For the optimizer trace, this is the symmetric, for statement preparation, - of what is done at statement execution (in mysql_execute_command()). - */ - Opt_trace_start ots(thd, tables, lex->sql_command, &lex->var_list, - thd->query(), thd->query_length(), - thd->variables.character_set_client); - - Json_writer_object trace_command(thd); - Json_writer_array trace_command_steps(thd, "steps"); - /* Reset warning count for each query that uses tables */ if (tables) thd->get_stmt_da()->opt_clear_warning_info(thd->query_id); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index a357d4f8c8a..45d8f54e264 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1287,11 +1287,6 @@ JOIN::prepare(TABLE_LIST *tables_init, COND *conds_init, uint og_num, join_list= &select_lex->top_join_list; union_part= unit_arg->is_unit_op(); - Json_writer_object trace_wrapper(thd); - Json_writer_object trace_prepare(thd, "join_preparation"); - trace_prepare.add_select_number(select_lex->select_number); - Json_writer_array trace_steps(thd, "steps"); - // simple check that we got usable conds dbug_print_item(conds); @@ -1675,12 +1670,6 @@ JOIN::prepare(TABLE_LIST *tables_init, COND *conds_init, uint og_num, } } - if (thd->trace_started()) - { - Json_writer_object trace_wrapper(thd); - opt_trace_print_expanded_query(thd, select_lex, &trace_wrapper); - } - if (!procedure && result && result->prepare(fields_list, unit_arg)) goto err; /* purecov: inspected */ @@ -1985,7 +1974,11 @@ JOIN::optimize_inner() Json_writer_object trace_wrapper(thd); Json_writer_object trace_prepare(thd, "join_optimization"); - trace_prepare.add_select_number(select_lex->select_number); + if (thd->trace_started()) + { + trace_prepare.add_select_number(select_lex->select_number); + opt_trace_print_expanded_query(thd, select_lex, &trace_prepare); + } Json_writer_array trace_steps(thd, "steps"); /*

1 0

[Commits] 1b21020e493: Code cleanup: don't call subquery_types_allow_materialization() on prepare
by psergey 15 Dec '21

15 Dec '21

revision-id: 1b21020e4938d2e778dbdf5c3f77bd995f38be12 (mariadb-10.6.1-243-g1b21020e493) parent(s): c88e37ff857a83387c4d86829fbaf2e277e4cf9f author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-12-15 23:06:06 +0300 message: Code cleanup: don't call subquery_types_allow_materialization() on prepare For subqueries that are processed as semi-joins. --- mysql-test/main/opt_trace.result | 119 ++++++++++++++++++--------------------- sql/opt_subselect.cc | 3 +- 2 files changed, 56 insertions(+), 66 deletions(-) diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index f7f5476ab23..e1b42a60986 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -2819,15 +2819,6 @@ explain extended select * from t1 where a in (select pk from t10) { "join_preparation": { "select_id": 2, "steps": [ - { - "transformation": { - "select_id": 2, - "from": "IN (SELECT)", - "to": "materialization", - "sjm_scan_allowed": true, - "possible": true - } - }, { "transformation": { "select_id": 2, @@ -2852,6 +2843,15 @@ explain extended select * from t1 where a in (select pk from t10) { "join_optimization": { "select_id": 1, "steps": [ + { + "transformation": { + "select_id": 2, + "from": "IN (SELECT)", + "to": "materialization", + "sjm_scan_allowed": true, + "possible": true + } + }, { "transformation": { "select_id": 2, @@ -4392,15 +4392,6 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ "join_preparation": { "select_id": 2, "steps": [ - { - "transformation": { - "select_id": 2, - "from": "IN (SELECT)", - "to": "materialization", - "sjm_scan_allowed": true, - "possible": true - } - }, { "transformation": { "select_id": 2, @@ -4425,6 +4416,15 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ "join_optimization": { "select_id": 1, "steps": [ + { + "transformation": { + "select_id": 2, + "from": "IN (SELECT)", + "to": "materialization", + "sjm_scan_allowed": true, + "possible": true + } + }, { "transformation": { "select_id": 2, @@ -4842,15 +4842,6 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "join_preparation": { "select_id": 2, "steps": [ - { - "transformation": { - "select_id": 2, - "from": "IN (SELECT)", - "to": "materialization", - "sjm_scan_allowed": true, - "possible": true - } - }, { "transformation": { "select_id": 2, @@ -4869,15 +4860,6 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "join_preparation": { "select_id": 3, "steps": [ - { - "transformation": { - "select_id": 3, - "from": "IN (SELECT)", - "to": "materialization", - "sjm_scan_allowed": true, - "possible": true - } - }, { "transformation": { "select_id": 3, @@ -4902,6 +4884,15 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "join_optimization": { "select_id": 1, "steps": [ + { + "transformation": { + "select_id": 2, + "from": "IN (SELECT)", + "to": "materialization", + "sjm_scan_allowed": true, + "possible": true + } + }, { "transformation": { "select_id": 2, @@ -4910,6 +4901,15 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "converted_to_semi_join": true } }, + { + "transformation": { + "select_id": 3, + "from": "IN (SELECT)", + "to": "materialization", + "sjm_scan_allowed": true, + "possible": true + } + }, { "transformation": { "select_id": 3, @@ -6422,15 +6422,6 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "join_preparation": { "select_id": 2, "steps": [ - { - "transformation": { - "select_id": 2, - "from": "IN (SELECT)", - "to": "materialization", - "sjm_scan_allowed": true, - "possible": true - } - }, { "transformation": { "select_id": 2, @@ -6449,15 +6440,6 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "join_preparation": { "select_id": 3, "steps": [ - { - "transformation": { - "select_id": 3, - "from": "IN (SELECT)", - "to": "materialization", - "sjm_scan_allowed": true, - "possible": true - } - }, { "transformation": { "select_id": 3, @@ -6482,6 +6464,15 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "join_optimization": { "select_id": 1, "steps": [ + { + "transformation": { + "select_id": 2, + "from": "IN (SELECT)", + "to": "materialization", + "sjm_scan_allowed": true, + "possible": true + } + }, { "transformation": { "select_id": 2, @@ -6490,6 +6481,15 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "converted_to_semi_join": true } }, + { + "transformation": { + "select_id": 3, + "from": "IN (SELECT)", + "to": "materialization", + "sjm_scan_allowed": true, + "possible": true + } + }, { "transformation": { "select_id": 3, @@ -8876,17 +8876,6 @@ json_detailed(json_extract(trace, '$**.in_to_subquery_conversion')) } }, - { - "transformation": - { - "select_id": 2, - "from": "IN (SELECT)", - "to": "materialization", - "sjm_scan_allowed": true, - "possible": true - } - }, - { "transformation": { diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index 596b5169659..f2d395a9ee2 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -703,7 +703,7 @@ int check_and_do_in_subquery_rewrites(JOIN *join) { DBUG_PRINT("info", ("Subquery is semi-join conversion candidate")); - (void)subquery_types_allow_materialization(thd, in_subs); + //(void)subquery_types_allow_materialization(thd, in_subs); in_subs->is_flattenable_semijoin= TRUE; @@ -1271,6 +1271,7 @@ bool convert_join_subqueries_to_semijoins(JOIN *join) while ((in_subq= li++)) { bool remove_item= TRUE; + subquery_types_allow_materialization(thd, in_subq); /* Stop processing if we've reached a subquery that's attached to the ON clause */ if (in_subq->do_not_convert_to_sj)

1 0

[Commits] 3c9b7393563: MDEV-27270: Wrong query plan with Range Checked for Each Record and ORDER BY ... LIMIT
by psergey 15 Dec '21

15 Dec '21

revision-id: 3c9b73935632aaacc941b1600c5046d57dec0ff6 (mariadb-10.2.40-139-g3c9b7393563) parent(s): 026984c360ce27c62072ed6ce798ec855952c974 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-12-15 15:12:06 +0300 message: MDEV-27270: Wrong query plan with Range Checked for Each Record and ORDER BY ... LIMIT Followup to fix for MDEV-25858: When test_if_skip_sort_order() decides to use an index to satisfy ORDER BY ... LIMIT clause, it should disable "Range Checked for Each Record" optimization. Do this in all cases. --- mysql-test/r/order_by_innodb.result | 23 +++++++++++++++++++++++ mysql-test/t/order_by_innodb.test | 22 ++++++++++++++++++++++ sql/sql_select.cc | 8 ++++++++ 3 files changed, 53 insertions(+) diff --git a/mysql-test/r/order_by_innodb.result b/mysql-test/r/order_by_innodb.result index 14b9b861a14..28922ef65f2 100644 --- a/mysql-test/r/order_by_innodb.result +++ b/mysql-test/r/order_by_innodb.result @@ -198,5 +198,28 @@ id id 1 NULL 2 1 3 3 +# +# MDEV-27270: Wrong query plan with Range Checked for Each Record and ORDER BY ... LIMIT +# +# This must NOT have "Range checked for each record" without any +# provisions to produce rows in the required ordering: +explain +select +t1.id,t2.id +from +t1 left join +t2 on t2.id2 = t1.id and +t2.id = (select dd.id +from t2 dd +where +dd.id2 = t1.id and +d1 > '2019-02-06 00:00:00' + order by +dd.d1, dd.d2, dd.id limit 1 +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index NULL PRIMARY 4 NULL # Using index +1 PRIMARY t2 eq_ref PRIMARY,id2 PRIMARY 4 func # Using where +2 DEPENDENT SUBQUERY dd range id2,for_latest_sort for_latest_sort 6 NULL # Using where drop table t1,t2; # End of 10.2 tests diff --git a/mysql-test/t/order_by_innodb.test b/mysql-test/t/order_by_innodb.test index 97c043b8dbc..af12644c073 100644 --- a/mysql-test/t/order_by_innodb.test +++ b/mysql-test/t/order_by_innodb.test @@ -184,6 +184,28 @@ from order by dd.d1 desc, dd.d2 desc, dd.id desc limit 1 ); + +--echo # +--echo # MDEV-27270: Wrong query plan with Range Checked for Each Record and ORDER BY ... LIMIT +--echo # + +--echo # This must NOT have "Range checked for each record" without any +--echo # provisions to produce rows in the required ordering: +--replace_column 9 # +explain +select + t1.id,t2.id +from + t1 left join + t2 on t2.id2 = t1.id and + t2.id = (select dd.id + from t2 dd + where + dd.id2 = t1.id and + d1 > '2019-02-06 00:00:00' + order by + dd.d1, dd.d2, dd.id limit 1 + ); drop table t1,t2; --echo # End of 10.2 tests diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 54a2facfe9f..1ab0c295e73 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -21980,7 +21980,15 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, } } else if (select && select->quick) + { + /* Cancel "Range checked for each record" */ + if (tab->use_quick == 2) + { + tab->use_quick= 1; + tab->read_first_record= join_init_read_record; + } select->quick->need_sorted_output(); + } tab->read_record.unlock_row= (tab->type == JT_EQ_REF) ? join_read_key_unlock_row : rr_unlock_row;

1 0

[Commits] de442635810: MDEV-26996: Support descending indexes in the range optimizer
by psergey 14 Dec '21

14 Dec '21

revision-id: de4426358101575648e968b4cdae35527da74b23 (mariadb-10.6.1-249-gde442635810) parent(s): c95270df0bc0ba4c93eeb17f078ac85dfd5f5c1b author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-12-14 16:39:37 +0300 message: MDEV-26996: Support descending indexes in the range optimizer - Code cleanup - Disable "Using index for GROUP BY" over indexes with DESC keyparts --- mysql-test/main/desc_index_range.result | 25 ++++++++++- mysql-test/main/desc_index_range.test | 13 +++++- mysql-test/main/opt_trace.result | 52 +++++++++++----------- mysql-test/main/opt_trace_index_merge.result | 8 ++-- .../main/opt_trace_index_merge_innodb.result | 8 ++-- sql/opt_range.cc | 23 +++++----- 6 files changed, 82 insertions(+), 47 deletions(-) diff --git a/mysql-test/main/desc_index_range.result b/mysql-test/main/desc_index_range.result index 53a608fe2d9..feec5dc1720 100644 --- a/mysql-test/main/desc_index_range.result +++ b/mysql-test/main/desc_index_range.result @@ -154,5 +154,28 @@ json_detailed(json_extract(trace, '$**.range_access_plan.ranges')) "(4,80) <= (a,b) <= (2,50)" ] ] -set optimizer_trace=default; drop table t2; +# +# Check that "Using index for group-by" is disabled (it's not supported, yet) +# +CREATE TABLE t1 (p int NOT NULL, a int NOT NULL, PRIMARY KEY (p,a desc)); +insert into t1 select 2,seq from seq_0_to_1000; +EXPLAIN select MIN(a) from t1 where p = 2 group by p; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref PRIMARY PRIMARY 4 const 1000 Using index +select json_detailed(json_extract(trace, '$**.potential_group_range_indexes')) +from information_schema.optimizer_trace; +json_detailed(json_extract(trace, '$**.potential_group_range_indexes')) +[ + + [ + + { + "index": "PRIMARY", + "usable": false, + "cause": "Reverse-ordered (not supported yet)" + } + ] +] +drop table t1; +set optimizer_trace=default; diff --git a/mysql-test/main/desc_index_range.test b/mysql-test/main/desc_index_range.test index 94d6b76258d..bcb9ce83318 100644 --- a/mysql-test/main/desc_index_range.test +++ b/mysql-test/main/desc_index_range.test @@ -73,5 +73,16 @@ select * from t2 where a between 2 and 4 and b between 50 and 80; select json_detailed(json_extract(trace, '$**.range_access_plan.ranges')) from information_schema.optimizer_trace; -set optimizer_trace=default; drop table t2; + +--echo # +--echo # Check that "Using index for group-by" is disabled (it's not supported, yet) +--echo # +CREATE TABLE t1 (p int NOT NULL, a int NOT NULL, PRIMARY KEY (p,a desc)); +insert into t1 select 2,seq from seq_0_to_1000; +EXPLAIN select MIN(a) from t1 where p = 2 group by p; +select json_detailed(json_extract(trace, '$**.potential_group_range_indexes')) +from information_schema.optimizer_trace; +drop table t1; + +set optimizer_trace=default; diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index 4913aac6c30..f7f5476ab23 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -1203,8 +1203,8 @@ EXPLAIN SELECT DISTINCT a FROM t1 { }, { "index": "a", - "key_parts": ["a"], - "usable": true + "usable": true, + "key_parts": ["a"] } ], "best_covering_index_scan": { @@ -1386,8 +1386,8 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { "potential_range_indexes": [ { "index": "a", - "key_parts": ["a", "b", "c", "d"], - "usable": true + "usable": true, + "key_parts": ["a", "b", "c", "d"] } ], "best_covering_index_scan": { @@ -1585,8 +1585,8 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id { "potential_range_indexes": [ { "index": "id", - "key_parts": ["id", "a"], - "usable": true + "usable": true, + "key_parts": ["id", "a"] } ], "best_covering_index_scan": { @@ -1773,8 +1773,8 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id { "potential_range_indexes": [ { "index": "id", - "key_parts": ["id", "a"], - "usable": true + "usable": true, + "key_parts": ["id", "a"] } ], "best_covering_index_scan": { @@ -2012,13 +2012,13 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { }, { "index": "a_c", - "key_parts": ["a", "c"], - "usable": true + "usable": true, + "key_parts": ["a", "c"] }, { "index": "a_b", - "key_parts": ["a", "b"], - "usable": true + "usable": true, + "key_parts": ["a", "b"] } ], "setup_range_conditions": [], @@ -2215,8 +2215,8 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { }, { "index": "a_c", - "key_parts": ["a", "c"], - "usable": true + "usable": true, + "key_parts": ["a", "c"] }, { "index": "a_b", @@ -3231,18 +3231,18 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "potential_range_indexes": [ { "index": "pk", - "key_parts": ["pk"], - "usable": true + "usable": true, + "key_parts": ["pk"] }, { "index": "pk_a", - "key_parts": ["pk", "a"], - "usable": true + "usable": true, + "key_parts": ["pk", "a"] }, { "index": "pk_a_b", - "key_parts": ["pk", "a", "b"], - "usable": true + "usable": true, + "key_parts": ["pk", "a", "b"] } ], "best_covering_index_scan": { @@ -3749,8 +3749,8 @@ explain delete from t0 where t0.a<3 { "potential_range_indexes": [ { "index": "a", - "key_parts": ["a"], - "usable": true + "usable": true, + "key_parts": ["a"] } ], "setup_range_conditions": [], @@ -3887,8 +3887,8 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "potential_range_indexes": [ { "index": "a", - "key_parts": ["a"], - "usable": true + "usable": true, + "key_parts": ["a"] } ], "best_covering_index_scan": { @@ -3952,8 +3952,8 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "potential_range_indexes": [ { "index": "a", - "key_parts": ["a"], - "usable": true + "usable": true, + "key_parts": ["a"] } ], "best_covering_index_scan": { diff --git a/mysql-test/main/opt_trace_index_merge.result b/mysql-test/main/opt_trace_index_merge.result index 011875762d1..f1e13586eda 100644 --- a/mysql-test/main/opt_trace_index_merge.result +++ b/mysql-test/main/opt_trace_index_merge.result @@ -78,13 +78,13 @@ explain select * from t1 where a=1 or b=1 { "potential_range_indexes": [ { "index": "a", - "key_parts": ["a"], - "usable": true + "usable": true, + "key_parts": ["a"] }, { "index": "b", - "key_parts": ["b"], - "usable": true + "usable": true, + "key_parts": ["b"] }, { "index": "c", diff --git a/mysql-test/main/opt_trace_index_merge_innodb.result b/mysql-test/main/opt_trace_index_merge_innodb.result index d372be85bd8..0ddaaeae89d 100644 --- a/mysql-test/main/opt_trace_index_merge_innodb.result +++ b/mysql-test/main/opt_trace_index_merge_innodb.result @@ -93,13 +93,13 @@ explain select * from t1 where pk1 != 0 and key1 = 1 { "potential_range_indexes": [ { "index": "PRIMARY", - "key_parts": ["pk1", "pk2"], - "usable": true + "usable": true, + "key_parts": ["pk1", "pk2"] }, { "index": "key1", - "key_parts": ["key1"], - "usable": true + "usable": true, + "key_parts": ["key1"] }, { "index": "key2", diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 541a921435a..ae2b5060625 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -2809,12 +2809,11 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, trace_idx_details.add("usable", false).add("cause", "fulltext"); continue; // ToDo: ft-keys in non-ft ranges, if possible SerG } - + trace_idx_details.add("usable", true); param.key[param.keys]=key_parts; key_part_info= key_info->key_part; uint cur_key_len= 0; Json_writer_array trace_keypart(thd, "key_parts"); - bool unusable_has_desc_keyparts= false; for (uint part= 0 ; part < n_key_parts ; part++, key_parts++, key_part_info++) { @@ -2829,18 +2828,9 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, (key_info->flags & HA_SPATIAL) ? Field::itMBR : Field::itRAW; /* Only HA_PART_KEY_SEG is used */ key_parts->flag= (uint8) key_part_info->key_part_flag; - if (key_part_info->key_part_flag & HA_REVERSE_SORT) - unusable_has_desc_keyparts= true; trace_keypart.add(key_parts->field->field_name); } trace_keypart.end(); - trace_idx_details.add("usable", !unusable_has_desc_keyparts); - unusable_has_desc_keyparts= false; - if (unusable_has_desc_keyparts) // TODO MDEV-13756 - { - key_parts= param.key[param.keys]; - continue; - } param.real_keynr[param.keys++]=idx; if (cur_key_len > max_key_len) max_key_len= cur_key_len; @@ -13833,6 +13823,17 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) cause= "not covering"; goto next_index; } + + { + for (uint i= 0; i < table->actual_n_key_parts(cur_index_info); i++) + { + if (cur_index_info->key_part[i].key_part_flag & HA_REVERSE_SORT) + { + cause="Reverse-ordered (not supported yet)"; + goto next_index; + } + } + } /* This function is called on the precondition that the index is covering.

1 0

[Commits] 870143250c5: Post-rebase fix
by psergey 14 Dec '21

14 Dec '21

revision-id: 870143250c54fcff949f1d8985b9fe1d2c47e26a (mariadb-10.6.1-250-g870143250c5) parent(s): 7f69a3c91462d6625c5f32261b0c059471156e6a author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-12-14 16:06:37 +0300 message: Post-rebase fix --- sql/key.cc | 1 - 1 file changed, 1 deletion(-) diff --git a/sql/key.cc b/sql/key.cc index 467a4f75044..4509634da2d 100644 --- a/sql/key.cc +++ b/sql/key.cc @@ -578,7 +578,6 @@ int key_rec_cmp(void *key_p, uchar *first_rec, uchar *second_rec) const int LESS= -GREATER; field= key_part->field; - int sort_order = (key_part->key_part_flag & HA_REVERSE_SORT) ? -1 : 1; if (key_part->null_bit) {

1 0

[Commits] 7f69a3c9146: Descending indexes code exposed a gap in fix for MDEV-25858.
by psergey 14 Dec '21

14 Dec '21

revision-id: 7f69a3c91462d6625c5f32261b0c059471156e6a (mariadb-10.6.1-249-g7f69a3c9146) parent(s): 9f5aa2c1d3435d66ad922c04a961905bad1ab2b7 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-12-14 16:02:44 +0300 message: Descending indexes code exposed a gap in fix for MDEV-25858. Extend the fix for MDEV-25858 to handle non-reverse-ordered ORDER BY: If test_if_skip_sort_order() decides to use an index to produce rows in the required ordering, it should disable "Range Checked for Each Record". The fix needs to be backported to earlier versions. --- mysql-test/main/order_by_innodb.result | 34 ++++++++++++++++++-- mysql-test/main/order_by_innodb.test | 37 ++++++++++++++++++++-- .../suite/engines/funcs/r/ix_using_order.result | 4 +-- sql/sql_select.cc | 8 +++++ 4 files changed, 77 insertions(+), 6 deletions(-) diff --git a/mysql-test/main/order_by_innodb.result b/mysql-test/main/order_by_innodb.result index 7083f04f7c4..77c40a8c0de 100644 --- a/mysql-test/main/order_by_innodb.result +++ b/mysql-test/main/order_by_innodb.result @@ -180,7 +180,7 @@ id id 1 NULL 2 1 3 3 -create index for_latest_sort on t2 (d1 desc, d2 desc, id desc); +create index for_latest_sort on t2 (d1, d2, id); select t1.id,t2.id from @@ -198,7 +198,37 @@ id id 1 NULL 2 1 3 3 -drop table t1,t2; +# Now, same as above but use a DESC index +CREATE TABLE t3 ( +id int NOT NULL PRIMARY KEY, +id2 int NOT NULL, +d1 datetime, +d2 timestamp NOT NULL, +KEY id2 (id2) +) engine=innodb; +insert into t3 values +(1,2,'2019-03-05 00:00:00','2019-03-06 00:00:00'), +(2,3,'2019-03-05 00:00:00','2019-03-06 00:00:00'), +(3,3,'2019-03-06 00:00:00','2019-03-05 00:00:00'); +create index for_latest_sort on t3 (d1 desc, d2 desc, id desc); +select +t1.id,t3.id +from +t1 left join +t3 on t3.id2 = t1.id and +t3.id = (select dd.id +from t3 dd +where +dd.id2 = t1.id and +d1 > '2019-02-06 00:00:00' + order by +dd.d1 desc, dd.d2 desc, dd.id desc limit 1 +); +id id +1 NULL +2 1 +3 3 +drop table t1,t2,t3; # End of 10.2 tests # # MDEV-26938 Support descending indexes internally in InnoDB diff --git a/mysql-test/main/order_by_innodb.test b/mysql-test/main/order_by_innodb.test index db801ef79f3..7fb036aaf5d 100644 --- a/mysql-test/main/order_by_innodb.test +++ b/mysql-test/main/order_by_innodb.test @@ -170,7 +170,7 @@ from dd.d1 desc, dd.d2 desc, dd.id desc limit 1 ); -create index for_latest_sort on t2 (d1 desc, d2 desc, id desc); +create index for_latest_sort on t2 (d1, d2, id); select t1.id,t2.id @@ -185,7 +185,40 @@ from order by dd.d1 desc, dd.d2 desc, dd.id desc limit 1 ); -drop table t1,t2; + +--echo # Now, same as above but use a DESC index + +CREATE TABLE t3 ( + id int NOT NULL PRIMARY KEY, + id2 int NOT NULL, + d1 datetime, + d2 timestamp NOT NULL, + KEY id2 (id2) +) engine=innodb; + +insert into t3 values + (1,2,'2019-03-05 00:00:00','2019-03-06 00:00:00'), + (2,3,'2019-03-05 00:00:00','2019-03-06 00:00:00'), + (3,3,'2019-03-06 00:00:00','2019-03-05 00:00:00'); +create index for_latest_sort on t3 (d1 desc, d2 desc, id desc); + + +select + t1.id,t3.id +from + t1 left join + t3 on t3.id2 = t1.id and + t3.id = (select dd.id + from t3 dd + where + dd.id2 = t1.id and + d1 > '2019-02-06 00:00:00' + order by + dd.d1 desc, dd.d2 desc, dd.id desc limit 1 + ); + + +drop table t1,t2,t3; --echo # End of 10.2 tests diff --git a/mysql-test/suite/engines/funcs/r/ix_using_order.result b/mysql-test/suite/engines/funcs/r/ix_using_order.result index 645b3fcfbc4..1e4389ce251 100644 --- a/mysql-test/suite/engines/funcs/r/ix_using_order.result +++ b/mysql-test/suite/engines/funcs/r/ix_using_order.result @@ -17,7 +17,7 @@ SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(11) NOT NULL, - KEY `i1` (`c1`) USING BTREE + KEY `i1` (`c1` DESC) USING BTREE ) ENGINE=ENGINE DEFAULT CHARSET=latin1 DROP TABLE t1; SHOW TABLES; @@ -40,7 +40,7 @@ SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(11) NOT NULL, - UNIQUE KEY `i1` (`c1`) USING BTREE + UNIQUE KEY `i1` (`c1` DESC) USING BTREE ) ENGINE=ENGINE DEFAULT CHARSET=latin1 DROP INDEX i1 ON t1; DROP TABLE t1; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index b08f2dd2545..ed224cce5e6 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -24265,7 +24265,15 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, } } else if (select && select->quick) + { + /* Cancel "Range checked for each record" */ + if (tab->use_quick == 2) + { + tab->use_quick= 1; + tab->read_first_record= join_init_read_record; + } select->quick->need_sorted_output(); + } if (tab->type == JT_EQ_REF) tab->read_record.unlock_row= join_read_key_unlock_row;

1 0

[Commits] 9f5aa2c1d34: Don't run main.desc_index_range under embedded. It uses optimizer trace
by psergey 14 Dec '21

14 Dec '21

revision-id: 9f5aa2c1d3435d66ad922c04a961905bad1ab2b7 (mariadb-10.6.1-248-g9f5aa2c1d34) parent(s): 809bda915484fb495fb31e30cdaf271e154ddd48 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-12-14 16:02:34 +0300 message: Don't run main.desc_index_range under embedded. It uses optimizer trace --- mysql-test/main/desc_index_range.test | 3 +++ 1 file changed, 3 insertions(+) diff --git a/mysql-test/main/desc_index_range.test b/mysql-test/main/desc_index_range.test index 7fdf439c523..94d6b76258d 100644 --- a/mysql-test/main/desc_index_range.test +++ b/mysql-test/main/desc_index_range.test @@ -4,6 +4,9 @@ --source include/have_sequence.inc --source include/have_innodb.inc +# The test uses optimizer trace: +--source include/not_embedded.inc + create table t1 ( a int, key (a desc)

1 0

[Commits] 809bda91548: MDEV-26996: Support descending indexes in the range optimizer
by psergey 14 Dec '21

14 Dec '21

revision-id: 809bda915484fb495fb31e30cdaf271e154ddd48 (mariadb-10.6.1-247-g809bda91548) parent(s): b5ec3e30b59a75e68192be8fb4550237bd146a2f author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-12-14 16:02:17 +0300 message: MDEV-26996: Support descending indexes in the range optimizer Make the Range Optimizer support descending index key parts. We follow the approach taken in MySQL-8. See HowRangeOptimizerHandlesDescKeyparts for the description. --- mysql-test/main/desc_index_range.result | 158 ++++++++++++++++++++++ mysql-test/main/desc_index_range.test | 74 +++++++++++ sql/item_geofunc.cc | 3 +- sql/key.cc | 10 +- sql/opt_range.cc | 162 +++++++++++++++------- sql/opt_range.h | 229 ++++++++++++++++++++++++++++---- sql/opt_range_mrr.cc | 46 +++---- 7 files changed, 580 insertions(+), 102 deletions(-) diff --git a/mysql-test/main/desc_index_range.result b/mysql-test/main/desc_index_range.result new file mode 100644 index 00000000000..53a608fe2d9 --- /dev/null +++ b/mysql-test/main/desc_index_range.result @@ -0,0 +1,158 @@ +create table t1 ( +a int, +key (a desc) +); +insert into t1 select seq from seq_1_to_1000; +set optimizer_trace=1; +explain select * from t1 force index(a) where a in (2, 4, 6); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 5 NULL 3 Using where; Using index +select json_detailed(json_extract(trace, '$**.range_access_plan.ranges')) +from information_schema.optimizer_trace; +json_detailed(json_extract(trace, '$**.range_access_plan.ranges')) +[ + + [ + "(6) <= (a) <= (6)", + "(4) <= (a) <= (4)", + "(2) <= (a) <= (2)" + ] +] +set optimizer_trace=default; +# These should go in reverse order: +select * from t1 force index(a) where a in (2, 4, 6); +a +6 +4 +2 +drop table t1; +# +# Multi-part key tests +# +create table t1 ( +a int not null, +b int not null, +key ab(a, b desc) +); +insert into t1 select A.seq, B.seq*10 from seq_1_to_10 A, seq_1_to_10 B; +set optimizer_trace=1; +explain select * from t1 force index(ab) where a>=8 and b>=50; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range ab ab 4 NULL 51 Using where; Using index +select json_detailed(json_extract(trace, '$**.range_access_plan.ranges')) +from information_schema.optimizer_trace; +json_detailed(json_extract(trace, '$**.range_access_plan.ranges')) +[ + + [ + "(8) <= (a)" + ] +] +explain select * from t1 force index(ab) where a>=8 and b<=50; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range ab ab 8 NULL 46 Using where; Using index +select json_detailed(json_extract(trace, '$**.range_access_plan.ranges')) +from information_schema.optimizer_trace; +json_detailed(json_extract(trace, '$**.range_access_plan.ranges')) +[ + + [ + "(8,50) <= (a,b)" + ] +] +select * from t1 force index(ab) where a>=8 and b<=50; +a b +8 50 +8 40 +8 30 +8 20 +8 10 +9 50 +9 40 +9 30 +9 20 +9 10 +10 50 +10 40 +10 30 +10 20 +10 10 +select * from t1 ignore index(ab) where a>=8 and b<=50 order by a, b desc; +a b +8 50 +8 40 +8 30 +8 20 +8 10 +9 50 +9 40 +9 30 +9 20 +9 10 +10 50 +10 40 +10 30 +10 20 +10 10 +explain +select * from t1 where a between 2 and 4 and b between 50 and 80; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range ab ab 8 NULL 17 Using where; Using index +select json_detailed(json_extract(trace, '$**.range_access_plan.ranges')) +from information_schema.optimizer_trace; +json_detailed(json_extract(trace, '$**.range_access_plan.ranges')) +[ + + [ + "(2,80) <= (a,b) <= (4,50)" + ] +] +select * from t1 where a between 2 and 4 and b between 50 and 80; +a b +2 80 +2 70 +2 60 +2 50 +3 80 +3 70 +3 60 +3 50 +4 80 +4 70 +4 60 +4 50 +drop table t1; +create table t2 ( +a int not null, +b int not null, +key ab(a desc, b desc) +); +insert into t2 select A.seq, B.seq*10 from seq_1_to_10 A, seq_1_to_10 B; +explain +select * from t2 where a between 2 and 4; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range ab ab 4 NULL 40 Using where; Using index +select json_detailed(json_extract(trace, '$**.range_access_plan.ranges')) +from information_schema.optimizer_trace; +json_detailed(json_extract(trace, '$**.range_access_plan.ranges')) +[ + + [ + "(4) <= (a) <= (2)" + ] +] +explain +select * from t2 where a between 2 and 4 and b between 50 and 80; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range ab ab 8 NULL 31 Using where; Using index +select json_detailed(json_extract(trace, '$**.range_access_plan.ranges')) +from information_schema.optimizer_trace; +json_detailed(json_extract(trace, '$**.range_access_plan.ranges')) +[ + + [ + "(4,80) <= (a,b) <= (2,50)" + ] +] +set optimizer_trace=default; +drop table t2; diff --git a/mysql-test/main/desc_index_range.test b/mysql-test/main/desc_index_range.test new file mode 100644 index 00000000000..7fdf439c523 --- /dev/null +++ b/mysql-test/main/desc_index_range.test @@ -0,0 +1,74 @@ +# +# Tests for range access and descending indexes +# +--source include/have_sequence.inc +--source include/have_innodb.inc + +create table t1 ( + a int, + key (a desc) +); +insert into t1 select seq from seq_1_to_1000; + +set optimizer_trace=1; +explain select * from t1 force index(a) where a in (2, 4, 6); + +select json_detailed(json_extract(trace, '$**.range_access_plan.ranges')) +from information_schema.optimizer_trace; +set optimizer_trace=default; + +--echo # These should go in reverse order: +select * from t1 force index(a) where a in (2, 4, 6); +drop table t1; + +--echo # +--echo # Multi-part key tests +--echo # +create table t1 ( + a int not null, + b int not null, + key ab(a, b desc) +); + +insert into t1 select A.seq, B.seq*10 from seq_1_to_10 A, seq_1_to_10 B; + +set optimizer_trace=1; +explain select * from t1 force index(ab) where a>=8 and b>=50; +select json_detailed(json_extract(trace, '$**.range_access_plan.ranges')) +from information_schema.optimizer_trace; + +explain select * from t1 force index(ab) where a>=8 and b<=50; +select json_detailed(json_extract(trace, '$**.range_access_plan.ranges')) +from information_schema.optimizer_trace; + +select * from t1 force index(ab) where a>=8 and b<=50; +select * from t1 ignore index(ab) where a>=8 and b<=50 order by a, b desc; + +explain +select * from t1 where a between 2 and 4 and b between 50 and 80; +select json_detailed(json_extract(trace, '$**.range_access_plan.ranges')) +from information_schema.optimizer_trace; + +select * from t1 where a between 2 and 4 and b between 50 and 80; + +drop table t1; + +create table t2 ( + a int not null, + b int not null, + key ab(a desc, b desc) +); +insert into t2 select A.seq, B.seq*10 from seq_1_to_10 A, seq_1_to_10 B; + +explain +select * from t2 where a between 2 and 4; +select json_detailed(json_extract(trace, '$**.range_access_plan.ranges')) +from information_schema.optimizer_trace; + +explain +select * from t2 where a between 2 and 4 and b between 50 and 80; +select json_detailed(json_extract(trace, '$**.range_access_plan.ranges')) +from information_schema.optimizer_trace; + +set optimizer_trace=default; +drop table t2; diff --git a/sql/item_geofunc.cc b/sql/item_geofunc.cc index 49b85e2213b..a2a99bcdf8f 100644 --- a/sql/item_geofunc.cc +++ b/sql/item_geofunc.cc @@ -1083,7 +1083,8 @@ Item_func_spatial_rel::get_mm_leaf(RANGE_OPT_PARAM *param, DBUG_RETURN(0); // out of memory field->get_key_image(str, key_part->length, key_part->image_type); SEL_ARG *tree; - if (!(tree= new (param->mem_root) SEL_ARG(field, str, str))) + + if (!(tree= new (param->mem_root) SEL_ARG(field, true, str, str))) DBUG_RETURN(0); // out of memory switch (type) { diff --git a/sql/key.cc b/sql/key.cc index ef1af849391..467a4f75044 100644 --- a/sql/key.cc +++ b/sql/key.cc @@ -495,6 +495,7 @@ int key_cmp(KEY_PART_INFO *key_part, const uchar *key, uint key_length) { int cmp; store_length= key_part->store_length; + int sort_order = (key_part->key_part_flag & HA_REVERSE_SORT) ? -1 : 1; if (key_part->null_bit) { /* This key part allows null values; NULL is lower than everything */ @@ -503,19 +504,19 @@ int key_cmp(KEY_PART_INFO *key_part, const uchar *key, uint key_length) { /* the range is expecting a null value */ if (!field_is_null) - return 1; // Found key is > range + return sort_order; // Found key is > range /* null -- exact match, go to next key part */ continue; } else if (field_is_null) - return -1; // NULL is less than any value + return -sort_order; // NULL is less than any value key++; // Skip null byte store_length--; } if ((cmp=key_part->field->key_cmp(key, key_part->length)) < 0) - return -1; + return -sort_order; if (cmp > 0) - return 1; + return sort_order; } return 0; // Keys are equal } @@ -577,6 +578,7 @@ int key_rec_cmp(void *key_p, uchar *first_rec, uchar *second_rec) const int LESS= -GREATER; field= key_part->field; + int sort_order = (key_part->key_part_flag & HA_REVERSE_SORT) ? -1 : 1; if (key_part->null_bit) { diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 86539046a32..541a921435a 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -1879,6 +1879,7 @@ SEL_ARG::SEL_ARG(SEL_ARG &arg) :Sql_alloc() max_flag=arg.max_flag; maybe_flag=arg.maybe_flag; maybe_null=arg.maybe_null; + is_ascending= arg.is_ascending; part=arg.part; field=arg.field; min_value=arg.min_value; @@ -1904,9 +1905,10 @@ inline void SEL_ARG::make_root() use_count=0; elements=1; } -SEL_ARG::SEL_ARG(Field *f,const uchar *min_value_arg, +SEL_ARG::SEL_ARG(Field *f, bool is_asc, const uchar *min_value_arg, const uchar *max_value_arg) :min_flag(0), max_flag(0), maybe_flag(0), maybe_null(f->real_maybe_null()), + is_ascending(is_asc), elements(1), use_count(1), field(f), min_value((uchar*) min_value_arg), max_value((uchar*) max_value_arg), next(0),prev(0), next_key_part(0), color(BLACK), type(KEY_RANGE), weight(1) @@ -1915,11 +1917,12 @@ SEL_ARG::SEL_ARG(Field *f,const uchar *min_value_arg, max_part_no= 1; } -SEL_ARG::SEL_ARG(Field *field_,uint8 part_, +SEL_ARG::SEL_ARG(Field *field_,uint8 part_, bool is_asc_, uchar *min_value_, uchar *max_value_, uint8 min_flag_,uint8 max_flag_,uint8 maybe_flag_) :min_flag(min_flag_),max_flag(max_flag_),maybe_flag(maybe_flag_), - part(part_),maybe_null(field_->real_maybe_null()), elements(1),use_count(1), + part(part_),maybe_null(field_->real_maybe_null()), is_ascending(is_asc_), + elements(1),use_count(1), field(field_), min_value(min_value_), max_value(max_value_), next(0),prev(0),next_key_part(0),color(BLACK),type(KEY_RANGE), weight(1) { @@ -1938,8 +1941,8 @@ SEL_ARG::SEL_ARG(Field *field_,uint8 part_, class SEL_ARG_LE: public SEL_ARG { public: - SEL_ARG_LE(const uchar *key, Field *field) - :SEL_ARG(field, key, key) + SEL_ARG_LE(const uchar *key, Field *field, bool is_asc) + :SEL_ARG(field, is_asc, key, key) { if (!field->real_maybe_null()) min_flag= NO_MIN_RANGE; // From start @@ -1959,16 +1962,17 @@ class SEL_ARG_LT: public SEL_ARG_LE Use this constructor if value->save_in_field() went precisely, without any data rounding or truncation. */ - SEL_ARG_LT(const uchar *key, Field *field) - :SEL_ARG_LE(key, field) + SEL_ARG_LT(const uchar *key, Field *field, bool is_asc) + :SEL_ARG_LE(key, field, is_asc) { max_flag= NEAR_MAX; } /* Use this constructor if value->save_in_field() returned success, but we don't know if rounding or truncation happened (as some Field::store() do not report minor data changes). */ - SEL_ARG_LT(THD *thd, const uchar *key, Field *field, Item *value) - :SEL_ARG_LE(key, field) + SEL_ARG_LT(THD *thd, const uchar *key, Field *field, bool is_asc, + Item *value) + :SEL_ARG_LE(key, field, is_asc) { if (stored_field_cmp_to_item(thd, field, value) == 0) max_flag= NEAR_MAX; @@ -1984,7 +1988,7 @@ class SEL_ARG_GT: public SEL_ARG without any data rounding or truncation. */ SEL_ARG_GT(const uchar *key, const KEY_PART *key_part, Field *field) - :SEL_ARG(field, key, key) + :SEL_ARG(field, !(key_part->flag & HA_REVERSE_SORT), key, key) { // Don't use open ranges for partial key_segments if (!(key_part->flag & HA_PART_KEY_SEG)) @@ -1998,7 +2002,7 @@ class SEL_ARG_GT: public SEL_ARG */ SEL_ARG_GT(THD *thd, const uchar *key, const KEY_PART *key_part, Field *field, Item *value) - :SEL_ARG(field, key, key) + :SEL_ARG(field, !(key_part->flag & HA_REVERSE_SORT), key, key) { // Don't use open ranges for partial key_segments if ((!(key_part->flag & HA_PART_KEY_SEG)) && @@ -2016,8 +2020,8 @@ class SEL_ARG_GE: public SEL_ARG Use this constructor if value->save_in_field() went precisely, without any data rounding or truncation. */ - SEL_ARG_GE(const uchar *key, Field *field) - :SEL_ARG(field, key, key) + SEL_ARG_GE(const uchar *key, Field *field, bool is_asc) + :SEL_ARG(field, is_asc, key, key) { max_flag= NO_MAX_RANGE; } @@ -2028,7 +2032,7 @@ class SEL_ARG_GE: public SEL_ARG */ SEL_ARG_GE(THD *thd, const uchar *key, const KEY_PART *key_part, Field *field, Item *value) - :SEL_ARG(field, key, key) + :SEL_ARG(field, !(key_part->flag & HA_REVERSE_SORT), key, key) { // Don't use open ranges for partial key_segments if ((!(key_part->flag & HA_PART_KEY_SEG)) && @@ -2059,7 +2063,8 @@ SEL_ARG *SEL_ARG::clone(RANGE_OPT_PARAM *param, SEL_ARG *new_parent, } else { - if (!(tmp= new (param->mem_root) SEL_ARG(field,part, min_value,max_value, + if (!(tmp= new (param->mem_root) SEL_ARG(field, part, is_ascending, + min_value, max_value, min_flag, max_flag, maybe_flag))) return 0; // OOM tmp->parent=new_parent; @@ -2830,6 +2835,7 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, } trace_keypart.end(); trace_idx_details.add("usable", !unusable_has_desc_keyparts); + unusable_has_desc_keyparts= false; if (unusable_has_desc_keyparts) // TODO MDEV-13756 { key_parts= param.key[param.keys]; @@ -4420,12 +4426,14 @@ int find_used_partitions(PART_PRUNE_PARAM *ppar, SEL_ARG *key_tree) key_tree->next_key_part->store_min_key(ppar->key, &tmp_min_key, &tmp_min_flag, - ppar->last_part_partno); + ppar->last_part_partno, + true); if (!tmp_max_flag) key_tree->next_key_part->store_max_key(ppar->key, &tmp_max_key, &tmp_max_flag, - ppar->last_part_partno); + ppar->last_part_partno, + false); flag= tmp_min_flag | tmp_max_flag; } else @@ -8671,7 +8679,8 @@ Item_func_null_predicate::get_mm_leaf(RANGE_OPT_PARAM *param, if (!field->real_maybe_null()) DBUG_RETURN(type == ISNULL_FUNC ? &null_element : NULL); SEL_ARG *tree; - if (!(tree= new (alloc) SEL_ARG(field, is_null_string, is_null_string))) + bool is_asc= !(key_part->flag & HA_REVERSE_SORT); + if (!(tree= new (alloc) SEL_ARG(field, is_asc, is_null_string, is_null_string))) DBUG_RETURN(0); if (type == Item_func::ISNOTNULL_FUNC) { @@ -8771,7 +8780,8 @@ Item_func_like::get_mm_leaf(RANGE_OPT_PARAM *param, int2store(min_str + maybe_null, min_length); int2store(max_str + maybe_null, max_length); } - SEL_ARG *tree= new (param->mem_root) SEL_ARG(field, min_str, max_str); + bool is_asc= !(key_part->flag & HA_REVERSE_SORT); + SEL_ARG *tree= new (param->mem_root) SEL_ARG(field, is_asc, min_str, max_str); DBUG_RETURN(tree); } @@ -9019,18 +9029,19 @@ SEL_ARG *Field::stored_field_make_mm_leaf(RANGE_OPT_PARAM *param, if (!(str= make_key_image(param->mem_root, key_part))) DBUG_RETURN(0); + bool is_asc= !(key_part->flag & HA_REVERSE_SORT); switch (op) { case SCALAR_CMP_LE: - DBUG_RETURN(new (mem_root) SEL_ARG_LE(str, this)); + DBUG_RETURN(new (mem_root) SEL_ARG_LE(str, this, is_asc)); case SCALAR_CMP_LT: - DBUG_RETURN(new (mem_root) SEL_ARG_LT(thd, str, this, value)); + DBUG_RETURN(new (mem_root) SEL_ARG_LT(thd, str, this, is_asc, value)); case SCALAR_CMP_GT: DBUG_RETURN(new (mem_root) SEL_ARG_GT(thd, str, key_part, this, value)); case SCALAR_CMP_GE: DBUG_RETURN(new (mem_root) SEL_ARG_GE(thd, str, key_part, this, value)); case SCALAR_CMP_EQ: case SCALAR_CMP_EQUAL: - DBUG_RETURN(new (mem_root) SEL_ARG(this, str, str)); + DBUG_RETURN(new (mem_root) SEL_ARG(this, is_asc, str, str)); break; } DBUG_ASSERT(0); @@ -9048,18 +9059,19 @@ SEL_ARG *Field::stored_field_make_mm_leaf_exact(RANGE_OPT_PARAM *param, if (!(str= make_key_image(param->mem_root, key_part))) DBUG_RETURN(0); + bool is_asc= !(key_part->flag & HA_REVERSE_SORT); switch (op) { case SCALAR_CMP_LE: - DBUG_RETURN(new (param->mem_root) SEL_ARG_LE(str, this)); + DBUG_RETURN(new (param->mem_root) SEL_ARG_LE(str, this, is_asc)); case SCALAR_CMP_LT: - DBUG_RETURN(new (param->mem_root) SEL_ARG_LT(str, this)); + DBUG_RETURN(new (param->mem_root) SEL_ARG_LT(str, this, is_asc)); case SCALAR_CMP_GT: DBUG_RETURN(new (param->mem_root) SEL_ARG_GT(str, key_part, this)); case SCALAR_CMP_GE: - DBUG_RETURN(new (param->mem_root) SEL_ARG_GE(str, this)); + DBUG_RETURN(new (param->mem_root) SEL_ARG_GE(str, this, is_asc)); case SCALAR_CMP_EQ: case SCALAR_CMP_EQUAL: - DBUG_RETURN(new (param->mem_root) SEL_ARG(this, str, str)); + DBUG_RETURN(new (param->mem_root) SEL_ARG(this, is_asc, str, str)); break; } DBUG_ASSERT(0); @@ -11777,6 +11789,46 @@ get_quick_select(PARAM *param,uint idx,SEL_ARG *key_tree, uint mrr_flags, } +void SEL_ARG::store_next_min_max_keys(KEY_PART *key, + uchar **cur_min_key, uint *cur_min_flag, + uchar **cur_max_key, uint *cur_max_flag, + int *min_part, int *max_part) +{ + DBUG_ASSERT(next_key_part); + bool asc = next_key_part->is_ascending; + + if (!get_min_flag()) + { + if (asc) + { + *min_part += next_key_part->store_min_key(key, cur_min_key, + cur_min_flag, MAX_KEY, true); + } + else + { + uint tmp_flag = invert_min_flag(*cur_min_flag); + *min_part += next_key_part->store_max_key(key, cur_min_key, &tmp_flag, + MAX_KEY, true); + *cur_min_flag = invert_max_flag(tmp_flag); + } + } + if (!get_max_flag()) + { + if (asc) + { + *max_part += next_key_part->store_max_key(key, cur_max_key, + cur_max_flag, MAX_KEY, false); + } + else + { + uint tmp_flag = invert_max_flag(*cur_max_flag); + *max_part += next_key_part->store_min_key(key, cur_max_key, &tmp_flag, + MAX_KEY, false); + *cur_max_flag = invert_min_flag(tmp_flag); + } + } +} + /* ** Fix this to get all possible sub_ranges */ @@ -11790,17 +11842,19 @@ get_quick_keys(PARAM *param,QUICK_RANGE_SELECT *quick,KEY_PART *key, int min_part= key_tree->part-1, // # of keypart values in min_key buffer max_part= key_tree->part-1; // # of keypart values in max_key buffer - if (key_tree->left != &null_element) + SEL_ARG *next_tree = key_tree->is_ascending ? key_tree->left : key_tree->right; + if (next_tree != &null_element) { - if (get_quick_keys(param,quick,key,key_tree->left, + if (get_quick_keys(param,quick,key,next_tree, min_key,min_key_flag, max_key, max_key_flag)) return 1; } uchar *tmp_min_key=min_key,*tmp_max_key=max_key; - min_part+= key_tree->store_min(key[key_tree->part].store_length, - &tmp_min_key,min_key_flag); - max_part+= key_tree->store_max(key[key_tree->part].store_length, - &tmp_max_key,max_key_flag); + + key_tree->store_min_max(key[key_tree->part].store_length, + &tmp_min_key, min_key_flag, + &tmp_max_key, max_key_flag, + &min_part, &max_part); if (key_tree->next_key_part && key_tree->next_key_part->type == SEL_ARG::KEY_RANGE && @@ -11810,31 +11864,40 @@ get_quick_keys(PARAM *param,QUICK_RANGE_SELECT *quick,KEY_PART *key, memcmp(min_key, max_key, (uint)(tmp_max_key - max_key))==0 && key_tree->min_flag==0 && key_tree->max_flag==0) { + // psergey-note: simplified the parameters below as follows: + // min_key_flag | key_tree->min_flag -> min_key_flag + // max_key_flag | key_tree->max_flag -> max_key_flag if (get_quick_keys(param,quick,key,key_tree->next_key_part, - tmp_min_key, min_key_flag | key_tree->min_flag, - tmp_max_key, max_key_flag | key_tree->max_flag)) + tmp_min_key, min_key_flag, + tmp_max_key, max_key_flag)) return 1; goto end; // Ugly, but efficient } { - uint tmp_min_flag=key_tree->min_flag,tmp_max_flag=key_tree->max_flag; - if (!tmp_min_flag) - min_part+= key_tree->next_key_part->store_min_key(key, - &tmp_min_key, - &tmp_min_flag, - MAX_KEY); - if (!tmp_max_flag) - max_part+= key_tree->next_key_part->store_max_key(key, - &tmp_max_key, - &tmp_max_flag, - MAX_KEY); + uint tmp_min_flag= key_tree->get_min_flag(); + uint tmp_max_flag= key_tree->get_max_flag(); + + key_tree->store_next_min_max_keys(key, + &tmp_min_key, &tmp_min_flag, + &tmp_max_key, &tmp_max_flag, + &min_part, &max_part); flag=tmp_min_flag | tmp_max_flag; } } else { - flag = (key_tree->min_flag & GEOM_FLAG) ? - key_tree->min_flag : key_tree->min_flag | key_tree->max_flag; + if (key_tree->is_ascending) + { + flag= (key_tree->min_flag & GEOM_FLAG) ? key_tree->min_flag: + (key_tree->min_flag | + key_tree->max_flag); + } + else + { + // Invert flags for DESC keypart + flag= invert_min_flag(key_tree->min_flag) | + invert_max_flag(key_tree->max_flag); + } } /* @@ -11895,8 +11958,9 @@ get_quick_keys(PARAM *param,QUICK_RANGE_SELECT *quick,KEY_PART *key, return 1; end: - if (key_tree->right != &null_element) - return get_quick_keys(param,quick,key,key_tree->right, + next_tree = key_tree->is_ascending ? key_tree->right : key_tree->left; + if (next_tree != &null_element) + return get_quick_keys(param,quick,key,next_tree, min_key,min_key_flag, max_key,max_key_flag); return 0; diff --git a/sql/opt_range.h b/sql/opt_range.h index 1014176ecc5..6864a5c583a 100644 --- a/sql/opt_range.h +++ b/sql/opt_range.h @@ -54,6 +54,33 @@ struct KEY_PART { }; +/** + A helper function to invert min flags to max flags for DESC key parts. + It changes NEAR_MIN, NO_MIN_RANGE to NEAR_MAX, NO_MAX_RANGE appropriately +*/ + +inline uint invert_min_flag(uint min_flag) +{ + uint max_flag_out = min_flag & ~(NEAR_MIN | NO_MIN_RANGE); + if (min_flag & NEAR_MIN) max_flag_out |= NEAR_MAX; + if (min_flag & NO_MIN_RANGE) max_flag_out |= NO_MAX_RANGE; + return max_flag_out; +} + + +/** + A helper function to invert max flags to min flags for DESC key parts. + It changes NEAR_MAX, NO_MAX_RANGE to NEAR_MIN, NO_MIN_RANGE appropriately +*/ + +inline uint invert_max_flag(uint max_flag) +{ + uint min_flag_out = max_flag & ~(NEAR_MAX | NO_MAX_RANGE); + if (max_flag & NEAR_MAX) min_flag_out |= NEAR_MIN; + if (max_flag & NO_MAX_RANGE) min_flag_out |= NO_MIN_RANGE; + return min_flag_out; +} + class RANGE_OPT_PARAM; /* A construction block of the SEL_ARG-graph. @@ -267,6 +294,8 @@ class RANGE_OPT_PARAM; - it is a lot easier to compute than computing the number of ranges, - it can be updated incrementally when performing AND/OR operations on parts of the graph. + + 6. For handling DESC keyparts, See HowRangeOptimizerHandlesDescKeyparts */ class SEL_ARG :public Sql_alloc @@ -277,6 +306,11 @@ class SEL_ARG :public Sql_alloc uint8 min_flag,max_flag,maybe_flag; uint8 part; // Which key part uint8 maybe_null; + /* + Whether the keypart is ascending or descending. + See HowRangeOptimizerHandlesDescKeyparts for details. + */ + uint8 is_ascending; /* The ordinal number the least significant component encountered in the ranges of the SEL_ARG tree (the first component has number 1) @@ -327,11 +361,15 @@ class SEL_ARG :public Sql_alloc SEL_ARG() {} SEL_ARG(SEL_ARG &); - SEL_ARG(Field *,const uchar *, const uchar *); - SEL_ARG(Field *field, uint8 part, uchar *min_value, uchar *max_value, + SEL_ARG(Field *, bool is_asc, const uchar *, const uchar *); + SEL_ARG(Field *field, uint8 part, bool is_asc, + uchar *min_value, uchar *max_value, uint8 min_flag, uint8 max_flag, uint8 maybe_flag); + + /* This is used to construct degenerate SEL_ARGS like ALWAYS, IMPOSSIBLE, etc */ SEL_ARG(enum Type type_arg) - :min_flag(0), max_part_no(0) /* first key part means 1. 0 mean 'no parts'*/, + :min_flag(0), is_ascending(false), + max_part_no(0) /* first key part means 1. 0 mean 'no parts'*/, elements(1),use_count(1),left(0),right(0), next_key_part(0), color(BLACK), type(type_arg), weight(1) {} @@ -409,19 +447,20 @@ class SEL_ARG :public Sql_alloc { new_max=arg->max_value; flag_max=arg->max_flag; } - return new (thd->mem_root) SEL_ARG(field, part, new_min, new_max, flag_min, + return new (thd->mem_root) SEL_ARG(field, part, is_ascending, + new_min, new_max, flag_min, flag_max, MY_TEST(maybe_flag && arg->maybe_flag)); } SEL_ARG *clone_first(SEL_ARG *arg) { // min <= X < arg->min - return new SEL_ARG(field,part, min_value, arg->min_value, + return new SEL_ARG(field, part, is_ascending, min_value, arg->min_value, min_flag, arg->min_flag & NEAR_MIN ? 0 : NEAR_MAX, maybe_flag | arg->maybe_flag); } SEL_ARG *clone_last(SEL_ARG *arg) { // min <= X <= key_max - return new SEL_ARG(field, part, min_value, arg->max_value, + return new SEL_ARG(field, part, is_ascending, min_value, arg->max_value, min_flag, arg->max_flag, maybe_flag | arg->maybe_flag); } SEL_ARG *clone(RANGE_OPT_PARAM *param, SEL_ARG *new_parent, SEL_ARG **next); @@ -504,6 +543,56 @@ class SEL_ARG :public Sql_alloc return 0; } + /* Save minimum and maximum, taking index order into account */ + void store_min_max(uint length, + uchar **min_key, uint min_flag, + uchar **max_key, uint max_flag, + int *min_part, int *max_part) + { + if (is_ascending) { + *min_part += store_min(length, min_key, min_flag); + *max_part += store_max(length, max_key, max_flag); + } else { + *max_part += store_min(length, max_key, min_flag); + *min_part += store_max(length, min_key, max_flag); + } + } + /* + Get the flag for range's starting endpoint, taking index order into + account. + */ + uint get_min_flag() + { + return (is_ascending ? min_flag : invert_max_flag(max_flag)); + } + /* + Get the flag for range's starting endpoint, taking index order into + account. + */ + uint get_max_flag() + { + return (is_ascending ? max_flag : invert_min_flag(min_flag)); + } + /* Get the previous interval, taking index order into account */ + inline SEL_ARG* index_order_prev() + { + return is_ascending? prev: next; + } + /* Get the next interval, taking index order into account */ + inline SEL_ARG* index_order_next() + { + return is_ascending? next: prev; + } + + /* + Produce a single multi-part interval, taking key part ordering into + account. + */ + void store_next_min_max_keys(KEY_PART *key, uchar **cur_min_key, + uint *cur_min_flag, uchar **cur_max_key, + uint *cur_max_flag, int *min_part, + int *max_part); + /* Returns a number of keypart values appended to the key buffer for min key and max key. This function is used by both Range @@ -516,7 +605,8 @@ class SEL_ARG :public Sql_alloc int store_min_key(KEY_PART *key, uchar **range_key, uint *range_key_flag, - uint last_part) + uint last_part, + bool start_key) { SEL_ARG *key_tree= first(); uint res= key_tree->store_min(key[key_tree->part].store_length, @@ -525,15 +615,26 @@ class SEL_ARG :public Sql_alloc if (!res) return 0; *range_key_flag|= key_tree->min_flag; - if (key_tree->next_key_part && - key_tree->next_key_part->type == SEL_ARG::KEY_RANGE && + SEL_ARG *nkp= key_tree->next_key_part; + if (nkp && nkp->type == SEL_ARG::KEY_RANGE && key_tree->part != last_part && - key_tree->next_key_part->part == key_tree->part+1 && + nkp->part == key_tree->part+1 && !(*range_key_flag & (NO_MIN_RANGE | NEAR_MIN))) - res+= key_tree->next_key_part->store_min_key(key, - range_key, - range_key_flag, - last_part); + { + const bool asc = nkp->is_ascending; + if (start_key == asc) + { + res+= nkp->store_min_key(key, range_key, range_key_flag, last_part, + start_key); + } + else + { + uint tmp_flag = invert_min_flag(*range_key_flag); + res += nkp->store_max_key(key, range_key, &tmp_flag, last_part, + start_key); + *range_key_flag = invert_max_flag(tmp_flag); + } + } return res; } @@ -541,7 +642,8 @@ class SEL_ARG :public Sql_alloc int store_max_key(KEY_PART *key, uchar **range_key, uint *range_key_flag, - uint last_part) + uint last_part, + bool start_key) { SEL_ARG *key_tree= last(); uint res=key_tree->store_max(key[key_tree->part].store_length, @@ -549,15 +651,26 @@ class SEL_ARG :public Sql_alloc if (!res) return 0; *range_key_flag|= key_tree->max_flag; - if (key_tree->next_key_part && - key_tree->next_key_part->type == SEL_ARG::KEY_RANGE && + SEL_ARG *nkp= key_tree->next_key_part; + if (nkp && nkp->type == SEL_ARG::KEY_RANGE && key_tree->part != last_part && - key_tree->next_key_part->part == key_tree->part+1 && + nkp->part == key_tree->part+1 && !(*range_key_flag & (NO_MAX_RANGE | NEAR_MAX))) - res+= key_tree->next_key_part->store_max_key(key, - range_key, - range_key_flag, - last_part); + { + const bool asc = nkp->is_ascending; + if ((!start_key && asc) || (start_key && !asc)) + { + res += nkp->store_max_key(key, range_key, range_key_flag, last_part, + start_key); + } + else + { + uint tmp_flag = invert_max_flag(*range_key_flag); + res += nkp->store_min_key(key, range_key, &tmp_flag, last_part, + start_key); + *range_key_flag = invert_min_flag(tmp_flag); + } + } return res; } @@ -661,13 +774,83 @@ class SEL_ARG :public Sql_alloc SEL_ARG *clone_tree(RANGE_OPT_PARAM *param); }; +/* + HowRangeOptimizerHandlesDescKeyparts + ==================================== + + Starting with MySQL-8.0 and MariaDB 10.8, index key parts may be descending, + for example: + + INDEX idx1(col1, col2 DESC, col3, col4 DESC) + + Range Optimizer handles this as follows: + + The SEL_ARG object has SEL_ARG::is_ascending which specifies whether the + keypart is ascending. + + Other than that, the SEL_ARG graph is built without any regard to DESC + keyparts. + + For example, for an index + + INDEX idx2(kp1 DESC, kp2) + + and range + + kp1 BETWEEN 10 and 20 (RANGE-1) + + the SEL_ARG will have min_value=10, max_value=20, is_ascending=false. + + The ordering of key parts is taken into account when SEL_ARG graph is + linearized to ranges, in sel_arg_range_seq_next() and get_quick_keys(). + + The storage engine expects the first bound to be the first in the index and + the last bound to be the last, that is, for (RANGE-1) we will flip min and + max and generate these key_range structures: + + start.key='20' , end.key='10' + + See SEL_ARG::store_min_max(). The flag values are flipped as well, see + SEL_ARG::get_min_flag(), get_max_flag(). + + == Handling multiple key parts == + + For multi-part keys, the order of key parts has an effect on which ranges are + generated. Consider + + kp1 >= 10 AND kp2 >'foo' + + for INDEX(kp1 ASC, kp2 ASC) the range will be + + (kp1, kp2) > (10, 'foo') + + while for INDEX(kp1 ASC, kp2 DESC) it will be just + + kp1 >= 10 + + Another example: + + (kp1 BETWEEN 10 AND 20) AND (kp2 BETWEEN 'foo' AND 'quux') + + with INDEX (kp1 ASC, kp2 ASC) will generate + + (10, 'foo') <= (kp1, kp2) < (20, 'quux') + + while with index INDEX (kp1 ASC, kp2 DESC) it will generate + + (10, 'quux') <= (kp1, kp2) < (20, 'foo') + + This is again achieved by sel_arg_range_seq_next() and get_quick_keys() + flipping SEL_ARG's min,max, their flags and next/prev as needed. +*/ + extern MYSQL_PLUGIN_IMPORT SEL_ARG null_element; class SEL_ARG_IMPOSSIBLE: public SEL_ARG { public: SEL_ARG_IMPOSSIBLE(Field *field) - :SEL_ARG(field, 0, 0) + :SEL_ARG(field, false, 0, 0) { type= SEL_ARG::IMPOSSIBLE; } diff --git a/sql/opt_range_mrr.cc b/sql/opt_range_mrr.cc index 20413f5df63..8877e15d5b5 100644 --- a/sql/opt_range_mrr.cc +++ b/sql/opt_range_mrr.cc @@ -34,7 +34,7 @@ typedef struct st_range_seq_entry uint min_key_flag, max_key_flag; /* Number of key parts */ - uint min_key_parts, max_key_parts; + int min_key_parts, max_key_parts; SEL_ARG *key_tree; } RANGE_SEQ_ENTRY; @@ -105,13 +105,14 @@ static void step_down_to(SEL_ARG_RANGE_SEQ *arg, SEL_ARG *key_tree) cur->max_key_parts= prev->max_key_parts; uint16 stor_length= arg->param->key[arg->keyno][key_tree->part].store_length; - cur->min_key_parts += key_tree->store_min(stor_length, &cur->min_key, - prev->min_key_flag); - cur->max_key_parts += key_tree->store_max(stor_length, &cur->max_key, - prev->max_key_flag); - cur->min_key_flag= prev->min_key_flag | key_tree->min_flag; - cur->max_key_flag= prev->max_key_flag | key_tree->max_flag; + key_tree->store_min_max(stor_length, + &cur->min_key, prev->min_key_flag, + &cur->max_key, prev->max_key_flag, + &cur->min_key_parts, &cur->max_key_parts); + + cur->min_key_flag= prev->min_key_flag | key_tree->get_min_flag(); + cur->max_key_flag= prev->max_key_flag | key_tree->get_max_flag(); if (key_tree->is_null_interval()) cur->min_key_flag |= NULL_RANGE; @@ -165,12 +166,12 @@ bool sel_arg_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range) /* Ok, we're at some "full tuple" position in the tree */ /* Step down if we can */ - if (key_tree->next && key_tree->next != &null_element) + if (key_tree->index_order_next() && key_tree->index_order_next() != &null_element) { //step down; (update the tuple, we'll step right and stay there) seq->i--; - step_down_to(seq, key_tree->next); - key_tree= key_tree->next; + step_down_to(seq, key_tree->index_order_next()); + key_tree= key_tree->index_order_next(); seq->is_ror_scan= FALSE; goto walk_right_n_up; } @@ -185,12 +186,12 @@ bool sel_arg_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range) key_tree= seq->stack[seq->i].key_tree; /* Step down if we can */ - if (key_tree->next && key_tree->next != &null_element) + if (key_tree->index_order_next() && key_tree->index_order_next() != &null_element) { // Step down; update the tuple seq->i--; - step_down_to(seq, key_tree->next); - key_tree= key_tree->next; + step_down_to(seq, key_tree->index_order_next()); + key_tree= key_tree->index_order_next(); break; } } @@ -214,16 +215,10 @@ bool sel_arg_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range) !key_tree->min_flag && !key_tree->max_flag)) { seq->is_ror_scan= FALSE; - if (!key_tree->min_flag) - cur->min_key_parts += - key_tree->next_key_part->store_min_key(seq->param->key[seq->keyno], - &cur->min_key, - &cur->min_key_flag, MAX_KEY); - if (!key_tree->max_flag) - cur->max_key_parts += - key_tree->next_key_part->store_max_key(seq->param->key[seq->keyno], - &cur->max_key, - &cur->max_key_flag, MAX_KEY); + key_tree->store_next_min_max_keys(seq->param->key[seq->keyno], + &cur->min_key, &cur->min_key_flag, + &cur->max_key, &cur->max_key_flag, + &cur->min_key_parts, &cur->max_key_parts); break; } } @@ -235,10 +230,11 @@ bool sel_arg_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range) key_tree= key_tree->next_key_part; walk_up_n_right: - while (key_tree->prev && key_tree->prev != &null_element) + while (key_tree->index_order_prev() && + key_tree->index_order_prev() != &null_element) { /* Step up */ - key_tree= key_tree->prev; + key_tree= key_tree->index_order_prev(); } step_down_to(seq, key_tree); }

1 0