MySQL Bugs: #80789: "Select" query gives empty result depending on index or spacing in query

Bug #80789	"Select" query gives empty result depending on index or spacing in query
Submitted:	18 Mar 2016 10:22	Modified:	4 May 2016 11:50
Reporter:	Pavlo Kaidalov	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	5.6.27-29, 5.7.11	OS:	CentOS (x86_64)
Assigned to:		CPU Architecture:	Any
Tags:	analyze, cache, empty, intersect, query, result, SELECT, wrong

Description:
Simple "select" query gives wrong empty result depending on used index or even spacing between operators, and the only way to fix this - to run "analyze table". First time I got such behaviour on MySQL 5.6.27, then 28,29 and now on the current stable 5.7.11. Searching MySQL bugtracker I found only one report about this or very similar problem: http://bugs.mysql.com/bug.php?id=79675

I faced this bug 4 times already, here is examples:

1) mysql> select user_id from table1 where type_id=6;
+---------+
| user_id |
+---------+
|      -4 |
|      -4 |
...

mysql> select user_id from table1 where type_id=6 and user_id = -4;
Empty set (0.00 sec)

And the only way to fix this was to restart mysqld or run:

mysql> analyze table table1;
+--------------------------+---------+----------+----------+
| Table                    | Op      | Msg_type | Msg_text |
+--------------------------+---------+----------+----------+
| db.table1 | analyze | status   | OK       |
+--------------------------+---------+----------+----------+
1 row in set (0.57 sec)

after that everything correct:

mysql> select user_id from table1 where type_id=6 and user_id = -4;
+---------+
| user_id |
+---------+
|      -4 |
|      -4 |
...

explain shows that these 2 queries used different indexes: using 1 index in first case, and "Using intersect(fk_idx1,fk_idx2); Using where; Using index" when I got wrong empty result set.

2) SELECT * FROM table2 t2
WHERE t2.type_id = 2 AND t2.type = 1;

gives correctly result set

SELECT COUNT(1) FROM table2 t2
WHERE t2.type_id = 2 AND t2.type = 1;

gives 0.

And again "explain" shows "Using intersect(idx1,idx2);". Only after "analyze table table2;" I got number of result set entries.

3) right now I have 100% reproducible bug on next pair of queries:

mysql> SELECT table1.* FROM table1 WHERE table1.parent_id IS NULL AND table1.type_id = 322 AND EXISTS (SELECT * FROM table2 WHERE table1.type_id = table2.type_id AND table2.d_id = 34);
+-------+-----------+-----------------------------------------+----------------------+----------+-----------+-------+--------+------------+---------------------+----------------+------------------------+
| id    | parent_id | name                                    | is_discussion_thread | left_key | right_key | level | is_new | session_id | type_id | rating_type_id | is_attachments_allowed |
+-------+-----------+-----------------------------------------+----------------------+----------+-----------+-------+--------+------------+---------------------+----------------+------------------------+
| 11337 |      NULL | sometext |                    1 |     6453 |      6558 |     1 |      0 | NULL       |                 322 |           NULL |                      1 |                   0 |
+-------+-----------+-----------------------------------------+----------------------+----------+-----------+-------+--------+------------+---------------------+----------------+------------------------+
1 row in set (0.01 sec)

the id is 11337, but when I use the same query:

mysql> SELECT table1.* FROM table1 WHERE table1.id = 11337 AND EXISTS (SELECT * FROM table2 WHERE table1.type_id = table2.type_id AND table2.d_id = 34);
Empty set (0.00 sec)

Again both queries use index intersect:

+----+--------------------+----------------------------+------------+-------------+---------------------------------------------------------------------------+---------------------------------------------------------------------------+---------+-------+------+----------+----------------------------------------------------------------------------------------------------------------------+
| id | select_type        | table                      | partitions | type        | possible_keys                                                             | key                                                                       | key_len | ref   | rows | filtered | Extra                                                                                                                |
+----+--------------------+----------------------------+------------+-------------+---------------------------------------------------------------------------+---------------------------------------------------------------------------+---------+-------+------+----------+----------------------------------------------------------------------------------------------------------------------+
|  1 | PRIMARY            | table1   | NULL       | const       | PRIMARY                                                                   | PRIMARY                                                                   | 4       | const |    1 |   100.00 | NULL                                                                                                                 |
|  2 | DEPENDENT SUBQUERY | table2 | NULL       | index_merge | fk_idx1,fk_idx2 | fk_idx1,fk_idx2 | 4,4     | NULL  |    1 |   100.00 | Using intersect(fk_idx1,fk_idx2); Using where; Using index |
+----+--------------------+----------------------------+------------+-------------+---------------------------------------------------------------------------+---------------------------------------------------------------------------+---------+-------+------+----------+----------------------------------------------------------------------------------------------------------------------+

