diff options
| -rw-r--r-- | mysql-test/r/greedy_optimizer.result | 40 | ||||
| -rw-r--r-- | mysql-test/r/mysqld--help.result | 5 | ||||
| -rw-r--r-- | mysql-test/r/subselect3.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/subselect3_jcl6.result | 2 | ||||
| -rw-r--r-- | mysql-test/suite/sys_vars/r/optimizer_search_depth_basic.result | 28 | ||||
| -rw-r--r-- | mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result | 4 | ||||
| -rw-r--r-- | sql/sys_vars.cc | 20 |
7 files changed, 39 insertions, 62 deletions
diff --git a/mysql-test/r/greedy_optimizer.result b/mysql-test/r/greedy_optimizer.result index 74fe9980958..de9db45cb15 100644 --- a/mysql-test/r/greedy_optimizer.result +++ b/mysql-test/r/greedy_optimizer.result @@ -118,10 +118,10 @@ select @@optimizer_prune_level; 1 set optimizer_search_depth=63; Warnings: -Warning 1287 'optimizer-search-depth=63' is deprecated and will be removed in a future release. Please use a search depth less than 63 instead +Warning 1292 Truncated incorrect optimizer_search_depth value: '63' select @@optimizer_search_depth; @@optimizer_search_depth -63 +62 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 @@ -133,7 +133,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 821.837037 +Last_query_cost 1693.637037 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 @@ -145,55 +145,55 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 821.837037 +Last_query_cost 1693.637037 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 794.837037 +Last_query_cost 844.037037 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 794.837037 +Last_query_cost 844.037037 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where +1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 794.837037 +Last_query_cost 844.037037 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where +1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 794.837037 +Last_query_cost 844.037037 set optimizer_prune_level=0; select @@optimizer_prune_level; @@optimizer_prune_level diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result index 6d0dbe8be42..af68b796b81 100644 --- a/mysql-test/r/mysqld--help.result +++ b/mysql-test/r/mysqld--help.result @@ -559,10 +559,7 @@ The following options may be given as the first argument: a query. Values smaller than the number of tables in a relation result in faster optimization, but may produce very bad query plans. If set to 0, the system will - automatically pick a reasonable value; if set to 63, the - optimizer will switch to the original find_best search. - NOTE: The value 63 and its associated behaviour is - deprecated + automatically pick a reasonable value --optimizer-selectivity-sampling-limit=# Controls number of record samples to check condition selectivity diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result index 24d9f0de35a..906f6a2f083 100644 --- a/mysql-test/r/subselect3.result +++ b/mysql-test/r/subselect3.result @@ -1294,7 +1294,7 @@ id select_type table type possible_keys key key_len ref rows Extra set @save_optimizer_search_depth=@@optimizer_search_depth; set @@optimizer_search_depth=63; Warnings: -Warning 1287 'optimizer-search-depth=63' is deprecated and will be removed in a future release. Please use a search depth less than 63 instead +Warning 1292 Truncated incorrect optimizer_search_depth value: '63' explain select * from t1 where (a,b) in (select a,b from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 10 diff --git a/mysql-test/r/subselect3_jcl6.result b/mysql-test/r/subselect3_jcl6.result index 19d3d25148f..7cea1cadddd 100644 --- a/mysql-test/r/subselect3_jcl6.result +++ b/mysql-test/r/subselect3_jcl6.result @@ -1304,7 +1304,7 @@ id select_type table type possible_keys key key_len ref rows Extra set @save_optimizer_search_depth=@@optimizer_search_depth; set @@optimizer_search_depth=63; Warnings: -Warning 1287 'optimizer-search-depth=63' is deprecated and will be removed in a future release. Please use a search depth less than 63 instead +Warning 1292 Truncated incorrect optimizer_search_depth value: '63' explain select * from t1 where (a,b) in (select a,b from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where diff --git a/mysql-test/suite/sys_vars/r/optimizer_search_depth_basic.result b/mysql-test/suite/sys_vars/r/optimizer_search_depth_basic.result index de448d3e2fc..75d99943288 100644 --- a/mysql-test/suite/sys_vars/r/optimizer_search_depth_basic.result +++ b/mysql-test/suite/sys_vars/r/optimizer_search_depth_basic.result @@ -10,7 +10,6 @@ SELECT @start_session_value; SET @@global.optimizer_search_depth = 100; Warnings: Warning 1292 Truncated incorrect optimizer_search_depth value: '100' -Warning 1287 'optimizer-search-depth=63' is deprecated and will be removed in a future release. Please use a search depth less than 63 instead SET @@global.optimizer_search_depth = DEFAULT; SELECT @@global.optimizer_search_depth; @@global.optimizer_search_depth @@ -18,7 +17,6 @@ SELECT @@global.optimizer_search_depth; SET @@session.optimizer_search_depth = 200; Warnings: Warning 1292 Truncated incorrect optimizer_search_depth value: '200' -Warning 1287 'optimizer-search-depth=63' is deprecated and will be removed in a future release. Please use a search depth less than 63 instead SET @@session.optimizer_search_depth = DEFAULT; SELECT @@session.optimizer_search_depth; @@session.optimizer_search_depth @@ -47,10 +45,10 @@ SELECT @@global.optimizer_search_depth; 62 SET @@global.optimizer_search_depth = 63; Warnings: -Warning 1287 'optimizer-search-depth=63' is deprecated and will be removed in a future release. Please use a search depth less than 63 instead +Warning 1292 Truncated incorrect optimizer_search_depth value: '63' SELECT @@global.optimizer_search_depth; @@global.optimizer_search_depth -63 +62 '#--------------------FN_DYNVARS_116_04-------------------------#' SET @@session.optimizer_search_depth = 0; SELECT @@session.optimizer_search_depth; @@ -66,18 +64,17 @@ SELECT @@session.optimizer_search_depth; 62 SET @@session.optimizer_search_depth = 63; Warnings: -Warning 1287 'optimizer-search-depth=63' is deprecated and will be removed in a future release. Please use a search depth less than 63 instead +Warning 1292 Truncated incorrect optimizer_search_depth value: '63' SELECT @@session.optimizer_search_depth; @@session.optimizer_search_depth -63 +62 '#------------------FN_DYNVARS_116_05-----------------------#' SET @@global.optimizer_search_depth = 64; Warnings: Warning 1292 Truncated incorrect optimizer_search_depth value: '64' -Warning 1287 'optimizer-search-depth=63' is deprecated and will be removed in a future release. Please use a search depth less than 63 instead SELECT @@global.optimizer_search_depth; @@global.optimizer_search_depth -63 +62 SET @@global.optimizer_search_depth = -1; Warnings: Warning 1292 Truncated incorrect optimizer_search_depth value: '-1' @@ -87,27 +84,25 @@ SELECT @@global.optimizer_search_depth; SET @@global.optimizer_search_depth = 65536; Warnings: Warning 1292 Truncated incorrect optimizer_search_depth value: '65536' -Warning 1287 'optimizer-search-depth=63' is deprecated and will be removed in a future release. Please use a search depth less than 63 instead SELECT @@global.optimizer_search_depth; @@global.optimizer_search_depth -63 +62 SET @@global.optimizer_search_depth = 65530.34; ERROR 42000: Incorrect argument type to variable 'optimizer_search_depth' SELECT @@global.optimizer_search_depth; @@global.optimizer_search_depth -63 +62 SET @@global.optimizer_search_depth = test; ERROR 42000: Incorrect argument type to variable 'optimizer_search_depth' SELECT @@global.optimizer_search_depth; @@global.optimizer_search_depth -63 +62 SET @@session.optimizer_search_depth = 64; Warnings: Warning 1292 Truncated incorrect optimizer_search_depth value: '64' -Warning 1287 'optimizer-search-depth=63' is deprecated and will be removed in a future release. Please use a search depth less than 63 instead SELECT @@session.optimizer_search_depth; @@session.optimizer_search_depth -63 +62 SET @@session.optimizer_search_depth = -2; Warnings: Warning 1292 Truncated incorrect optimizer_search_depth value: '-2' @@ -119,15 +114,14 @@ ERROR 42000: Incorrect argument type to variable 'optimizer_search_depth' SET @@session.optimizer_search_depth = 65550; Warnings: Warning 1292 Truncated incorrect optimizer_search_depth value: '65550' -Warning 1287 'optimizer-search-depth=63' is deprecated and will be removed in a future release. Please use a search depth less than 63 instead SELECT @@session.optimizer_search_depth; @@session.optimizer_search_depth -63 +62 SET @@session.optimizer_search_depth = test; ERROR 42000: Incorrect argument type to variable 'optimizer_search_depth' SELECT @@session.optimizer_search_depth; @@session.optimizer_search_depth -63 +62 '#------------------FN_DYNVARS_116_06-----------------------#' SELECT @@global.optimizer_search_depth = VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result index a562a668a97..1ab57c1ab36 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result @@ -2592,9 +2592,9 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 62 VARIABLE_SCOPE SESSION VARIABLE_TYPE BIGINT UNSIGNED -VARIABLE_COMMENT Maximum depth of search performed by the query optimizer. Values larger than the number of relations in a query result in better query plans, but take longer to compile a query. Values smaller than the number of tables in a relation result in faster optimization, but may produce very bad query plans. If set to 0, the system will automatically pick a reasonable value; if set to 63, the optimizer will switch to the original find_best search. NOTE: The value 63 and its associated behaviour is deprecated +VARIABLE_COMMENT Maximum depth of search performed by the query optimizer. Values larger than the number of relations in a query result in better query plans, but take longer to compile a query. Values smaller than the number of tables in a relation result in faster optimization, but may produce very bad query plans. If set to 0, the system will automatically pick a reasonable value NUMERIC_MIN_VALUE 0 -NUMERIC_MAX_VALUE 63 +NUMERIC_MAX_VALUE 62 NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc index 682d8039478..274a26d95c5 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -2316,17 +2316,6 @@ static Sys_var_ulong Sys_optimizer_use_condition_selectivity( SESSION_VAR(optimizer_use_condition_selectivity), CMD_LINE(REQUIRED_ARG), VALID_RANGE(1, 5), DEFAULT(1), BLOCK_SIZE(1)); -/** Warns about deprecated value 63 */ -static bool fix_optimizer_search_depth(sys_var *self, THD *thd, - enum_var_type type) -{ - SV *sv= type == OPT_GLOBAL ? &global_system_variables : &thd->variables; - if (sv->optimizer_search_depth == MAX_TABLES+2) - WARN_DEPRECATED(thd, 10, 2, "optimizer-search-depth=63", - "a search depth less than 63"); - return false; -} - static Sys_var_ulong Sys_optimizer_search_depth( "optimizer_search_depth", "Maximum depth of search performed by the query optimizer. Values " @@ -2334,13 +2323,10 @@ static Sys_var_ulong Sys_optimizer_search_depth( "query plans, but take longer to compile a query. Values smaller " "than the number of tables in a relation result in faster " "optimization, but may produce very bad query plans. If set to 0, " - "the system will automatically pick a reasonable value; if set to " - "63, the optimizer will switch to the original find_best search. " - "NOTE: The value 63 and its associated behaviour is deprecated", + "the system will automatically pick a reasonable value", SESSION_VAR(optimizer_search_depth), CMD_LINE(REQUIRED_ARG), - VALID_RANGE(0, MAX_TABLES+2), DEFAULT(MAX_TABLES+1), BLOCK_SIZE(1), - NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(0), - ON_UPDATE(fix_optimizer_search_depth)); + VALID_RANGE(0, MAX_TABLES+1), DEFAULT(MAX_TABLES+1), BLOCK_SIZE(1), + NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(0), ON_UPDATE(0)); /* this is used in the sigsegv handler */ export const char *optimizer_switch_names[]= |