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
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Description |
{code:sql}
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; {code} {noformat} 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) {noformat} So filtered here shows 99.61 % which is incorrect, selectivity should be ~66% Lets try with INT instead of BIT(32) {noformat} 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) {noformat} Filtered here is ~66%, so this looks correct. |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Link |
This issue relates to |
| Assignee | Varun Gupta [ varun ] | Sergei Petrunia [ psergey ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Assignee | Sergei Petrunia [ psergey ] | Igor Babaev [ igor ] |
| Fix Version/s | 10.1 [ 16100 ] |
| Assignee | Igor Babaev [ igor ] | Michael Widenius [ monty ] |
| Assignee | Michael Widenius [ monty ] | Varun Gupta [ varun ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Fix Version/s | 10.2.37 [ 25112 ] | |
| Fix Version/s | 10.3.28 [ 25111 ] | |
| Fix Version/s | 10.4.18 [ 25110 ] | |
| Fix Version/s | 10.5.9 [ 25109 ] | |
| Fix Version/s | 10.2 [ 14601 ] | |
| Fix Version/s | 10.3 [ 22126 ] | |
| Fix Version/s | 10.4 [ 22408 ] | |
| Fix Version/s | 10.5 [ 23123 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Fix Version/s | 10.2.38 [ 25207 ] | |
| Fix Version/s | 10.2.37 [ 25112 ] |
| Fix Version/s | 10.5.10 [ 25204 ] | |
| Fix Version/s | 10.4.19 [ 25205 ] | |
| Fix Version/s | 10.3.29 [ 25206 ] | |
| Fix Version/s | 10.5.9 [ 25109 ] | |
| Fix Version/s | 10.4.18 [ 25110 ] | |
| Fix Version/s | 10.2.38 [ 25207 ] |
| Fix Version/s | 10.2.38 [ 25207 ] | |
| Fix Version/s | 10.3.29 [ 25206 ] |
| Workflow | MariaDB v3 [ 108778 ] | MariaDB v4 [ 157783 ] |