From 8cb265752d25bdb6e61af249116d2d2a3d94057d Mon Sep 17 00:00:00 2001 From: davidYichengWei Date: Mon, 4 Mar 2024 12:28:02 -0500 Subject: [PATCH] Bug#112198 Incorrect multiple equality optimization causes performance degradation Problem: For a query like SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a AND t2.a>0; during the optimization stage, substitute_for_best_equal_field() will convert t2.a>0 to t1.a>0 as t1 has a higher priority in compare_fields_by_table_order(). This prevents the condition t2.a>0 (now t1.a>0) from being pushed down to t2 in function CreateHashJoinAccessPath(), as it now depends on t1 due to the substitution. Fix: Only perform the substitution if the condition is EQ_FUNC. Equality conditions can be pushed down normally as it takes a different optimization path. --- .../r/hash_join_predicate_pushdown.result | 19 +++++++++++++++++++ .../t/hash_join_predicate_pushdown.test | 13 +++++++++++++ sql/sql_optimizer.cc | 11 ++++++++++- 3 files changed, 42 insertions(+), 1 deletion(-) create mode 100644 mysql-test/r/hash_join_predicate_pushdown.result create mode 100644 mysql-test/t/hash_join_predicate_pushdown.test diff --git a/mysql-test/r/hash_join_predicate_pushdown.result b/mysql-test/r/hash_join_predicate_pushdown.result new file mode 100644 index 00000000000..150b04abd86 --- /dev/null +++ b/mysql-test/r/hash_join_predicate_pushdown.result @@ -0,0 +1,19 @@ +DROP TABLE IF EXISTS t1, t2; +CREATE TABLE t1(a INT, b INT, c VARCHAR(100)); +CREATE TABLE t2(a INT, b INT, c VARCHAR(100)); +explain format=tree select * from t1 left join t2 on t1.a = t2.a and t2.a > 0; +EXPLAIN +-> Left hash join (t2.a = t1.a) (cost=0.701 rows=1) + -> Table scan on t1 (cost=0.35 rows=1) + -> Hash + -> Filter: (t2.a > 0) (cost=0.35 rows=1) + -> Table scan on t2 (cost=0.35 rows=1) + +explain format=tree select * from t1 left join t2 on t1.a = t2.a and t2.a = 0; +EXPLAIN +-> Left hash join (no condition), extra conditions: (t1.a = 0) (cost=0.701 rows=1) + -> Table scan on t1 (cost=0.35 rows=1) + -> Hash + -> Filter: (t2.a = 0) (cost=0.35 rows=1) + -> Table scan on t2 (cost=0.35 rows=1) + diff --git a/mysql-test/t/hash_join_predicate_pushdown.test b/mysql-test/t/hash_join_predicate_pushdown.test new file mode 100644 index 00000000000..e40b7205117 --- /dev/null +++ b/mysql-test/t/hash_join_predicate_pushdown.test @@ -0,0 +1,13 @@ +# Test that filter predicate depending only on the inner table of an outer hash join is pushed down + +--disable_warnings + +DROP TABLE IF EXISTS t1, t2; + +CREATE TABLE t1(a INT, b INT, c VARCHAR(100)); +CREATE TABLE t2(a INT, b INT, c VARCHAR(100)); + +--enable_warnings +explain format=tree select * from t1 left join t2 on t1.a = t2.a and t2.a > 0; + +explain format=tree select * from t1 left join t2 on t1.a = t2.a and t2.a = 0; \ No newline at end of file diff --git a/sql/sql_optimizer.cc b/sql/sql_optimizer.cc index 77995398c98..e455199503e 100644 --- a/sql/sql_optimizer.cc +++ b/sql/sql_optimizer.cc @@ -4813,7 +4813,16 @@ Item *substitute_for_best_equal_field(THD *thd, Item *cond, if (cond_equal && cond_equal->current_level.head() == item_equal) cond_equal = cond_equal->upper_levels; return eliminate_item_equal(thd, nullptr, cond_equal, item_equal); - } else { + } else if (cond->type() == Item::FUNC_ITEM && + (down_cast(cond))->functype() == + Item_func::EQ_FUNC) { + /** + * Rewriting table in conditions other than equality will prevent the condition + * from being pushed down to the correct table in left outer hash join. + * e.g. SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a AND t2.a > 0 + * If t2.a > 0 is rewritten to t1.a > 0, the condition will not be pushed down + * to t2, which will result in a larger hash table than necessary. + */ Item::Replace_equal replace; uchar *arg = pointer_cast(&replace); if (cond->compile(&Item::replace_equal_field_checker, &arg, -- 2.39.1