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

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

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • 11.1
    • 11.1.1
    • Optimizer
    • None

    Description

      The Connect 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 (see the part of the test for connection 'master'):

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

      Example test:

      connect (master,127.0.0.1,root,,test,$MASTER_MYPORT,);
      connect (slave,127.0.0.1,root,,test,$SLAVE_MYPORT,);
       
      connection master;
      CREATE DATABASE connect;
       
      connection slave;
      CREATE DATABASE connect;
       
      create table t1 (pk int primary key, a datetime, key(a)) ;
       
      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;
       
      connection master;
      eval CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=MYSQL
        CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT';
      explain format=json  select * from t1 where year(a) = 2009;
      DROP TABLE t1;
       
      connection slave;
      DROP TABLE t1;
      DROP DATABASE connect;
       
      connection master;
      DROP DATABASE connect;
      

      Actual result:

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

      Attachments

        Issue Links

          Activity

            People

              lstartseva Lena Startseva
              lstartseva Lena Startseva
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.