1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
|
# Test for Bug #30423, InnoDBs treatment of NULL in index stats causes
# bad "rows examined" estimates.
# Implemented InnoDB system variable "innodb_stats_method" with
# "nulls_equal" (default), "nulls_unequal", and "nulls_ignored" options.
-- source include/have_innodb.inc
let $innodb_stats_method_orig = `select @@innodb_stats_method`;
# default setting for innodb_stats_method is "nulls_equal"
set global innodb_stats_method = default;
select @@innodb_stats_method;
# create three tables, bug30243_1, bug30243_2 and bug30243_3.
# The test scenario is adopted from original bug #30423 report.
# table bug30243_1 and bug30243_3 have many NULL values
-- disable_result_log
-- disable_query_log
DROP TABLE IF EXISTS bug30243_1;
CREATE TABLE bug30243_1 (
org_id int(11) NOT NULL default '0',
UNIQUE KEY (org_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
LOCK TABLES bug30243_1 WRITE;
INSERT INTO bug30243_1 VALUES (11),(15),(16),(17),(19),(20),(21),(23),(24),
(25),(26),(27),(28),(29),(30),(31),(32),(33),(34),(35),(37),(38),(40),(41),
(42),(43),(44),(45),(46),(47),(48),(49),(50),(51),(52),(53),(54),(55),(56),
(57),(58),(59),(60),(61),(62),(63),(64),(65),(66),(67),(68),(69),(70),(71),
(72),(73),(74),(75),(76),(77),(78),(79),(80),(81),(82),(83),(84),(85),(86),
(87),(88),(89),(90),(91),(92),(93),(94),(95),(96),(97),(98),(99),(100),(101),
(102),(103),(104),(105),(106),(107),(108),(109),(110),(111),(112),(113),(114),
(115),(116),(117),(118),(119),(120),(121),(122),(123),(124),(125),(126),(127),
(128),(129),(130),(131),(132),(133),(134),(135),(136),(137),(138),(139),(140),
(141),(142),(143),(144),(145);
UNLOCK TABLES;
DROP TABLE IF EXISTS bug30243_3;
CREATE TABLE bug30243_3 (
org_id int(11) default NULL,
KEY (org_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO bug30243_3 VALUES (NULL);
begin;
let $i=14;
while ($i)
{
INSERT INTO bug30243_3 SELECT NULL FROM bug30243_3;
dec $i;
}
INSERT INTO bug30243_3 VALUES (34),(34),(35),(56),(58),(62),(62),(64),(65),(66),(80),(135),(137),(138),(139),(140),(142),(143),(144),(145);
commit;
DROP TABLE IF EXISTS bug30243_2;
CREATE TABLE bug30243_2 (
org_id int(11) default NULL,
KEY `contacts$org_id` (org_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO bug30243_2 VALUES (NULL);
begin;
let $i=16;
while ($i)
{
INSERT INTO bug30243_2 SELECT NULL FROM bug30243_2;
dec $i;
}
INSERT INTO bug30243_2 VALUES (11),(15),(16),(17),(20),(21),(23),(24),(25),
(26),(27),(28),(29),(30),(31),(32),(33),(34),(37),(38),(40),(41),(42),(43),
(44),(45),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),
(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),
(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),
(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),
(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),
(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(48),
(48),(50),(51),(52),(52),(53),(54),(55),(57),(60),(61),(62),(62),(62),(62),
(62),(63),(64),(64),(65),(66),(66),(67),(68),(69),(70),(71),(72),(73),(74),
(75),(76),(77),(78),(79),(80),(80),(81),(82),(83),(84),(85),(86),(87),(88),
(89),(90),(91),(92),(93),(94),(95),(96),(97),(98),(99),(100),(101),(102),
(103),(104),(105),(106),(107),(108),(109),(110),(111),(112),(113),(114),
(115),(116),(117),(118),(119),(120),(121),(122),(123),(124),(125),(126),
(127),(128),(129),(130),(131),(132),(133),(133),(135),(135),(135),(135),
(136),(136),(138),(138),(139),(139),(139),(140),(141),(141),(142),(143),
(143),(145),(145);
commit;
-- enable_result_log
-- enable_query_log
# check tables's value
select count(*) from bug30243_3 where org_id is not NULL;
select count(*) from bug30243_3 where org_id is NULL;
select count(*) from bug30243_2 where org_id is not NULL;
select count(*) from bug30243_2 where org_id is NULL;
select @@innodb_stats_method;
analyze table bug30243_1;
analyze table bug30243_2;
analyze table bug30243_3;
# Following query plan shows that we over estimate the rows per
# unique value (since there are many NULLs).
# Skip this query log since the stats estimate could vary from runs
-- disable_query_log
-- disable_result_log
explain SELECT COUNT(*), 0
FROM bug30243_1 orgs
LEFT JOIN bug30243_3 sa_opportunities
ON orgs.org_id=sa_opportunities.org_id
LEFT JOIN bug30243_2 contacts
ON orgs.org_id=contacts.org_id ;
-- enable_query_log
-- enable_result_log
# following set operation will fail
#--error ER_WRONG_VALUE_FOR_VAR
--error 1231
set global innodb_stats_method = "NULL";
set global innodb_stats_method = "nulls_ignored";
select @@innodb_stats_method;
# Regenerate the stats with "nulls_ignored" option
analyze table bug30243_1;
analyze table bug30243_2;
analyze table bug30243_3;
# Following query plan shows that we get the correct rows per
# unique value (should be approximately 1 row per value)
explain SELECT COUNT(*), 0
FROM bug30243_1 orgs
LEFT JOIN bug30243_3 sa_opportunities
ON orgs.org_id=sa_opportunities.org_id
LEFT JOIN bug30243_2 contacts
ON orgs.org_id=contacts.org_id ;
select @@innodb_stats_method;
# Try the "nulls_unequal" option
set global innodb_stats_method = "nulls_unequal";
select @@innodb_stats_method;
analyze table bug30243_1;
analyze table bug30243_2;
analyze table bug30243_3;
# Following query plan shows that we get the correct rows per
# unique value (~1)
explain SELECT COUNT(*), 0
FROM bug30243_1 orgs
LEFT JOIN bug30243_3 sa_opportunities
ON orgs.org_id=sa_opportunities.org_id
LEFT JOIN bug30243_2 contacts
ON orgs.org_id=contacts.org_id;
# Create a table with all NULL values, make sure the stats calculation
# does not crash with table of all NULL values
-- disable_query_log
CREATE TABLE table_bug30423 (
org_id int(11) default NULL,
KEY(org_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `table_bug30423` VALUES (NULL);
begin;
let $i=10;
while ($i)
{
INSERT INTO table_bug30423 SELECT NULL FROM table_bug30423;
dec $i;
}
commit;
-- enable_query_log
SELECT COUNT(*) FROM table_bug30423 WHERE org_id IS NULL;
# calculate the statistics for the table for "nulls_ignored" and
# "nulls_unequal" option
set global innodb_stats_method = "nulls_unequal";
analyze table table_bug30423;
set global innodb_stats_method = "nulls_ignored";
analyze table table_bug30423;
eval set global innodb_stats_method = $innodb_stats_method_orig;
drop table bug30243_2;
drop table bug30243_1;
drop table bug30243_3;
drop table table_bug30423;
|
