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

filesort still select when desc/asc index matches ORDER BY

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.11.5
    • None
    • Optimizer
    • None

    Description

      from mysql-test/main/order_by.test (without forcing the index to something that should be used)

      create table t1 (a int, b int, c int, key r (a desc, b asc));
      insert t1 select seq % 10, seq div 10, seq from seq_1_to_55;
      insert t1 values (NULL, NULL, NULL), (9, NULL, NULL);
       
      MariaDB [test]> explain format=json select * from t1 order by a desc,b asc\G
      *************************** 1. row ***************************
      EXPLAIN: {
        "query_block": {
          "select_id": 1,
          "nested_loop": [
            {
              "read_sorted_file": {
                "filesort": {
                  "sort_key": "t1.a desc, t1.b",
                  "table": {
                    "table_name": "t1",
                    "access_type": "ALL",
                    "rows": 57,
                    "filtered": 100
                  }
                }
              }
            }
          ]
        }
      }
      

      expected result, index r used for the query.

      same with the inverse:

      MariaDB [test]> explain format=json select * from t1 order by a asc,b desc\G
      *************************** 1. row ***************************
      EXPLAIN: {
        "query_block": {
          "select_id": 1,
          "nested_loop": [
            {
              "read_sorted_file": {
                "filesort": {
                  "sort_key": "t1.a, t1.b desc",
                  "table": {
                    "table_name": "t1",
                    "access_type": "ALL",
                    "rows": 57,
                    "filtered": 100
                  }
                }
              }
            }
          ]
        }
      }
      

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              danblack Daniel Black
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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