From 9f9a53be4057a1c65478dfd04cd11fe9c079ce59 Mon Sep 17 00:00:00 2001
From: Oleg Smirnov <olernov@gmail.com>
Date: Fri, 24 Mar 2023 21:01:09 +0700
Subject: MDEV-30901 Index usage for DATE(datetime_column) = const does not
 work for engine Memory

Fix incorrect approach to determine whether a field is part of an index.
Remove "force index" hints from tests.
Add tests with composite indexes
---
 mysql-test/main/sargable_date_cond.result | 927 ++++++++++++++++++++----------
 mysql-test/main/sargable_date_cond.test   | 129 +++--
 2 files changed, 720 insertions(+), 336 deletions(-)

(limited to 'mysql-test/main')

diff --git a/mysql-test/main/sargable_date_cond.result b/mysql-test/main/sargable_date_cond.result
index 5c0ac7cd16d..86dd5cbfe19 100644
--- a/mysql-test/main/sargable_date_cond.result
+++ b/mysql-test/main/sargable_date_cond.result
@@ -10,6 +10,10 @@ A.a*10+B.a,
 date_add(date_add('2017-01-01', interval A.a*8 day), interval B.a hour),
 date_add('2017-01-01', interval A.a*7 day)
 from t1 A, t0 B;
+analyze table t2;
+Table	Op	Msg_type	Msg_text
+test.t2	analyze	status	Engine-independent statistics collected
+test.t2	analyze	status	Table is already up to date
 #
 # "YEAR(datetime_col) CMP year_value", basic checks
 #
@@ -19,12 +23,12 @@ count(*)
 select count(*) from t2 where a < '2018-01-01';
 count(*)
 460
