Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3, 10.4, 10.5
-
None
Description
Filing this as a followup to the discussion about MDEV-21958.
The idea is to do the following: catch this pattern in the WHERE/ON condition:
colX IN (constA_1, ..., constA_N1) OR |
colX IN (constB_1, ..., const_BN2) OR |
...
|
and rewrite it as
colX IN (constA_1, ..., constA_N1, constB_1, ..., const_BN2) OR ... |
That is, join the IN-predicates together into one.
Do the same for sibling NOT IN (... ) predicates which are part of an AND.
The idea is that single IN-list is processed efficiently by the range optimizer, while multiple IN-lists are not. This will work around the problem.
The change needs to be performed (TODO: don't recall: late in Prepare stage, or the first in the execution stage). Execution stage is better because this is a rewrite. Prepare is better because we can make destructive modifications to the IN-lists.