MySQL Bugs: #75121: IN clause subquery optimization

Bug #75121	IN clause subquery optimization
Submitted:	5 Dec 2014 18:25	Modified:	5 Dec 2014 21:42
Reporter:	Raghbir Banwait	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.6.21	OS:	Linux (CentOS 5.11)
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
IN clause optimization returns different results if enforced INDEX use.

How to repeat:

CREATE TABLE `a` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'The id',
  `f` char(1) NOT NULL DEFAULT 'A' COMMENT 'Flag',
  PRIMARY KEY (`id`),
  KEY `IDX_A_F` (`f`)
) ENGINE=InnoDB AUTO_INCREMENT=368305 DEFAULT CHARSET=utf8 COMMENT='Table a' ;

CREATE TABLE `b` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
  `a_id` int(10) unsigned DEFAULT NULL,
  `ts` datetime NOT NULL COMMENT 'Timestamp',
  `f` char(1) NOT NULL COMMENT 'Flag',
  PRIMARY KEY (`id`),
  KEY `B_FK` (`a_id`),
  KEY `IDX_B_F` (`f`),
  CONSTRAINT `B_FK` FOREIGN KEY (`a_id`) REFERENCES `a` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=40157 DEFAULT CHARSET=utf8 COMMENT='Table b' ;