-explain format=json select * from t2 force index(a) where year(a) <  2018;
+explain format=json select count(*) from t2 where year(a) < 2018;
 EXPLAIN
 {
   "query_block": {
     "select_id": 1,
-    "cost": 0.558056003,
+    "cost": 0.063940854,
     "nested_loop": [
       {
         "table": {
@@ -36,9 +40,10 @@ EXPLAIN
           "used_key_parts": ["a"],
           "loops": 1,
           "rows": 451,
-          "cost": 0.558056003,
+          "cost": 0.063940854,
           "filtered": 100,
-          "index_condition": "t2.a < '2018-01-01 00:00:00'"
+          "attached_condition": "t2.a < '2018-01-01 00:00:00'",
+          "using_index": true
         }
       }
     ]
@@ -46,27 +51,24 @@ EXPLAIN
 }
 # Check rewrite for a prepared statement:
 execute immediate 
-"explain format=json select * from t2 force index(a) where year(a) < ?"
+"explain format=json select * from t2 where year(a) < ?"
   using 2018;
 EXPLAIN
 {
   "query_block": {
     "select_id": 1,
-    "cost": 0.558056003,
+    "cost": 0.1671618,
     "nested_loop": [
       {
         "table": {
           "table_name": "t2",
-          "access_type": "range",
+          "access_type": "ALL",
           "possible_keys": ["a"],
-          "key": "a",
-          "key_length": "6",
-          "used_key_parts": ["a"],
           "loops": 1,
-          "rows": 451,
-          "cost": 0.558056003,
-          "filtered": 100,
-          "index_condition": "t2.a < '2018-01-01 00:00:00'"
+          "rows": 1000,
+          "cost": 0.1671618,
+          "filtered": 45.09999847,
+          "attached_condition": "t2.a < '2018-01-01 00:00:00'"
         }
       }
     ]
@@ -104,12 +106,12 @@ count(*)
 select count(*) from t2 where a < '2019-01-01';
 count(*)
 920
-explain format=json select * from t2 force index(a) where year(a) <= 2018;
+explain format=json select count(*) from t2 where year(a) <= 2018;
 EXPLAIN
 {
   "query_block": {
     "select_id": 1,
-    "cost": 1.115764791,
+    "cost": 0.126438894,
     "nested_loop": [
       {
         "table": {
@@ -121,36 +123,34 @@ EXPLAIN
           "used_key_parts": ["a"],
           "loops": 1,
           "rows": 903,
-          "cost": 1.115764791,
+          "cost": 0.126438894,
           "filtered": 100,
-          "index_condition": "t2.a <= '2018-12-31 23:59:59'"
+          "attached_condition": "t2.a <= '2018-12-31 23:59:59'",
+          "using_index": true
         }
       }
     ]
   }
 }
 execute immediate 
-"explain format=json select * from t2 force index(a) where year(a) <= ?"
+"explain format=json select * from t2 where year(a) <= ?"
   using 2018;
 EXPLAIN
 {
   "query_block": {
     "select_id": 1,
-    "cost": 1.115764791,
+    "cost": 0.1671618,
     "nested_loop": [
       {
         "table": {
           "table_name": "t2",
-          "access_type": "range",
+          "access_type": "ALL",
           "possible_keys": ["a"],
-          "key": "a",
-          "key_length": "6",
-          "used_key_parts": ["a"],
           "loops": 1,
-          "rows": 903,
-          "cost": 1.115764791,
-          "filtered": 100,
-          "index_condition": "t2.a <= '2018-12-31 23:59:59'"
+          "rows": 1000,
+          "cost": 0.1671618,
+          "filtered": 90.30000305,
+          "attached_condition": "t2.a <= '2018-12-31 23:59:59'"
         }
       }
     ]
@@ -187,12 +187,12 @@ count(*)
 select count(*) from t2 where a > '2018-12-31 23:59:59.999999';
 count(*)
 80
-explain format=json select * from t2 force index(a) where year(a) >  2018;
+explain format=json select count(*) from t2 where year(a) > 2018;
 EXPLAIN
 {
   "query_block": {
     "select_id": 1,
-    "cost": 0.121025977,
+    "cost": 0.014752874,
     "nested_loop": [
       {
         "table": {
@@ -204,16 +204,17 @@ EXPLAIN
           "used_key_parts": ["a"],
           "loops": 1,
           "rows": 97,
-          "cost": 0.121025977,
+          "cost": 0.014752874,
           "filtered": 100,
-          "index_condition": "t2.a > '2018-12-31 23:59:59'"
+          "attached_condition": "t2.a > '2018-12-31 23:59:59'",
+          "using_index": true
         }
       }
     ]
   }
 }
 execute immediate 
-"explain format=json select * from t2 force index(a) where year(a) > ?"
+"explain format=json select * from t2 where year(a) > ?"
   using 2018;
 EXPLAIN
 {
@@ -270,12 +271,12 @@ count(*)
 select count(*) from t2 where a >= '2018-01-01';
 count(*)
 540
-explain format=json select * from t2 force index(a) where year(a) >= 2018;
+explain format=json select count(*) from t2 where year(a) >= 2018;
 EXPLAIN
 {
   "query_block": {
     "select_id": 1,
-    "cost": 0.678734765,
+    "cost": 0.077250914,
     "nested_loop": [
       {
         "table": {
@@ -287,36 +288,34 @@ EXPLAIN
           "used_key_parts": ["a"],
           "loops": 1,
           "rows": 549,
-          "cost": 0.678734765,
+          "cost": 0.077250914,
           "filtered": 100,
-          "index_condition": "t2.a >= '2018-01-01 00:00:00'"
+          "attached_condition": "t2.a >= '2018-01-01 00:00:00'",
+          "using_index": true
         }
       }
     ]
   }
 }
 execute immediate 
-"explain format=json select * from t2 force index(a) where year(a) >= ?"
+"explain format=json select * from t2 where year(a) >= ?"
   using 2018;
 EXPLAIN
 {
   "query_block": {
     "select_id": 1,
-    "cost": 0.678734765,
+    "cost": 0.1671618,
     "nested_loop": [
       {
         "table": {
           "table_name": "t2",
-          "access_type": "range",
+          "access_type": "ALL",
           "possible_keys": ["a"],
-          "key": "a",
-          "key_length": "6",
-          "used_key_parts": ["a"],
           "loops": 1,
-          "rows": 549,
-          "cost": 0.678734765,
-          "filtered": 100,
-          "index_condition": "t2.a >= '2018-01-01 00:00:00'"
+          "rows": 1000,
+          "cost": 0.1671618,
+          "filtered": 54.90000153,
+          "attached_condition": "t2.a >= '2018-01-01 00:00:00'"
         }
       }
     ]
@@ -353,12 +352,12 @@ count(*)
 select count(*) from t2 where a >= '2017-01-01' and a < '2018-01-01';
 count(*)
 460
-explain format=json select * from t2 force index(a) where year(a) =  2017;
+explain format=json select count(*) from t2 where year(a) = 2017;
 EXPLAIN
 {
   "query_block": {
     "select_id": 1,
-    "cost": 0.558056003,
+    "cost": 0.063940854,
     "nested_loop": [
       {
         "table": {
@@ -370,36 +369,34 @@ EXPLAIN
           "used_key_parts": ["a"],
           "loops": 1,
           "rows": 451,
-          "cost": 0.558056003,
+          "cost": 0.063940854,
           "filtered": 100,
-          "index_condition": "t2.a between '2017-01-01 00:00:00' and '2017-12-31 23:59:59'"
+          "attached_condition": "t2.a between '2017-01-01 00:00:00' and '2017-12-31 23:59:59'",
+          "using_index": true
         }
       }
     ]
   }
 }
 execute immediate 
-"explain format=json select * from t2 force index(a) where year(a) = ?"
+"explain format=json select * from t2 where year(a) = ?"
   using 2017;
 EXPLAIN
 {
   "query_block": {
     "select_id": 1,
-    "cost": 0.558056003,
+    "cost": 0.1671618,
     "nested_loop": [
       {
         "table": {
           "table_name": "t2",
-          "access_type": "range",
+          "access_type": "ALL",
           "possible_keys": ["a"],
-          "key": "a",
-          "key_length": "6",
-          "used_key_parts": ["a"],
           "loops": 1,
-          "rows": 451,
-          "cost": 0.558056003,
-          "filtered": 100,
-          "index_condition": "t2.a between '2017-01-01 00:00:00' and '2017-12-31 23:59:59'"
+          "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'"
         }
       }
     ]
@@ -439,12 +436,12 @@ count(*)
 select count(*) from t2 where a >= '2018-01-01';
 count(*)
 540
-explain format=json select * from t2 force index(a) where 2017 <  year(a);
+explain format=json select count(*) from t2 where 2017 < year(a);
 EXPLAIN
 {
   "query_block": {
     "select_id": 1,
-    "cost": 0.678734765,
+    "cost": 0.077250914,
     "nested_loop": [
       {
         "table": {
@@ -456,36 +453,34 @@ EXPLAIN
           "used_key_parts": ["a"],
           "loops": 1,
           "rows": 549,
-          "cost": 0.678734765,
+          "cost": 0.077250914,
           "filtered": 100,
-          "index_condition": "t2.a > '2017-12-31 23:59:59'"
+          "attached_condition": "t2.a > '2017-12-31 23:59:59'",
+          "using_index": true
         }
       }
     ]
   }
 }
 execute immediate 
-"explain format=json select * from t2 force index(a) where ? < year(a)"
+"explain format=json select * from t2 where ? < year(a)"
   using 2017;
 EXPLAIN
 {
   "query_block": {
     "select_id": 1,
-    "cost": 0.678734765,
+    "cost": 0.1671618,
     "nested_loop": [
       {
         "table": {
           "table_name": "t2",
-          "access_type": "range",
+          "access_type": "ALL",
           "possible_keys": ["a"],
-          "key": "a",
-          "key_length": "6",
-          "used_key_parts": ["a"],
           "loops": 1,
-          "rows": 549,
-          "cost": 0.678734765,
-          "filtered": 100,
-          "index_condition": "t2.a > '2017-12-31 23:59:59'"
+          "rows": 1000,
+          "cost": 0.1671618,
+          "filtered": 54.90000153,
+          "attached_condition": "t2.a > '2017-12-31 23:59:59'"
         }
       }
     ]
@@ -519,12 +514,12 @@ count(*)
 select count(*) from t2 where a >= '2018-01-01';
 count(*)
 540
-explain format=json select * from t2 force index(a) where 2018 <= year(a);
+explain format=json select count(*) from t2 where 2018 <= year(a);
 EXPLAIN
 {
   "query_block": {
     "select_id": 1,
-    "cost": 0.678734765,
+    "cost": 0.077250914,
     "nested_loop": [
       {
         "table": {
@@ -536,36 +531,34 @@ EXPLAIN
           "used_key_parts": ["a"],
           "loops": 1,
           "rows": 549,
-          "cost": 0.678734765,
+          "cost": 0.077250914,
           "filtered": 100,
-          "index_condition": "t2.a >= '2018-01-01 00:00:00'"
+          "attached_condition": "t2.a >= '2018-01-01 00:00:00'",
+          "using_index": true
         }
       }
     ]
   }
 }
 execute immediate 
-"explain format=json select * from t2 force index(a) where ? <= year(a)"
+"explain format=json select * from t2 where ? <= year(a)"
   using 2018;
 EXPLAIN
 {
   "query_block": {
     "select_id": 1,
-    "cost": 0.678734765,
+    "cost": 0.1671618,
     "nested_loop": [
       {
         "table": {
           "table_name": "t2",
-          "access_type": "range",
+          "access_type": "ALL",
           "possible_keys": ["a"],
-          "key": "a",
-          "key_length": "6",
-          "used_key_parts": ["a"],
           "loops": 1,
-          "rows": 549,
-          "cost": 0.678734765,
-          "filtered": 100,
-          "index_condition": "t2.a >= '2018-01-01 00:00:00'"
+          "rows": 1000,
+          "cost": 0.1671618,
+          "filtered": 54.90000153,
+          "attached_condition": "t2.a >= '2018-01-01 00:00:00'"
         }
       }
     ]
@@ -602,12 +595,12 @@ count(*)
 select count(*) from t2 where a < '2018-01-01';
 count(*)
 460
-explain format=json select * from t2 force index(a) where 2018 >  year(a);
+explain format=json select count(*) from t2 where 2018 > year(a);
 EXPLAIN
 {
   "query_block": {
     "select_id": 1,
-    "cost": 0.558056003,
+    "cost": 0.063940854,
     "nested_loop": [
       {
         "table": {
@@ -619,36 +612,34 @@ EXPLAIN
           "used_key_parts": ["a"],
           "loops": 1,
           "rows": 451,
-          "cost": 0.558056003,
+          "cost": 0.063940854,
           "filtered": 100,
-          "index_condition": "t2.a < '2018-01-01 00:00:00'"
+          "attached_condition": "t2.a < '2018-01-01 00:00:00'",
+          "using_index": true
         }
       }
     ]
   }
 }
 execute immediate 
-"explain format=json select * from t2 force index(a) where ? > year(a)"
+"explain format=json select * from t2 where ? > year(a)"
   using 2018;
 EXPLAIN
 {
   "query_block": {
     "select_id": 1,
-    "cost": 0.558056003,
+    "cost": 0.1671618,
     "nested_loop": [
       {
         "table": {
           "table_name": "t2",
-          "access_type": "range",
+          "access_type": "ALL",
           "possible_keys": ["a"],
-          "key": "a",
-          "key_length": "6",
-          "used_key_parts": ["a"],
           "loops": 1,
-          "rows": 451,
-          "cost": 0.558056003,
-          "filtered": 100,
-          "index_condition": "t2.a < '2018-01-01 00:00:00'"
+          "rows": 1000,
+          "cost": 0.1671618,
+          "filtered": 45.09999847,
+          "attached_condition": "t2.a < '2018-01-01 00:00:00'"
         }
       }
     ]
@@ -682,12 +673,12 @@ count(*)
 select count(*) from t2 where a < '2019-01-01';
 count(*)
 920
-explain format=json select * from t2 force index(a) where 2018 >= year(a);
+explain format=json select count(*) from t2 where 2018 >= year(a);
 EXPLAIN
 {
   "query_block": {
     "select_id": 1,
-    "cost": 1.115764791,
+    "cost": 0.126438894,
     "nested_loop": [
       {
         "table": {
@@ -699,36 +690,34 @@ EXPLAIN
           "used_key_parts": ["a"],
           "loops": 1,
           "rows": 903,
-          "cost": 1.115764791,
+          "cost": 0.126438894,
           "filtered": 100,
-          "index_condition": "t2.a <= '2018-12-31 23:59:59'"
+          "attached_condition": "t2.a <= '2018-12-31 23:59:59'",
+          "using_index": true
         }
       }
     ]
   }
 }
 execute immediate 
-"explain format=json select * from t2 force index(a) where ? >= year(a)"
+"explain format=json select * from t2 where ? >= year(a)"
   using 2018;
 EXPLAIN
 {
   "query_block": {
     "select_id": 1,
-    "cost": 1.115764791,
+    "cost": 0.1671618,
     "nested_loop": [
       {
         "table": {
           "table_name": "t2",
-          "access_type": "range",
+          "access_type": "ALL",
           "possible_keys": ["a"],
-          "key": "a",
-          "key_length": "6",
-          "used_key_parts": ["a"],
           "loops": 1,
-          "rows": 903,
-          "cost": 1.115764791,
-          "filtered": 100,
-          "index_condition": "t2.a <= '2018-12-31 23:59:59'"
+          "rows": 1000,
+          "cost": 0.1671618,
+          "filtered": 90.30000305,
+          "attached_condition": "t2.a <= '2018-12-31 23:59:59'"
         }
       }
     ]
@@ -765,12 +754,12 @@ count(*)
 select count(*) from t2 where a >= '2018-01-01' and a < '2019-01-01';
 count(*)
 460
-explain format=json select * from t2 force index(a) where 2018 =  year(a);
+explain format=json select count(*) from t2 where 2018 =  year(a);
 EXPLAIN
 {
   "query_block": {
     "select_id": 1,
-    "cost": 0.559289872,
+    "cost": 0.064079124,
     "nested_loop": [
       {
         "table": {
@@ -782,36 +771,34 @@ EXPLAIN
           "used_key_parts": ["a"],
           "loops": 1,
           "rows": 452,
-          "cost": 0.559289872,
+          "cost": 0.064079124,
           "filtered": 100,
-          "index_condition": "t2.a between '2018-01-01 00:00:00' and '2018-12-31 23:59:59'"
+          "attached_condition": "t2.a between '2018-01-01 00:00:00' and '2018-12-31 23:59:59'",
+          "using_index": true
         }
       }
     ]
   }
 }
 execute immediate 
-"explain format=json select * from t2 force index(a) where ? = year(a)"
+"explain format=json select * from t2 where ? = year(a)"
   using 2018;
 EXPLAIN
 {
   "query_block": {
     "select_id": 1,
-    "cost": 0.559289872,
+    "cost": 0.1671618,
     "nested_loop": [
       {
         "table": {
           "table_name": "t2",
-          "access_type": "range",
+          "access_type": "ALL",
           "possible_keys": ["a"],
-          "key": "a",
-          "key_length": "6",
-          "used_key_parts": ["a"],
           "loops": 1,
-          "rows": 452,
-          "cost": 0.559289872,
-          "filtered": 100,
-          "index_condition": "t2.a between '2018-01-01 00:00:00' and '2018-12-31 23:59:59'"
+          "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'"
         }
       }
     ]
@@ -851,12 +838,12 @@ count(*)
 select count(*) from t2 where a < '2017-06-01';
 count(*)
 190
-explain format=json select * from t2 force index(a) where date(a)< '2017-06-01';
+explain format=json select count(*) from t2 where date(a) < '2017-06-01';
 EXPLAIN
 {
   "query_block": {
     "select_id": 1,
-    "cost": 0.230840318,
+    "cost": 0.027058904,
     "nested_loop": [
       {
         "table": {
@@ -868,36 +855,34 @@ EXPLAIN
           "used_key_parts": ["a"],
           "loops": 1,
           "rows": 186,
-          "cost": 0.230840318,
+          "cost": 0.027058904,
           "filtered": 100,
-          "index_condition": "t2.a < '2017-06-01 00:00:00'"
+          "attached_condition": "t2.a < '2017-06-01 00:00:00'",
+          "using_index": true
         }
       }
     ]
   }
 }
 execute immediate 
-"explain format=json select * from t2 force index(a) where date(a) < ?"
+"explain format=json select * from t2 where date(a) < ?"
   using '2017-06-01';
 EXPLAIN
 {
   "query_block": {
     "select_id": 1,
-    "cost": 0.230840318,
+    "cost": 0.1671618,
     "nested_loop": [
       {
         "table": {
           "table_name": "t2",
-          "access_type": "range",
+          "access_type": "ALL",
           "possible_keys": ["a"],
-          "key": "a",
-          "key_length": "6",
-          "used_key_parts": ["a"],
           "loops": 1,
-          "rows": 186,
-          "cost": 0.230840318,
-          "filtered": 100,
-          "index_condition": "t2.a < '2017-06-01 00:00:00'"
+          "rows": 1000,
+          "cost": 0.1671618,
+          "filtered": 18.60000038,
+          "attached_condition": "t2.a < '2017-06-01 00:00:00'"
         }
       }
     ]
@@ -934,12 +919,12 @@ count(*)
 select count(*) from t2 where a < '2017-06-04';
 count(*)
 200
-explain format=json select * from t2 force index(a) where date(a)<='2017-06-04';
+explain format=json select count(*) from t2 where date(a) <= '2017-06-03';
 EXPLAIN
 {
   "query_block": {
     "select_id": 1,
-    "cost": 0.243179008,
+    "cost": 0.028441604,
     "nested_loop": [
       {
         "table": {
@@ -951,36 +936,34 @@ EXPLAIN
           "used_key_parts": ["a"],
           "loops": 1,
           "rows": 196,
-          "cost": 0.243179008,
+          "cost": 0.028441604,
           "filtered": 100,
-          "index_condition": "t2.a <= '2017-06-04 23:59:59'"
+          "attached_condition": "t2.a <= '2017-06-03 23:59:59'",
+          "using_index": true
         }
       }
     ]
   }
 }
 execute immediate 
-"explain format=json select * from t2 force index(a) where date(a) <= ?"
+"explain format=json select * from t2 where date(a) <= ?"
   using '2017-06-04';
 EXPLAIN
 {
   "query_block": {
     "select_id": 1,
-    "cost": 0.243179008,
+    "cost": 0.1671618,
     "nested_loop": [
       {
         "table": {
           "table_name": "t2",
-          "access_type": "range",
+          "access_type": "ALL",
           "possible_keys": ["a"],
-          "key": "a",
-          "key_length": "6",
-          "used_key_parts": ["a"],
           "loops": 1,
-          "rows": 196,
-          "cost": 0.243179008,
-          "filtered": 100,
-          "index_condition": "t2.a <= '2017-06-04 23:59:59'"
+          "rows": 1000,
+          "cost": 0.1671618,
+          "filtered": 19.60000038,
+          "attached_condition": "t2.a <= '2017-06-04 23:59:59'"
         }
       }
     ]
@@ -1017,12 +1000,12 @@ count(*)
 select count(*) from t2 where a >= '2018-06-02';
 count(*)
 350
-explain format=json select * from t2 force index(a) where date(a)> '2018-06-01';
+explain format=json select count(*) from t2 where date(a) > '2018-06-01';
 EXPLAIN
 {
   "query_block": {
     "select_id": 1,
-    "cost": 0.449475531,
+    "cost": 0.051773094,
     "nested_loop": [
       {
         "table": {
@@ -1034,36 +1017,34 @@ EXPLAIN
           "used_key_parts": ["a"],
           "loops": 1,
           "rows": 363,
-          "cost": 0.449475531,
+          "cost": 0.051773094,
           "filtered": 100,
-          "index_condition": "t2.a > '2018-06-01 23:59:59'"
+          "attached_condition": "t2.a > '2018-06-01 23:59:59'",
+          "using_index": true
         }
       }
     ]
   }
 }
 execute immediate 
-"explain format=json select * from t2 force index(a) where date(a) > ?"
+"explain format=json select * from t2 where date(a) > ?"
   using '2018-06-01';
 EXPLAIN
 {
   "query_block": {
     "select_id": 1,
-    "cost": 0.449475531,
+    "cost": 0.1671618,
     "nested_loop": [
       {
         "table": {
           "table_name": "t2",
-          "access_type": "range",
+          "access_type": "ALL",
           "possible_keys": ["a"],
-          "key": "a",
-          "key_length": "6",
-          "used_key_parts": ["a"],
           "loops": 1,
-          "rows": 363,
-          "cost": 0.449475531,
-          "filtered": 100,
-          "index_condition": "t2.a > '2018-06-01 23:59:59'"
+          "rows": 1000,
+          "cost": 0.1671618,
+          "filtered": 36.29999924,
+          "attached_condition": "t2.a > '2018-06-01 23:59:59'"
         }
       }
     ]
@@ -1100,12 +1081,12 @@ count(*)
 select count(*) from t2 where a >= '2018-06-01';
 count(*)
 350
-explain format=json select * from t2 force index(a) where date(a)>='2018-06-01';
+explain format=json select count(*) from t2 where date(a) >= '2018-06-01';
 EXPLAIN
 {
   "query_block": {
     "select_id": 1,
-    "cost": 0.449475531,
+    "cost": 0.051773094,
     "nested_loop": [
       {
         "table": {
@@ -1117,36 +1098,34 @@ EXPLAIN
           "used_key_parts": ["a"],
           "loops": 1,
           "rows": 363,
-          "cost": 0.449475531,
+          "cost": 0.051773094,
           "filtered": 100,
-          "index_condition": "t2.a >= '2018-06-01 00:00:00'"
+          "attached_condition": "t2.a >= '2018-06-01 00:00:00'",
+          "using_index": true
         }
       }
     ]
   }
 }
 execute immediate 
-"explain format=json select * from t2 force index(a) where date(a) >= ?"
+"explain format=json select * from t2 where date(a) >= ?"
   using '2018-06-01';
 EXPLAIN
 {
   "query_block": {
     "select_id": 1,
-    "cost": 0.449475531,
+    "cost": 0.1671618,
     "nested_loop": [
       {
         "table": {
           "table_name": "t2",
-          "access_type": "range",
+          "access_type": "ALL",
           "possible_keys": ["a"],
-          "key": "a",
-          "key_length": "6",
-          "used_key_parts": ["a"],
           "loops": 1,
-          "rows": 363,
-          "cost": 0.449475531,
-          "filtered": 100,
-          "index_condition": "t2.a >= '2018-06-01 00:00:00'"
+          "rows": 1000,
+          "cost": 0.1671618,
+          "filtered": 36.29999924,
+          "attached_condition": "t2.a >= '2018-06-01 00:00:00'"
         }
       }
     ]
@@ -1183,12 +1162,12 @@ count(*)
 select count(*) from t2 where a >= '2017-06-02' and a < '2017-06-03';
 count(*)
 10
-explain format=json select * from t2 force index(a) where date(a)= '2017-06-02';
+explain format=json select count(*) from t2 where date(a) = '2017-06-02';
 EXPLAIN
 {
   "query_block": {
     "select_id": 1,
-    "cost": 0.013679374,
+    "cost": 0.002723384,
     "nested_loop": [
       {
         "table": {
@@ -1200,16 +1179,17 @@ EXPLAIN
           "used_key_parts": ["a"],
           "loops": 1,
           "rows": 10,
-          "cost": 0.013679374,
+          "cost": 0.002723384,
           "filtered": 100,
-          "index_condition": "t2.a between '2017-06-02 00:00:00' and '2017-06-02 23:59:59'"
+          "attached_condition": "t2.a between '2017-06-02 00:00:00' and '2017-06-02 23:59:59'",
+          "using_index": true
         }
       }
     ]
   }
 }
 execute immediate 
-"explain format=json select * from t2 force index(a) where date(a) = ?"
+"explain format=json select * from t2 where date(a) = ?"
   using '2017-06-02';
 EXPLAIN
 {
@@ -1269,12 +1249,12 @@ count(*)
 select count(*) from t2 where '2017-06-01' > a;
 count(*)
 190
-explain format=json select * from t2 force index(a) where '2017-06-01' > date(a);
+explain format=json select count(*) from t2 where '2017-06-01' > date(a);
 EXPLAIN
 {
   "query_block": {
     "select_id": 1,
-    "cost": 0.230840318,
+    "cost": 0.027058904,
     "nested_loop": [
       {
         "table": {
@@ -1286,36 +1266,34 @@ EXPLAIN
           "used_key_parts": ["a"],
           "loops": 1,
           "rows": 186,
-          "cost": 0.230840318,
+          "cost": 0.027058904,
           "filtered": 100,
-          "index_condition": "t2.a < '2017-06-01 00:00:00'"
+          "attached_condition": "t2.a < '2017-06-01 00:00:00'",
+          "using_index": true
         }
       }
     ]
   }
 }
 execute immediate 
-"explain format=json select * from t2 force index(a) where ? > date(a)"
+"explain format=json select * from t2 where ? > date(a)"
   using '2017-06-01';
 EXPLAIN
 {
   "query_block": {
     "select_id": 1,
-    "cost": 0.230840318,
+    "cost": 0.1671618,
     "nested_loop": [
       {
         "table": {
           "table_name": "t2",
-          "access_type": "range",
+          "access_type": "ALL",
           "possible_keys": ["a"],
-          "key": "a",
-          "key_length": "6",
-          "used_key_parts": ["a"],
           "loops": 1,
-          "rows": 186,
-          "cost": 0.230840318,
-          "filtered": 100,
-          "index_condition": "t2.a < '2017-06-01 00:00:00'"
+          "rows": 1000,
+          "cost": 0.1671618,
+          "filtered": 18.60000038,
+          "attached_condition": "t2.a < '2017-06-01 00:00:00'"
         }
       }
     ]
@@ -1352,12 +1330,12 @@ count(*)
 select count(*) from t2 where '2017-06-03' >= a;
 count(*)
 200
-explain format=json select * from t2 force index(a) where '2017-06-03' >= date(a);
+explain format=json select count(*) from t2 where '2017-06-03' >= date(a);
 EXPLAIN
 {
   "query_block": {
     "select_id": 1,
-    "cost": 0.243179008,
+    "cost": 0.028441604,
     "nested_loop": [
       {
         "table": {
@@ -1369,36 +1347,34 @@ EXPLAIN
           "used_key_parts": ["a"],
           "loops": 1,
           "rows": 196,
-          "cost": 0.243179008,
+          "cost": 0.028441604,
           "filtered": 100,
-          "index_condition": "t2.a <= '2017-06-03 23:59:59'"
+          "attached_condition": "t2.a <= '2017-06-03 23:59:59'",
+          "using_index": true
         }
       }
     ]
   }
 }
 execute immediate 
-"explain format=json select * from t2 force index(a) where ? >= date(a)"
+"explain format=json select * from t2 where ? >= date(a)"
   using '2017-06-03';
 EXPLAIN
 {
   "query_block": {
     "select_id": 1,
-    "cost": 0.243179008,
+    "cost": 0.1671618,
     "nested_loop": [
       {
         "table": {
           "table_name": "t2",
-          "access_type": "range",
+          "access_type": "ALL",
           "possible_keys": ["a"],
-          "key": "a",
-          "key_length": "6",
-          "used_key_parts": ["a"],
           "loops": 1,
-          "rows": 196,
-          "cost": 0.243179008,
-          "filtered": 100,
-          "index_condition": "t2.a <= '2017-06-03 23:59:59'"
+          "rows": 1000,
+          "cost": 0.1671618,
+          "filtered": 19.60000038,
+          "attached_condition": "t2.a <= '2017-06-03 23:59:59'"
         }
       }
     ]
@@ -1435,12 +1411,12 @@ count(*)
 select count(*) from t2 where '2018-06-02' <= a;
 count(*)
 350
-explain format=json select * from t2 force index(a) where '2018-06-01' < date(a);
+explain format=json select count(*) from t2 where '2018-06-01' < date(a);
 EXPLAIN
 {
   "query_block": {
     "select_id": 1,
-    "cost": 0.449475531,
+    "cost": 0.051773094,
     "nested_loop": [
       {
         "table": {
@@ -1452,36 +1428,34 @@ EXPLAIN
           "used_key_parts": ["a"],
           "loops": 1,
           "rows": 363,
-          "cost": 0.449475531,
+          "cost": 0.051773094,
           "filtered": 100,
-          "index_condition": "t2.a > '2018-06-01 23:59:59'"
+          "attached_condition": "t2.a > '2018-06-01 23:59:59'",
+          "using_index": true
         }
       }
     ]
   }
 }
 execute immediate 
-"explain format=json select * from t2 force index(a) where ? < date(a)"
+"explain format=json select * from t2 where ? < date(a)"
   using '2017-06-01';
 EXPLAIN
 {
   "query_block": {
     "select_id": 1,
-    "cost": 1.00595045,
+    "cost": 0.1671618,
     "nested_loop": [
       {
         "table": {
           "table_name": "t2",
-          "access_type": "range",
+          "access_type": "ALL",
           "possible_keys": ["a"],
-          "key": "a",
-          "key_length": "6",
-          "used_key_parts": ["a"],
           "loops": 1,
-          "rows": 814,
-          "cost": 1.00595045,
-          "filtered": 100,
-          "index_condition": "t2.a > '2017-06-01 23:59:59'"
+          "rows": 1000,
+          "cost": 0.1671618,
+          "filtered": 81.40000153,
+          "attached_condition": "t2.a > '2017-06-01 23:59:59'"
         }
       }
     ]
@@ -1518,12 +1492,12 @@ count(*)
 select count(*) from t2 where '2018-06-01' <= a;
 count(*)
 350
-explain format=json select * from t2 force index(a) where '2018-06-01' <= date(a);
+explain format=json select count(*) from t2 where '2018-06-01' <= date(a);
 EXPLAIN
 {
   "query_block": {
     "select_id": 1,
-    "cost": 0.449475531,
+    "cost": 0.051773094,
     "nested_loop": [
       {
         "table": {
@@ -1535,36 +1509,34 @@ EXPLAIN
           "used_key_parts": ["a"],
           "loops": 1,
           "rows": 363,
-          "cost": 0.449475531,
+          "cost": 0.051773094,
           "filtered": 100,
-          "index_condition": "t2.a >= '2018-06-01 00:00:00'"
+          "attached_condition": "t2.a >= '2018-06-01 00:00:00'",
+          "using_index": true
         }
       }
     ]
   }
 }
 execute immediate 
-"explain format=json select * from t2 force index(a) where ? <= date(a)"
+"explain format=json select * from t2 where ? <= date(a)"
   using '2017-06-01';
 EXPLAIN
 {
   "query_block": {
     "select_id": 1,
-    "cost": 1.00595045,
+    "cost": 0.1671618,
     "nested_loop": [
       {
         "table": {
           "table_name": "t2",
-          "access_type": "range",
+          "access_type": "ALL",
           "possible_keys": ["a"],
-          "key": "a",
-          "key_length": "6",
-          "used_key_parts": ["a"],
           "loops": 1,
-          "rows": 814,
-          "cost": 1.00595045,
-          "filtered": 100,
-          "index_condition": "t2.a >= '2017-06-01 00:00:00'"
+          "rows": 1000,
+          "cost": 0.1671618,
+          "filtered": 81.40000153,
+          "attached_condition": "t2.a >= '2017-06-01 00:00:00'"
         }
       }
     ]
@@ -1601,12 +1573,12 @@ count(*)
 select count(*) from t2 where a >= '2017-06-02' and a < '2017-06-03';
 count(*)
 10
-explain format=json select * from t2 force index(a) where '2017-06-02' = date(a);
+explain format=json select count(*) from t2 where '2017-06-02' = date(a);
 EXPLAIN
 {
   "query_block": {
     "select_id": 1,
-    "cost": 0.013679374,
+    "cost": 0.002723384,
     "nested_loop": [
       {
         "table": {
@@ -1618,16 +1590,17 @@ EXPLAIN
           "used_key_parts": ["a"],
           "loops": 1,
           "rows": 10,
-          "cost": 0.013679374,
+          "cost": 0.002723384,
           "filtered": 100,
-          "index_condition": "t2.a between '2017-06-02 00:00:00' and '2017-06-02 23:59:59'"
+          "attached_condition": "t2.a between '2017-06-02 00:00:00' and '2017-06-02 23:59:59'",
+          "using_index": true
         }
       }
     ]
   }
 }
 execute immediate 
-"explain format=json select * from t2 force index(a) where ? = date(a)"
+"explain format=json select * from t2 where ? = date(a)"
   using '2017-06-02';
 EXPLAIN
 {
@@ -1687,7 +1660,7 @@ EXPLAIN
 {
   "query_block": {
     "select_id": 1,
-    "cost": 536.1519424,
+    "cost": 300.4906324,
     "nested_loop": [
       {
         "table": {
@@ -1700,7 +1673,7 @@ EXPLAIN
           "loops": 1,
           "rows": 451,
           "cost": 0.558056003,
-          "filtered": 100,
+          "filtered": 56,
           "index_condition": "t21.a < '2018-01-01 00:00:00'",
           "attached_condition": "t21.b > '2017-11-01'"
         }
@@ -1714,10 +1687,10 @@ EXPLAIN
             "key": "a",
             "key_length": "6",
             "used_key_parts": ["a"],
-            "loops": 451,
+            "loops": 252.56,
             "rows": 961,
-            "cost": 535.5938864,
-            "filtered": 100,
+            "cost": 299.9325764,
+            "filtered": 56,
             "index_condition": "t22.a >= '2017-02-01 00:00:00'",
             "attached_condition": "t22.a >= '2017-02-01 00:00:00' and t22.b > '2017-11-01'"
           },
@@ -1893,12 +1866,12 @@ count(*)
 select count(*) from t2 where b < '2017-06-03';
 count(*)
 220
-explain format=json select * from t2 force index(b) where date(b)< '2017-06-03';
+explain format=json select count(*) from t2 where date(b)< '2017-06-03';
 EXPLAIN
 {
   "query_block": {
     "select_id": 1,
-    "cost": 0.267856388,
+    "cost": 0.031207004,
     "nested_loop": [
       {
         "table": {
@@ -1910,9 +1883,10 @@ EXPLAIN
           "used_key_parts": ["b"],
           "loops": 1,
           "rows": 216,
-          "cost": 0.267856388,
+          "cost": 0.031207004,
           "filtered": 100,
-          "index_condition": "t2.b < '2017-06-03'"
+          "attached_condition": "t2.b < '2017-06-03'",
+          "using_index": true
         }
       }
     ]
@@ -1943,18 +1917,18 @@ count(*)
 execute stmt using '2017-06-10';
 count(*)
 230
-select count(*) from t2 force index(b) where date(b)= '2017-06-04';
+select count(*) from t2 where date(b)= '2017-06-04';
 count(*)
 10
 select count(*) from t2 where b >= '2017-06-04' and b < '2017-06-05';
 count(*)
 10
-explain format=json select * from t2 force index(b) where date(b)='2017-06-04';
+explain format=json select count(*) from t2 where date(b)= '2017-06-04';
 EXPLAIN
 {
   "query_block": {
     "select_id": 1,
-    "cost": 0.013679374,
+    "cost": 0.002723384,
     "nested_loop": [
       {
         "table": {
@@ -1966,26 +1940,27 @@ EXPLAIN
           "used_key_parts": ["b"],
           "loops": 1,
           "rows": 10,
-          "cost": 0.013679374,
+          "cost": 0.002723384,
           "filtered": 100,
-          "index_condition": "t2.b between '2017-06-04' and '2017-06-04'"
+          "attached_condition": "t2.b between '2017-06-04' and '2017-06-04'",
+          "using_index": true
         }
       }
     ]
   }
 }
-prepare stmt from "select count(*) from t2 force index(b) where date(b)= '2017-06-04'";
+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 force index(b) where date(b)= '2017-06-04';
+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 force index(b) where date(b)= '2017-06-04';
+create or replace procedure sp() select count(*) from t2 where date(b)= '2017-06-04';
 call sp();
 count(*)
 10
@@ -2177,15 +2152,15 @@ EXPLAIN
 {
   "query_block": {
     "select_id": 1,
-    "cost": 0.16810023,
+    "cost": 0.1671618,
     "nested_loop": [
       {
         "table": {
           "table_name": "t2",
           "access_type": "ALL",
           "loops": 1,
-          "rows": 1006,
-          "cost": 0.16810023,
+          "rows": 1000,
+          "cost": 0.1671618,
           "filtered": 100,
           "attached_condition": "year(t2.a) = -1"
         }
@@ -2198,15 +2173,15 @@ EXPLAIN
 {
   "query_block": {
     "select_id": 1,
-    "cost": 0.16810023,
+    "cost": 0.1671618,
     "nested_loop": [
       {
         "table": {
           "table_name": "t2",
           "access_type": "ALL",
           "loops": 1,
-          "rows": 1006,
-          "cost": 0.16810023,
+          "rows": 1000,
+          "cost": 0.1671618,
           "filtered": 100,
           "attached_condition": "year(t2.a) > -5"
         }
@@ -2219,15 +2194,15 @@ EXPLAIN
 {
   "query_block": {
     "select_id": 1,
-    "cost": 0.16810023,
+    "cost": 0.1671618,
     "nested_loop": [
       {
         "table": {
           "table_name": "t2",
           "access_type": "ALL",
           "loops": 1,
-          "rows": 1006,
-          "cost": 0.16810023,
+          "rows": 1000,
+          "cost": 0.1671618,
           "filtered": 100,
           "attached_condition": "year(t2.a) < -1"
         }
@@ -2240,15 +2215,15 @@ EXPLAIN
 {
   "query_block": {
     "select_id": 1,
-    "cost": 0.16810023,
+    "cost": 0.1671618,
     "nested_loop": [
       {
         "table": {
           "table_name": "t2",
           "access_type": "ALL",
           "loops": 1,
-          "rows": 1006,
-          "cost": 0.16810023,
+          "rows": 1000,
+          "cost": 0.1671618,
           "filtered": 100,
           "attached_condition": "year(t2.a) <= 10000"
         }
@@ -2261,15 +2236,15 @@ EXPLAIN
 {
   "query_block": {
     "select_id": 1,
-    "cost": 0.16810023,
+    "cost": 0.1671618,
     "nested_loop": [
       {
         "table": {
           "table_name": "t2",
           "access_type": "ALL",
           "loops": 1,
-          "rows": 1006,
-          "cost": 0.16810023,
+          "rows": 1000,
+          "cost": 0.1671618,
           "filtered": 100,
           "attached_condition": "year(t2.a) >= 10020"
         }
@@ -2282,15 +2257,15 @@ EXPLAIN
 {
   "query_block": {
     "select_id": 1,
-    "cost": 0.16810023,
+    "cost": 0.1671618,
     "nested_loop": [
       {
         "table": {
           "table_name": "t2",
           "access_type": "ALL",
           "loops": 1,
-          "rows": 1006,
-          "cost": 0.16810023,
+          "rows": 1000,
+          "cost": 0.1671618,
           "filtered": 100,
           "attached_condition": "cast(t2.a as date) = '10000-01-01'"
         }
@@ -2305,15 +2280,15 @@ EXPLAIN
 {
   "query_block": {
     "select_id": 1,
-    "cost": 0.16810023,
+    "cost": 0.1671618,
     "nested_loop": [
       {
         "table": {
           "table_name": "t2",
           "access_type": "ALL",
           "loops": 1,
-          "rows": 1006,
-          "cost": 0.16810023,
+          "rows": 1000,
+          "cost": 0.1671618,
           "filtered": 100,
           "attached_condition": "cast(t2.a as date) < '-1-01-01'"
         }
@@ -2323,6 +2298,374 @@ EXPLAIN
 }
 Warnings:
 Warning	1292	Incorrect datetime value: '-1-01-01'
-drop table t0,t1,t2,t3;
+#
+# Composite indexes
+#
+create table t4 (a datetime, b int, key(a, b)) ;
+insert into t4 values ('2009-11-29 13:43:32', 2);
+insert into t4 values ('2009-10-15 12:15:11', 2);
+insert into t4 values ('2009-09-01 15:55:00', 3);
+insert into t4 values ('2009-08-23 12:07:47', 4);
+explain format=json select * from t4 where year(a) = 2009;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.001893764,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t4",
+          "access_type": "range",
+          "possible_keys": ["a"],
+          "key": "a",
+          "key_length": "6",
+          "used_key_parts": ["a"],
+          "loops": 1,
+          "rows": 4,
+          "cost": 0.001893764,
+          "filtered": 100,
+          "attached_condition": "t4.a between '2009-01-01 00:00:00' and '2009-12-31 23:59:59'",
+          "using_index": true
+        }
+      }
+    ]
+  }
+}
+explain format=json select * from t4 where year(a) >= 2009;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.001893764,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t4",
+          "access_type": "range",
+          "possible_keys": ["a"],
+          "key": "a",
+          "key_length": "6",
+          "used_key_parts": ["a"],
+          "loops": 1,
+          "rows": 4,
+          "cost": 0.001893764,
+          "filtered": 100,
+          "attached_condition": "t4.a >= '2009-01-01 00:00:00'",
+          "using_index": true
+        }
+      }
+    ]
+  }
+}
+explain format=json select * from t4 where year(a) < 2018;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.001893764,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t4",
+          "access_type": "range",
+          "possible_keys": ["a"],
+          "key": "a",
+          "key_length": "6",
+          "used_key_parts": ["a"],
+          "loops": 1,
+          "rows": 4,
+          "cost": 0.001893764,
+          "filtered": 100,
+          "attached_condition": "t4.a < '2018-01-01 00:00:00'",
+          "using_index": true
+        }
+      }
+    ]
+  }
+}
+explain format=json select * from t4 where date(a) = '2009-12-01';
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.001478954,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t4",
+          "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": "t4.a between '2009-12-01 00:00:00' and '2009-12-01 23:59:59'",
+          "using_index": true
+        }
+      }
+    ]
+  }
+}
+explain format=json select * from t4 where b = 2 and year(a) = 2009;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.001893764,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t4",
+          "access_type": "range",
+          "possible_keys": ["a"],
+          "key": "a",
+          "key_length": "11",
+          "used_key_parts": ["a", "b"],
+          "loops": 1,
+          "rows": 4,
+          "cost": 0.001893764,
+          "filtered": 100,
+          "attached_condition": "t4.b = 2 and t4.a between '2009-01-01 00:00:00' and '2009-12-31 23:59:59'",
+          "using_index": true
+        }
+      }
+    ]
+  }
+}
+explain format=json select * from t4 where b = 10 and year(a) > 2001;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.001893764,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t4",
+          "access_type": "range",
+          "possible_keys": ["a"],
+          "key": "a",
+          "key_length": "6",
+          "used_key_parts": ["a"],
+          "loops": 1,
+          "rows": 4,
+          "cost": 0.001893764,
+          "filtered": 100,
+          "attached_condition": "t4.b = 10 and t4.a > '2001-12-31 23:59:59'",
+          "using_index": true
+        }
+      }
+    ]
+  }
+}
+explain format=json select * from t4 where b = 2 and date(a) = '2009-11-02';
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.001478954,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t4",
+          "access_type": "range",
+          "possible_keys": ["a"],
+          "key": "a",
+          "key_length": "11",
+          "used_key_parts": ["a", "b"],
+          "loops": 1,
+          "rows": 1,
+          "cost": 0.001478954,
+          "filtered": 100,
+          "attached_condition": "t4.b = 2 and t4.a between '2009-11-02 00:00:00' and '2009-11-02 23:59:59'",
+          "using_index": true
+        }
+      }
+    ]
+  }
+}
+# Reverse order of fields in the index
+create table t5 (a datetime, b int, c varchar(20), key(b, a));
+insert into t5 values ('2009-11-29 13:43:32', 2, 'abc');
+insert into t5 values ('2009-10-15 12:15:11', 2, 'def');
+insert into t5 values ('2009-09-01 15:55:00', 3, 'gfd');
+insert into t5 values ('2009-08-23 12:07:47', 4, 'xyz');
+explain format=json select * from t5 where year(a) = 2009;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.01066122,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t5",
+          "access_type": "ALL",
+          "loops": 1,
+          "rows": 4,
+          "cost": 0.01066122,
+          "filtered": 100,
+          "attached_condition": "t5.a between <cache>('2009-01-01 00:00:00') and <cache>('2009-12-31 23:59:59')"
+        }
+      }
+    ]
+  }
+}
+explain format=json select * from t4 where date(a) = '2009-12-01';
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.001478954,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t4",
+          "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": "t4.a between '2009-12-01 00:00:00' and '2009-12-01 23:59:59'",
+          "using_index": true
+        }
+      }
+    ]
+  }
+}
+explain format=json select * from t5 where b = 2 and year(a) = 2009;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.002037211,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t5",
+          "access_type": "range",
+          "possible_keys": ["b"],
+          "key": "b",
+          "key_length": "11",
+          "used_key_parts": ["b", "a"],
+          "loops": 1,
+          "rows": 2,
+          "cost": 0.002037211,
+          "filtered": 70,
+          "index_condition": "t5.b = 2 and t5.a between '2009-01-01 00:00:00' and '2009-12-31 23:59:59'"
+        }
+      }
+    ]
+  }
+}
+explain format=json select * from t5 where b = 10 and year(a) > 2001;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.002574553,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t5",
+          "access_type": "range",
+          "possible_keys": ["b"],
+          "key": "b",
+          "key_length": "11",
+          "used_key_parts": ["b", "a"],
+          "loops": 1,
+          "rows": 1,
+          "cost": 0.002574553,
+          "filtered": 100,
+          "index_condition": "t5.b = 10 and t5.a > '2001-12-31 23:59:59'"
+        }
+      }
+    ]
+  }
+}
+explain format=json select * from t5 where b = 3 and date(a) > '2009-09-01';
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.002574553,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t5",
+          "access_type": "range",
+          "possible_keys": ["b"],
+          "key": "b",
+          "key_length": "11",
+          "used_key_parts": ["b", "a"],
+          "loops": 1,
+          "rows": 1,
+          "cost": 0.002574553,
+          "filtered": 100,
+          "index_condition": "t5.b = 3 and t5.a > '2009-09-01 23:59:59'"
+        }
+      }
+    ]
+  }
+}
+#
+# No rewrite for a non-indexed column
+#
+create table t6 (a datetime);
+insert into t6 values ('2009-11-29 13:43:32');
+insert into t6 values ('2009-10-15 12:15:11');
+insert into t6 values ('2009-09-01 15:55:00');
+insert into t6 values ('2009-08-23 12:07:47');
+explain format=json select * from t6 where year(a) = 2009;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.01066122,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t6",
+          "access_type": "ALL",
+          "loops": 1,
+          "rows": 4,
+          "cost": 0.01066122,
+          "filtered": 100,
+          "attached_condition": "year(t6.a) = 2009"
+        }
+      }
+    ]
+  }
+}
+explain format=json select * from t6 where date(a) = '2009-12-01';
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.01066122,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t6",
+          "access_type": "ALL",
+          "loops": 1,
+          "rows": 4,
+          "cost": 0.01066122,
+          "filtered": 100,
+          "attached_condition": "cast(t6.a as date) = '2009-12-01'"
+        }
+      }
+    ]
+  }
+}
+drop table t0,t1,t2,t3,t4,t5,t6;
 drop view v1;
 drop procedure sp;
