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

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

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.4
    • Fix Version/s: 10.4.6
    • Component/s: Optimizer
    • Labels:
      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

            Activity

              People

              • Assignee:
                varun Varun Gupta
                Reporter:
                psergey Sergei Petrunia
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: