Bug #95678 query gives empty result when using index_merge
Submitted: 6 Jun 2019 21:22 Modified: 8 Jul 2019 12:38
Reporter: Kendal Miller Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.6.33 OS:Ubuntu
Assigned to: CPU Architecture:x86
Tags: analyze, cache, empty, index_merge, result

[6 Jun 2019 21:22] Kendal Miller 
Description:
I'm seeing this in mysql 5.6.33

The table looks like:

```
mysql> mysql> show create table users \G
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1',
  `system_account` tinyint(1) NOT NULL DEFAULT '0',
  `email` varchar(255) NOT NULL,
  `user_type` varchar(20) NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `sermo_employee` tinyint(1) DEFAULT '0',
  `eula_date` datetime DEFAULT NULL,
  `eula_rejected_date` datetime DEFAULT NULL,
  `reset_password_token` varchar(255) DEFAULT NULL,
  `reset_password_sent_at` datetime DEFAULT NULL,
  `remember_created_at` datetime DEFAULT NULL,
  `sign_in_count` int(11) DEFAULT '0',
  `current_sign_in_at` datetime DEFAULT NULL,
  `last_sign_in_at` datetime DEFAULT NULL,
  `current_sign_in_ip` varchar(255) DEFAULT NULL,
  `last_sign_in_ip` varchar(255) DEFAULT NULL,
  `failed_attempts` int(11) DEFAULT '0',
  `unlock_token` varchar(255) DEFAULT NULL,
  `locked_at` datetime DEFAULT NULL,
  `login_token` varchar(255) DEFAULT NULL,
  `dea_attempt` int(11) DEFAULT '0',
  `primary_role` varchar(255) DEFAULT NULL,
  `uuid` varchar(255) DEFAULT NULL,
  `client_identifier` varchar(255) DEFAULT NULL,
  `otp_secret_key` varchar(255) DEFAULT NULL,
  `second_factor_attempts_count` int(11) DEFAULT '0',
  `mobile_phone` varchar(15) DEFAULT NULL,
  `master_group_staff_master_group_id` int(11) DEFAULT NULL,
  `call_center` varchar(255) DEFAULT NULL,
  `pat_enabled` tinyint(1) NOT NULL DEFAULT '1',
  `about_me` text,
  `avatar_file_name` varchar(255) DEFAULT NULL,
  `avatar_content_type` varchar(255) DEFAULT NULL,
  `avatar_file_size` int(11) DEFAULT NULL,
  `avatar_updated_at` datetime DEFAULT NULL,
  `browser_notifications_enabled` tinyint(1) DEFAULT NULL,
  `profile_setup_completed_at` datetime DEFAULT NULL,
  `profile_setup_skip_count` int(11) DEFAULT '0',
  `profile_setup_last_skipped_at` datetime DEFAULT NULL,
  `last_synced_at` datetime DEFAULT NULL,
  `activity_based_on_transition_in_cares` tinyint(1) NOT NULL DEFAULT '0',
  `county` varchar(255) DEFAULT NULL,
  `state` varchar(2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_users_on_email` (`email`),
  UNIQUE KEY `index_users_on_reset_password_token` (`reset_password_token`),
  UNIQUE KEY `index_users_on_unlock_token` (`unlock_token`),
  UNIQUE KEY `index_users_on_uuid` (`uuid`),
  UNIQUE KEY `index_users_on_otp_secret_key` (`otp_secret_key`),
  KEY `index_users_on_active` (`active`),
  KEY `index_users_on_created_at` (`created_at`),
  KEY `index_users_on_first_name` (`first_name`),
  KEY `index_users_on_last_name` (`last_name`),
  KEY `index_users_on_sermo_employee` (`sermo_employee`),
  KEY `index_users_on_user_type` (`user_type`),
  KEY `index_users_on_client_identifier` (`client_identifier`),
  KEY `users_master_group_staff_master_group_id_fk` (`master_group_staff_master_group_id`),
  KEY `index_users_on_last_synced_at` (`last_synced_at`) USING BTREE,
  FULLTEXT KEY `index_users_on_first_name_and_last_name` (`first_name`,`last_name`),
  CONSTRAINT `users_master_group_staff_master_group_id_fk` FOREIGN KEY (`master_group_staff_master_group_id`) REFERENCES `master_groups` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=38498 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