diff --git a/mysql-test/main/sargable_date_cond.test b/mysql-test/main/sargable_date_cond.test
index 21ec9f7ddd0..3f1622c590b 100644
--- a/mysql-test/main/sargable_date_cond.test
+++ b/mysql-test/main/sargable_date_cond.test
@@ -20,16 +20,18 @@ select
   date_add('2017-01-01', interval A.a*7 day)
 from t1 A, t0 B;
 
+analyze table t2;
+
 --echo #
 --echo # "YEAR(datetime_col) CMP year_value", basic checks
 --echo #
 let $q= select count(*) from t2 where year(a) < 2018;
 eval $q;
 select count(*) from t2 where a < '2018-01-01';
-explain format=json select * from t2 force index(a) where year(a) <  2018;
+eval explain format=json $q;
 --echo # Check rewrite for a prepared statement:
 execute immediate 
-  "explain format=json select * from t2 force index(a) where year(a) < ?"
+  "explain format=json select * from t2 where year(a) < ?"
   using 2018;
 eval prepare stmt from "$q";
 execute stmt;
@@ -47,9 +49,9 @@ execute stmt using 2017;
 let $q= select count(*) from t2 where year(a) <= 2018;
 eval $q;
 select count(*) from t2 where a < '2019-01-01';
-explain format=json select * from t2 force index(a) where year(a) <= 2018;
+eval explain format=json $q;
 execute immediate 