INSERT INTO a(id,f) VALUES (1, 'A') ;
INSERT INTO a(id,f) VALUES (2, 'B') ;
INSERT INTO a(id,f) VALUES (3, 'A') ;
INSERT INTO a(id,f) VALUES (4, 'B') ;
INSERT INTO a(id,f) VALUES (5, 'A') ;
INSERT INTO a(id,f) VALUES (6, 'B') ;
INSERT INTO a(id,f) VALUES (7, 'A') ;
INSERT INTO a(id,f) VALUES (8, 'B') ;
INSERT INTO a(id,f) VALUES (9, 'A') ;
INSERT INTO a(id,f) VALUES (10, 'B') ;
INSERT INTO a(id,f) VALUES (11, 'A') ;
INSERT INTO a(id,f) VALUES (12, 'B') ;
INSERT INTO a(id,f) VALUES (13, 'A') ;
INSERT INTO a(id,f) VALUES (14, 'B') ;
INSERT INTO a(id,f) VALUES (15, 'A') ;
INSERT INTO a(id,f) VALUES (16, 'B') ;
INSERT INTO a(id,f) VALUES (17, 'A') ;
INSERT INTO a(id,f) VALUES (18, 'B') ;
INSERT INTO a(id,f) VALUES (19, 'A') ;
INSERT INTO a(id,f) VALUES (20, 'B') ;
INSERT INTO a(id,f) VALUES (21, 'A') ;
INSERT INTO a(id,f) VALUES (22, 'B') ;
INSERT INTO a(id,f) VALUES (23, 'A') ;
INSERT INTO a(id,f) VALUES (24, 'B') ;
INSERT INTO a(id,f) VALUES (25, 'A') ;
INSERT INTO a(id,f) VALUES (26, 'B') ;
INSERT INTO a(id,f) VALUES (27, 'A') ;
INSERT INTO a(id,f) VALUES (28, 'B') ;
INSERT INTO a(id,f) VALUES (29, 'A') ;
INSERT INTO a(id,f) VALUES (30, 'B') ;
-- INSERTs ordered by ts DESC
INSERT INTO b(a_id,ts,f) VALUES (30,DATE(SYSDATE() - INTERVAL 100 DAY),'A') ;
INSERT INTO b(a_id,ts,f) VALUES (20,DATE(SYSDATE() - INTERVAL 100 DAY),'A') ;
INSERT INTO b(a_id,ts,f) VALUES (19,DATE(SYSDATE() - INTERVAL 99 DAY),'B') ;
INSERT INTO b(a_id,ts,f) VALUES (29,DATE(SYSDATE() - INTERVAL 99 DAY),'B') ;
INSERT INTO b(a_id,ts,f) VALUES (28,DATE(SYSDATE() - INTERVAL 98 DAY),'B') ;
INSERT INTO b(a_id,ts,f) VALUES (18,DATE(SYSDATE() - INTERVAL 98 DAY),'B') ;
INSERT INTO b(a_id,ts,f) VALUES (17,DATE(SYSDATE() - INTERVAL 97 DAY),'A') ;
INSERT INTO b(a_id,ts,f) VALUES (27,DATE(SYSDATE() - INTERVAL 97 DAY),'A') ;
INSERT INTO b(a_id,ts,f) VALUES (16,DATE(SYSDATE() - INTERVAL 96 DAY),'B') ;
INSERT INTO b(a_id,ts,f) VALUES (26,DATE(SYSDATE() - INTERVAL 96 DAY),'B') ;
INSERT INTO b(a_id,ts,f) VALUES (15,DATE(SYSDATE() - INTERVAL 95 DAY),'A') ;
INSERT INTO b(a_id,ts,f) VALUES (25,DATE(SYSDATE() - INTERVAL 95 DAY),'A') ;
INSERT INTO b(a_id,ts,f) VALUES (18,DATE(SYSDATE() - INTERVAL 58 DAY),'A') ;
INSERT INTO b(a_id,ts,f) VALUES (28,DATE(SYSDATE() - INTERVAL 58 DAY),'A') ;
INSERT INTO b(a_id,ts,f) VALUES (17,DATE(SYSDATE() - INTERVAL 57 DAY),'B') ;
INSERT INTO b(a_id,ts,f) VALUES (27,DATE(SYSDATE() - INTERVAL 57 DAY),'B') ;
INSERT INTO b(a_id,ts,f) VALUES (16,DATE(SYSDATE() - INTERVAL 56 DAY),'A') ;
INSERT INTO b(a_id,ts,f) VALUES (26,DATE(SYSDATE() - INTERVAL 56 DAY),'A') ;
INSERT INTO b(a_id,ts,f) VALUES (15,DATE(SYSDATE() - INTERVAL 55 DAY),'B') ;
INSERT INTO b(a_id,ts,f) VALUES (25,DATE(SYSDATE() - INTERVAL 55 DAY),'B') ;
INSERT INTO b(a_id,ts,f) VALUES (24,DATE(SYSDATE() - INTERVAL 54 DAY),'A') ;
INSERT INTO b(a_id,ts,f) VALUES (14,DATE(SYSDATE() - INTERVAL 54 DAY),'A') ;
INSERT INTO b(a_id,ts,f) VALUES (23,DATE(SYSDATE() - INTERVAL 53 DAY),'B') ;
INSERT INTO b(a_id,ts,f) VALUES (13,DATE(SYSDATE() - INTERVAL 53 DAY),'B') ;
INSERT INTO b(a_id,ts,f) VALUES (22,DATE(SYSDATE() - INTERVAL 52 DAY),'B') ;
INSERT INTO b(a_id,ts,f) VALUES (12,DATE(SYSDATE() - INTERVAL 52 DAY),'B') ;
INSERT INTO b(a_id,ts,f) VALUES (21,DATE(SYSDATE() - INTERVAL 51 DAY),'A') ;
INSERT INTO b(a_id,ts,f) VALUES (11,DATE(SYSDATE() - INTERVAL 51 DAY),'A') ;
INSERT INTO b(a_id,ts,f) VALUES (30,DATE(SYSDATE() - INTERVAL 50 DAY),'B') ;
INSERT INTO b(a_id,ts,f) VALUES (20,DATE(SYSDATE() - INTERVAL 50 DAY),'B') ;
INSERT INTO b(a_id,ts,f) VALUES (10,DATE(SYSDATE() - INTERVAL 50 DAY),'B') ;
INSERT INTO b(a_id,ts,f) VALUES (19,DATE(SYSDATE() - INTERVAL 49 DAY),'A') ;
INSERT INTO b(a_id,ts,f) VALUES ( 9,DATE(SYSDATE() - INTERVAL 49 DAY),'A') ;
INSERT INTO b(a_id,ts,f) VALUES (29,DATE(SYSDATE() - INTERVAL 49 DAY),'A') ;
INSERT INTO b(a_id,ts,f) VALUES ( 6,DATE(SYSDATE() - INTERVAL 46 DAY),'A') ;
INSERT INTO b(a_id,ts,f) VALUES ( 5,DATE(SYSDATE() - INTERVAL 45 DAY),'B') ;
INSERT INTO b(a_id,ts,f) VALUES ( 8,DATE(SYSDATE() - INTERVAL 28 DAY),'A') ;
INSERT INTO b(a_id,ts,f) VALUES ( 7,DATE(SYSDATE() - INTERVAL 27 DAY),'B') ;
INSERT INTO b(a_id,ts,f) VALUES (17,DATE(SYSDATE() - INTERVAL 17 DAY),'A') ;
INSERT INTO b(a_id,ts,f) VALUES (27,DATE(SYSDATE() - INTERVAL 17 DAY),'A') ;
INSERT INTO b(a_id,ts,f) VALUES (26,DATE(SYSDATE() - INTERVAL 16 DAY),'B') ;
INSERT INTO b(a_id,ts,f) VALUES (16,DATE(SYSDATE() - INTERVAL 16 DAY),'B') ;
INSERT INTO b(a_id,ts,f) VALUES (15,DATE(SYSDATE() - INTERVAL 15 DAY),'A') ;
INSERT INTO b(a_id,ts,f) VALUES (25,DATE(SYSDATE() - INTERVAL 15 DAY),'A') ;
INSERT INTO b(a_id,ts,f) VALUES (24,DATE(SYSDATE() - INTERVAL 14 DAY),'B') ;
INSERT INTO b(a_id,ts,f) VALUES (14,DATE(SYSDATE() - INTERVAL 14 DAY),'B') ;
INSERT INTO b(a_id,ts,f) VALUES (13,DATE(SYSDATE() - INTERVAL 13 DAY),'A') ;
INSERT INTO b(a_id,ts,f) VALUES (23,DATE(SYSDATE() - INTERVAL 13 DAY),'A') ;
INSERT INTO b(a_id,ts,f) VALUES (28,DATE(SYSDATE() - INTERVAL  8 DAY),'B') ;
INSERT INTO b(a_id,ts,f) VALUES ( 8,DATE(SYSDATE() - INTERVAL  8 DAY),'B') ;
INSERT INTO b(a_id,ts,f) VALUES (18,DATE(SYSDATE() - INTERVAL  8 DAY),'B') ;
INSERT INTO b(a_id,ts,f) VALUES ( 7,DATE(SYSDATE() - INTERVAL  7 DAY),'A') ;
INSERT INTO b(a_id,ts,f) VALUES ( 6,DATE(SYSDATE() - INTERVAL  6 DAY),'B') ;
INSERT INTO b(a_id,ts,f) VALUES ( 5,DATE(SYSDATE() - INTERVAL  5 DAY),'A') ;
INSERT INTO b(a_id,ts,f) VALUES ( 4,DATE(SYSDATE() - INTERVAL  4 DAY),'B') ;
INSERT INTO b(a_id,ts,f) VALUES ( 3,DATE(SYSDATE() - INTERVAL  3 DAY),'A') ;

