[MDEV-13628] ORed condition in pushed index condition is not removed from the WHERE Created: 2017-08-23  Updated: 2019-05-13  Resolved: 2019-05-11

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.1, 10.2, 10.3
Fix Version/s: 10.4.5

Type: Bug Priority: Major
Reporter: Sergei Petrunia Assignee: Varun Gupta (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-13905 condition in pushed index condition i... Stalled
relates to MDEV-15253 Default optimizer setting changes for... Closed

 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>



 Comments   
Comment by Varun Gupta (Inactive) [ 2017-08-31 ]

Have checked the history of 10.1 where analyze was introduced, the above issue was there in all the versions of 10.1

Comment by Sergei Petrunia [ 2017-09-01 ]

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).

Comment by Sergei Petrunia [ 2017-09-13 ]

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?

Comment by Sergei Petrunia [ 2017-09-13 ]

This is the commit that removed it:
https://github.com/MariaDB/server/commit/e0c1b3f24246d22e6785315f9a8448bd9a590422#diff-c573d4ac6f8a4062a1d261c87a2c60a8L244

Comment by Sergei Petrunia [ 2017-09-13 ]

... 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.

Comment by Sergei Petrunia [ 2017-10-03 ]

Got a question: http://lists.askmonty.org/pipermail/commits/2017-September/011501.html Need to investigate this before we can proceed.

Comment by Varun Gupta (Inactive) [ 2017-10-03 ]

psergey
http://lists.askmonty.org/pipermail/commits/2017-October/011516.html

Comment by Varun Gupta (Inactive) [ 2018-07-19 ]

New patch with lots of result files updated (which were incorrectly updated earlier)

http://lists.askmonty.org/pipermail/commits/2018-July/012710.html

Comment by Igor Babaev [ 2019-05-02 ]

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.

Comment by Varun Gupta (Inactive) [ 2019-05-06 ]

The fix version is decided as 10.4 and this can be back-ported to earlier versions if requested

Comment by Varun Gupta (Inactive) [ 2019-05-06 ]

Patch
http://lists.askmonty.org/pipermail/commits/2019-May/013745.html

Generated at Thu Feb 08 08:07:05 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.