-  "explain format=json select * from t2 force index(a) where year(a) <= ?"
+  "explain format=json select * from t2 where year(a) <= ?"
   using 2018;
 eval prepare stmt from "$q";
 execute stmt;
@@ -67,9 +69,9 @@ 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';
-explain format=json select * from t2 force index(a) where year(a) >  2018;
+eval explain format=json $q;
 execute immediate 
-  "explain format=json select * from t2 force index(a) where year(a) > ?"
+  "explain format=json select * from t2 where year(a) > ?"
   using 2018;
 eval prepare stmt from "$q";
 execute stmt;
@@ -86,9 +88,9 @@ 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';
-explain format=json select * from t2 force index(a) where year(a) >= 2018;
+eval explain format=json $q;
 execute immediate 
-  "explain format=json select * from t2 force index(a) where year(a) >= ?"
+  "explain format=json select * from t2 where year(a) >= ?"
   using 2018;
 eval prepare stmt from "$q";
 execute stmt;
@@ -105,9 +107,9 @@ 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';
-explain format=json select * from t2 force index(a) where year(a) =  2017;
+eval explain format=json $q;
 execute immediate 
-  "explain format=json select * from t2 force index(a) where year(a) = ?"
+  "explain format=json select * from t2 where year(a) = ?"
   using 2017;
 eval prepare stmt from "$q";
 execute stmt;
