MySQL Bugs: #112198: Incorrect multiple equality optimization causes performance degradation

Bug #112198	Incorrect multiple equality optimization causes performance degradation
Submitted:	28 Aug 2023 8:21	Modified:	28 Aug 2023 8:32
Reporter:	Wen He (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	8.0, 8.0.34	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Contribution, equality, hash join, OUTER JOIN.

Description:
For query `select * from t1 left join t2 on t1.a = t2.a and t2.a > 0;`, the condition `t2.a > 0` can apparently be pushed down to be a filter uppon the inner table. But it does not do so as the condition is optimized to `t1.a > 0`, which causes a bigger join buffer consumption and performance degradation.

How to repeat:
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\G

*************************** 1. row ***************************
-> Left hash join (t2.a = t1.a), extra conditions: (t1.a > 0)  (cost=0.70 rows=1)
    -> Table scan on t1  (cost=0.35 rows=1)
    -> Hash
        -> Table scan on t2  (cost=0.35 rows=1)

Suggested fix:
The optimization for multiple equality is based on the assumption that fields belong to tables in the front of the join order set up by JOIN::make_join_plan will be retrieved first. However, it's not the case for outer joins.

Hello Wen He,

Thank you for the report and feedback.

regards,
Umesh

This is a fix for Bug #112198 by disabling field substitution for conditions other than equality, allowing predicate pushdown. (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: Bug#112198.patch (application/octet-stream, text), 4.00 KiB.

Hi, I just submitted a patch to fix the issue.

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.

I do think the bypass of the field substitution should be applied more carefully, like only if the condition is on the inner table of an outer join. 
However, I was unable to check such information at the stage when the optimization happened. I tried query_block->outer_join and is_inner_table_of_outer_join(), but they both contain no useful information (I suppose their values haven't been set when the substitution happens?).
Please let me know if it breaks other parts of MySQL, and I'll try to modify the patch.