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

Selectivity of an equality condition discounted twice

    XMLWordPrintable

Details

    • Bug
    • Status: Stalled (View Workflow)
    • Major
    • Resolution: Unresolved
    • None
    • 10.4(EOL)
    • Optimizer
    • 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

            igor Igor Babaev
            igor Igor Babaev
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.