summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorOleg Smirnov <olernov@gmail.com>2023-02-17 19:21:30 +0700
committerOleg Smirnov <olernov@gmail.com>2023-04-25 20:21:35 +0700
commitf0b665f880bb6a6864660818d926fde9db18fa3e (patch)
tree98c94847fde17e9d25780d65075191547cf61812
parent54c11273e340620f319d0675166bd04b6e64db26 (diff)
downloadmariadb-git-f0b665f880bb6a6864660818d926fde9db18fa3e.tar.gz
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
Diffstat
-rw-r--r--libmysqld/CMakeLists.txt4
-rw-r--r--mysql-test/main/sargable_date_cond.result2328
-rw-r--r--mysql-test/main/sargable_date_cond.test539
-rw-r--r--sql/CMakeLists.txt4
-rw-r--r--sql/item.cc13
-rw-r--r--sql/item.h6
-rw-r--r--sql/item_cmpfunc.cc1
-rw-r--r--sql/item_cmpfunc.h13
-rw-r--r--sql/item_func.cc2
-rw-r--r--sql/item_func.h1
-rw-r--r--sql/item_timefunc.h4
-rw-r--r--sql/opt_rewrite_date_cmp.cc363
-rw-r--r--sql/opt_rewrite_date_cmp.h111
-rw-r--r--sql/sql_lex.h20
-rw-r--r--sql/sql_select.cc16
-rw-r--r--sql/sql_select.h1
-rw-r--r--sql/sql_yacc.yy3
17 files changed, 3421 insertions, 8 deletions
diff --git a/libmysqld/CMakeLists.txt b/libmysqld/CMakeLists.txt
index 5d5cc35e1be..fb27cb221c1 100644
--- a/libmysqld/CMakeLists.txt
+++ b/libmysqld/CMakeLists.txt
@@ -68,7 +68,9 @@ SET(SQL_EMBEDDED_SOURCES emb_qcache.cc libmysqld.c lib_sql.cc
../sql/key.cc ../sql/lock.cc ../sql/log.cc
../sql/log_event.cc ../sql/log_event_server.cc
../sql/mf_iocache.cc ../sql/my_decimal.cc
- ../sql/net_serv.cc ../sql/opt_range.cc ../sql/opt_sum.cc
+ ../sql/net_serv.cc ../sql/opt_range.cc
+ ../sql/opt_rewrite_date_cmp.cc
+ ../sql/opt_sum.cc
../sql/parse_file.cc ../sql/procedure.cc ../sql/protocol.cc
../sql/records.cc ../sql/repl_failsafe.cc ../sql/rpl_filter.cc
../sql/rpl_record.cc ../sql/des_key_file.cc
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;
diff --git a/sql/CMakeLists.txt b/sql/CMakeLists.txt
index 2ac802314ee..9ef1ee46d5c 100644
--- a/sql/CMakeLists.txt
+++ b/sql/CMakeLists.txt
@@ -111,7 +111,9 @@ SET (SQL_SOURCE
mf_iocache.cc my_decimal.cc
mysqld.cc net_serv.cc keycaches.cc
../sql-common/client_plugin.c
- opt_range.cc opt_sum.cc
+ opt_range.cc
+ opt_rewrite_date_cmp.cc
+ opt_sum.cc
../sql-common/pack.c parse_file.cc password.c procedure.cc
protocol.cc records.cc repl_failsafe.cc rpl_filter.cc
session_tracker.cc
diff --git a/sql/item.cc b/sql/item.cc
index 92f3d55fcf9..499d23aa0f2 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -6957,7 +6957,8 @@ Item *Item_int::clone_item(THD *thd)
}
-void Item_datetime::set(longlong packed, enum_mysql_timestamp_type ts_type)
+void Item_datetime::set_from_packed(longlong packed,
+ enum_mysql_timestamp_type ts_type)
{
unpack_time(packed, &ltime, ts_type);
}
@@ -6973,6 +6974,16 @@ longlong Item_datetime::val_int()
return TIME_to_ulonglong(&ltime);
}
+void Item_datetime::print(String *str, enum_query_type query_type)
+{
+ Datetime dt(current_thd, this);
+ String dt_str;
+ dt.to_string(&dt_str, decimals);
+ str->append('\'');
+ str->append(dt_str);
+ str->append('\'');
+}
+
int Item_decimal::save_in_field(Field *field, bool no_conversions)
{
field->set_notnull();
diff --git a/sql/item.h b/sql/item.h
index 5956b810d51..5b8b858c13c 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -2514,6 +2514,8 @@ public:
{ return this; }
virtual Item *multiple_equality_transformer(THD *thd, uchar *arg)
{ return this; }
+ virtual Item* date_conds_transformer(THD *thd, uchar *arg)
+ { return this; }
virtual bool expr_cache_is_needed(THD *) { return FALSE; }
virtual Item *safe_charset_converter(THD *thd, CHARSET_INFO *tocs);
bool needs_charset_converter(uint32 length, CHARSET_INFO *tocs) const
@@ -4514,12 +4516,14 @@ public:
int save_in_field(Field *field, bool no_conversions) override;
longlong val_int() override;
double val_real() override { return (double)val_int(); }
- void set(longlong packed, enum_mysql_timestamp_type ts_type);
+ void set(const MYSQL_TIME *datetime) { ltime= *datetime; }
+ void set_from_packed(longlong packed, enum_mysql_timestamp_type ts_type);
bool get_date(THD *thd, MYSQL_TIME *to, date_mode_t fuzzydate) override
{
*to= ltime;
return false;
}
+ void print(String *str, enum_query_type query_type) override;
};
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index fcdb2aaf2d4..b5f747219a5 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -7833,3 +7833,4 @@ Item *Item_equal::multiple_equality_transformer(THD *thd, uchar *arg)
break;
}
}
+
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index efe0bf59559..7f808580470 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -25,6 +25,7 @@
#include "item_func.h" /* Item_int_func, Item_bool_func */
#include "item.h"
+#include "opt_rewrite_date_cmp.h"
extern Item_result item_cmp_type(Item_result a,Item_result b);
inline Item_result item_cmp_type(const Item *a, const Item *b)
@@ -789,6 +790,8 @@ public:
friend class Arg_comparator;
Item *get_copy(THD *thd) override
{ return get_item_copy<Item_func_eq>(thd, this); }
+ Item* date_conds_transformer(THD *thd, uchar *arg) override
+ { return do_date_conds_transformation(thd, this); }
};
class Item_func_equal final :public Item_bool_rowready_func2
@@ -838,6 +841,8 @@ public:
Item *negated_item(THD *thd) override;
Item *get_copy(THD *thd) override
{ return get_item_copy<Item_func_ge>(thd, this); }
+ Item* date_conds_transformer(THD *thd, uchar *arg) override
+ { return do_date_conds_transformation(thd, this); }
};
@@ -858,6 +863,8 @@ public:
Item *negated_item(THD *thd) override;
Item *get_copy(THD *thd) override
{ return get_item_copy<Item_func_gt>(thd, this); }
+ Item* date_conds_transformer(THD *thd, uchar *arg) override
+ { return do_date_conds_transformation(thd, this); }
};
@@ -878,6 +885,8 @@ public:
Item *negated_item(THD *thd) override;
Item *get_copy(THD *thd) override
{ return get_item_copy<Item_func_le>(thd, this); }
+ Item* date_conds_transformer(THD *thd, uchar *arg) override
+ { return do_date_conds_transformation(thd, this); }
};
@@ -898,6 +907,8 @@ public:
Item *negated_item(THD *thd) override;
Item *get_copy(THD *thd) override
{ return get_item_copy<Item_func_lt>(thd, this); }
+ Item* date_conds_transformer(THD *thd, uchar *arg) override
+ { return do_date_conds_transformation(thd, this); }
};
@@ -1584,7 +1595,7 @@ public:
{
packed_longlong *val= reinterpret_cast<packed_longlong*>(base)+pos;
Item_datetime *dt= static_cast<Item_datetime*>(item);
- dt->set(val->val, type_handler()->mysql_timestamp_type());
+ dt->set_from_packed(val->val, type_handler()->mysql_timestamp_type());
}
friend int cmp_longlong(void *cmp_arg, packed_longlong *a,packed_longlong *b);
};
diff --git a/sql/item_func.cc b/sql/item_func.cc
index a94dcefe32f..61b3304a2b6 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -277,7 +277,6 @@ bool Item_func::check_argument_types_scalar(uint start, uint end) const
return false;
}
-
/*
Resolve references to table column for a function and its argument
@@ -362,6 +361,7 @@ Item_func::fix_fields(THD *thd, Item **ref)
if (fix_length_and_dec(thd))
return TRUE;
base_flags|= item_base_t::FIXED;
+
return FALSE;
}
diff --git a/sql/item_func.h b/sql/item_func.h
index 6e714814526..dcb04a62727 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -78,6 +78,7 @@ public:
JSON_EXTRACT_FUNC, JSON_VALID_FUNC, ROWNUM_FUNC,
CASE_SEARCHED_FUNC, // Used by ColumnStore/Spider
CASE_SIMPLE_FUNC, // Used by ColumnStore/spider,
+ DATE_FUNC, YEAR_FUNC
};
static scalar_comparison_op functype_to_scalar_comparison_op(Functype type)
{
diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h
index a5f6d9307c6..953984e4c72 100644
--- a/sql/item_timefunc.h
+++ b/sql/item_timefunc.h
@@ -462,6 +462,7 @@ public:
static LEX_CSTRING name= {STRING_WITH_LEN("year") };
return name;
}
+ enum Functype functype() const override { return YEAR_FUNC; }
enum_monotonicity_info get_monotonicity_info() const override;
longlong val_int_endpoint(bool left_endp, bool *incl_endp) override;
bool fix_length_and_dec(THD *thd) override
@@ -1334,6 +1335,7 @@ public:
{
print_cast_temporal(str, query_type);
}
+ enum Functype functype() const override { return DATE_FUNC; }
bool get_date(THD *thd, MYSQL_TIME *ltime, date_mode_t fuzzydate) override;
bool fix_length_and_dec(THD *thd) override
{
@@ -2025,6 +2027,4 @@ public:
return false;
}
};
-
-
#endif /* ITEM_TIMEFUNC_INCLUDED */
diff --git a/sql/opt_rewrite_date_cmp.cc b/sql/opt_rewrite_date_cmp.cc
new file mode 100644
index 00000000000..234dbb09311
--- /dev/null
+++ b/sql/opt_rewrite_date_cmp.cc
@@ -0,0 +1,363 @@
+/*
+ Copyright (c) 2023, MariaDB
+
+ This program is free software; you can redistribute it and/or modify
+ it under the terms of the GNU General Public License as published by
+ the Free Software Foundation; version 2 of the License.
+
+ This program is distributed in the hope that it will be useful,
+ but WITHOUT ANY WARRANTY; without even the implied warranty of
+ MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+ GNU General Public License for more details.
+
+ You should have received a copy of the GNU General Public License
+ along with this program; if not, write to the Free Software
+ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1335 USA */
+
+
+/**
+ @file
+
+ @brief
+ Rewrites that make non-sargable date[time] comparisons sargable.
+*/
+
+#ifdef USE_PRAGMA_IMPLEMENTATION
+#pragma implementation // gcc: Class implementation
+#endif
+
+#include "mariadb.h"
+#include "sql_priv.h"
+#include "my_json_writer.h"
+#include "opt_rewrite_date_cmp.h"
+
+Date_cmp_func_rewriter::Date_cmp_func_rewriter(THD* thd,
+ Item_func_eq *item_func) :
+ thd(thd),
+ result(item_func)
+{
+ if (!check_cond_match_and_prepare(item_func))
+ return;
+
+ /*
+ This is an equality. Do a rewrite like this:
+ "YEAR(col) = val" -> col BETWEEN year_start(val) AND year_end(val)
+ "DATE(col) = val" -> col BETWEEN day_start(val) AND day_end(val)
+ */
+ Item *start_bound, *end_bound;
+ if (!(start_bound= create_start_bound()) || !(end_bound= create_end_bound()))
+ return;
+ Item *new_cond;
+ if (!(new_cond= new (thd->mem_root) Item_func_between(thd, field_ref,
+ start_bound, end_bound)))
+ return;
+ if (!new_cond->fix_fields(thd, &new_cond))
+ result= new_cond;
+}
+
+
+Date_cmp_func_rewriter::Date_cmp_func_rewriter(THD* thd,
+ Item_func_ge *item_func) :
+ thd(thd),
+ result(item_func)
+{
+ if (!check_cond_match_and_prepare(item_func))
+ return;
+ rewrite_le_gt_lt_ge();
+}
+
+
+Date_cmp_func_rewriter::Date_cmp_func_rewriter(THD* thd,
+ Item_func_lt *item_func) :
+ thd(thd),
+ result(item_func)
+{
+ if (!check_cond_match_and_prepare(item_func))
+ return;
+ rewrite_le_gt_lt_ge();
+}
+
+
+Date_cmp_func_rewriter::Date_cmp_func_rewriter(THD* thd,
+ Item_func_gt *item_func) :
+ thd(thd),
+ result(item_func)
+{
+ if (!check_cond_match_and_prepare(item_func))
+ return;
+ rewrite_le_gt_lt_ge();
+}
+
+
+Date_cmp_func_rewriter::Date_cmp_func_rewriter(THD* thd,
+ Item_func_le*item_func) :
+ thd(thd),
+ result(item_func)
+{
+ if (!check_cond_match_and_prepare(item_func))
+ return;
+ rewrite_le_gt_lt_ge();
+}
+
+
+bool Date_cmp_func_rewriter::check_cond_match_and_prepare(
+ Item_bool_rowready_func2 *item_func)
+{
+ if (thd->lex->is_ps_or_view_context_analysis())
+ {
+ DBUG_ASSERT(0);
+ return false;
+ }
+
+ Item **args= item_func->arguments();
+ rewrite_func_type= item_func->functype();
+ bool condition_matches= false;
+ const Type_handler *comparison_type= item_func->get_comparator()->
+ compare_type_handler();
+
+ /*
+ Check if this is "YEAR(indexed_col) CMP const_item" or
+ "DATE(indexed_col) CMP const_item"
+ */
+ if ((field_ref= is_date_rounded_field(args[0], comparison_type,
+ &argument_func_type)) &&
+ args[1]->basic_const_item())
+ {
+ const_arg_value= args[1];
+ condition_matches= true;
+ }
+ else
+ /*
+ Check if this is "const_item CMP YEAR(indexed_col)" or
+ "const_item CMP DATE(indexed_col)"
+ */
+ if ((field_ref= is_date_rounded_field(args[1], comparison_type,
+ &argument_func_type)) &&
+ args[0]->basic_const_item())
+ {
+ /*
+ Ok, the condition has form like "const<YEAR(col)"/"const<DATE(col)".
+ Turn it around to be "YEAR(col)>const"/"DATE(col)>const"
+ */
+ const_arg_value= args[0];
+
+ rewrite_func_type= item_func->rev_functype();
+ condition_matches= true;
+ }
+ return condition_matches;
+}
+
+/*
+ Check if the passed item is YEAR(key_col) or DATE(key_col).
+
+ Also
+ - key_col must be covered by an index usable by the current query
+ - key_col must have a DATE[TIME] or TIMESTAMP type
+ - The value of the YEAR(..) or DATE(..) function must be compared using an a
+ appropriate comparison_type.
+
+ @param item IN Item to check
+ @param comparison_type IN Which datatype is used to compare the item value
+ @param out_func_type OUT Function (is it YEAR or DATE)
+
+ @return
+ key_col if the check suceeded
+ NULL otherwise
+*/
+Item_field *Date_cmp_func_rewriter::is_date_rounded_field(Item* item,
+ const Type_handler *comparison_type,
+ Item_func::Functype *out_func_type) const
+{
+ if (item->type() != Item::FUNC_ITEM)
+ return nullptr;
+
+ Item_func::Functype func_type= ((Item_func*)item)->functype();
+ bool function_ok= false;
+ switch (func_type) {
+ case Item_func::YEAR_FUNC:
+ // The value of YEAR(x) must be compared as integer
+ if (comparison_type == &type_handler_slonglong)
+ function_ok= true;
+ break;
+ case Item_func::DATE_FUNC:
+ // The value of DATE(x) must be compared as dates.
+ if (comparison_type == &type_handler_newdate)
+ function_ok= true;
+ break;
+ default:
+ ;// do nothing
+ }
+
+ if (function_ok)
+ {
+ Item* arg= ((Item_func*)item)->arguments()[0];
+ // Check if the argument is a column that's covered by some index
+ if (arg->real_item()->type() == Item::FIELD_ITEM)
+ {
+ Item_field *item_field= (Item_field*)(arg->real_item());
+ const key_map * used_indexes=
+ &item_field->field->table->keys_in_use_for_query;
+ enum_field_types field_type= item_field->field_type();
+ if ((field_type == MYSQL_TYPE_DATE ||
+ field_type == MYSQL_TYPE_DATETIME ||
+ field_type == MYSQL_TYPE_NEWDATE ||
+ field_type == MYSQL_TYPE_TIMESTAMP) &&
+ item_field->field->part_of_key.is_overlapping(*used_indexes))
+ {
+ *out_func_type= func_type;
+ return item_field;
+ }
+ }
+ }
+ return nullptr;
+}
+
+
+void Date_cmp_func_rewriter::rewrite_le_gt_lt_ge()
+{
+ if (rewrite_func_type == Item_func::LE_FUNC ||
+ rewrite_func_type == Item_func::GT_FUNC)
+ {
+ const_arg_value= create_end_bound();
+ }
+ else if (rewrite_func_type == Item_func::LT_FUNC ||
+ rewrite_func_type == Item_func::GE_FUNC)
+ {
+ const_arg_value= create_start_bound();
+ }
+ if (!const_arg_value)
+ return;
+ Item *repl= create_cmp_func(rewrite_func_type, field_ref, const_arg_value);
+ if (!repl)
+ return;
+ if (!repl->fix_fields(thd, &repl))
+ result= repl;
+}
+
+
+Item *Date_cmp_func_rewriter::create_start_bound()
+{
+ Item_datetime *res;
+ MYSQL_TIME const_arg_ts;
+ memset(&const_arg_ts, 0, sizeof(const_arg_ts));
+ const_arg_ts.time_type= MYSQL_TIMESTAMP_DATETIME;
+ switch (argument_func_type) {
+ case Item_func::YEAR_FUNC:
+ const_arg_ts.year= static_cast<unsigned int>(const_arg_value->val_int());
+ const_arg_ts.month= 1;
+ const_arg_ts.day= 1;
+ if (check_datetime_range(&const_arg_ts))
+ return nullptr;
+ res= new (thd->mem_root) Item_datetime(thd);
+ res->set(&const_arg_ts);
+ break;
+ case Item_func::DATE_FUNC:
+ if (field_ref->field->type() == MYSQL_TYPE_DATE)
+ return const_arg_value;
+ else
+ {
+ Datetime const_arg_dt(current_thd, const_arg_value);
+ if (!const_arg_dt.is_valid_datetime())
+ return nullptr;
+ res= new (thd->mem_root) Item_datetime(thd);
+ const_arg_dt.copy_to_mysql_time(&const_arg_ts);
+ const_arg_ts.second_part= const_arg_ts.second=
+ const_arg_ts.minute= const_arg_ts.hour= 0;
+ const_arg_ts.time_type= MYSQL_TIMESTAMP_DATETIME;
+ res->set(&const_arg_ts);
+ }
+ break;
+ default:
+ DBUG_ASSERT(0);
+ res= nullptr;
+ break;
+ }
+ return res;
+}
+
+
+Item *Date_cmp_func_rewriter::create_end_bound()
+{
+ Item_datetime *res;
+ MYSQL_TIME const_arg_ts;
+ memset(&const_arg_ts, 0, sizeof(const_arg_ts));
+ const_arg_ts.time_type= MYSQL_TIMESTAMP_DATETIME;
+ switch (argument_func_type) {
+ case Item_func::YEAR_FUNC:
+ const_arg_ts.year= static_cast<unsigned int>(const_arg_value->val_int());
+ const_arg_ts.month= 12;
+ const_arg_ts.day= 31;
+ const_arg_ts.hour= 23;
+ const_arg_ts.minute= TIME_MAX_MINUTE;
+ const_arg_ts.second= TIME_MAX_SECOND;
+ const_arg_ts.second_part= TIME_MAX_SECOND_PART;
+ if (check_datetime_range(&const_arg_ts))
+ return nullptr;
+ res= new (thd->mem_root) Item_datetime(thd);
+ res->set(&const_arg_ts);
+ break;
+ case Item_func::DATE_FUNC:
+ if (field_ref->field->type() == MYSQL_TYPE_DATE)
+ return const_arg_value;
+ else
+ {
+ res= new (thd->mem_root) Item_datetime(thd);
+ Datetime const_arg_dt(current_thd, const_arg_value);
+ if (!const_arg_dt.is_valid_datetime())
+ return nullptr;
+ const_arg_dt.copy_to_mysql_time(&const_arg_ts);
+ const_arg_ts.hour= 23;
+ const_arg_ts.minute= TIME_MAX_MINUTE;
+ const_arg_ts.second= TIME_MAX_SECOND;
+ const_arg_ts.second_part=TIME_MAX_SECOND_PART;
+ const_arg_ts.time_type= MYSQL_TIMESTAMP_DATETIME;
+ res->set(&const_arg_ts);
+ }
+ break;
+ default:
+ DBUG_ASSERT(0);
+ res= nullptr;
+ break;
+ }
+ return res;
+}
+
+
+/*
+ Create an Item for "arg1 $CMP arg2", where $CMP is specified by func_type.
+*/
+Item *Date_cmp_func_rewriter::create_cmp_func(Item_func::Functype func_type,
+ Item *arg1, Item *arg2)
+{
+ Item *res;
+ switch (func_type) {
+ case Item_func::GE_FUNC:
+ res= new (thd->mem_root) Item_func_ge(thd, arg1, arg2);
+ break;
+ case Item_func::GT_FUNC:
+ res= new (thd->mem_root) Item_func_gt(thd, arg1, arg2);
+ break;
+ case Item_func::LE_FUNC:
+ res= new (thd->mem_root) Item_func_le(thd, arg1, arg2);
+ break;
+ case Item_func::LT_FUNC:
+ res= new (thd->mem_root) Item_func_lt(thd, arg1, arg2);
+ break;
+ default:
+ DBUG_ASSERT(0);
+ res= NULL;
+ }
+ return res;
+}
+
+void trace_date_item_rewrite(THD *thd, Item *new_item, Item *old_item)
+{
+ if (new_item != old_item)
+ {
+ Json_writer_object trace_wrapper(thd);
+ trace_wrapper.add("transformation", "date_conds_into_sargable")
+ .add("before", old_item)
+ .add("after", new_item);
+ }
+}
+
diff --git a/sql/opt_rewrite_date_cmp.h b/sql/opt_rewrite_date_cmp.h
new file mode 100644
index 00000000000..9f6e1d74ffa
--- /dev/null
+++ b/sql/opt_rewrite_date_cmp.h
@@ -0,0 +1,111 @@
+/*
+ Copyright (c) 2023, MariaDB
+
+ This program is free software; you can redistribute it and/or modify
+ it under the terms of the GNU General Public License as published by
+ the Free Software Foundation; version 2 of the License.
+
+ This program is distributed in the hope that it will be useful,
+ but WITHOUT ANY WARRANTY; without even the implied warranty of
+ MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+ GNU General Public License for more details.
+
+ You should have received a copy of the GNU General Public License
+ along with this program; if not, write to the Free Software
+ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1335 USA */
+
+#ifndef OPT_REWRITE_DATE_CMP_INCLUDED
+#define OPT_REWRITE_DATE_CMP_INCLUDED
+
+class Item_func_eq;
+class Item_func_ge;
+class Item_func_gt;
+class Item_func_le;
+class Item_func_lt;
+class Item_bool_rowready_func2;
+
+/*
+ @brief Class responsible for rewriting datetime comparison condition.
+ It rewrites non-sargable conditions into sargable.
+
+ @detail
+ The intent of this class is to do equivalent rewrites as follows:
+
+ 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 >= year_start(val) AND col<=year_end(val)
+
+ Also the same is done for comparisons with DATE(col):
+
+ DATE(col) <= val -> col <= day_end(val)
+
+ if col has a DATE type (not DATETIME), then the rewrite becomes:
+
+ DATE(col) <= val -> col <= val
+
+ @usage
+ Date_cmp_func_rewriter rwr(thd, item_func);
+ Item *new_item= rwr.get_rewrite_result();
+
+ Returned new_item points to an item that item_func was rewritten to.
+ new_item already has fixed fields (fix_fields() was called).
+ If no rewrite happened, new_item points to the initial item_func parameter
+
+ @todo
+ Also handle conditions in form "YEAR(date_col) BETWEEN 2014 AND 2017"
+ and "YEAR(col) = c1 AND MONTH(col) = c2"
+*/
+class Date_cmp_func_rewriter
+{
+public:
+ Date_cmp_func_rewriter(THD* thd, Item_func_eq *item_func);
+
+ Date_cmp_func_rewriter(THD* thd, Item_func_ge *item_func);
+
+ Date_cmp_func_rewriter(THD* thd, Item_func_gt *item_func);
+
+ Date_cmp_func_rewriter(THD* thd, Item_func_le *item_func);
+
+ Date_cmp_func_rewriter(THD* thd, Item_func_lt *item_func);
+
+ Item* get_rewrite_result() const { return result; }
+
+ Date_cmp_func_rewriter() = delete;
+ Date_cmp_func_rewriter(const Date_cmp_func_rewriter&) = delete;
+ Date_cmp_func_rewriter(Date_cmp_func_rewriter&&) = delete;
+
+private:
+ bool check_cond_match_and_prepare(Item_bool_rowready_func2 *item_func);
+ Item_field *is_date_rounded_field(Item* item,
+ const Type_handler *comparison_type,
+ Item_func::Functype *out_func_type) const;
+ void rewrite_le_gt_lt_ge();
+ Item *create_start_bound();
+ Item *create_end_bound();
+ Item *create_cmp_func(Item_func::Functype func_type, Item *arg1, Item *arg2);
+
+ THD *thd= nullptr;
+ Item *const_arg_value= nullptr;
+ Item_func::Functype rewrite_func_type= Item_func::UNKNOWN_FUNC;
+ Item_func::Functype argument_func_type= Item_func::UNKNOWN_FUNC;
+ Item_field *field_ref= nullptr;
+ Item *result= nullptr;
+};
+
+
+void trace_date_item_rewrite(THD *thd,Item *new_item, Item *old_item);
+
+template<typename T>
+Item* do_date_conds_transformation(THD *thd, T *item)
+{
+ Date_cmp_func_rewriter rwr(thd, item);
+ /* If the rewrite failed for some reason, we get the original item */
+ Item *new_item= rwr.get_rewrite_result();
+ trace_date_item_rewrite(thd, new_item, item);
+ return new_item;
+}
+
+
+#endif
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 5a7fa14916f..acf23d5ffa2 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -2348,6 +2348,20 @@ public:
bool uses_stored_routines() const
{ return sroutines_list.elements != 0; }
+ void set_date_funcs_used_flag()
+ {
+ date_funcs_used_flag= true;
+ }
+
+ /*
+ Returns TRUE if date functions such as YEAR(), MONTH() or DATE()
+ are used in this LEX
+ */
+ bool are_date_funcs_used() const
+ {
+ return date_funcs_used_flag;
+ }
+
private:
/**
@@ -2388,6 +2402,12 @@ private:
be accessed while executing a statement.
*/
uint32 stmt_accessed_table_flag;
+
+ /*
+ Flag indicating that date functions such as YEAR(), MONTH() or DATE() are
+ used in this LEX
+ */
+ bool date_funcs_used_flag= false;
};
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 4b01a96a132..e98121231a7 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -2297,6 +2297,8 @@ JOIN::optimize_inner()
}
transform_in_predicates_into_equalities(thd);
+ if (thd->lex->are_date_funcs_used())
+ transform_date_conds_into_sargable();
conds= optimize_cond(this, conds, join_list, ignore_on_expr,
&cond_value, &cond_equal, OPT_LINK_EQUAL_FIELDS);
@@ -32286,6 +32288,20 @@ bool JOIN::transform_in_predicates_into_equalities(THD *thd)
/**
@brief
+ Rewrite datetime comparison conditions into sargable.
+ See details in the description for class Date_cmp_func_rewriter
+*/
+
+bool JOIN::transform_date_conds_into_sargable()
+{
+ DBUG_ENTER("JOIN::transform_date_conds_into_sargable");
+ DBUG_RETURN(transform_all_conds_and_on_exprs(
+ thd, &Item::date_conds_transformer));
+}
+
+
+/**
+ @brief
Transform all items in WHERE and ON expressions using a given transformer
@param thd The context of the statement
diff --git a/sql/sql_select.h b/sql/sql_select.h
index f908484444b..0d53d4c9798 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -1920,6 +1920,7 @@ private:
void free_pushdown_handlers(List<TABLE_LIST>& join_list);
void init_join_cache_and_keyread();
bool transform_in_predicates_into_equalities(THD *thd);
+ bool transform_date_conds_into_sargable();
bool transform_all_conds_and_on_exprs(THD *thd,
Item_transformer transformer);
bool transform_all_conds_and_on_exprs_in_join_list(THD *thd,
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index ccce38a4c60..c8a9ec0cb41 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -9867,6 +9867,7 @@ function_call_keyword:
$$= new (thd->mem_root) Item_date_typecast(thd, $3);
if (unlikely($$ == NULL))
MYSQL_YYABORT;
+ Lex->set_date_funcs_used_flag();
}
| DAY_SYM '(' expr ')'
{
@@ -9929,6 +9930,7 @@ function_call_keyword:
$$= new (thd->mem_root) Item_func_month(thd, $3);
if (unlikely($$ == NULL))
MYSQL_YYABORT;
+ Lex->set_date_funcs_used_flag();
}
| RIGHT '(' expr ',' expr ')'
{
@@ -9979,6 +9981,7 @@ function_call_keyword:
$$= new (thd->mem_root) Item_func_year(thd, $3);
if (unlikely($$ == NULL))
MYSQL_YYABORT;
+ Lex->set_date_funcs_used_flag();
}
;
View Lorry Controller Status