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

Index usage for DATE(datetime_column) = const does not work for engine Memory

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 11.1
    • 11.1.1
    • Optimizer
    • None

    Description

      The Memory engine retained the old behavior.
      Instead of:

      "access_type": "range"
      "index_condition": "t1.a between '2009-01-01 00:00:00' and '2009-12-31 23:59:59'"
      

      Query plan has:

      "access_type": "ALL"
      "attached_condition": "year(t1.a) = 2009"
      

      Example test:

      create table t1 (pk int primary key, a datetime, key(a)) engine=memory;
       
      INSERT INTO t1 (pk,a) VALUES (1,'2009-11-29 13:43:32');
      INSERT INTO t1 (pk,a) VALUES (2,'2009-11-29 03:23:32');
      INSERT INTO t1 (pk,a) VALUES (3,'2009-10-16 05:56:32');
      INSERT INTO t1 (pk,a) VALUES (4,'2010-11-29 13:43:32');
      INSERT INTO t1 (pk,a) VALUES (5,'2010-10-16 05:56:32');
      INSERT INTO t1 (pk,a) VALUES (6,'2011-11-29 13:43:32');
      INSERT INTO t1 (pk,a) VALUES (7,'2012-10-16 05:56:32');
       
      explain format=json  select * from t1 where year(a) = 2009;
       
      drop table t1;
      

      Actual result:

      explain format=json  select * from t1 where year(a) = 2009;
      EXPLAIN
      {
        "query_block": {
          "select_id": 1,
          "cost": 0.010296454,
          "nested_loop": [
            {
              "table": {
                "table_name": "t1",
                "access_type": "ALL",
                "loops": 1,
                "rows": 7,
                "cost": 0.010296454,
                "filtered": 100,
                "attached_condition": "year(t1.a) = 2009"
              }
            }
          ]
        }
      }
      

      Attachments

        Issue Links

          Activity

            People

              oleg.smirnov Oleg Smirnov
              lstartseva Lena Startseva
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.