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