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

              Assignee:
              varun Varun Gupta (Inactive)
              Reporter:
              varun Varun Gupta (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: