Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1(EOL), 10.2(EOL), 10.3(EOL)
-
None
Description
Noticed this when working on MyRocks but it seems to be an issue affecting any storage engine:
create table ten(a int); |
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); |
create table one_k(a int); |
insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C; |
create table t10 (key1 int not null, filler char(100)) engine=rocksdb; |
insert into t10 select A.a + 1000 *B.a, 'filler-data' from one_k A, ten B; |
alter table t10 add key(key1); |
explain format=json select * from t10 where key1 < 3 or key1 > 99999\G |
*************************** 1. row ***************************
|
EXPLAIN: {
|
"query_block": { |
"select_id": 1, |
"table": { |
"table_name": "t10", |
"access_type": "range", |
"possible_keys": ["key1"], |
"key": "key1", |
"key_length": "4", |
"used_key_parts": ["key1"], |
"rows": 2, |
"filtered": 100, |
"index_condition": "t10.key1 < 3 or t10.key1 > 99999", |
"attached_condition": "t10.key1 < 3 or t10.key1 > 99999" |
}
|
}
|
}
|
Note that index_condition and attached_condition have the same condition. This should not happen, the optimizer should try to remove the condition that is already checked. FB/mysql does it, and MariaDB's one should, too. ( We should check the revision history - did this got broken un-intentionally?)
The storage engine doesn't matter:
alter table t10 engine=myisam; |
explain format=json select * from t10 where key1 < 3 or key1 > 99999\G |
<the same output> |
Attachments
Issue Links
- relates to
-
MDEV-13905 condition in pushed index condition is not removed from the WHERE in BNL joins
- Stalled
-
MDEV-15253 Default optimizer setting changes for MariaDB 10.4
- Closed