[MDEV-20697] Take selectivity of join conditions into account correctly with optimizer_use_condition_selectivity > 1 Created: 2019-09-30  Updated: 2023-04-27

Status: Stalled
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.1, 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.4, 10.5

Type: Bug Priority: Major
Reporter: Varun Gupta (Inactive) Assignee: Igor Babaev
Resolution: Unresolved Votes: 1
Labels: eits

Issue Links:
Relates
relates to MDEV-20595 Assertion `0 < sel && sel <= 2.0' fai... Stalled
relates to MDEV-23707 Fix condition selectivity computation... Stalled

 Description   

For multi equalities we try to add selectivity estimates using EITS but currently this does not work
In the function table_multi_eq_cond_selectivity

    if (adjust_sel)
 
    {
 
      /* 
        If ref == 0 and there are no fields in the multiple equality
 
        item_equal that belong to the tables joined prior to s
 
        then the selectivity of multiple equality will be set to 1.0.
 
      */
 
      double eq_fld_sel= 1.0;
 
      fi.rewind();
 
      while ((fi++))
 
      {
 
        double curr_eq_fld_sel;
 
        Field *fld= fi.get_curr_field();
 
        if (!(fld->table->map & ~(table_bit | rem_tables)))
 
          continue;
 
        curr_eq_fld_sel= get_column_avg_frequency(fld) /
 
                         fld->table->stat_records();
 
        if (curr_eq_fld_sel < 1.0)
 
          set_if_bigger(eq_fld_sel, curr_eq_fld_sel);
 
      }
 
      sel*= eq_fld_sel;
 
    }

So as we see here the comparision 

        if (curr_eq_fld_sel < 1.0)
 
          set_if_bigger(eq_fld_sel, curr_eq_fld_sel);

eq_fld_sel is set to 1, so eq_fld_sel would always remain 1 as we try to update the value in the cases curr_eq_fld_sel < 1.
This needs to be corrected.
What I understand the code it does here is it takes the highest selectivity estimate for fields inside the multiple equality which makes sense as
we want to keep our estimates pessimistic

After discussing with Igor he mentioned that the intention for the function was to take into account selectivity of equalities for condition on the same table like tbl.col1 = tbl.col2 .
Generated at Thu Feb 08 09:01:29 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.