SELECT * FROM a WHERE a.f = 'B' AND a.id IN (SELECT a_id FROM b WHERE b.f = 'B' AND DATE(ts) > (DATE(SYSDATE()) - INTERVAL 30 DAY));

SELECT * FROM a WHERE a.f = 'B' AND EXISTS (SELECT a_id FROM b WHERE b.f = 'B' AND DATE(ts) > (DATE(SYSDATE()) - INTERVAL 30 DAY) AND b.a_id = a.id);

SELECT * FROM a USE INDEX (PRIMARY) WHERE a.f = 'B' AND a.id IN (SELECT a_id FROM b WHERE b.f = 'B' AND DATE(ts) > (DATE(SYSDATE()) - INTERVAL 30 DAY));

SELECT * FROM a USE INDEX (IDX_A_F) WHERE a.f = 'B' AND EXISTS (SELECT a_id FROM b WHERE b.f = 'B' AND DATE(ts) > (DATE(SYSDATE()) - INTERVAL 30 DAY) AND b.a_id = a.id);

Last four queries are effectively the same but they return different results. In our production situation, the first two returned different number of rows.

mysql 5.6 > SELECT * FROM a WHERE a.f = 'B' AND a.id IN (SELECT a_id FROM b WHERE b.f = 'B' AND DATE(ts) > (DATE(SYSDATE()) - INTERVAL 30 DAY));
+----+---+
| id | f |
+----+---+
|  4 | B |
|  6 | B |
|  8 | B |
| 14 | B |
| 16 | B |
| 18 | B |
| 24 | B |
| 26 | B |
| 28 | B |
+----+---+
9 rows in set (0.05 sec)

mysql 5.6 >
mysql 5.6 > SELECT * FROM a WHERE a.f = 'B' AND EXISTS (SELECT a_id FROM b WHERE b.f = 'B' AND DATE(ts) > (DATE(SYSDATE()) - INTERVAL 30 DAY) AND b.a_id = a.id);
+----+---+
| id | f |
+----+---+
|  4 | B |
|  6 | B |
|  8 | B |
| 14 | B |
| 16 | B |
| 18 | B |
| 24 | B |
| 26 | B |
| 28 | B |
+----+---+
9 rows in set (0.00 sec)

