MDEV-30946 Index usage for DATE(datetime_column) = const does not work for DELETE and UPDATEpreview-11.1-mdev-8320

Add date conditions transformation to the execution paths
of DELETE and UPDATE commands.

Strip excessive prepared statements from the test file

4 files changed, 482 insertions, 1351 deletions

diff --git a/mysql-test/main/sargable_date_cond.result b/mysql-test/main/sargable_date_cond.result
index 86dd5cbfe19..11b6684789d 100644
--- a/mysql-test/main/sargable_date_cond.result
+++ b/mysql-test/main/sargable_date_cond.result
@@ -20,6 +20,7 @@ test.t2	analyze	status	Table is already up to date
 select count(*) from t2 where year(a) < 2018;
 count(*)
 460
+# Compare the results, they must be equal:
 select count(*) from t2 where a < '2018-01-01';
 count(*)
 460
@@ -132,55 +133,6 @@ EXPLAIN
     ]
   }
 }
-execute immediate 
-"explain format=json select * from t2 where year(a) <= ?"
-  using 2018;
-EXPLAIN
-{
-  "query_block": {
-    "select_id": 1,
-    "cost": 0.1671618,
-    "nested_loop": [
-      {
-        "table": {
-          "table_name": "t2",
-          "access_type": "ALL",
-          "possible_keys": ["a"],
-          "loops": 1,
-          "rows": 1000,
-          "cost": 0.1671618,
-          "filtered": 90.30000305,
-          "attached_condition": "t2.a <= '2018-12-31 23:59:59'"
-        }
-      }
-    ]
-  }
-}
-prepare stmt from "select count(*) from t2 where year(a) <= 2018";
-execute stmt;
-count(*)
-920
-execute stmt;
-count(*)
-920
-create or replace view v1 as select count(*) from t2 where year(a) <= 2018;
-select * from v1;
-count(*)
-920
-create or replace procedure sp() select count(*) from t2 where year(a) <= 2018;
-call sp();
-count(*)
-920
-call sp();
-count(*)
-920
-prepare stmt from "select count(*) from t2 where year(a) <= ?";
-execute stmt using 2018;
-count(*)
-920
-execute stmt using 2017;
-count(*)
-460
 select count(*) from t2 where year(a) > 2018;
 count(*)
 80
@@ -213,58 +165,6 @@ EXPLAIN
     ]
   }
 }
-execute immediate 
-"explain format=json select * from t2 where year(a) > ?"
-  using 2018;
-EXPLAIN
-{
-  "query_block": {
-    "select_id": 1,
-    "cost": 0.121025977,
-    "nested_loop": [
-      {
-        "table": {
-          "table_name": "t2",
-          "access_type": "range",
-          "possible_keys": ["a"],
-          "key": "a",
-          "key_length": "6",
-          "used_key_parts": ["a"],
-          "loops": 1,
-          "rows": 97,
-          "cost": 0.121025977,
-          "filtered": 100,
-          "index_condition": "t2.a > '2018-12-31 23:59:59'"
-        }
-      }
-    ]
-  }
-}
-prepare stmt from "select count(*) from t2 where year(a) > 2018";
-execute stmt;
-count(*)
-80
-execute stmt;
-count(*)
-80
-create or replace view v1 as select count(*) from t2 where year(a) > 2018;
-select * from v1;
-count(*)
-80
-create or replace procedure sp() select count(*) from t2 where year(a) > 2018;
-call sp();
-count(*)
-80
-call sp();
-count(*)
-80
-prepare stmt from "select count(*) from t2 where year(a) > ?";
-execute stmt using 2018;
-count(*)
-80
-execute stmt using 2017;
-count(*)
-540
 select count(*) from t2 where year(a) >= 2018;
 count(*)
 540
@@ -297,55 +197,6 @@ EXPLAIN
     ]
   }
 }
-execute immediate 
-"explain format=json select * from t2 where year(a) >= ?"
-  using 2018;
-EXPLAIN
-{
-  "query_block": {
-    "select_id": 1,
-    "cost": 0.1671618,
-    "nested_loop": [
-      {
-        "table": {
-          "table_name": "t2",
-          "access_type": "ALL",
-          "possible_keys": ["a"],
-          "loops": 1,
-          "rows": 1000,
-          "cost": 0.1671618,
-          "filtered": 54.90000153,
-          "attached_condition": "t2.a >= '2018-01-01 00:00:00'"
-        }
-      }
-    ]
-  }
-}
-prepare stmt from "select count(*) from t2 where year(a) >= 2018";
-execute stmt;
-count(*)
-540
-execute stmt;
-count(*)
-540
-create or replace view v1 as select count(*) from t2 where year(a) >= 2018;
-select * from v1;
-count(*)
-540
-create or replace procedure sp() select count(*) from t2 where year(a) >= 2018;
-call sp();
-count(*)
-540
-call sp();
-count(*)
-540
-prepare stmt from "select count(*) from t2 where year(a) >= ?";
-execute stmt using 2018;
-count(*)
-540
-execute stmt using 2019;
-count(*)
-80
 select count(*) from t2 where year(a) = 2017;
 count(*)
 460
@@ -378,55 +229,6 @@ EXPLAIN
     ]
   }
 }
-execute immediate 
-"explain format=json select * from t2 where year(a) = ?"
-  using 2017;
-EXPLAIN
-{
-  "query_block": {
-    "select_id": 1,
-    "cost": 0.1671618,
-    "nested_loop": [
-      {
-        "table": {
-          "table_name": "t2",
-          "access_type": "ALL",
-          "possible_keys": ["a"],
-          "loops": 1,
-          "rows": 1000,
-          "cost": 0.1671618,
-          "filtered": 45.09999847,
-          "attached_condition": "t2.a between '2017-01-01 00:00:00' and '2017-12-31 23:59:59'"
-        }
-      }
-    ]
-  }
-}
-prepare stmt from "select count(*) from t2 where year(a) = 2017";
-execute stmt;
-count(*)
-460
-execute stmt;
-count(*)
-460
-create or replace view v1 as select count(*) from t2 where year(a) = 2017;
-select * from v1;
-count(*)
-460
-create or replace procedure sp() select count(*) from t2 where year(a) = 2017;
-call sp();
-count(*)
-460
-call sp();
-count(*)
-460
-prepare stmt from "select count(*) from t2 where year(a) = ?";
-execute stmt using 2017;
-count(*)
-460
-execute stmt using 2019;
-count(*)
-80
 #
 # "YEAR(datetime_col) CMP year_value", reverse argument order
 #
@@ -462,55 +264,6 @@ EXPLAIN
     ]
   }
 }
-execute immediate 
-"explain format=json select * from t2 where ? < year(a)"
-  using 2017;
-EXPLAIN
-{
-  "query_block": {
-    "select_id": 1,
-    "cost": 0.1671618,
-    "nested_loop": [
-      {
-        "table": {
-          "table_name": "t2",
-          "access_type": "ALL",
-          "possible_keys": ["a"],
-          "loops": 1,
-          "rows": 1000,
-          "cost": 0.1671618,
-          "filtered": 54.90000153,
-          "attached_condition": "t2.a > '2017-12-31 23:59:59'"
-        }
-      }
-    ]
-  }
-}
-prepare stmt from "select count(*) from t2 where 2017 < year(a)";
-execute stmt;
-count(*)
-540
-execute stmt;
-count(*)
-540
-create or replace view v1 as select count(*) from t2 where 2017 < year(a);
-select * from v1;
-count(*)
-540
-create or replace procedure sp() select count(*) from t2 where 2017 < year(a);
-call sp();
-count(*)
-540
-call sp();
-count(*)
-540
-prepare stmt from "select count(*) from t2 where ? < year(a)";
-execute stmt using 2017;
-count(*)
-540
-execute stmt using 2018;
-count(*)
-80
 select count(*) from t2 where a >= '2018-01-01';
 count(*)
 540
