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

Selectivity for BIT columns in filtered column for EXPLAIN is incorrect

    XMLWordPrintable

Details

    Description

      SET optimizer_use_condition_selectivity=4;
      SET histogram_size=255;
      SET use_stat_tables='preferably';
      CREATE TABLE t1 (a BIT(32), b INT);
      INSERT INTO t1 VALUES (80, 80), (81, 81), (82, 82);
      ANALYZE TABLE t1 PERSISTENT FOR ALL;
      

      MariaDB [test]> EXPLAIN EXTENDED SELECT * from t1 where t1.a >= 81;
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
      |    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    3 |    99.61 | Using where |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
      1 row in set, 1 warning (0.00 sec)
      

      So filtered here shows 99.61 % which is incorrect, selectivity should be ~66%

      Lets try with INT instead of BIT(32)

      MariaDB [test]> EXPLAIN EXTENDED SELECT * from t1 where t1.b >= 81;
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
      |    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    3 |    66.41 | Using where |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
      1 row in set, 1 warning (0.00 sec)
      

      Filtered here is ~66%, so this looks correct.

      Attachments

        Issue Links

          Activity

            People

              varun Varun Gupta (Inactive)
              varun Varun Gupta (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.