Debian Codegraph @

mysql-test/suite/innodb/t/innodb_bug30423.test

raw
1 -- source include/big_test.inc
2 # Test for Bug #30423, InnoDBs treatment of NULL in index stats causes
3 # bad "rows examined" estimates.
4 # Implemented InnoDB system variable "innodb_stats_method" with
5 # "nulls_equal" (default), "nulls_unequal", and "nulls_ignored" options.
6
7
8 let $innodb_stats_method_orig = `select @@innodb_stats_method`;
9
10 # default setting for innodb_stats_method is "nulls_equal"
11 set global innodb_stats_method = default;
12
13 select @@innodb_stats_method;
14
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
18
19 -- disable_result_log
20 -- disable_query_log
21
22 DROP TABLE IF EXISTS bug30243_1;
23 CREATE TABLE bug30243_1 (
24 org_id int(11) NOT NULL default '0',
25 UNIQUE KEY (org_id)
26 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
27
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);
39 UNLOCK TABLES;
40
41 DROP TABLE IF EXISTS bug30243_3;
42 CREATE TABLE bug30243_3 (
43 org_id int(11) default NULL,
44 KEY (org_id)
45 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
46
47 INSERT INTO bug30243_3 VALUES (NULL);
48
49 begin;
50 let $i=14;
51 while ($i)
52 {
53 INSERT INTO bug30243_3 SELECT NULL FROM bug30243_3;
54 dec $i;
55 }
56
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);
58 commit;
59
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;
65
66 INSERT INTO bug30243_2 VALUES (NULL);
67
68 begin;
69 let $i=16;
70 while ($i)
71 {
72 INSERT INTO bug30243_2 SELECT NULL FROM bug30243_2;
73 dec $i;
74 }
75
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),
92 (143),(145),(145);
93 commit;
94
95
96 -- enable_result_log
97 -- enable_query_log
98
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;
102
103 select count(*) from bug30243_2 where org_id is not NULL;
104 select count(*) from bug30243_2 where org_id is NULL;
105
106 select @@innodb_stats_method;
107
108 analyze table bug30243_1;
109 analyze table bug30243_2;
110 analyze table bug30243_3;
111
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
115 -- disable_query_log
116 -- disable_result_log
117 explain SELECT COUNT(*), 0
118 FROM bug30243_1 orgs
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 ;
123 -- enable_query_log
124 -- enable_result_log
125
126 # following set operation will fail
127 #--error ER_WRONG_VALUE_FOR_VAR
128 --error 1231
129 set global innodb_stats_method = "NULL";
130
131 set global innodb_stats_method = "nulls_ignored";
132
133 select @@innodb_stats_method;
134
135 # Regenerate the stats with "nulls_ignored" option
136
137 analyze table bug30243_1;
138 analyze table bug30243_2;
139 analyze table bug30243_3;
140
141 # Following query plan shows that we get the correct rows per
142 # unique value (should be approximately 1 row per value)
143 -- replace_column 10 ROWS
144 explain SELECT COUNT(*), 0
145 FROM bug30243_1 orgs
146 LEFT JOIN bug30243_3 sa_opportunities
147 ON orgs.org_id=sa_opportunities.org_id
148 LEFT JOIN bug30243_2 contacts
149 ON orgs.org_id=contacts.org_id ;
150
151 select @@innodb_stats_method;
152
153 # Try the "nulls_unequal" option
154 set global innodb_stats_method = "nulls_unequal";
155
156 select @@innodb_stats_method;
157
158 analyze table bug30243_1;
159 analyze table bug30243_2;
160 analyze table bug30243_3;
161
162 # Following query plan shows that we get the correct rows per
163 # unique value (~1)
164 -- replace_column 10 ROWS
165 explain SELECT COUNT(*), 0
166 FROM bug30243_1 orgs
167 LEFT JOIN bug30243_3 sa_opportunities
168 ON orgs.org_id=sa_opportunities.org_id
169 LEFT JOIN bug30243_2 contacts
170 ON orgs.org_id=contacts.org_id;
171
172
173 # Create a table with all NULL values, make sure the stats calculation
174 # does not crash with table of all NULL values
175 -- disable_query_log
176 CREATE TABLE table_bug30423 (
177 org_id int(11) default NULL,
178 KEY(org_id)
179 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
180
181 INSERT INTO `table_bug30423` VALUES (NULL);
182
183 begin;
184 let $i=10;
185 while ($i)
186 {
187 INSERT INTO table_bug30423 SELECT NULL FROM table_bug30423;
188 dec $i;
189 }
190 commit;
191
192 -- enable_query_log
193
194 SELECT COUNT(*) FROM table_bug30423 WHERE org_id IS NULL;
195
196 # calculate the statistics for the table for "nulls_ignored" and
197 # "nulls_unequal" option
198 set global innodb_stats_method = "nulls_unequal";
199 analyze table table_bug30423;
200
201 set global innodb_stats_method = "nulls_ignored";
202 analyze table table_bug30423;
203
204
205 eval set global innodb_stats_method = $innodb_stats_method_orig;
206
207 drop table bug30243_2;
208
209 drop table bug30243_1;
210
211 drop table bug30243_3;
212
213 drop table table_bug30423;