MDEV-15253: Default optimizer setting changes for MariaDB 10.410.4-selectivity3

6 files changed, 33 insertions, 28 deletions

diff --git a/mysql-test/suite/sys_vars/r/max_seeks_for_key_func.result b/mysql-test/suite/sys_vars/r/max_seeks_for_key_func.result
index 1798bd5d6e0..f833f417290 100644
--- a/mysql-test/suite/sys_vars/r/max_seeks_for_key_func.result
+++ b/mysql-test/suite/sys_vars/r/max_seeks_for_key_func.result
@@ -70,6 +70,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	17	Using where; Using join buffer (flat, BNL join)
 ANALYZE TABLE t1;
 Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
 test.t1	analyze	status	OK
 SET MAX_SEEKS_FOR_KEY=1;
 EXPLAIN SELECT STRAIGHT_JOIN * FROM t1,t1 AS t2 WHERE t1.b = t2.b;
diff --git a/mysql-test/suite/sys_vars/r/myisam_stats_method_func.result b/mysql-test/suite/sys_vars/r/myisam_stats_method_func.result
index cae0e68765b..75fe06e2faf 100644
--- a/mysql-test/suite/sys_vars/r/myisam_stats_method_func.result
+++ b/mysql-test/suite/sys_vars/r/myisam_stats_method_func.result
@@ -20,6 +20,7 @@ INSERT INTO t1 SELECT NULL FROM t1;
 SET myisam_stats_method = nulls_unequal;
 ANALYZE TABLE t1;
 Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
 test.t1	analyze	status	OK
 SHOW INDEX FROM t1;
 Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
@@ -38,6 +39,7 @@ INSERT INTO t1 VALUES (11);
 DELETE FROM t1 WHERE a = 11;
 ANALYZE TABLE t1;
 Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
 test.t1	analyze	status	OK
 SHOW INDEX FROM t1;
 Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
@@ -49,7 +51,7 @@ Table	Op	Msg_type	Msg_text
 test.t1	check	status	OK
 SHOW INDEX FROM t1;
 Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
-t1	1	a	1	a	A	5	NULL	NULL	YES	BTREE		
+t1	1	a	1	a	A	10	NULL	NULL	YES	BTREE		
 'Set nulls to be ignored'
 SET myisam_stats_method = nulls_ignored;
 SHOW VARIABLES LIKE 'myisam_stats_method';
@@ -66,6 +68,7 @@ INSERT INTO t1 VALUES ('bce','def1', 'yuu', NULL);
 INSERT INTO t1 VALUES ('bce','def2', NULL, 'quux');
 ANALYZE TABLE t1;
 Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
 test.t1	analyze	status	OK
 SHOW INDEX FROM t1;
 Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
@@ -76,6 +79,7 @@ t1	1	a	4	d	A	4	NULL	NULL	YES	BTREE
 DELETE FROM t1;
 ANALYZE TABLE t1;
 Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
 test.t1	analyze	status	OK
 SHOW INDEX FROM t1;
 Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
diff --git a/mysql-test/suite/sys_vars/r/optimizer_use_condition_selectivity_basic.result b/mysql-test/suite/sys_vars/r/optimizer_use_condition_selectivity_basic.result
index a030bae3750..941b7699747 100644
--- a/mysql-test/suite/sys_vars/r/optimizer_use_condition_selectivity_basic.result
+++ b/mysql-test/suite/sys_vars/r/optimizer_use_condition_selectivity_basic.result
@@ -1,33 +1,33 @@
 SET @start_global_value = @@global.optimizer_use_condition_selectivity;
 SELECT @start_global_value;
 @start_global_value
-1
+4
 SET @start_session_value = @@session.optimizer_use_condition_selectivity;
 SELECT @start_session_value;
 @start_session_value
-1
+4
 '#--------------------FN_DYNVARS_115_01-------------------------#'
 SET @@global.optimizer_use_condition_selectivity = DEFAULT;
 SELECT @@global.optimizer_use_condition_selectivity;
 @@global.optimizer_use_condition_selectivity
