MDEV-6239: Partition pruning is not working as expected in an inner query

- Make partition pruning work for tables inside semi-join nests
  (the new condition is the same that range optimizer uses so 
   it should be ok)

1 files changed, 52 insertions, 0 deletions

diff --git a/mysql-test/r/partition_pruning.result b/mysql-test/r/partition_pruning.result
index ec7fd798d4c..0a4cf9932c0 100644
--- a/mysql-test/r/partition_pruning.result
+++ b/mysql-test/r/partition_pruning.result
@@ -3301,3 +3301,55 @@ explain partitions select * from t1 where a between 10 and 10+33;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	p0,p1,p2	ALL	NULL	NULL	NULL	NULL	100	Using where
 drop table t0, t1;
+#
+# MDEV-6239: Partition pruning is not working as expected in an inner query
+#
+create table t1
+(
+company_id bigint(5),
+dept_id bigint(5),
+emp_id bigint(5),
+emp_name varchar(100),
+primary key (company_id, emp_id)
+) partition by list (company_id) (
+partition p_1000 values in (1000),
+partition p_2000 values in (2000),
+partition p_3000 values in (3000)
+);
+create table t2
+(
+company_id bigint(5),
+dept_id bigint(5),
+dept_name varchar(100),
+primary key (company_id, dept_id)
+) partition by list (company_id) (
+partition p_1000 values in (1000),
+partition p_2000 values in (2000),
+partition p_3000 values in (3000)
+);
+insert into t2 values 
+(1000, 10, 'Engineering'),
+(1000, 20, 'Product Management'),
+(1000, 30, 'QA'),
+(2000, 40, 'Support'),
+(2000, 50, 'Professional Services');
+insert into t1 values 
+(1000, 10, 1, 'John'),
+(1000, 10, 2, 'Smith'),
+(1000, 20, 3, 'Jacob'),
+(1000, 20, 4, 'Brian'),
+(1000, 30, 5, 'Chris'),
+(1000, 30, 6, 'Ryan'),
+(2000, 40, 7, 'Karin'),
+(2000, 40, 8, 'Jay'),
+(2000, 50, 9, 'Ana'),
+(2000, 50, 10, 'Jessica');
+# Table t2 should have only partition p_1000.
+explain partitions
+select * from t1
+where company_id = 1000
+and dept_id in (select dept_id from t2 where COMPANY_ID = 1000);
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	p_1000	ref	PRIMARY	PRIMARY	8	const	2	Using index
+1	PRIMARY	t1	p_1000	ALL	PRIMARY	NULL	NULL	NULL	6	Using where; Using join buffer (flat, BNL join)
+drop table t1,t2;