@@ -540,55 +293,6 @@ EXPLAIN
     ]
   }
 }
-execute immediate 
-"explain format=json select * from t2 where ? <= year(a)"
-  using 2018;
-EXPLAIN
-{
-  "query_block": {
-    "select_id": 1,
-    "cost": 0.1671618,
-    "nested_loop": [
-      {
-        "table": {
-          "table_name": "t2",
-          "access_type": "ALL",
-          "possible_keys": ["a"],
-          "loops": 1,
-          "rows": 1000,
-          "cost": 0.1671618,
-          "filtered": 54.90000153,
-          "attached_condition": "t2.a >= '2018-01-01 00:00:00'"
-        }
-      }
-    ]
-  }
-}
-prepare stmt from "select count(*) from t2 where 2018 <= year(a)";
-execute stmt;
-count(*)
-540
-execute stmt;
-count(*)
-540
-create or replace view v1 as select count(*) from t2 where 2018 <= year(a);
-select * from v1;
-count(*)
-540
-create or replace procedure sp() select count(*) from t2 where 2018 <= year(a);
-call sp();
-count(*)
-540
-call sp();
-count(*)
-540
-prepare stmt from "select count(*) from t2 where ? <= year(a)";
-execute stmt using 2018;
-count(*)
-540
-execute stmt using 2019;
-count(*)
-80
 select count(*) from t2 where 2018 > year(a);
 count(*)
 460
@@ -621,55 +325,6 @@ EXPLAIN
     ]
   }
 }
-execute immediate 
-"explain format=json select * from t2 where ? > year(a)"
-  using 2018;
-EXPLAIN
-{
-  "query_block": {
-    "select_id": 1,
-    "cost": 0.1671618,
-    "nested_loop": [
-      {
-        "table": {
-          "table_name": "t2",
-          "access_type": "ALL",
-          "possible_keys": ["a"],
-          "loops": 1,
-          "rows": 1000,
-          "cost": 0.1671618,
-          "filtered": 45.09999847,
-          "attached_condition": "t2.a < '2018-01-01 00:00:00'"
-        }
-      }
-    ]
-  }
-}
-prepare stmt from "select count(*) from t2 where 2018 > year(a)";
-execute stmt;
-count(*)
-460
-execute stmt;
-count(*)
-460
-create or replace view v1 as select count(*) from t2 where 2018 > year(a);
-select * from v1;
-count(*)
-460
-create or replace procedure sp() select count(*) from t2 where 2018 > year(a);
-call sp();
-count(*)
-460
-call sp();
-count(*)
-460
-prepare stmt from "select count(*) from t2 where ? > year(a)";
-execute stmt using 2018;
-count(*)
-460
-execute stmt using 2019;
-count(*)
-920
 select count(*) from t2 where a < '2019-01-01';
 count(*)
 920
@@ -699,55 +354,6 @@ EXPLAIN
     ]
   }
 }
-execute immediate 
-"explain format=json select * from t2 where ? >= year(a)"
-  using 2018;
-EXPLAIN
-{
-  "query_block": {
-    "select_id": 1,
-    "cost": 0.1671618,
-    "nested_loop": [
-      {
-        "table": {
-          "table_name": "t2",
-          "access_type": "ALL",
-          "possible_keys": ["a"],
-          "loops": 1,
-          "rows": 1000,
-          "cost": 0.1671618,
-          "filtered": 90.30000305,
-          "attached_condition": "t2.a <= '2018-12-31 23:59:59'"
-        }
-      }
-    ]
-  }
-}
-prepare stmt from "select count(*) from t2 where 2018 >= year(a)";
-execute stmt;
-count(*)
-920
-execute stmt;
-count(*)
-920
-create or replace view v1 as select count(*) from t2 where 2018 >= year(a);
-select * from v1;
-count(*)
-920
-create or replace procedure sp() select count(*) from t2 where 2018 >= year(a);
-call sp();
-count(*)
-920
-call sp();
-count(*)
-920
-prepare stmt from "select count(*) from t2 where ? >= year(a)";
-execute stmt using 2018;
-count(*)
-920
-execute stmt using 2019;
-count(*)
-1000
 select count(*) from t2 where 2018 =  year(a);
 count(*)
 460
@@ -780,55 +386,6 @@ EXPLAIN
     ]
   }
 }
-execute immediate 
-"explain format=json select * from t2 where ? = year(a)"
-  using 2018;
-EXPLAIN
-{
-  "query_block": {
-    "select_id": 1,
-    "cost": 0.1671618,
-    "nested_loop": [
-      {
-        "table": {
-          "table_name": "t2",
-          "access_type": "ALL",
-          "possible_keys": ["a"],
-          "loops": 1,
-          "rows": 1000,
-          "cost": 0.1671618,
-          "filtered": 45.20000076,
-          "attached_condition": "t2.a between '2018-01-01 00:00:00' and '2018-12-31 23:59:59'"
-        }
-      }
-    ]
-  }
-}
-prepare stmt from "select count(*) from t2 where 2018 =  year(a)";
-execute stmt;
-count(*)
-460
-execute stmt;
-count(*)
-460
-create or replace view v1 as select count(*) from t2 where 2018 =  year(a);
-select * from v1;
-count(*)
-460
-create or replace procedure sp() select count(*) from t2 where 2018 =  year(a);
-call sp();
-count(*)
-460
-call sp();
-count(*)
-460
-prepare stmt from "select count(*) from t2 where ? = year(a)";
-execute stmt using 2018;
-count(*)
-460
-execute stmt using 2019;
-count(*)
-80
 #
 # "DATE(datetime_col) CMP date_value", basic checks
 #
@@ -864,55 +421,6 @@ EXPLAIN
     ]
   }
 }
-execute immediate 
-"explain format=json select * from t2 where date(a) < ?"
-  using '2017-06-01';
-EXPLAIN
-{
-  "query_block": {
-    "select_id": 1,
-    "cost": 0.1671618,
-    "nested_loop": [
-      {
-        "table": {
-          "table_name": "t2",
-          "access_type": "ALL",
-          "possible_keys": ["a"],
-          "loops": 1,
-          "rows": 1000,
-          "cost": 0.1671618,
-          "filtered": 18.60000038,
-          "attached_condition": "t2.a < '2017-06-01 00:00:00'"
-        }
-      }
-    ]
-  }
-}
-prepare stmt from "select count(*) from t2 where date(a) < '2017-06-01'";
-execute stmt;
-count(*)
-190
-execute stmt;
-count(*)
-190
-create or replace view v1 as select count(*) from t2 where date(a) < '2017-06-01';
-select * from v1;
-count(*)
-190
-create or replace procedure sp() select count(*) from t2 where date(a) < '2017-06-01';
-call sp();
-count(*)
-190
-call sp();
-count(*)
-190
-prepare stmt from "select count(*) from t2 where date(a) < ?";
-execute stmt using '2017-06-01';
-count(*)
-190
-execute stmt using '2017-06-05';
-count(*)
-200
 select count(*) from t2 where date(a) <= '2017-06-03';
 count(*)
 200
@@ -945,55 +453,6 @@ EXPLAIN
     ]
   }
 }