@@ -127,9 +129,9 @@ execute stmt using 2019;
 let $q= select count(*) from t2 where 2017 < year(a);
 eval $q;
 select count(*) from t2 where a >= '2018-01-01';
-explain format=json select * from t2 force index(a) where 2017 <  year(a);
+eval explain format=json $q;
 execute immediate 
-  "explain format=json select * from t2 force index(a) where ? < year(a)"
+  "explain format=json select * from t2 where ? < year(a)"
   using 2017;
 eval prepare stmt from "$q";
 execute stmt;
@@ -145,9 +147,9 @@ execute stmt using 2018;
 
 let $q= select count(*) from t2 where 2018 <= year(a);
 select count(*) from t2 where a >= '2018-01-01';
-explain format=json select * from t2 force index(a) where 2018 <= year(a);
+eval explain format=json $q;
 execute immediate 
-  "explain format=json select * from t2 force index(a) where ? <= year(a)"
+  "explain format=json select * from t2 where ? <= year(a)"
   using 2018;
 eval prepare stmt from "$q";
 execute stmt;
@@ -164,9 +166,9 @@ 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';
-explain format=json select * from t2 force index(a) where 2018 >  year(a);
+eval explain format=json $q;
 execute immediate 
-  "explain format=json select * from t2 force index(a) where ? > year(a)"
+  "explain format=json select * from t2 where ? > year(a)"
   using 2018;
 eval prepare stmt from "$q";
 execute stmt;
