[MDEV-15368] Unnecessary condition used in Index Condition Pushdown Created: 2018-02-20  Updated: 2023-11-10

Status: Stalled
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Varun Gupta (Inactive) Assignee: Igor Babaev
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-15253 Default optimizer setting changes for... Closed

 Description   

EXPLAIN SELECT MIN(b), MIN(c) FROM t1 WHERE a = 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	a	a	5	const	2	Using index condition
analyze format=json
SELECT MIN(b), MIN(c) FROM t1 WHERE a = 1;
ANALYZE
{
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 0.0893,
    "table": {
      "table_name": "t1",
      "access_type": "ref",
      "possible_keys": ["a"],
      "key": "a",
      "key_length": "5",
      "used_key_parts": ["a"],
      "ref": ["const"],
      "r_loops": 1,
      "rows": 2,
      "r_rows": 3,
      "r_total_time_ms": 0.0462,
      "filtered": 100,
      "r_filtered": 100,
      "index_condition": "1"
    }
  }
}

So here we see that we have a pushed index condition but as you see that is a constant , not mentioned in the where clause of the query. So we should not be having such a index condition.



 Comments   
Comment by Varun Gupta (Inactive) [ 2018-02-20 ]

The complete mtr test

CREATE TABLE t1( a INT, b INT, c INT, KEY(a, b) );
INSERT INTO t1 ( a, b, c ) VALUES ( 1, NULL, 2 ), ( 1, 3, 4 ), ( 1, 4, 4 );
set join_cache_level=4;
EXPLAIN SELECT MIN(b), MIN(c) FROM t1 WHERE a = 1;
analyze format=json
SELECT MIN(b), MIN(c) FROM t1 WHERE a = 1;
drop table t1;

Comment by Elena Stepanova [ 2018-03-24 ]

varun, please set affects/fix versions.

Comment by Varun Gupta (Inactive) [ 2019-04-30 ]

Patch
http://lists.askmonty.org/pipermail/commits/2018-March/012138.html

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