optimizer_use_cond_selectivity > 1 can cause slow plans

In MariaDB 10.4 we changed the default of optimizer_use_cond_selectivity
from 1 to to 4 to get better plans for complex queries.

The definition of the values are (from mysqld --help):

1 - use selectivity of index backed range
conditions to calculate the cardinality of a partial join
if the last joined table is accessed by full table scan
or an index scan. This is basically how MariaDB and MySQL 5.5 and before
works.

2 - use selectivity of index backed range conditions to calculate the
cardinality of a partial join in any case

3 - additionally always use selectivity of range conditions that are not backed
by any index to calculate the cardinality of a partial join

4 - use histograms to calculate selectivity of range conditions that are not
backed by any index to calculate the cardinality of a partial join

5 - additionally use selectivity of certain non-range predicates calculated on
record samples

In some case when optimizer_use_cond_selectivity = 2 (or bigger) the optimizer
calculates the selectivity wrong, which can cause it to select a wrong (slow)
plan.

One can find if this is what causes wrong plan by using optimizer_trace
(https://mariadb.com/kb/en/optimizer-trace-overview/). If you see a cost
that is DOUBLE_MAX then you have probably hit this bug.

A temporary workaround, until this problem is fixed, is to set
optimizer_use_cond_selectivity to 1.

If you have this problem, please also attach to this Jira entry:

• The query,
• Table defintions
• Optimizer trace