Bug #107791 Left join with derived partitioned table returns wrong result
Submitted: 7 Jul 2022 7:01 Modified: 7 Jul 2022 7:45
Reporter: Diancheng Wang Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:8.0.29, 5.7.38 OS:Any
Assigned to: CPU Architecture:Any

[7 Jul 2022 7:01] Diancheng Wang
Description:
The query should return empty but it got some results. The query's inner table is a derived table with a partition table.

How to repeat:
create table t1(a int);
CREATE TABLE t2(a int primary key) partition by list(a) (partition p1 values in(1));
insert into t1 values(1);
insert into t2 values(1);
select * from t1 x left join (select a from t2) y on (x.a = y.a) where y.a is null;

Suggested fix:
diff --git a/sql/sql_resolver.cc b/sql/sql_resolver.cc
index 45be337df22..f29821c7fca 100644
--- a/sql/sql_resolver.cc
+++ b/sql/sql_resolver.cc
@@ -790,8 +790,11 @@ bool Query_block::apply_local_transforms(THD *thd, bool prune) {
         This will only prune constant conditions, which will be used for
         lock pruning.
       */
+      Item *prune_cond = tbl->join_cond();
+      if (!prune_cond && !tbl->is_inner_table_of_outer_join())
+        prune_cond = m_where_cond;
       if (prune_partitions(thd, tbl->table, this,
-                           tbl->join_cond() ? tbl->join_cond() : m_where_cond))
+                           prune_cond))
         return true; /* purecov: inspected */

       if (tbl->table->all_partitions_pruned_away &&
[7 Jul 2022 7:45] MySQL Verification Team
Hello Diancheng Wang,

Thank you for the report and feedback.

regards,
Umesh
[7 Jul 2022 7:46] MySQL Verification Team
In order to submit contributions you must first sign the Oracle Contribution Agreement (OCA).For additional information please check https://oca.opensource.oracle.com
If you have any questions, please contact the MySQL community team - https://dev.mysql.com/community/
[18 Jul 2022 9:11] meng jia liang
When will the official fix be available?