-1
+4
 SET @@session.optimizer_use_condition_selectivity = DEFAULT;
 SELECT @@session.optimizer_use_condition_selectivity;
 @@session.optimizer_use_condition_selectivity
-1
+4
 '#--------------------FN_DYNVARS_115_02-------------------------#'
 SET @@global.optimizer_use_condition_selectivity = DEFAULT;
 SELECT @@global.optimizer_use_condition_selectivity = 1;
 @@global.optimizer_use_condition_selectivity = 1
-1
+0
 SET @@session.optimizer_use_condition_selectivity = DEFAULT;
 SELECT @@session.optimizer_use_condition_selectivity = 1;
 @@session.optimizer_use_condition_selectivity = 1
-1
+0
 '#--------------------FN_DYNVARS_115_03-------------------------#'
 SELECT @@global.optimizer_use_condition_selectivity;
 @@global.optimizer_use_condition_selectivity
-1
+4
 SET @@global.optimizer_use_condition_selectivity = 0;
 Warnings:
 Warning	1292	Truncated incorrect optimizer_use_condition_selectiv value: '0'
@@ -63,7 +63,7 @@ SELECT @@global.optimizer_use_condition_selectivity;
 '#--------------------FN_DYNVARS_115_04-------------------------#'
 SELECT @@session.optimizer_use_condition_selectivity;
 @@session.optimizer_use_condition_selectivity
-1
+4
 SET @@session.optimizer_use_condition_selectivity = 0;
 Warnings:
 Warning	1292	Truncated incorrect optimizer_use_condition_selectiv value: '0'
@@ -134,8 +134,8 @@ SELECT @@local.optimizer_use_condition_selectivity = @@session.optimizer_use_con
 SET @@global.optimizer_use_condition_selectivity = @start_global_value;
 SELECT @@global.optimizer_use_condition_selectivity;
 @@global.optimizer_use_condition_selectivity
-1
+4
 SET @@session.optimizer_use_condition_selectivity = @start_session_value;
 SELECT @@session.optimizer_use_condition_selectivity;
 @@session.optimizer_use_condition_selectivity
-1
+4
diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result
index 29e80c79e7c..85faa31c064 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result
@@ -2729,10 +2729,10 @@ ENUM_VALUE_LIST	index_merge,index_merge_union,index_merge_sort_union,index_merge
 READ_ONLY	NO
 COMMAND_LINE_ARGUMENT	REQUIRED
 VARIABLE_NAME	OPTIMIZER_USE_CONDITION_SELECTIVITY
-SESSION_VALUE	1
-GLOBAL_VALUE	1
+SESSION_VALUE	4
+GLOBAL_VALUE	4
 GLOBAL_VALUE_ORIGIN	COMPILE-TIME
-DEFAULT_VALUE	1
+DEFAULT_VALUE	4
 VARIABLE_SCOPE	SESSION
 VARIABLE_TYPE	BIGINT UNSIGNED
 VARIABLE_COMMENT	Controls selectivity of which conditions the optimizer takes into account to calculate cardinality of a partial join when it searches for the best execution plan Meaning: 1 - use selectivity of index backed range conditions to calculate the cardinality of a partial join if the last joined table is accessed by full table scan or an index scan, 2 - use selectivity of index backed range conditions to calculate the cardinality of a partial join in any case, 3 - additionally always use selectivity of range conditions that are not backed by any index to calculate the cardinality of a partial join, 4 - use histograms to calculate selectivity of range conditions that are not backed by any index to calculate the cardinality of a partial join.5 - additionally use selectivity of certain non-range predicates calculated on record samples
@@ -4507,10 +4507,10 @@ ENUM_VALUE_LIST	OFF,ON
 READ_ONLY	NO
 COMMAND_LINE_ARGUMENT	OPTIONAL
 VARIABLE_NAME	USE_STAT_TABLES
-SESSION_VALUE	NEVER
-GLOBAL_VALUE	NEVER
+SESSION_VALUE	PREFERABLY
+GLOBAL_VALUE	PREFERABLY
 GLOBAL_VALUE_ORIGIN	COMPILE-TIME
