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

Wrong number of filtered rows for a table in EXPLAIN EXTENDED

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 10.0.27
    • Fix Version/s: 10.0.29
    • Component/s: Optimizer
    • Labels:
      None

      Description

      EXPLAIN EXTENDED always returns 0.00 percentage of filtered rows if there is a range condition over the column for which no statistics has been collected:

      The following test case demonstrates the problem:

      set use_stat_tables='preferably';
      set optimizer_use_condition_selectivity=3;
       
      create table t1(col1 char(32));
      insert into t1 values ('a'),('b'),('c'),('d'), ('e'),('f'),('g'),('h');
      analyze table t1 persistent for columns () indexes ();
      explain extended 
      select * from t1 where col1 > 'b' and col1 < 'd';
       
      MariaDB [test]> explain extended 
          -> select * from t1 where col1 > 'b' and col1 < 'e';
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
      |    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    8 |     0.00 | Using where |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
      

        Attachments

          Activity

            People

            Assignee:
            igor Igor Babaev
            Reporter:
            igor Igor Babaev
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: