1 files changed, 57 insertions, 0 deletions
diff --git a/mysql-test/t/partition_pruning.test b/mysql-test/t/partition_pruning.test
index e7e764ce138..4c97bab454d 100644
--- a/mysql-test/t/partition_pruning.test
+++ b/mysql-test/t/partition_pruning.test
@@ -1412,3 +1412,60 @@ explain partitions select * from t1 where a between 10 and 13;
 explain partitions select * from t1 where a between 10 and 10+33;
 
 drop table t0, t1;
+
+--echo #
+--echo # MDEV-6239: Partition pruning is not working as expected in an inner query
+--echo #
+
+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');
+
+--echo # 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);
+
+drop table t1,t2;
+