```

```
mysql> SET SESSION optimizer_switch="index_merge_intersection=on";
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT SQL_NO_CACHE count(`users`.`id`) FROM `users` WHERE `users`.`user_type` = 'master_group_staff' AND `users`.`master_group_staff_master_group_id` = 35;
+---------------------+
| count(`users`.`id`) |
+---------------------+
|                   0 |
+---------------------+
1 row in set (0.00 sec)

mysql> explain SELECT SQL_NO_CACHE count(`users`.`id`) FROM `users` WHERE `users`.`user_type` = 'master_group_staff' AND `users`.`master_group_staff_master_group_id` = 35;
+----+-------------+-------+-------------+----------------------------------------------------------------------+----------------------------------------------------------------------+---------+------+------+-----------------------------------------------------------------------------------------------------------------+
| id | select_type | table | type        | possible_keys                                                        | key                                                                  | key_len | ref  | rows | Extra                                                                                                           |
+----+-------------+-------+-------------+----------------------------------------------------------------------+----------------------------------------------------------------------+---------+------+------+-----------------------------------------------------------------------------------------------------------------+
|  1 | SIMPLE      | users | index_merge | index_users_on_user_type,users_master_group_staff_master_group_id_fk | users_master_group_staff_master_group_id_fk,index_users_on_user_type | 5,62    | NULL |    5 | Using intersect(users_master_group_staff_master_group_id_fk,index_users_on_user_type); Using where; Using index |
+----+-------------+-------+-------------+----------------------------------------------------------------------+----------------------------------------------------------------------+---------+------+------+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SET SESSION optimizer_switch="index_merge_intersection=off";
Query OK, 0 rows affected (0.00 sec)

mysql> explain SELECT SQL_NO_CACHE count(`users`.`id`) FROM `users` WHERE `users`.`user_type` = 'master_group_staff' AND `users`.`master_group_staff_master_group_id` = 35;
+----+-------------+-------+------+----------------------------------------------------------------------+---------------------------------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys                                                        | key                                         | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+----------------------------------------------------------------------+---------------------------------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | users | ref  | index_users_on_user_type,users_master_group_staff_master_group_id_fk | users_master_group_staff_master_group_id_fk | 5       | const |   38 | Using where |
+----+-------------+-------+------+----------------------------------------------------------------------+---------------------------------------------+---------+-------+------+-------------+
1 row in set (0.00 sec)
```

https://bugs.mysql.com/bug.php?id=80789 is probably the same bug but I can't open it.

https://bugs.mysql.com/bug.php?id=79675 doesn't appear to be related because there is no subquery.

How to repeat:
I can't figure out a way to repeat it. It happens randomly. If I restart mysql, it works for some period of time - weeks?. It can happen on master but the replica is fine or the opposite scenario.

Suggested fix:
Temporary fixes: 
Restarting mysql will fix it. 
`analyze table users` will fix it.
Disabling index_merge_intersection will fix it. 
 SET SESSION optimizer_switch="index_merge_intersection=on"
[7 Jun 2019 6:33] MySQL Verification Team 
Hello Kendal Miller,

Thank you for taking the time to report a problem.  Unfortunately you are not using a current version of the product you reported a problem with, 5.6.33 is very old version and many bugs fixed since then -- the problem might already be fixed. Please download a new version from http://www.mysql.com/downloads/

If you are able to reproduce the bug with one of the latest versions, please change the version on this bug report to the version you tested and change the status back to "Open", provide logical dump of the table(structure, subset of data which reproduces issue) to reproduce issue at our end.  Again, thank you for your continued support of MySQL.

Also we don't fix bugs in old versions, don't backport bug fixes, so need to check with latest version anyway. So, please, upgrade and inform us if problem still exists.

regards,
Umesh
[7 Jun 2019 12:47] Kendal Miller 
Do you consider 5.6.44 recent enough? I can install that version and report back if it happens again. We do plan on upgrading to 5.7 in the next month or so.

For now, I have disabled the optimization that breaks it.
in my.cnf
optimizer_switch=index_merge_intersection=off
[7 Jun 2019 13:09] MySQL Verification Team 
Hi,

Yes, 5.6.44 is the most recent release of the 5.6 version.

Let us know whether you experience the same problem with it.
[8 Jul 2019 1:00] Bugs System 
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".