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 --source include/force_myisam_default.inc
22
23 # Skip the test if MyISAM is not available
24 --source include/have_myisam.inc
25
26 --echo '#--------------------FN_DYNVARS_097_01-------------------------#'
27 #####################################################################
28 # Check if Setting myisam_stats_method is changed in new connection #
29 #####################################################################
30
31 SET @start_value = @@global.myisam_stats_method;
32
33 SET @@global.myisam_stats_method = nulls_equal;
34 --echo 'connect (con1,localhost,root,,,,)'
35 connect (con1,localhost,root,,,,);
36 --echo 'connection con1'
37 connection con1;
38 SELECT @@global.myisam_stats_method;
39 SELECT @@session.myisam_stats_method;
40 disconnect con1;
41
42 --echo '#--------------------FN_DYNVARS_097_02-------------------------#'
43 ###########################################################
44 # Begin the functionality Testing of myisam_stats_method #
45 ###########################################################
46
47 --echo 'connection default'
48 connection default;
49
50 --disable_warnings
51 DROP TABLE IF EXISTS t1;
52 --enable_warnings
53
54 CREATE TABLE t1 (a INT, KEY (a));
55 INSERT INTO t1 VALUES (0),(1),(2),(3),(4);
56 INSERT INTO t1 SELECT NULL FROM t1;
57
58 #=======================================
59 --echo 'default: NULLs considered unequal'
60 #=======================================
61 SET myisam_stats_method = nulls_unequal;
62
63 ANALYZE TABLE t1;
64 SHOW INDEX FROM t1;
65 INSERT INTO t1 VALUES (11);
66 DELETE FROM t1 WHERE a = 11;
67 CHECK TABLE t1;
68 SHOW INDEX FROM t1;
69
70
71 #=====================================
72 --echo 'Set nulls to be equal'
73 #=====================================
74 SET myisam_stats_method = nulls_equal;
75
76 INSERT INTO t1 VALUES (11);
77 DELETE FROM t1 WHERE a = 11;
78 ANALYZE TABLE t1;
79 SHOW INDEX FROM t1;
80 INSERT INTO t1 VALUES (11);
81 DELETE FROM t1 WHERE a = 11;
82 CHECK TABLE t1;
83 SHOW INDEX FROM t1;
84
85 #=====================================
86 --echo 'Set nulls to be ignored'
87 #=====================================
88
89 SET myisam_stats_method = nulls_ignored;
90 SHOW VARIABLES LIKE 'myisam_stats_method';
91 DROP TABLE t1;
92
93 CREATE TABLE t1 (
94 a CHAR(3), b CHAR(4), c CHAR(5), d CHAR(6),
95 KEY(a,b,c,d)
96 );
97 INSERT INTO t1 VALUES ('bcd','def1', NULL, 'zz');
98 INSERT INTO t1 VALUES ('bcd','def2', NULL, 'zz');
99 INSERT INTO t1 VALUES ('bce','def1', 'yuu', NULL);
100 INSERT INTO t1 VALUES ('bce','def2', NULL, 'quux');
101 ANALYZE TABLE t1;
102 SHOW INDEX FROM t1;
103 DELETE FROM t1;
104 ANALYZE TABLE t1;
105 SHOW INDEX FROM t1;
106
107 SET myisam_stats_method = DEFAULT;
108 DROP TABLE t1;
109
110 SET @@global.myisam_stats_method= @start_value;
111
112 ########################################################
113 # End of functionality Testing for myisam_stats_method #
114 ########################################################
115