Debian Codegraph @

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

raw
1 ############## mysql-test\t\myisam_stats_method_func.test ####################
2 # #
3 # Variable Name: myisam_stats_method #
4 # Scope: GLOBAL | SESSION #
5 # Access Type: Dynamic #
6 # Data Type: enumeration #
7 # Default Value: nulls_equal #
8 # Valid Values: nulls_equal, nulls_unequal #
9 # #
10 # #
11 # Creation Date: 2008-03-08 #
12 # Author: Rizwan #
13 # #
14 # Description: Test Cases of Dynamic System Variable myisam_stats_method #
15 # that checks the behavior of this variable #
16 # #
17 # Reference: #
18 # http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html #
19 # #
20 ###############################################################################
21
22 --echo '#--------------------FN_DYNVARS_097_01-------------------------#'
23 #####################################################################
24 # Check if Setting myisam_stats_method is changed in new connection #
25 #####################################################################
26
27 SET @start_value = @@global.myisam_stats_method;
28
29 SET @@global.myisam_stats_method = nulls_equal;
30 connect (con1,localhost,root,,,,);
31 connection con1;
32 SELECT @@global.myisam_stats_method;
33 SELECT @@session.myisam_stats_method;
34 disconnect con1;
35
36 --echo '#--------------------FN_DYNVARS_097_02-------------------------#'
37 ###########################################################
38 # Begin the functionality Testing of myisam_stats_method #
39 ###########################################################
40
41 connection default;
42
43 --disable_warnings
44 DROP TABLE IF EXISTS t1;
45 --enable_warnings
46
47 CREATE TABLE t1 (a INT, KEY (a));
48 INSERT INTO t1 VALUES (0),(1),(2),(3),(4);
49 INSERT INTO t1 SELECT NULL FROM t1;
50
51 #=======================================
52 --echo 'default: NULLs considered unequal'
53 #=======================================
54 SET myisam_stats_method = nulls_unequal;
55
56 ANALYZE TABLE t1;
57 SHOW INDEX FROM t1;
58 INSERT INTO t1 VALUES (11);
59 DELETE FROM t1 WHERE a = 11;
60 CHECK TABLE t1;
61 SHOW INDEX FROM t1;
62
63
64 #=====================================
65 --echo 'Set nulls to be equal'
66 #=====================================
67 SET myisam_stats_method = nulls_equal;
68 set @save_use_stat_tables= @@use_stat_tables;
69 set @@use_stat_tables= COMPLEMENTARY;
70
71 INSERT INTO t1 VALUES (11);
72 DELETE FROM t1 WHERE a = 11;
73 ANALYZE TABLE t1;
74 SHOW INDEX FROM t1;
75 INSERT INTO t1 VALUES (11);
76 DELETE FROM t1 WHERE a = 11;
77 CHECK TABLE t1;
78 SHOW INDEX FROM t1;
79
80
81 #=====================================
82 --echo 'Set nulls to be ignored'
83 #=====================================
84
85 SET myisam_stats_method = nulls_ignored;
86 SHOW VARIABLES LIKE 'myisam_stats_method';
87 DROP TABLE t1;
88
89 CREATE TABLE t1 (
90 a CHAR(3), b CHAR(4), c CHAR(5), d CHAR(6),
91 KEY(a,b,c,d)
92 );
93 INSERT INTO t1 VALUES ('bcd','def1', NULL, 'zz');
94 INSERT INTO t1 VALUES ('bcd','def2', NULL, 'zz');
95 INSERT INTO t1 VALUES ('bce','def1', 'yuu', NULL);
96 INSERT INTO t1 VALUES ('bce','def2', NULL, 'quux');
97 ANALYZE TABLE t1;
98 SHOW INDEX FROM t1;
99 DELETE FROM t1;
100 ANALYZE TABLE t1;
101 SHOW INDEX FROM t1;
102
103 SET myisam_stats_method = DEFAULT;
104 set @@use_stat_tables= @save_use_stat_tables;
105 DROP TABLE t1;
106
107 SET @@global.myisam_stats_method= @start_value;
108
109 ########################################################
110 # End of functionality Testing for myisam_stats_method #
111 ########################################################