@@ -182,9 +184,9 @@ execute stmt using 2019;
 
 let $q= select count(*) from t2 where 2018 >= year(a);
 select count(*) from t2 where a < '2019-01-01';
-explain format=json select * from t2 force index(a) where 2018 >= year(a);
+eval explain format=json $q;
 execute immediate 
-  "explain format=json select * from t2 force index(a) where ? >= year(a)"
+  "explain format=json select * from t2 where ? >= year(a)"
   using 2018;
 eval prepare stmt from "$q";
 execute stmt;
@@ -201,9 +203,9 @@ 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';
-explain format=json select * from t2 force index(a) where 2018 =  year(a);
+eval explain format=json $q;
 execute immediate 
-  "explain format=json select * from t2 force index(a) where ? = year(a)"
+  "explain format=json select * from t2 where ? = year(a)"
   using 2018;
 eval prepare stmt from "$q";
 execute stmt;
@@ -223,9 +225,9 @@ execute stmt using 2019;
 let $q= select count(*) from t2 where date(a) < '2017-06-01';
 eval $q;
 select count(*) from t2 where a < '2017-06-01';
-explain format=json select * from t2 force index(a) where date(a)< '2017-06-01';
+eval explain format=json $q;
 execute immediate 
-  "explain format=json select * from t2 force index(a) where date(a) < ?"
+  "explain format=json select * from t2 where date(a) < ?"
   using '2017-06-01';
 eval prepare stmt from "$q";
 execute stmt;