-execute immediate 
-"explain format=json select * from t2 where date(a) <= ?"
-  using '2017-06-04';
-EXPLAIN
-{
-  "query_block": {
-    "select_id": 1,
-    "cost": 0.1671618,
-    "nested_loop": [
-      {
-        "table": {
-          "table_name": "t2",
-          "access_type": "ALL",
-          "possible_keys": ["a"],
-          "loops": 1,
-          "rows": 1000,
-          "cost": 0.1671618,
-          "filtered": 19.60000038,
-          "attached_condition": "t2.a <= '2017-06-04 23:59:59'"
-        }
-      }
-    ]
-  }
-}
-prepare stmt from "select count(*) from t2 where date(a) <= '2017-06-03'";
-execute stmt;
-count(*)
-200
-execute stmt;
-count(*)
-200
-create or replace view v1 as select count(*) from t2 where date(a) <= '2017-06-03';
-select * from v1;
-count(*)
-200
-create or replace procedure sp() select count(*) from t2 where date(a) <= '2017-06-03';
-call sp();
-count(*)
-200
-call sp();
-count(*)
-200
-prepare stmt from "select count(*) from t2 where date(a) <= ?";
-execute stmt using '2017-06-03';
-count(*)
-200
-execute stmt using '2017-06-10';
-count(*)
-210
 select count(*) from t2 where date(a) > '2018-06-01';
 count(*)
 350
@@ -1026,55 +485,6 @@ EXPLAIN
     ]
   }
 }
-execute immediate 
-"explain format=json select * from t2 where date(a) > ?"
-  using '2018-06-01';
-EXPLAIN
-{
-  "query_block": {
-    "select_id": 1,
-    "cost": 0.1671618,
-    "nested_loop": [
-      {
-        "table": {
-          "table_name": "t2",
-          "access_type": "ALL",
-          "possible_keys": ["a"],
-          "loops": 1,
-          "rows": 1000,
-          "cost": 0.1671618,
-          "filtered": 36.29999924,
-          "attached_condition": "t2.a > '2018-06-01 23:59:59'"
-        }
-      }
-    ]
-  }
-}
-prepare stmt from "select count(*) from t2 where date(a) > '2018-06-01'";
-execute stmt;
-count(*)
-350
-execute stmt;
-count(*)
-350
-create or replace view v1 as select count(*) from t2 where date(a) > '2018-06-01';
-select * from v1;
-count(*)
-350
-create or replace procedure sp() select count(*) from t2 where date(a) > '2018-06-01';
-call sp();
-count(*)
-350
-call sp();
-count(*)
-350
-prepare stmt from "select count(*) from t2 where date(a) > ?";
-execute stmt using '2018-06-01';
-count(*)
-350
-execute stmt using '2018-06-05';
-count(*)
-340
 select count(*) from t2 where date(a) >= '2018-06-01';
 count(*)
 350
@@ -1107,55 +517,6 @@ EXPLAIN
     ]
   }
 }
-execute immediate 
-"explain format=json select * from t2 where date(a) >= ?"
-  using '2018-06-01';
-EXPLAIN
-{
-  "query_block": {
-    "select_id": 1,
-    "cost": 0.1671618,
-    "nested_loop": [
-      {
-        "table": {
-          "table_name": "t2",
-          "access_type": "ALL",
-          "possible_keys": ["a"],
-          "loops": 1,
-          "rows": 1000,
-          "cost": 0.1671618,
-          "filtered": 36.29999924,
-          "attached_condition": "t2.a >= '2018-06-01 00:00:00'"
-        }
-      }
-    ]
-  }
-}
-prepare stmt from "select count(*) from t2 where date(a) >= '2018-06-01'";
-execute stmt;
-count(*)
-350
-execute stmt;
-count(*)
-350
-create or replace view v1 as select count(*) from t2 where date(a) >= '2018-06-01';
-select * from v1;
-count(*)
-350
-create or replace procedure sp() select count(*) from t2 where date(a) >= '2018-06-01';
-call sp();
-count(*)
-350
-call sp();
-count(*)
-350
-prepare stmt from "select count(*) from t2 where date(a) >= ?";
-execute stmt using '2018-06-01';
-count(*)
-350
-execute stmt using '2018-06-10';
-count(*)
-340
 select count(*) from t2 where date(a) = '2017-06-02';
 count(*)
 10
@@ -1188,58 +549,6 @@ EXPLAIN
     ]
   }
 }
-execute immediate 
-"explain format=json select * from t2 where date(a) = ?"
-  using '2017-06-02';
-EXPLAIN
-{
-  "query_block": {
-    "select_id": 1,
-    "cost": 0.013679374,
-    "nested_loop": [
-      {
-        "table": {
-          "table_name": "t2",
-          "access_type": "range",
-          "possible_keys": ["a"],
-          "key": "a",
-          "key_length": "6",
-          "used_key_parts": ["a"],
-          "loops": 1,
-          "rows": 10,
-          "cost": 0.013679374,
-          "filtered": 100,
-          "index_condition": "t2.a between '2017-06-02 00:00:00' and '2017-06-02 23:59:59'"
-        }
-      }
-    ]
-  }
-}
-prepare stmt from "select count(*) from t2 where date(a) = '2017-06-02'";
-execute stmt;
-count(*)
-10
-execute stmt;
-count(*)
-10
-create or replace view v1 as select count(*) from t2 where date(a) = '2017-06-02';
-select * from v1;
-count(*)
-10
-create or replace procedure sp() select count(*) from t2 where date(a) = '2017-06-02';
-call sp();
-count(*)
-10
-call sp();
-count(*)
-10
-prepare stmt from "select count(*) from t2 where date(a) = ?";
-execute stmt using '2017-06-02';
-count(*)
-10
-execute stmt using '2017-06-05';
-count(*)
-0
 #
 # "DATE(datetime_col) CMP date_value", reverse order
 #
@@ -1275,55 +584,6 @@ EXPLAIN
     ]
   }
 }
-execute immediate 
-"explain format=json select * from t2 where ? > date(a)"
-  using '2017-06-01';
-EXPLAIN
-{
-  "query_block": {
-    "select_id": 1,
-    "cost": 0.1671618,
-    "nested_loop": [
-      {
-        "table": {
-          "table_name": "t2",
-          "access_type": "ALL",
-          "possible_keys": ["a"],
-          "loops": 1,
-          "rows": 1000,
-          "cost": 0.1671618,
-          "filtered": 18.60000038,
-          "attached_condition": "t2.a < '2017-06-01 00:00:00'"
-        }
-      }
-    ]
-  }
-}
-prepare stmt from "select count(*) from t2 where '2017-06-01' > date(a)";
-execute stmt;
-count(*)
-190
-execute stmt;
-count(*)
-190
-create or replace view v1 as select count(*) from t2 where '2017-06-01' > date(a);
-select * from v1;
-count(*)
-190
-create or replace procedure sp() select count(*) from t2 where '2017-06-01' > date(a);
-call sp();
-count(*)
-190
-call sp();
-count(*)
-190
-prepare stmt from "select count(*) from t2 where ? > date(a)";
-execute stmt using '2017-06-01';
-count(*)
-190
-execute stmt using '2017-06-05';
-count(*)
-200
 select count(*) from t2 where '2017-06-03' >= date(a);
 count(*)
 200
@@ -1356,55 +616,6 @@ EXPLAIN
     ]
   }
 }
