Debian Codegraph @

mysql-test/suite/sys_vars/t/secondary_engine_cost_threshold_basic.test

raw
1 ############## mysql-test\t\secondary_engine_cost_threshold_basic.test ###########
2 # #
3 # Variable Name: secondary_engine_cost_threshold #
4 # Scope: GLOBAL/SESSION #
5 # Access Type: Dynamic #
6 # Data Type: numeric #
7 # Default Value:0 #
8 # #
9 # #
10 # #
11 # Description: Test Cases of Dynamic System Variable #
12 # Optimizer offload to secondary engine if the estimated cost of executing the #
13 # query on the primary storage engine exceeds a threshold value #
14 # test checks the behavior of this variable in the following ways #
15 # * Default Value #
16 # * Valid & Invalid values #
17 # * Scope & Access method #
18 # * Data Integrity #
19 # #
20 # Reference: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#
21 # #
22 ##################################################################################
23
24 --source include/load_sysvars.inc
25
26 ########################################################################
27
28 SET @start_value = @@session.secondary_engine_cost_threshold;
29 SELECT @start_value;
30 SET @start_value_global = @@global.secondary_engine_cost_threshold;
31 SELECT @start_value_global;
32
33 ########################################################################
34 # Display the DEFAULT value of secondary_engine_cost_threshold #
35 ########################################################################
36
37 SET @@session.secondary_engine_cost_threshold = 8734635;
38 SET @@session.secondary_engine_cost_threshold = DEFAULT;
39 SELECT @@session.secondary_engine_cost_threshold;
40
41
42 ##################################################################################
43 # Change the value of secondary_engine_cost_threshold to a valid value #
44 ##################################################################################
45
46 SET @@session.secondary_engine_cost_threshold = 0;
47 SELECT @@session.secondary_engine_cost_threshold;
48 SET @@session.secondary_engine_cost_threshold = 99;
49 SELECT @@session.secondary_engine_cost_threshold;
50 SET @@session.secondary_engine_cost_threshold = 10;
51 SELECT @@session.secondary_engine_cost_threshold;
52 SET @@session.secondary_engine_cost_threshold = 10000.01;
53 SELECT @@session.secondary_engine_cost_threshold;
54 SET @@session.secondary_engine_cost_threshold = 21;
55 SELECT @@session.secondary_engine_cost_threshold;
56
57 #####################################################################################
58 # Change the value of secondary_engine_cost_threshold to invalid value #
59 #####################################################################################
60
61 SET @@session.secondary_engine_cost_threshold = -1;
62 SELECT @@session.secondary_engine_cost_threshold;
63 SET @@session.secondary_engine_cost_threshold = -1024;
64 SELECT @@session.secondary_engine_cost_threshold;
65 SET @@session.secondary_engine_cost_threshold = 42949672950;
66 SELECT @@session.secondary_engine_cost_threshold;
67
68 --Error ER_WRONG_TYPE_FOR_VAR
69 SET @@session.secondary_engine_cost_threshold = ON;
70 SELECT @@session.secondary_engine_cost_threshold;
71 --Error ER_WRONG_TYPE_FOR_VAR
72 SET @@session.secondary_engine_cost_threshold = 'test';
73 SELECT @@session.secondary_engine_cost_threshold;
74
75
76 #####################################################################################
77 # Test if accessing global secondary_engine_cost_threshold works #
78 #####################################################################################
79
80 SELECT @@global.secondary_engine_cost_threshold,@@session.secondary_engine_cost_threshold;
81 SET @@global.secondary_engine_cost_threshold = 0;
82 SELECT @@global.secondary_engine_cost_threshold,@@session.secondary_engine_cost_threshold;
83
84 ##############################################################################
85 # Check if the value in session & SESSION Tables matches values in variable #
86 ##############################################################################
87
88 --disable_warnings
89 SELECT @@session.secondary_engine_cost_threshold = VARIABLE_VALUE
90 FROM performance_schema.session_variables
91 WHERE VARIABLE_NAME='secondary_engine_cost_threshold';
92
93 SELECT @@global.secondary_engine_cost_threshold = VARIABLE_VALUE
94 FROM performance_schema.global_variables
95 WHERE VARIABLE_NAME='secondary_engine_cost_threshold';
96 --enable_warnings
97
98 ###################################################################
99 # Check if TRUE and FALSE values can be used on variable #
100 ###################################################################
101
102 SET @@session.secondary_engine_cost_threshold = TRUE;
103 SELECT @@session.secondary_engine_cost_threshold;
104 SET @@session.secondary_engine_cost_threshold = FALSE;
105 SELECT @@session.secondary_engine_cost_threshold;
106
107 ########################################################################################################
108 # Check if accessing variable with SESSION,LOCAL and without SCOPE points to same session variable #
109 ########################################################################################################
110
111 SET @@session.secondary_engine_cost_threshold = 1;
112 SELECT @@secondary_engine_cost_threshold = @@session.secondary_engine_cost_threshold;
113
114 ####################################################################################
115 # Check if secondary_engine_cost_threshold can be accessed with and without @@ sign #
116 ####################################################################################
117
118 --Error ER_PARSE_ERROR
119 SET local.secondary_engine_cost_threshold = 1;
120 --Error ER_UNKNOWN_TABLE
121 SELECT local.secondary_engine_cost_threshold;
122 --Error ER_PARSE_ERROR
123 SET session.secondary_engine_cost_threshold = 1;
124 --Error ER_UNKNOWN_TABLE
125 SELECT session.secondary_engine_cost_threshold;
126 --Error ER_BAD_FIELD_ERROR
127 SELECT secondary_engine_cost_threshold;
128
129 ##############################
130 # Restore initial value #
131 ##############################
132
133 SET @@session.secondary_engine_cost_threshold = @start_value;
134 SET @@global.secondary_engine_cost_threshold = @start_value_global;
135 SELECT @@session.secondary_engine_cost_threshold,@@global.secondary_engine_cost_threshold;
136