mysql 5.6 >
mysql 5.6 > SELECT * FROM a USE INDEX (PRIMARY) WHERE a.f = 'B' AND a.id IN (SELECT a_id FROM b WHERE b.f = 'B' AND DATE(ts) > (DATE(SYSDATE()) - INTERVAL 30 DAY));
+----+---+
| id | f |
+----+---+
|  4 | B |
|  6 | B |
|  8 | B |
| 10 | B |
| 12 | B |
| 14 | B |
| 16 | B |
| 18 | B |
| 20 | B |
| 22 | B |
| 24 | B |
| 26 | B |
| 28 | B |
| 30 | B |
+----+---+
14 rows in set (0.00 sec)

mysql 5.6 >
mysql 5.6 > SELECT * FROM a USE INDEX (IDX_A_F) WHERE a.f = 'B' AND EXISTS (SELECT a_id FROM b WHERE b.f = 'B' AND DATE(ts) > (DATE(SYSDATE()) - INTERVAL 30 DAY) AND b.a_id = a.id);
+----+---+
| id | f |
+----+---+
|  4 | B |
|  6 | B |
|  8 | B |
| 14 | B |
| 16 | B |
| 18 | B |
| 24 | B |
| 26 | B |
| 28 | B |
+----+---+
9 rows in set (0.00 sec)

mysql 5.6 > SHOW VARIABLES LIKE "%VERSION%";
+-------------------------+--------------------------------+
| Variable_name           | Value                          |
+-------------------------+--------------------------------+
| innodb_version          | 5.6.23                         |
| protocol_version        | 10                             |
| slave_type_conversions  |                                |
| version                 | 5.6.23                         |
| version_comment         | Source distribution 2014.12.02 |
| version_compile_machine | x86_64                         |
| version_compile_os      | Win64                          |
+-------------------------+--------------------------------+
7 rows in set (0.00 sec)

*************************************************5.0************************************************************
mysql 5.0 > SELECT * FROM a WHERE a.f = 'B' AND a.id IN (SELECT a_id FROM b WHERE b.f = 'B' AND DATE(ts) > (DATE(SYSDATE()) - INTERVAL 30 DAY));
+----+---+
| id | f |
+----+---+
|  4 | B |
|  6 | B |
|  8 | B |
| 14 | B |
| 16 | B |
| 18 | B |
| 24 | B |
| 26 | B |
| 28 | B |
+----+---+
9 rows in set (0.00 sec)

mysql 5.0 >
mysql 5.0 > SELECT * FROM a WHERE a.f = 'B' AND EXISTS (SELECT a_id FROM b WHERE b.f = 'B' AND DATE(ts) > (DATE(SYSDATE()) - INTERVAL 30 DAY) AND b.a_id = a.id);
+----+---+
| id | f |
+----+---+
|  4 | B |
|  6 | B |
|  8 | B |
| 14 | B |
| 16 | B |
| 18 | B |
| 24 | B |
| 26 | B |
| 28 | B |
+----+---+
9 rows in set (0.00 sec)

mysql 5.0 >
mysql 5.0 > SELECT * FROM a USE INDEX (PRIMARY) WHERE a.f = 'B' AND a.id IN (SELECT a_id FROM b WHERE b.f = 'B' AND DATE(ts) > (DATE(SYSDATE()) - INTERVAL 30 DAY));
+----+---+
| id | f |
+----+---+
|  4 | B |
|  6 | B |
|  8 | B |
| 14 | B |
| 16 | B |
| 18 | B |
| 24 | B |
| 26 | B |
| 28 | B |
+----+---+
9 rows in set (0.00 sec)

mysql 5.0 >
mysql 5.0 > SELECT * FROM a USE INDEX (IDX_A_F) WHERE a.f = 'B' AND EXISTS (SELECT a_id FROM b WHERE b.f = 'B' AND DATE(ts) > (DATE(SYSDATE()) - INTERVAL 30 DAY) AND b.a_id = a.id);
+----+---+
| id | f |
+----+---+
|  4 | B |
|  6 | B |
|  8 | B |
| 14 | B |
| 16 | B |
| 18 | B |
| 24 | B |
| 26 | B |
| 28 | B |
+----+---+
9 rows in set (0.00 sec)