-execute immediate 
-"explain format=json select * from t2 where ? >= date(a)"
-  using '2017-06-03';
-EXPLAIN
-{
-  "query_block": {
-    "select_id": 1,
-    "cost": 0.1671618,
-    "nested_loop": [
-      {
-        "table": {
-          "table_name": "t2",
-          "access_type": "ALL",
-          "possible_keys": ["a"],
-          "loops": 1,
-          "rows": 1000,
-          "cost": 0.1671618,
-          "filtered": 19.60000038,
-          "attached_condition": "t2.a <= '2017-06-03 23:59:59'"
-        }
-      }
-    ]
-  }
-}
-prepare stmt from "select count(*) from t2 where '2017-06-03' >= date(a)";
-execute stmt;
-count(*)
-200
-execute stmt;
-count(*)
-200
-create or replace view v1 as select count(*) from t2 where '2017-06-03' >= date(a);
-select * from v1;
-count(*)
-200
-create or replace procedure sp() select count(*) from t2 where '2017-06-03' >= date(a);
-call sp();
-count(*)
-200
-call sp();
-count(*)
-200
-prepare stmt from "select count(*) from t2 where ? >= date(a)";
-execute stmt using '2017-06-03';
-count(*)
-200
-execute stmt using '2017-06-12';
-count(*)
-210
 select count(*) from t2 where '2018-06-01' < date(a);
 count(*)
 350
@@ -1437,55 +648,6 @@ EXPLAIN
     ]
   }
 }
-execute immediate 
-"explain format=json select * from t2 where ? < date(a)"
-  using '2017-06-01';
-EXPLAIN
-{
-  "query_block": {
-    "select_id": 1,
-    "cost": 0.1671618,
-    "nested_loop": [
-      {
-        "table": {
-          "table_name": "t2",
-          "access_type": "ALL",
-          "possible_keys": ["a"],
-          "loops": 1,
-          "rows": 1000,
-          "cost": 0.1671618,
-          "filtered": 81.40000153,
-          "attached_condition": "t2.a > '2017-06-01 23:59:59'"
-        }
-      }
-    ]
-  }
-}
-prepare stmt from "select count(*) from t2 where '2018-06-01' < date(a)";
-execute stmt;
-count(*)
-350
-execute stmt;
-count(*)
-350
-create or replace view v1 as select count(*) from t2 where '2018-06-01' < date(a);
-select * from v1;
-count(*)
-350
-create or replace procedure sp() select count(*) from t2 where '2018-06-01' < date(a);
-call sp();
-count(*)
-350
-call sp();
-count(*)
-350
-prepare stmt from "select count(*) from t2 where ? < date(a)";
-execute stmt using '2018-06-02';
-count(*)
-350
-execute stmt using '2018-06-15';
-count(*)
-330
 select count(*) from t2 where '2018-06-01' <= date(a);
 count(*)
 350
@@ -1518,55 +680,6 @@ EXPLAIN
     ]
   }
 }
-execute immediate 
-"explain format=json select * from t2 where ? <= date(a)"
-  using '2017-06-01';
-EXPLAIN
-{
-  "query_block": {
-    "select_id": 1,
-    "cost": 0.1671618,
-    "nested_loop": [
-      {
-        "table": {
-          "table_name": "t2",
-          "access_type": "ALL",
-          "possible_keys": ["a"],
-          "loops": 1,
-          "rows": 1000,
-          "cost": 0.1671618,
-          "filtered": 81.40000153,
-          "attached_condition": "t2.a >= '2017-06-01 00:00:00'"
-        }
-      }
-    ]
-  }
-}
-prepare stmt from "select count(*) from t2 where '2018-06-01' <= date(a)";
-execute stmt;
-count(*)
-350
-execute stmt;
-count(*)
-350
-create or replace view v1 as select count(*) from t2 where '2018-06-01' <= date(a);
-select * from v1;
-count(*)
-350
-create or replace procedure sp() select count(*) from t2 where '2018-06-01' <= date(a);
-call sp();
-count(*)
-350
-call sp();
-count(*)
-350
-prepare stmt from "select count(*) from t2 where ? <= date(a)";
-execute stmt using '2018-06-01';
-count(*)
-350
-execute stmt using '2018-06-15';
-count(*)
-330
 select count(*) from t2 where '2017-06-02' = date(a);
 count(*)
 10
@@ -1599,58 +712,6 @@ EXPLAIN
     ]
   }
 }
-execute immediate 
-"explain format=json select * from t2 where ? = date(a)"
-  using '2017-06-02';
-EXPLAIN
-{
-  "query_block": {
-    "select_id": 1,
-    "cost": 0.013679374,
-    "nested_loop": [
-      {
-        "table": {
-          "table_name": "t2",
-          "access_type": "range",
-          "possible_keys": ["a"],
-          "key": "a",
-          "key_length": "6",
-          "used_key_parts": ["a"],
-          "loops": 1,
-          "rows": 10,
-          "cost": 0.013679374,
-          "filtered": 100,
-          "index_condition": "t2.a between '2017-06-02 00:00:00' and '2017-06-02 23:59:59'"
-        }
-      }
-    ]
-  }
-}
-prepare stmt from "select count(*) from t2 where '2017-06-02' = date(a)";
-execute stmt;
-count(*)
-10
-execute stmt;
-count(*)
-10
-create or replace view v1 as select count(*) from t2 where '2017-06-02' = date(a);
-select * from v1;
-count(*)
-10
-create or replace procedure sp() select count(*) from t2 where '2017-06-02' = date(a);
-call sp();
-count(*)
-10
-call sp();
-count(*)
-10
-prepare stmt from "select count(*) from t2 where ? = date(a)";
-execute stmt using '2017-06-03';
-count(*)
-0
-execute stmt using '2017-06-10';
-count(*)
-10
 # Check rewrite of a more complicated query
 explain format=json select * from t2 as t21 force index(a),
 t2 as t22 force index(a)
@@ -1892,31 +953,6 @@ EXPLAIN
     ]
   }
 }
-prepare stmt from "select count(*) from t2 where date(b)< '2017-06-03'";
-execute stmt;
-count(*)
-220
-execute stmt;
-count(*)
-220
-create or replace view v1 as select count(*) from t2 where date(b)< '2017-06-03';
-select * from v1;
-count(*)
-220
-create or replace procedure sp() select count(*) from t2 where date(b)< '2017-06-03';
-call sp();
-count(*)
-220
-call sp();
-count(*)
-220
-prepare stmt from "select count(*) from t2 where date(b) < ?";
-execute stmt using '2017-06-03';
-count(*)
-220
-execute stmt using '2017-06-10';
-count(*)
-230
 select count(*) from t2 where date(b)= '2017-06-04';
 count(*)
 10
@@ -1949,31 +985,6 @@ EXPLAIN
     ]
   }
 }
-prepare stmt from "select count(*) from t2 where date(b)= '2017-06-04'";
-execute stmt;
-count(*)
-10
-execute stmt;
-count(*)
-10
-create or replace view v1 as select count(*) from t2 where date(b)= '2017-06-04';
-select * from v1;
-count(*)
-10
-create or replace procedure sp() select count(*) from t2 where date(b)= '2017-06-04';
-call sp();
-count(*)
-10
-call sp();
-count(*)
-10
-prepare stmt from "select count(*) from t2 where date(b) = ?";
-execute stmt using '2017-06-04';
-count(*)
-10
-execute stmt using '2017-06-10';
-count(*)
-0
 #
 # Check actual query results
 #
@@ -2047,6 +1058,8 @@ pk	a	b
 # Test the TIMESTAMP column
 #
 create table t3 (a timestamp, b date, key(a));
