[MDEV-30901] Index usage for DATE(datetime_column) = const does not work for engine Memory Created: 2023-03-22  Updated: 2023-04-25  Resolved: 2023-04-25

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 11.1
Fix Version/s: 11.1.1

Type: Bug Priority: Critical
Reporter: Lena Startseva Assignee: Oleg Smirnov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Problem/Incident
is caused by MDEV-8320 Allow index usage for DATE(datetime_c... Closed

 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"
 
        }
 
      }
 
    ]
 
  }
 
}



 Comments   
Comment by Oleg Smirnov [ 2023-03-23 ]

The reason why the condition rewrite doesn't happen is false result of condition check:

item_field->field->part_of_key.is_overlapping(*used_indexes)

which happens here:

opt_rewrite_date.cc lines 201-205

 
if ((field_type == MYSQL_TYPE_DATE ||
 
           field_type == MYSQL_TYPE_DATETIME ||
 
           field_type == MYSQL_TYPE_NEWDATE ||
 
           field_type == MYSQL_TYPE_TIMESTAMP) &&
 
          item_field->field->part_of_key.is_overlapping(*used_indexes))

item_field->field->part_of_key equals to 0 for the MEMORY engine while it equals to 3 for MyISAM. Field::part_of_key is set here:

table.cc TABLE_SHARE::init_from_binary_frm_image

 
          if (handler_file->index_flags(key, i, 0) & HA_KEYREAD_ONLY)
 
          {
 
            share->keys_for_keyread.set_bit(key);
 
            /*
 
              part_of_key is used to check if we can use the field
 
              as part of covering key (which implies HA_KEYREAD_ONLY).
 
            */
 
            field->part_of_key.set_bit(key);
 
          }

Condition 

if (handler_file->index_flags(key, i, 0) & HA_KEYREAD_ONLY)

is false for the MEMORY engine which prevents setting field->part_of_key bits.

Comment by Oleg Smirnov [ 2023-03-24 ]

The fix is pushed into preview-11.1-mdev-8320.

Comment by Sergei Petrunia [ 2023-04-13 ]

using the check for item_field->field->flags & PART_KEY_FLAG will cause the rewite still occur for key parts of indexes that were disabled with IGNORE INDEX hint:

create table t1 (a datetime, key(a));
 
insert into t1 values (now()),(now());
 
explain format=json
 
select * from t1 ignore index(a) where year(a)=2023;
 
EXPLAIN
 
{
 
  "query_block": {
 
    "select_id": 1,
 
    "cost": 0.01034841,
 
    "nested_loop": [
 
      {
 
        "table": {
 
          "table_name": "t1",
 
          "access_type": "ALL",
 
          "loops": 1,
 
          "rows": 2,
 
          "cost": 0.01034841,
 
          "filtered": 100,
 
          "attached_condition": "t1.a between <cache>('2023-01-01 00:00:00') and <cache>('2023-12-31 23:59:59')"
 
        }
 
      }
 
    ]
 
  }
 
}

Comment by Sergei Petrunia [ 2023-04-13 ]

... but that doesn't look significant. Ok to push.

Comment by Oleg Smirnov [ 2023-04-19 ]

Raising priority to Critical to get into the current release timeline (approved by julien.fritsch).

Comment by Lena Startseva [ 2023-04-24 ]

Testing done. Ok to push

Comment by Oleg Smirnov [ 2023-04-25 ]

Pushed to 11.1

Generated at Thu Feb 08 10:19:44 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.