maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #11015
Review for MDEV-8320 Allow index usage for DATE(datetime_column) = const
Hello Sergey,
I generally like your patch for MDEV-8320:
http://lists.askmonty.org/pipermail/commits/2017-November/011680.html
However, there is one problem with it.
We're changing the code to have data type plugins.
Every data type plugin will provide its own list of SQL functions.
Those data type specific functions should be able to use
the same optimization approach without having to change
the server code.
Therefore, everything related to YEAR(field) transformation
should reside inside Item_func_year,
everything related to DATE(field) transformation should reside
inside Item_date_typecast.
I reorganized the code to implement this idea.
Note, also I changed the logic behind DATE(indexed_date_field)
transformation slightly. The DATE() just gets removed.
DATE(indexed_date_field) = const
is changed to:
indexed_date_field = const
instead of:
indexed_date_field BETWEEN ... AND ...
Please have a look:
sargable_date_cond.result is slightly different in my version.
Greetings.
diff --git a/mysql-test/r/sargable_date_cond.result b/mysql-test/r/sargable_date_cond.result
new file mode 100644
index 0000000..1b9f83a
--- /dev/null
+++ b/mysql-test/r/sargable_date_cond.result
@@ -0,0 +1,447 @@
+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 + C.a * 100 from t0 A, t0 B, t0 C;
+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 day), interval B.a hour),
+date_add('2017-01-01', interval A.a day)
+from t1 A, t0 B;
+#
+# "YEAR(datetime_col) CMP year_value", basic checks
+#
+explain format=json select * from t2 force index(a) where year(a)< 2017;
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "t2",
+ "access_type": "range",
+ "possible_keys": ["a"],
+ "key": "a",
+ "key_length": "6",
+ "used_key_parts": ["a"],
+ "rows": 1,
+ "filtered": 100,
+ "index_condition": "t2.a < <cache>(year_start(2017))"
+ }
+ }
+}
+explain format=json select * from t2 force index(a) where year(a)<= 2017;
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "t2",
+ "access_type": "range",
+ "possible_keys": ["a"],
+ "key": "a",
+ "key_length": "6",
+ "used_key_parts": ["a"],
+ "rows": 2556,
+ "filtered": 100,
+ "index_condition": "t2.a <= <cache>(year_end(2017))"
+ }
+ }
+}
+explain format=json select * from t2 force index(a) where year(a)> 2017;
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "t2",
+ "access_type": "range",
+ "possible_keys": ["a"],
+ "key": "a",
+ "key_length": "6",
+ "used_key_parts": ["a"],
+ "rows": 7444,
+ "filtered": 100,
+ "index_condition": "t2.a > <cache>(year_end(2017))"
+ }
+ }
+}
+explain format=json select * from t2 force index(a) where year(a)>= 2017;
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "t2",
+ "access_type": "range",
+ "possible_keys": ["a"],
+ "key": "a",
+ "key_length": "6",
+ "used_key_parts": ["a"],
+ "rows": 10000,
+ "filtered": 100,
+ "index_condition": "t2.a >= <cache>(year_start(2017))"
+ }
+ }
+}
+explain format=json select * from t2 force index(a) where year(a)= 2017;
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "t2",
+ "access_type": "range",
+ "possible_keys": ["a"],
+ "key": "a",
+ "key_length": "6",
+ "used_key_parts": ["a"],
+ "rows": 2556,
+ "filtered": 100,
+ "index_condition": "t2.a >= <cache>(year_start(2017)) and t2.a <= <cache>(year_end(2017))"
+ }
+ }
+}
+#
+# "YEAR(datetime_col) CMP year_value", reverse argument order
+#
+explain format=json select * from t2 force index(a) where 2017 < year(a);
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "t2",
+ "access_type": "range",
+ "possible_keys": ["a"],
+ "key": "a",
+ "key_length": "6",
+ "used_key_parts": ["a"],
+ "rows": 7444,
+ "filtered": 100,
+ "index_condition": "t2.a > <cache>(year_end(2017))"
+ }
+ }
+}
+explain format=json select * from t2 force index(a) where 2017 <= year(a);
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "t2",
+ "access_type": "range",
+ "possible_keys": ["a"],
+ "key": "a",
+ "key_length": "6",
+ "used_key_parts": ["a"],
+ "rows": 10000,
+ "filtered": 100,
+ "index_condition": "t2.a >= <cache>(year_start(2017))"
+ }
+ }
+}
+explain format=json select * from t2 force index(a) where 2017 > year(a);
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "t2",
+ "access_type": "range",
+ "possible_keys": ["a"],
+ "key": "a",
+ "key_length": "6",
+ "used_key_parts": ["a"],
+ "rows": 1,
+ "filtered": 100,
+ "index_condition": "t2.a < <cache>(year_start(2017))"
+ }
+ }
+}
+explain format=json select * from t2 force index(a) where 2017 >= year(a);
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "t2",
+ "access_type": "range",
+ "possible_keys": ["a"],
+ "key": "a",
+ "key_length": "6",
+ "used_key_parts": ["a"],
+ "rows": 2556,
+ "filtered": 100,
+ "index_condition": "t2.a <= <cache>(year_end(2017))"
+ }
+ }
+}
+explain format=json select * from t2 force index(a) where 2017 = year(a);
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "t2",
+ "access_type": "range",
+ "possible_keys": ["a"],
+ "key": "a",
+ "key_length": "6",
+ "used_key_parts": ["a"],
+ "rows": 2556,
+ "filtered": 100,
+ "index_condition": "t2.a >= <cache>(year_start(2017)) and t2.a <= <cache>(year_end(2017))"
+ }
+ }
+}
+#
+# "DATE(datetime_col) CMP date_value", basic checks
+#
+explain format=json select * from t2 force index(a) where date(a)< '2017-06-01';
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "t2",
+ "access_type": "range",
+ "possible_keys": ["a"],
+ "key": "a",
+ "key_length": "6",
+ "used_key_parts": ["a"],
+ "rows": 1057,
+ "filtered": 100,
+ "index_condition": "t2.a < <cache>(day_start('2017-06-01'))"
+ }
+ }
+}
+explain format=json select * from t2 force index(a) where date(a)<='2017-06-01';
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "t2",
+ "access_type": "range",
+ "possible_keys": ["a"],
+ "key": "a",
+ "key_length": "6",
+ "used_key_parts": ["a"],
+ "rows": 1064,
+ "filtered": 100,
+ "index_condition": "t2.a <= <cache>(day_end('2017-06-01'))"
+ }
+ }
+}
+explain format=json select * from t2 force index(a) where date(a)> '2017-06-01';
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "t2",
+ "access_type": "range",
+ "possible_keys": ["a"],
+ "key": "a",
+ "key_length": "6",
+ "used_key_parts": ["a"],
+ "rows": 8936,
+ "filtered": 100,
+ "index_condition": "t2.a > <cache>(day_end('2017-06-01'))"
+ }
+ }
+}
+explain format=json select * from t2 force index(a) where date(a)>='2017-06-01';
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "t2",
+ "access_type": "range",
+ "possible_keys": ["a"],
+ "key": "a",
+ "key_length": "6",
+ "used_key_parts": ["a"],
+ "rows": 8943,
+ "filtered": 100,
+ "index_condition": "t2.a >= <cache>(day_start('2017-06-01'))"
+ }
+ }
+}
+explain format=json select * from t2 force index(a) where date(a)= '2017-06-01';
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "t2",
+ "access_type": "range",
+ "possible_keys": ["a"],
+ "key": "a",
+ "key_length": "6",
+ "used_key_parts": ["a"],
+ "rows": 7,
+ "filtered": 100,
+ "index_condition": "t2.a >= <cache>(day_start('2017-06-01')) and t2.a <= <cache>(day_end('2017-06-01'))"
+ }
+ }
+}
+#
+# Check if "YEAR(date_col) CMP year_value" works
+#
+explain format=json select * from t2 force index(b) where year(b)< 2017;
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "t2",
+ "access_type": "range",
+ "possible_keys": ["b"],
+ "key": "b",
+ "key_length": "4",
+ "used_key_parts": ["b"],
+ "rows": 1,
+ "filtered": 100,
+ "index_condition": "t2.b < <cache>(year_start(2017))"
+ }
+ }
+}
+#
+# Try DATE function and DATE (not DATETIME) column:
+#
+explain format=json select * from t2 force index(b) where date(b)< '2017-06-01';
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "t2",
+ "access_type": "range",
+ "possible_keys": ["b"],
+ "key": "b",
+ "key_length": "4",
+ "used_key_parts": ["b"],
+ "rows": 1175,
+ "filtered": 100,
+ "index_condition": "t2.b < '2017-06-01'"
+ }
+ }
+}
+explain format=json select * from t2 force index(b) where date(b)='2017-06-01';
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "t2",
+ "access_type": "ref",
+ "possible_keys": ["b"],
+ "key": "b",
+ "key_length": "4",
+ "used_key_parts": ["b"],
+ "ref": ["const"],
+ "rows": 7,
+ "filtered": 100
+ }
+ }
+}
+#
+# 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,
+ "table": {
+ "table_name": "t2",
+ "access_type": "range",
+ "possible_keys": ["b"],
+ "key": "b",
+ "key_length": "4",
+ "used_key_parts": ["b"],
+ "rows": 3,
+ "filtered": 100,
+ "index_condition": "t2.b >= <cache>(year_start(2007)) and t2.b <= <cache>(year_end(2007))"
+ }
+ }
+}
+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,
+ "table": {
+ "table_name": "t2",
+ "access_type": "range",
+ "possible_keys": ["a"],
+ "key": "a",
+ "key_length": "6",
+ "used_key_parts": ["a"],
+ "rows": 3,
+ "filtered": 100,
+ "index_condition": "t2.a >= <cache>(day_start('2006-12-31')) and t2.a <= <cache>(day_end('2006-12-31'))"
+ }
+ }
+}
+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
+#
+# Check that conditions on TIMESTAMP columns are not rewritten
+#
+create table t3 (a timestamp, b date, key(a));
+insert into t3 select a,b from t2;
+explain format=json
+select * from t3 force index(a) where year(a)>= 2007;
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "t3",
+ "access_type": "ALL",
+ "rows": 10006,
+ "filtered": 100,
+ "attached_condition": "year(t3.a) >= 2007"
+ }
+ }
+}
+explain format=json
+select * from t3 force index(a) where a >= '2007-01-01 00:00:00';
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "t3",
+ "access_type": "range",
+ "possible_keys": ["a"],
+ "key": "a",
+ "key_length": "4",
+ "used_key_parts": ["a"],
+ "rows": 10004,
+ "filtered": 100,
+ "index_condition": "t3.a >= '2007-01-01 00:00:00'"
+ }
+ }
+}
+drop table t3;
+drop table t0,t1,t2;
diff --git a/mysql-test/t/sargable_date_cond.test b/mysql-test/t/sargable_date_cond.test
new file mode 100644
index 0000000..38ba4aa
--- /dev/null
+++ b/mysql-test/t/sargable_date_cond.test
@@ -0,0 +1,94 @@
+#
+# 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 + C.a * 100 from t0 A, t0 B, t0 C;
+
+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 day), interval B.a hour),
+ date_add('2017-01-01', interval A.a day)
+from t1 A, t0 B;
+
+--echo #
+--echo # "YEAR(datetime_col) CMP year_value", basic checks
+--echo #
+
+explain format=json select * from t2 force index(a) where year(a)< 2017;
+explain format=json select * from t2 force index(a) where year(a)<= 2017;
+explain format=json select * from t2 force index(a) where year(a)> 2017;
+explain format=json select * from t2 force index(a) where year(a)>= 2017;
+explain format=json select * from t2 force index(a) where year(a)= 2017;
+
+--echo #
+--echo # "YEAR(datetime_col) CMP year_value", reverse argument order
+--echo #
+
+explain format=json select * from t2 force index(a) where 2017 < year(a);
+explain format=json select * from t2 force index(a) where 2017 <= year(a);
+explain format=json select * from t2 force index(a) where 2017 > year(a);
+explain format=json select * from t2 force index(a) where 2017 >= year(a);
+explain format=json select * from t2 force index(a) where 2017 = year(a);
+
+--echo #
+--echo # "DATE(datetime_col) CMP date_value", basic checks
+--echo #
+explain format=json select * from t2 force index(a) where date(a)< '2017-06-01';
+explain format=json select * from t2 force index(a) where date(a)<='2017-06-01';
+explain format=json select * from t2 force index(a) where date(a)> '2017-06-01';
+explain format=json select * from t2 force index(a) where date(a)>='2017-06-01';
+explain format=json select * from t2 force index(a) where date(a)= '2017-06-01';
+
+--echo #
+--echo # Check if "YEAR(date_col) CMP year_value" works
+--echo #
+explain format=json select * from t2 force index(b) where year(b)< 2017;
+
+--echo #
+--echo # Try DATE function and DATE (not DATETIME) column:
+--echo #
+explain format=json select * from t2 force index(b) where date(b)< '2017-06-01';
+explain format=json select * from t2 force index(b) where date(b)='2017-06-01';
+
+--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 # Check that conditions on TIMESTAMP columns are not rewritten
+--echo #
+create table t3 (a timestamp, b date, key(a));
+insert into t3 select a,b from t2;
+explain format=json
+select * from t3 force index(a) where year(a)>= 2007;
+explain format=json
+select * from t3 force index(a) where a >= '2007-01-01 00:00:00';
+drop table t3;
+
+drop table t0,t1,t2;
+
diff --git a/sql/item.cc b/sql/item.cc
index d36e651..2a93b7e 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -10341,3 +10341,25 @@ void Item::register_in(THD *thd)
next= thd->free_list;
thd->free_list= this;
}
+
+
+Item_field*
+Item::get_indexed_field()
+{
+ if (real_item()->type() == Item::FIELD_ITEM)
+ {
+ Item_field *item_field= static_cast<Item_field*>(real_item());
+ const key_map * used_indexes=
+ &item_field->field->table->keys_in_use_for_query;
+ if (item_field->field->part_of_key.is_overlapping(*used_indexes))
+ return item_field;
+ }
+ return NULL;
+}
+
+
+Item *
+Item::rewrite_owner_comparison(THD *thd, Item_bool_rowready_func2 *owner)
+{
+ return owner;
+}
diff --git a/sql/item.h b/sql/item.h
index c7ef520..b05517d 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -96,6 +96,7 @@ struct TABLE_LIST;
void item_init(void); /* Init item functions */
class Item_field;
class Item_param;
+class Item_bool_rowready_func2;
class user_var_entry;
class JOIN;
struct KEY_FIELD;
@@ -1935,6 +1936,30 @@ class Item: public Value_source,
{
marker &= ~EXTRACTION_MASK;
}
+ /*
+ When this Item is an argument of a comparison predicate,
+ it can rewrite the owner comparison predicate to make it sargable.
+ For example, function YEAR in this condition:
+ YEAR(indexed_date_col) < YYYY
+ can rewrite the condition as:
+ indexed_date_col <= TIMESTAMP'YYYY-12-31 23:59:59.999999'
+ @param thd - Current THD
+ @param owner - The comparision predicate that has "this" as an argument.
+ @returns - The original condition (if rewrite is not possible),
+ the rewritten condition (if rewrite is possible),
+ or NULL on error.
+ */
+ virtual Item *rewrite_owner_comparison(THD *thd,
+ Item_bool_rowready_func2 *owner);
+ /*
+ Check if "this" is an Item_field covered by
+ an index usable by the current query.
+
+ @return
+ !NULL a pointer to Item_field on success
+ NULL the item is not an indexed field covered by the current query
+ */
+ Item_field *get_indexed_field();
};
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 775fb42..858d2d1 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -7070,3 +7070,178 @@ Item_bool_rowready_func2* Le_creator::create_swap(THD *thd, Item *a, Item *b) co
{
return new(thd->mem_root) Item_func_ge(thd, b, a);
}
+
+
+////////////////////////////////////////////////////////////////////////////
+// do_datetime_cmp_rewrites() and its utility functions
+/////////////////////////////////////////////////////////////////////////////
+
+/*
+ Create an Item for "arg1 $CMP arg2", where $CMP is specified by func_type.
+*/
+
+
+Item_bool_rowready_func2 *
+Item_bool_rowready_func2::create_by_functype(THD *thd, Functype func_type,
+ Item *arg1, Item *arg2)
+{
+ switch (func_type) {
+ case GE_FUNC: return new (thd->mem_root) Item_func_ge(thd, arg1, arg2);
+ case GT_FUNC: return new (thd->mem_root) Item_func_gt(thd, arg1, arg2);
+ case LE_FUNC: return new (thd->mem_root) Item_func_le(thd, arg1, arg2);
+ case LT_FUNC: return new (thd->mem_root) Item_func_lt(thd, arg1, arg2);
+ case EQ_FUNC: return new (thd->mem_root) Item_func_eq(thd, arg1, arg2);
+ case EQUAL_FUNC: return new (thd->mem_root) Item_func_equal(thd, arg1, arg2);
+ case NE_FUNC: return new (thd->mem_root) Item_func_ne(thd, arg1, arg2);
+ default:
+ DBUG_ASSERT(0);
+ }
+ return NULL;
+}
+
+
+/*
+ @brief Do comparison condition rewrites. Non-sargable conditions
+ can be rewritten into sargable.
+
+ @param thd The current THD
+
+ @detail
+ The intent of this function is to do equivalent rewrites as follows:
+
+ YEAR(col) <= val -> col <= YEAR_END(val)
+ YEAR(col) < val -> val < 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)
+
+ (There are Item classes implementing YEAR_END and YEAR_START but these
+ functions are not visible to the SQL parser)
+
+ 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
+
+ @todo
+ Also handle conditions in form "YEAR(date_col) BETWEEN 2014 AND 2017"
+
+ @return
+ Item that item_func was rewritten to.
+ If no rewrite happened, "this" is returned.
+*/
+
+Item *Item_bool_rowready_func2::do_cmp_rewrites(THD *thd)
+{
+ Item *res;
+ for (uint i= 0; i < 2; i++)
+ {
+ if (this != (res= args[i]->rewrite_owner_comparison(thd, this)))
+ return res;
+ }
+ return this;
+}
+
+
+Item *
+Item_func_eq::do_cmp_rewrites_func_to_range(THD *thd,
+ Func_to_range_rewrite
+ *field_ref_owner,
+ Item_field *field_ref,
+ Item *const_value)
+{
+ /*
+ Do a rewrite like this:
+ "YEAR(col) = val" -> col >= YEAR_START(val) AND col<=YEAR_END(val)
+ */
+ Item *start_const, *end_const;
+ if (!(start_const= field_ref_owner->create_start_bound(thd,
+ this, field_ref,
+ const_value)) ||
+ !(end_const= field_ref_owner->create_end_bound(thd,
+ this, field_ref,
+ const_value)))
+ return this;
+
+ Item *col_gt, *col_lt;
+ if (!(col_gt= new (thd->mem_root) Item_func_ge(thd, field_ref, start_const)) ||
+ !(col_lt= new (thd->mem_root) Item_func_le(thd, field_ref, end_const)))
+ {
+ /*
+ No needs to call fix_fields for col_gt and col_lt.
+ The call for new_cond->fix_fields() below will recursively fix them.
+ */
+ return this;
+ }
+
+ Item *new_cond;
+ if (!(new_cond= new (thd->mem_root) Item_cond_and(thd, col_gt, col_lt)) ||
+ new_cond->fix_fields(thd, &new_cond))
+ return this;
+
+ return new_cond;
+}
+
+
+Item *
+Item_bool_rowready_func2::do_cmp_rewrites_func_to_range(THD *thd,
+ Func_to_range_rewrite
+ *field_ref_owner,
+ Item_field *field_ref,
+ Item *const_value)
+{
+ /*
+ If field is the second argument in the current comparison predicate,
+ we reverse the predicate (for simplicity of the below code):
+ 1. YYYY > YEAR(field)
+ 2. YEAR(field) < YYYY
+ 3. field <= 'YYYY-12-31 23:59:59.999999'
+ */
+ Functype owner_func_type= args[1] == const_value ? functype() :
+ rev_functype();
+ if (owner_func_type == Item_func::LE_FUNC ||
+ owner_func_type == Item_func::GT_FUNC)
+ {
+ if (!(const_value= field_ref_owner->create_end_bound(thd, this, field_ref,
+ const_value)))
+ return this;
+ Item *repl;
+ if (!(repl= create_by_functype(thd, owner_func_type,
+ field_ref, const_value)))
+ return this;
+ return repl;
+ }
+ else if (owner_func_type == Item_func::LT_FUNC ||
+ owner_func_type == Item_func::GE_FUNC)
+ {
+ if (!(const_value= field_ref_owner->create_start_bound(thd, this, field_ref,
+ const_value)))
+ return this;
+ Item *repl;
+ if (!(repl= create_by_functype(thd, owner_func_type,
+ field_ref, const_value)))
+ return this;
+ return repl;
+ }
+ return this;
+}
+
+
+bool Item_bool_func::fix_fields(THD *thd, Item **ref)
+{
+ if (Item_int_func::fix_fields(thd, ref))
+ return true;
+ if (ref && !thd->lex->is_ps_or_view_context_analysis())
+ {
+ Item *new_item= do_cmp_rewrites(thd);
+ if (!new_item)
+ return true; // Error
+ if (new_item != this)
+ thd->change_item_tree(ref, new_item);
+ }
+ return false;
+}
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 2cb46de..5e3fd95 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -154,6 +154,14 @@ struct KEY_PART;
class Item_bool_func :public Item_int_func
{
+ /*
+ Rewrite the current function to make it sargable.
+ For example, this comparison predicate:
+ YEAR(field) < YYYY
+ can rewrite itself as:
+ field < TIMESTAMP'YYYY-12-31 23:59:59.999999'
+ */
+ virtual Item *do_cmp_rewrites(THD *thd) { return this; }
protected:
/*
Build a SEL_TREE for a simple predicate
@@ -218,6 +226,7 @@ class Item_bool_func :public Item_int_func
const Type_handler *type_handler() const { return &type_handler_long; }
bool is_bool_type() { return true; }
virtual CHARSET_INFO *compare_collation() const { return NULL; }
+ bool fix_fields(THD *, Item **);
void fix_length_and_dec() { decimals=0; max_length=1; }
uint decimal_precision() const { return 1; }
bool need_parentheses_in_default() { return true; }
@@ -490,6 +499,7 @@ class Item_bool_func2_with_rev :public Item_bool_func2
class Item_bool_rowready_func2 :public Item_bool_func2_with_rev
{
+ virtual Item *do_cmp_rewrites(THD *thd);
protected:
Arg_comparator cmp;
bool check_arguments() const
@@ -500,6 +510,9 @@ class Item_bool_rowready_func2 :public Item_bool_func2_with_rev
Item_bool_rowready_func2(THD *thd, Item *a, Item *b):
Item_bool_func2_with_rev(thd, a, b), cmp(tmp_arg, tmp_arg + 1)
{ }
+ static Item_bool_rowready_func2 *create_by_functype(THD *thd,
+ Functype func_type,
+ Item *arg1, Item *arg2);
void print(String *str, enum_query_type query_type)
{
Item_func::print_op(str, query_type);
@@ -539,6 +552,11 @@ class Item_bool_rowready_func2 :public Item_bool_func2_with_rev
return add_key_fields_optimize_op(join, key_fields, and_level,
usable_tables, sargables, false);
}
+ virtual Item *do_cmp_rewrites_func_to_range(THD *thd,
+ Func_to_range_rewrite
+ *field_ref_owner,
+ Item_field *field,
+ Item *const_value);
Item *build_clone(THD *thd, MEM_ROOT *mem_root)
{
Item_bool_rowready_func2 *clone=
@@ -687,6 +705,10 @@ class Item_func_nop_all :public Item_func_not_all
class Item_func_eq :public Item_bool_rowready_func2
{
bool abort_on_null;
+ Item *do_cmp_rewrites_func_to_range(THD *thd,
+ Func_to_range_rewrite *field_ref_owner,
+ Item_field *field,
+ Item *const_value);
public:
Item_func_eq(THD *thd, Item *a, Item *b):
Item_bool_rowready_func2(thd, a, b),
@@ -726,6 +748,7 @@ class Item_func_eq :public Item_bool_rowready_func2
class Item_func_equal :public Item_bool_rowready_func2
{
+ Item *do_cmp_rewrites(THD *thd) { return this; }
public:
Item_func_equal(THD *thd, Item *a, Item *b):
Item_bool_rowready_func2(thd, a, b) {}
@@ -815,6 +838,7 @@ class Item_func_lt :public Item_bool_rowready_func2
class Item_func_ne :public Item_bool_rowready_func2
{
+ Item *do_cmp_rewrites(THD *thd) { return this; }
protected:
SEL_TREE *get_func_mm_tree(RANGE_OPT_PARAM *param,
Field *field, Item *value)
diff --git a/sql/item_func.h b/sql/item_func.h
index de213df..5a33be8 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -31,6 +31,35 @@ extern "C" /* Bug in BSDI include file */
#endif
+class Func_to_range_rewrite
+{
+public:
+ /*
+ Create the start range value when rewriting a function
+ with a field as an argument to the field range, e.g.:
+ from: YEAR(field) < YYYY
+ to: field <= 'YYYY-12-31 23:59:59'
+ @param thd - the current THD
+ @param cmp - the comparison predicate
+ @param field_ref - the field, which is an argument for "cmp"
+ @param const_value - the constant, which is the second argument for "cmp"
+
+ @returns - the start range value for the rewrite, or NULL on error.
+ */
+ virtual Item *create_start_bound(THD *thd,
+ Item_bool_rowready_func2 *cmp,
+ Item_field *field_ref,
+ Item *const_value) const= 0;
+ /*
+ Create the end range value (see comments above).
+ */
+ virtual Item *create_end_bound(THD *thd,
+ Item_bool_rowready_func2 *cmp,
+ Item_field *field_ref,
+ Item *const_value) const= 0;
+};
+
+
class Item_func :public Item_func_or_sum
{
void sync_with_sum_func_and_with_field(List<Item> &list);
diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc
index 3a7684f..c7453bf 100644
--- a/sql/item_timefunc.cc
+++ b/sql/item_timefunc.cc
@@ -3332,3 +3332,182 @@ bool Item_func_last_day::get_date(MYSQL_TIME *ltime, ulonglong fuzzy_date)
ltime->time_type= MYSQL_TIMESTAMP_DATE;
return (null_value= 0);
}
+
+///////////////////////////////////////////////////////////////////////////////
+// Special item functions used by do_cmp_rewrites()
+///////////////////////////////////////////////////////////////////////////////
+
+
+bool Item_func_day_start::get_date(MYSQL_TIME *res, ulonglong fuzzy_date)
+{
+ if (get_arg0_date(res, fuzzy_date))
+ return (null_value=1);
+
+ res->second_part= res->second= res->minute= res->hour= 0;
+ res->time_type= MYSQL_TIMESTAMP_DATETIME;
+
+ return null_value= 0;
+}
+
+
+bool Item_func_day_end::get_date(MYSQL_TIME *res, ulonglong fuzzy_date)
+{
+ if (get_arg0_date(res, fuzzy_date))
+ return (null_value=1);
+
+ res->hour= 23;
+ res->second= res->minute= 59;
+ res->second_part= 999999;
+ res->time_type= MYSQL_TIMESTAMP_DATETIME;
+ return null_value= 0;
+}
+
+
+bool Item_func_year_start::get_date(MYSQL_TIME *res, ulonglong fuzzy_date)
+{
+ res->year= args[0]->val_int();
+ if ((null_value= args[0]->null_value || res->year >= 9999))
+ return 0;
+
+ res->day= res->month= 1;
+ res->second_part= res->second= res->minute= res->hour= 0;
+ res->time_type= MYSQL_TIMESTAMP_DATETIME;
+ res->neg= 0;
+ return null_value= 0;
+}
+
+
+bool Item_func_year_end::get_date(MYSQL_TIME *res, ulonglong fuzzy_date)
+{
+ res->year= args[0]->val_int();
+ if ((null_value= args[0]->null_value || res->year >= 9999))
+ return 0;
+
+ res->month= 12;
+ res->day= 31;
+ res->hour= 23;
+ res->second= res->minute= 59;
+ res->second_part= 999999;
+ res->time_type= MYSQL_TIMESTAMP_DATETIME;
+ res->neg= 0;
+ return null_value= 0;
+}
+
+
+Item *
+Item_func_year::rewrite_owner_comparison(THD *thd,
+ Item_bool_rowready_func2 *owner)
+{
+ // The value of YEAR(x) must be compared as integer
+ if (owner->compare_type_handler() != &type_handler_longlong)
+ return owner;
+ /*
+ Don't do the rewrite for the TIMESTAMP datatype. In order to handle
+ timestamp, we will need to support leap seconds, and MDEV-13995
+ should be fixed too.
+ */
+ enum_field_types field_type= args[0]->field_type();
+ if (field_type != MYSQL_TYPE_DATE &&
+ field_type != MYSQL_TYPE_DATETIME &&
+ field_type != MYSQL_TYPE_NEWDATE)
+ return owner;
+
+ Item_field *field_ref= args[0]->get_indexed_field();
+ if (!field_ref)
+ return owner;
+
+ // YEAR(indexed_col) CMP basic_const_item
+ if (this == owner->arguments()[0] &&
+ owner->arguments()[1]->basic_const_item())
+ return owner->do_cmp_rewrites_func_to_range(thd, this, field_ref,
+ owner->arguments()[1]);
+ // basic_const_item CMP YEAR(indexed_col)
+ if (this == owner->arguments()[1] &&
+ owner->arguments()[0]->basic_const_item())
+ return owner->do_cmp_rewrites_func_to_range(thd, this, field_ref,
+ owner->arguments()[0]);
+ return owner;
+}
+
+
+Item *
+Item_date_typecast::rewrite_owner_comparison(THD *thd,
+ Item_bool_rowready_func2 *owner)
+{
+ // The value of DATE(x) must be compared as dates.
+ if (owner->compare_type_handler() != &type_handler_newdate)
+ return owner;
+ /*
+ Don't do the rewrite for TIMESTAMP datatype. In order to handle
+ timestamp, we will need to support leap seconds, and MDEV-13995
+ should be fixed too.
+ */
+ enum_field_types field_type= args[0]->field_type();
+ if (field_type != MYSQL_TYPE_DATETIME &&
+ field_type != MYSQL_TYPE_DATE)
+ return owner;
+
+ Item_field *field_ref= args[0]->get_indexed_field();
+ if (!field_ref)
+ return owner;
+
+ if (field_type == MYSQL_TYPE_DATE)
+ {
+ if (this == owner->arguments()[0])
+ return Item_bool_rowready_func2::create_by_functype(thd, owner->functype(),
+ args[0],
+ owner->arguments()[1]);
+ else
+ return Item_bool_rowready_func2::create_by_functype(thd, owner->functype(),
+ owner->arguments()[0],
+ args[0]);
+ }
+
+ // DATE(indexed_col) CMP basic_const_item
+ if (this == owner->arguments()[0] &&
+ owner->arguments()[1]->basic_const_item())
+ return owner->do_cmp_rewrites_func_to_range(thd, this, field_ref,
+ owner->arguments()[1]);
+ // basic_const_item CMP DATE(indexed_col)
+ if (this == owner->arguments()[1] &&
+ owner->arguments()[0]->basic_const_item())
+ return owner->do_cmp_rewrites_func_to_range(thd, this, field_ref,
+ owner->arguments()[0]);
+ return owner;
+}
+
+
+Item *Item_func_year::create_start_bound(THD *thd,
+ Item_bool_rowready_func2 *cmp,
+ Item_field *field_ref,
+ Item *arg) const
+{
+ return new (thd->mem_root) Item_func_year_start(thd, arg);
+}
+
+
+Item *Item_date_typecast::create_start_bound(THD *thd,
+ Item_bool_rowready_func2 *cmp,
+ Item_field *field_ref,
+ Item *arg) const
+{
+ return new (thd->mem_root) Item_func_day_start(thd, arg);
+}
+
+
+Item *Item_func_year::create_end_bound(THD *thd,
+ Item_bool_rowready_func2 *cmp,
+ Item_field *field_ref,
+ Item *arg) const
+{
+ return new (thd->mem_root) Item_func_year_end(thd, arg);
+}
+
+
+Item *Item_date_typecast::create_end_bound(THD *thd,
+ Item_bool_rowready_func2 *cmp,
+ Item_field *field_ref,
+ Item *arg) const
+{
+ return new (thd->mem_root) Item_func_day_end(thd, arg);
+}
diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h
index 9c102e8..9f91913 100644
--- a/sql/item_timefunc.h
+++ b/sql/item_timefunc.h
@@ -398,7 +398,8 @@ class Item_func_yearweek :public Item_long_func
};
-class Item_func_year :public Item_long_func_date_field
+class Item_func_year :public Item_long_func_date_field,
+ public Func_to_range_rewrite
{
public:
Item_func_year(THD *thd, Item *a): Item_long_func_date_field(thd, a) {}
@@ -418,6 +419,11 @@ class Item_func_year :public Item_long_func_date_field
{
return !has_date_args();
}
+ Item *create_start_bound(THD *thd, Item_bool_rowready_func2 *cmp,
+ Item_field *field_ref, Item *arg) const;
+ Item *create_end_bound(THD *thd, Item_bool_rowready_func2 *cmp,
+ Item_field *field_ref, Item *arg) const;
+ Item *rewrite_owner_comparison(THD *thd, Item_bool_rowready_func2 *owner);
Item *get_copy(THD *thd, MEM_ROOT *mem_root)
{ return get_item_copy<Item_func_year>(thd, mem_root, this); }
};
@@ -1124,7 +1130,8 @@ class Item_temporal_typecast: public Item_temporal_func
void print(String *str, enum_query_type query_type);
};
-class Item_date_typecast :public Item_temporal_typecast
+class Item_date_typecast :public Item_temporal_typecast,
+ public Func_to_range_rewrite
{
public:
Item_date_typecast(THD *thd, Item *a): Item_temporal_typecast(thd, a) {}
@@ -1136,6 +1143,11 @@ class Item_date_typecast :public Item_temporal_typecast
{
args[0]->type_handler()->Item_date_typecast_fix_length_and_dec(this);
}
+ Item *create_start_bound(THD *thd, Item_bool_rowready_func2 *cmp,
+ Item_field *field_ref, Item *arg) const;
+ Item *create_end_bound(THD *thd, Item_bool_rowready_func2 *cmp,
+ Item_field *field_ref, Item *arg) const;
+ Item *rewrite_owner_comparison(THD *thd, Item_bool_rowready_func2 *owner);
Item *get_copy(THD *thd, MEM_ROOT *mem_root)
{ return get_item_copy<Item_date_typecast>(thd, mem_root, this); }
};
@@ -1351,4 +1363,77 @@ class Item_func_last_day :public Item_datefunc
{ return get_item_copy<Item_func_last_day>(thd, mem_root, this); }
};
+
+///////////////////////////////////////////////////////////////////////////////
+// Special item functions used by do_datetime_cmp_rewrites()
+///////////////////////////////////////////////////////////////////////////////
+
+class Item_func_day_start :public Item_datetimefunc
+{
+public:
+ Item_func_day_start(THD *thd, Item *a): Item_datetimefunc(thd, a) {}
+ const char *func_name() const { return "day_start"; }
+ bool get_date(MYSQL_TIME *res, ulonglong fuzzy_date);
+ Item *get_copy(THD *thd, MEM_ROOT *mem_root)
+ { return get_item_copy<Item_func_day_start>(thd, mem_root, this); }
+
+ void fix_length_and_dec()
+ {
+ fix_attributes_datetime(args[0]->datetime_precision());
+ maybe_null= true;
+ }
+};
+
+
+class Item_func_day_end :public Item_datetimefunc
+{
+public:
+ Item_func_day_end(THD *thd, Item *a): Item_datetimefunc(thd, a) {}
+ const char *func_name() const { return "day_end"; }
+ bool get_date(MYSQL_TIME *res, ulonglong fuzzy_date);
+ Item *get_copy(THD *thd, MEM_ROOT *mem_root)
+ { return get_item_copy<Item_func_day_end>(thd, mem_root, this); }
+
+ void fix_length_and_dec()
+ {
+ fix_attributes_datetime(args[0]->datetime_precision());
+ maybe_null= true;
+ }
+};
+
+
+class Item_func_year_start :public Item_datetimefunc
+{
+public:
+ Item_func_year_start(THD *thd, Item *a): Item_datetimefunc(thd, a) {}
+ const char *func_name() const { return "year_start"; }
+ bool get_date(MYSQL_TIME *res, ulonglong fuzzy_date);
+ Item *get_copy(THD *thd, MEM_ROOT *mem_root)
+ { return get_item_copy<Item_func_year_start>(thd, mem_root, this); }
+
+ void fix_length_and_dec()
+ {
+ fix_attributes_datetime(args[0]->datetime_precision());
+ maybe_null= true;
+ }
+};
+
+
+class Item_func_year_end :public Item_datetimefunc
+{
+public:
+ Item_func_year_end(THD *thd, Item *a): Item_datetimefunc(thd, a) {}
+ const char *func_name() const { return "year_end"; }
+ bool get_date(MYSQL_TIME *res, ulonglong fuzzy_date);
+ Item *get_copy(THD *thd, MEM_ROOT *mem_root)
+ { return get_item_copy<Item_func_year_end>(thd, mem_root, this); }
+
+ void fix_length_and_dec()
+ {
+ fix_attributes_datetime(args[0]->datetime_precision());
+ maybe_null= true;
+ }
+};
+
+
#endif /* ITEM_TIMEFUNC_INCLUDED */
Follow ups