+# Insert data starting from 2016 since that year had a leap second
+# (https://en.wikipedia.org/wiki/Leap_second)
 set time_zone="UTC";
 insert into t3
 select
@@ -2089,60 +1102,6 @@ EXPLAIN
     ]
   }
 }
-# Check rewrite for a prepared statement:
-execute immediate
-"explain format=json select * from t3 force index(a) where year(a) < ?"
-  using 2017;
-EXPLAIN
-{
-  "query_block": {
-    "select_id": 1,
-    "cost": 0.562991479,
-    "nested_loop": [
-      {
-        "table": {
-          "table_name": "t3",
-          "access_type": "range",
-          "possible_keys": ["a"],
-          "key": "a",
-          "key_length": "5",
-          "used_key_parts": ["a"],
-          "loops": 1,
-          "rows": 455,
-          "cost": 0.562991479,
-          "filtered": 100,
-          "index_condition": "t3.a < '2017-01-01 00:00:00'"
-        }
-      }
-    ]
-  }
-}
-prepare stmt from "select count(*) from t3 force index(a) where year(a)= 2016";
-execute stmt;
-count(*)
-460
-execute stmt;
-count(*)
-460
-create or replace view v1 as select count(*) from t3 force index(a) where year(a)= 2016;
-select * from v1;
-count(*)
-460
-create or replace procedure sp() select count(*) from t3 force index(a) where year(a)= 2016;
-call sp();
-count(*)
-460
-call sp();
-count(*)
-460
-# Prepared statement with a placeholder
-prepare stmt from "select count(*) from t3 where year(a) < ?";
-execute stmt using 2017;
-count(*)
-460
-execute stmt using 2018;
-count(*)
-920
 set time_zone= @@global.time_zone;
 #
 # Incorrect const values processing (no rewrite is possible)
@@ -2669,3 +1628,380 @@ EXPLAIN
 drop table t0,t1,t2,t3,t4,t5,t6;
 drop view v1;
 drop procedure sp;
+#
+# MDEV-30946 Index usage for DATE(datetime_column) = const
+#            does not work for DELETE and UPDATE
+#
+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);
+# YEAR() conditions, UPDATE
+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": "range",
+      "possible_keys": ["a"],
+      "key": "a",
+      "key_length": "6",
+      "used_key_parts": ["a"],
+      "rows": 2,
+      "attached_condition": "t1.a between '2010-01-01 00:00:00' and '2010-12-31 23:59:59'"
+    }
+  }
+}
+update t1 set c = 0 where year(a) = 2010;
+select * from t1;
+pk	a	c
+1	2009-11-29 13:43:32	2
+2	2009-11-29 03:23:32	2
+3	2009-10-16 05:56:32	2
+4	2010-11-29 13:43:32	0
+5	2010-10-16 05:56:32	0
+6	2011-11-29 13:43:32	2
+7	2012-10-16 05:56:32	2
+explain format=json update t1 set c = 1
+where c < (select count(*) from t1 where year(a) = 2010);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.011130435,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "loops": 1,
+          "rows": 7,
+          "cost": 0.011130435,
+          "filtered": 100,
+          "attached_condition": "t1.c < (subquery#2)"
+        }
+      }
+    ],
+    "subqueries": [
+      {
+        "query_block": {
+          "select_id": 2,
+          "cost": 0.001617224,
+          "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.001617224,
+                "filtered": 100,
+                "attached_condition": "t1.a between '2010-01-01 00:00:00' and '2010-12-31 23:59:59'",
+                "using_index": true
+              }
+            }
+          ]
+        }
+      }
+    ]
+  }
+}
+explain format=json update t1 set c = 0
+where year(a) = 2010 and c < (select count(*) 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'",
+          "attached_condition": "t1.c < (subquery#2)"
+        }
+      }
+    ],
+    "subqueries": [
+      {
+        "query_block": {
+          "select_id": 2,
+          "cost": 0.001617224,
+          "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.001617224,
+                "filtered": 100,
+                "attached_condition": "t1.a between '2010-01-01 00:00:00' and '2010-12-31 23:59:59'",
+                "using_index": true
+              }
+            }
+          ]
+        }
+      }
+    ]
+  }
+}
+# Multi-table update
+create table t2 (a int);
+insert into t2 values (4),(5),(6);
+explain format=json update t1, t2 set c = 0
+where year(t1.a) = 2010 and t1.c = t2.a;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.014992165,
+    "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,
+          "attached_condition": "t1.a between '2010-01-01 00:00:00' and '2010-12-31 23:59:59'"
+        }
+      },
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "ALL",
+          "loops": 2,
+          "rows": 3,
+          "cost": 0.011183743,
+          "filtered": 100,
+          "attached_condition": "t2.a = t1.c"
+        }
+      }
+    ]
+  }
+}
+prepare stmt from "update t1 set c = 0 where year(a) = 2010";
+execute stmt;
+execute stmt;
+# YEAR() conditions, DELETE
+explain format=json delete from t1 where year(a) = 2010;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "delete": 1,
+      "table_name": "t1",
+      "access_type": "range",
+      "possible_keys": ["a"],
+      "key": "a",
+      "key_length": "6",
+      "used_key_parts": ["a"],
+      "rows": 2,
+      "attached_condition": "t1.a between '2010-01-01 00:00:00' and '2010-12-31 23:59:59'"
+    }
+  }
+}
+delete from t1 where year(a) = 2010;
+select * from t1;
+pk	a	c
+1	2009-11-29 13:43:32	2
+2	2009-11-29 03:23:32	2
+3	2009-10-16 05:56:32	2
+6	2011-11-29 13:43:32	2
+7	2012-10-16 05:56:32	2
+explain format=json delete from t1
+where c < (select count(*) from t1 where year(a) = 2010);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.010817625,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "loops": 1,
+          "rows": 5,
+          "cost": 0.010817625,
+          "filtered": 100,
+          "attached_condition": "t1.c < (subquery#2)"
+        }
+      }
+    ],
+    "subqueries": [
+      {
+        "query_block": {
+          "select_id": 2,
+          "cost": 0.001478954,
+          "nested_loop": [
+            {
+              "table": {
+                "table_name": "t1",
+                "access_type": "range",
+                "possible_keys": ["a"],
+                "key": "a",
+                "key_length": "6",
+                "used_key_parts": ["a"],
+                "loops": 1,
+                "rows": 1,
+                "cost": 0.001478954,
+                "filtered": 100,
+                "attached_condition": "t1.a between '2010-01-01 00:00:00' and '2010-12-31 23:59:59'",
+                "using_index": true
+              }
+            }
+          ]
+        }
+      }
+    ]
+  }
+}
+delete from t1 where c < (select count(*) from t1 where year(a) = 2010);
+prepare stmt from "delete from t1 where year(a) = 2009";
+execute stmt;
+execute stmt;
+select * from t1;
+pk	a	c
+6	2011-11-29 13:43:32	2
+7	2012-10-16 05:56:32	2
+# DATE() conditions, UPDATE
+explain format=json update t1 set c = 0 where date(a) = '2010-10-16';
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "update": 1,
+      "table_name": "t1",
+      "access_type": "range",
+      "possible_keys": ["a"],
+      "key": "a",
+      "key_length": "6",
+      "used_key_parts": ["a"],
+      "rows": 1,
+      "attached_condition": "t1.a between '2010-10-16 00:00:00' and '2010-10-16 23:59:59'"
+    }
+  }
+}
+explain format=json update t1 set c = 0 where date(a) <= '2011-10-16';
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "update": 1,
+      "table_name": "t1",
+      "access_type": "range",
+      "possible_keys": ["a"],
+      "key": "a",
+      "key_length": "6",
+      "used_key_parts": ["a"],
+      "rows": 1,
+      "attached_condition": "t1.a <= '2011-10-16 23:59:59'"
+    }
+  }
+}
+prepare stmt from "update t1 set c = 3 where date(a) = '2011-11-29'";
+execute stmt;
+execute stmt;
+select * from t1;
+pk	a	c
+6	2011-11-29 13:43:32	3
+7	2012-10-16 05:56:32	2
+# DATE() conditions, DELETE
+explain format=json delete from t1 where date(a) = '2010-10-16';
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "delete": 1,
+      "table_name": "t1",
+      "access_type": "range",
+      "possible_keys": ["a"],
+      "key": "a",
+      "key_length": "6",
+      "used_key_parts": ["a"],
+      "rows": 1,
+      "attached_condition": "t1.a between '2010-10-16 00:00:00' and '2010-10-16 23:59:59'"
+    }
+  }
+}
+explain format=json delete from t1 where date(a) <= '2011-10-16';
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "delete": 1,
+      "table_name": "t1",
+      "access_type": "range",
+      "possible_keys": ["a"],
+      "key": "a",
+      "key_length": "6",
+      "used_key_parts": ["a"],
+      "rows": 1,
+      "attached_condition": "t1.a <= '2011-10-16 23:59:59'"
+    }
+  }
+}
+prepare stmt from "delete from t1 where date(a) <= '2012-01-01'";
+execute stmt;
+execute stmt;
+select * from t1;
+pk	a	c
+7	2012-10-16 05:56:32	2
+# Test partition pruning
+create table t3 (
+a datetime,
+key(a)
+) partition by range(year(a)) (
+partition p0 values less than (2022),
+partition p1 values less than (MAXVALUE)
+);
+insert into t3
+select date_add('2020-01-01', interval seq*10 day)
+from seq_1_to_100;
+# Must be only "p0" partition
+explain partitions select * from t3 where year(a) = 2020;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t3	p0	range	a	a	6	NULL	36	Using where; Using index
+explain partitions delete from t3 where year(a) = 2020;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t3	p0	ALL	a	NULL	NULL	NULL	73	Using where
+explain partitions update t3 set a = a + 1 where year(a) = 2020;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t3	p0	ALL	a	NULL	NULL	NULL	73	Using where; Using buffer
+drop tables t1,t2,t3;
diff --git a/mysql-test/main/sargable_date_cond.test b/mysql-test/main/sargable_date_cond.test
index 3f1622c590b..e1fa08177d8 100644
--- a/mysql-test/main/sargable_date_cond.test
+++ b/mysql-test/main/sargable_date_cond.test
@@ -1,3 +1,6 @@
+--source include/have_partition.inc
+--source include/have_sequence.inc
+
 #
 # MDEV-8320: Allow index usage for DATE(datetime_column) = const
 #