@@ -242,9 +244,9 @@ 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';
-explain format=json select * from t2 force index(a) where date(a)<='2017-06-04';
+eval explain format=json $q;
 execute immediate 
-  "explain format=json select * from t2 force index(a) where date(a) <= ?"
+  "explain format=json select * from t2 where date(a) <= ?"
   using '2017-06-04';
 eval prepare stmt from "$q";
 execute stmt;
@@ -261,9 +263,9 @@ 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';
-explain format=json select * from t2 force index(a) where date(a)> '2018-06-01';
+eval explain format=json $q;
 execute immediate 
-  "explain format=json select * from t2 force index(a) where date(a) > ?"
+  "explain format=json select * from t2 where date(a) > ?"
   using '2018-06-01';
 eval prepare stmt from "$q";
 execute stmt;
@@ -280,9 +282,9 @@ 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';
-explain format=json select * from t2 force index(a) where date(a)>='2018-06-01';
+eval explain format=json $q;
 execute immediate 
-  "explain format=json select * from t2 force index(a) where date(a) >= ?"
+  "explain format=json select * from t2 where date(a) >= ?"
   using '2018-06-01';
 eval prepare stmt from "$q";
 execute stmt;
@@ -299,9 +301,9 @@ 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';
-explain format=json select * from t2 force index(a) where date(a)= '2017-06-02';
+eval explain format=json $q;
 execute immediate 
