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
-
-
- Stalled
-
-
MDEV-15253 Default optimizer setting changes for MariaDB 10.4
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Description |
Noticed this when working on MyRocks but it seems to be an issue affecting any storage engine:
{code:sql} 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); {code} {code:sql} 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" } } } {code} 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 * was it always like that in MariaDB or something changed since ICP was introduced (please check the revision history) The storage engine doesn't matter: {code:sql} alter table t10 engine=myisam; explain format=json select * from t10 where key1 < 3 or key1 > 99999\G <the same output> {code} |
Noticed this when working on MyRocks but it seems to be an issue affecting any storage engine:
{code:sql} 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); {code} {code:sql} 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" } } } {code} 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: {code:sql} alter table t10 engine=myisam; explain format=json select * from t10 where key1 < 3 or key1 > 99999\G <the same output> {code} |
| Assignee | Varun Gupta [ varun ] |
| Fix Version/s | 10.3 [ 22126 ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Assignee | Varun Gupta [ varun ] | Sergei Petrunia [ psergey ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Fix Version/s | 10.1 [ 16100 ] | |
| Fix Version/s | 10.3 [ 22126 ] |
| Affects Version/s | 10.3 [ 22126 ] | |
| Affects Version/s | 10.1 [ 16100 ] |
However, index condition pushdown was there before the ANALYZE, and this bug is about ICP ( ANALYZE, or actually FORMAT=JSON only provides a way to view the condition for the user).
If I take MariaDB and roll it back to the revision d9a81475c70aebc1f2542bf0c115b3ebd9649193 , I can see there:
Item *make_cond_remainder(Item *cond, bool exclude_index)
|
{
|
if (exclude_index && cond->marker == ICP_COND_USES_INDEX_ONLY) |
return 0; /* Already checked */ |
|
this is exactly the piece of code that the patch wants to add. I'm wondering on what occasion it was removed?
This is the commit that removed it:
https://github.com/MariaDB/server/commit/e0c1b3f24246d22e6785315f9a8448bd9a590422#diff-c573d4ac6f8a4062a1d261c87a2c60a8L244
... and that commit says:
The code
erroneously assumed that the function make_cond_for_table left
the value of ICP_COND_USES_INDEX_ONLY in sub-condition markers.
Adjusted many result files from the regression test suite
after this fix .
So the current patch is trying to do just that: assume that make_cond_for_table (I assume Igor has meant make_cond_for_index here) leaves the value of ICP_COND_USES_INDEX_ONLY in sub-condition markers.
Let's discuss this with Igor.
| Link | This issue relates to MDEV-13905 [ MDEV-13905 ] |
Got a question: http://lists.askmonty.org/pipermail/commits/2017-September/011501.html Need to investigate this before we can proceed.
| Link |
This issue relates to |
New patch with lots of result files updated (which were incorrectly updated earlier)
http://lists.askmonty.org/pipermail/commits/2018-July/012710.html
| Assignee | Sergei Petrunia [ psergey ] | Igor Babaev [ igor ] |
Varun,
This is a performance improvement. Although the patch looks quite harmless it would be better to push it only into 10.4. So please prepare the patch for 10.4 and in the commit comment say that the issue is of 5.5 and can be easily back-ported when it is requested. Also add such comment in Jira.
| Assignee | Igor Babaev [ igor ] | Varun Gupta [ varun ] |
| Fix Version/s | 10.4 [ 22408 ] | |
| Fix Version/s | 10.1 [ 16100 ] |
The fix version is decided as 10.4 and this can be back-ported to earlier versions if requested
| Assignee | Varun Gupta [ varun ] | Igor Babaev [ igor ] |
| Assignee | Igor Babaev [ igor ] | Varun Gupta [ varun ] |
| Fix Version/s | 10.4.5 [ 23311 ] | |
| Fix Version/s | 10.4 [ 22408 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | In Review [ 10002 ] | Closed [ 6 ] |
| Workflow | MariaDB v3 [ 82235 ] | MariaDB v4 [ 152693 ] |
Have checked the history of 10.1 where analyze was introduced, the above issue was there in all the versions of 10.1