| 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; |