-  "explain format=json select * from t2 force index(a) where date(a) = ?"
+  "explain format=json select * from t2 where date(a) = ?"
   using '2017-06-02';
 eval prepare stmt from "$q";
 execute stmt;
@@ -321,9 +323,9 @@ execute stmt using '2017-06-05';
 let $q= select count(*) from t2 where '2017-06-01' > date(a);
 eval $q;
 select count(*) from t2 where '2017-06-01' > a;
-explain format=json select * from t2 force index(a) where '2017-06-01' > date(a);
+eval explain format=json $q;
 execute immediate 
-  "explain format=json select * from t2 force index(a) where ? > date(a)"
+  "explain format=json select * from t2 where ? > date(a)"
   using '2017-06-01';
 eval prepare stmt from "$q";
 execute stmt;
@@ -340,9 +342,9 @@ 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;
-explain format=json select * from t2 force index(a) where '2017-06-03' >= date(a);
+eval explain format=json $q;
 execute immediate 
-  "explain format=json select * from t2 force index(a) where ? >= date(a)"
+  "explain format=json select * from t2 where ? >= date(a)"
   using '2017-06-03';
 eval prepare stmt from "$q";
 execute stmt;
@@ -359,9 +361,9 @@ 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;
-explain format=json select * from t2 force index(a) where '2018-06-01' < date(a);
+eval explain format=json $q;
 execute immediate 
-  "explain format=json select * from t2 force index(a) where ? < date(a)"
+  "explain format=json select * from t2 where ? < date(a)"
   using '2017-06-01';
 eval prepare stmt from "$q";
 execute stmt;
@@ -378,9 +380,9 @@ 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;
-explain format=json select * from t2 force index(a) where '2018-06-01' <= date(a);
+eval explain format=json $q;
 execute immediate 
-  "explain format=json select * from t2 force index(a) where ? <= date(a)"
+  "explain format=json select * from t2 where ? <= date(a)"
   using '2017-06-01';
 eval prepare stmt from "$q";
 execute stmt;
@@ -397,9 +399,9 @@ 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';
-explain format=json select * from t2 force index(a) where '2017-06-02' = date(a);
+eval explain format=json $q;
 execute immediate 
-  "explain format=json select * from t2 force index(a) where ? = date(a)"
+  "explain format=json select * from t2 where ? = date(a)"
   using '2017-06-02';
 eval prepare stmt from "$q";
 execute stmt;
@@ -436,7 +438,7 @@ explain format=json select * from t2 where date(a) < '-1-01-01';
 let $q= select count(*) from t2 where date(b)< '2017-06-03';
 eval $q;
 select count(*) from t2 where b < '2017-06-03';
-explain format=json select * from t2 force index(b) where date(b)< '2017-06-03';
+eval explain format=json $q;
 eval prepare stmt from "$q";
 execute stmt;
 execute stmt;
@@ -449,10 +451,10 @@ 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 force index(b) where date(b)= '2017-06-04';
+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';
-explain format=json select * from t2 force index(b) where date(b)='2017-06-04';
+eval explain format=json $q;
 eval prepare stmt from "$q";
 execute stmt;
 execute stmt;
@@ -534,6 +536,45 @@ explain format=json select * from t2 where year(a) >= 10020;
 explain format=json select * from t2 where date(a) = '10000-01-01';
 explain format=json select * from t2 where date(a) < '-1-01-01';
 
-drop table t0,t1,t2,t3;
+--echo #
+--echo # Composite indexes
+--echo #
+create table t4 (a datetime, b int, key(a, b)) ;
+insert into t4 values ('2009-11-29 13:43:32', 2);
+insert into t4 values ('2009-10-15 12:15:11', 2);
+insert into t4 values ('2009-09-01 15:55:00', 3);
+insert into t4 values ('2009-08-23 12:07:47', 4);
+explain format=json select * from t4 where year(a) = 2009;
+explain format=json select * from t4 where year(a) >= 2009;
+explain format=json select * from t4 where year(a) < 2018;
+explain format=json select * from t4 where date(a) = '2009-12-01';
+explain format=json select * from t4 where b = 2 and year(a) = 2009;
+explain format=json select * from t4 where b = 10 and year(a) > 2001;
+explain format=json select * from t4 where b = 2 and date(a) = '2009-11-02';
+
+--echo # Reverse order of fields in the index
+create table t5 (a datetime, b int, c varchar(20), key(b, a));
+insert into t5 values ('2009-11-29 13:43:32', 2, 'abc');
+insert into t5 values ('2009-10-15 12:15:11', 2, 'def');
+insert into t5 values ('2009-09-01 15:55:00', 3, 'gfd');
+insert into t5 values ('2009-08-23 12:07:47', 4, 'xyz');
+explain format=json select * from t5 where year(a) = 2009;
+explain format=json select * from t4 where date(a) = '2009-12-01';
+explain format=json select * from t5 where b = 2 and year(a) = 2009;
+explain format=json select * from t5 where b = 10 and year(a) > 2001;
+explain format=json select * from t5 where b = 3 and date(a) > '2009-09-01';
+
+--echo #
+--echo # No rewrite for a non-indexed column
+--echo #
+create table t6 (a datetime);
+insert into t6 values ('2009-11-29 13:43:32');
+insert into t6 values ('2009-10-15 12:15:11');
+insert into t6 values ('2009-09-01 15:55:00');
+insert into t6 values ('2009-08-23 12:07:47');
+explain format=json select * from t6 where year(a) = 2009;
+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;
-- 
cgit v1.2.1