@@ -27,6 +30,7 @@ analyze table t2;
 --echo #
 let $q= select count(*) from t2 where year(a) < 2018;
 eval $q;
+--echo # Compare the results, they must be equal:
 select count(*) from t2 where a < '2018-01-01';
 eval explain format=json $q;
 --echo # Check rewrite for a prepared statement:
@@ -50,78 +54,21 @@ let $q= select count(*) from t2 where year(a) <= 2018;
 eval $q;
 select count(*) from t2 where a < '2019-01-01';
 eval explain format=json $q;
-execute immediate 
-  "explain format=json select * from t2 where year(a) <= ?"
-  using 2018;
-eval prepare stmt from "$q";
-execute stmt;
-execute stmt;
-eval create or replace view v1 as $q;
-select * from v1;
-eval create or replace procedure sp() $q;
-call sp();
-call sp();
-prepare stmt from "select count(*) from t2 where year(a) <= ?";
-execute stmt using 2018;
-execute stmt using 2017;
-
 
 let $q= select count(*) from t2 where year(a) > 2018;
 eval $q;
 select count(*) from t2 where a > '2018-12-31 23:59:59.999999';
 eval explain format=json $q;
-execute immediate 
-  "explain format=json select * from t2 where year(a) > ?"
-  using 2018;
-eval prepare stmt from "$q";
-execute stmt;
-execute stmt;
-eval create or replace view v1 as $q;
-select * from v1;
-eval create or replace procedure sp() $q;
-call sp();
-call sp();
-prepare stmt from "select count(*) from t2 where year(a) > ?";
-execute stmt using 2018;
-execute stmt using 2017;
 
 let $q= select count(*) from t2 where year(a) >= 2018;
 eval $q;
 select count(*) from t2 where a >= '2018-01-01';
 eval explain format=json $q;
-execute immediate 
-  "explain format=json select * from t2 where year(a) >= ?"
-  using 2018;
-eval prepare stmt from "$q";
-execute stmt;
-execute stmt;
-eval create or replace view v1 as $q;
-select * from v1;
-eval create or replace procedure sp() $q;
-call sp();
-call sp();
-prepare stmt from "select count(*) from t2 where year(a) >= ?";
-execute stmt using 2018;
-execute stmt using 2019;
 
 let $q= select count(*) from t2 where year(a) = 2017;
 eval $q;
 select count(*) from t2 where a >= '2017-01-01' and a < '2018-01-01';
 eval explain format=json $q;
-execute immediate 
-  "explain format=json select * from t2 where year(a) = ?"
-  using 2017;
-eval prepare stmt from "$q";
-execute stmt;
-execute stmt;
-eval create or replace view v1 as $q;
-select * from v1;
-eval create or replace procedure sp() $q;
-call sp();
-call sp();
-prepare stmt from "select count(*) from t2 where year(a) = ?";
-execute stmt using 2017;
-execute stmt using 2019;
 
 --echo #
 --echo # "YEAR(datetime_col) CMP year_value", reverse argument order
@@ -130,94 +77,24 @@ let $q= select count(*) from t2 where 2017 < year(a);
 eval $q;
 select count(*) from t2 where a >= '2018-01-01';
 eval explain format=json $q;
-execute immediate 
-  "explain format=json select * from t2 where ? < year(a)"
-  using 2017;
-eval prepare stmt from "$q";
-execute stmt;
-execute stmt;
-eval create or replace view v1 as $q;
-select * from v1;
-eval create or replace procedure sp() $q;
-call sp();
-call sp();
-prepare stmt from "select count(*) from t2 where ? < year(a)";
-execute stmt using 2017;
-execute stmt using 2018;
 
 let $q= select count(*) from t2 where 2018 <= year(a);
 select count(*) from t2 where a >= '2018-01-01';
 eval explain format=json $q;
-execute immediate 
-  "explain format=json select * from t2 where ? <= year(a)"
-  using 2018;
-eval prepare stmt from "$q";
-execute stmt;
-execute stmt;
-eval create or replace view v1 as $q;
-select * from v1;
-eval create or replace procedure sp() $q;
-call sp();
-call sp();
-prepare stmt from "select count(*) from t2 where ? <= year(a)";
-execute stmt using 2018;
-execute stmt using 2019;
 
 let $q= select count(*) from t2 where 2018 > year(a);
 eval $q;
 select count(*) from t2 where a < '2018-01-01';
 eval explain format=json $q;
-execute immediate 
-  "explain format=json select * from t2 where ? > year(a)"
-  using 2018;
-eval prepare stmt from "$q";
-execute stmt;
-execute stmt;
-eval create or replace view v1 as $q;
-select * from v1;
-eval create or replace procedure sp() $q;
-call sp();
-call sp();
-prepare stmt from "select count(*) from t2 where ? > year(a)";
-execute stmt using 2018;
-execute stmt using 2019;
 
 let $q= select count(*) from t2 where 2018 >= year(a);
 select count(*) from t2 where a < '2019-01-01';
 eval explain format=json $q;
