1set global innodb_stats_method = default; 2select @@innodb_stats_method; 3@@innodb_stats_method 4nulls_equal 5select count(*) from bug30243_3 where org_id is not NULL; 6count(*) 720 8select count(*) from bug30243_3 where org_id is NULL; 9count(*) 1016384 11select count(*) from bug30243_2 where org_id is not NULL; 12count(*) 13224 14select count(*) from bug30243_2 where org_id is NULL; 15count(*) 1665536 17select @@innodb_stats_method; 18@@innodb_stats_method 19nulls_equal 20analyze table bug30243_1; 21Table Op Msg_type Msg_text 22test.bug30243_1 analyze status OK 23analyze table bug30243_2; 24Table Op Msg_type Msg_text 25test.bug30243_2 analyze status OK 26analyze table bug30243_3; 27Table Op Msg_type Msg_text 28test.bug30243_3 analyze status OK 29set global innodb_stats_method = "NULL"; 30ERROR 42000: Variable 'innodb_stats_method' can't be set to the value of 'NULL' 31set global innodb_stats_method = "nulls_ignored"; 32select @@innodb_stats_method; 33@@innodb_stats_method 34nulls_ignored 35analyze table bug30243_1; 36Table Op Msg_type Msg_text 37test.bug30243_1 analyze status OK 38analyze table bug30243_2; 39Table Op Msg_type Msg_text 40test.bug30243_2 analyze status OK 41analyze table bug30243_3; 42Table Op Msg_type Msg_text 43test.bug30243_3 analyze status OK 44explain SELECT COUNT(*), 0 45FROM bug30243_1 orgs 46LEFT JOIN bug30243_3 sa_opportunities 47ON orgs.org_id=sa_opportunities.org_id 48LEFT JOIN bug30243_2 contacts 49ON orgs.org_id=contacts.org_id ; 50id select_type table partitions type possible_keys key key_len ref rows filtered Extra 511 SIMPLE orgs NULL index NULL org_id 4 NULL ROWS 100.00 Using index 521 SIMPLE sa_opportunities NULL ref org_id org_id 5 test.orgs.org_id ROWS 100.00 Using index 531 SIMPLE contacts NULL ref contacts$org_id contacts$org_id 5 test.orgs.org_id ROWS 100.00 Using index 54Warnings: 55Note 1003 /* select#1 */ select count(0) AS `COUNT(*)`,0 AS `0` from `test`.`bug30243_1` `orgs` left join `test`.`bug30243_3` `sa_opportunities` on((`test`.`sa_opportunities`.`org_id` = `test`.`orgs`.`org_id`)) left join `test`.`bug30243_2` `contacts` on((`test`.`contacts`.`org_id` = `test`.`orgs`.`org_id`)) where 1 56select @@innodb_stats_method; 57@@innodb_stats_method 58nulls_ignored 59set global innodb_stats_method = "nulls_unequal"; 60select @@innodb_stats_method; 61@@innodb_stats_method 62nulls_unequal 63analyze table bug30243_1; 64Table Op Msg_type Msg_text 65test.bug30243_1 analyze status OK 66analyze table bug30243_2; 67Table Op Msg_type Msg_text 68test.bug30243_2 analyze status OK 69analyze table bug30243_3; 70Table Op Msg_type Msg_text 71test.bug30243_3 analyze status OK 72explain SELECT COUNT(*), 0 73FROM bug30243_1 orgs 74LEFT JOIN bug30243_3 sa_opportunities 75ON orgs.org_id=sa_opportunities.org_id 76LEFT JOIN bug30243_2 contacts 77ON orgs.org_id=contacts.org_id; 78id select_type table partitions type possible_keys key key_len ref rows filtered Extra 791 SIMPLE orgs NULL index NULL org_id 4 NULL ROWS 100.00 Using index 801 SIMPLE sa_opportunities NULL ref org_id org_id 5 test.orgs.org_id ROWS 100.00 Using index 811 SIMPLE contacts NULL ref contacts$org_id contacts$org_id 5 test.orgs.org_id ROWS 100.00 Using index 82Warnings: 83Note 1003 /* select#1 */ select count(0) AS `COUNT(*)`,0 AS `0` from `test`.`bug30243_1` `orgs` left join `test`.`bug30243_3` `sa_opportunities` on((`test`.`sa_opportunities`.`org_id` = `test`.`orgs`.`org_id`)) left join `test`.`bug30243_2` `contacts` on((`test`.`contacts`.`org_id` = `test`.`orgs`.`org_id`)) where 1 84SELECT COUNT(*) FROM table_bug30423 WHERE org_id IS NULL; 85COUNT(*) 861024 87set global innodb_stats_method = "nulls_unequal"; 88analyze table table_bug30423; 89Table Op Msg_type Msg_text 90test.table_bug30423 analyze status OK 91set global innodb_stats_method = "nulls_ignored"; 92analyze table table_bug30423; 93Table Op Msg_type Msg_text 94test.table_bug30423 analyze status OK 95set global innodb_stats_method = nulls_equal; 96drop table bug30243_2; 97drop table bug30243_1; 98drop table bug30243_3; 99drop table table_bug30423; 100