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)

3 files changed, 111 insertions, 1 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;
diff --git a/mysql-test/t/partition_pruning.test b/mysql-test/t/partition_pruning.test
index 1c8a4d254a8..69f159e8142 100644
--- a/mysql-test/t/partition_pruning.test
+++ b/mysql-test/t/partition_pruning.test
@@ -1413,3 +1413,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;
+
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index aee6905c5a6..e6ae3b05c1d 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1140,7 +1140,8 @@ JOIN::optimize()
         part of the nested outer join, and we can't do partition pruning
         (TODO: check if this limitation can be lifted)
       */
-      if (!tbl->embedding)
+      if (!tbl->embedding ||
+          (tbl->embedding && tbl->embedding->sj_on_expr))
       {
         Item *prune_cond= tbl->on_expr? tbl->on_expr : conds;
         tbl->table->no_partitions_used= prune_partitions(thd, tbl->table,