[MDEV-30946] Index usage for DATE(datetime_column) = const does not work for DELETE and UPDATE Created: 2023-03-28  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: Major
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   

Expected that index usage for like "DATE(col) = const" to "col BETWEEN concat(const, ' 00:00:00') AND concat(const, ' 23:59:59')" is the same for SELECT, UPDATE and DELETE, but it works only for SELECT.
Example test:

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

Actual result:

explain format=json  select * from t1 where year(a) = 2010;
 
EXPLAIN
 
{
 
  "query_block": {
 
    "select_id": 1,
 
    "cost": 0.003808422,
 
    "nested_loop": [
 
      {
 
        "table": {
 
          "table_name": "t1",
 
          "access_type": "range",
 
          "possible_keys": ["a"],
 
          "key": "a",
 
          "key_length": "6",
 
          "used_key_parts": ["a"],
 
          "loops": 1,
 
          "rows": 2,
 
          "cost": 0.003808422,
 
          "filtered": 100,
 
          "index_condition": "t1.a between '2010-01-01 00:00:00' and '2010-12-31 23:59:59'"
 
        }
 
      }
 
    ]
 
  }
 
}
 
explain format=json  update t1 set c = 0 where year(a) = 2010;
 
EXPLAIN
 
{
 
  "query_block": {
 
    "select_id": 1,
 
    "table": {
 
      "update": 1,
 
      "table_name": "t1",
 
      "access_type": "ALL",
 
      "rows": 7,
 
      "attached_condition": "year(t1.a) = 2010"
 
    }
 
  }
 
}
 
explain format=json  delete from t1 where year(a) = 2010;
 
EXPLAIN
 
{
 
  "query_block": {
 
    "select_id": 1,
 
    "table": {
 
      "delete": 1,
 
      "table_name": "t1",
 
      "access_type": "ALL",
 
      "rows": 7,
 
      "attached_condition": "year(t1.a) = 2010"
 
    }
 
  }
 
}



 Comments   
Comment by Oleg Smirnov [ 2023-04-11 ]

The fix is pushed to preview-11.1-mdev-8320. psergei, please review.

Comment by Sergei Petrunia [ 2023-04-12 ]

https://lists.launchpad.net/maria-developers/msg13311.html review input

Comment by Oleg Smirnov [ 2023-04-13 ]

Fixed, pushed: 

commit 799c63ee15068e92eb1a8d1c4a7103c7c5f4855a (HEAD -> preview-11.1-mdev-8320, origin/preview-11.1-mdev-8320)
 
Author: Oleg Smirnov <olernov@gmail.com>
 
Date:   Thu Apr 13 18:35:57 2023 +0700
 
 
 
    MDEV-8320 Place date conditions transformation before partition pruning
 
    
    This allows partition pruning to use date conditions

There now 5 commits in the branch, should I squash them?

Comment by Sergei Petrunia [ 2023-04-24 ]

The last patch is ok to push.

Comment by Lena Startseva [ 2023-04-25 ]

Testing done. Ok to push.

Comment by Oleg Smirnov [ 2023-04-25 ]

Pushed to 11.1

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