[#MDEV-18741] Optimizer trace: multi-part key ranges are printed incorrectly.

[MDEV-18741] Optimizer trace: multi-part key ranges are printed incorrectly. Created: 2019-02-26 Updated: 2019-05-31 Resolved: 2019-05-28
Status:	Closed
Project:	MariaDB Server
Component/s:	Optimizer
Affects Version/s:	10.4
Fix Version/s:	10.4.6

Type:

Bug

Priority:

Major

Reporter:

Sergei Petrunia

Assignee:

Varun Gupta (Inactive)

Resolution:

Fixed

Votes:

Labels:

None

Issue Links:

Problem/Incident
causes	~~MDEV-19634~~	Assertion `0' failed in row_sel_conve...	Closed
Relates
relates to	~~MDEV-6111~~	optimizer trace	Closed

Description

create table t1 (start_date date, end_date date, filler char(100), key(start_date, end_date)) ;

source ~/fill-test.sql

create table t1 (start_date date, end_date date, filler char(100), key(start_date, end_date)) ;

insert into t1 select date_add(now(), interval a day), date_add(now(), interval (a+7) day), 'data' from one_k;

Query OK, 1000 rows affected, 2000 warnings (0.40 sec)

Records: 1000  Duplicates: 0  Warnings: 2000

mysql> set optimizer_trace=1;

mysql> explain format=json select * from t1 force index(start_date) where start_date >= '2019-02-10' and end_date <'2019-04-01';

| {

  "query_block": {

    "select_id": 1,

    "table": {

      "table_name": "t1",

      "access_type": "range",

      "possible_keys": ["start_date"],

      "key": "start_date",

      "key_length": "8",

      "used_key_parts": ["start_date", "end_date"],

      "rows": 1000,

      "filtered": 100,

      "index_condition": "t1.start_date >= '2019-02-10' and t1.end_date < '2019-04-01'"

Note that

      "used_key_parts": ["start_date", "end_date"],

But due to a bug in append_range_all_keyparts near is_singlepoint() call the
printed range uses one key part:

    "range_scan_alternatives": [

        "index": "start_date",

        "ranges": ["0x4ac60f <= start_date"],

        "rowid_ordered": false,

        "using_mrr": false,

        "index_only": false,

        "rows": 1000,

        "cost": 1252.4,

        "chosen": true

],

The fact that date is printed as 0x4ac60f is a separate bug and should also be
fixed.

Comments

Comment by Varun Gupta (Inactive) [ 2019-03-11 ]

Comment by Sergei Petrunia [ 2019-03-16 ]

Note that the upstream is also affected, albeit in a different way.

Let's try the following on MySQL-8.0.12-debug:

create table t0(a int);

insert into t0 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 t0 A, t0 B, t0 C;

create table t1 ( a int, b int, c int, d int, key (a,b), key(c,d));

mysql> explain select * from t1 where a >= 900 and b between 10 and 20;

+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+

| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |

+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+

|  1 | SIMPLE      | t1    | NULL       | range | a             | a    | 10      | NULL |  100 |    11.11 | Using index condition |

+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+

Note key_len=10. This means, two components are used. Optimizer trace will show that only one key part is used:

select * from information_schema.optimizer_trace:

...

"chosen_range_access_summary": {

  "range_access_plan": {

    "type": "range_scan",

    "index": "a",

    "rows": 100,

    "ranges": [

      "900 <= a"

},

...

One can see the actual range in /tmp/mysqld.trace:

T@8: | | | | | | | | | | >print_quick

quick range select, key a, length: 10

  900/10 <= X

other_keys: 0x0:

T@8: | | | | | | | | | | <print_quick 14335

Comment by Sergei Petrunia [ 2019-03-18 ]

Review input provided over email

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

Comment by Sergei Petrunia [ 2019-04-04 ]

Review feedback sent. There's still work to do.

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

Comment by Sergei Petrunia [ 2019-05-03 ]

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

Comment by Sergei Petrunia [ 2019-05-27 ]

Review feedback sent. (One small comment, ok to push after that)

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