Debian Codegraph @

mysql-test/suite/sys_vars/r/myisam_stats_method_func.result

raw
1 '#--------------------FN_DYNVARS_097_01-------------------------#'
2 SET @start_value = @@global.myisam_stats_method;
3 SET @@global.myisam_stats_method = nulls_equal;
4 connect con1,localhost,root,,,,;
5 connection con1;
6 SELECT @@global.myisam_stats_method;
7 @@global.myisam_stats_method
8 NULLS_EQUAL
9 SELECT @@session.myisam_stats_method;
10 @@session.myisam_stats_method
11 NULLS_EQUAL
12 disconnect con1;
13 '#--------------------FN_DYNVARS_097_02-------------------------#'
14 connection default;
15 DROP TABLE IF EXISTS t1;
16 CREATE TABLE t1 (a INT, KEY (a));
17 INSERT INTO t1 VALUES (0),(1),(2),(3),(4);
18 INSERT INTO t1 SELECT NULL FROM t1;
19 'default: NULLs considered unequal'
20 SET myisam_stats_method = nulls_unequal;
21 ANALYZE TABLE t1;
22 Table Op Msg_type Msg_text
23 test.t1 analyze status Engine-independent statistics collected
24 test.t1 analyze status OK
25 SHOW INDEX FROM t1;
26 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
27 t1 1 a 1 a A 10 NULL NULL YES BTREE NO
28 INSERT INTO t1 VALUES (11);
29 DELETE FROM t1 WHERE a = 11;
30 CHECK TABLE t1;
31 Table Op Msg_type Msg_text
32 test.t1 check status OK
33 SHOW INDEX FROM t1;
34 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
35 t1 1 a 1 a A 10 NULL NULL YES BTREE NO
36 'Set nulls to be equal'
37 SET myisam_stats_method = nulls_equal;
38 set @save_use_stat_tables= @@use_stat_tables;
39 set @@use_stat_tables= COMPLEMENTARY;
40 INSERT INTO t1 VALUES (11);
41 DELETE FROM t1 WHERE a = 11;
42 ANALYZE TABLE t1;
43 Table Op Msg_type Msg_text
44 test.t1 analyze status Engine-independent statistics collected
45 test.t1 analyze status OK
46 SHOW INDEX FROM t1;
47 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
48 t1 1 a 1 a A 5 NULL NULL YES BTREE NO
49 INSERT INTO t1 VALUES (11);
50 DELETE FROM t1 WHERE a = 11;
51 CHECK TABLE t1;
52 Table Op Msg_type Msg_text
53 test.t1 check status OK
54 SHOW INDEX FROM t1;
55 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
56 t1 1 a 1 a A 5 NULL NULL YES BTREE NO
57 'Set nulls to be ignored'
58 SET myisam_stats_method = nulls_ignored;
59 SHOW VARIABLES LIKE 'myisam_stats_method';
60 Variable_name Value
61 myisam_stats_method NULLS_IGNORED
62 DROP TABLE t1;
63 CREATE TABLE t1 (
64 a CHAR(3), b CHAR(4), c CHAR(5), d CHAR(6),
65 KEY(a,b,c,d)
66 );
67 INSERT INTO t1 VALUES ('bcd','def1', NULL, 'zz');
68 INSERT INTO t1 VALUES ('bcd','def2', NULL, 'zz');
69 INSERT INTO t1 VALUES ('bce','def1', 'yuu', NULL);
70 INSERT INTO t1 VALUES ('bce','def2', NULL, 'quux');
71 ANALYZE TABLE t1;
72 Table Op Msg_type Msg_text
73 test.t1 analyze status Engine-independent statistics collected
74 test.t1 analyze status OK
75 SHOW INDEX FROM t1;
76 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
77 t1 1 a 1 a A 2 NULL NULL YES BTREE NO
78 t1 1 a 2 b A 4 NULL NULL YES BTREE NO
79 t1 1 a 3 c A 4 NULL NULL YES BTREE NO
80 t1 1 a 4 d A 4 NULL NULL YES BTREE NO
81 DELETE FROM t1;
82 ANALYZE TABLE t1;
83 Table Op Msg_type Msg_text
84 test.t1 analyze status Engine-independent statistics collected
85 test.t1 analyze status OK
86 SHOW INDEX FROM t1;
87 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
88 t1 1 a 1 a A 0 NULL NULL YES BTREE NO
89 t1 1 a 2 b A 0 NULL NULL YES BTREE NO
90 t1 1 a 3 c A 0 NULL NULL YES BTREE NO
91 t1 1 a 4 d A 0 NULL NULL YES BTREE NO
92 SET myisam_stats_method = DEFAULT;
93 set @@use_stat_tables= @save_use_stat_tables;
94 DROP TABLE t1;
95 SET @@global.myisam_stats_method= @start_value;