MySQL Bugs: #99634: partition pruning not working with sub query

Bug #99634	partition pruning not working with sub query
Submitted:	19 May 2020 19:24	Modified:	21 May 2020 18:50
Reporter:	Sivanarayana Chekuri	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	8.0.19	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	partitioning, SUB QUERY

Description:
Partition pruning not working, it's scanning all partitions when i use sub query.
Below is the query ...
select * from s3apiappl.list_partition_test
where wfid in ( select wfid from s3apiappl.dnac_upload_status where party_id = '5769295');

s3apiappl.list_partition_test table,  partition defined on wfid.  Sub query returns below 5 partitions. and query search should limit to only these 5 partitions.
pWfid_1,pWfid_2,pWfid_3,pWfid_4,pWfid_5

But Actually it's doing all partition scan as per the explain plan. 

If i hard coded wfid values and passing it as a string in where clause, it's working fine. 
Below query working fine.   But i don't want to hard code these values, this list comes from master table.
explain select * from s3apiappl.list_partition_test 
		where wfid in ('658dda9b-8b07-4b73-88b2-b811b0fb3e3c'
					,'58a2045d-780e-4bdb-906c-dc17a7b376b5'
					,'b6505721-1a0e-460c-99fb-0d236c7c8a72'
					,'9fcf6730-ab2c-41e1-94bb-36099bbfea7b'
					,'7518595a-8a58-47c3-99a7-a3df87ac3f23' )
				and party_id = '5769295';

How to repeat:
1) create partition table.   
2) load some data in partition table.
3) select the partition table and use sub query in where clause to get the required data.
4) Verify the explain plan.  It's doing  ALL Partition scan.

Please provide the complete test case not just the description, the SQL script to create table, insert data, query, real result and expected result. Thanks in advance.

Thanks Miguel Solorzano for the response 

Following are the steps to reproduce this issue....

-- create party_id and wfid mapping table 
CREATE TABLE s3apiappl.partition_mapping_table (
  PARTY_ID VARCHAR(32), 
  wfid VARCHAR(64) 
);

-- sample data for party_id and wfid table. 
insert into s3apiappl.partition_mapping_table values ('100','wfid1');
insert into s3apiappl.partition_mapping_table values ('100','wfid2');
insert into s3apiappl.partition_mapping_table values ('100','wfid3');
insert into s3apiappl.partition_mapping_table values ('200','wfid4');
insert into s3apiappl.partition_mapping_table values ('200','wfid5');
insert into s3apiappl.partition_mapping_table values ('200','wfid6');
insert into s3apiappl.partition_mapping_table values ('300','wfid7');
insert into s3apiappl.partition_mapping_table values ('300','wfid8');
commit; 
 
 
 -- create transaction table which will have diff inventory's for each wfid. 
 CREATE TABLE s3apiappl.list_partition_test (
  PARTY_ID VARCHAR(32), 
  wfid VARCHAR(64),
  inv VARCHAR(8) 
) 
PARTITION BY LIST COLUMNS( wfid) (
 PARTITION pWfid_1 VALUES IN( 'wfid1'),
 PARTITION pWfid_2 VALUES IN( 'wfid2'),
 PARTITION pWfid_3 VALUES IN( 'wfid3'),
 PARTITION pWfid_4 VALUES IN( 'wfid4'),
 PARTITION pWfid_5 VALUES IN( 'wfid5'),
 PARTITION pWfid_6 VALUES IN( 'wfid6'),
 PARTITION pWfid_7 VALUES IN( 'wfid7'),
  PARTITION pWfid_8 VALUES IN( 'wfid8')
);

-- create sample data set 
insert into s3apiappl.list_partition_test values ('100','wfid1','inv1');
insert into s3apiappl.list_partition_test values ('100','wfid1','inv2');
insert into s3apiappl.list_partition_test values ('100','wfid1','inv3');
insert into s3apiappl.list_partition_test values ('100','wfid1','inv4');
insert into s3apiappl.list_partition_test values ('100','wfid2','inv1');
insert into s3apiappl.list_partition_test values ('100','wfid3','inv1');
insert into s3apiappl.list_partition_test values ('200','wfid4','inv1');
insert into s3apiappl.list_partition_test values ('200','wfid5','inv1');
insert into s3apiappl.list_partition_test values ('200','wfid6','inv1');
insert into s3apiappl.list_partition_test values ('300','wfid7','inv1');
insert into s3apiappl.list_partition_test values ('300','wfid8','inv1');

commit;

-- Sub query, which is not working partition pruning., doing ALL Partition scan. 
select * from s3apiappl.list_partition_test 
where wfid in (select wfid from s3apiappl.partition_mapping_table where party_id = 100);

-- showing All partition scan. It should limit to only 3 partitions(pWfid_1,pWfid_2,pWfid_3) scan.
explain select * from s3apiappl.list_partition_test 
where wfid in (select wfid from s3apiappl.partition_mapping_table where party_id = 100);

-- Tried with joing two tables, instead sub query.  Joing also showing all partition scan.
explain select * 
from s3apiappl.list_partition_test a,
	s3apiappl.partition_mapping_table b
where a.party_id = b.party_id 
	and a.wfid = b.wfid 
    and b.party_id = 100;

Hi Mr. Chekuri,

Thank you for your bug report.

However, I do not think that this is a bug.

There are several reasons of why pruning would not work, of which I will mention only a couple of those.

Since you do not have any suitable index, how can pruning function ???

Next, if even you have all suitable indices, pruning would not function, because for the number of rows smaller than one or two hundreds, it is MUCH faster to scan them all, instead of doing index search or pruning.

This is all explained in our Reference Manual, chapter on Optimisation.

Not a bug.

Actual table will have more than  One billion records.  
Since i can't add all the data here, i given sample data.  

I tried with adding  index on both the tables and Analyze the table, still it's not showing partition pruning. 

This problem i can see with only sub query's and  joining with other tables.

If i use partition key in where clause and passing exact values as comma separated, it's working fine. 
ex..
select * from s3apiappl.list_partition_test 
where wfid in ('wfid1','wfid2','wfid3','wfid4') and party_id = 100;   -- working fine. 

issue with only getting wfid in subquery or joining with other table.
ex...
select * from s3apiappl.list_partition_test 
where wfid in (select wfid from s3apiappl.partition_mapping_table where party_id = 100);

or 
select * 
from s3apiappl.list_partition_test a,
	s3apiappl.partition_mapping_table b
where a.party_id = b.party_id 
	and a.wfid = b.wfid 
    and b.party_id = 100;

Our analysis still stands, regarding index.

Also, there are queries that can't use indices, like for some tables in outer joins, semi joins or in dependent nested queries.

This is all described in our Reference Manual.