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

Selectivity of an equality condition discounted twice

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • None
    • N/A
    • 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

          Ascending order - Click to sort in descending order
          psergei Sergei Petrunia added a comment -

          http://lists.askmonty.org/pipermail/commits/2019-June/013885.html

          Igor, please review

          psergei Sergei Petrunia added a comment - http://lists.askmonty.org/pipermail/commits/2019-June/013885.html Igor, please review
          psergei Sergei Petrunia added a comment -

          Pushed the fix into 10.1 (with a testcase): https://github.com/MariaDB/server/commit/1c75ad6eed744672fdce77b0752801b67edb69f8 .

          psergei Sergei Petrunia added a comment - Pushed the fix into 10.1 (with a testcase): https://github.com/MariaDB/server/commit/1c75ad6eed744672fdce77b0752801b67edb69f8 .
          monty Michael Widenius added a comment -

          Was pushed into 10.1.42

          monty Michael Widenius added a comment - Was pushed into 10.1.42

          People

            igor Igor Babaev (Inactive)
            igor Igor Babaev (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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