Bug #81469 Slow performance for IN subquery and UNION

Description:
If I use IN subquery which contains UNION of 3 integers performance is slower than if I wrap same subquery in another select.

E.g.

select id from events where id in (select 60241922 union all select 60241923 union all select 60241924); - slow query

select id from events where id in (select * from (select 60241922 union all select 60241923 union all select 60241924) v); - fast query

How to repeat:
In 5.7.12:

flush status;
select now(6);
now(6)
2016-05-18 00:33:11.453692
select id from events where id in (select 60241922 union all select 60241923 union all select 60241924);
id
60241922
60241923
60241924
select now(6);
now(6)
2016-05-18 00:33:11.458543
show status like 'Handler%';
Variable_name	Value
Handler_commit	1
Handler_delete	0
Handler_discover	0
Handler_external_lock	2
Handler_mrr_init	0
Handler_prepare	0
Handler_read_first	1
Handler_read_key	1
Handler_read_last	0
Handler_read_next	510
Handler_read_prev	0
Handler_read_rnd	0
Handler_read_rnd_next	0
Handler_rollback	0
Handler_savepoint	0
Handler_savepoint_rollback	0
Handler_update	0
Handler_write	0
flush status;
select now(6);
now(6)
2016-05-18 00:33:11.459594
select id from events where id in (select * from (select 60241922 union all select 60241923 union all select 60241924) v);
id
60241922
60241923
60241924
select now(6);
now(6)
2016-05-18 00:33:11.460421
show status like 'Handler%';
Variable_name	Value
Handler_commit	1
Handler_delete	0
Handler_discover	0
Handler_external_lock	2
Handler_mrr_init	0
Handler_prepare	0
Handler_read_first	0
Handler_read_key	3
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_rnd	0
Handler_read_rnd_next	8
Handler_rollback	0
Handler_savepoint	0
Handler_savepoint_rollback	0
Handler_update	0
Handler_write	6

Full test case will be attached soon.

Suggested fix:
Optimize first query same way as second one

Test case for MTR

Attachment: bug81469.test (application/octet-stream, text), 71.96 KiB.

Percona bug report: https://bugs.launchpad.net/percona-server/+bug/1582919

Hello Sveta,

Thank you for the report and test case.
Verified as described with 5.6.30 build.

Thanks,
Umesh