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

EITS: wrong selectivity for REF(t1.key=t0.col) AND COND(t0.col)

    XMLWordPrintable

    Details

      Description

      This issue is a branch-off from MDEV-5985.

      The dataset is:

      ## Dataset from MDEV-5985:
      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 one_k2 as select * from one_k;
      set histogram_size=100;
      set use_stat_tables='preferably';
      set optimizer_use_condition_selectivity=4;
      analyze table one_k persistent for all;
      analyze table one_k2 persistent for all;
       
      # Extra data for this MDEV:
      create table one_k3 (a int, b int, key(a));
      insert into one_k3 select A.a + 10*B.a, 12345 from ten A, ten B, ten C;
      analyze table one_k3 persistent for all;

      The query is:

      explain extended select * from one_k2 straight_join one_k3 where one_k2.a=one_k3.a and one_k2.a<10;
      +------+-------------+--------+------+---------------+------+---------+--------------+------+----------+-------------+
      | id   | select_type | table  | type | possible_keys | key  | key_len | ref          | rows | filtered | Extra       |
      +------+-------------+--------+------+---------------+------+---------+--------------+------+----------+-------------+
      |    1 | SIMPLE      | one_k2 | ALL  | NULL          | NULL | NULL    | NULL         | 1000 |     0.99 | Using where |
      |    1 | SIMPLE      | one_k3 | ref  | a             | a    | 5       | j22.one_k2.a |   10 |     9.90 |             |
      +------+-------------+--------+------+---------------+------+---------+--------------+------+----------+-------------+

      Here, one_k2.filtered=0.99% is correct. However, we should have
      one_k3.filtered=100%, because

      • ref access makes sure that one_k2.a=one_k3.a,
      • "one_k2.a<10" is checked for table one_k2
      • hence, checking that "one_k3.a<10" will not produce any selectivity (in fact, the optimizer will not even generate this condition)

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              igor Igor Babaev
              Reporter:
              psergey Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: