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

EITS: different order of predicates in IN (...) causes different estimates

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0.9
    • 10.0.10
    • None

    Description

      Create the dataset:

      create table ten(a int);
      insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
      create table one_k(a int);
      insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;
      create table t1 (col1 int);
      insert into t1 select a from one_k;
      insert into t1 select 2000+a from one_k;
      insert into t1 select 2000+a from one_k;
      insert into t1 select 2000+a from one_k;
      insert into t1 select 2000+a from one_k;
      insert into t1 select 2000+a from one_k;
      insert into t1 select 2000+a from one_k;
      insert into t1 select 2000+a from one_k;
      insert into t1 select 2000+a from one_k;
      insert into t1 select 2000+a from one_k;
      insert into t1 select 2000+a from one_k;
      set use_stat_tables = 'preferably';
      set optimizer_use_condition_selectivity=3;
      set optimizer_use_condition_selectivity=4;
      set histogram_size=100;
      analyze table t1 persistent for all; 

      Then run:

      mysql> explain extended select * from t1 where col1 in (2990, 10);
      +------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
      +------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+
      |    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 11000 |     0.03 | Using where |
      +------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+

      And then run:

      mysql> explain extended select * from t1 where col1 in (10,2990);
      +------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
      +------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+
      |    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 11000 |     0.25 | Using where |
      +------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+

      Note that IN predicate is essentailly the same, but "filtered" changes.

      Attachments

        Activity

          People

            psergei Sergei Petrunia
            psergei Sergei Petrunia
            Votes:
            0 Vote for this issue
            Watchers:
            1 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.