-DEFAULT_VALUE	NEVER
+DEFAULT_VALUE	PREFERABLY
 VARIABLE_SCOPE	SESSION
 VARIABLE_TYPE	ENUM
 VARIABLE_COMMENT	Specifies how to use system statistics tables
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 71761df1ab3..36a02f6e79f 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
@@ -2953,10 +2953,10 @@ ENUM_VALUE_LIST	index_merge,index_merge_union,index_merge_sort_union,index_merge
 READ_ONLY	NO
 COMMAND_LINE_ARGUMENT	REQUIRED
 VARIABLE_NAME	OPTIMIZER_USE_CONDITION_SELECTIVITY
-SESSION_VALUE	1
-GLOBAL_VALUE	1
+SESSION_VALUE	4
+GLOBAL_VALUE	4
 GLOBAL_VALUE_ORIGIN	COMPILE-TIME
-DEFAULT_VALUE	1
+DEFAULT_VALUE	4
 VARIABLE_SCOPE	SESSION
 VARIABLE_TYPE	BIGINT UNSIGNED
 VARIABLE_COMMENT	Controls selectivity of which conditions the optimizer takes into account to calculate cardinality of a partial join when it searches for the best execution plan Meaning: 1 - use selectivity of index backed range conditions to calculate the cardinality of a partial join if the last joined table is accessed by full table scan or an index scan, 2 - use selectivity of index backed range conditions to calculate the cardinality of a partial join in any case, 3 - additionally always use selectivity of range conditions that are not backed by any index to calculate the cardinality of a partial join, 4 - use histograms to calculate selectivity of range conditions that are not backed by any index to calculate the cardinality of a partial join.5 - additionally use selectivity of certain non-range predicates calculated on record samples
@@ -5571,10 +5571,10 @@ ENUM_VALUE_LIST	OFF,ON
 READ_ONLY	NO
 COMMAND_LINE_ARGUMENT	OPTIONAL
 VARIABLE_NAME	USE_STAT_TABLES
-SESSION_VALUE	NEVER
-GLOBAL_VALUE	NEVER
+SESSION_VALUE	PREFERABLY
+GLOBAL_VALUE	PREFERABLY
 GLOBAL_VALUE_ORIGIN	COMPILE-TIME
-DEFAULT_VALUE	NEVER
+DEFAULT_VALUE	PREFERABLY
 VARIABLE_SCOPE	SESSION
 VARIABLE_TYPE	ENUM
 VARIABLE_COMMENT	Specifies how to use system statistics tables
diff --git a/mysql-test/suite/sys_vars/r/use_stat_tables_basic.result b/mysql-test/suite/sys_vars/r/use_stat_tables_basic.result
index 64f6d868fa6..3db981c7e86 100644
--- a/mysql-test/suite/sys_vars/r/use_stat_tables_basic.result
+++ b/mysql-test/suite/sys_vars/r/use_stat_tables_basic.result
@@ -1,16 +1,16 @@
 SET @start_global_value = @@global.use_stat_tables;
 SELECT @start_global_value;
 @start_global_value
-NEVER
+PREFERABLY
 SET @start_session_value = @@session.use_stat_tables;
 SELECT @start_session_value;
 @start_session_value
-NEVER
+PREFERABLY
 SET @@global.use_stat_tables = 2;
 SET @@global.use_stat_tables = DEFAULT;
 SELECT @@global.use_stat_tables;
 @@global.use_stat_tables
-NEVER
+PREFERABLY
 SET @@global.use_stat_tables = 0;
 SELECT @@global.use_stat_tables;
 @@global.use_stat_tables
@@ -87,9 +87,9 @@ USE_STAT_TABLES	COMPLEMENTARY
 SET @@global.use_stat_tables = @start_global_value;
 SELECT @@global.use_stat_tables;
 @@global.use_stat_tables
-NEVER
+PREFERABLY
 SET @@session.use_stat_tables = @start_session_value;
 SELECT @@session.use_stat_tables;
 @@session.use_stat_tables
-NEVER
+PREFERABLY
 set sql_mode='';