-execute immediate 
-  "explain format=json select * from t2 where ? >= year(a)"
-  using 2018;
-eval prepare stmt from "$q";
-execute stmt;
-execute stmt;
-eval create or replace view v1 as $q;
-select * from v1;
-eval create or replace procedure sp() $q;
-call sp();
-call sp();
-prepare stmt from "select count(*) from t2 where ? >= year(a)";
-execute stmt using 2018;
-execute stmt using 2019;
 
 let $q= select count(*) from t2 where 2018 =  year(a);
 eval $q;
 select count(*) from t2 where a >= '2018-01-01' and a < '2019-01-01';
 eval explain format=json $q;
-execute immediate 
-  "explain format=json select * from t2 where ? = year(a)"
-  using 2018;
-eval prepare stmt from "$q";
-execute stmt;
-execute stmt;
-eval create or replace view v1 as $q;
-select * from v1;
-eval create or replace procedure sp() $q;
-call sp();
-call sp();
-prepare stmt from "select count(*) from t2 where ? = year(a)";
-execute stmt using 2018;
-execute stmt using 2019;
 
 --echo #
 --echo # "DATE(datetime_col) CMP date_value", basic checks
@@ -226,96 +103,26 @@ let $q= select count(*) from t2 where date(a) < '2017-06-01';
 eval $q;
 select count(*) from t2 where a < '2017-06-01';
 eval explain format=json $q;
-execute immediate 
-  "explain format=json select * from t2 where date(a) < ?"
-  using '2017-06-01';
-eval prepare stmt from "$q";
-execute stmt;
-execute stmt;
-eval create or replace view v1 as $q;
-select * from v1;
-eval create or replace procedure sp() $q;
-call sp();
-call sp();
-prepare stmt from "select count(*) from t2 where date(a) < ?";
-execute stmt using '2017-06-01';
-execute stmt using '2017-06-05';
 
 let $q= select count(*) from t2 where date(a) <= '2017-06-03';
 eval $q;
 select count(*) from t2 where a < '2017-06-04';
 eval explain format=json $q;
-execute immediate 
-  "explain format=json select * from t2 where date(a) <= ?"
-  using '2017-06-04';
-eval prepare stmt from "$q";
-execute stmt;
-execute stmt;
-eval create or replace view v1 as $q;
-select * from v1;
-eval create or replace procedure sp() $q;
-call sp();
-call sp();
-prepare stmt from "select count(*) from t2 where date(a) <= ?";
-execute stmt using '2017-06-03';
-execute stmt using '2017-06-10';
 
 let $q= select count(*) from t2 where date(a) > '2018-06-01';
 eval $q;
 select count(*) from t2 where a >= '2018-06-02';
 eval explain format=json $q;
-execute immediate 
-  "explain format=json select * from t2 where date(a) > ?"
-  using '2018-06-01';
-eval prepare stmt from "$q";
-execute stmt;
-execute stmt;
-eval create or replace view v1 as $q;
-select * from v1;
-eval create or replace procedure sp() $q;
-call sp();
-call sp();
-prepare stmt from "select count(*) from t2 where date(a) > ?";
-execute stmt using '2018-06-01';
-execute stmt using '2018-06-05';
 
 let $q= select count(*) from t2 where date(a) >= '2018-06-01';
 eval $q;
 select count(*) from t2 where a >= '2018-06-01';
 eval explain format=json $q;
-execute immediate 
-  "explain format=json select * from t2 where date(a) >= ?"
-  using '2018-06-01';
-eval prepare stmt from "$q";
-execute stmt;
-execute stmt;
-eval create or replace view v1 as $q;
-select * from v1;
-eval create or replace procedure sp() $q;
-call sp();
-call sp();
-prepare stmt from "select count(*) from t2 where date(a) >= ?";
-execute stmt using '2018-06-01';
-execute stmt using '2018-06-10';
 
 let $q= select count(*) from t2 where date(a) = '2017-06-02';
 eval $q;
 select count(*) from t2 where a >= '2017-06-02' and a < '2017-06-03';
 eval explain format=json $q;
-execute immediate 
-  "explain format=json select * from t2 where date(a) = ?"
-  using '2017-06-02';
-eval prepare stmt from "$q";
-execute stmt;
-execute stmt;
-eval create or replace view v1 as $q;
-select * from v1;
-eval create or replace procedure sp() $q;
-call sp();
-call sp();
-prepare stmt from "select count(*) from t2 where date(a) = ?";
-execute stmt using '2017-06-02';
-execute stmt using '2017-06-05';
 
 --echo #
 --echo # "DATE(datetime_col) CMP date_value", reverse order
@@ -324,96 +131,26 @@ let $q= select count(*) from t2 where '2017-06-01' > date(a);
 eval $q;
 select count(*) from t2 where '2017-06-01' > a;
 eval explain format=json $q;
-execute immediate 
-  "explain format=json select * from t2 where ? > date(a)"
-  using '2017-06-01';
-eval prepare stmt from "$q";
-execute stmt;
-execute stmt;
-eval create or replace view v1 as $q;
-select * from v1;
-eval create or replace procedure sp() $q;
-call sp();
-call sp();
-prepare stmt from "select count(*) from t2 where ? > date(a)";
-execute stmt using '2017-06-01';
-execute stmt using '2017-06-05';
 
 let $q= select count(*) from t2 where '2017-06-03' >= date(a);
 eval $q;
 select count(*) from t2 where '2017-06-03' >= a;
 eval explain format=json $q;
-execute immediate 
-  "explain format=json select * from t2 where ? >= date(a)"
-  using '2017-06-03';
-eval prepare stmt from "$q";
-execute stmt;
-execute stmt;
-eval create or replace view v1 as $q;
-select * from v1;
-eval create or replace procedure sp() $q;
-call sp();
-call sp();
-prepare stmt from "select count(*) from t2 where ? >= date(a)";
-execute stmt using '2017-06-03';
-execute stmt using '2017-06-12';
 
 let $q= select count(*) from t2 where '2018-06-01' < date(a);
 eval $q;
 select count(*) from t2 where '2018-06-02' <= a;
 eval explain format=json $q;
-execute immediate 
-  "explain format=json select * from t2 where ? < date(a)"
-  using '2017-06-01';
-eval prepare stmt from "$q";
-execute stmt;
-execute stmt;
-eval create or replace view v1 as $q;
-select * from v1;
-eval create or replace procedure sp() $q;
-call sp();
-call sp();
-prepare stmt from "select count(*) from t2 where ? < date(a)";
-execute stmt using '2018-06-02';
-execute stmt using '2018-06-15';
 
 let $q= select count(*) from t2 where '2018-06-01' <= date(a);
 eval $q;
 select count(*) from t2 where '2018-06-01' <= a;
 eval explain format=json $q;
-execute immediate 
-  "explain format=json select * from t2 where ? <= date(a)"
-  using '2017-06-01';
-eval prepare stmt from "$q";
-execute stmt;
-execute stmt;
-eval create or replace view v1 as $q;
-select * from v1;
-eval create or replace procedure sp() $q;
-call sp();
-call sp();
-prepare stmt from "select count(*) from t2 where ? <= date(a)";
-execute stmt using '2018-06-01';
-execute stmt using '2018-06-15';
 
 let $q= select count(*) from t2 where '2017-06-02' = date(a);
 eval $q;
 select count(*) from t2 where a >= '2017-06-02' and a < '2017-06-03';
 eval explain format=json $q;
