Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-19834

Selectivity of an equality condition discounted twice

    Details

    • Type: Bug
    • Status: In Review (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: 10.4
    • Component/s: Optimizer
    • Labels:
      None

      Description

      The following simple test case can demonstrate the problem:

      set optimizer_switch='rowid_filter=off'; 
      create table t1 (a int, b int, key (b), key (a)) engine=innodb;
      insert into t1
      select (rand(1)*1000)/10, (rand(1001)*1000)/50 from seq_1_to_1000;
      analyze table t1 ;
      explain extended select * from t1 where a in (17,51,5) and b=2;
      

      MariaDB [test]> explain extended select * from t1 where a in (17,51,5) and b=2;
      +------+-------------+-------+------+---------------+------+---------+-------+------+----------+-------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
      +------+-------------+-------+------+---------------+------+---------+-------+------+----------+-------------+
      |    1 | SIMPLE      | t1    | ref  | b,a           | b    | 5       | const | 59   |    49.15 | Using where |
      +------+-------------+-------+------+---------------+------+---------+-------+------+----------+-------------+
      

      It can be seen from the output of the last command that the selectivity of the condition is shown as absurdly low (49.15%). In fact it is pretty high:

      MariaDB [test]> explain extended select * from t1 where a in (17,51,5);
      +------+-------------+-------+-------+---------------+------+---------+------+------+----------+-----------------------+
      | id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
      +------+-------------+-------+-------+---------------+------+---------+------+------+----------+-----------------------+
      |    1 | SIMPLE      | t1    | range | a             | a    | 5       | NULL | 30   |   100.00 | Using index condition |
      +------+-------------+-------+-------+---------------+------+---------+------+------+----------+-----------------------+
      

      It happens because after the commit 2acd81af73ac337658eb646ba2434f46a6dc8dc5 for MDEV-6003: "EITS: ref access, keypart2=const vs keypart2=expr - inconsistent filtered% value" the function table_cond_selectivity() started discounting the selectivity of the condition b=2 twice:
      the first time as a range condition:

      sel /= (double)table->quick_rows[key] / (double) table->stat_records();
      

      the second time as the ref condition:

      sel /= table->field[fldno]->cond_selectivity;
      

      Setting optimizer_switch='rowid_filter=off' does not help.
      Most probably the bug can be reproduced in 10.1,10.2,10.3 with corresponding settings.

        Attachments

          Activity

            People

            • Assignee:
              igor Igor Babaev
              Reporter:
              igor Igor Babaev
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated: