[MDEV-22583] Selectivity for BIT columns in filtered column for EXPLAIN is incorrect - Jira

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.

relates to

MDEV-22509 Server crashes in Field_inet6::store_inet6_null_with_warn / Field::maybe_null

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server