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

Selectivity of an equality condition discounted twice

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

          Transition Time In Source Status Execution Times
          Sergei Petrunia made transition -
          Open In Progress
          		5d 16h 39m 1
          Sergei Petrunia made transition -
          In Progress In Review
          		14h 11m 1
          Julien Fritsch made transition -
          In Review Stalled
          		1613d 1h 46m 1

          People

            igor Igor Babaev (Inactive)
            igor Igor Babaev (Inactive)
            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.