| 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: | |
| Category: | MySQL Server: Partitions | Severity: | S3 (Non-critical) |
| Version: | 8.0.29, 8.0.39, 8.4.2, 9.0.1 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[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?
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 &&