MDEV-8320 Allow index usage for DATE(col) <=> const and YEAR <=> const

Rewrite datetime comparison conditions into sargeable. For example,
    YEAR(col) <= val  ->  col <= YEAR_END(val)
    YEAR(col) <  val  ->  col <  YEAR_START(val)
    YEAR(col) >= val  ->  col >= YEAR_START(val)
    YEAR(col) >  val  ->  col >  YEAR_END(val)
    YEAR(col) =  val  ->  col BETWEEN YEAR_START(val) AND YEAR_END(val)
Do the same with DATE(col), for example:
    DATE(col) <= val  ->  col <= DAY_END(val)

After such a rewrite index lookup on column "col" can be employed

2 files changed, 2867 insertions, 0 deletions

diff --git a/mysql-test/main/sargable_date_cond.result b/mysql-test/main/sargable_date_cond.result
new file mode 100644
index 00000000000..5c0ac7cd16d
--- /dev/null
+++ b/mysql-test/main/sargable_date_cond.result
@@ -0,0 +1,2328 @@
+drop table if exists t0,t1,t2,t3;
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1(a int);
+insert into t1 select A.a + B.a* 10 from t0 A, t0 B;
+create table t2 (pk int primary key, a datetime, b date, key(a), key(b));
+insert into t2
+select
+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;
+#
+# "YEAR(datetime_col) CMP year_value", basic checks
+#
+select count(*) from t2 where year(a) < 2018;
+count(*)
+460
+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
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.558056003,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "range",
+          "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'"
+        }
+      }
+    ]
+  }
+}
+# Check rewrite for a prepared statement:
+execute immediate 
+"explain format=json select * from t2 force index(a) where year(a) < ?"
+  using 2018;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.558056003,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "range",
+          "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'"
+        }
+      }
+    ]
+  }
+}
+prepare stmt from "select count(*) from t2 where year(a) < 2018";
+execute stmt;
+count(*)
+460
+execute stmt;
+count(*)
+460
+create or replace view v1 as select count(*) from t2 where year(a) < 2018;
+select * from v1;
+count(*)
+460
+create or replace procedure sp() select count(*) from t2 where year(a) < 2018;
+call sp();
+count(*)
+460
+call sp();
+count(*)
+460
+# Prepared statement with a placeholder
+prepare stmt from "select count(*) from t2 where year(a) < ?";
+execute stmt using 2018;
+count(*)
+460
+execute stmt using 2017;
+count(*)
+0
+select count(*) from t2 where year(a) <= 2018;
+count(*)
+920
+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
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 1.115764791,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "range",
+          "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'"
+        }
+      }
+    ]
+  }
+}
+execute immediate 
+"explain format=json select * from t2 force index(a) where year(a) <= ?"
+  using 2018;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 1.115764791,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "range",
+          "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'"
+        }
+      }
+    ]
+  }
+}
+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
+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
+{
+  "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'"
+        }
+      }
+    ]
+  }
+}
+execute immediate 
+"explain format=json select * from t2 force index(a) 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
+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
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.678734765,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "range",
+          "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'"
+        }
+      }
+    ]
+  }
+}
+execute immediate 
+"explain format=json select * from t2 force index(a) where year(a) >= ?"
+  using 2018;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.678734765,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "range",
+          "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'"
+        }
+      }
+    ]
+  }
+}
+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
+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
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.558056003,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "range",
+          "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'"
+        }
+      }
+    ]
+  }
+}
+execute immediate 
+"explain format=json select * from t2 force index(a) where year(a) = ?"
+  using 2017;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.558056003,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "range",
+          "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'"
+        }
+      }
+    ]
+  }
+}
+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
+#
+select count(*) from t2 where 2017 < year(a);
+count(*)
+540
+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
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.678734765,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "range",
+          "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'"
+        }
+      }
+    ]
+  }
+}
+execute immediate 
+"explain format=json select * from t2 force index(a) where ? < year(a)"
+  using 2017;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.678734765,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "range",
+          "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'"
+        }
+      }
+    ]
+  }
+}
+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
+explain format=json select * from t2 force index(a) where 2018 <= year(a);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.678734765,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "range",
+          "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'"
+        }
+      }
+    ]
+  }
+}
+execute immediate 
+"explain format=json select * from t2 force index(a) where ? <= year(a)"
+  using 2018;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.678734765,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "range",
+          "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'"
+        }
+      }
+    ]
+  }
+}
+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
+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
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.558056003,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "range",
+          "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'"
+        }
+      }
+    ]
+  }
+}
+execute immediate 
+"explain format=json select * from t2 force index(a) where ? > year(a)"
+  using 2018;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.558056003,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "range",
+          "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'"
+        }
+      }
+    ]
+  }
+}
+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
+explain format=json select * from t2 force index(a) where 2018 >= year(a);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 1.115764791,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "range",
+          "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'"
+        }
+      }
+    ]
+  }
+}
+execute immediate 
+"explain format=json select * from t2 force index(a) where ? >= year(a)"
+  using 2018;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 1.115764791,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "range",
+          "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'"
+        }
+      }
+    ]
+  }
+}
+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
+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
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.559289872,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "range",
+          "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'"
+        }
+      }
+    ]
+  }
+}
+execute immediate 
+"explain format=json select * from t2 force index(a) where ? = year(a)"
+  using 2018;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.559289872,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "range",
+          "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'"
+        }
+      }
+    ]
+  }
+}
+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
+#
+select count(*) from t2 where date(a) < '2017-06-01';
+count(*)
+190
+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
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.230840318,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "range",
+          "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'"
+        }
+      }
+    ]
+  }
+}
+execute immediate 
+"explain format=json select * from t2 force index(a) where date(a) < ?"
+  using '2017-06-01';
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.230840318,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "range",
+          "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'"
+        }
+      }
+    ]
+  }
+}
+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
+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
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.243179008,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "range",
+          "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'"
+        }
+      }
+    ]
+  }
+}
+execute immediate 
+"explain format=json select * from t2 force index(a) where date(a) <= ?"
+  using '2017-06-04';
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.243179008,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "range",
+          "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'"
+        }
+      }
+    ]
+  }
+}
+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
+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
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.449475531,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "range",
+          "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'"
+        }
+      }
+    ]
+  }
+}
+execute immediate 
+"explain format=json select * from t2 force index(a) where date(a) > ?"
+  using '2018-06-01';
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.449475531,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "range",
+          "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'"
+        }
+      }
+    ]
+  }
+}
+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
+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
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.449475531,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "range",
+          "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'"
+        }
+      }
+    ]
+  }
+}
+execute immediate 
+"explain format=json select * from t2 force index(a) where date(a) >= ?"
+  using '2018-06-01';
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.449475531,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "range",
+          "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'"
+        }
+      }
+    ]
+  }
+}
+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
+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
+{
+  "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'"
+        }
+      }
+    ]
+  }
+}
+execute immediate 
+"explain format=json select * from t2 force index(a) 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
+#
+select count(*) from t2 where '2017-06-01' > date(a);
+count(*)
+190
+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
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.230840318,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "range",
+          "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'"
+        }
+      }
+    ]
+  }
+}
+execute immediate 
+"explain format=json select * from t2 force index(a) where ? > date(a)"
+  using '2017-06-01';
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.230840318,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "range",
+          "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'"
+        }
+      }
+    ]
+  }
+}
+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
+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
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.243179008,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "range",
+          "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'"
+        }
+      }
+    ]
+  }
+}
+execute immediate 
+"explain format=json select * from t2 force index(a) where ? >= date(a)"
+  using '2017-06-03';
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.243179008,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "range",
+          "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'"
+        }
+      }
+    ]
+  }
+}
+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
+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
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.449475531,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "range",
+          "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'"
+        }
+      }
+    ]
+  }
+}
+execute immediate 
+"explain format=json select * from t2 force index(a) where ? < date(a)"
+  using '2017-06-01';
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 1.00595045,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "range",
+          "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'"
+        }
+      }
+    ]
+  }
+}
+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
+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
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.449475531,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "range",
+          "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'"
+        }
+      }
+    ]
+  }
+}
+execute immediate 
+"explain format=json select * from t2 force index(a) where ? <= date(a)"
+  using '2017-06-01';
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 1.00595045,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "range",
+          "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'"
+        }
+      }
+    ]
+  }
+}
+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
+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
+{
+  "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'"
+        }
+      }
+    ]
+  }
+}
+execute immediate 
+"explain format=json select * from t2 force index(a) 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)
+where year(t21.a) < 2018 and t21.b > '2017-11-01'
+  and date(t22.a) >= '2017-02-01' and t22.b > '2017-11-01';
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 536.1519424,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t21",
+          "access_type": "range",
+          "possible_keys": ["a"],
+          "key": "a",
+          "key_length": "6",
+          "used_key_parts": ["a"],
+          "loops": 1,
+          "rows": 451,
+          "cost": 0.558056003,
+          "filtered": 100,
+          "index_condition": "t21.a < '2018-01-01 00:00:00'",
+          "attached_condition": "t21.b > '2017-11-01'"
+        }
+      },
+      {
+        "block-nl-join": {
+          "table": {
+            "table_name": "t22",
+            "access_type": "range",
+            "possible_keys": ["a"],
+            "key": "a",
+            "key_length": "6",
+            "used_key_parts": ["a"],
+            "loops": 451,
+            "rows": 961,
+            "cost": 535.5938864,
+            "filtered": 100,
+            "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'"
+          },
+          "buffer_type": "flat",
+          "buffer_size": "7Kb",
+          "join_type": "BNL",
+          "attached_condition": "t22.b > '2017-11-01'"
+        }
+      }
+    ]
+  }
+}
+#
+# Incorrect const values processing (no rewrite is possible)
+#
+explain format=json select * from t2 where year(a) = -1;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.1671618,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "ALL",
+          "loops": 1,
+          "rows": 1000,
+          "cost": 0.1671618,
+          "filtered": 100,
+          "attached_condition": "year(t2.a) = -1"
+        }
+      }
+    ]
+  }
+}
+explain format=json select * from t2 where year(a) > -5;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.1671618,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "ALL",
+          "loops": 1,
+          "rows": 1000,
+          "cost": 0.1671618,
+          "filtered": 100,
+          "attached_condition": "year(t2.a) > -5"
+        }
+      }
+    ]
+  }
+}
+explain format=json select * from t2 where year(a) < -1;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.1671618,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "ALL",
+          "loops": 1,
+          "rows": 1000,
+          "cost": 0.1671618,
+          "filtered": 100,
+          "attached_condition": "year(t2.a) < -1"
+        }
+      }
+    ]
+  }
+}
+explain format=json select * from t2 where year(a) <= 10000;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.1671618,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "ALL",
+          "loops": 1,
+          "rows": 1000,
+          "cost": 0.1671618,
+          "filtered": 100,
+          "attached_condition": "year(t2.a) <= 10000"
+        }
+      }
+    ]
+  }
+}
+explain format=json select * from t2 where year(a) >= 10020;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.1671618,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "ALL",
+          "loops": 1,
+          "rows": 1000,
+          "cost": 0.1671618,
+          "filtered": 100,
+          "attached_condition": "year(t2.a) >= 10020"
+        }
+      }
+    ]
+  }
+}
+explain format=json select * from t2 where date(a) = '10000-01-01';
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.1671618,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "ALL",
+          "loops": 1,
+          "rows": 1000,
+          "cost": 0.1671618,
+          "filtered": 100,
+          "attached_condition": "cast(t2.a as date) = '10000-01-01'"
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Warning	1292	Incorrect datetime value: '10000-01-01'
+explain format=json select * from t2 where date(a) < '-1-01-01';
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.1671618,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "ALL",
+          "loops": 1,
+          "rows": 1000,
+          "cost": 0.1671618,
+          "filtered": 100,
+          "attached_condition": "cast(t2.a as date) < '-1-01-01'"
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Warning	1292	Incorrect datetime value: '-1-01-01'
+#
+# Try DATE function and DATE (not DATETIME) column:
+#
+select count(*) from t2 where date(b)< '2017-06-03';
+count(*)
+220
+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
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.267856388,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "range",
+          "possible_keys": ["b"],
+          "key": "b",
+          "key_length": "4",
+          "used_key_parts": ["b"],
+          "loops": 1,
+          "rows": 216,
+          "cost": 0.267856388,
+          "filtered": 100,
+          "index_condition": "t2.b < '2017-06-03'"
+        }
+      }
+    ]
+  }
+}
+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 force index(b) 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
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.013679374,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "range",
+          "possible_keys": ["b"],
+          "key": "b",
+          "key_length": "4",
+          "used_key_parts": ["b"],
+          "loops": 1,
+          "rows": 10,
+          "cost": 0.013679374,
+          "filtered": 100,
+          "index_condition": "t2.b between '2017-06-04' and '2017-06-04'"
+        }
+      }
+    ]
+  }
+}
+prepare stmt from "select count(*) from t2 force index(b) 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';
+select * from v1;
+count(*)
+10
+create or replace procedure sp() select count(*) from t2 force index(b) 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
+#
+insert into t2 values (10001,'2006-12-31 23:59:59','2006-12-31');
+insert into t2 values (10002,'2007-01-01 00:00:00','2007-01-01');
+insert into t2 values (10003,'2007-12-31 23:59:59','2007-12-31');
+insert into t2 values (10004,'2008-01-01 00:00:00','2008-01-01');
+explain format=json
+select * from t2 force index(b) where year(b)=2007;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.005042291,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "range",
+          "possible_keys": ["b"],
+          "key": "b",
+          "key_length": "4",
+          "used_key_parts": ["b"],
+          "loops": 1,
+          "rows": 3,
+          "cost": 0.005042291,
+          "filtered": 100,
+          "index_condition": "t2.b between '2007-01-01' and '2007-12-31'"
+        }
+      }
+    ]
+  }
+}
+select * from t2 force index(b) where year(b)=2007;
+pk	a	b
+10002	2007-01-01 00:00:00	2007-01-01
+10003	2007-12-31 23:59:59	2007-12-31
+insert into t2 values (10010,'2006-12-31 00:00:00','2006-12-31');
+insert into t2 values (10011,'2006-12-30 23:59:59','2006-12-30');
+explain format=json
+select * from t2 force index(a) where date(a)='2006-12-31';
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.005042291,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "range",
+          "possible_keys": ["a"],
+          "key": "a",
+          "key_length": "6",
+          "used_key_parts": ["a"],
+          "loops": 1,
+          "rows": 3,
+          "cost": 0.005042291,
+          "filtered": 100,
+          "index_condition": "t2.a between '2006-12-31 00:00:00' and '2006-12-31 23:59:59'"
+        }
+      }
+    ]
+  }
+}
+select * from t2 force index(a) where date(a)='2006-12-31';
+pk	a	b
+10010	2006-12-31 00:00:00	2006-12-31
+10001	2006-12-31 23:59:59	2006-12-31
+#
+# Test the TIMESTAMP column
+#
+create table t3 (a timestamp, b date, key(a));
+set time_zone="UTC";
+insert into t3
+select
+timestampadd(hour, B.a, date_add('2016-01-01', interval A.a*8 day)),
+date_add('2016-01-01', interval A.a*7 day)
+from t1 A, t0 B;
+# Results of those two queries must be equal:
+select count(*) from t3 force index(a) where year(a)= 2016;
+count(*)
+460
+# The result must be the same as this query's:
+select count(*) from t3 force index(a) where a >= '2016-01-01 00:00:00'
+    and a <= '2016-12-31 23:59:59.999999';
+count(*)
+460
+explain format=json
+select count(*) from t3 force index(a) where year(a)= 2016;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.064493934,
+    "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.064493934,
+          "filtered": 100,
+          "attached_condition": "t3.a between '2016-01-01 00:00:00' and '2016-12-31 23:59:59'",
+          "using_index": true
+        }
+      }
+    ]
+  }
+}
+# 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)
+#
+explain format=json select * from t2 where year(a) = -1;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.16810023,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "ALL",
+          "loops": 1,
+          "rows": 1006,
+          "cost": 0.16810023,
+          "filtered": 100,
+          "attached_condition": "year(t2.a) = -1"
+        }
+      }
+    ]
+  }
+}
+explain format=json select * from t2 where year(a) > -5;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.16810023,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "ALL",
+          "loops": 1,
+          "rows": 1006,
+          "cost": 0.16810023,
+          "filtered": 100,
+          "attached_condition": "year(t2.a) > -5"
+        }
+      }
+    ]
+  }
+}
+explain format=json select * from t2 where year(a) < -1;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.16810023,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "ALL",
+          "loops": 1,
+          "rows": 1006,
+          "cost": 0.16810023,
+          "filtered": 100,
+          "attached_condition": "year(t2.a) < -1"
+        }
+      }
+    ]
+  }
+}
+explain format=json select * from t2 where year(a) <= 10000;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.16810023,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "ALL",
+          "loops": 1,
+          "rows": 1006,
+          "cost": 0.16810023,
+          "filtered": 100,
+          "attached_condition": "year(t2.a) <= 10000"
+        }
+      }
+    ]
+  }
+}
+explain format=json select * from t2 where year(a) >= 10020;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.16810023,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "ALL",
+          "loops": 1,
+          "rows": 1006,
+          "cost": 0.16810023,
+          "filtered": 100,
+          "attached_condition": "year(t2.a) >= 10020"
+        }
+      }
+    ]
+  }
+}
+explain format=json select * from t2 where date(a) = '10000-01-01';
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.16810023,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "ALL",
+          "loops": 1,
+          "rows": 1006,
+          "cost": 0.16810023,
+          "filtered": 100,
+          "attached_condition": "cast(t2.a as date) = '10000-01-01'"
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Warning	1292	Incorrect datetime value: '10000-01-01'
+explain format=json select * from t2 where date(a) < '-1-01-01';
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "cost": 0.16810023,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "ALL",
+          "loops": 1,
+          "rows": 1006,
+          "cost": 0.16810023,
+          "filtered": 100,
+          "attached_condition": "cast(t2.a as date) < '-1-01-01'"
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Warning	1292	Incorrect datetime value: '-1-01-01'
+drop table t0,t1,t2,t3;
+drop view v1;
+drop procedure sp;
diff --git a/mysql-test/main/sargable_date_cond.test b/mysql-test/main/sargable_date_cond.test
new file mode 100644
index 00000000000..21ec9f7ddd0
--- /dev/null
+++ b/mysql-test/main/sargable_date_cond.test
@@ -0,0 +1,539 @@
+#
+# MDEV-8320: Allow index usage for DATE(datetime_column) = const
+#
+
+--disable_warnings
+drop table if exists t0,t1,t2,t3;
+--enable_warnings
+
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+create table t1(a int);
+insert into t1 select A.a + B.a* 10 from t0 A, t0 B;
+
+create table t2 (pk int primary key, a datetime, b date, key(a), key(b));
+insert into t2
+select
+  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;
+
+--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;
+--echo # Check rewrite for a prepared statement:
+execute immediate 
+  "explain format=json select * from t2 force index(a) 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();
+--echo # Prepared statement with a placeholder
+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 < '2019-01-01';
+explain format=json select * from t2 force index(a) where year(a) <= 2018;
+execute immediate 
+  "explain format=json select * from t2 force index(a) 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';
+explain format=json select * from t2 force index(a) where year(a) >  2018;
+execute immediate 
+  "explain format=json select * from t2 force index(a) 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';
+explain format=json select * from t2 force index(a) where year(a) >= 2018;
+execute immediate 
+  "explain format=json select * from t2 force index(a) 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';
+explain format=json select * from t2 force index(a) where year(a) =  2017;
+execute immediate 
+  "explain format=json select * from t2 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();
+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
+--echo #
+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);
+execute immediate 
+  "explain format=json select * from t2 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();
+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';
+explain format=json select * from t2 force index(a) where 2018 <= year(a);
+execute immediate 
+  "explain format=json select * from t2 force index(a) 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';
+explain format=json select * from t2 force index(a) where 2018 >  year(a);
+execute immediate 
+  "explain format=json select * from t2 force index(a) 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';
+explain format=json select * from t2 force index(a) where 2018 >= year(a);
+execute immediate 
+  "explain format=json select * from t2 force index(a) 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';
+explain format=json select * from t2 force index(a) where 2018 =  year(a);
+execute immediate 
+  "explain format=json select * from t2 force index(a) 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
+--echo #
+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';
+execute immediate 
+  "explain format=json select * from t2 force index(a) 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';
+explain format=json select * from t2 force index(a) where date(a)<='2017-06-04';
+execute immediate 
+  "explain format=json select * from t2 force index(a) 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';
+explain format=json select * from t2 force index(a) where date(a)> '2018-06-01';
+execute immediate 
+  "explain format=json select * from t2 force index(a) 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';
+explain format=json select * from t2 force index(a) where date(a)>='2018-06-01';
+execute immediate 
+  "explain format=json select * from t2 force index(a) 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';
+explain format=json select * from t2 force index(a) where date(a)= '2017-06-02';
+execute immediate 
+  "explain format=json select * from t2 force index(a) 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
+--echo #
+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);
+execute immediate 
+  "explain format=json select * from t2 force index(a) 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;
+explain format=json select * from t2 force index(a) where '2017-06-03' >= date(a);
+execute immediate 
+  "explain format=json select * from t2 force index(a) 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;
+explain format=json select * from t2 force index(a) where '2018-06-01' < date(a);
+execute immediate 
+  "explain format=json select * from t2 force index(a) 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;
+explain format=json select * from t2 force index(a) where '2018-06-01' <= date(a);
+execute immediate 
+  "explain format=json select * from t2 force index(a) 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';
+explain format=json select * from t2 force index(a) where '2017-06-02' = date(a);
+execute immediate 
+  "explain format=json select * from t2 force index(a) 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),
+  t2 as t22 force index(a)
+  where year(t21.a) < 2018 and t21.b > '2017-11-01'
+  and date(t22.a) >= '2017-02-01' and t22.b > '2017-11-01';
+
+--echo #
+--echo # Incorrect const values processing (no rewrite is possible)
+--echo #
+explain format=json select * from t2 where year(a) = -1;
+explain format=json select * from t2 where year(a) > -5;
+explain format=json select * from t2 where year(a) < -1;
+explain format=json select * from t2 where year(a) <= 10000;
+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';
+
+--echo #
+--echo # Try DATE function and DATE (not DATETIME) column:
+--echo #
+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 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 force index(b) 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 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
+--echo #
+insert into t2 values (10001,'2006-12-31 23:59:59','2006-12-31');
+insert into t2 values (10002,'2007-01-01 00:00:00','2007-01-01');
+insert into t2 values (10003,'2007-12-31 23:59:59','2007-12-31');
+insert into t2 values (10004,'2008-01-01 00:00:00','2008-01-01');
+
+explain format=json
+select * from t2 force index(b) where year(b)=2007;
+select * from t2 force index(b) where year(b)=2007;
+
+insert into t2 values (10010,'2006-12-31 00:00:00','2006-12-31');
+insert into t2 values (10011,'2006-12-30 23:59:59','2006-12-30');
+
+explain format=json
+select * from t2 force index(a) where date(a)='2006-12-31';
+select * from t2 force index(a) where date(a)='2006-12-31';
+
+--echo #
+--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)
+set time_zone="UTC"; # To make sure we avoid daylight saving time shifts
+insert into t3
+  select
+    timestampadd(hour, B.a, date_add('2016-01-01', interval A.a*8 day)),
+    date_add('2016-01-01', interval A.a*7 day)
+  from t1 A, t0 B;
+
+--echo # Results of those two queries must be equal:
+let $q= select count(*) from t3 force index(a) where year(a)= 2016;
+eval $q;
+--echo # The result must be the same as this query's:
+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 #
+--echo # Incorrect const values processing (no rewrite is possible)
+--echo #
+explain format=json select * from t2 where year(a) = -1;
+explain format=json select * from t2 where year(a) > -5;
+explain format=json select * from t2 where year(a) < -1;
+explain format=json select * from t2 where year(a) <= 10000;
+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;
+drop view v1;
+drop procedure sp;