-execute immediate 
-  "explain format=json select * from t2 where ? = date(a)"
-  using '2017-06-02';
-eval prepare stmt from "$q";
-execute stmt;
-execute stmt;
-eval create or replace view v1 as $q;
-select * from v1;
-eval create or replace procedure sp() $q;
-call sp();
-call sp();
-prepare stmt from "select count(*) from t2 where ? = date(a)";
-execute stmt using '2017-06-03';
-execute stmt using '2017-06-10';
 
 --echo # Check rewrite of a more complicated query
 explain format=json select * from t2 as t21 force index(a),
@@ -439,33 +176,11 @@ let $q= select count(*) from t2 where date(b)< '2017-06-03';
 eval $q;
 select count(*) from t2 where b < '2017-06-03';
 eval explain format=json $q;
-eval prepare stmt from "$q";
-execute stmt;
-execute stmt;
-eval create or replace view v1 as $q;
-select * from v1;
-eval create or replace procedure sp() $q;
-call sp();
-call sp();
-prepare stmt from "select count(*) from t2 where date(b) < ?";
-execute stmt using '2017-06-03';
-execute stmt using '2017-06-10';
 
 let $q= select count(*) from t2 where date(b)= '2017-06-04';
 eval $q;
 select count(*) from t2 where b >= '2017-06-04' and b < '2017-06-05';
 eval explain format=json $q;
-eval prepare stmt from "$q";
-execute stmt;
-execute stmt;
-eval create or replace view v1 as $q;
-select * from v1;
-eval create or replace procedure sp() $q;
-call sp();
-call sp();
-prepare stmt from "select count(*) from t2 where date(b) = ?";
-execute stmt using '2017-06-04';
-execute stmt using '2017-06-10';
 
 --echo #
 --echo # Check actual query results
@@ -490,8 +205,8 @@ select * from t2 force index(a) where date(a)='2006-12-31';
 --echo # Test the TIMESTAMP column
 --echo #
 create table t3 (a timestamp, b date, key(a));
-# Insert data starting from 2016 since that year had a leap second
-# (https://en.wikipedia.org/wiki/Leap_second)
+--echo # Insert data starting from 2016 since that year had a leap second
+--echo # (https://en.wikipedia.org/wiki/Leap_second)
 set time_zone="UTC"; # To make sure we avoid daylight saving time shifts
 insert into t3
   select
@@ -507,22 +222,6 @@ select count(*) from t3 force index(a) where a >= '2016-01-01 00:00:00'
     and a <= '2016-12-31 23:59:59.999999';
 explain format=json
   select count(*) from t3 force index(a) where year(a)= 2016;
---echo # Check rewrite for a prepared statement:
-execute immediate
-  "explain format=json select * from t3 force index(a) where year(a) < ?"
-  using 2017;
-eval prepare stmt from "$q";
-execute stmt;
-execute stmt;
-eval create or replace view v1 as $q;
-select * from v1;
-eval create or replace procedure sp() $q;
-call sp();
-call sp();
---echo # Prepared statement with a placeholder
-prepare stmt from "select count(*) from t3 where year(a) < ?";
-execute stmt using 2017;
-execute stmt using 2018;
 set time_zone= @@global.time_zone;
 
 --echo #
@@ -578,3 +277,87 @@ explain format=json select * from t6 where date(a) = '2009-12-01';
 drop table t0,t1,t2,t3,t4,t5,t6;
 drop view v1;
 drop procedure sp;
+
+--echo #
+--echo # MDEV-30946 Index usage for DATE(datetime_column) = const
+--echo #            does not work for DELETE and UPDATE
+--echo #
+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);
+
+--echo # YEAR() conditions, UPDATE
+explain format=json update t1 set c = 0 where year(a) = 2010;
+update t1 set c = 0 where year(a) = 2010;
+select * from t1;
+explain format=json update t1 set c = 1
+  where c < (select count(*) from t1 where year(a) = 2010);
+explain format=json update t1 set c = 0
+  where year(a) = 2010 and c < (select count(*) from t1 where year(a) = 2010);
+
+--echo # Multi-table update
+create table t2 (a int);
+insert into t2 values (4),(5),(6);
+explain format=json update t1, t2 set c = 0
+  where year(t1.a) = 2010 and t1.c = t2.a;
+
+prepare stmt from "update t1 set c = 0 where year(a) = 2010";
+execute stmt;
+execute stmt;
+
+--echo # YEAR() conditions, DELETE
+explain format=json delete from t1 where year(a) = 2010;
+delete from t1 where year(a) = 2010;
+select * from t1;
+explain format=json delete from t1
+  where c < (select count(*) from t1 where year(a) = 2010);
+delete from t1 where c < (select count(*) from t1 where year(a) = 2010);
+
+prepare stmt from "delete from t1 where year(a) = 2009";
+execute stmt;
+execute stmt;
+select * from t1;
+
+--echo # DATE() conditions, UPDATE
+explain format=json update t1 set c = 0 where date(a) = '2010-10-16';
+explain format=json update t1 set c = 0 where date(a) <= '2011-10-16';
+prepare stmt from "update t1 set c = 3 where date(a) = '2011-11-29'";
+execute stmt;
+execute stmt;
+select * from t1;
+
+--echo # DATE() conditions, DELETE
+explain format=json delete from t1 where date(a) = '2010-10-16';
+explain format=json delete from t1 where date(a) <= '2011-10-16';
+prepare stmt from "delete from t1 where date(a) <= '2012-01-01'";
+execute stmt;
+execute stmt;
+select * from t1;
+
+--echo # Test partition pruning
+create table t3 (
+  a datetime,
+  key(a)
+) partition by range(year(a)) (
+partition p0 values less than (2022),
+partition p1 values less than (MAXVALUE)
+);
+
+insert into t3
+select date_add('2020-01-01', interval seq*10 day)
+from seq_1_to_100;
+
+--echo # Must be only "p0" partition
+explain partitions select * from t3 where year(a) = 2020;
+
+explain partitions delete from t3 where year(a) = 2020;
+
+explain partitions update t3 set a = a + 1 where year(a) = 2020;
+
+drop tables t1,t2,t3;
diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc
index 93ec11ca063..dcf61e9b085 100644
--- a/sql/sql_delete.cc
+++ b/sql/sql_delete.cc
@@ -464,6 +464,12 @@ bool Sql_cmd_delete::delete_from_single_table(THD *thd)
         goto produce_explain_and_leave;
     }
   }
+  if (conds && thd->lex->are_date_funcs_used())
+  {
+    /* Rewrite datetime comparison conditions into sargable */
+    conds= conds->top_level_transform(thd, &Item::date_conds_transformer,
+                                      (uchar *) 0);
+  }
 
 #ifdef WITH_PARTITION_STORAGE_ENGINE
   if (prune_partitions(thd, table, conds))
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index ff1d70e7f8e..2be2a85b889 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -433,6 +433,12 @@ bool Sql_cmd_update::update_single_table(THD *thd)
         goto produce_explain_and_leave;
     }
   }
+  if (conds && thd->lex->are_date_funcs_used())
+  {
+    /* Rewrite datetime comparison conditions into sargable */
+    conds= conds->top_level_transform(thd, &Item::date_conds_transformer,
+                                      (uchar *) 0);
+  }
 
   // Don't count on usage of 'only index' when calculating which key to use
   table->covering_keys.clear_all();