mysql 5.0 > SHOW VARIABLES LIKE "%VERSION%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| protocol_version        | 10                  |
| version                 | 5.0.97-Win X64      |
| version_comment         | Source distribution |
| version_compile_machine | unknown             |
| version_compile_os      | Win64               |
+-------------------------+---------------------+
5 rows in set (0.00 sec)

mysql 5.0 >

mysql 5.5 > SELECT * FROM a WHERE a.f = 'B' AND a.id IN (SELECT a_id FROM b WHERE b.f = 'B' AND DATE(ts) > (DATE(SYSDATE()) - INTERVAL 30 DAY));
+----+---+
| id | f |
+----+---+
|  4 | B |
|  6 | B |
|  8 | B |
| 14 | B |
| 16 | B |
| 18 | B |
| 24 | B |
| 26 | B |
| 28 | B |
+----+---+
9 rows in set (0.01 sec)

mysql 5.5 >
mysql 5.5 > SELECT * FROM a WHERE a.f = 'B' AND EXISTS (SELECT a_id FROM b WHERE b.f = 'B' AND DATE(ts) > (DATE(SYSDATE()) - INTERVAL 30 DAY) AND b.a_id = a.id);
+----+---+
| id | f |
+----+---+
|  4 | B |
|  6 | B |
|  8 | B |
| 14 | B |
| 16 | B |
| 18 | B |
| 24 | B |
| 26 | B |
| 28 | B |
+----+---+
9 rows in set (0.00 sec)

mysql 5.5 >
mysql 5.5 > SELECT * FROM a USE INDEX (PRIMARY) WHERE a.f = 'B' AND a.id IN (SELECT a_id FROM b WHERE b.f = 'B' AND DATE(ts) > (DATE(SYSDATE()) - INTERVAL 30 DAY));
+----+---+
| id | f |
+----+---+
|  4 | B |
|  6 | B |
|  8 | B |
| 14 | B |
| 16 | B |
| 18 | B |
| 24 | B |
| 26 | B |
| 28 | B |
+----+---+
9 rows in set (0.00 sec)

mysql 5.5 >
mysql 5.5 > SELECT * FROM a USE INDEX (IDX_A_F) WHERE a.f = 'B' AND EXISTS (SELECT a_id FROM b WHERE b.f = 'B' AND DATE(ts) > (DATE(SYSDATE()) - INTERVAL 30 DAY) AND b.a_id = a.id);
+----+---+
| id | f |
+----+---+
|  4 | B |
|  6 | B |
|  8 | B |
| 14 | B |
| 16 | B |
| 18 | B |
| 24 | B |
| 26 | B |
| 28 | B |
+----+---+
9 rows in set (0.02 sec)

mysql 5.5 > SHOW VARIABLES LIKE "%VERSION%";
+-------------------------+--------------------------------+
| Variable_name           | Value                          |
+-------------------------+--------------------------------+
| innodb_version          | 5.5.42                         |
| protocol_version        | 10                             |
| slave_type_conversions  |                                |
| version                 | 5.5.42                         |
| version_comment         | Source distribution 2014.12.02 |
| version_compile_machine | AMD64                          |
| version_compile_os      | Win64                          |
+-------------------------+--------------------------------+
7 rows in set (0.00 sec)

Thank you for the bug report. Only 5.6 server affected.

mysql 5.7 > SELECT * FROM a WHERE a.f = 'B' AND a.id IN (SELECT a_id FROM b WHERE b.f = 'B' AND DATE(ts) > (DATE(SYSDATE()) - INTERVAL 30 DAY));
+----+---+
| id | f |
+----+---+
|  4 | B |
|  6 | B |
|  8 | B |
| 14 | B |
| 16 | B |
| 18 | B |
| 24 | B |
| 26 | B |
| 28 | B |
+----+---+
9 rows in set (0.01 sec)

mysql 5.7 >
mysql 5.7 > SELECT * FROM a WHERE a.f = 'B' AND EXISTS (SELECT a_id FROM b WHERE b.f = 'B' AND DATE(ts) > (DATE(SYSDATE()) - INTERVAL 30 DAY) AND b.a_id = a.id);
+----+---+
| id | f |
+----+---+
|  4 | B |
|  6 | B |
|  8 | B |
| 14 | B |
| 16 | B |
| 18 | B |
| 24 | B |
| 26 | B |
| 28 | B |
+----+---+
9 rows in set (0.00 sec)

