Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-18741

Optimizer trace: multi-part key ranges are printed incorrectly.

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.4(EOL)
    • 10.4.6
    • Optimizer
    • None

    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.

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-19634 Assertion `0' failed in row_sel_convert_mysql_key_to_innobase, [Warning] InnoDB: Using a partial-field key prefix in search

          • Major - Major loss of function.
          • Closed
          relates to

          Task - A task that needs to be done. MDEV-6111 optimizer trace

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            psergei Sergei Petrunia created issue -
            psergei Sergei Petrunia made changes -
            Field Original Value New Value
            varun Varun Gupta (Inactive) made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            varun Varun Gupta (Inactive) made changes -
            Assignee Varun Gupta [ varun ] Sergei Petrunia [ psergey ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            varun Varun Gupta (Inactive) added a comment - - edited

            Patch
            http://lists.askmonty.org/pipermail/commits/2019-March/013531.html

            varun Varun Gupta (Inactive) added a comment - - edited Patch http://lists.askmonty.org/pipermail/commits/2019-March/013531.html
            psergei Sergei Petrunia added a comment - - edited

            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

            psergei Sergei Petrunia added a comment - - edited 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
            psergei Sergei Petrunia made changes -
            Assignee Sergei Petrunia [ psergey ] Varun Gupta [ varun ]
            psergei Sergei Petrunia added a comment -

            Review input provided over email

            psergei Sergei Petrunia added a comment - Review input provided over email
            varun Varun Gupta (Inactive) made changes -
            Status In Review [ 10002 ] Stalled [ 10000 ]
            varun Varun Gupta (Inactive) made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            varun Varun Gupta (Inactive) added a comment -

            Patch
            http://lists.askmonty.org/pipermail/commits/2019-April/013606.html

            varun Varun Gupta (Inactive) added a comment - Patch http://lists.askmonty.org/pipermail/commits/2019-April/013606.html
            varun Varun Gupta (Inactive) made changes -
            Assignee Varun Gupta [ varun ] Sergei Petrunia [ psergey ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            psergei Sergei Petrunia added a comment -

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

            psergei Sergei Petrunia added a comment - Review feedback sent. There's still work to do.
            psergei Sergei Petrunia made changes -
            Assignee Sergei Petrunia [ psergey ] Varun Gupta [ varun ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            varun Varun Gupta (Inactive) added a comment -

            Updated Patch
            http://lists.askmonty.org/pipermail/commits/2019-April/013616.html

            varun Varun Gupta (Inactive) added a comment - Updated Patch http://lists.askmonty.org/pipermail/commits/2019-April/013616.html
            varun Varun Gupta (Inactive) made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            varun Varun Gupta (Inactive) made changes -
            Assignee Varun Gupta [ varun ] Sergei Petrunia [ psergey ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            psergei Sergei Petrunia added a comment -

            Review feedback sent https://lists.launchpad.net/maria-developers/msg11823.html

            psergei Sergei Petrunia added a comment - Review feedback sent https://lists.launchpad.net/maria-developers/msg11823.html
            psergei Sergei Petrunia made changes -
            Assignee Sergei Petrunia [ psergey ] Varun Gupta [ varun ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            varun Varun Gupta (Inactive) made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            varun Varun Gupta (Inactive) added a comment -

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

            varun Varun Gupta (Inactive) added a comment - Patch http://lists.askmonty.org/pipermail/commits/2019-May/013801.html
            varun Varun Gupta (Inactive) made changes -
            Assignee Varun Gupta [ varun ] Sergei Petrunia [ psergey ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            psergei Sergei Petrunia added a comment -

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

            psergei Sergei Petrunia added a comment - Review feedback sent. (One small comment, ok to push after that)
            psergei Sergei Petrunia made changes -
            Assignee Sergei Petrunia [ psergey ] Varun Gupta [ varun ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            varun Varun Gupta (Inactive) made changes -
            Fix Version/s 10.4.6 [ 23412 ]
            Fix Version/s 10.4 [ 22408 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            psergei Sergei Petrunia made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 92854 ] MariaDB v4 [ 155807 ]

            People

              varun Varun Gupta (Inactive)
              psergei Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.