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