mysql 5.7 >
mysql 5.7 > SELECT * FROM a USE INDEX (PRIMARY) WHERE a.f = 'B' AND a.id IN (SELECT a_id FROM b WHERE b.f = 'B' AND DATE(ts) > (DATE(SYSDATE()) - INTERVAL 30 DAY));
+----+---+
| id | f |
+----+---+
|  4 | B |
|  6 | B |
|  8 | B |
| 14 | B |
| 16 | B |
| 18 | B |
| 24 | B |
| 26 | B |
| 28 | B |
+----+---+
9 rows in set (0.00 sec)

mysql 5.7 >
mysql 5.7 > SELECT * FROM a USE INDEX (IDX_A_F) WHERE a.f = 'B' AND EXISTS (SELECT a_id FROM b WHERE b.f = 'B' AND DATE(ts) > (DATE(SYSDATE()) - INTERVAL 30 DAY) AND b.a_id = a.id);
+----+---+
| id | f |
+----+---+
|  4 | B |
|  6 | B |
|  8 | B |
| 14 | B |
| 16 | B |
| 18 | B |
| 24 | B |
| 26 | B |
| 28 | B |
+----+---+
9 rows in set (0.00 sec)

mysql 5.7 > SHOW VARIABLES LIKE "%VERSION%";
+-------------------------+--------------------------------+
| Variable_name           | Value                          |
+-------------------------+--------------------------------+
| innodb_version          | 5.7.6                          |
| protocol_version        | 10                             |
| slave_type_conversions  |                                |
| version                 | 5.7.6-m16                      |
| version_comment         | Source distribution 2014.12.02 |
| version_compile_machine | x86_64                         |
| version_compile_os      | Win64                          |
+-------------------------+--------------------------------+
7 rows in set (0.00 sec)

Query that gives wrong result uses semijoin materialization:

mysql> EXPLAIN SELECT * FROM a USE INDEX (PRIMARY) WHERE a.f = 'B' AND a.id IN (SELECT a_id FROM b WHERE b.f = 'B' AND DATE(ts) > (DATE(SYSDATE()) - INTERVAL 30 DAY));
+----+--------------+-------------+--------+---------------+------------+---------+------------------+------+-----------------------+
| id | select_type  | table       | type   | possible_keys | key        | key_len | ref              | rows | Extra                 |
+----+--------------+-------------+--------+---------------+------------+---------+------------------+------+-----------------------+
|  1 | SIMPLE       | a           | ALL    | PRIMARY       | NULL       | NULL    | NULL             |   30 | Using where           |
|  1 | SIMPLE       | <subquery2> | eq_ref | <auto_key>    | <auto_key> | 5       | bug20164684.a.id |    1 | NULL                  |
|  2 | MATERIALIZED | b           | ref    | B_FK,IDX_B_F  | IDX_B_F    | 3       | const            |   28 | Using index condition |
+----+--------------+-------------+--------+---------------+------------+---------+------------------+------+-----------------------+
3 rows in set (0.00 sec)

After turning off materialization, query gives the correct result:

mysql> set optimizer_switch='materialization=off';
Query OK, 0 rows affected (0.00 sec)

mysql> EXPLAIN SELECT * FROM a USE INDEX (PRIMARY) WHERE a.f = 'B' AND a.id IN (SELECT a_id FROM b WHERE b.f = 'B' AND DATE(ts) > (DATE(SYSDATE()) - INTERVAL 30 DAY));
+----+-------------+-------+------+---------------+------+---------+------------------+------+----------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref              | rows | Extra                      |
+----+-------------+-------+------+---------------+------+---------+------------------+------+----------------------------+
|  1 | SIMPLE      | a     | ALL  | PRIMARY       | NULL | NULL    | NULL             |   30 | Using where                |
|  1 | SIMPLE      | b     | ref  | B_FK,IDX_B_F  | B_FK | 5       | bug20164684.a.id |    1 | Using where; FirstMatch(a) |
+----+-------------+-------+------+---------------+------+---------+------------------+------+----------------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM a USE INDEX (PRIMARY) WHERE a.f = 'B' AND a.id IN (SELECT a_id FROM b WHERE b.f = 'B' AND DATE(ts) > (DATE(SYSDATE()) - INTERVAL 30 DAY));
+----+---+
| id | f |
+----+---+
|  4 | B |
|  6 | B |
|  8 | B |
| 14 | B |
| 16 | B |
| 18 | B |
| 24 | B |
| 26 | B |
| 28 | B |
+----+---+
9 rows in set (0.00 sec)