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

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

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.0.9
    • Fix Version/s: 10.0.10
    • Component/s: None
    • Labels:

      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

            • Assignee:
              psergey Sergei Petrunia
              Reporter:
              psergey Sergei Petrunia
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: