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
- relates to
-
MDEV-22509 Server crashes in Field_inet6::store_inet6_null_with_warn / Field::maybe_null
-
- Closed
-