1
set global innodb_stats_method = default;
2
select @@innodb_stats_method;
5
select count(*) from bug30243_3 where org_id is not NULL;
8
select count(*) from bug30243_3 where org_id is NULL;
11
select count(*) from bug30243_2 where org_id is not NULL;
14
select count(*) from bug30243_2 where org_id is NULL;
17
select @@innodb_stats_method;
20
analyze table bug30243_1;
21
Table Op Msg_type Msg_text
22
test.bug30243_1 analyze status OK
23
analyze table bug30243_2;
24
Table Op Msg_type Msg_text
25
test.bug30243_2 analyze status OK
26
analyze table bug30243_3;
27
Table Op Msg_type Msg_text
28
test.bug30243_3 analyze status OK
29
set global innodb_stats_method = "NULL";
30
ERROR 42000: Variable 'stats_method' can't be set to the value of 'NULL'
31
set global innodb_stats_method = "nulls_ignored";
32
select @@innodb_stats_method;
35
analyze table bug30243_1;
36
Table Op Msg_type Msg_text
37
test.bug30243_1 analyze status OK
38
analyze table bug30243_2;
39
Table Op Msg_type Msg_text
40
test.bug30243_2 analyze status OK
41
analyze table bug30243_3;
42
Table Op Msg_type Msg_text
43
test.bug30243_3 analyze status OK
44
explain SELECT COUNT(*), 0
46
LEFT JOIN bug30243_3 sa_opportunities
47
ON orgs.org_id=sa_opportunities.org_id
48
LEFT JOIN bug30243_2 contacts
49
ON orgs.org_id=contacts.org_id ;
50
id select_type table type possible_keys key key_len ref rows Extra
51
1 SIMPLE orgs index NULL org_id 4 NULL 128 Using index
52
1 SIMPLE sa_opportunities ref org_id org_id 5 test.orgs.org_id 1 Using index
53
1 SIMPLE contacts ref contacts$org_id contacts$org_id 5 test.orgs.org_id 1 Using index
54
select @@innodb_stats_method;
57
set global innodb_stats_method = "nulls_unequal";
58
select @@innodb_stats_method;
61
analyze table bug30243_1;
62
Table Op Msg_type Msg_text
63
test.bug30243_1 analyze status OK
64
analyze table bug30243_2;
65
Table Op Msg_type Msg_text
66
test.bug30243_2 analyze status OK
67
analyze table bug30243_3;
68
Table Op Msg_type Msg_text
69
test.bug30243_3 analyze status OK
70
explain SELECT COUNT(*), 0
72
LEFT JOIN bug30243_3 sa_opportunities
73
ON orgs.org_id=sa_opportunities.org_id
74
LEFT JOIN bug30243_2 contacts
75
ON orgs.org_id=contacts.org_id;
76
id select_type table type possible_keys key key_len ref rows Extra
77
1 SIMPLE orgs index NULL org_id 4 NULL 128 Using index
78
1 SIMPLE sa_opportunities ref org_id org_id 5 test.orgs.org_id 1 Using index
79
1 SIMPLE contacts ref contacts$org_id contacts$org_id 5 test.orgs.org_id 1 Using index
80
SELECT COUNT(*) FROM table_bug30423 WHERE org_id IS NULL;
83
set global innodb_stats_method = "nulls_unequal";
84
analyze table table_bug30423;
85
Table Op Msg_type Msg_text
86
test.table_bug30423 analyze status OK
87
set global innodb_stats_method = "nulls_ignored";
88
analyze table table_bug30423;
89
Table Op Msg_type Msg_text
90
test.table_bug30423 analyze status OK
91
set global innodb_stats_method = nulls_equal;
92
drop table bug30243_2;
93
drop table bug30243_1;
94
drop table bug30243_3;
95
drop table table_bug30423;