I tried several things with no success:

- set session query_cache_type=1; does not help
- using select SQL_NO_CACHE or SQL_CACHE does not help
- as suggested in Bug #79675, I tried to set "set session optimizer_switch='index_merge_intersection=off'" - id does not help
- using different database (dump with the almost the same data imported a week ago) from the same or any new session, e.g. "use db_20160310; select ..." - does not help, I still see the empty set.

And several things with success:
- using or forcing different index shows correct result, e.g.:

SELECT table1.* FROM table1 use index (PRIMARY) WHERE table1.id = 11337 AND EXISTS (SELECT * FROM table2 WHERE table1.observation_type_id = table2.observation_type_id AND table2.district_id = 34);

or

SELECT table1.* FROM table1 WHERE table1.id = 11337 AND EXISTS (SELECT * FROM table2 use index (PRIMARY) WHERE table1.type_id = table2.type_id AND table2.district_id = 34);

gives correct

+-------+-----------+-----------------------------------------+----------------------+----------+-----------+-------+--------+------------+---------------------+----------------+------------------------+
| id    | parent_id | name                                    | is_discussion_thread | left_key | right_key | level | is_new | session_id | type_id | rating_type_id | is_attachments_allowed |
+-------+-----------+-----------------------------------------+----------------------+----------+-----------+-------+--------+------------+---------------------+----------------+------------------------+
| 11337 |      NULL | sometext |                    1 |     6453 |      6558 |     1 |      0 | NULL       |                 322 |           NULL |                      1 |                   0 |
+-------+-----------+-----------------------------------------+----------------------+----------+-----------+-------+--------+------------+---------------------+----------------+------------------------+
1 row in set (0.01 sec)

- any query simplifying or changing any other way - gives correct result.

- and several minutes ago I have found that adding space/s to query fixes this, gives correct result, e.g.

mysql> SELECT table1.* FROM           table1 WHERE                    table1.id = 11337 AND EXISTS (SELECT * FROM table2 WHERE table1.type_id = table2.type_id AND table2.d_id = 34);

BUT REMOVING spaces back to original query STILL gives empty set.

I need help, this is very serious bug, but analyze table or mysql restart will fix this, I can't create any test environment.

How to repeat:
I can't reproduce this on any simple dataset, the same as http://bugs.mysql.com/bug.php?id=79675. After analyze table or restart mysqld I have correct results.

Thank you for the bug report. Please provide the test case of your example (create table, insert data, queries, actual result and expected one). Thanks.

It also affects me. The odd thing is when you run an affected statement, some times the result comes ok and in the majority of times, it comes wrong....

Really important issue...i had to downgrade my mysql instalation to 5.6.25....will not update mysql on production enviroment anymore....

Hi,

1. can we see a table structure for table1 ?

2. is query cache enabled on the affected server?
  2.1  if yes, does 'reset query cache; flush query cache;' fix problem ?

3. does 'flush tables;' also fix it?

i have downgraded to 5.6.25 and yet the problem keep occurring...so today (in dispear) i downgraded to 5.6.19 (my last know working version)

my config:

query_cache_limit = 1048576
query_cache_min_res_unit = 4096
query_cache_size = 1048576
query_cache_type = OFF
query_cache_wlock_invalidate = OFF

i think it is related to cache yes, because executing the query that generated the issue, 98% of times we got the wrong result, but in 2% the result is right.

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".

Same problem on a 5.7 server. 

The ANALYZE solve the problem. The answer is some thing like: empty set (0.00s) warning 25860. With a very important number of warning.

This arrive with a mysql client and the PhpPDO (PHP 7.1) driver, but not with a client Sql.

I'm seeing this in mysql 5.6.33

```
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)
```

Restarting mysql will fix it. `analyze table users` will fix it.
Disabling index_merge will fix it.

```
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)
```